1.原始实现
在jdbcTemplate之前，我们都是使用原始的方式来实现jdbc的相关操作，下面先看下原始的操作步骤
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
String sql = "update blog set name = ? where id=?";
try {
// 1.获取连接
Connection connection = DriverManager.getConnection(url, username, password);
// 2.创建 preparedStatement
PreparedStatement prepareStatement = connection.prepareStatement(sql);
// 3.初始化参数
prepareStatement.setString(1, "lucy");
prepareStatement.setInt(2, 1);
// 4.执行update
prepareStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}主要分四步：获取连接、创建preparedStatement、封装参数、执行
实际jdbcTemplate的实现也是按照这个步骤来的，只不过封装的更好
2.jdbcTemplate的实现
1）创建JdbcConfig类，用于创建DataSource、JdbcTemplate
@Configuration
@ComponentScan(basePackages={"jdbc"})
public class JdbcConfig {
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource){
return new JdbcTemplate(dataSource);
}
@Bean
public DataSource dataSource(){
try {
return new SimpleDriverDataSource(new com.mysql.jdbc.Driver(), "jdbc:mysql://localhost:3306/test", "root", "root");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}2）创建JdbcOper类，用于具体实现
@Component
public class JdbcOper {
@Autowired
private JdbcTemplate jdbcTemplate;
public void query(){
String sql = "select id,name,url from blog";
jdbcTemplate.query(sql, new ResultSetExtractor() {
@Override
public Blog extractData(ResultSet rs) throws SQLException, DataAccessException {
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.println(id+":"+name);
}
return null;
}
});
/*Blog blog = jdbcTemplate.queryForObject(sql, Blog.class);
System.out.println(blog);*/
}
public void update(){
String sql = "update blog set name = ? where id=?";
jdbcTemplate.update(sql, new Object[]{"jack",1});
}
}3）创建Test类，用于测试
public static void main(String[] args) {
AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext(JdbcConfig.class);
JdbcOper bean = context.getBean(JdbcOper.class);
// bean.query();
bean.update();
}3.jdbcTemplate源码解析
笔者使用的版本为spring-jdbc-4.3.8.RELEASE.jar
jdbcTemplate.update方法主要代码如下
protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
return execute(psc, new PreparedStatementCallback() {
@Override
public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
try {
if (pss != null) {
// 3.初始化参数
pss.setValues(ps);
}
// 4.执行update
int rows = ps.executeUpdate();
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " + rows + " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}
});
}经过两步的对SQL和参数的封装之后，直接到该方法，最终执行execute方法，execute方法的第二个参数为一个回调函数，最终的执行会到这，下面看一下execute方法
public T execute(PreparedStatementCreator psc, PreparedStatementCallback action)
throws DataAccessException {
...
// 1.获取连接
Connection con = DataSourceUtils.getConnection(getDataSource());
PreparedStatement ps = null;
try {
Connection conToUse = con;
if (this.nativeJdbcExtractor != null &&
this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) {
conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
}
// 2.创建 preparedStatment
ps = psc.createPreparedStatement(conToUse);
applyStatementSettings(ps);
PreparedStatement psToUse = ps;
if (this.nativeJdbcExtractor != null) {
psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps);
}
// 3.直接调用action回调方法，参考上update() 剩余两步的封装参数和执行update都在回调方法中执行
T result = action.doInPreparedStatement(psToUse);
handleWarnings(ps);
return result;
}下面逐步解析这四步操作：
1）get connection
进入DataSourceUtils类进行查看，可看到如下代码
public static Connection doGetConnection(DataSource dataSource) throws SQLException {
...
logger.debug("Fetching JDBC Connection from DataSource");
// 真正的实现也就是这一句
Connection con = dataSource.getConnection();
...
return con;
}2）create preparedStatement
默认使用的是SimplePreparedStatementCreator类，直接查看该类的实现
private static class SimplePreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
private final String sql;
public SimplePreparedStatementCreator(String sql) {
Assert.notNull(sql, "SQL must not be null");
this.sql = sql;
}
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
// 真正实现的就是这句话
return con.prepareStatement(this.sql);
}
@Override
public String getSql() {
return this.sql;
}
}3）init parm
封装参数的过程在update的回调函数中，也就是PreparedStatementCallback
protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
return execute(psc, new PreparedStatementCallback() {
@Override
public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
try {
if (pss != null) {
// 也就是这句话
pss.setValues(ps);
}
...
}
...
}
});
}可知默认使用的是newArgPreparedStatementSetter的setValues实现，查看该类实现如下
public void setValues(PreparedStatement ps) throws SQLException {
if (this.args != null) {
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 {
// 真正实现在这
StatementCreatorUtils.setParameterValue(ps, parameterPosition, SqlTypeValue.TYPE_UNKNOWN, argValue);
}
}继续跟踪，最终实现在StatementCreatorUtils类中的setValue方法中
private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName,
Integer scale, Object inValue) throws SQLException {}
具体实现为：
if (isStringValue(inValue.getClass())) {
ps.setString(paramIndex, inValue.toString());
}
else {
// Fall back to generic setObject call without SQL type specified.
ps.setObject(paramIndex, inValue);
}至此，对preparedStatement参数的封装结束
4）execute update
也就是回调函数中的
protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
return execute(psc, new PreparedStatementCallback() {
@Override
public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
try {
...
// 直接执行update操作
int rows = ps.executeUpdate();
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " + rows + " rows");
}
return rows;
}
...
}
});
}至此，update的源码分析解析，而至于其他操作，基本类似，也都是在回调函数中执行真正的execute操作，而对获取连接、封装参数等行为则抽象为公共方法
使用这种方式的好处就是，用户可以不关心公共行为，而只关心SQL及结果封装即可，这就是这种模板模式的好处
