Previously on
In the first article, Java Operation Database (I) — JDBC Quick Start, we introduced JDBC fast start.
Since this is just a quick start, some things are not covered, such as how JDBC uses connection pooling, how JDBC enables transactions, and so on.
Therefore, this article will supplement the above information first.
Because native JDBC is cumbersome to develop, the result set obtained by SELECT is also valued column by column.
So here comes DbUtils, which automatically encapsulates our results into objects and makes it very easy to use.
Second, JDBC supplementary knowledge
2.1 Connection Pool (DataSource)
A Connection pool, a container used to manage JDBC-created connections, is analogous to our Java thread pool.
- Function: It can reduce the repeated creation and destruction of connections and improve the efficiency of program execution as much as possible.
- Principle (simplicity) : a number of connection pools are created in advance
The connectionWhen the callThe close method of the connectiontheThe connectionWill come back toThe connection poolRather thanThe destruction.
There are many types of connection pools: DBCP, C3P0, Druid, Hikari, and so on.
We chose to use alibaba’s Druid.
First, we need to introduce Druid (Maven is recommended).
-
Jar package download address (extract code: 8b5L)
Maven depends on:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
Copy the codeNext, we use connection pooling to modify the JDBC utility classes from the previous article.
The above configuration is written directly into Java code, but in general development, it should not be in Java code, but in a file that stores configuration information (e.g..properties,.yml, etc.).
Configuration file location (adjustable) :
- Non-maven projects:
src->configPackage and create onedruid.propertiesFile. - Maven project: Directly in
resourcesCreated in thedruid.propertiesFile.
Druid. Properties:
# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = database related configuration (adjust) = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/travel? useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=123456
# = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Druid connection pool related configuration (can adjust itself, there are other configuration Settings) = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
# Initial number of connections
initialSize=10
# Maximum number of connections
maxActive=20
Get the maximum connection wait time
maxWait=3000
Copy the codeJDBCUtil. Java:
Lol, isn’t this tool class a bit crude… (Haha DbUtils did OwO for us because of close etc trivia)
One item at the end is a base item: one item for the highlight (●’◡’●)
public class JDBCUtil {
/ * *
* connection pool
* /
static DataSource dataSource = null;
/ * *
* Static code block used to initialize connection pool
* /
static {
try {
//1. Use the classloader to turn the properties file into an input stream
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
Properties properties = new Properties();
// Load the input stream
properties.load(is);
//2. Create a database connection pool with the specified parameters
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/ * *
* Gets the current connection pool
* @return
* /
public static DataSource getDataSource(a) {
return dataSource;
}
/ * *
* access to the Connection
* @return Connection
* /
public static Connection getConnection(a){
Connection connection = null;
try {
/ / the Connection is established
connection = dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
/ * *
* Get Connection and start transaction
* @return Connection
* /
public static Connection getConnectionWithTransaction(a){
Connection connection = null;
try {
/ / the Connection is established
connection = dataSource.getConnection();
// Turn off auto commit, that is, turn on transaction. (!!!!!! You need to write your own business-related code and submit it manually)
connection.setAutoCommit(false);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
}
Copy the code2.2 JDBC Enabling transactions
(The above utility class has corresponding methods)
-
Get the connection
-
Turn off auto commit, that is, start a transaction
java connection.setAutoCommit(false); -
Execute the process
Java try{// business operation¨ K46K}catch{// rollback connection.rollback(); }finally{// Close connection.close(); }
Quick start of DbUtils
Finally, it’s the turn of our protagonist DbUtils!
DbUtils is an open source project owned by Apache
3.1 introduction
So let’s take a quick look at what DbUtils do.
I’ll summarize it in a moment. (Poof, just to prove I got 233 from the website.)
In short: DbUtils can be used for efficient JDBC development, allowing you to focus more on querying and updating data (automated mapping of entity classes, etc.).
Features:
Small–DbUtilsNot much content, very fast, there are two main core:QueryRunnerandResultSetHandler.TransparentYou just need to do the operations (write the SQL),DbUtilsWill performsqlReturns the result and closes the resourceFast– the introduction ofDbUtilsReady to use, no additional operations required.
3.2 ` QueryRunner ` and ` ResultSetHandler `
QueryRunner and ResultSetHandler are the two cores of DbUtils.
3.2.1 QueryRunner introduction
QueryRunner’s main functions are: processing SQL statements, add, delete, change, and so on.
Common constructors:
// Constructor for QueryRunner that takes a DataSource to use.
// Pass a connection pool to QR to use
QueryRunner(DataSource ds)
Copy the codeCommon CRUD apis I’ll demonstrate in Section 4.
3.2.2 ResultSetHandler introduction
The main purpose of ‘ResultSetHandler’ is to complete the result mapping (using reflection), for example, we can find the result directly a User entity class.
| Common mapping | describe |
|---|---|
| BeanHandler | Select * from user where id = 1 and so on |
| BeanListHandler | Result set -> entity set (select * from user, etc.) |
| ScalarHandler | Result set -> single data (such as select count(*) from user, etc.) |
Common constructors:
// Creates a new instance of BeanHandler.
// This is the constructor for BeanHandler, passing in the Class type of the entity Class you want to convert, since reflection is still used at the bottom
// Other similar
BeanHandler(Class<? extends T> type)
Copy the code3.3 Hands-on cases
3.3.1 Creating the user table
You can simply create a user table.
create database if not exists jdbc_demo
use jdbc_demo
create table user
(
id bigint auto_increment,
username varchar(64) not null comment 'account'.
password varchar(64) not null comment 'password'.
constraint user_pk
primary key (id)
)
comment 'User table';
INSERT INTO jdbc_demo.user (username, password) VALUES ('user1'.'123');
INSERT INTO jdbc_demo.user (username, password) VALUES ('user2'.'456');
INSERT INTO jdbc_demo.user (username, password) VALUES ('user3'.'123456');
Copy the code3.3.2 Importing JAR packages
Mysql connector - Java - 8.0.19. Jar– mysql driverCommons dbutils - 1.7. The jar– DbUtilsJar package download address (extract code: R7q9)Druid - 1.1.22. Jar– the druid
3.3.3 Operation process
There are 6 items here, mostly for ease of understanding (●’ plus-one ‘●). In chapter 4, I’ll simplify the process.
// 1. Obtain the connection pool
DataSource dataSource = JDBCUtil.getDataSource();
// 2. Create QueryRunner and pass the connection pool as an argument to the constructor
QueryRunner queryRunner = new QueryRunner(dataSource);
// 3. Create related ResultSetHandle to form the result map
BeanHandler<User> userBeanHandler = new BeanHandler<>(User.class);
// 4. Write SQL statements
String sql = "select * from jdbc_demo.user where id = ?";
5. Execute qr's query method
// Parameter 1: SQL statement
// Parameter 2: ResultSetHandler
// Parameter 3: Replaces placeholders for SQL statements, similar to preparedStatement
// The underlying query is preparedStatement
User user = queryRunner.query(sql, userBeanHandler, 1);
// 6. View the result
System.out.println(user);
/ / Perfect (low low ◡)
// User{id=1, username='user1', password='123'}
Copy the codeHa, ha, ha. With this small example, we successfully get information about the User entity class.
One smooth item: one item ahead (●’◡’●).
4. Introduction to common CRUD apis
4.1 Finding a Single data
The use of ScalarHandler
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
// 2.
// Tip: Write the handler first, then write the SQL statement, so there is a hint
Long query = queryRunner.query("select count(*) from jdbc_demo.user".new ScalarHandler<>());
/ / 3. Input
System.out.println(query);
/ / 3
Copy the code4.2 Searching for Single Data
Use the BeanHandler
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
// 2.
// Tip: Write the handler first, then write the SQL statement, so there is a hint
User user = queryRunner.query(
"select * from jdbc_demo.user where username = ?".
new BeanHandler<>(User.class),
"user2");
/ / 3. Input
System.out.println(user);
{id=2, username='user2', password='456'}
Copy the code4.3 Searching for Multiple Pieces of Data
The use of BeanListHandler
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
// 2.
// Tip: Write the handler first, then write the SQL statement, so there is a hint
// You can also use where as an example
List<User> users = queryRunner.query(
"select * from jdbc_demo.user".
new BeanListHandler<>(User.class));
/ / 3. The output
System.out.println(users);
/ / get
// [User{id=1, username='user1', password='123'},
// User{id=2, username='user2', password='456'},
// User{id=3, username='user3', password='123456'}]
Copy the code4.4 increase
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
// 2.
// Tip: Write the handler first, then write the SQL statement, so there is a hint
// You can also use where as an example
// Add, delete, and change are updates
int res = queryRunner.update("insert into jdbc_demo.user values (null,'user4','user4')");
/ / 3. The output
System.out.println(res);
// A one item for a successful item (●'◡'●)
Copy the codeWe can see that the database does have new data.
The 4.5 update
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
// 2.
// Tip: Write the handler first, then write the SQL statement, so there is a hint
// Add, delete, and change are updates
int res = queryRunner.update(
"update jdbc_demo.user set password = '123456' where username = ?".
"user4");
/ / 3. The output
System.out.println(res);
// A one item for a successful item (●'◡'●)
Copy the codeAs you can see, the data in the database has been modified.
4.6 delete
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
// 2.
// Tip: Write the handler first, then write the SQL statement, so there is a hint
// Add, delete, and change are updates
int res = queryRunner.update(
"delete from jdbc_demo.user where username = ? ".
"user4");
/ / 3. The output
System.out.println(res);
// A one item for a successful item (●'◡'●)
Copy the codeAs you can see, user4 in the database has been deleted from OvO
4.7 Batch Operations
The same is true for batch inserts, deletes, and updates. (Remember to write rewriteBatchedStatements=true in the DATABASE URL)
Create QueryRunner
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
/ / 2. The operation
// Using the batch method,
// Parameter 1: batch operation SQL
// Argument 2: binary array, number of rows -> number of executions, column -> replace placeholders
Object[][] params = new Object[10] [];
for (int i = 0; i < params.length; i++) {
params[i] = new Object[]{"lemonfish" + i, i};
}
// Batch represents the execution result of each line of statements
int[] batch = queryRunner.batch(
"insert into jdbc_demo.user values (null,? ,?) ".
params
);
/ / 3. The output
System.out.println(Arrays.toString(batch));
/ / get [1, 1, 1, 1, 1, 1, 1, 1, 1, 1] to complete success (low low ◡)
Copy the codeAs you can see, 10 data were inserted successfully
4.8 Transaction Operations
Transaction operations are a little different from the previous ones. QueryRunner handles transactions using connection (which is actually native JDBC), so we use our own packaged utility class to get connection.
Create QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 2. Obtain the connection to start the transaction
Connection connection = JDBCUtil.getConnectionWithTransaction();
/ / 3. Business
// 3.1 Delete and add
try {
int delete = queryRunner.update(connection,
"delete from jdbc_demo.user where username = ? ".
"user3");
int insert = queryRunner.update(connection,
"insert into jdbc_demo.user values (null,'user4','user4')");
// 3.2 Manually create an error and view the rollback effect
int error = 1 / 0;
// 3.3 Manual Submission
connection.commit();
} catch (SQLException throwables) {
3.4 Performing a Rollback
connection.rollback();
throwables.printStackTrace();
} finally{
// Remember to close
connection.close();
}
Copy the codeRollback rollback will rollback to the state before the connection SQL statement was executed in the event of code execution errors.
5. Packaging BaseDAO
DbUtils is pretty cool compared to native JDBC.
Don’t worry, there’s more to it
Next, we’ll wrap a BaseDAO that encapsulates some common add, delete, change, and query operations, and then use subclass inheritance to use them.
Add delete change check, a SQL fix
This is the BaseDAO code, basically integrating the previous API.
/ * *
* Encapsulate basic add, delete, change and review to reuse code
* /
public class BaseDAO<T> {
QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
/ * *
* add deletion
*
* @paramSQL SQL statements
* @paramParams placeholder
* @return
* /
public boolean update(String sql, Object... params) {
int result = 0;
try {
result = qr.update(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
return result > 0;
}
/ * *
* Query a single value
*
* @param sql
* @param params
* @return
* /
public Object selectScalar(String sql, Object... params) {
try {
return qr.query(sql, new ScalarHandler<>(), params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/ * *
* Query a single row
*
* @param sql
* @param clazz
* @param params
* @return
* /
public T selectOne(String sql, Class<T> clazz, Object... params) {
try {
return qr.query(sql, new BeanHandler<>(clazz), params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/ * *
* Query multiple rows
*
* @param sql
* @param clazz
* @param params
* @return
* /
public List<T> selectList(String sql, Class<T> clazz, Object... params) {
try {
return qr.query(sql, new BeanListHandler<>(clazz), params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/ * *
* Batch operation add delete change
* @param sql
* @param params
* @return
* /
public int[] batch(String sql,Object[][] params){
int[] batch = new int[0];
try {
batch = qr.batch(sql,params);
} catch (SQLException e) {
e.printStackTrace();
}
return batch;
}
}
Copy the codeuse
-
Write a UserDAO inheriting from BaseDAO
Public class UserDAO extends BaseDAO<User>{public class UserDAO extends BaseDAO<User>{ -
Using the instance
UserDAO UserDAO = new UserDAO(); SelectOne ("select * from User where username=?") , User.class, "user1"); System.out.println(user); // 3. User{id=1, username='user1', password='123'}
Ha ha! Is not a simple SQL to fix the query OwO, add, delete, change, etc. Oh ~~
The underlying implementation of DbUtils is mainly JDBC and reflection (such as generating beans, and then calling setters, etc.), the source code is probably not very difficult, interested friends can read by themselves.
6. The DEMO source code
In order to facilitate you to get the DEMO code in the article, I put the DEMO source on Github, there is a need to partners can download oh (including all the code appeared in this article).
7. Write at the end
One item for a further look at your Java operation database (●’◡’)
Basically before touching the persistence layer frame, this is a better way to deal with it.
Feel free to point it out in the comments section if there is anything that is not well written
Hope everyone can make progress together, don’t forget to click 👍b(~ ▽ ~)d oh ~~