Spring之JDBC模板(手写)

一 模板父类: SpringBaseDAO.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.lob.OracleLobHandler;
import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
import org.springframework.jdbc.support.nativejdbc.WebLogicNativeJdbcExtractor;


public class SpringBaseDAO extends JdbcDaoSupport{		
	
	private NativeJdbcExtractor nativeJdbcExtractor;	
	protected OracleLobHandler lobHandler;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private SimpleJdbcTemplate simpleJdbcTemplate;
	

	public SpringBaseDAO() {
		setDataSource(ConnectionUtils.getDataSource());
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(ConnectionUtils.getDataSource());
		simpleJdbcTemplate = new SimpleJdbcTemplate(ConnectionUtils.getDataSource());
		nativeJdbcExtractor = new WebLogicNativeJdbcExtractor();
		lobHandler = new OracleLobHandler();
		lobHandler.setNativeJdbcExtractor(nativeJdbcExtractor);
		getJdbcTemplate().setNativeJdbcExtractor(nativeJdbcExtractor);		
	}	

	public void close(Connection childConnection) throws SQLException {
		releaseConnection(childConnection);
	}

	public void close(Statement childStatement) throws SQLException {
		if (null != childStatement) {
			childStatement.close();
			childStatement = null;
		}
	}

	public void close(CallableStatement childCallStatement) throws SQLException {
		if (null != childCallStatement) {
			childCallStatement.close();
			childCallStatement = null;
		}
	}

	public void close(PreparedStatement childPsmt) throws SQLException {
		if (null != childPsmt) {
			childPsmt.close();
			childPsmt = null;
		}
	}

	public void close(ResultSet childRs) throws SQLException {
		if (null != childRs) {
			childRs.close();
			childRs = null;
		}
	}

	public void close(Connection childConnection, Statement childStatement)
			throws SQLException {
		close(childConnection);
		close(childStatement);
	}

	public void close(Connection childConnection, PreparedStatement childPsmt)
			throws SQLException {
		close(childConnection);
		close(childPsmt);
	}

	public void close(Connection childConnection, ResultSet childRs)
			throws SQLException {
		close(childConnection);
		close(childRs);
	}

	public void closeChild(Connection childConnection,
			Statement childStatement, ResultSet childRs) throws SQLException {
		close(childConnection, childStatement);
		close(childRs);
	}

	public void close(Connection childConnection, PreparedStatement childPsmt,
			ResultSet childRs) throws SQLException {
		close(childConnection, childPsmt);
		close(childRs);
	}

	public void close(PreparedStatement childPsmt, ResultSet childRs)
			throws SQLException {
		close(childPsmt);
		close(childRs);
	}

	public NativeJdbcExtractor getNativeJdbcExtractor() {
		return nativeJdbcExtractor;
	}

	public void setNativeJdbcExtractor(NativeJdbcExtractor nativeJdbcExtractor) {
		this.nativeJdbcExtractor = nativeJdbcExtractor;
	}

	public OracleLobHandler getLobHandler() {
		return lobHandler;
	}

	public void setLobHandler(OracleLobHandler lobHandler) {
		this.lobHandler = lobHandler;
	}

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setNamedParameterJdbcTemplate(
			NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}

	public SimpleJdbcTemplate getSimpleJdbcTemplate() {
		return simpleJdbcTemplate;
	}

	public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
		this.simpleJdbcTemplate = simpleJdbcTemplate;
	}
}

二 应用类CGDAOImpl.java

public interface CGDAO {
	
	public boolean save(CG cg);
	public boolean update(CG cg);
	public boolean del(CG cg);
	public CG get(CG cg);
	public List<CG> getCgData(String querySql);	

}

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.stereotype.Component;

@Component("cgDao")
public class CGDAOImpl extends SpringBaseDAO implements CGDAO {

	private static final String INSERT = "insert into COMMUNICATIONGROUPINGS(CGID,CGNAME,CGCOMMENT,USERID,USERNAME,CGTIME,CGIMAGE,CGTXT,IMGPATH,TXTPATH)VALUES(?,?,?,?,?,?,?,?,?,?)";
	private static final String UPDATE = "update COMMUNICATIONGROUPINGS set CGNAME=?,CGCOMMENT=?,CGTIME=?,CGIMAGE=?,CGTXT=?,IMGPATH=?,TXTPATH=? where CGID=?";
	private static final String GET = "select t.*,'' totalItems ,'' num from COMMUNICATIONGROUPINGS t where t.CGID=?";
	private static final String DEL = "delete COMMUNICATIONGROUPINGS t where t.cgid=?";
	
	public boolean del(CG cg) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("cgid", cg.getCgId());
		return getJdbcTemplate().update(DEL, cg.getClass(), params) > 0;
	}

	@SuppressWarnings("unchecked")
	public CG get(CG cg) {
		CG dbCg = (CG) getJdbcTemplate().queryForObject(GET, new CGRowMapper(lobHandler),
				cg.getCgId());
		return dbCg;
	}

	@SuppressWarnings("unchecked")
	public List<CG> getCgData(String querySql) {
		List<CG> cgList = getJdbcTemplate().query(querySql, new CGRowMapper(lobHandler));
		return cgList;
	}

	public boolean save(final CG cg) {
		//abstractLobCreatingPreparedStatementCallback
		// return 0 < getJdbcTemplate().update(INSERT,
		// cg.getCgId(),cg.getCgName(),cg.getCgComment(),cg.getCgUserId(),cg.getCgUserName(),cg.getCgDateTime());		
		return getJdbcTemplate().execute(INSERT,new CGLobCreatingPreparedStatementCallback(lobHandler,cg)) > 0;
	}

	public boolean update(final CG cg) {
		/*return getJdbcTemplate().update(UPDATE, cg.getCgName(),
				cg.getCgComment(), cg.getCgDateTime(), cg.getCgId()) > 0;*/
		return getJdbcTemplate().execute(UPDATE,
				new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
					// ②
					protected void setValues(PreparedStatement ps, LobCreator lobCreator)
							throws SQLException {
						int i=0;
						ps.setString(++i, cg.getCgName());
						ps.setString(++i, cg.getCgComment());
						ps.setString(++i, cg.getCgUserId());
						ps.setString(++i, cg.getCgUserName());
						ps.setString(++i, cg.getCgDateTime());
						// ③ 设置 BLOB 字段
						lobCreator.setBlobAsBytes(ps, ++i, cg.getCgImg());
						// ④ 设置 CLOB 字段
						lobCreator.setClobAsString(ps, ++i, cg.getCgTxt());
						ps.setString(++i, cg.getImgPath());
						ps.setString(++i, cg.getTxtPath());
						ps.setString(++i, cg.getCgId());

					}
				}) > 0;
	}
}

三 CGRowMapper.java

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.lob.AbstractLobHandler;

@SuppressWarnings("unchecked")
public class CGRowMapper implements RowMapper {	
	
	private AbstractLobHandler lobHandler;
	
	public CGRowMapper(){}
	
	public CGRowMapper(AbstractLobHandler lobHandler){
		this.lobHandler = lobHandler;
	}
	
	// 默认已经执行rs.next(),可以直接取数据
	public Object mapRow(ResultSet rs, int index) throws SQLException {		
		CG cg = new CG(
				rs.getString("CGID"),
				rs.getString("CGNAME"),
				rs.getString("CGCOMMENT"),
				rs.getString("CGTIME"),
				rs.getString("USERID"),
				rs.getString("USERNAME"),	
				rs.getString("IMGPATH"),
				rs.getString("TXTPATH"),	
				lobHandler.getBlobAsBytes(rs, "CGIMAGE"),				
				lobHandler.getClobAsString(rs, "CGTXT"),	
				rs.getString("totalItems"),
				rs.getString("num")
		);		
		return cg;
	}
}

四 applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/aop
           http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
           http://www.springframework.org/schema/tx 
           http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">


	<context:annotation-config />
	<context:component-scan base-package="com.xxx" />

	<context:property-placeholder location="classpath:db.properties" />


	<bean id="dataSource"
		class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName">
			<value>${DbJNDI}</value>
		</property>
		<property name="resourceRef">
			<value>${RESOURCEREF}</value>
		</property>
		<property name="jndiEnvironment">
			<props>
				<prop key="java.naming.provider.url">
					${Context.PROVIDER_URL}
				</prop>
				<prop key="java.naming.factory.initial">
					${Context.INITIAL_CONTEXT_FACTORY}
				</prop>
			</props>
		</property>
	</bean>

	<!-- 
		<bean id="dataSource"	
		class="org.apache.commons.dbcp.BasicDataSource"	destroy-method="close">				
		property name="driverClassName" value="org.gjt.mm.mysql.Driver"/>
		<property name="url" value="jdbc:mysql://localhost/spring_datasource" />		
		<property name="driverClassName" value="${dbdriver}" />
		<property name="url" value="${constr}" />
		<property name="username" value="${username}" />
		<property name="password" value="${password}" />
		</bean>
	-->



	<bean id="nativeJdbcExtractor"
		class="org.springframework.jdbc.support.nativejdbc.WebLogicNativeJdbcExtractor"
		lazy-init="true" />
	<bean id="lobHandler"
		class="org.springframework.jdbc.support.lob.OracleLobHandler"
		lazy-init="true">
		<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
	</bean>
	<bean id="defaultLobHandler"
		class="org.springframework.jdbc.support.lob.DefaultLobHandler"
		lazy-init="true" />

	<bean id="jdbcTemplate"
		class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>


	<bean id="txManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>

	<aop:config>
		<aop:pointcut id="bussinessService"
			expression="execution(public * com.xxx.*.service.*.*(..))" />
		<aop:advisor pointcut-ref="bussinessService"
			advice-ref="txAdvice" />
	</aop:config>

	<tx:advice id="txAdvice" transaction-manager="txManager">
		<tx:attributes>
			<tx:method name="*" />
			<tx:method name="add*" propagation="REQUIRED" />
			<tx:method name="del*" propagation="REQUIRED" />
			<tx:method name="edit*" propagation="REQUIRED" />
		</tx:attributes>
	</tx:advice>
</beans>

sql文件如下:

-- Create table
create table COMMUNICATIONGROUPINGS
(
  cgid      VARCHAR2(32),
  cgname    VARCHAR2(60),
  cgcomment VARCHAR2(4000),
  userid    VARCHAR2(32),
  username  VARCHAR2(60),
  cgtime    VARCHAR2(40)
)
-- Add comments to the table 
comment on table COMMUNICATIONGROUPINGS
  is '通讯录分组';
-- Add comments to the columns 
comment on column COMMUNICATIONGROUPINGS.cgid
  is '主键ID';
comment on column COMMUNICATIONGROUPINGS.cgname
  is '通讯录组名';
comment on column COMMUNICATIONGROUPINGS.cgcomment
  is '备注';
comment on column COMMUNICATIONGROUPINGS.userid
  is '创建人ID';
comment on column COMMUNICATIONGROUPINGS.username
  is '创建人姓名';
comment on column COMMUNICATIONGROUPINGS.cgtime
  is '		创建时间<YYYY-MM-DD hh:mm:ss>';