In previous posts in this series, we have never explained how to manipulate a database, but in practice, almost all systems are dependent on data persistence, so it is very important to understand how to use an operational database.
There are many ways to manipulate a database in Spring. You can use JDBC, Hibernate, MyBatis, or any other data persistence framework. This blog will focus on how to manipulate a database in Spring through JDBC.
1. Project construction failure solved
Before we get into JDBC, let’s resolve a problem because a normally built application reported the following error when repacking:
We checked the Spring dependency in the pop.xml file:
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> < version > 4.3.18. RELEASE < / version > < / dependency > < the dependency > < groupId > org. Springframework < / groupId > The < artifactId > spring - webmvc < / artifactId > < version > 4.3.18. RELEASE < / version > < / dependency > <! -- Spring aop support --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring aop</artifactId> < version > 5.1.8. RELEASE < / version > < / dependency > < the dependency > < groupId > org. Springframework < / groupId > < artifactId > spring - test < / artifactId > < version > 4.3.18. RELEASE < / version > < scope > test < / scope > < / dependency >Copy the codeWhere, the version of spring-AOP is 5.1.8.RELEASE, and the version of the other three packages is 4.3.18.RELEASE, change the version of spring-aop to 4.3.18.RELEASE:
<! -- Spring aop support --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring aop</artifactId> < version > 4.3.18. RELEASE < / version > < / dependency >Copy the codeAt this point, rebuild the package, no error, package success:
However, the above dependency can be reduced to the following:
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.18. RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.3.18. RELEASE</version>
<scope>test</scope>
</dependency>
Copy the codeSince the spring-WebMVC package already includes spring-context and spring-AOP, there is no need to add these two dependencies repeatedly:
2. Configure the data source
MySql > create spring_action_db;
CREATE DATABASE spring_action_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Copy the codeCreate table book:
use spring_action_db; Create table Book (book_id bigint auto_increment COMMENT 'bookid ', book_name varchar(50) not null comment' bookid ', Author varchar(50) not null comment 'author ', create_by varchar(20) not null comment' creator ', Create_time datetime not NULL comment 'create time ', modify_by varchar(20) not null comment' create time ', Modify_time datetime not NULL comment 'Update ', column Book_pk primary key (book_id) comment' update ';Copy the codeWhen ready, create a data source for the configuration class configuration:
package chapter10.config;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
@Configuration
@ComponentScan("chapter10")
public class DataSourceConfig {
@Bean
public BasicDataSource dataSource(a) {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/spring_action_db");
dataSource.setUsername("root");
dataSource.setPassword("root");
returndataSource; }}Copy the codeBecause we are using a MySql database, the Driver name is set to com.mysql.jdbc.driver.
If you are using a different type of database, change the name to the corresponding one.
Since we are using the MySql driver, we need to add the following dependencies to pom.xml, otherwise we will not get connections when accessing the database:
<! -- MySql driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
Copy the code3. Use the original JDBC code
First, create a new database entity class Book:
package chapter10.domain;
import java.util.Date;
public class Book {
private Long bookId;
private String bookName;
private String author;
private String createBy;
private Date createTime;
private String modifyBy;
private Date modifyTime;
public Book(String bookName, String author, String createBy) {
this.bookName = bookName;
this.author = author;
this.createBy = createBy;
this.createTime = new Date();
this.modifyBy=createBy;
this.modifyTime=new Date();
}
public Book(Long bookId, String bookName, String author, String modifyBy) {
this.bookId = bookId;
this.bookName = bookName;
this.author = author;
this.modifyBy = modifyBy;
}
public Book(a) {}// omit the get and set methods
}
Copy the codeThen define the data access interface BookRepository and just add the addBook method for now:
package chapter10.db;
import chapter10.domain.Book;
public interface BookRepository {
void addBook(Book book);
}
Copy the code3.1 Adding Data
Create a new data access implementation class JdbcBookRepository as follows:
package chapter10.db.jdbc;
import chapter10.db.BookRepository;
import chapter10.domain.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
@Repository
public class JdbcBookRepository implements BookRepository {
private static final String SQL_INSERT_BOOK =
"INSERT INTO book(book_name, author, create_by, create_time, modify_by, modify_time) VALUES (? ,? ,? ,? ,? ,?) ;";
@Autowired
private DataSource dataSource;
@Override
public void addBook(Book book) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(SQL_INSERT_BOOK);
preparedStatement.setString(1, book.getBookName());
preparedStatement.setString(2, book.getAuthor());
preparedStatement.setString(3, book.getCreateBy());
preparedStatement.setTimestamp(4.new Timestamp(calendar.getTimeInMillis()));
preparedStatement.setString(5, book.getModifyBy());
preparedStatement.setTimestamp(6.new Timestamp(calendar.getTimeInMillis()));
preparedStatement.execute();
} catch (SQLException e) {
// Exception handling code
} finally {
try {
if(preparedStatement ! =null) {
preparedStatement.close();
}
if(connection ! =null) { connection.close(); }}catch (SQLException e) {
// Exception handling code}}}}Copy the codeNote: The @repository annotation is added to this class so that Spring can scan it to register it as a bean.
It is worth noting that we caught SQLException twice in this code because connection = datasource.getConnection (); , a preparedStatement. The execute (); , a preparedStatement. Close (); , the connection. The close (); Throws a checkable exception SQLException, so it must be caught in the method or the compiler will fail:
Connection getConnection(a) throws SQLException;
boolean execute(a) throws SQLException;
void close(a) throws SQLException;
void close(a) throws SQLException;
Copy the codeFinally, create a new unit test class, BookRepositoryTest, as follows:
package chapter10;
import chapter10.config.DataSourceConfig;
import chapter10.db.BookRepository;
import chapter10.domain.Book;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = DataSourceConfig.class)
public class BookRepositoryTest {
@Autowired
private BookRepository bookRepository;
@Test
public void testAddBook(a) {
Book book = new Book("Spring In Action (4th edition)"."Craig Walls"."Stranger from Shencheng");
bookRepository.addBook(book);
book = new Book("Java EE Disruptors: Spring Boot in Action"."Wang Yunfei"."Stranger from Shencheng");
bookRepository.addBook(book);
book = new Book("RabbitMQ Field Guide"."Zhu Zhonghua"."Stranger from Shencheng"); bookRepository.addBook(book); }}Copy the codeRun testAddBook() and add data to the database successfully:
3.2 Updating Data
First, add the update method to the data access interface BookRepository:
void updateBook(Book book);
Copy the codeThe method is then implemented in the data access implementation class JdbcBookRepository:
private static final String SQL_UPDATE_BOOK =
"UPDATE Book SET book_name = ? ,author = ? ,modify_by = ? ,modify_time=? WHERE book_id = ? ;";
@Override
public void updateBook(Book book) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(SQL_UPDATE_BOOK);
preparedStatement.setString(1, book.getBookName());
preparedStatement.setString(2, book.getAuthor());
preparedStatement.setString(3, book.getModifyBy());
preparedStatement.setTimestamp(4.new Timestamp(calendar.getTimeInMillis()));
preparedStatement.setLong(5, book.getBookId());
preparedStatement.execute();
} catch (SQLException e) {
// Exception handling code
} finally {
try {
if(preparedStatement ! =null) {
preparedStatement.close();
}
if(connection ! =null) { connection.close(); }}catch (SQLException e) {
// Exception handling code}}}Copy the codeThe code is almost the same as the previous new code, and I had to catch the check exception SQLException twice.
Finally, add the test method testUpdateBook to the test class BookRepositoryTest, as shown below:
@Test
public void testUpdateBook(a) {
Book book = new Book(1L."Spring In Action (4th edition)"."Craig Walls"."zwwhnly");
bookRepository.updateBook(book);
book = new Book(2L."Java EE Disruptors: Spring Boot in Action"."Wang Yunfei"."zwwhnly");
bookRepository.updateBook(book);
book = new Book(3L."RabbitMQ Field Guide"."Zhu Zhonghua"."zwwhnly");
bookRepository.updateBook(book);
}
Copy the codeAfter executing the test method, the data is updated successfully:
3.3 Searching for Data
First, add the update method to the data access interface BookRepository:
Book findBook(long bookId);
Copy the codeThe method is then implemented in the data access implementation class JdbcBookRepository:
private static final String SQL_SELECT_BOOK =
"SELECT book_id,book_name,author,create_by,create_time,modify_by,modify_time FROM book WHERE book_id = ? ;";
@Override
public Book findBook(long bookId) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Book book = null;
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(SQL_SELECT_BOOK);
preparedStatement.setLong(1, bookId);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
book = new Book();
book.setBookId(resultSet.getLong("book_id"));
book.setBookName(resultSet.getString("book_name"));
book.setAuthor(resultSet.getString("author"));
book.setCreateBy(resultSet.getString("create_by"));
book.setCreateTime(resultSet.getTimestamp("create_time"));
book.setModifyBy(resultSet.getString("modify_by"));
book.setModifyTime(resultSet.getTimestamp("modify_time")); }}catch (SQLException e) {
// Exception handling code
} finally {
try {
if(resultSet ! =null) {
resultSet.close();
}
if(preparedStatement ! =null) {
preparedStatement.close();
}
if(connection ! =null) { connection.close(); }}catch (SQLException e) {
// Exception handling code}}return book;
}
Copy the codeI found that the code was mostly the same as the previous new and updated code, and I also had to catch the check type exception SQLException twice.
Finally, add the test method testFindBook to the test class BookRepositoryTest, as shown below:
@Test
public void testFindBook(a) {
Book book = bookRepository.findBook(1L);
Assert.assertNotNull(book);
Assert.assertEquals(book.getBookName(), "Spring In Action (4th edition)");
}
Copy the codeWhen the test method is executed, the data query succeeds:
4. Use the JDBC template
Since most of the code is boilerplate code, and only a small portion of it is relevant to business logic, the good news is that Spring has already done this for us. Spring abstracts the data access boilerplate code into template classes. We can use template classes directly, simplifying JDBC code.
4.1 Adding Data
First, add the following configuration to the configuration class DataSourceConfig:
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
Copy the codeThen remove the @Repository annotation from the newly created JdbcBookRepository class.
Then, a new data access implementation class JdbcTemplateBookRepository as shown below:
package chapter10.db.jdbc;
import chapter10.db.BookRepository;
import chapter10.domain.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.stereotype.Repository;
import java.sql.Date;
@Repository
public class JdbcTemplateBookRepository implements BookRepository {
private static final String SQL_INSERT_BOOK =
"INSERT INTO book(book_name, author, create_by, create_time, modify_by, modify_time) VALUES (? ,? ,? ,? ,? ,?) ;";
@Autowired
private JdbcOperations jdbcOperations;
@Override
public void addBook(Book book) {
jdbcOperations.update(SQL_INSERT_BOOK, book.getBookName(),
book.getAuthor(),
book.getCreateBy(),
new Date(System.currentTimeMillis()),
book.getModifyBy(),
newDate(System.currentTimeMillis())); }}Copy the codeNote: The @repository annotation is added to this class so that Spring can scan it to register it as a bean.
Very simple there is no, from the previous code optimization to the current code, code cleanliness of the students estimate happy dead.
Because before the test class BookRepositoryTest, we injected is the interface, so we don’t need to modify the test class code, can direct access to the realization of the new JdbcTemplateBookRepository class methods:
@Autowired
private BookRepository bookRepository;
Copy the codeRun testAddBook() and the data is successfully added to the database:
4.2 Updating Data
In the data access implementation class JdbcTemplateBookRepository to add the following code:
private static final String SQL_UPDATE_BOOK =
"UPDATE Book SET book_name = ? ,author = ? ,modify_by = ? ,modify_time=? WHERE book_id = ? ;";
@Override
public void updateBook(Book book) {
jdbcOperations.update(SQL_UPDATE_BOOK, book.getBookName(),
book.getAuthor(),
book.getModifyBy(),
new Timestamp(System.currentTimeMillis()),
book.getBookId());
}
Copy the codeThen simply modify the previous test method testUpdateBook() :
@Test
public void testUpdateBook(a) {
Book book = new Book(4L."Spring In Action (4th edition)"."Craig Walls"."zwwhnly");
bookRepository.updateBook(book);
book = new Book(5L."Java EE Disruptors: Spring Boot in Action"."Wang Yunfei"."zwwhnly");
bookRepository.updateBook(book);
book = new Book(6L."RabbitMQ Field Guide"."Zhu Zhonghua"."zwwhnly");
bookRepository.updateBook(book);
}
Copy the codeRun the previous test method testUpdateBook() and the data is updated successfully:
4.3 Searching For Data
In the data access implementation class JdbcTemplateBookRepository to add the following code:
private static final String SQL_SELECT_BOOK =
"SELECT book_id,book_name,author,create_by,create_time,modify_by,modify_time FROM book WHERE book_id = ? ;";
@Override
public Book findBook(long bookId) {
return jdbcOperations.queryForObject(SQL_SELECT_BOOK, new BookRowMapper(), bookId);
}
private static final class BookRowMapper implements RowMapper<Book> {
@Override
public Book mapRow(ResultSet resultSet, int i) throws SQLException {
Book book = new Book();
book.setBookId(resultSet.getLong("book_id"));
book.setBookName(resultSet.getString("book_name"));
book.setAuthor(resultSet.getString("author"));
book.setCreateBy(resultSet.getString("create_by"));
book.setCreateTime(resultSet.getTimestamp("create_time"));
book.setModifyBy(resultSet.getString("modify_by"));
book.setModifyTime(resultSet.getTimestamp("modify_time"));
returnbook; }}Copy the codeTestFindBook ();
5. Source code and reference
Source code address: github.com/zwwhnly/spr… Welcome to download.
Craig Walls: Spring In Action (4th Edition)