注意，读完本篇文章需要很长很长时间
传统JDBC
相信大家对传统的jdbc已经很熟悉了，无非就是下面这个流程
代码语言：javascript复制//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2. 获得数据库连接
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.操作数据库
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT name, age from user");
//4.处理返回值
while(rs.next()){
System.out.println("名字:" rs.getString("name") " 年龄:" rs.getInt("age"));
}使用SpringJDBC
引入maven依赖
代码语言：javascript复制<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.0.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.18</version>
</dependency>实体类
代码语言：javascript复制public class User {
private int id;
private String name;
private int age;
//getter、setter省略
}返回值处理类
代码语言：javascript复制public class UserRowMapper implements RowMapper {
@Nullable
public Object mapRow(ResultSet resultSet, int i) throws SQLException {
User user=new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
}业务处理类
代码语言：javascript复制public interface JDBCService {
public void queryById(int id);
public void updateNameById(int id,String name);
}
public class JDBCServiceImpl implements JDBCService {
private JdbcTemplate jdbcTemplate;
public JDBCServiceImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void queryById(int id) {
List<User> list = jdbcTemplate.query("select id,name,age from user where id=?", new Object[]{id}, new UserRowMapper());
if (list.size() > 0) {
System.out.println("id 为" id "的用户名为：" list.get(0).getName());
}
}
public void updateNameById(int id, String name) {
jdbcTemplate.update("update user set name=? where id=?", new Object[]{name, id}, new UserRowMapper());
}
}配置类
代码语言：javascript复制@Configuration
public class JDBCConfig {
@Bean
public DruidDataSource druidDataSource(){
DruidDataSource druidDataSource=new DruidDataSource();
druidDataSource.setUsername("root");
druidDataSource.setPassword("123456");
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql://172.16.40.159:3306/cfkk?characterEncoding=utf-8&useSSL=false");
return druidDataSource;
}
@Bean
public JDBCService jdbcService(DruidDataSource druidDataSource){
JdbcTemplate jdbcTemplate=new JdbcTemplate(druidDataSource);
JDBCService jdbcService=new JDBCServiceImpl(jdbcTemplate);
return jdbcService;
}
}启动类
代码语言：javascript复制public class JDBCDemo {
public static void main (String args[]){
ApplicationContext context = new AnnotationConfigApplicationContext("cn.shiyujun.config");
JDBCService jdbcService= context.getBean(JDBCService.class);
jdbcService.updateNameById(1,"李四");
jdbcService.queryById(1);
}
}至此Deno工程搭建完毕，有需要源码的同学可以从下方地址获取 https://github.com/shiyujun/spring-framework
update
我们首先来分析一下update方法的实现
参数封装
代码语言：javascript复制public int update(String sql, @Nullable Object... args) throws DataAccessException {
return update(sql, newArgPreparedStatementSetter(args));
}
public int update(String sql, @Nullable PreparedStatementSetter pss) throws DataAccessException {
return update(new SimplePreparedStatementCreator(sql), pss);
}可以看到，首先使用了ArgPreparedStatementSetter对参数进行了一层封装，然后又使用了SimplePreparedStatementCreator对SQL进行了封装
核心逻辑
代码语言：javascript复制protected int update(final PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
return updateCount(execute(psc, ps -> {
try {
if (pss != null) {
//设置所需的全部参数
pss.setValues(ps);
}
//调用jdbc的更新方法
int rows = ps.executeUpdate();
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " rows " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}));
}上方这个方法是update的核心逻辑了，这里面的代码不是太多，主要涉及几个核心的逻辑
获取更新条数updateCount
进入方法以后可以看到这个方法是对execute方法的integer返回值的一个封装，也就是此次修改的行数
private static int updateCount(@Nullable Integer result) {
Assert.state(result != null, "No update count");
return result;
}前置方法execute
这里完成了传统JDBC的前两步加载驱动和获取连接，
代码语言：javascript复制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 "]" : ""));
}
//根据具体的连接池组件获取数据库连接，这里就不深入研究了，放到以后的连接池源码解析里
Connection con = DataSourceUtils.getConnection(obtainDataSource());
PreparedStatement ps = null;
try {
//应该对这个PreparedStatement印象很深刻了
ps = psc.createPreparedStatement(con);
applyStatementSettings(ps);
//调用回调函数也就是update方法中execute的lambda表达式里的逻辑
T result = action.doInPreparedStatement(ps);
//警告处理
handleWarnings(ps);
return result;
}
catch (SQLException ex) {
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
String sql = getSql(psc);
// 释放资源
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);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}执行更新
现在就进入了最后的逻辑了
代码语言：javascript复制protected int update(final PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
return updateCount(execute(psc, ps -> {
try {
if (pss != null) {
// 往下看
pss.setValues(ps);
}
//调用jdbc的方法执行更新
int rows = ps.executeUpdate();
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " rows " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}));
}请求参数设置
代码语言：javascript复制public void setValues(PreparedStatement ps) throws SQLException {
int parameterPosition = 1;
if (this.args != null && this.argTypes != null) {
//遍历参数
for (int i = 0; i < this.args.length; i ) {
Object arg = this.args[i];
//如果是集合的话则递归处理
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 {
//核心方法
doSetValue(ps, parameterPosition, this.argTypes[i], arg);
parameterPosition ;
}
}
}
}
protected void doSetValue(PreparedStatement ps, int parameterPosition, int argType, Object argValue)
throws SQLException {
StatementCreatorUtils.setParameterValue(ps, parameterPosition, argType, argValue);
}
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 (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 {
//往下看
setValue(ps, paramIndex, sqlTypeToUse, typeNameToUse, scale, inValueToUse);
}
}
private static void setValue(PreparedStatement ps, int paramIndex, int sqlType,
@Nullable String typeName, @Nullable Integer scale, Object inValue) throws SQLException {
if (inValue instanceof SqlTypeValue) {
((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName);
}
else if (inValue instanceof SqlValue) {
((SqlValue) inValue).setValue(ps, paramIndex);
}
else if (sqlType == Types.VARCHAR || sqlType == Types.NVARCHAR ||
sqlType == Types.LONGVARCHAR || sqlType == Types.LONGNVARCHAR) {
ps.setString(paramIndex, inValue.toString());
}
else if ((sqlType == Types.CLOB || sqlType == Types.NCLOB) && isStringValue(inValue.getClass())) {
String strVal = inValue.toString();
if (strVal.length() > 4000) {
// Necessary for older Oracle drivers, in particular when running against an Oracle 10 database.
// Should also work fine against other drivers/databases since it uses standard JDBC 4.0 API.
if (sqlType == Types.NCLOB) {
ps.setNClob(paramIndex, new StringReader(strVal), strVal.length());
}
else {
ps.setClob(paramIndex, new StringReader(strVal), strVal.length());
}
return;
}
// Fallback: regular setString binding
ps.setString(paramIndex, strVal);
}
else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
if (inValue instanceof BigDecimal) {
ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
}
else if (scale != null) {
ps.setObject(paramIndex, inValue, sqlType, scale);
}
else {
ps.setObject(paramIndex, inValue, sqlType);
}
}
else if (sqlType == Types.BOOLEAN) {
if (inValue instanceof Boolean) {
ps.setBoolean(paramIndex, (Boolean) inValue);
}
else {
ps.setObject(paramIndex, inValue, Types.BOOLEAN);
}
}
else if (sqlType == Types.DATE) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Date) {
ps.setDate(paramIndex, (java.sql.Date) inValue);
}
else {
ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
}
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
}
else {
ps.setObject(paramIndex, inValue, Types.DATE);
}
}
else if (sqlType == Types.TIME) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Time) {
ps.setTime(paramIndex, (java.sql.Time) inValue);
}
else {
ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime()));
}
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal);
}
else {
ps.setObject(paramIndex, inValue, Types.TIME);
}
}
else if (sqlType == Types.TIMESTAMP) {
if (inValue instanceof java.util.Date) {
if (inValue instanceof java.sql.Timestamp) {
ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue);
}
else {
ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
}
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
}
else {
ps.setObject(paramIndex, inValue, Types.TIMESTAMP);
}
}
else if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER &&
"Oracle".equals(ps.getConnection().getMetaData().getDatabaseProductName()))) {
if (isStringValue(inValue.getClass())) {
ps.setString(paramIndex, inValue.toString());
}
else if (isDateValue(inValue.getClass())) {
ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
}
else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
}
else {
// Fall back to generic setObject call without SQL type specified.
ps.setObject(paramIndex, inValue);
}
}
else {
// Fall back to generic setObject call with SQL type specified.
ps.setObject(paramIndex, inValue, sqlType);
}
}至此update方法更新分析完毕
query
代码语言：javascript复制public <T> List<T> query(String sql, @Nullable Object[] args,
RowMapper<T> rowMapper) throws DataAccessException {
return result(query(sql, args, new
RowMapperResultSetExtractor<>(rowMapper)));
}首先最外层的result方法没有什么特别的，就是对返回值的处理
代码语言：javascript复制private static <T> T result(@Nullable T result) {
Assert.state(result != null, "No result");
return result;
}参数封装
接着同样是封装请求参数
代码语言：javascript复制public <T> T query(String sql, @Nullable Object[] args, ResultSetExtractor<T> rse) throws DataAccessException {
return query(sql, newArgPreparedStatementSetter(args), rse);
}
public <T> T query(String sql, @Nullable PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException {
return query(new SimplePreparedStatementCreator(sql), pss, rse);
}核心查询
代码语言：javascript复制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");
//这里的execute的逻辑与update是一样的
return execute(psc, new PreparedStatementCallback<T>() {
@Override
@Nullable
public T doInPreparedStatement(PreparedStatement ps) throws SQLException {
ResultSet rs = null;
try {
if (pss != null) {
// 同上
pss.setValues(ps);
}
rs = ps.executeQuery();
//这里是重点
return rse.extractData(rs);
}
finally {
JdbcUtils.closeResultSet(rs);
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}
});
}处理返回值
因为修改的时候只需要返回一个int值修改的条数就ok了，但是查询的时候各种查询的类型什么的就不一样了。所以在这需要单独处理一下
代码语言：javascript复制@Override
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()) {
//在这里就是调用的我们一开始定义的UserRowMapper的mapRow方法
results.add(this.rowMapper.mapRow(rs, rowNum ));
}
return results;
}
