使用ORM操作SQL数据库（JPA、Mybatis）

---

---

1 JDBCTemplate数据库连接模板

1.1 JDBC

JDBC（Java DataBase Connectivity）：用于执行数据库SQL语句的Java API。
JDBC操作每次都需要进行数据库连接，然后处理SQL语句、传值、关闭数据库。为了减少繁琐的步骤，减少开发人员的工作量，于是出现了JDBCTemplate。

1.2 JDBCTemplate

JDBCTemplate是对JDBC的封装。它已经完成所有的JDBC底层工作，所以，不需要每次都进行连接、打开、关闭等操作了。

1.3 使用JDBCTemplate实现数据的增删改查

1. 配置基础依赖

<!--导入JDBC的场景启动器-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<!--导入数据库驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

2. 在application.yml配置数据库的连接信息

#数据源连接信息
spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=yes&characterEncoding=UTF-8&useInformationSchema=true&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    driver-class-name: com.mysql.cj.jdbc.Driver

3. 新建一个实体类User，实现RowMapper类，重写mapRow方法，以便实体字段和数据表字段映射。

@Data
public class User implement RowMapper<User> {
	private int id;
	private String username;
	private String password;

	// 必须重写 mapRow方法
	@Override
	public User mapRow(ResultSet resultSet, int i) throws SQLException {
	User user = new User();
	user.setId(resultSet.getInt("id"));
	user.setUsername(resultSet.getString("username"));
	user.setPassword(resultSet.getString("password"));
	return user;
	}
}

4. 操作数据
   JDBCTemplate提供以下3个操作数据的方法：
   • execute：表示“执行”，用于直接执行SQL语句
   • update：表示“更新”，包括新增、修改、删除操作
   • query：表示查询

（1）添加数据

public void saveUser throws Exception {
	String sql = "INSERT INTO user (USERNAME, PASSWORD) VALUES ('test', '123')";
	int rows = jdbcTemplate.update(sql);
	System.out.println(rows);
}

（2）查询数据

public void getUserByName() throws Exception {
	String name = "test";
	String sql = "SELECT * FROM user WHERE USERNAME = ?";
	List<User> list = jdbcTemplate.query(sql, name);
	for (User user : list) {
		System.out.println(user);
	}
}

（3）查询所有记录

public void list() throws Exception {
	String sql = "SELECT * FROM user limit 1000";
	List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class));
	for (User userList : userList) {
		Syetem.out.println(userList);
	}
}

（4）修改数据

public void updateUserPassword() throws Exception {
	Integer id = 1;
	String password = "666";
	String sql = "UPDATE user Set PASSWORD = ? WHERE id = ?";
	int rows = jdbcTemplate.update(sql, password, id);
	System.out.println(rows);
}

（5）删除数据

public void deleteUserById() throws Exception {
	String sql = "DELETE FROM user WHERE id = ?";
	int rows = jdbcTemplate.update(sql, 1);
	System.out.println(rows);
}

1.4 ORM

ORM（Object Relation Mapping）是对象/关系映射。将数据库中的表和内存中的对象建立映射关系。

2 JPA（Java Persistence API）

2.1 SpringData和JPA

SpringData是Spring的一个子项目，旨在统一和简化各类型数据的持久化存储方式，其集成了各种关系型数据和NoSQL数据库。
SpringData官网
常用的主要模块：

• Spring Data Commons：通过共享的基础框架，支持跨数据库持久化
• Spring Data JDBC：提供对JDBC的支持，其中封装了JDBCTemplate
• Spring Data JPA：简化创建JPA数据访问层和跨存储的持久层功能
• Spring Data KeyValue：集成Redis和Riak，提供常用场景下的简单封装，便于构建key-value模块
• Spring Data MongoDB：集成数据库MongoDB支持
• Spring Data Redis：集成对Redis的支持

JPA（Java Persistence API）是Java的持久化API，用于对象的持久化。其免去了使用JDBCTemplate开发的编写工作，通过简单约定好接口方法的规则自动生成相应的JPQL语句，然后映射成POJO对象。
JPA是一个规范化接口，其封装了HIbernate的操作作为默认实现，让用户可以没有任何配置即可完成数据库的操作。
以下为SpringData、JPA和Hibernate的关系如下图。

Hibernate主要通过hibernate-annotation、hibernate-entitymanager和hibernate-core三个组件来操作数据。

• hibernate-annotation：包括标准的JPA annotation、Hibernate自身特殊功能的annotation
• hibernate-core：提供Hibernate所有的核心功能
• hibernate-entitymanager：实现标准的JPA，它是hibernate-core和JPA之间的适配器，对hibernate-core进行封装，使得Hibernate符合JPA的规范

2.2 配置JPA

<!-- Spring Boot JPA 依赖 -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
 
 <!-- MySQL 连接驱动依赖 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>${mysql-connector}</version>
</dependency>

#数据源连接信息
spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=yes&characterEncoding=UTF-8&useInformationSchema=true&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    driver-class-name: com.mysql.cj.jdbc.Driver
#JPA配置
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

2.3 JPA注解

JPA的常用注解

注解	说明
@Entity	声明类为实体
@Table	当实体类与其映射的数据库表名不同名时需要使用 @Table注解说明
@Column	当实体的属性与其映射的数据库表的列不同名时需要使用@Column 标注说明
@Id	标注用于声明一个实体类的属性映射为数据库的主键列
@IdClass	实体类定义联合主键@IdClass注解的使用
@GeneratedValue	为一个实体生成一个唯一标识的主键。@GeneratedValue注解有两个属性,分别是strategy和generator
@Basic	表示一个简单的属性到数据库表的字段的映射，指定加载方式@Basic(fetch=FetchType.LAZY)
@Transient	并非一个到数据库表的字段的映射,表示非持久化属性
@Enumerated	直接映射enum 枚举类型的字段

映射关系注解：
@OneToOne、@OneToMany、@ManyToOne、@ManyToMany

2.4 使用JPA构建实体数据表

public class Article implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(nullable = false, unique = true)
    @NotEmpty
    private String title;
    
    @Column(columnDefinition = "enum('图', '图文', '文')")
    private String type;
    
    private Boolean available = Boolean.FALSE;
    
    @Size(min = 0, max = 20)
    private String keyword;
    
    @Size(max = 255)
    private String description;
    
    @Column(nullable = false)
    private String body;
    
    @Transient
    private List<String> keywordlists;
    
    public List<String> getKeywordlists() {
        return Arrays.asList(this.keyword.trim().split("|"));
    }
    public void setKeywordlists(List<String> keywordlists) {
        this.keywordlists = keywordlists;
    }
}

2.5 JPA的接口

分页接口Pageable和Page的使用

 @RequestMapping("/article")
 public Result articleList(@RequestParam(value = "start", defaultValue = "0") Integer start, 
                           @RequestParam(value = "limit", defaultValue = "10") Integer limit) {
     start = start < 0 ? 0 : start;
     Sort sort = new Sort(Sort.Direction.DESC, "id");
     Pageable pageable = PageRequest.of(start, limit, sort);
     Page<Article> page = articleRepository.findAll(pageable);
     return ResultFactory.buildResult(page);   
}

2.6 JPA的查询方式

1. 使用约定方法名

SQL	方法例子
and	findByLastnameAndFirstname
or	findByLastnameOrFirstname
=	findByFirstnameEquals
between xxx and xxx	findByStartDateBetween
<	findByAgeLessThan, findByAgeBefore
<=	findByAgeLessThanEqual
>	findByAgeGreaterThan, findByAgeAfter
is null	findByAgeIsNull
is not null	findByAgeIsNotNull
like	findByFirstnameLike
not like	findByFirstnameNotLike
like ‘xxx%’	findByFirstnameStartingWith
like ‘%xxx’	findByFirstnameEndingWith
like ‘%xxx%’	findByFirstnameContaining
order by	findByAgeOrderByLastnameDesc
<>	findByAgeNot
in()	findByAgeIn(Collection<Age> ages)
not in()	findByAgeNotIn(Collection<Age> ages)
TRUE	findByActiveTrue
FALSE	findByActiveFalse

2. 使用原生SQL进行查询

public interface UserRepository extends JpaRepository<User, Long> {
	@Query(value = "select * from user u where u.id = :id", nativeQuery = true)
	User findById(@Param("id") Long id);
	
	@Modifying
	@Query("update User u set u.userName = ?1 where u.id = ?2")
	int modifyByIdAndUserId(String userName, Long id);
} 

2.7 实体关系映射开发

1. 实现“一对一”映射

@Entity
@Data
@Table(name = "student")
public class Student {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    private String name;
    
    @Column(columnDefinition = "enum('male', 'female')")
    private String sex;
    
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "card_id")
    private Card card;
}

2. 实现“一对多”映射

@Entity
@Data
@Table(name = "school")
public class School {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;

    @OneToMany()
    @JoinColumn(name = "school_id")
    private List<Teacher> teacherList;
}

@Data
@Entity
@Table(name = "teacher")
public class Teacher {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    private String name;
    
    @ManyToOne
    private School school;
}

3. 实现“多对多”映射，通过中间表的方式进行映射

@Entity
@Data
@Table(name = "student")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;

    @Column(columnDefinition = "enum('male', 'female')")
    private String sex;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "card_id")
    private Card card;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "teacher_student", joinColumns = {@JoinColumn(name = "s_id")}, inverseJoinColumns = {@JoinColumn(name = "t_id")})
    private Set<Teacher> teachers;
}

@Data
@Entity
@Table(name = "teacher")
public class Teacher {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;

    @ManyToOne
    private School school;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "teacher_student", joinColumns = {@JoinColumn(name = "t_id")}, inverseJoinColumns = {@JoinColumn(name = "s_id")})
    private Set<Student> students;
}

3 Mybatis----Java数据持久层框架

3.1 引入依赖以及数据库连接配置

<!-- MyBatis 依赖 -->
<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot</artifactId>
    <version>2.2.2</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

3.2 数据表的自动初始化

1. 在项目的“resources”目录下新建db目录，并添加“schema.sql”文件。在文件中写入DDL语句。
2. 在application.yml配置文件中加上数据表初始化的配置

spring:
	sql:
    init:
      mode: always
      schema-locations: db/schema.sql

3.3 注解实现CRUD

1. CRUD注解
   @Select、@Insert、@Update、@Delete

@Mapper
public interface UserMapper {

    @Select("SELECT * FROM user WHERE id = #{id}")
    User queryById(@Param("id") int id);

    @Select("SELECT * FROM user limit 1000")
    List<User> queryAll();

    //使用user对象传递数据,直接在#{}中括号内写user对象的变量名
    @Insert("INSERT INTO user(name, age) VALUES (#{name}, #{age})")
    int add(User user);

    @Delete("DELETE FROM user WHERE id = #{id}")
    int delById(int id);

    @Update("UPDATE user SET name = #{name}, age = #{age} WHERE id = #{id}")
    int updateById(User user);

    @Select("SELECT * FROM user limit 1000")
    Page<User> getUserList();
}

2. 映射注解。当属性名和字段名不同时，需要手动进行映射

	@Results({
        @Result(property = "username", column = "USERNAME"),
        @Result(property = "password", column = "PASSWORD")
	})
	@Select("SELECT * FROM user limit 1000")
  	List<User> list();

多个参数进行查询，必须加上注解@Param

	@Select("SELECT * FROM user WHERE name LIKE #{name} AND age LIKE #{age}")
	User getByNameAndAge(@Param("name") String name, @Param("age") int age)

3.5 分页功能

1. 添加分页依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.1.6</version>
</dependency>

2. 创建分页配置类

@Configuration
public class PageHelperConfig {
    
    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

3. 在控制器中使用

@RequestMapping("/listAll")
    public Result listUser(@RequestParam(value = "start", defaultValue = "0") int start,
                           @RequestParam(value = "size", defaultValue = "10") int size) throws Exception {
        PageHelper.startPage(start, size, "id desc");
        List<User> userList = userMapper.queryAll();
        PageInfo<User> pageInfo = new PageInfo<User>(userList);
        return ResultFactory.buildResult(pageInfo);
    }