spring boot 基础-数据库连接-mybatis

前言

上一节说到了操作数据库的方式有两种：jpa和nybatis，并且简单介绍了jpa的使用方式，这节介绍mybatis。 GitHub地址

使用前提

工具/jar包	版本
idea	2018.2.8
spring boot	2.1.4.RELEASE
Maven	3.6.0
jdk	1.8

pom.xml

<dependencies>
	<!--添加Mybatis依赖 -->
	<dependency>
		<groupId>org.mybatis.spring.boot</groupId>
		<artifactId>mybatis-spring-boot-starter</artifactId>
		<version>1.3.1</version>
	</dependency>

	<!--添加MySQL驱动依赖 -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
	</dependency>

	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
	</dependency>

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-configuration-processor</artifactId>
		<optional>true</optional>
	</dependency>

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-devtools</artifactId>
		<scope>runtime</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
		<scope>test</scope>
	</dependency>
</dependencies>

配置

本节使用两种方式来介绍mybatis：无配置文件注解版与 xml配置版 ，直接修改properties来切换。

application.properties

spring.profiles.active=dev
# spring.profiles.active=prod

application-dev.yml

spring:
  datasource:
    url: jdbc:mysql://192.168.80.11:3306/db_geoary?characterEncoding=utf-8
    username: root
    password: '123456'
    driver-class-name: com.mysql.jdbc.Driver

application-prod.yml

spring:
  datasource:
    url: jdbc:mysql://192.168.80.11:3306/db_geoary?characterEncoding=utf-8
    username: root
    password: '123456'
    driver-class-name: com.mysql.jdbc.Driver
# 此为mybatis配置文件的引入
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml

无配置文件注解版

先创建数据库表结构：

字段	类型	长度	主键	自增
id	int	10	是	+1
personname	varchar	20	否	否
age	varchar	4	否	否

Person.java

/**
 * @program: spring-boot-jdbc-02
 * @description: person
 * @author: geoary
 * @create: 2019-05-12 10:28
 **/
public class Person {
    private Integer id;
    private String name;
    private String age;

    public Person() {

    }

    public Person(String name, String age) {
        this.name = name;
        this.age = age;
    }
    
    // ...
    // get / set 方法
    // ...
    
    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age='" + age + '\'' +
                '}';
    }
}

PersonMap.java

package cn.geoary.mapper;

import cn.geoary.entity.Person;
import org.apache.ibatis.annotations.*;

import java.util.List;
public interface PersonMapper {
    // 这里指定了 表的列名与Java对象属性名的对应关系 name ==> personname
    @Select("select * from person")
    @Results({
            @Result(property = "name", column = "personname"),
            @Result(property = "age", column = "age")
    })
    List<Person> getList();

    @Select("select * from person where id=#{id}")
    @Results({
            @Result(property = "name", column = "personname"),
            @Result(property = "age", column = "age")
    })
    Person getOneById(Integer id);

    @Insert("insert into person(personname,age) values(#{name}, #{age})")
    void insertPerson(Person person);

    @Update("update person set personname=#{name}, age=#{age} where id=#{id}")
    void updatePerson(Person person);

    @Delete("delete from person where id=#{id}")
    void deletePersonById(Integer id);
}

SpringBootJdbcApplication.java

只是spring boot 的入口方法，指定mapper，使用 @MapperScan(mapper所在的包) 。

@SpringBootApplication
@MapperScan("cn.geoary.mapper")
public class SpringBootJdbc02Application {

    public static void main(String[] args) {
        SpringApplication.run(SpringBootJdbc02Application.class, args);
    }

}

测试

@SuppressWarnings("all")
@Autowired
private PersonMapper personMapper;

@Test
public void test01() {
	personMapper.insertPerson(new Person("geoary", "26"));
	personMapper.insertPerson(new Person("mayun", "40"));
	personMapper.insertPerson(new Person("wangjl", "50"));
}

@Test
public void test02(){
	Person person = personMapper.getOneById(new Integer(3));
	System.out.println(person);
	person.setName("wxz");
	personMapper.updatePerson(person);
	Person person2 = personMapper.getOneById(new Integer(3));
	System.out.println(person2);
}

@Test
public void test03(){
	personMapper.deletePersonById(new Integer(3));
	List<Person> personList = personMapper.getList();
	System.out.println(personList);
}

@Test
public void test04(){
	System.out.println(personMapper.getList());
}

xml配置版

目录结构如图所示：

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>
        <typeAlias alias="Integer" type="java.lang.Integer"/>
        <typeAlias alias="HashMap" type="java.util.HashMap"/>
        <typeAlias alias="ArrayList" type="java.util.ArrayList"/>
    </typeAliases>
</configuration>

personMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.geoary.mapper.PersonXmlMapper">

    <resultMap id="baseResultMap" type="cn.geoary.entity.Person">
        <id column="personname" property="name"/>
        <id column="age" property="age"/>
        <id column="id" property="id"></id>
    </resultMap>

    <sql id="base_column">
        id, personname, age
    </sql>

    <select id="getAll" resultMap="baseResultMap">
      select
      <include refid="base_column"/>
        from person
    </select>

    <select id="getAllMap" resultType="HashMap">
        select * from person
    </select>

    <select id="getOneByName" parameterType="java.lang.String" resultType="HashMap">
        select * from person where personname = #{name}
    </select>

    <insert id="insertPerson" parameterType="HashMap">
        insert into person (personname, age) values (#{name}, #{age})
    </insert>

    <update id="updatePerson" parameterType="HashMap">
        update person set personname = #{name}, age = #{age} where id = #{id}
    </update>

    <delete id="deletePerson" parameterType="Integer">
      delete from person where id = #{id}
    </delete>
</mapper>

测试

@SuppressWarnings("all")
@Autowired
private PersonXmlMapper personXmlMapper;

@Test
public void test05(){
	System.out.println(personXmlMapper.getAll());
}

@Test
public void test06(){
	System.out.println(personXmlMapper.getAllMap());
}

@Test
public void test07(){
	System.out.println(personXmlMapper.getOneByName("geoary"));
}

@Test
public void test08(){
	HashMap personMap = new HashMap();
	personMap.put("name", "zhaoyun");
	personMap.put("age", "300");
	personXmlMapper.insertPerson(personMap);
}

@Test
public void test09(){
	HashMap personMap = new HashMap();
	personMap.put("name", "caocao");
	personMap.put("age", "299");
	personMap.put("id", "3");
	personXmlMapper.updatePerson(personMap);
}

@Test
public void test10(){
	personXmlMapper.deletePerson(new Integer(3));
}