SpringBoot入门3-SpringBoot整合数据库操作

SpringBoot整合数据库，通常我们现在都是mybatis，但是有些项目上还是使用原生的JDBCTemplate其实开发效率更高，不过不规范的编码可能会导致sql注入的风险。

1. 整合JDBC

对于数据访问层，无论是 SQL(关系型数据库) 还是 NOSQL(非关系型数据库)，Spring Boot 底层都是采用 Spring Data 的方式进行统一处理。

1.1 pom文件

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>compile</scope>
        </dependency>

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

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

spring-boot-starter-jdbc是必须要引入的依赖，mysql-connector-java是连接数据库所需依赖。

1.2 application.yml

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

1.3 controller

package com.liu.hello.controller;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@RestController
public class JDBCController {
    @Resource
    JdbcTemplate jdbcTemplate;

    @GetMapping("/userList")
    public List<Map<String, Object>> userList() {
        String sql = "select * from user";
        List<Map<String, Object>> allUser = jdbcTemplate.queryForList(sql);
        return allUser;
    }

    @GetMapping("/addUser")
    public  int addUser(){
        String sql = "insert into user value (100,'22','22')";
        int update = jdbcTemplate.update(sql);
        return update;
    }

    @GetMapping("/updateUser")
    public  int updateUser(){
        String sql = "update user set name = '33' where id = 100";
        int update = jdbcTemplate.update(sql);
        return update;
    }

    @GetMapping("/deleteUser")
    public  int deleteUser(){
        String sql = "delete from user where id = 100";
        int update = jdbcTemplate.update(sql);
        return update;
    }
  @GetMapping("/getListByid")
    public List<Map<String, Object>> getListByid(int id) {
        String sql = "select * from user where id =? ";
        List<Map<String, Object>> allUser = jdbcTemplate.queryForList(sql,id);
        return allUser;
    }
}

当时实际开发还是写三层架构，防止sql注入。
不过这种真的好用

2. 整合Mybatis

2.1 pom文件

在原来的基础上加

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.1</version>
		</dependency>

我们在加入实体操作插件lombok
lombok

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

2.2 application.yml

和JdbcTemplate 相同。

2.3 新增User类

package com.liu.hello.controller.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;
}

2.4 新增UserMapper接口

package com.liu.hello.mapper;

import com.liu.hello.controller.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface UserMapper {
    List<User> queryUserList();
}

2.5 新增对应的xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--configuration core file-->
<mapper namespace="com.liu.hello.mapper.UserMapper">

    <select id="queryUserList" resultType="User">
		select * from user
    </select>

</mapper>

我们Controller测试

  @Autowired
    UserMapper userMapper;

    @GetMapping("/userList2")
    public List<User> userList2() {
        List<User> allUser = userMapper.queryUserList();
        return allUser;
    }