SpringBoot Data Interaction

JDBC Interaction Framework

In addition to MyBatis, which we’ve discussed previously, Spring also provides an official and convenient JDBC tool that supports quick CRUD (Create, Read, Update, Delete) operations. To start using it, first add the dependency to your project with the following starter:

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

Once the dependency is added, you can easily begin using Spring’s JDBC utilities for database interactions.

JdbcTemplate Class

Besides MyBatis, which we've already discussed, Spring also provides a very convenient tool for JDBC operations. This tool can quickly perform CRUD (Create, Read, Update, Delete) operations. To get started, we first add the dependency using the starter:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

To interact with a database, the simplest and most direct way is to use JdbcTemplate:

@Resource
JdbcTemplate template;

JdbcTemplate provides many built-in methods that make database operations easier. For example, if we want to query a single record from the database:

We can use queryForMap to quickly query a single row of data and retrieve it as a Map:

@Test
void contextLoads() {
    Map<String, Object> map = template.queryForMap("select * from user where id = ?", 1);
    System.out.println(map);
}

This approach is very convenient, as it retrieves the row where id = 1 and maps the column names to their corresponding values, making it easy to access the data in a key-value format.

We can also write a custom mapper to directly map query results into an object:

@Data
@AllArgsConstructor
public class User {
    int id;
    String name;
    String email;
    String password;
}

@Test
void contextLoads() {
    User user = template.queryForObject("select * from user where id = ?",
        (r, i) -> new User(r.getInt(1), r.getString(2), r.getString(3), r.getString(4)), 1);
    System.out.println(user);
}

In this example, queryForObject is used with a custom row mapper to directly retrieve a User object based on the query results, making data retrieval more type-safe and manageable.

Apart from this, JdbcTemplate also provides the update method, which is versatile for insert, update, and delete operations:

@Test
void contextLoads() {
    int update = template.update("insert into user values(2, 'admin', '654321@qq.com', '123456')");
    System.out.println("Updated " + update + " row(s)");
}

This way, for small projects or test cases, JdbcTemplate can be used efficiently for a variety of operations, making it a quick and convenient tool for database interactions.

Simple JDBC Insert in Spring JDBC

For certain insert operations, Spring JDBC offers the SimpleJdbcInsert utility, which makes it easy to perform advanced insert operations. For example, if the table's primary key is an auto-increment ID, SimpleJdbcInsert can insert a record and return the automatically generated ID, which is very convenient:

@Configuration
public class WebConfiguration {

    @Resource
    DataSource source;

    @Test
    void contextLoads() {
        // Creating a SimpleJdbcInsert instance
        SimpleJdbcInsert simple = new SimpleJdbcInsert(source)
                .withTableName("user")   // Set the target table name
                .usingGeneratedKeyColumns("id");    // Specify the auto-increment primary key column

        Map<String, Object> user = new HashMap<>(2);  // Inserting requires a Map to hold the data
        user.put("name", "bob");
        user.put("email", "112233@qq.com");
        user.put("password", "123456");

        Number number = simple.executeAndReturnKey(user);   // The returned Number is the generated primary key
        System.out.println(number);
    }
}

This approach enables quick insertion while also returning the auto-generated primary key, making it quite convenient.

Although Spring JDBC provides these helpful utilities, they are mainly suitable for small, simple projects. As projects grow in complexity, Spring JDBC might not be the ideal choice. JPA framework will be better.

---

JPA Framework

• After using MyBatis, looking back at JDBC, it just feels outdated.
• So, does your project feel bold and modern?
• Of course, my code is top-notch, no joke!
• Have you ever dreamed of a project without any SQL statements?
• No SQL statements? How would I interact with the database then?
• Sounds like you don’t fully understand, huh?
• Understand what?
• You want to understand? Come check out my project—I’ll show you something amazing.
• Amazing? Is it some new framework?
• New framework? It’s even better! It’ll make your project shine!
• Wow, Jack, there are no SQL statements in your project. What framework is this?

In the projects we’ve built before, we often find that most database interactions ultimately serve one purpose: mapping database records to Java objects. For example, when finding a user by username or retrieving a student’s information by ID, we only need to write the correct SQL query using MyBatis, and the results are automatically mapped to Java objects. We then just call the method in the Mapper to get the entity directly, making it easier to work with data in Java.

But all these operations share a common pattern: querying based on some condition, with the results being mapped to an entity class. You may find yourself repeating the same pattern in SQL: select * from xxx where xxx=xxx. For such simple SQL statements, we could almost create a template for reuse. So, wouldn’t it be nice to have a framework that packages these repetitive patterns, hiding similar SQL generation and execution from us?

That’s where JPA (Java Persistence API) comes in. It allows us to work with database records as Java objects directly, without writing SQL for each interaction. The framework takes care of building and executing SQL statements based on entity classes, making it an efficient and modern approach to database handling.

Understanding Spring Data JPA

Why is it that, despite MyBatis being powerful and popular, only around 10% of developers favor it? The reality is that in North America, JPA dominates, whereas MyBatis isn’t as widely adopted as it is in China. This is evident in Spring Boot, which offers an official starter for JPA but didn’t officially include MyBatis until Spring Boot 3.

So, what is JPA?

JPA (Java Persistence API), similar to JDBC, is an official set of interfaces. However, unlike traditional JDBC, JPA was designed to support ORM (Object-Relational Mapping), enabling a mapping between relational databases and objects. With JPA, you interact with data as if you’re handling objects, without needing to write complex SQL statements.

Some common JPA implementations include:

1. Hibernate: A widely used JPA implementation that provides powerful ORM capabilities. Hibernate maps Java objects to database tables and offers robust querying and caching mechanisms.
2. EclipseLink: Developed and maintained by the Eclipse Foundation, this JPA implementation offers features like ORM, caching, query language support, and connection pool management. It is known for high performance and scalability.
3. OpenJPA: An Apache project that provides a high-performance JPA implementation with features like lazy loading, caching, and distributed transactions.
4. TopLink: Originally developed by Oracle, TopLink is another ORM framework implementing JPA. Although EclipseLink has replaced TopLink as Oracle’s recommended JPA implementation, TopLink is still widely used.

Previously, with JDBC or MyBatis, we accessed data by writing SQL queries directly. Most of these operations involve reading data and mapping it to entity classes. So, why not directly link these entity classes to database tables? For instance, an object’s properties could map directly to a table’s fields, making each row of data a Java object. The framework could then handle SQL generation based on the defined mappings, freeing us from writing repetitive SQL statements.

The most commonly used framework that implements the JPA specification is Hibernate, a heavyweight framework that has a steeper learning curve compared to MyBatis. Spring Data JPA uses Hibernate as its default implementation, adding its own layer of abstraction to simplify usage.

Official site: https://spring.io/projects/spring-data-jpa

Getting Started with JPA

As with other Spring Boot modules, we only need to import the starter dependency to get started:

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

    </parent>
    <groupId>com.example</groupId>
    <artifactId>SpringBootDataStudy</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringBootDataStudy</name>
    <description>SpringBootDataStudy</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>3.3.0</version>
        </dependency>

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

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

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.30</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Next, we create a simple class, such as an Account class, and define all the attributes corresponding to the account details:

@Data
public class Account {
    int id;
    String username;
    String password;
}

Then, we add annotations to map the properties to the database columns so JPA understands the structure of the corresponding database table. Here are some annotations used:

@Data
@Entity   // Marks this class as an entity
@Table(name = "account")    // Maps to the "account" table in the database
public class Account {

    @GeneratedValue(strategy = GenerationType.IDENTITY)   // Auto-increment strategy
    @Column(name = "id")    // Maps to the "id" column in the table
    @Id     // Marks this property as the primary key
    int id;

    @Column(name = "username")   // Maps to the "username" column
    String username;

    @Column(name = "password")   // Maps to the "password" column
    String password;
}

Next, let’s adjust the configuration to enable SQL logging:

spring:
  jpa:
    # Enables SQL execution logging
    show-sql: true
    hibernate:
      # Sets database structure to be auto-created if missing
      ddl-auto: update

The ddl-auto property controls how table creation and updates are handled. The available options are:

• none: No changes; you must manage the database structure manually.
• create: Deletes and recreates all tables every time the application starts.
• create-drop: Deletes and recreates all tables on startup, then drops them at shutdown.
• update: Checks the table structure and makes changes to match the entity definitions.
• validate: Verifies the table structure matches the entities; throws an error if not.

This property saves time on database structure management, allowing for easier development and testing. However, in production, it’s recommended to use migration tools for database changes.

On startup, you’ll see SQL statements in the logs indicating the creation of tables:

Our database now has the corresponding tables automatically created.

To access the table, we create a Repository interface:

@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
}

JpaRepository takes two generic parameters: the entity class and the ID type. It already provides common database operations, so implementing this interface gives us CRUD capabilities.

We can now inject and use this interface:

@Resource
AccountRepository repository;

@Test
void contextLoads() {
    Account account = new Account();
    account.setUsername("Xiaohong");
    account.setPassword("1234567");
    System.out.println(repository.save(account).getId());   // Uses save to insert data, returning the inserted entity. If ID is auto-generated, it’s assigned to the entity automatically.
}

Execution result:

Querying is also straightforward:

@Test
void contextLoads() {
    // Finds by ID, returning an Optional-wrapped result
    repository.findById(1).ifPresent(System.out::println);
}

Output:

This includes common methods for counting, deleting, and other operations—all possible by simply configuring the repository interface.

With JPA, we don’t write any SQL in the project code. JPA automatically handles all data mappings and relationships based on the annotations.

Compared to MyBatis, JPA is a fully automated ORM framework, while MyBatis is semi-automated at best.

方法名称拼接自定义SQL

虽然接口预置的方法使用起来非常方便，但是如果我们需要进行条件查询等操作或是一些判断，就需要自定义一些方法来实现，同样的，我们不需要编写SQL语句，而是通过方法名称的拼接来实现条件判断，这里列出了所有支持的条件判断名称：

属性	拼接方法名称示例	执行的语句
Distinct	findDistinctByLastnameAndFirstname	select distinct … where x.lastname = ?1 and x.firstname = ?2
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is，Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull，Null	findByAge(Is)Null	… where x.age is null
IsNotNull，NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1（参数与附加%绑定）
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1（参数与前缀%绑定）
Containing	findByFirstnameContaining	… where x.firstname like ?1（参数绑定以%包装）
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection ages)	… where x.age not in ?1
True	findByActiveTrue	… where x.active = true
False	findByActiveFalse	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstname) = UPPER(?1)

比如我们想要实现根据用户名模糊匹配查找用户：

@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
    //按照表中的规则进行名称拼接，不用刻意去记，IDEA会有提示
    List<Account> findAllByUsernameLike(String str);
}

我们来测试一下：

@Test
void contextLoads() {
    repository.findAllByUsernameLike("%明%").forEach(System.out::println);
}

又比如我们想同时根据用户名和ID一起查询：

@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
    List<Account> findAllByUsernameLike(String str);

    Account findByIdAndUsername(int id, String username);
    //也可以使用Optional类进行包装，Optional<Account> findByIdAndUsername(int id, String username);
}

@Test
void contextLoads() {
    System.out.println(repository.findByIdAndUsername(1, "小明"));
}

比如我们想判断数据库中是否存在某个ID的用户：

@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
    List<Account> findAllByUsernameLike(String str);
    Account findByIdAndUsername(int id, String username);
    //使用exists判断是否存在
    boolean existsAccountById(int id);
}

注意自定义条件操作的方法名称一定要遵循规则，不然会出现异常：

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract  ...

有了这些操作，我们在编写一些简单SQL的时候就很方便了，用久了甚至直接忘记SQL怎么写。

关联查询

在实际开发中，比较常见的场景还有关联查询，也就是我们会在表中添加一个外键字段，而此外键字段又指向了另一个表中的数据，当我们查询数据时，可能会需要将关联数据也一并获取，比如我们想要查询某个用户的详细信息，一般用户简略信息会单独存放一个表，而用户详细信息会单独存放在另一个表中。当然，除了用户详细信息之外，可能在某些电商平台还会有用户的购买记录、用户的购物车，交流社区中的用户帖子、用户评论等，这些都是需要根据用户信息进行关联查询的内容。

我们知道，在JPA中，每张表实际上就是一个实体类的映射，而表之间的关联关系，也可以看作对象之间的依赖关系，比如用户表中包含了用户详细信息的ID字段作为外键，那么实际上就是用户表实体中包括了用户详细信息实体对象：

@Data
@Entity
@Table(name = "users_detail")
public class AccountDetail {

    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    int id;

    @Column(name = "address")
    String address;

    @Column(name = "email")
    String email;

    @Column(name = "phone")
    String phone;

    @Column(name = "real_name")
    String realName;
}

而用户信息和用户详细信息之间形成了一对一的关系，那么这时我们就可以直接在类中指定这种关系：

@Data
@Entity
@Table(name = "users")
public class Account {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    @Id
    int id;

    @Column(name = "username")
    String username;

    @Column(name = "password")
    String password;

    @JoinColumn(name = "detail_id")   //指定存储外键的字段名称
    @OneToOne    //声明为一对一关系
    AccountDetail detail;
}

在修改实体类信息后，我们发现在启动时也进行了更新，日志如下：

Hibernate: alter table users add column detail_id integer
Hibernate: create table users_detail (id integer not null auto_increment, address varchar(255), email varchar(255), phone varchar(255), real_name varchar(255), primary key (id)) engine=InnoDB
Hibernate: alter table users add constraint FK7gb021edkxf3mdv5bs75ni6jd foreign key (detail_id) references users_detail (id)

是不是感觉非常方便！都懒得去手动改表结构了。

接着我们往用户详细信息中添加一些数据，一会我们可以直接进行查询：

@Test
void pageAccount() {
    repository.findById(1).ifPresent(System.out::println);
}

查询后，可以发现，得到如下结果：

Hibernate: select account0_.id as id1_0_0_, account0_.detail_id as detail_i4_0_0_, account0_.password as password2_0_0_, account0_.username as username3_0_0_, accountdet1_.id as id1_1_1_, accountdet1_.address as address2_1_1_, accountdet1_.email as email3_1_1_, accountdet1_.phone as phone4_1_1_, accountdet1_.real_name as real_nam5_1_1_ from users account0_ left outer join users_detail accountdet1_ on account0_.detail_id=accountdet1_.id where account0_.id=?
Account(id=1, username=Test, password=123456, detail=AccountDetail(id=1, address=四川省成都市青羊区, email=8371289@qq.com, phone=1234567890, realName=本伟))

也就是，在建立关系之后，我们查询Account对象时，会自动将关联数据的结果也一并进行查询。

那要是我们只想要Account的数据，不想要用户详细信息数据怎么办呢？我希望在我要用的时候再获取详细信息，这样可以节省一些网络开销，我们可以设置懒加载，这样只有在需要时才会向数据库获取：

@JoinColumn(name = "detail_id")
@OneToOne(fetch = FetchType.LAZY)    //将获取类型改为LAZY
AccountDetail detail;

接着我们测试一下：

@Transactional   //懒加载属性需要在事务环境下获取，因为repository方法调用完后Session会立即关闭
@Test
void pageAccount() {
    repository.findById(1).ifPresent(account -> {
        System.out.println(account.getUsername());   //获取用户名
        System.out.println(account.getDetail());  //获取详细信息（懒加载）
    });
}

接着我们来看看控制台输出了什么：

Hibernate: select account0_.id as id1_0_0_, account0_.detail_id as detail_i4_0_0_, account0_.password as password2_0_0_, account0_.username as username3_0_0_ from users account0_ where account0_.id=?
Test
Hibernate: select accountdet0_.id as id1_1_0_, accountdet0_.address as address2_1_0_, accountdet0_.email as email3_1_0_, accountdet0_.phone as phone4_1_0_, accountdet0_.real_name as real_nam5_1_0_ from users_detail accountdet0_ where accountdet0_.id=?
AccountDetail(id=1, address=四川省成都市青羊区, email=8371289@qq.com, phone=1234567890, realName=卢本)

可以看到，获取用户名之前，并没有去查询用户的详细信息，而是当我们获取详细信息时才进行查询并返回AccountDetail对象。

那么我们是否也可以在添加数据时，利用实体类之间的关联信息，一次性添加两张表的数据呢？可以，但是我们需要稍微修改一下级联关联操作设定：

@JoinColumn(name = "detail_id")
@OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL) //设置关联操作为ALL
AccountDetail detail;

• ALL：所有操作都进行关联操作
• PERSIST：插入操作时才进行关联操作
• REMOVE：删除操作时才进行关联操作
• MERGE：修改操作时才进行关联操作

可以多个并存，接着我们来进行一下测试：

@Test
void addAccount(){
    Account account = new Account();
    account.setUsername("Nike");
    account.setPassword("123456");
    AccountDetail detail = new AccountDetail();
    detail.setAddress("重庆市渝中区解放碑");
    detail.setPhone("1234567890");
    detail.setEmail("73281937@qq.com");
    detail.setRealName("张三");
    account.setDetail(detail);
    account = repository.save(account);
    System.out.println("插入时，自动生成的主键ID为："+account.getId()+"，外键ID为："+account.getDetail().getId());
}

可以看到日志结果：

Hibernate: insert into users_detail (address, email, phone, real_name) values (?, ?, ?, ?)
Hibernate: insert into users (detail_id, password, username) values (?, ?, ?)
插入时，自动生成的主键ID为：6，外键ID为：3

结束后会发现数据库中两张表都同时存在数据。

接着我们来看一对多关联，比如每个用户的成绩信息：

@JoinColumn(name = "uid")  //注意这里的name指的是Score表中的uid字段对应的就是当前的主键，会将uid外键设置为当前的主键
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.REMOVE)   //在移除Account时，一并移除所有的成绩信息，依然使用懒加载
List<Score> scoreList;

@Data
@Entity
@Table(name = "users_score")   //成绩表，注意只存成绩，不存学科信息，学科信息id做外键
public class Score {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    @Id
    int id;

    @OneToOne   //一对一对应到学科上
    @JoinColumn(name = "cid")
    Subject subject;

    @Column(name = "socre")
    double score;

    @Column(name = "uid")
    int uid;
}

@Data
@Entity
@Table(name = "subjects")   //学科信息表
public class Subject {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cid")
    @Id
    int cid;

    @Column(name = "name")
    String name;

    @Column(name = "teacher")
    String teacher;

    @Column(name = "time")
    int time;
}

在数据库中填写相应数据，接着我们就可以查询用户的成绩信息了：

@Transactional
@Test
void test() {
    repository.findById(1).ifPresent(account -> {
        account.getScoreList().forEach(System.out::println);
    });
}

成功得到用户所有的成绩信息，包括得分和学科信息。

同样的，我们还可以将对应成绩中的教师信息单独分出一张表存储，并建立多对一的关系，因为多门课程可能由同一个老师教授（千万别搞晕了，一定要理清楚关联关系，同时也是考验你的基础扎不扎实)：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "tid")   //存储教师ID的字段，和一对一是一样的，也会当前表中创个外键
Teacher teacher;

接着就是教师实体类了：

@Data
@Entity
@Table(name = "teachers")
public class Teacher {
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    int id;

    @Column(name = "name")
    String name;

    @Column(name = "sex")
    String sex;
}

最后我们再进行一下测试：

@Transactional
@Test
void test() {
    repository.findById(3).ifPresent(account -> {
        account.getScoreList().forEach(score -> {
            System.out.println("课程名称："+score.getSubject().getName());
            System.out.println("得分："+score.getScore());
            System.out.println("任课教师："+score.getSubject().getTeacher().getName());
        });
    });
}

成功得到多对一的教师信息。

最后我们再来看最复杂的情况，现在我们一门课程可以由多个老师教授，而一个老师也可以教授多个课程，那么这种情况就是很明显的多对多场景，现在又该如何定义呢？我们可以像之前一样，插入一张中间表表示教授关系，这个表中专门存储哪个老师教哪个科目：

@ManyToMany(fetch = FetchType.LAZY)   //多对多场景
@JoinTable(name = "teach_relation",     //多对多中间关联表
        joinColumns = @JoinColumn(name = "cid"),    //当前实体主键在关联表中的字段名称
        inverseJoinColumns = @JoinColumn(name = "tid")   //教师实体主键在关联表中的字段名称
)
List<Teacher> teacher;

接着，JPA会自动创建一张中间表，并自动设置外键，我们就可以将多对多关联信息编写在其中了。

JPQL自定义SQL语句

虽然SpringDataJPA能够简化大部分数据获取场景，但是难免会有一些特殊的场景，需要使用复杂查询才能够去完成，这时你又会发现，如果要实现，只能用回Mybatis了，因为我们需要自己手动编写SQL语句，过度依赖SpringDataJPA会使得SQL语句不可控。

使用JPA，我们也可以像Mybatis那样，直接编写SQL语句，不过它是JPQL语言，与原生SQL语句很类似，但是它是面向对象的，当然我们也可以编写原生SQL语句。

比如我们要更新用户表中指定ID用户的密码：

@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {

    @Transactional    //DML操作需要事务环境，可以不在这里声明，但是调用时一定要处于事务环境下
    @Modifying     //表示这是一个DML操作
    @Query("update Account set password = ?2 where id = ?1") //这里操作的是一个实体类对应的表，参数使用?代表，后面接第n个参数
    int updatePasswordById(int id, String newPassword);
}

@Test
void updateAccount(){
    repository.updatePasswordById(1, "654321");
}

现在我想使用原生SQL来实现根据用户名称修改密码：

@Transactional
@Modifying
@Query(value = "update users set password = :pwd where username = :name", nativeQuery = true) //使用原生SQL，和Mybatis一样，这里使用 :名称 表示参数，当然也可以继续用上面那种方式。
int updatePasswordByUsername(@Param("name") String username,   //我们可以使用@Param指定名称
                             @Param("pwd") String newPassword);

@Test
void updateAccount(){
    repository.updatePasswordByUsername("Admin", "654321");
}

通过编写原生SQL，在一定程度上弥补了SQL不可控的问题。

虽然JPA能够为我们带来非常便捷的开发体验，但是正是因为太便捷了，保姆级的体验有时也会适得其反，尤其是一些国内用到复杂查询业务的项目，可能开发到后期特别庞大时，就只能从底层SQL语句开始进行优化，而由于JPA尽可能地在屏蔽我们对SQL语句的编写，所以后期优化是个大问题，并且Hibernate相对于Mybatis来说，更加重量级。不过，在微服务的时代，单体项目一般不会太大，JPA的劣势并没有太明显地体现出来。

---

MybatisPlus框架

前面我们体验了JPA带来的快速开发体验，但是我们发现，面对一些复杂查询时，JPA似乎有点力不从心，反观稍微麻烦一点的Mybatis却能够手动编写SQL，使用起来更加灵活，那么有没有一种既能灵活掌控逻辑又能快速完成开发的持久层框架呢？

MyBatis-Plus（简称 MP）是一个 MyBatis的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

MybatisPlus的愿景是成为 MyBatis 最好的搭档，就像 魂斗罗 中的 1P、2P，基友搭配，效率翻倍。

官方网站地址：https://baomidou.com

MybatisPlus具有以下特性：

• 无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
• 损耗小：启动即会自动注入基本 CURD，性能基本无损耗，直接面向对象操作
• 强大的 CRUD 操作：内置通用 Mapper、通用 Service，仅仅通过少量配置即可实现单表大部分 CRUD 操作，更有强大的条件构造器，满足各类使用需求
• 支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
• 支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题
• 支持 ActiveRecord 模式：支持 ActiveRecord 形式调用，实体类只需继承 Model 类即可进行强大的 CRUD 操作
• 支持自定义全局通用操作：支持全局通用方法注入（ Write once, use anywhere ）
• 内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用
• 内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询
• 分页插件支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
• 内置性能分析插件：可输出 SQL 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询
• 内置全局拦截插件：提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作

框架整体结构如下：

不过，光说还是不能体会到它带来的便捷性，我们接着就来上手体验一下。

快速上手

跟之前一样，还是添加依赖：

<dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>3.5.3.1</version>
</dependency>
<dependency>
     <groupId>com.mysql</groupId>
     <artifactId>mysql-connector-j</artifactId>
</dependency>

配置文件依然只需要配置数据源即可：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

然后依然是实体类，可以直接映射到数据库中的表：

@Data
@TableName("user")  //对应的表名
public class User {
    @TableId(type = IdType.AUTO)   //对应的主键
    int id;
    @TableField("name")   //对应的字段
    String name;
    @TableField("email")
    String email;
    @TableField("password")
    String password;
}

接着，我们就可以编写一个Mapper来操作了：

@Mapper
public interface UserMapper extends BaseMapper<User> {
    //使用方式与JPA极其相似，同样是继承一个基础的模版Mapper
    //这个模版里面提供了预设的大量方法直接使用，跟JPA如出一辙
}

这里我们就来写一个简单测试用例：

@SpringBootTest
class DemoApplicationTests {

    @Resource
    UserMapper mapper;

    @Test
    void contextLoads() {
        System.out.println(mapper.selectById(1));  //同样可以直接selectById，非常快速方便
    }
}

可以看到这个Mapper提供的方法还是很丰富的：

后续的板块我们将详细介绍它的使用方式。

出现这种错误 springboot3.2.3会报错，换成3.1.6 尝试

啊啊啊啊啊啊啊 这大半夜的 我想睡觉来着

条件构造器

对于一些复杂查询的情况，MybatisPlus支持我们自己构造QueryWrapper用于复杂条件查询：

@Test
void contextLoads() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();    //复杂查询可以使用QueryWrapper来完成
    wrapper
            .select("id", "name", "email", "password")    //可以自定义选择哪些字段
            .ge("id", 2)                //选择判断id大于等于1的所有数据
            .orderByDesc("id");   //根据id字段进行降序排序
    System.out.println(mapper.selectList(wrapper));   //Mapper同样支持使用QueryWrapper进行查询
}

通过使用上面的QueryWrapper对象进行查询，也就等价于下面的SQL语句：

select id,name,email,password from user where id >= 2 order by id desc

我们可以在配置中开启SQL日志打印：

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

最后得到的结果如下：

有些时候我们遇到需要批处理的情况，也可以直接使用批处理操作：

@Test
void contextLoads() {
    //支持批处理操作，我们可以一次性删除多个指定ID的用户
    int count = mapper.deleteBatchIds(List.of(1, 3));
    System.out.println(count);
}

我们也可以快速进行分页查询操作，不过在执行前我们需要先配置一下：

@Configuration
public class MybatisConfiguration {
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //添加分页拦截器到MybatisPlusInterceptor中
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

这样我们就可以愉快地使用分页功能了：

@Test
void contextLoads() {
    //这里我们将用户表分2页，并获取第一页的数据
    Page<User> page = mapper.selectPage(Page.of(1, 2), Wrappers.emptyWrapper());
    System.out.println(page.getRecords());   //获取分页之后的数据
}

对于数据更新操作，我们也可以使用UpdateWrapper非常方便的来完成：

@Test
void contextLoads() {
    UpdateWrapper<User> wrapper = new UpdateWrapper<>();
    wrapper
            .set("name", "lbw")
            .eq("id", 1);
    System.out.println(mapper.update(null, wrapper));
}

这样就可以快速完成更新操作了：

QueryWrapper和UpdateWrapper还有专门支持Java 8新增的Lambda表达式的特殊实现，可以直接以函数式的形式进行编写，使用方法是一样的，这里简单演示几个：

@Test
void contextLoads() {
        LambdaQueryWrapper<User> wrapper = Wrappers
                .<User>lambdaQuery()
                .eq(User::getId, 2)   //比如我们需要选择id为2的用户，前面传入方法引用，后面比的值
                .select(User::getName, User::getId);   //比如我们只需要选择name和id，那就传入对应的get方法引用
        System.out.println(mapper.selectOne(wrapper));
}

不过感觉可读性似乎没有不用Lambda高啊。

接口基本操作

虽然使用MybatisPlus提供的BaseMapper已经很方便了，但是我们的业务中，实际上很多时候也是一样的工作，都是去简单调用底层的Mapper做一个很简单的事情，那么能不能干脆把Service也给弄个模版？MybatisPlus为我们提供了很方便的CRUD接口，直接实现了各种业务中会用到的增删改查操作。

我们只需要继承即可：

@Service
public interface UserService extends IService<User> {
    //除了继承模版，我们也可以把它当成普通Service添加自己需要的方法
}

接着我们还需要编写一个实现类，这个实现类就是UserService的实现：

@Service   //需要继承ServiceImpl才能实现那些默认的CRUD方法
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}

使用起来也很方便，整合了超多方法：

比如我们想批量插入一组用户数据到数据库中：

@Test
void contextLoads() {
    List<User> users = List.of(new User("xxx"), new User("yyy"));
    //预设方法中已经支持批量保存了，这相比我们直接用for效率高不少
    service.saveBatch(users);
}

还有更加方便快捷的保存或更新操作，当数据不存在时（通过主键ID判断）则插入新数据，否则就更新数据：

@Test
void contextLoads() {
    service.saveOrUpdate(new User("aaa"));
}

我们也可以直接使用Service来进行链式查询，写法非常舒服：

@Test
void contextLoads() {
    User one = service.query().eq("id", 1).one();
    System.out.println(one);
}

新版代码生成器

最后我们再来隆重介绍一下MybatisPlus的代码生成器，这个东西可谓是拯救了千千万万学子的毕设啊。

它能够根据数据库做到代码的一键生成，能做到什么程度呢？

你没看错，整个项目从Mapper到Controller，所有的东西全部都给你生成好了，你只管把需要补充的业务给写了就行，这是真正的把饭给喂到你嘴边的行为，是广大学子的毕设大杀器。

那么我们就来看看，这玩意怎么去用的，首先我们需要先把整个项目的数据库给创建好，创建好之后，我们继续下一步，这里我们从头开始创建一个项目，感受一下它的强大，首先创建一个普通的SpringBoot项目：

接着我们导入一会需要用到的依赖：

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.3.1</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>

然后再配置一下数据源：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

接着我们就可以开始编写自动生成脚本了，这里依然选择测试类，用到FastAutoGenerator作为生成器：

        @Test
    void contextLoads() {
        FastAutoGenerator
                    //首先使用create来配置数据库链接信息
                .create(new DataSourceConfig.Builder(dataSource))
                .execute();
    }

接着我们配置一下全局设置，这些会影响一会生成的代码：

@Test
void contextLoads() {
    FastAutoGenerator
            .create(new DataSourceConfig.Builder(dataSource))
            .globalConfig(builder -> {
                builder.author("lbw");              //作者信息，一会会变成注释
                builder.commentDate("2024-01-01");  //日期信息，一会会变成注释
                builder.outputDir("src/main/java"); //输出目录设置为当前项目的目录
            })
            .execute();
}

然后是打包设置，也就是项目的生成的包等等，这里简单配置一下：

@Test
void contextLoads() {
    FastAutoGenerator
            ...
                //打包设置，这里设置一下包名就行，注意跟我们项目包名设置为一致的
                .packageConfig(builder -> builder.parent("com.example"))
                .strategyConfig(builder -> {
                    //设置为所有Mapper添加@Mapper注解
                    builder
                            .mapperBuilder()
                            .mapperAnnotation(Mapper.class)
                            .build();
            })
            .execute();
}

接着我们就可以直接执行了这个脚本了：

现在，可以看到我们的项目中已经出现自动生成代码了：

我们也可以直接运行这个项目：

速度可以说是非常之快，一个项目模版就搭建完成了，我们只需要接着写业务就可以了，当然如果各位小伙伴需要更多定制化的话，可以在官网查看其他的配置：https://baomidou.com/pages/981406/

对于一些有特殊要求的用户来说，我们希望能够以自己的模版来进行生产，怎么才能修改它自动生成的代码模版呢，我们可以直接找到mybatis-plus-generator的源码：

生成模版都在在这个里面有写，我们要做的就是去修改这些模版，变成我们自己希望的样子，由于默认的模版解析引擎为Velocity，我们需要复制以.vm结尾的文件到resource随便一个目录中，然后随便改：

接着我们配置一下模版：

@Test
void contextLoads() {
    FastAutoGenerator
            ...
                .strategyConfig(builder -> {
                builder
                        .mapperBuilder()
                        .enableFileOverride()   //开启文件重写，自动覆盖新的
                        .mapperAnnotation(Mapper.class)
                        .build();
            })
            .templateConfig(builder -> {
                builder.mapper("/template/mapper.java.vm");
            })
            .execute();
}

这样，新生成的代码中就是按照我们自己的模版来定义了:

有了代码生成器，我们工 (划) 作 (水) 效率更上一层楼啦~

Redis交互

Elasticsearch交互