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 测试结果
测试插入结果:
测试查询结果: