An overview of
JDBC is introduced
Starting with this article, we will introduce another core SpringBoot technology, namely database access technology. When it comes to data access, Java students will immediately think of JDBC technology. JDBC is the full name of Java Database Connectivity, is used in the Java language to standardize the client program how to access the Database application program interface, provides such as query and update Database data in a set of standard API, this standard between different Database manufacturers common adherence, And provide their specific implementation. As shown in the figure:
The advantage of this design is that Java programs only need to interact with the JDBC API, thus shielding the complex implementation of accessing the database and greatly reducing the complexity of Java programs accessing the database. For daily development, we only need to master a few core programming objects in the JDBC API specification, including DriverManger, Connection, Statement, and ResultSet.
DriverManager
DriverManager is responsible for loading Driver packages provided by different database vendors and returning Connection objects to Java programs according to different requests.
public interface Driver {
// Get the database connection
Connection connect(String url, java.util.Properties info)
throws SQLException;
boolean acceptsURL(String url) throws SQLException;
DriverPropertyInfo[] getPropertyInfo(String url, java.util.Properties info)
throws SQLException;
int getMajorVersion(a);
int getMinorVersion(a);
boolean jdbcCompliant(a);
public Logger getParentLogger(a) throws SQLFeatureNotSupportedException;
}
Copy the codeThe Driver has an important method, connect, that provides Connection objects
Different databases have specific implementations of Driver. Take MySql as an example:
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
// Register the Driver through DriverManager
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
…
}
Copy the codeDriverManager is used, which registers the drivers of different databases through registerDriver and provides getConnection to return database connection objects.
Connection
DriverManager can obtain the Connetion object, the Connection object can understand a database Connection (Session), a Connection object represents a database Connection, responsible for completing the communication with the database bottom layer.
Connection objects provide a set of overloaded methods for creating statements and preparedStatements, which are the vehicles for SQL execution, as well as transaction-related operations.
The core methods of a Connection object are as follows:
public interface Connection extends Wrapper.AutoCloseable {
/ / create the Statement
Statement createStatement(a) throws SQLException;
/ / create a PreparedStatement
PreparedStatement prepareStatement(String sql) throws SQLException;
/ / submit
void commit(a) throws SQLException;
/ / rollback
void rollback(a) throws SQLException;
// Close the connection
void close(a) throws SQLException;
}
Copy the codeStatement/PreparedStatement
Statement and PreparedStatement are created by Connection objects to execute static SQL statements and return generated result set objects. There are two types of Statement: plain Statement. Another PreparedStatement that supports precompilation.
The so-called precompilation means that the database compiler compiles SQL statements in advance, and then caches the precompiled results in the database. The next time you execute SQL statements, you can replace parameters and directly use the compiled statements, thus greatly improving the execution efficiency of SQL.
Take Statement as an example, and let’s take a look at the core methods of Statement:
public interface Statement extends Wrapper.AutoCloseable {
// Execute the query statement
ResultSet executeQuery(String sql) throws SQLException;
// Execute update statement
int executeUpdate(String sql) throws SQLException;
// Execute the SQL statement
boolean execute(String sql) throws SQLException;
// Perform batch processing
int[] executeBatch() throws SQLException;
}
Copy the codeResultSet
Execute the SQL Statement in Statement or PreparedStatement. We generate another object, a ResultSet object, which looks like this:
public interface ResultSet extends Wrapper.AutoCloseable {
// Get the next result
boolean next(a) throws SQLException;
// Get the result value of a certain type
Value getXXX(int columnIndex) throws SQLException; ... }Copy the codeThe ResultSet object provides a next() method that iterates through the entire ResultSet. If the next() method returns true, there is another record,
We can call a series of getXXX() methods on a ResultSet object to get the corresponding result value.
JDBC database access process
For developers, Java access to the database through JDBC API is the main way, the following code to show the overall process of accessing the database:
String url = "jdbc:mysql://localhost:3306/test" ;
String username = "root" ;
String password = "root" ;
//1. Obtain the connection from DriverManager
Connection connection = DriverManager.getConnection(url,username,password);
/ / 2. Create a preparedStatement
PreparedStatement preparedStatement = connection.prepareStatement("select * from user");
//3. Run SQL to return ResultSet
ResultSet resultSet = preparedStatement.executeQuery();
//4. Iterate through the resultSet
while (resultSet.next()){
//resultSet.getString("1");
}
//5. Release resources
resultSet.close();
preparedStatement.close();
connection.close();
Copy the codeConfiguring a Data Source
When introducing JDBC, the Connection object is obtained through DriverManager. The Connection object represents the Connection to the database. Obtaining the Connection object through DriverManager takes time and affects the system performance. Is there a way to reuse Connection objects? Yes
JDBC provides us with DataSource interface to achieve Connection reuse, the core code is as follows:
public interface DataSource extends CommonDataSource.Wrapper {
Connection getConnection(a) throws SQLException;
Connection getConnection(String username, String password)
throws SQLException;
}
Copy the codeAs a basic component, there is no need for developers to implement DataSource themselves, as there are already many excellent implementations in the industry, such as DBCP, C3P0, Druid, Hikari, etc
The default HikariDataSource is HikariDataSource. The default HikariDataSource is HikariDataSource. The default HikariDataSource is hikaridasource. SpringBoot is very simple to configure a DataSource, just add the DataSource configuration to the configuration file:
spring:
# datasource configures the datasource
datasource:
url: jdbc:mysql://localhost:3306/test? useSSL=false&useUnicode=true&characterEncoding=UTF-8
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
Copy the codeManipulate the database using JDBC
After DataSource configuration, we create a test database in the local database service, and execute the following DDL to create the user table
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`username` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Username',
`password` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'password',
`create_time` datetime DEFAULT NULL COMMENT 'Creation time'.PRIMARY KEY (`id`),
UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Copy the codeNext we create an entity class with attributes that correspond to the fields in the User table
@Data
public class User {
/** * primary key */
private Integer id;
/** * User name */
private String username;
/** * Password */
private String password;
/** * create time */
private Date createTime;
}
Copy the codeNote: Lombok’s @data annotation is used to generate get/set methods.
Let’s define a UserDao interface,
public interface UserDao {
/** * new *@param user
* @return* /
Integer insert(User user);
/** * query * by ID@param id
* @return* /
User selectById(Integer id);
/** * Update by ID *@param user
* @return* /
Integer updateById(User user);
/** * delete * based on ID@param id
* @return* /
Integer deleteById(Integer id);
}
Copy the codeThere are two reasons to pull out a UserDao layer here: First, UserDao only encapsulates the database operation of the USE table, and the code is easy to maintain and manage. Second, we can provide different implementations to access the database based on the UserDao interface. For example, we can provide implementation based on native JDBC, or we can use JDBCTemplate to achieve database access. You can also use Mybatis, etc
The following code shows how SpringBoot performs CRUD operations on the database through the JDBC API. UserRawJdbcDao implements the following methods:
The new data
@Override
public Integer insert(User user) {
final String SQL_INSERT = "INSERT INTO user(username, password, create_time) VALUES(? ,? ,?) ";
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
Integer count = 0;
try{
connection = dataSource.getConnection();
statement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
statement.setString(1,user.getUsername());
statement.setString(2,user.getPassword());
statement.setTimestamp(3.new Timestamp(user.getCreateTime().getTime()));
count = statement.executeUpdate();
rs = statement.getGeneratedKeys();
if(rs.next()){
user.setId(rs.getInt(1)); }}catch (SQLException e){
e.printStackTrace();
}finally {
try {
if(rs ! =null){
rs.close();
}
if(statement ! =null){
statement.close();
}
if(connection ! =null){ connection.close(); }}catch(SQLException e) { e.printStackTrace(); }}return count;
}
Copy the codeQuery data
@Override
public User selectById(Integer id) {
final String SQL_SELECT_ID = "SELECT id,username,password,create_time FROM user WHERE id = ?";
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
User user = null;
try{
connection = dataSource.getConnection();
statement = connection.prepareStatement(SQL_SELECT_ID);
statement.setInt(1, id);
rs = statement.executeQuery();
if(rs.next()){
user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCreateTime(rs.getTimestamp("create_time")); }}catch (SQLException e){
e.printStackTrace();
}finally {
try {
if(rs ! =null){
rs.close();
}
if(statement ! =null){
statement.close();
}
if(connection ! =null){ connection.close(); }}catch(SQLException e) { e.printStackTrace(); }}return user;
}
Copy the codeUpdate the data
@Override
public Integer updateById(User user) {
final String SQL_UPDATE = "UPDATE user SET username = ? , password = ? WHERE id = ?";
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
Integer count = 0;
try{
connection = dataSource.getConnection();
statement = connection.prepareStatement(SQL_UPDATE);
statement.setString(1,user.getUsername());
statement.setString(2,user.getPassword());
statement.setInt(3,user.getId());
count = statement.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}finally {
try {
if(rs ! =null){
rs.close();
}
if(statement ! =null){
statement.close();
}
if(connection ! =null){ connection.close(); }}catch(SQLException e) { e.printStackTrace(); }}return count;
}
Copy the codeDelete the data
@Override
public Integer deleteById(Integer id) {
final String SQL_DELETE = "DELETE FROM user WHERE id = ?";
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
Integer count = 0;
try{
connection = dataSource.getConnection();
statement = connection.prepareStatement(SQL_DELETE);
statement.setInt(1,id);
count = statement.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}finally {
try {
if(rs ! =null){
rs.close();
}
if(statement ! =null){
statement.close();
}
if(connection ! =null){ connection.close(); }}catch(SQLException e) { e.printStackTrace(); }}return count;
}
Copy the codeTo this point, SpringBoot calls the native JDBC API to complete the CRUD operation of the user table, this code for code cleanliness students simply can not bear, there are a lot of common code, such as creating a Connection, Statement, ResultSet, resource release and exception processing. This part of encapsulation and optimization has already been dealt with by SpringBoot, which provides the JdbcTemplate template utility class for data access, which simplifies the use of the JDBC API.
Manipulate the database using the JdbcTemplate
With UserRawJdbcDao, we define another set of implementation classes for UserDao named UserJdbcDao. This set of implementation classes complete operations on the database through JdbcTemplate. The method to complete interface definition is as follows:
The new data
@Override
public Integer insert(User user){
// Create a KeyHolder object and set the returned primary key ID
KeyHolder keyHolder = new GeneratedKeyHolder();
int count = jdbcTemplate.update(INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.newPreparedStatementCreator(
Arrays.asList(user.getUsername(),user.getPassword(),user.getCreateTime())),keyHolder);
// Set the ID primary key to the entity
if(keyHolder.getKey() ! =null) {
user.setId(keyHolder.getKey().intValue());
}
// Returns the number of affected rows
return count;
}
Copy the codeQuery data
@Override
public User selectById(Integer id){
User result = jdbcTemplate.queryForObject("SELECT id, username, password, create_time FROM user WHERE id=?".new BeanPropertyRowMapper<>(User.class), id);
return result;
}
Copy the codeUpdate the data
@Override
public Integer updateById(User user) {
return jdbcTemplate.update("UPDATE user SET username = ? , password = ? WHERE id = ?",
user.getUsername(),user.getPassword(),user.getId());
}
Copy the codeDelete the data
@Override
public Integer deleteById(Integer id){
return jdbcTemplate.update("DELETE FROM user WHERE id = ?", id);
}
Copy the codesummary
By contrast, we found that the use of JdbcTemplate template tool class can greatly reduce JDBC access to the database code complexity, as a developer we should only care about the specific implementation process of business logic, the creation of JDBC underlying objects, the release of resources, the capture of exceptions, should be given to the framework unified maintenance and management.
Although JdbcTemplate reduces the amount of code we need to access the database, it also has some problems. For example, when new data is added, it cannot return the id of the generated primary key by default. The SQL is hardcoded into Java code, and the Java code needs to be recompiled if the SQL is changed, which is not good for system maintenance. In this case, we need another framework, which is the famous Mybatis. In the next article, I will show how SpringBoot integrates Mybatis.
Program source code
Github:github.com/dragon8844/…
One last word
If this article is helpful to you, or inspired, help pay attention to it, your support is the biggest motivation I insist on writing, thank you for your support.
In addition, pay attention to the public number: black lighthouse, focus on Java back-end technology sharing, covering Spring, Spring the Boot, SpringCloud, Docker, Kubernetes middleware technology, etc.