SpringBoot从入门到精通教程（六）- Mysql和Mybatis+XML用法详解

女爷i 2022-01-10 01:11 219阅读 0赞

# 需求背景 #

> 在Springboot2框架中，使用Mysql和Mybatis功能：
> 
> 1. Mysql+Datasource集成
> 
> 2. Mybatis+XML用法详解

# 技术点 #

主要使用了两个包组件

1. mysql-connector-java，Mysql数据库集成包

2. mybatis-spring-boot-starter，Mybatis集成包

3. [数据库初始化init.sql脚本内容][init.sql]（或[Docker安装MySQL数据库][Docker_MySQL]）

drop table city;
    
    CREATE TABLE `city` (
      `id` int(11) NOT NULL primary key,
      `name` varchar(32) DEFAULT NULL,
      `state` varchar(32) DEFAULT NULL,
      `country` varchar(32) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `city` (`id`, `name`, `state`, `country`)
    VALUES
    	(1, '广州16', 'GZ16', 'CH16'),
    	(2, '北京17', 'BJ17', 'CH17'),
    	(3, '深圳18', 'SZ18', 'CH18');

# 代码演示 #

**1. 引入包，配置pom.xml文件**

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    
    	<parent>
    		<groupId>com.md</groupId>
    		<artifactId>spring-boot2-parent</artifactId>
    		<version>0.0.1-SNAPSHOT</version>
    		<relativePath>../pom.xml</relativePath>
    	</parent>
    
    	<artifactId>spring-boot2-mysql-mybatis-xml</artifactId>
    	<packaging>jar</packaging>
    
    	<name>spring-boot2-mysql-mybatis-xml</name>
    	<description>Spring Boot, MVC, Rest API for App</description>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>net.sf.json-lib</groupId>
    			<artifactId>json-lib-ext-spring</artifactId>
    		</dependency>
    	</dependencies>
    	
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

**2. 配置application.yml**

spring: 
      datasource:
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.101:3306/cfdb?useUnicode=true&autoReconnect=true&allowMultiQueries=true&useSSL=false
        username: root
        password: 123456
    server:
      port: 9090
    mybatis: 
      config-location: classpath:mybatis-config.xml

**3. 配置mybatis-config.xml**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <typeAliases>
            <package name="com.md"/>
        </typeAliases>
        <mappers>
        		<mapper resource="mybatis/mapper/CityMapper.xml"/>
        </mappers>
    </configuration>

**4. 编写VO类**

package com.md.demo.bo;
    
    import java.io.Serializable;
    
    public class City implements Serializable {
    
    	private static final long serialVersionUID = 1L;
    
    	private Long id;
    
    	private String name;
    
    	private String state;
    
    	private String country;
    
    	public Long getId() {
    		return this.id;
    	}
    
    	public void setId(Long id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return this.name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public String getState() {
    		return this.state;
    	}
    
    	public void setState(String state) {
    		this.state = state;
    	}
    
    	public String getCountry() {
    		return this.country;
    	}
    
    	public void setCountry(String country) {
    		this.country = country;
    	}
    
    	@Override
    	public String toString() {
    		return getId() + "," + getName() + "," + getState() + "," + getCountry();
    	}
    }

**5. 编写Dao层**

接口

package com.md.demo.dao;
    
    import java.util.List;
    
    import com.md.demo.vo.CityVo;
    
    public interface CityDao {
    
    	public List<CityVo> listCities();
    
    	public CityVo getCityById(Long id);
    
    }

实现类

package com.md.demo.dao.impl;
    
    import java.util.List;
    
    import org.mybatis.spring.SqlSessionTemplate;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    
    import com.md.demo.dao.CityDao;
    import com.md.demo.vo.CityVo;
    
    @Component
    public class CityDaoImpl implements CityDao {
    
    	@Autowired
    	public SqlSessionTemplate sqlSessionTemplate;
    
    	@Override
    	public List<CityVo> listCities() {
    		return this.sqlSessionTemplate.selectList("listCities");
    	}
    
    	@Override
    	public CityVo getCityById(Long id) {
    		return this.sqlSessionTemplate.selectOne("getCityById", id);
    	}
    
    }

**6. 编写Service层**

接口

package com.md.demo.service;
    
    import java.util.List;
    import com.md.demo.vo.CityVo;
    
    public interface CityService {
    
    	public List<CityVo> listCities();
    
    	public CityVo getCityById(Long id);
    }

实现类

package com.md.demo.service.impl;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.md.demo.dao.CityDao;
    import com.md.demo.service.CityService;
    import com.md.demo.vo.CityVo;
    
    @Service
    public class CityServiceImpl implements CityService {
    
    	@Autowired
    	private CityDao cityDao;
    
    	/**
    	 * 获得城市列表
    	 */
    	@Override
    	public List<CityVo> listCities() {
    		return this.cityDao.listCities();
    	}
    
    	/**
    	 * 根据id，获得某个城市
    	 */
    	@Override
    	public CityVo getCityById(Long id) {
    		return this.cityDao.getCityById(id);
    	}
    
    }

**7. 编写接口访问层**

package com.md.demo.rest;
    
    import java.util.List;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.md.demo.service.CityService;
    import com.md.demo.util.JsonResult;
    import com.md.demo.util.ResultCode;
    import com.md.demo.vo.CityVo;
    
    /**
     * @author Minbo
     */
    @RestController
    public class InitRest {
    
    	protected static Logger logger = LoggerFactory.getLogger(InitRest.class);
    
    	@Autowired
    	private CityService cityService;
    
    	/**
    	 * http://localhost:9090/hello
    	 * 
    	 * @return
    	 */
    	@GetMapping("/hello")
    	public String hello() {
    		return "Hello greetings from spring-boot2-mysql-mybatis-xml";
    	}
    
    	/**
    	 * http://localhost:9090/listCities
    	 * 
    	 * @return
    	 */
    	@GetMapping("/listCities")
    	public JsonResult listCities() {
    		List<CityVo> list = this.cityService.listCities();
    		return new JsonResult(ResultCode.SUCCESS, list);
    	}
    
    	/**
    	 * http://localhost:9090/getCityById?id=1
    	 * 
    	 * @return
    	 */
    	@GetMapping("/getCityById")
    	public JsonResult listCities(Long id) {
    		CityVo obj = this.cityService.getCityById(id);
    		return new JsonResult(ResultCode.SUCCESS, obj);
    	}
    }

# 完整源码下载 #

我的[Github源码地址：][Github]

[https://github.com/hemin1003/spring-boot-study/tree/master/spring-boot2-study/spring-boot2-parent][Github]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70][]

# 官方资料 #

[SpringBoot2配置mysql+datasource][SpringBoot2_mysql_datasource]

# 附加说明 #

**建议1：在实际项目中，建议使用Mybatis+XML文件方式，而不建议使用Annotation方式**

Annotation使用方式如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1][]

XML使用方式如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 2][]

**原因：**因为使用了XML方式，可以在xml文件直接复制sql，从而评估sql的性能等（如果有专门的dba的话），而不需要在代码中查找，便捷高效

# 下一章教程 #

[SpringBoot从入门到精通教程（七）- Mysql多数据源和Hikari用法详解][SpringBoot_- Mysql_Hikari]

# 该系列教程 #

[SpringBoot从入门到精通教程][SpringBoot]

至此，全部介绍就结束了

\------------------------------------------------------

[关于我][Link 1]（个人域名）

[我的开源项目集Github][Github 1]

期望和大家***一起学习，一起成长***，共勉，O(∩\_∩)O谢谢

欢迎交流问题，可加个人QQ 469580884，

或者，加我的群号 **751925591**，一起探讨交流问题

不讲虚的，只做实干家

Talk is cheap，show me the code

![20180411181545874][]

如果觉得内容赞，您可以请我喝杯咖啡：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 3][]        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 4][]

[init.sql]: https://github.com/hemin1003/spring-boot-study/blob/master/spring-boot2-study/spring-boot2-parent/spring-boot2-mysql-mybatis-xml/init.sql
[Docker_MySQL]: https://blog.csdn.net/hemin1003/article/details/52808736
[Github]: https://github.com/hemin1003/spring-boot-study/tree/master/spring-boot2-study/spring-boot2-parent
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: /images/20220109/1e9cb41fb6b9480b9b4dbd75c090cdee.png
[SpringBoot2_mysql_datasource]: https://docs.spring.io/spring-boot/docs/2.0.9.RELEASE/reference/htmlsingle/#howto-use-yaml-for-external-properties
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]: /images/20220109/b36cd95ed0814b30a8e731ce923abf01.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 2]: /images/20220109/2e8f1258ccab4fa6929556f6db80b0e2.png
[SpringBoot_- Mysql_Hikari]: https://blog.csdn.net/hemin1003/article/details/94194400
[SpringBoot]: https://hemin.blog.csdn.net/column/info/40170
[Link 1]: http://heminit.com/about/
[Github 1]: https://github.com/hemin1003
[20180411181545874]: /images/20220109/2f5c2446ac3a4630bbf640cb2b6100f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190816110426562.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190816110923699.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZW1pbi5ibG9nLmNzZG4ubmV0,size_16,color_FFFFFF,t_70