- 未整合之前使用JDBC
public class JdbcDemo {
/**
* @param args
* @throws SQLException
*/
@SuppressWarnings("cast")
public static void main(String[] args) throws Exception {
PreparedStatement pstmt = null;
Connection c = null;
try {
//加载驱动, 把Driver类加载进内存初始化静态资源
Class.forName("com.mysql.jdbc.Driver");
c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/pagetron?characterEncoding=UTF-8", "root", "root");
String sql = "insert into jdbctest values(?,?)";
pstmt = (PreparedStatement) c.prepareStatement(sql);
pstmt.setInt(1, 1);
pstmt.setString(2,"ef");
pstmt.executeUpdate();
}finally{
if(pstmt!=null) {
pstmt.close();
}
c.close();
}
}
}
- Spring 整合JDBC
public class SpringIntegrationJDBC {
private JdbcTemplate jdbcTemplate;
//用连接池初始化JdbcTemplate
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void save() {
jdbcTemplate.update("insert into jdbctest values(?,?)", new Object[] {1,"23"});
}
public static void main(String[] args) {
ApplicationContext act = new ClassPathXmlApplicationContext("jdbcBean.xml");
SpringIntegrationJDBC executor = (SpringIntegrationJDBC) act.getBean("executor");
executor.save();
}
spring 配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd" >
<!-- 连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/pagetron"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
<!-- 连接池启动时初始连接 -->
<property name="initialSize" value="1"></property>
<!-- 连接池最大值 -->
<property name="maxActive" value="300"></property>
<!-- 最大空闲值,连接池会慢慢将不用的连接释放掉,最少保存maxIdle -->
<property name="maxIdle" value="2"></property>
<!-- 最小空闲连接,当连接池里的连接少于阙值，连接池会去申请一些连接 -->
<property name="minIdle" value="1"></property>
</bean>
<bean id="executor" class="spring.jdbc.SpringIntegrationJDBC" >
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
可以看到最终的执行由jdbcTemplate 完成,jdbcTemplate 封装了JDBC，而jdbcTemplate由spring-jdbc 提供
- jdbcTemplate 的update
public int update(String sql, PreparedStatementSetter pss) throws DataAccessException {
return update(new SimplePreparedStatementCreator(sql), newArgPreparedStatementSetter(args));
}
SimplePreparedStatementCreator对sql进行了封装
newArgPreparedStatementSetter方法使用ArgPreparedStatementSetter封装了参数
protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
throws DataAccessException {
logger.debug("Executing prepared SQL update");
//调用了execute方法执行sql,
return execute(psc, new PreparedStatementCallback<Integer>() {
public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
try {
//设置所需的所有参数
if (pss != null) {
pss.setValues(ps);
}
int rows = ps.executeUpdate();
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " + rows + " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}
});
}
调用execute执行sql ,传入封装了sql的PreparedStatementCreator，执行sql的PreparedStatementCallback
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 + "]" : ""));
}
//取得连接，如果没有配置事务,那么每次去从dataSource中取得一个新的连接
Connection con = DataSourceUtils.getConnection(getDataSource());
//预编译对象
PreparedStatement ps = null;
try {
Connection conToUse = con;
//是否配置了自定义的jdbc连接
if (this.nativeJdbcExtractor != null &&
this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) {
conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
}
//取得预编译对象,
ps = psc.createPreparedStatement(conToUse);
//ps 应用设定的输入参数,如fetchSize : 调用rs.next时就取得fetchSize1个结果,这样下次就可以直接从缓存中获取了
applyStatementSettings(ps);
PreparedStatement psToUse = ps;
if (this.nativeJdbcExtractor != null) {
psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps);
}
//执行sql.
T result = action.doInPreparedStatement(psToUse);
// 打印数据库的警告信息
handleWarnings(ps);
return result;
}
catch (SQLException ex) {
//释放连接
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
String sql = getSql(psc);
psc = null;
JdbcUtils.closeStatement(ps);
ps = null;
// ---4
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex);
}
finally {
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
JdbcUtils.closeStatement(ps);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
execute 回调了PreparedStatementCallback的doInPreparedStatement方法执行sql
public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
try {
if (pss != null) {
//设置参数
pss.setValues(ps);
}
int rows = ps.executeUpdate();
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " + rows + " rows");
}
return rows;
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}
});
pss.setValues(ps);用于设置参数，一般我们直接写jdbc设置参数时类型会自己设置
pstmt.setInt(1, 1);
pstmt.setString(2,"ef");
pstmt.executeUpdate();
而jdbcTemplate就需要根据参数判断类型了
private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName,
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.LONGVARCHAR ||
(sqlType == Types.CLOB && isStringValue(inValue.getClass()))) {
ps.setString(paramIndex, inValue.toString());
}
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.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) {
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);
}
}
如果未配置事务,jdbcTemplate每次都从连接池中随机取得connection
Connection connection = jdbcTemplate.getDataSource().getConnection();
Connection connection1 = jdbcTemplate.getDataSource().getConnection();
System.out.println(connection == connection1);
jdbcTemplate.update("insert into jdbctest values(?,?)", new Object[] {3,"23"});
float f = 1/0;
jdbcTemplate.update("insert into jdbctest values(?,?)", new Object[] {2,"22"});
结果时false,上面的insert会执行成功，不会rollback
1531
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
