Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to use JDBC to operate the database in SpringBoot. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

A brief introduction to JDBC

JDBC (Java Data Base Connectivity,Java database connection), to put it bluntly, JDBC is a set of API specifications for Java to access databases. Spring Boot supports the mainstream ORM frameworks: MyBatis, Hibernate and Spring JDBC. Several ORM have their own advantages in different scenarios, and there are corresponding Starter packages in the Spring Boot system to facilitate integration.

The steps of using JDBC directly in Java are complicated in price comparison, and need to be completed in 7 steps:

Try {/ / 1, load database driver Class.forName (driver); / / 2, get database connection conn = DriverManager.getConnection (url, username, password); / / 3, get database operation object stmt = conn.createStatement (); / / 4, define SQL statement String sql = "select * from user where id = 6"; / / 5, execute database operation rs = stmt.executeQuery (sql) / / 6. Get and operate the result set while (rs.next ()) {/ / parse the result set}} catch (Exception e) {/ / log information} finally {/ / 7, close the resource}

From the above example, we can see that it is complicated to use JDBC to operate the database directly, so many famous ORM frameworks have been developed on the basis of JDBC, of which Hibernate, MyBatis and Spring JDBC are the most popular. Here we mainly learn about the use of Spring JDBC in Spring Boot.

II. Example of single data source operation

It is easy for Spring Boot to integrate JDBC, which requires the introduction of dependencies and basic configuration. Before developing a project, you need to create a table to be used as a project demonstration. Design a User user table with id, name, password, age and other fields. The corresponding SQL script is as follows:

DROP TABLE IF EXISTS `users`; CREATE TABLE `users` (`id` bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'primary key id', `name` varchar (32) DEFAULT NULL COMMENT' username', `password` varchar (32) DEFAULT NULL COMMENT 'password', `age`int DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Add configuration

Add dependency packages:

Org.springframework.boot spring-boot-starter-jdbc mysql mysql-connector-java

MySQL is used as the database in the demonstration project, so you need to introduce the MySQL driver package as well as spring-boot-starter-jdbc in the project. Open the pom.xml file and press the shortcut key: Ctrl + Alt + SHIFT + U, or right-click and select the Diagrams | Show Dependencies option to view the project dependency class diagram.

After popping up the Class Diagram dialog box, scroll the mouse to zoom in and find that spring-boot-starter-jdbc is directly dependent on HikariCP and spring-jdbc.

HikariCP is the default database connection pool used by Spring Boot 2.0, and it is also the fastest database connection pool in legend.

Spring-jdbc is a Spring toolkit that encapsulates the operation of JDBC.

Configuration of data sources

Spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=truespring.datasource.username=rootspring.datasource.password=rootspring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

It is worth noting that in Spring Boot 2.1.0, com.mysql.jdbc.Driver is out of date and com.mysql.cj.jdbc.Driver is recommended.

Entity class

Create the entity class corresponding to the table:

Public class User {private Long id; private String name; private String password; private int age; public User (String name, String password, int age) {this.name = name; this.password = password; this.age = age;} / / omit getter setter}

Encapsulated Repository

Create UserRepository to define the APIs we use to add, delete, modify and query.

Public interface UserRepository {int save (User user); int update (User user); int delete (long id); List findALL (); User findById (long id);}

Create a UserRepositoryImpl class to implement the UserRepository class interface; the @ Repository annotation is used on the class to annotate the data access component, while injecting JdbcTemplate into the class, which is a utility class provided by the Spring operation JDBC.

@ Repositorypublic class UserRepositoryImpl implements UserRepository {@ Autowired private JdbcTemplate jdbcTemplate;}

Next, encapsulate the method of saving user / updating user information / deleting user information / user id query / getting user list

@ Overridepublic int save (User user) {return jdbcTemplate.update ("INSERT INTO users (name, password, age) values", user.getName (), user.getPassword (), user.getAge ();} @ Overridepublic int update (User user) {return jdbcTemplate.update ("UPDATE users SET name =?, password =?, age =? WHERE id=? ", user.getName (), user.getPassword (), user.getAge (), user.getId ();} @ Overridepublic int delete (long id) {return jdbcTemplate.update (" DELETE FROM users where id=? ", id);} @ Overridepublic User findById (long id) {return jdbcTemplate.queryForObject (" SELECT * FROM users WHERE id=? ", new Object [] {id}, new BeanPropertyRowMapper (User.class)) } @ Overridepublic List findALL () {return jdbcTemplate.query ("SELECT * FROM users", new UserRowMapper ()); / / return jdbcTemplate.query ("SELECT * FROM users", new BeanPropertyRowMapper (User.class));}

Here, new BeanPropertyRowMapper (User.class) is used to encapsulate the returned data, which can automatically map a row of data to an instance of the specified class, first instantiate the class, and then map to the attribute by name matching.

FindALL () uses a new way to encapsulate the return of the result set, creating an inner class UserRowMapper. UserRowMapper inherits RowMapper,RowMapper to encapsulate each row of data into a user-defined class, implements the RowMapper interface to override the mapRow method, and encapsulates the return processing of the data in the mapRow method. From the above code, you can see that the UserRowMapper loop iterates through the result set returned by the query, and assigns values according to attributes at the same time. In this way, when the query is used, you only need to pass in new UserRowMapper () to automatically parse the returned data.

Class UserRowMapper implements RowMapper {@ Override public User mapRow (ResultSet rs, int rowNum) throws SQLException {User user = new User (); user.setId (rs.getLong ("id")); user.setName (rs.getString ("name")); user.setPassword (rs.getString ("password")); user.setAge (rs.getInt ("age"));}}

test

Next, we test the encapsulated UserRepository to test whether the methods in the UserRepository are correct. Create the UserRepositoryTests class and inject userRepository into the class.

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class UserRepositoryTests {@ Autowired private UserRepository userRepository;} / / Test insert data, directly call the corresponding save method @ Testpublic void testSave () {User user = new User ("neo", "123456", 30); userRepository.save (user);} / / test other methods as above. III. Use of multiple data sources

It is common to use multiple data sources in a project, and the use of multiple data sources in Spring Boot needs to be self-encapsulated. We modify it on the basis of the above example project.

Configuration file

Two data sources have been added, one is the test1 library and the other is the test2 library.

Spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=truespring.datasource.primary.username=rootspring.datasource.primary.password=rootspring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=truespring.datasource.secondary.username=rootspring.datasource.secondary.password=rootspring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

Initialize JDBC

Read the information in the configuration file when the project starts, and initialize the JDBC.

@ Configurationpublic class DataSourceConfig {@ Primary @ Bean (name = "primaryDataSource") @ Qualifier ("primaryDataSource") @ ConfigurationProperties (prefix= "spring.datasource.primary") public DataSource primaryDataSource () {return DataSourceBuilder.create () .build ();} @ Bean (name = "secondaryDataSource") @ Qualifier ("secondaryDataSource") @ ConfigurationProperties (prefix= "spring.datasource.secondary") public DataSource secondaryDataSource () {return DataSourceBuilder.create () .build () @ Bean (name= "primaryJdbcTemplate") public JdbcTemplate primaryJdbcTemplate (@ Qualifier ("primaryDataSource") DataSource dataSource) {return new JdbcTemplate (dataSource);} @ Bean (name= "secondaryJdbcTemplate") public JdbcTemplate secondaryJdbcTemplate (@ Qualifier ("secondaryDataSource") DataSource dataSource) {return new JdbcTemplate (dataSource);}}

This code means that different data sources are loaded according to a specific prefix at startup, and different JDBC is created based on the built data sources.

UserRepository transformation

We modify all the methods in UserRepository by adding a parameter to JdbcTemplate. If JdbcTemplate is transferred in the method, the passed JdbcTemplate will be used in the method. If the passed JdbcTemplate is empty, the default JdbcTemplate connection operation will be used.

@ Repositorypublic class UserRepositoryImpl implements UserRepository {@ Autowired private JdbcTemplate primaryJdbcTemplate; @ Override public int save (User user,JdbcTemplate jdbcTemplate) {if (jdbcTemplate= = null) {jdbcTemplate= primaryJdbcTemplate;} return jdbcTemplate.update ("INSERT INTO users (name, password, age) values (), user.getName (), user.getPassword (), user.getAge ()) } / / other methods are omitted. Please see the source code for details.

Multiple data source testing

JdbcTemplate from two different data sources is injected into the test class, along with UserRepository. The test uses different JdbcTemplate to insert two pieces of data to see if both databases are saved successfully. Before testing, create test1 and test2 databases, as well as user tables in both databases.

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class UserRepositoryTests {@ Autowired private UserRepository userRepository; @ Autowired private JdbcTemplate primaryJdbcTemplate; @ Autowired private JdbcTemplate secondaryJdbcTemplate; @ Testpublic void testSave () {User user = new User ("smile", "123456", 30); userRepository.save (user,primaryJdbcTemplate); userRepository.save (user,secondaryJdbcTemplate);}}

After the successful execution of testSave (), log in to the test1 and test 2 databases to view the user table, and there is a user message with name of smile, indicating that the data was inserted successfully by multiple data sources, and the tests of other methods are roughly the same. In this way, in the project, when we want to use which data source to operate the database, we only need to pass in the JdbcTemplate instance corresponding to the data source.

On how to use JDBC in SpringBoot to operate on the database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.