JdbcTemplate
JdbcTemplate is Spring's encapsulation of JDBC to make JDBC easier to use. The JdbcTemplate is part of Spring. The JdbcTemplate handles the creation and release of resources. It helps us avoid some common mistakes, such as forgetting to always close the connection. It runs the core JDBC workflow, such as the establishment and execution of Statement, and we only need to provide SQL statements and extract results.
The methods of executing SQL statements in JdbcTemplate can be roughly divided into three categories:
- Execute: all SQL statements can be executed, which is generally used to execute DDL statements.
- Update: used to execute DML statements such as INSERT, update and DELETE.
- queryXxx: used for DQL data query statement.
In addition to providing the core class of JdbcTemplate, Spring also provides the NamedParameterJdbcTemplate class based on the implementation of JdbcTemplate to support named parameter binding, and the SimpleJdbcTemplate class to support Java 5 + variable parameters and automatic boxing and unpacking.
(1) 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: get the Connection provided by JdbcTemplate through callback, and the user will use the Connection to create relevant PreparedStatement;
CallableStatementCreator: obtain the Connection provided by the JdbcTemplate through callback, and the user uses the Connection to create a related CallableStatement;
- Precompiled statement 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: ； Similar to PreparedStatementSetter, but 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 the Statement provided by JdbcTemplate through callback, and users can perform any number of operations on the Statement;
PreparedStatementCallback: obtain the PreparedStatement provided by the JdbcTemplate through the callback, and the user can perform any number of operations on the PreparedStatement;
CallableStatementCallback: get the CallableStatement provided by JdbcTemplate through callback. Users can perform any number of operations on the CallableStatement;
- 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: used to process the results of each row of the ResultSet. The user needs to implement the method processRow(ResultSet rs) to complete the processing. In this callback method, there is no need to execute rs.next(). This operation is performed by the JdbcTemplate. The user only needs to obtain data by row and then process it.
ResultSetExtractor: used 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;
(2) JdbcTemplate operation
1) Preparatory work
-
Import jar package
-
Configure the database connection pool in the spring configuration file
-
Configure the JdbcTemplate object and inject DataSource
-
Create a service class, create a dao class, and inject a JDBC template object into dao
<!-- Component scan -->
<context:component-scan base-package="com.atguigu"></context:component-scan>
12
@Service
public class BookService {
//Injection dao
@Autowired
private BookDao bookDao;
}
@Repository
public class BookDaoImpl implements BookDao {
//Inject JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
2) JdbcTemplate operation database (add)
a) Create entity class corresponding to database
b) Create service and dao
(1) Add database in dao
(2) Call the update method in the JdbcTemplate object to add
@Repository
public class BookDaoImpl implements BookDao {
//Inject JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
//Add method
@Override
public void add(Book book) {
//1. Create sql statement
String sql = "insert into t_book values(?,?,?)";
//2 call method implementation
Object[] args = {book.getUserId(), book.getUsername(),book.getUstatus()};
int update = jdbcTemplate.update(sql,args);
System.out.println(update);
}
}
3) JdbcTemplate operation database (modification and deletion)
//1. Modification
@Override
public void updateBook(Book book) {
String sql = "update t_book set username=?,ustatus=? where user_id=?";
Object[] args = {book.getUsername(), book.getUstatus(),book.getUserId()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
//2. Delete
@Override
public void delete(String id) {
String sql = "delete from t_book where user_id=?";
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
}
//The "addition, deletion and modification" implemented by using the JdbcTemplate template calls the same "update" method
4) JdbcTemplate operation database (query returns a value)
//Number of query table records
@Override
public int selectCount() {
String sql = "select count(*) from t_book";
//In the queryForObject method: the first parameter represents -- sql statement; The second parameter represents -- return type class
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
5) JdbcTemplate operation database (query return object)
//Query return object
@Override
public Book findBookInfo(String id) {
String sql = "select * from t_book where user_id=?";
//Call method
/*
queryForObject Method:
First parameter: sql statement
The second parameter: RowMapper is an interface. For different types of data returned, use the implementation classes in this interface to complete data encapsulation
The third parameter: sql statement value
*/
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
6) JdbcTemplate operation database (query return set)
//Scenario used: query book list Pagination
//Query return set
@Override
public List<Book> findAllBook() {
String sql = "select * from t_book";
//Call method
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
7) JdbcTemplate operation database (batch operation)
//Batch add
@Override
public void batchAddBook(List<Object[]> batchArgs) {
String sql = "insert into t_book values(?,?,?)";
//The first parameter of batchUpdate method: sql statement The second parameter: List set, adding multiple record data
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
//Batch add test
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3","java","a"};
Object[] o2 = {"4","c++","b"};
Object[] o3 = {"5","MySQL","c"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//Call batch add
bookService.batchAdd(batchArgs);
8) The JdbcTemplate implements batch modification operations
//Batch modification (call the same method as batch addition)
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "update t_book set username=?,ustatus=? where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}