Spring 3.x企业应用开发实战(13)----Spring JDBC访问数据库
Spring JDBC是Spring所提供的持久层技术。他的目的是降低JDBC API门槛,以一种更直接、更简洁的方式使用JDBC API。
JdbcTemplate小试牛刀
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;@Repositorypublic class ViewPointDao {@Autowiredprivate JdbcTemplate jdbcTemplate;@Autowiredprivate 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) {@Overrideprotected 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>(){@Overridepublic ViewPoint mapRow(ResultSet rs, int rowNum)throws SQLException {// TODO Auto-generated method stubint 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!");}@Overrideprotected void streamData(ResultSet arg0) throws SQLException, IOException,DataAccessException {// TODO Auto-generated method stubInputStream is=lobHandler.getBlobAsBinaryStream(arg0, 1);if(is!=null){FileCopyUtils.copy(is, os);}}});}}