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

需求背景

在Springboot2框架中，使用Mysql和Mybatis功能：

1. Mysql+Datasource集成

2. Mybatis+XML用法详解

技术点

主要使用了两个包组件

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

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

3. 数据库初始化init.sql脚本内容（或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>
		<!-- 构建成可运行的Web项目 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- Mysql数据库集成 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!-- Mybatis集成 -->
		<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源码地址：

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

官方资料

SpringBoot2配置mysql+datasource

附加说明

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

Annotation使用方式如下：

XML使用方式如下：

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

 

下一章教程

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

该系列教程

SpringBoot从入门到精通教程

 

 

 

至此，全部介绍就结束了

 

------------------------------------------------------

------------------------------------------------------

 

关于我（个人域名）

我的开源项目集Github

 

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

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

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

不讲虚的，只做实干家

Talk is cheap，show me the code

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