Spring JDBC是Spring所提供的持久層技術。他的目的是降低JDBC API門檻,以一種更直接、更簡潔的方式使用JDBC API。
JdbcTemplate小試牛刀
[java]
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;
}
}
[html] view plaincopyprint?
<!-- 掃描類包,將標注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類型數據操作
[java]
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);
}
}
});
}
}
Android Notes 之 Activity生命周期的探索
Android開發--利用Matrix進行圖片操作
Android中Activity的生命周期探討
Android Studio如何修改快捷鍵
