1. Implementation of spring connection database (JDBC)
Spring has done a lot of encapsulation for JDBC, eliminating redundant code and greatly reducing the development cost. Let's learn about the operation of JDBC in spring through an example.
1) Create data table structure
CREATE TABLE user ( id int(1) NOT NULL auto increment, name varchar (255) default NULL , age int ( 11 ) default NULL , sex varchar (255 ) default NULL , PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2) Create corresponding PO
public class User {
private int id;
private String name;
private int age;
private String sex;
// Omit getter and setter methods
}
3) Create mapping between tables and entities
public class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User person = new User(rs.getInt("id"), rs.getString("name"),
rs.getInt("age"), rs.getString("sex"));
return person;
}
}
4) Create data operation interface
public interface UserService {
int save(User user);
List<User> getUser();
}
5) Create data operation interface implementation class
public class UserServiceImpl implements UserService {
private JdbcTemplate jdbcTemplate;
/**
* set up data sources
*/
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public int save(User user) {
/*jdbcTemplate.update(
"INSERT INTO `user` (`name`, age, sex) VALUES (?, ? ,?)",
new Object[]{user.getName(), user.getAge(), user.getSex()},
new int[]{Types.VARCHAR, Types.INTEGER, Types.VARCHAR});*/
// SqlParameterValue can be used to encapsulate values
SqlParameterValue name = new SqlParameterValue(Types.VARCHAR, user.getName());
SqlParameterValue age = new SqlParameterValue(Types.INTEGER, user.getAge());
SqlParameterValue sex = new SqlParameterValue(Types.VARCHAR, user.getSex());
return jdbcTemplate.update("INSERT INTO `user` (`name`, age, sex) VALUES (?, ? ,?)", name, age, sex);
}
@Override
public List<User> getUser() {
//With parameters
/*List<User> list = jdbcTemplate.query("select * from user where age = ?",
new Object[]{20}, new int[]{Types.INTEGER}, new UserRowMapper());*/
//Without parameters
List<User> list = jdbcTemplate.query("select * from user", new UserRowMapper());
return list;
}
}
6) Create Spring configuration file
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tool/spring-tx.xsd"> <!-- Configure data source--> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="com.mysql.jdbc.Driver" /> <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/study" /> <property name="user" value="root" /> <property name="password" value="123456" /> <!-- Initial value of connection pool startup--> <property name="initialPoolSize" value="1" /> <!-- Connection pool maximum--> <property name="maxPoolSize" value="300" /> <!-- Connection pool minimum--> <property name="minPoolSize" value="1" /> </bean> <!-- Allocation industry bean--> <bean id="userService" class="org.springframework.test.jdbc.UserServiceImpl"> <property name="dataSource" ref="dataSource" /> </bean> </beans>
7) Testing
public class Main {
public static void main(String[] args) {
ClassPathXmlApplicationContext factory = new ClassPathXmlApplicationContext("test/jdbc/bean.xml");
UserService userService = (UserService) factory.getBean("userService");
User user = new User();
user.setName("Li Si");
user.setAge(20);
user.setSex("male");
userService.save(user);
List<User> users = userService.getUser();
System.out.println("Get all User");
for (User tmp : users) {
System.out.println(tmp);
}
}
}
2. Implementation of save / update function
The realization of the whole function is based on JdbcTemplate (template method design pattern). DataSource needs to be set in the JdbcTemplate, which is the basis of the whole database operation and encapsulates the connection information of the whole database. Firstly, take saving entity class as an example.
public void save(User user) {
jdbcTemplate.update("INSERT INTO `user` (`name`, age, sex) VALUES (?, ? ,?)",
new Object[]{user.getName(), user.getAge(), user.getSex()}, new int[]{Types.VARCHAR, Types.INTEGER, Types.VARCHAR});
}
This method calls the JdbcTemplate#update method.
public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException {
return update(sql, newArgTypePreparedStatementSetter(args, argTypes));
}
public int update(String sql, @Nullable PreparedStatementSetter pss) throws DataAccessException {
return update(new SimplePreparedStatementCreator(sql), pss);
}
After entering the update method, first use ArgPreparedStatementSetter to encapsulate the parameters and SimplePreparedStatementCreator to encapsulate the sql statement. After encapsulation, you can enter the core data processing.
/**
* Perform insert or update
* @param psc Encapsulates SQL default implementation {@ link simplepreparedstatementcreator}
* @param pss Encapsulated parameters
*/
protected int update(final PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
// use
return updateCount(execute(psc, ps -> {
try {
if (pss != null) {
// Set the parameters required for PreparedStatement
pss.setValues(ps);
}
// implement
int rows = ps.executeUpdate();
if (logger.isTraceEnabled()) {
logger.trace("SQL update affected " + rows + " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}));
}
The execute method is the most basic operation, while other operations, such as update and query, pass in different PreparedStatementCallback parameters to execute different logic.
2.1 basic method execute
As the core entry of database operations, the execute method encapsulates the same steps of most database operations, and uses the parameter PreparedStatementCallback for callback processing of personalized operations.
public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action)
throws DataAccessException {
Assert.notNull(psc, "PreparedStatementCreator must not be null");
Assert.notNull(action, "Callback object must not be null");
if (logger.isDebugEnabled()) {
String sql = getSql(psc);
logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
}
// Get database connection
Connection con = DataSourceUtils.getConnection(obtainDataSource());
PreparedStatement ps = null;
try {
// Create PreparedStatement
ps = psc.createPreparedStatement(con);
// Apply user set input parameters
applyStatementSettings(ps);
// Call callback function
T result = action.doInPreparedStatement(ps);
handleWarnings(ps);
return result;
}
catch (SQLException ex) {
// Release Connection early, to avoid potential connection pool deadlock
// in the case when the exception translator hasn't been initialized yet.
// Release the database connection to avoid potential connection pool deadlock when the exception converter is not initialized
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
String sql = getSql(psc);
psc = null;
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
throw translateException("PreparedStatementCallback", sql, ex);
}
finally {
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
JdbcUtils.closeStatement(ps);
// Release resources
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
The above methods encapsulate the common operations. The important logic is as follows
1) Get database connection
Getting a database connection is not directly using datasource Getconjunction () method, but considers a variety of situations.
// DataSourceUtils.java
public static Connection getConnection(DataSource dataSource) throws CannotGetJdbcConnectionException {
try {
return doGetConnection(dataSource);
}
catch (SQLException ex) {
throw new CannotGetJdbcConnectionException("Failed to obtain JDBC Connection", ex);
}
catch (IllegalStateException ex) {
throw new CannotGetJdbcConnectionException("Failed to obtain JDBC Connection: " + ex.getMessage());
}
}
public static Connection doGetConnection(DataSource dataSource) throws SQLException {
Assert.notNull(dataSource, "No DataSource specified");
ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
conHolder.requested();
if (!conHolder.hasConnection()) {
logger.debug("Fetching resumed JDBC Connection from DataSource");
conHolder.setConnection(fetchConnection(dataSource));
}
return conHolder.getConnection();
}
// Else we either got no holder or an empty thread-bound holder here.
logger.debug("Fetching JDBC Connection from DataSource");
// Get connection
Connection con = fetchConnection(dataSource);
// The current thread supports synchronization
if (TransactionSynchronizationManager.isSynchronizationActive()) {
try {
// Use same Connection for further JDBC actions within the transaction.
// Thread-bound object will get removed by synchronization at transaction completion.
// Use the same transaction connection in the same database
ConnectionHolder holderToUse = conHolder;
if (holderToUse == null) {
holderToUse = new ConnectionHolder(con);
}
else {
holderToUse.setConnection(con);
}
// Record database connection
holderToUse.requested();
TransactionSynchronizationManager.registerSynchronization(
new ConnectionSynchronization(holderToUse, dataSource));
holderToUse.setSynchronizedWithTransaction(true);
if (holderToUse != conHolder) {
TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
}
}
catch (RuntimeException ex) {
// Unexpected exception from external delegation call -> close Connection and rethrow.
releaseConnection(con, dataSource);
throw ex;
}
}
return con;
}
In terms of database connection, Spring mainly considers transaction processing. Based on the particularity of transaction processing, Spring needs to ensure that all database operations in the thread use the same transaction connection.
2) Apply user set input parameters
protected void applyStatementSettings(Statement stmt) throws SQLException {
int fetchSize = getFetchSize();
if (fetchSize != -1) {
stmt.setFetchSize(fetchSize);
}
int maxRows = getMaxRows();
if (maxRows != -1) {
stmt.setMaxRows(maxRows);
}
DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
}
setFetchSize is designed to reduce the number of network interactions. When accessing the ResultSet, if it reads only one row of data from the server at a time, it will incur a lot of overhead. setFetchSize means how many rows of data the ResultSet will get from the server at one time when calling rs.next. In this way, it can get data directly from memory without network interaction in the next rs.next, which improves the efficiency. However, over setting will cause the memory to rise.
setMaxRows limits the maximum number of rows that all ResultSet objects generated by this Statement object can contain to the given number.
3) Call callback function
Handle personalized processing outside some general methods, that is, the callback of doInPreparedStatement method with parameters of PreparedStatementCallback type.
4) Warning handling
protected void handleWarnings(Statement stmt) throws SQLException {
// When set to ignore warnings, only try to print the log
if (isIgnoreWarnings()) {
// If the log is on, print the log
if (logger.isDebugEnabled()) {
// Get alarm content
SQLWarning warningToLog = stmt.getWarnings();
while (warningToLog != null) {
logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + "', error code '" +
warningToLog.getErrorCode() + "', message [" + warningToLog.getMessage() + "]");
warningToLog = warningToLog.getNextWarning();
}
}
}
else {
handleWarnings(stmt.getWarnings());
}
}
protected void handleWarnings(@Nullable SQLWarning warning) throws SQLWarningException {
if (warning != null) {
throw new SQLWarningException("Warning not ignored", warning);
}
}
A SQLWarning class is used in this method, which provides exceptions about database access warning information. These exceptions are directly linked to the object of the method that caused the alarm to be reported. Warnings can be obtained from Connection, Statement and ResultSet objects. An attempt to get a warning on a closed Connection, closed Statement, or closed result set will throw an exception. In addition, when you close a Statement, you also close the result set it may generate.
The handling of warnings does not directly throw exceptions. Data errors may occur when warnings appear, but it does not necessarily affect the execution of the program. All users can set their own methods to handle warnings. Ignore the warning print log, otherwise an exception will be thrown.
5) Resource release
The connection release of the database does not directly call the Connection#close method. Considering the existence of transactions, if there are transactions in the current thread, it indicates that there is a common database connection in the current thread. In this case, the ConnectionHolder#released method is directly used to reduce the connection by one, rather than releasing the connection.
// DataSourceUtils.java
public static void releaseConnection(@Nullable Connection con, @Nullable DataSource dataSource) {
try {
doReleaseConnection(con, dataSource);
}
catch (SQLException ex) {
logger.debug("Could not close JDBC Connection", ex);
}
catch (Throwable ex) {
logger.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
public static void doReleaseConnection(@Nullable Connection con, @Nullable DataSource dataSource) throws SQLException {
if (con == null) {
return;
}
if (dataSource != null) {
// If there is a transaction in the current thread, it indicates that there is a shared database connection in the current thread,
// In this case, the released method in the ConnectionHolder is directly used to reduce the connection by one, rather than releasing the connection.
ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (conHolder != null && connectionEquals(conHolder, con)) {
// It's the transactional Connection: Don't close it.
// Number of connections minus one
conHolder.released();
return;
}
}
// Close connection
doCloseConnection(con, dataSource);
}
public static void doCloseConnection(Connection con, @Nullable DataSource dataSource) throws SQLException {
if (!(dataSource instanceof SmartDataSource) || ((SmartDataSource) dataSource).shouldClose(con)) {
con.close();
}
}
2.2 callback function in update
PreparedStatementCallback is an interface, in which there is only one method doInPreparedStatement. This method is used to call the general method execute, which cannot handle some personalized processing methods. It is the implementation of the function in update.
return updateCount(execute(psc, ps -> {
try {
if (pss != null) {
// Set the parameters required for PreparedStatement
pss.setValues(ps);
}
// implement
int rows = ps.executeUpdate();
if (logger.isTraceEnabled()) {
logger.trace("SQL update affected " + rows + " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}));
In the above method, ps.executeUpdate() is the implementation of SQL and JDBC. Mainly looking at PSS Setvalues (PS) method is used to set the input parameters. First, review the execution process of using SQL in Spring.
jdbcTemplate.update(
"INSERT INTO `user` (`name`, age, sex) VALUES (?, ? ,?)",
new Object[]{user.getName(), user.getAge(), user.getSex()},
new int[]{Types.VARCHAR, Types.INTEGER, Types.VARCHAR});
The parameters and parameter types of SQL statements are clear. Spring has encapsulated them for us. However, the real JDBC call is very complex and requires:
PreparedStatement updateSales = connection.prepareStatement("INSERT INTO `user` (`name`, age, sex) VALUES (?, ? ,?)");
updateSales.setString(1, user.getName());
updateSales.setInt(2, user.getAge());
updateSales.setString(3, user.getSex());
So how does Spring encapsulate the above? All operations are based on PSS setValues(ps); Method is imported. In business logic, we use ArgumentTypePreparedStatementSetter to encapsulate parameters. Enter to view the source code. ArgumentTypePreparedStatementSetter this class specifies the encapsulation of database parameters for.
// ArgumentTypePreparedStatementSetter.java
public void setValues(PreparedStatement ps) throws SQLException {
// Location of the current parameter
int parameterPosition = 1;
if (this.args != null && this.argTypes != null) {
// Traverse each parameter as type matching and conversion
for (int i = 0; i < this.args.length; i++) {
Object arg = this.args[i];
// If it is a collection class, you need to enter the collection class to recursively resolve the internal attributes
if (arg instanceof Collection && this.argTypes[i] != Types.ARRAY) {
Collection<?> entries = (Collection<?>) arg;
for (Object entry : entries) {
if (entry instanceof Object[]) {
Object[] valueArray = ((Object[]) entry);
for (Object argValue : valueArray) {
doSetValue(ps, parameterPosition, this.argTypes[i], argValue);
parameterPosition++;
}
}
else {
doSetValue(ps, parameterPosition, this.argTypes[i], entry);
parameterPosition++;
}
}
}
else {
// Set current properties
doSetValue(ps, parameterPosition, this.argTypes[i], arg);
parameterPosition++;
}
}
}
}
/**
* Matching processing of single parameter and type
*/
protected void doSetValue(PreparedStatement ps, int parameterPosition, int argType, Object argValue)
throws SQLException {
StatementCreatorUtils.setParameterValue(ps, parameterPosition, argType, argValue);
}
// StatementCreatorUtils.java
public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType,
@Nullable Object inValue) throws SQLException {
setParameterValueInternal(ps, paramIndex, sqlType, null, null, inValue);
}
private static void setParameterValueInternal(PreparedStatement ps, int paramIndex, int sqlType,
@Nullable String typeName, @Nullable Integer scale, @Nullable Object inValue) throws SQLException {
String typeNameToUse = typeName;
int sqlTypeToUse = sqlType;
Object inValueToUse = inValue;
// override type info?
// If it is of type SqlParameterValue
if (inValue instanceof SqlParameterValue) {
SqlParameterValue parameterValue = (SqlParameterValue) inValue;
if (logger.isDebugEnabled()) {
logger.debug("Overriding type info with runtime info from SqlParameterValue: column index " + paramIndex +
", SQL type " + parameterValue.getSqlType() + ", type name " + parameterValue.getTypeName());
}
if (parameterValue.getSqlType() != SqlTypeValue.TYPE_UNKNOWN) {
sqlTypeToUse = parameterValue.getSqlType();
}
if (parameterValue.getTypeName() != null) {
typeNameToUse = parameterValue.getTypeName();
}
inValueToUse = parameterValue.getValue();
}
if (logger.isTraceEnabled()) {
logger.trace("Setting SQL statement parameter value: column index " + paramIndex +
", parameter value [" + inValueToUse +
"], value class [" + (inValueToUse != null ? inValueToUse.getClass().getName() : "null") +
"], SQL type " + (sqlTypeToUse == SqlTypeValue.TYPE_UNKNOWN ? "unknown" : Integer.toString(sqlTypeToUse)));
}
if (inValueToUse == null) {
setNull(ps, paramIndex, sqlTypeToUse, typeNameToUse);
}
else {
// The type is resolved in this method
setValue(ps, paramIndex, sqlTypeToUse, typeNameToUse, scale, inValueToUse);
}
}
If the corresponding parameter type is not specified when calling the update method, that is
SqlParameterValue name = new SqlParameterValue(Types.VARCHAR, user.getName());
SqlParameterValue age = new SqlParameterValue(Types.INTEGER, user.getAge());
SqlParameterValue sex = new SqlParameterValue(Types.VARCHAR, user.getSex());
return jdbcTemplate.update("INSERT INTO `user` (`name`, age, sex) VALUES (?, ? ,?)", name, age, sex);
It will be encapsulated using ArgumentPreparedStatementSetter.
public void setValues(PreparedStatement ps) throws SQLException {
if (this.args != null) {
// Traversal for assignment
for (int i = 0; i < this.args.length; i++) {
Object arg = this.args[i];
doSetValue(ps, i + 1, arg);
}
}
}
protected void doSetValue(PreparedStatement ps, int parameterPosition, Object argValue) throws SQLException {
if (argValue instanceof SqlParameterValue) {
SqlParameterValue paramValue = (SqlParameterValue) argValue;
StatementCreatorUtils.setParameterValue(ps, parameterPosition, paramValue, paramValue.getValue());
}
else {
// Set to a value of unknown type
StatementCreatorUtils.setParameterValue(ps, parameterPosition, SqlTypeValue.TYPE_UNKNOWN, argValue);
}
}
3. Implementation of query function
The use method of search operation is as follows.
@Override
public List<User> getUser() {
List<User> list = jdbcTemplate.query("select * from user where age = ?",
new Object[]{20}, new int[]{Types.INTEGER}, new UserRowMapper());
return list;
}
Trace the query method in the JdbcTemplate.
public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException {
return result(query(sql, args, argTypes, new RowMapperResultSetExtractor<>(rowMapper)));
}
public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException {
// Encapsulate parameters using ArgTypePreparedStatementSetter
return query(sql, newArgTypePreparedStatementSetter(args, argTypes), rse);
}
public <T> T query(String sql, @Nullable PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException {
// Encapsulating sql statements
return query(new SimplePreparedStatementCreator(sql), pss, rse);
}
public <T> T query(
PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
throws DataAccessException {
Assert.notNull(rse, "ResultSetExtractor must not be null");
logger.debug("Executing prepared SQL query");
return execute(psc, new PreparedStatementCallback<T>() {
@Override
@Nullable
public T doInPreparedStatement(PreparedStatement ps) throws SQLException {
ResultSet rs = null;
try {
if (pss != null) {
// The setting value is similar to update
pss.setValues(ps);
}
// query
rs = ps.executeQuery();
// Encapsulate result set
return rse.extractData(rs);
}
finally {
// Close result set
JdbcUtils.closeResultSet(rs);
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}
});
}
You can see that the overall processing is similar to that of update, except that the query operation is performed in the doInPreparedStatement method in the callback class PreparedStatementCallback, and the results and are processed.
rse. The extractdata (RS) method is responsible for encapsulating the results and replacing them with POJO. The class currently represented by RSE is RowMapperResultSetExtractor. When constructing RowMapperResultSetExtractor, we set the custom rowMapper into it.
// RowMapperResultSetExtractor.java
public List<T> extractData(ResultSet rs) throws SQLException {
List<T> results = (this.rowsExpected > 0 ? new ArrayList<>(this.rowsExpected) : new ArrayList<>());
int rowNum = 0;
while (rs.next()) {
// Put the results of the mapping into the collection
// Call mapRow method of custom RowMapper to map the value
results.add(this.rowMapper.mapRow(rs, rowNum++));
}
return results;
}
The above code does not have complex operations, but just traverses the returned results and uses rowMapper for conversion.
The above query method takes the query parameter "?" If there are no parameters, Spring uses another processing method. for example
List<User> list = jdbcTemplate.query("select * from user", new UserRowMapper());
Tracking code:
public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
return result(query(sql, new RowMapperResultSetExtractor<>(rowMapper)));
}
/**
* Query method without parameters
*/
@Override
@Nullable
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
Assert.notNull(sql, "SQL must not be null");
Assert.notNull(rse, "ResultSetExtractor must not be null");
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL query [" + sql + "]");
}
/**
* Callback to execute the query.
* Callback function
*/
class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
@Override
@Nullable
public T doInStatement(Statement stmt) throws SQLException {
ResultSet rs = null;
try {
// Execute query
rs = stmt.executeQuery(sql);
// Processing result set
return rse.extractData(rs);
}
finally {
///Close result set
JdbcUtils.closeResultSet(rs);
}
}
@Override
public String getSql() {
return sql;
}
}
// Perform the general method first
return execute(new QueryStatementCallback());
}
The biggest difference from the previous query method is that it reduces the transmission of parameters and parameter types, and naturally reduces the encapsulation of PreparedStatementSetter type. The execute method will also change.
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
Assert.notNull(action, "Callback object must not be null");
// Get data source
Connection con = DataSourceUtils.getConnection(obtainDataSource());
// Use Statement instead of PrepareStatement
Statement stmt = null;
try {
stmt = con.createStatement();
// Apply user set input parameters
// fetchSize and maxRows
applyStatementSettings(stmt);
// Execute callback function
T result = action.doInStatement(stmt);
handleWarnings(stmt);
return result;
}
catch (SQLException ex) {
// Release Connection early, to avoid potential connection pool deadlock
// in the case when the exception translator hasn't been initialized yet.
String sql = getSql(action);
JdbcUtils.closeStatement(stmt);
stmt = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
throw translateException("StatementCallback", sql, ex);
}
finally {
// Close statement
JdbcUtils.closeStatement(stmt);
// Release connection
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
This execute is not much different from the previous execute. It does some routine processing. But the creation of Statement is different. Here, the connection is used to create a Statement, while the PreparedStatement with parameters is created by using the PreparedStatementCreator class. PreparedStatement inherits from Statement. The differences between them are as follows:
- The reparedStatement instance contains precompiled SQL statements. This is to make the statement "ready". This statement reserves a "? For each IN parameter As a placeholder. The value of each question mark must be set before the statement is executed.
- PreparedStatement is precompiled and executes faster than Statement.
4 queryForObject
Spring not only provides us with query methods, but also encapsulates them and provides different types of query methods. As shown in the figure below.
Taking queryForObject as an example, this paper discusses how Spring encapsulates on the basis of the returned results.
public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException {
// Using SingleColumnRowMapper
return queryForObject(sql, getSingleColumnRowMapper(requiredType));
}
public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException {
List<T> results = query(sql, rowMapper);
// Get value
return DataAccessUtils.nullableSingleResult(results);
}
public static <T> T nullableSingleResult(@Nullable Collection<T> results) throws IncorrectResultSizeDataAccessException {
// This is identical to the requiredSingleResult implementation but differs in the
// semantics of the incoming Collection (which we currently can't formally express)
if (CollectionUtils.isEmpty(results)) {
throw new EmptyResultDataAccessException(1);
}
if (results.size() > 1) {
throw new IncorrectResultSizeDataAccessException(1, results.size());
}
return results.iterator().next();
}
The biggest difference is the use of RowMapper. mapRow method in SingleColumnRowMapper type. The mapping only takes the value of the first column as the result.:
// SingleColumnRowMapper.java
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
// Validate column count.
// Number of results returned from validation
ResultSetMetaData rsmd = rs.getMetaData();
int nrOfColumns = rsmd.getColumnCount();
if (nrOfColumns != 1) {
throw new IncorrectResultSetColumnCountException(1, nrOfColumns);
}
// Extract column value from JDBC ResultSet.
// Extract the first result for processing
Object result = getColumnValue(rs, 1, this.requiredType);
if (result != null && this.requiredType != null && !this.requiredType.isInstance(result)) {
// Extracted value does not match already: try to convert it.
try {
// Convert to corresponding type
return (T) convertValueToRequiredType(result, this.requiredType);
}
catch (IllegalArgumentException ex) {
throw new TypeMismatchDataAccessException(
"Type mismatch affecting row number " + rowNum + " and column type '" +
rsmd.getColumnTypeName(1) + "': " + ex.getMessage());
}
}
return (T) result;
}
protected Object getColumnValue(ResultSet rs, int index, @Nullable Class<?> requiredType) throws SQLException {
if (requiredType != null) {
return JdbcUtils.getResultSetValue(rs, index, requiredType);
}
else {
// No required type specified -> perform default extraction.
return getColumnValue(rs, index);
}
}
Corresponding type conversion function:
protected Object convertValueToRequiredType(Object value, Class<?> requiredType) {
// String type
if (String.class == requiredType) {
return value.toString();
}
else if (Number.class.isAssignableFrom(requiredType)) {
if (value instanceof Number) {
// Convert original Number to target Number class.
// Convert the entity of the original Number type to the Number class
return NumberUtils.convertNumberToTargetClass(((Number) value), (Class<Number>) requiredType);
}
else {
// Convert stringified value to target Number class.
// Convert String to Number class
return NumberUtils.parseNumber(value.toString(),(Class<Number>) requiredType);
}
}
else if (this.conversionService != null && this.conversionService.canConvert(value.getClass(), requiredType)) {
// Use the converter to convert. The default GenericConversionService
return this.conversionService.convert(value, requiredType);
}
else {
throw new IllegalArgumentException(
"Value [" + value + "] is of type [" + value.getClass().getName() +
"] and cannot be converted to required type [" + requiredType.getName() + "]");
}
}