1. Overview of jdbctemplage
JdbcTemplage is located at org springframework. jdbc. Under the core package, the most basic JDBC operations are encapsulated, which simplifies the operation process of JDBC.
2. How to use it?
Start with the code and understand how to use it according to the code:
(1) Register BaseJdbc first
package com.dechnic.common.core.jdbc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @description:
* @author:houqd
* @time: 2021/8/12 11:19
*/
@Component
public class BaseJdbc implements IConnection {
@Autowired
private DataSource dataSource;
@Autowired
protected JdbcTemplate jdbcTemplate;
private ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<>();
@Override
public Connection getConnection() {
Connection conn = CONNECTION_HOLDER.get();
if (conn == null){
try {
conn = dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
CONNECTION_HOLDER.set(conn);
}
}
return conn;
}
@Override
public void closeConnection() {
Connection conn = CONNECTION_HOLDER.get();
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
CONNECTION_HOLDER.remove();
}
}
}
}
Introduce dataSource and JDBC template dependencies into the Spring container.
(2) Write test class to inherit BaseJdbc
package com.dechnic.common.core.jdbc;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.boot.autoconfigure.data.jdbc.JdbcRepositoriesAutoConfiguration;
import org.springframework.context.annotation.Scope;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.stereotype.Component;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @description:
* @author:houqd
* @time: 2021/8/12 13:45
*/
@Component
@Scope("prototype")
public class TestJdbc extends BaseJdbc{
// query
public List<Map<String,Object>> getUsers(){
String sql="select * from sys_user";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
return maps;
}
//DDL statement
public Object createTempUserTable(){
String sqlBefore = "drop table if exists AA";
String sql = "create table if not exists AA (id int(11) not null auto_increment primary key,name varchar(255) default null )";
Object o = jdbcTemplate.execute(new StatementCallback<Object>() {
@Override
public Object doInStatement(Statement stmt) throws SQLException, DataAccessException {
boolean execute1 = stmt.execute(sqlBefore);
boolean execute = stmt.execute(sql,0);
return execute;
}
});
return o;
}
// Execute insert
public void insertTempUser(){
jdbcTemplate.execute(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String sql="insert into AA(name)value (?)";
PreparedStatement pstmt = con.prepareStatement(sql);
for (int i = 0; i < 10; i++) {
pstmt.setInt(1,i);
pstmt.addBatch();
}
return pstmt;
}
}, new PreparedStatementCallback<Object>() {
@Override
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
return ps.executeBatch();
}
});
}
// Execute update
public void updateTempUser(){
String sql = "update AA set name = ? where id=?";
Object[] args=new Object[2];
args[0] = "test";
args[1]= 1;
int[] argTypes = {Types.VARCHAR,Types.INTEGER};
jdbcTemplate.update(sql,args,argTypes);
}
// Call stored procedure
public void invokeProc(){
// String sql = "call temp_findById (1)";
// jdbcTemplate.execute(sql);
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String proc = "{call temp_findById(?)}";
CallableStatement cs = con.prepareCall(proc);
cs.setInt(1,1);
return cs;
}
}, new CallableStatementCallback<List<TestU>>() {
@Override
public List<TestU> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.execute();
ResultSet resultSet = cs.getResultSet();
List<TestU> list = new ArrayList<>();
if (resultSet!=null){
RowMapper<TestU> rowMapper = new BeanPropertyRowMapper<TestU>(TestU.class);
int rowNum =1;
while (resultSet.next()){
list.add(rowMapper.mapRow(resultSet,rowNum++));
}
}
return list;
}
});
}
public void invokeProc2(){
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall("{call temp_findById(?)}");
cs.setInt(1,1);
return cs;
}
}, new CallableStatementCallback<List<Map<String,Object>>>() {
@Override
public List<Map<String,Object>> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.execute();
ResultSet resultSet = cs.getResultSet();
List<Map<String,Object>> list = new ArrayList<>();
int rowNum =1;
if (resultSet!=null){
while (resultSet.next()){
Map<String,Object> map = new HashMap<>();
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
map.put("id",id);
map.put("name",name);
}
}
return list;
}
});
}
}
The stored procedures in mysql database are:
AA table is:
3. Summary:
JdbcTemplate mainly provides the following five methods:
Execute method: it can be used to execute any SQL statement, generally used to execute DDL statements;
Update method and batchUpdate method: the update method is used to execute new, modify, delete and other statements; The batchUpdate method is used to execute batch related statements;
Query method and queryForXXX method: used to execute query related statements;
call method: used to execute stored procedures, functions and related statements.
Callback classes supported by the JdbcTemplate class:
Pre compiled statement and stored procedure creation callback: used to create corresponding statements according to the connection provided by JdbcTemplate;
PreparedStatementCreator: obtain the Connection provided by the JdbcTemplate through callback, and the user uses the Connection to create relevant PreparedStatement;
CallableStatementCreator: Get through callback JdbcTemplate Provided Connection，Used by the user Conncetion Create related CallableStatement；
Precompiled statement value setting callback: used to set values for corresponding parameters of precompiled statements;
PreparedStatementSetter: obtain the PreparedStatement provided by the JdbcTemplate through the callback, and the user will set the value of the corresponding parameters of the corresponding precompiled statement;
BatchPreparedStatementSetter: ；be similar to PreparedStatementSetter，However, for batch processing, you need to specify the batch size;
User defined function callback: provides users with an extension point. Users can perform any number of required operations at the specified type of extension point;
ConnectionCallback: get the Connection provided by JdbcTemplate through callback, and users can perform any number of operations on the Connection;
StatementCallback: Get through callback JdbcTemplate Provided Statement，Users can Statement Perform any number of operations;
PreparedStatementCallback: Get through callback JdbcTemplate Provided PreparedStatement，Users can PreparedStatement Perform any number of operations;
CallableStatementCallback: Get through callback JdbcTemplate Provided CallableStatement，Users can CallableStatement Perform any number of operations;
Result set processing callback: process the ResultSet through callback or convert the ResultSet to the required form;
RowMapper: used to convert each row of data in the result set to the required type. The user needs to implement the method mapRow(ResultSet rs, int rowNum) to convert each row of data to the corresponding type.
RowCallbackHandler: For processing ResultSet For each line of results, the user needs to implement the method processRow(ResultSet rs)To complete the processing, there is no need to execute in the callback method rs.next()，This operation is performed by JdbcTemplate To execute, users only need to obtain data by row and then process it.
ResultSetExtractor: For result set data extraction, the user needs to implement the method extractData(ResultSet rs)To process the result set, the user must process the whole result set;