【Spring】Spring 3.x企业应用开发实战(13)----Spring JDBC访问数据库

Spring JDBC是Spring所提供的持久层技术。他的目的是降低JDBC API门槛,以一种更直接、更简洁的方式使用JDBC API。

JdbcTemplate小试牛刀

 

package com.smart.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.CallableStatementCreatorFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.smart.domain.User;

@Repository
public class ViewSpaceDao 
{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public void initDb()
	{
		String sql="create table t_user_2(user_id int primary key,user_name varchar(60))";
		jdbcTemplate.execute(sql);
	}
	
	public void updateJdbc1()
	{
		final String sql="INSERT INTO t_user(user_name,password) VALUES(?,?)";
		Object[] params=new Object[]{"MonkeyKing","孫悟空"};
		jdbcTemplate.update(sql,params);
	}
	
	public void updateJdbc2()
	{
		final String sql="INSERT INTO t_user(user_name,password) VALUES(?,?)";
		Object[] params=new Object[]{"MonkeyKing","孫悟空"};
		jdbcTemplate.update(sql,params,new int[]{Types.VARCHAR,Types.VARCHAR});
	}
	
	//獲得主鍵的更新
	public  void updateJdbc3()
	{
		final String sql="INSERT INTO t_user(user_name,password) VALUES(?,?)";
		
		KeyHolder keyHolder=new GeneratedKeyHolder();//創建一個主鍵執有		
		jdbcTemplate.update(new PreparedStatementCreator() {			
			@Override
			public PreparedStatement createPreparedStatement(Connection arg0)
					throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps=arg0.prepareStatement(sql);
				ps.setString(1, "MonkeyKing");
				ps.setString(2, "孫悟空");
				return ps;
			}
		},keyHolder);
		//獲得主鍵并打印出主鍵
		System.out.println(keyHolder.getKey().intValue());
		//jdbcTemplate.
	}
	
	//批量更改數據
	public void batchUpdateSql(final List<User> users)
	{
		final String sql="INSERT INTO t_user(user_name,password) VALUES(?,?)";
		jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				User user=users.get(arg1);
				arg0.setString(1,user.getUserName());
				arg0.setString(2, user.getPassword());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return users.size();
			}
		});
	}
	
	//查詢單條數據集,多條只需修改返回類型和一些定義數據類型
	public User getDataSql(final int userId)
	{
		String sql="SELECT user_name,password from t_user where user_id=?";
		final User user=new User();
		jdbcTemplate.query(sql, new Object[]{userId},new RowCallbackHandler(){

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				user.setUserId(userId);
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
			}			
		});		
		return user;
	}
	
	//使用RowMapper<T>處理結果集
	public List<User> getUserById(final int startId,final int endId)
	{
		String sql=" SELECT user_name,password from t_user where user_id between ? and ?";
		
		return jdbcTemplate.query(sql, new Object[]{startId,endId}, new RowMapper<User>(){

			@Override
			public User mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				User user=new User();
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));				
				return user;
			}			
		});
	}
	
	//調用存儲過程
	public int getUserNum(final int userId)
	{
		String sql="{call P_GET_USER(?,?)}";//#1、調用存儲過程的SQL語句		
		Integer num=jdbcTemplate.execute(sql, new CallableStatementCallback<Integer>() {
			@Override
			public Integer doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.setInt(1, userId);//#2、綁定入參
				cs.registerOutParameter(2, Types.INTEGER);//#3、註冊輸出參數
				cs.execute();
				return cs.getInt(2);//#4、獲取輸出參數的值
			}			
		});
		return num;
	}
	
	//調用存儲過程
	public int getUserNum2(final int userId)
	{
		String sql = "{call P_GET_USER(?,?)}";// #1、調用存儲過程的SQL語句
		//通過CallableStatementCreatorFactory創建CallableStatementCreator
		CallableStatementCreatorFactory fac=new CallableStatementCreatorFactory(sql);
		fac.addParameter(new SqlParameter("userId", Types.INTEGER));//入參,userId表示邏輯名,可以是任意名
		fac.addParameter(new SqlOutParameter("endId",Types.INTEGER));//出參
		Map<String,Integer> paramsMap=new HashMap<String, Integer>();
		paramsMap.put("userId", userId);
		CallableStatementCreator csc=fac.newCallableStatementCreator(paramsMap);//創建一個CallableStatementCreator實例
		Integer num=jdbcTemplate.execute(csc, new CallableStatementCallback<Integer>() {
			@Override
			public Integer doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				return cs.getInt(2);
			}			
		});
		return num;		
	}
}

<!-- 扫描类包,将标注Spring注解的类自动转化成Bean,同时完成Bean的注入 -->
	<context:component-scan base-package="com.smart.dao"/>
	
	
	<!-- 定义一个使用DBCP实现的数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" p:driverClassName="com.mysql.jdbc.Driver" 
		p:url="jdbc:mysql://localhost:3306/sampledb"
		p:username="root"
		p:password=""/>	
	
	<!-- 定义JDBC模板Bean -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" 
		  p:dataSource-ref="dataSource"/><!-- 这里将dataSource注入到JdbcTemplate中 -->
	<!-- JdbcTemplate Bean将通过@Autowired自动注入到LoginLog和UserDao的Bean中 -->
	
	<!-- 配置事物管理器 -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" 
		  p:dataSource-ref="dataSource"/>
	
	<bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.commonsDbcpNativeJdbcExtractor" lazy-init="true"/>
	
	<!-- 設置本地JDBC對象抽取 -->
	<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" 
			lazy-init="true" 
			p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>
		  
	<!-- 通过AOP配置提供事物增强,让Service包下所有Bean的所有方法拥有事物 -->
	<aop:config proxy-target-class="true">
		<aop:pointcut expression="execution(* com.techman.service..*(..))" id="serviceMethod"/>
		<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceMethod"/>
	</aop:config>
	
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name=""/>
		</tx:attributes>
	</tx:advice>

BLOB/CLOB类型数据操作

 

 

package com.smart.dao;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.LobRetrievalFailureException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.util.FileCopyUtils;

import com.smart.domain.ViewPoint;

@Repository
public class ViewPointDao 
{
	@Autowired
	private JdbcTemplate jdbcTemplate;	
	@Autowired
	private LobHandler lobHandler;//定義LobHandler屬性
	@Autowired
    private DataFieldMaxValueIncrementer incre;
	
	//添加Lob字段數據
	public void addViewPoint(final ViewPoint viewPoint)
	{
		String sql="INSERT INTO t_view_point(point_id,space_id,point_name,ticket_price,img_file,description) VALUES(?,?,?,?,?,?)";
		
		jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
			
			@Override
			protected void setValues(PreparedStatement ps, LobCreator lobCreator)
					throws SQLException, DataAccessException {
				
				ps.setInt(1,incre.nextIntValue());//通過自增鍵指定主鍵值LobHandler屬性
				ps.setInt(2, viewPoint.getSpaceId());
				ps.setString(3, viewPoint.getPointName());
				ps.setDouble(4, viewPoint.getTicketPrice());
				lobCreator.setBlobAsBytes(ps, 5, viewPoint.getImgFile());//設置Blob字段
				lobCreator.setClobAsString(ps, 6, viewPoint.getDescription());//設置Clob字段
			}
		});
	}
	
	//以塊數據方式讀取Lob數據
	public List<ViewPoint> getImgFiles(final int spaceId)
	{
		String sql="SELECT point_id,img_file FROM t_view_point WHERE point_id=? and img_file is not null ";
		return jdbcTemplate.query(sql, new Object[]{spaceId},new RowMapper<ViewPoint>(){

			@Override
			public ViewPoint mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				// TODO Auto-generated method stub
				int pointId=rs.getInt(1);				
				byte[]attach=lobHandler.getBlobAsBytes(rs, 2);
				ViewPoint viewPoint=new ViewPoint();
				viewPoint.setPointId(pointId);
				viewPoint.setImgFile(attach);				
				return viewPoint;
			}
			
		});
	}
	
	//以流數據方式讀取Lob數據
	public void getImgFile(final int pointId,final OutputStream os)
	{
		String sql=" SELECT img_file FROM t_view_point where point_id=? ";
		jdbcTemplate.query(sql, new Object[]{pointId},new AbstractLobStreamingResultSetExtractor() {
			
			protected void handleNoRowFound()throws LobRetrievalFailureException
			{
				System.out.println("Not Found Result!");
			}
			@Override
			protected void streamData(ResultSet arg0) throws SQLException, IOException,
					DataAccessException {
				// TODO Auto-generated method stub
				InputStream is=lobHandler.getBlobAsBinaryStream(arg0, 1);
				if(is!=null)
				{
					FileCopyUtils.copy(is, os);
				}				
			}
		});
	}
}