In previous blogs in this series, we have never explained how to operate databases, but in practical work, almost all systems are inseparable from data persistence, so it is very important to master how to operate databases.
There are many ways to operate databases in Spring. We can use JDBC, Hibernate, MyBatis or other data persistence frameworks. The focus of this blog is to explain how to operate databases through JDBC in Spring.
1. Failure resolution of project construction
Before explaining JDBC, let's first solve a problem, because the normal program that was built originally reported the following error when rebuilding the packages: JDBC, JDBC, JDBC, JDBC, JDBC, JDBC, JDBC, JDBC, JDBC, JDBC and JDBC.
After searching for information on the Internet, it was said that there was a conflict between the dependent versions, so it checked the dependencies of Spring added before in pom.xml:
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<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>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.3.18.RELEASE</version>
<scope>test</scope>
</dependency>The spring-aop version is 5.1.8.RELEASE, while the other three packages are 4.3.18.RELEASE. The spring-aop version is also modified to 4.3.18.RELEASE:
<!--spring aop Support-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>At this point, rebuild the package, no more errors, and the package succeeds:
However, the above dependencies can also be simplified as follows:
<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>Because the spring-webmvc package already contains spring-context and spring-aop, it is not necessary to add these two dependencies again:
2. Configuring data sources
First, execute the following statement to create MySql database spring_action_db:
CREATE DATABASE spring_action_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Then execute the following statement to create the table book:
use spring_action_db;
create table Book
(
book_id bigint auto_increment comment 'book id',
book_name varchar(50) not null comment 'Title',
author varchar(50) not null comment 'author',
create_by varchar(20) not null comment 'Founder',
create_time datetime not null comment 'Creation time',
modify_by varchar(20) not null comment 'Modifier',
modify_time datetime not null comment 'Modification time',
constraint Book_pk
primary key (book_id)
)
comment 'book';When ready, the new configuration class configures the data source:
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() {
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");
return dataSource;
}
}Because we are using MySql database, the driver name is set to com.mysql.jdbc.Driver.
If you are using another type of database, you need to change it to the corresponding name.
Because MySql driver is used, we need to add the following dependencies in pom.xml, otherwise we will not get a connection when accessing the database:
<!-- MySql drive -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>3. 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() {
}
// Omitting get and set methods
}Then the data access interface BookRepository is defined, and only addBook method is added for the time being:
package chapter10.db;
import chapter10.domain.Book;
public interface BookRepository {
void addBook(Book book);
}3.1 Additional data
The new data access implementation class JdbcBook Repository is 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 related code
} finally {
try {
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// Exception handling related code
}
}
}
}Note: This class adds the @Repository annotation so that Spring can scan to register it as a bean.
It's worth noting that in this code, we caught SQLException twice because connection = dataSource.getConnection();, preparedStatement.execute();, preparedStatement.close();, connection.close(); all throw checked exception SQLException, so the method must be caught, otherwise the compilation will not pass:
Connection getConnection() throws SQLException; boolean execute() throws SQLException; void close() throws SQLException; void close() throws SQLException;
Finally, a new unit test class, BookRepository Test, is created 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() {
Book book = new Book("Spring Actual Warfare (4th Edition)", "Craig Walls", "Shencheng Foreigners");
bookRepository.addBook(book);
book = new Book("Java EE The subverters of development: Spring Boot actual combat", "Yun Fei Wang", "Shencheng Foreigners");
bookRepository.addBook(book);
book = new Book("RabbitMQ Practical Study", "Zhu Zhong Hua", "Shencheng Foreigners");
bookRepository.addBook(book);
}
}Running test method testAddBook(), the data was successfully added to the database:
3.2 Update data
First, an update method is added to the data access interface BookRepository:
void updateBook(Book book);
Then the method is implemented in the data access implementation class JdbcBook Repository:
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 related code
} finally {
try {
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// Exception handling related code
}
}
}Is it found that its code is almost the same as the new code before, but also had to check the exception SQLException captured twice, code cleanliness people can not help but want to refactor, haha.
Finally, the test method testUpdateBook is added to the test class BookRepository Test, as follows:
@Test
public void testUpdateBook() {
Book book = new Book(1L, "Spring Actual Warfare (4th Edition)", "Craig Walls", "zwwhnly");
bookRepository.updateBook(book);
book = new Book(2L, "Java EE The subverters of development: Spring Boot actual combat", "Yun Fei Wang", "zwwhnly");
bookRepository.updateBook(book);
book = new Book(3L, "RabbitMQ Practical Study", "Zhu Zhong Hua", "zwwhnly");
bookRepository.updateBook(book);
}The test method was implemented and the data was updated successfully:
3.3 Find Data
First, an update method is added to the data access interface BookRepository:
Book findBook(long bookId);
Then the method is implemented in the data access implementation class JdbcBook Repository:
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 related code
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// Exception handling related code
}
}
return book;
}Did you find that most of its code is the same as the new and updated code before, and you have to capture the checked exception SQLException twice? Has anyone with code cleanliness started refactoring, haha?
Finally, test FindBook is added to the test class BookRepository Test, as follows:
@Test
public void testFindBook() {
Book book = bookRepository.findBook(1L);
Assert.assertNotNull(book);
Assert.assertEquals(book.getBookName(), "Spring Actual Warfare (4th Edition)");
}The test method is implemented and the data query is successful:
4. Use JDBC templates
After using the original JDBC to operate the database, many students with code cleanliness can not help but start to rebuild, because most of the code is boilerplate code, only a few of which are related to business logic. The good news is that Spring has helped us rebuild it. Spring abstracts the template code of data access into the template class, and we can use the template class directly, thus simplifying it. JDBC code.
4.1 Additional data
First, add the following configuration to the configuration class DataSourceConfig:
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}Then remove the @Repository annotation from the newly created class JdbcBook Repository.
Next, a new data access implementation class, JdbcTemplateBook Repository, is created as follows:
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(),
new Date(System.currentTimeMillis()));
}
}Note: This class adds the @Repository annotation so that Spring can scan to register it as a bean.
Is it very concise? From the previous code optimization to the present code, the students who have code cleanliness are estimated to be happy to die.
Because in the previous test class BookRepository Test, we injected the interface, so we can directly access the implementation method of the new JdbcTemplateBookRepository class without modifying the code of the test class:
@Autowired private BookRepository bookRepository;
Before running the test method testAddBook(), the data was successfully added to the database:
4.2 Update data
Add the following code to the data access implementation class JdbcTemplateBook Repository:
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());
}Then simply modify the previous test method testUpdateBook():
@Test
public void testUpdateBook() {
Book book = new Book(4L, "Spring Actual Warfare (4th Edition)", "Craig Walls", "zwwhnly");
bookRepository.updateBook(book);
book = new Book(5L, "Java EE The subverters of development: Spring Boot actual combat", "Yun Fei Wang", "zwwhnly");
bookRepository.updateBook(book);
book = new Book(6L, "RabbitMQ Practical Study", "Zhu Zhong Hua", "zwwhnly");
bookRepository.updateBook(book);
}The test method testUpdateBook() before running, the data was updated successfully:
4.3 Find Data
Add the following code to the data access implementation class JdbcTemplateBook Repository:
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"));
return book;
}
}The test method testFindBook() before running succeeded in data query:
5. Source Code and Reference
Source address: https://github.com/zwwhnly/spring-action.git Welcome to download.
Craig Walls, Spring Actual Warfare (4th Edition)
6. last
Welcome to pay close attention to Wechat's public number: "Shencheng Foreigners" and regularly share the dried products of Java technology, so that we can make progress together.
