In Chapter 2, we showed you how to implement the HTTP interface through Spring Boot, and the practical skills surrounding unit testing, document generation, and so on. However, this is not enough to help us build a dynamic application server. Whether we’re building apps, applets, or traditional Web sites, we usually need to store user information, business-related content, rather than store user information in memory as in Chapter 2 (reboot and lose it!). .

For information storage, there are now many products to choose from, including many excellent open source free products, such as MySQL, Redis and so on. Next, in Chapter 3, we will continue to learn how to add, delete, change and query popular data storage products while developing server-side programs using Spring Boot.

As the first data access chapter, we’ll start with the most commonly used relational databases. With a simple example, learn about the most basic data access tool in Spring Boot: JdbcTemplate.

Data Source Configuration

When we access the database, we need to configure a data source first. Here are several different database configuration methods.

First, JDBC support needs to be introduced in order to connect to the database. The following configuration is introduced in POM.xml:

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

Embedded database support

Embedded databases are typically used in development and test environments and are not recommended for production environments. Spring Boot provides automatic configuration for embedded databases such as H2, HSQL, and Derby, and you don’t need to provide any connection configuration to use it.

For example, we could introduce the following configuration using HSQL in pom.xml

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>runtime</scope>
</dependency>Copy the code

Connecting production data sources

MySQL database for example, first introduce MySQL connection dependency package, pom. XML add:

< the dependency > < groupId > mysql < / groupId > < artifactId > mysql connector - Java < / artifactId > < version > 5.1.46 < / version > </dependency>Copy the code

In the SRC/main/resources/application. The properties in the configuration data source information

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.DriverCopy the code

Connect to the JNDI data source

When you deploy your application to an application server and want the data source to be managed by the application server, you can import the JNDI data source using the following configuration.

spring.datasource.jndi-name=java:jboss/datasources/customersCopy the code

Use the JdbcTemplate to manipulate the database

Spring’s JdbcTemplate is automatically configured, and you can inject it directly into your own bean using @AutoWired or constructors (recommended).

Add, delete, change, check

Preparing the database

Create table User with attributes name and age. This can be done by executing the following statement:

CREATE TABLE `User` (
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciCopy the code

Writing domain objects

Create a domain object from the User table created in the database:

@Data
@NoArgsConstructor
public class User {

    private String name;
    private Integer age;

}Copy the code

Lombok’s @data and @Noargsconstructor annotations are used to automatically generate Set and Get functions for each argument, as well as no-argument constructors. If you’re not familiar with Lombok, check out this article: The Use and Principles of Lombok, a Java development artifact.

Write data access objects

  • Define the abstract interface UserService that contains insert, delete, and query
Public interface UserService {/** * add a user ** @param name * @param age */ int create(String name, Integer age); ** @param name * @return */ List<User> getByName(String name); ** @param */ int deleteByName(String name); /** * get total users */ int getAllUsers(); /** * deleteAllUsers */ int deleteAllUsers(); }Copy the code

  • throughJdbcTemplateimplementationUserServiceData access operations defined in
@Service public class UserServiceImpl implements UserService { private JdbcTemplate jdbcTemplate; UserServiceImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public int create(String name, Integer age) { return jdbcTemplate.update("insert into USER(NAME, AGE) values(? ,?) ", name, age); } @Override public List<User> getByName(String name) { List<User> users = jdbcTemplate.query("select NAME, AGE from USER where NAME = ?" , (resultSet, i) -> { User user = new User(); user.setName(resultSet.getString("NAME")); user.setAge(resultSet.getInt("AGE")); return user; }, name); return users; } @Override public int deleteByName(String name) { return jdbcTemplate.update("delete from USER where NAME = ?" , name); } @Override public int getAllUsers() { return jdbcTemplate.queryForObject("select count(1) from USER", Integer.class); } @Override public int deleteAllUsers() { return jdbcTemplate.update("delete from USER"); }}Copy the code

Write unit test cases

  • Create a unit test case for UserService to verify the correctness of the database operation by creating, deleting, and querying.
@RunWith(SpringRunner.class) @SpringBootTest public class Chapter31ApplicationTests { @Autowired private UserService userSerivce; @ Before public void setUp () {/ / preparation, the empty user table userSerivce. DeleteAllUsers (); } @test public void Test () throws Exception {// Insert 5 users userSerivce.create("Tom", 10); userSerivce.create("Mike", 11); userSerivce.create("Didispace", 30); userSerivce.create("Oscar", 21); userSerivce.create("Linda", 17); List<User> userList = userServce.getByName ("Oscar"); Assert.assertEquals(21, userList.get(0).getAge().intValue()); AssertEquals (5, userSerivce.getallUsers ()); assert.assertequals (5, userSerivce.getallUsers ()); // Delete two users userSerivce.deleteByName("Tom"); userSerivce.deleteByName("Mike"); AssertEquals (3, userSerivce.getallUsers ()); assert.assertequals (3, userSerivce.getallUsers ()); }}Copy the code

The JdbcTemplate operations described above are just a few of the most basic operations. See the JdbcTemplate API for more data access operations

With this simple example above, we can see that the configuration of accessing the database under Spring Boot remains the same as the framework was intended to be: simple. Instead of creating the JdbcTemplate Bean in the Spring application, we can inject it directly into our own objects.

Code sample

For an example of this article, see the chapter3-1 directory in the repository below:

  • Github:github.com/dyc87112/Sp…
  • Gitee:gitee.com/didispace/S…

If you think this article is good, welcome Star support, your attention is my motivation!

Welcome to pay attention to my official account: Program ape DD, for exclusive learning resources and daily dry goods push.

If you are interested in my feature content, you can also follow my blog: didispace.com