【Spring】SpringBoot 学习教程（三）：Spring Boot连接MySQL数据库

1.简介

本文主要介绍Spring Boot连接数据库的方式，以及如何进行增删改查。

2.环境

• jdk 1.8+
• Maven 3.0+
• IDEA
• MySQL

3.Spring Boot用JdbcTemplate方式连接数据库

3.1 创建表和初始化数据

DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
  `id`                 bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name`             varchar(32) NOT NULL COMMENT '用户姓名',
  `nick_name` varchar(32) NOT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO `test_user` VALUES ('1', '张三', '张三风');
INSERT INTO `test_user` VALUES ('2', '李四', '李四雨');
INSERT INTO `test_user` VALUES ('3', '王五', '王五雪');

3.2 修改配置文件，此处使用yaml配置。

（此处有不理解的地方可见【Spring】SpringBoot 学习教程（二）：Spring Boot配置文件使用解析）

application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/mysql?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false    
    driverClassName: com.mysql.cj.jdbc.Driver
    username: root
    password: root

注意：我这里使用的是8.0+的数据库驱动文件，所以 driverClassName 写为 com.mysql.cj.jdbc.Driver
不然会报Loading class 'com.mysql.jdbc.Driver'. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver.错误

当使用5.0+的数据库驱动文件时，可以使用不加cj的 com.mysql.jdbc.Driver

3.3 创建与表对应的实体类

User.java

public class User {
    private Long id;
    private String Name;
    private String nickName;
        
       //此处省略了get和set方法
}

3.4 引入相关依赖

pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>                                             <!--Junit测试-->
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-test</artifactId>
       <scope>test</scope>
</dependency>

<dependency>
    <groupId>mysql</groupId>                            <!--mysql连接类-->
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

3.5 Dao层接口

UserDao.java

import java.util.List;


public interface UserDao {
    //添加用户
    public void add(User user);

    //删除用户
    public void del(Long id);

    //修改用户
    public int update(User user);

    //查找用户
    public User findUserById(Long id);

    //查找用户列表
    public List<User> findUserList();
}

3.6 接口实现类

@Repository
public class UserDaoImpl implements UserDao {


    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public void add(User user) {
        jdbcTemplate.update("insert into test_user(name, nick_name) values(?, ?)",
                user.getName(),user.getNickName());
    }


    @Override
    public void del(Long id) {
        jdbcTemplate.update("delete from test_user where id=?",
                id);
    }


    @Override
    public int update(User user) {
        return (int)jdbcTemplate.update("update test_user set name=?,nick_name=? where id=?",
                user.getName(),user.getNickName(),user.getId());
    }


    @Override
    public User findUserById(Long id) {
        List<User> userList = jdbcTemplate.query("select * from test_user where id=?",new Object[]{id},new BeanPropertyRowMapper(User.class));
        if(userList!=null && userList.size()>0){
            return  userList.get(0);
        }else{
            return null;
        }
    }


    @Override
    public List<User> findUserList() {
        List<User> userList = jdbcTemplate.query("select * from test_user ",new Object[]{},new BeanPropertyRowMapper(User.class));
        if(userList!=null && userList.size()>0){
            return  userList;
        }else{
            return null;
        }
    }
}

3.7 单元测试类

UserDaoTest.java

注意：这里的@SpringBootTest(classes = SpringBootDemoApplication.class)需要根据实际的Application进行更改！

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = SpringBootDemoApplication.class)
public class UserDaoTest {


    @Autowired
    private UserDao userDao;


    @Test
    public void testInsert(){
        User user = new User();
        user.setId(1L);
        user.setName("插入名字");
        user.setNickName("插入昵称");


        userDao.add(user);
        System.out.println("插入功能完成！");
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setId((long) 4);
        user.setName("插入名字");
        user.setNickName("插入昵称");
        System.out.println(userDao.update(user));
        System.out.println("更新功能完成！");
      }


@Test
public void testDel(){
    userDao.del((long) 4);
    System.out.println("删除功能完成！");
}


@Test
public void testFindUserById(){
    User user = userDao.findUserById((long) 3);
    System.out.println(user.getName()+":"+user.getNickName());
    System.out.println("通过id查找功能完成！");
}


@Test
public void testFindUserList(){
    List<User> userList = userDao.findUserList();
    for (User usertmp:userList) {
        System.out.println(usertmp.getName()+":"+usertmp.getNickName());
    }
    System.out.println("查找用户列表功能完成！");
}
}

3.8 测试结果

测试插入结果：


测试更新结果：

删除更新结果：

通过id查找打印结果：

查找列表打印结果：

4.Spring Boot集成Mybatis连接数据库

项目源码地址：https://github.com/826643679/Spring-Boot-Learning/tree/master/spring-boot-mybatis-xml

4.0 项目结构

4.1 创建数据库

users.sql

SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `userName` varchar(32) DEFAULT NULL COMMENT '用户名',
  `passWord` varchar(32) DEFAULT NULL COMMENT '密码',
  `user_sex` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

4.2 修改application配置文件

application.properties

#mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.neo.model


spring.datasource.url=jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

在配置文件中写入spring.datasource后，当spring boot 启动后数据源就会自动注入到sqlSessionFactory中，sqlSessionFactory会自动注入到Mapper中

4.3 定义User对象

User.java

package com.neo.model;


import java.io.Serializable;


import com.neo.enums.UserSexEnum;


public class User implements Serializable {


   private static final long serialVersionUID = 1L;
   private Long id;
   private String userName;
   private String passWord;
   private UserSexEnum userSex;
   private String nickName;


   public User() {
      super();
   }


   public User(String userName, String passWord, UserSexEnum userSex) {
      super();
      this.passWord = passWord;
      this.userName = userName;
      this.userSex = userSex;
   }


   public Long getId() {
      return id;
   }


   public void setId(Long id) {
      this.id = id;
   }


   public String getUserName() {
      return userName;
   }


   public void setUserName(String userName) {
      this.userName = userName;
   }


   public String getPassWord() {
      return passWord;
   }


   public void setPassWord(String passWord) {
      this.passWord = passWord;
   }


   public UserSexEnum getUserSex() {
      return userSex;
   }


   public void setUserSex(UserSexEnum userSex) {
      this.userSex = userSex;
   }


   public String getNickName() {
      return nickName;
   }


   public void setNickName(String nickName) {
      this.nickName = nickName;
   }


   @Override
   public String toString() {
      // TODO Auto-generated method stub
      return "userName " + this.userName + ", pasword " + this.passWord + "sex " + userSex.name();
   }


}

性别枚举类：

UserSexEnum.java

package com.neo.enums;


public enum UserSexEnum {
   MAN, WOMAN
}

4.4 定义UserMapper接口

package com.neo.mapper;


import java.util.List;


import com.neo.model.User;


public interface UserMapper {
   
   List<User> getAll();
   
   User getOne(Long id);


   void insert(User user);


   void update(User user);


   void delete(Long id);


}

4.5 配置Mapper的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="com.neo.mapper.UserMapper" >
    <resultMap id="BaseResultMap" type="com.neo.model.User" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="userName" property="userName" jdbcType="VARCHAR" />
        <result column="passWord" property="passWord" jdbcType="VARCHAR" />
        <result column="user_sex" property="userSex" javaType="com.neo.enums.UserSexEnum"/>
        <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
    </resultMap>
    
    <sql id="Base_Column_List" >
        id, userName, passWord, user_sex, nick_name
    </sql>


    <select id="getAll" resultMap="BaseResultMap"  >
       SELECT
       <include refid="Base_Column_List" />
      FROM users
    </select>


    <select id="getOne" parameterType="java.lang.Long" resultMap="BaseResultMap" >
        SELECT
       <include refid="Base_Column_List" />
      FROM users
      WHERE id = #{id}
    </select>


    <insert id="insert" parameterType="com.neo.model.User" >
       INSERT INTO
              users
              (userName,passWord,user_sex)
           VALUES
              (#{userName}, #{passWord}, #{userSex})
    </insert>
    
    <update id="update" parameterType="com.neo.model.User" >
       UPDATE
              users
       SET
           <if test="userName != null">userName = #{userName},</if>
           <if test="passWord != null">passWord = #{passWord},</if>
           nick_name = #{nickName}
       WHERE
              id = #{id}
    </update>
    
    <delete id="delete" parameterType="java.lang.Long" >
       DELETE FROM
               users
       WHERE
               id =#{id}
    </delete>


</mapper>

4.6 修改Application启动类，在此处加上xml文件的扫描包

MybatisXmlApplication.java

package com.neo;


import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication
@MapperScan("com.neo.mapper")
public class MybatisXmlApplication {


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

4.7 编写测试类

package com.neo.mapper;


import java.util.List;


import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;


import com.neo.model.User;
import com.neo.enums.UserSexEnum;


@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {


   @Autowired
   private UserMapper userMapper;


   @Test
   public void testInsert() throws Exception {
      userMapper.insert(new User("aa", "a123456", UserSexEnum.MAN));
      userMapper.insert(new User("bb", "b123456", UserSexEnum.WOMAN));
      userMapper.insert(new User("cc", "b123456", UserSexEnum.WOMAN));


      Assert.assertEquals(3, userMapper.getAll().size());
   }


   @Test
   public void testQuery() throws Exception {
      List<User> users = userMapper.getAll();
      if(users==null || users.size()==0){
         System.out.println("is null");
      }else{
         System.out.println("=======================================");
         System.out.println(users.toString());
         System.out.println("=======================================");
      }
   }
   
   
   @Test
   public void testUpdate() throws Exception {
      User user = userMapper.getOne(6l);
      System.out.println(user.toString());
      user.setNickName("neo");
      userMapper.update(user);
      Assert.assertTrue(("neo".equals(userMapper.getOne(6l).getNickName())));
   }


}

4.8 测试结果

测试插入结果：

测试查询结果：