【Spring Boot】浅析数据访问方式

一、JdbcTemplate

数据库连接模板，JDBC + Template的组合，是对JDBC的封装，替我们完成所有的JDBC底层工作，这样对于数据库的操作，再也不需要每次都进行连接、打开、关闭了

@RestController
public class HelloController {
	@Autowired
    JdbcTemplate jdbcTemplate;
    
    @RequestMapping("/hello")
    public String hello(){
    	String id = "1";
    	return  jdbcTemplate.queryForObject("select userName from person where id = ?",new Object[]{id},String.class);
    }

}

二、ORM之MyBatis

MyBatis是一款优秀的持久层框架，支持定制化SQL、存储过程以及高级映射，可以使用简单的XML或注解来配置和映射原生信息。
MyBatis 3提供的注解可以取代XML。

 		<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>LATEST</version>
        </dependency>

1、xml配置

@Mapper
@Component
public interface LoginMapper {
    String findNameById(int id);
    List<Person> getList();
}

resources/mapper下LoginMapper.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.xmliu.spboot.mapper.LoginMapper">
    <select id="findNameById" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT userName FROM person where id = #{0}
    </select>
    <select id="getList" resultType="com.xmliu.spboot.entity.Person">
        SELECT * FROM person 
    </select>
</mapper>

@RestController
public class HelloController {
	@Autowired
    LoginMapper loginMapper;
    @RequestMapping("/hello")
    public String hello(){
    	return loginMapper.findNameById(1);
    }
}

2、普通注解Select等

CRUD注解，包括@Select，@Insert，@Update，@Delete
映射注解，包括@Results，@Result，@ResultMap

@Mapper
@Component
public interface LoginMapper {
	@Select("select userName from person where id = 2")
    String findNameById();
}

@RestController
public class HelloController {
	@Autowired
    LoginMapper loginMapper;
    @RequestMapping("/hello")
    public String hello(){
    	return loginMapper.findNameById();
    }
}

3、高级注解SelectProvider等

CRUD高级注解，包括@SelectProvider，@InsertProvider，@UpdateProvider，@DeleteProvider，都是用来构建动态SQL

@Mapper
@Component
public interface LoginMapper {
	@SelectProvider(type = LoginProvider.class,method = "queryName")
    String queryName();
}

public class LoginProvider {
    public String queryName(){
        SQL sql = new SQL();
        return sql
                .SELECT("userName")
                .FROM("person")
                .WHERE("id = 3")
                .toString();
    }
}

@RestController
public class HelloController {
	@Autowired
    LoginMapper loginMapper; 
    @RequestMapping("/hello")
    public String hello(){
    	return loginMapper.queryName();
    }
}

三、ORM之JPA

JPA（Java Persistence API）是Java的持久化API，用于对象的持久化，非常强大，免去了使用JDBCTemplate开发的编写脚本工作。
JPA是一个规范化接口，封装了Hibernate的操作作为默认实现，让用户不通过任何操作就能完成数据库的操作

@Entity
@Getter
@Setter
public class Person {

    @Id
    @GeneratedValue
    private int id;

    @Column(name = "userName", nullable = true, length = 20)
    private String userName;
}

public interface LoginRepository extends JpaRepository<Person,Integer> {

    @Query(value = "SELECT userName FROM person p where p.id = :id",nativeQuery = true)
    String findNameJpa(@Param("id")int id);
}

@RestController
public class HelloController {
    @Autowired
    LoginRepository loginRepository;
    @RequestMapping("/hello")
    public String hello(){
    	return loginRepository.findNameJpa(4);
    }
}

PS: person表结构及内容如下