Spring JDBC模版基本操作示例 （转）

jdbc 模板基本操作的基本步骤：
1 数据库连接，返回全局使用的datasorce;
2 jdbc模板建立，全局使用，依赖database;
3自定义dao，对应一个配置文件(jdbcTemplate 属性 ) 依赖 jdbc模板。
4 控制通过 getBean对像得到jdbcTemplate.

user表
+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(15) | NO | PRI | NULL | auto_increment |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+


Insert：

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class TestDAOjdbc extends JdbcDaoSupport{

String tableName="user";
String sql = "insert into " + tableName+"(name)"+ "values(?)";

JdbcTemplate jt=getJdbcTemplate();

jt.update(sql,
new PreparedStatementSetter(){
public void setValues(PreparedStatement ps) throws SQLException{
ps.setString(1,"coco");
}
}
);
//删除
public void deldb(){
JdbcTemplate jt=getJdbcTemplate();
String sql="DELETE FROM user WHERE id=?";
jt.update(sql, new PreparedStatementSetter(){
public void setValues(PreparedStatement ps)throws SQLException{
ps.setInt(1,5);
}
});
}

//查询数据库多少行 信息 －－使用queryForInt()方法传回user表格中的数据数目
public int getCount(){
JdbcTemplate jt=getJdbcTemplate();
String sql="select count(*)from user";
int count=jt.queryForInt(sql);
return count;
}
//查询指定名字信息－－使用queryForObject()传回一个查询后的结果对象，例如传回一个String对象
public String getName(){
JdbcTemplate jt=getJdbcTemplate();
String sql="select name from user where id=10";
String name=(String)jt.queryForObject(sql, java.lang.String.class);
return name;
}

//将查询的多项数据信息填写到user Bean中
public List findUser(){
JdbcTemplate jt=getJdbcTemplate();
String sql="select * from user";
List list=jt.queryForList(sql);
List<user> userlist=new ArrayList<user>();

Iterator it=list.iterator();
while(it.hasNext()){
user uinfo=new user();
Map map=(Map)it.next();
uinfo.setId((Integer)map.get("id"));
uinfo.setName((String)map.get("name"));
userlist.add(uinfo);
}
return userlist;
}
//在查询到数据之后先作一些处理再传回，在RowCallback-Handler的processRow()方法中实现简单的ORM动作
//ORM--Object-Relational Mapping
//用new Object[]{id}代替 new PreparedStatementSetter()
public user find(Integer id){
final user user=new user();
final int iid=id;
JdbcTemplate jt=getJdbcTemplate();
String sql="select * from user where id = ?";
jt.query(sql,
// new PreparedStatementSetter(){
// public void setValues(PreparedStatement ps)throws SQLException{
// ps.setInt(1, iid);
//}}
new Object[]{id}
,new RowCallbackHandler(){
public void processRow(ResultSet rs)throws SQLException{
user.setId(new Integer(rs.getInt("id")));
user.setName(rs.getString("name"));
}
});
return user;
}


Select：

String sql = "select * from " + tableName + " where cpermissionid=? and ipproleid=?";

Object[] values = new Object[] { permissionID,pproleType };

List list = this.getJdbcTemplate().query(sql, values,

new BaseRowMapper(M2PpuserPermission.class));

return (list == null || list.size() == 0 ? null : (M2PpuserPermission) list

.get(0));


public final class BaseRowMapper implements RowMapper {


private Class cls;



/**//**

* @param dao

*/

public BaseRowMapper(Class cls) {

this.cls = cls;

}


public Object mapRow(ResultSet rs, int row) throws SQLException {

if (rs != null) {

return RSUtil.toBean(rs, cls);

} else {

return null;

}

}

}

批量更新：

final Object[] propValue = getColumnPropValue(domain);

final Object[] propName = getColumnPropName();

String INSERTPROPSQL = "insert into M_COMMON_COLUMN_PROP "

+ " (icolumnid, cpropname, cpropvalue) values(?,?,?)";

getJdbcTemplate().batchUpdate(INSERTPROPSQL,

new BatchPreparedStatementSetter() {

public int getBatchSize() {

return propName.length;

}


public void setValues(PreparedStatement ps, int i)

throws SQLException {

ps.setInt(1, id);

ps.setString(2, propName[i].toString());

ps.setString(3, propValue[i].toString());

}

});

Clob/Blob：


/**//**

* @author YunHongTao

*/


public class ExampleSpringLobDao extends JdbcDaoSupport {



/**//**

* Comment for <code>lobHandler</code>

* 处理blob、clob字段的对象 提供了方便的处理方法 一定要先初始化

* 在配置文件可以如下指定

* <bean id="nativeJdbcExtractor"

* class="org.springframework.jdbc.support.nativejdbc.WebLogicNativeJdbcExtractor" lazy-init="true"/>

* <bean id="oracleLobHandler"

* class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true">

* <property name="nativeJdbcExtractor"><ref local="nativeJdbcExtractor"/></property>

* </bean>

* <bean id="ExampleSpringLobDao" class="test.ExampleSpringLobDao">

* <property name="dataSource">

* <ref local="dataSource"/>

* </property>

* <property name="lobHandler">

* <ref bean="oracleLobHandler"/>

* </property>

* </bean>

*/

private LobHandler lobHandler;




private String text = "萨克雷发动机三卡领导及罚款司机阿东风口浪尖萨克路灯风纪扣拉萨定界符可滤色镜阿斗罚款滤色镜打伏击送达可分离\n"

+ "萨雷队交锋拉开三角枫框架萨克路风机司克拉风纪扣拉萨定界符可立即撒风口浪尖送达风\n"

+ "口浪尖斯大林咖啡碱四克拉定界符可怜三大件罚款来三大件分厘卡三大件罚款拉萨定界符\n";


/**//**插入clob字段的方法示例

* @param id

* @param name

* @param text

* @throws DataAccessException

*/

public void insertClob(final int id, final String name, final String text)

throws DataAccessException {

getJdbcTemplate().execute(

"INSERT INTO yuntest (id,name,text) VALUES (?, ?, ?)",

new AbstractLobCreatingPreparedStatementCallback(

this.lobHandler) {

protected void setValues(PreparedStatement ps,

LobCreator lobCreator) throws SQLException {

ps.setInt(1, id);

ps.setString(2, name);

lobCreator.setClobAsString(ps, 3, text);

}

});

}


/**//**更新clob字段的方法示例

* @param name

* @param text

* @param id

* @throws DataAccessException

*/

public void updateClob(final String name, final String text, final int id)

throws DataAccessException {

getJdbcTemplate().execute(

"UPDATE yuntest set name=?,text=? where id=?",

new AbstractLobCreatingPreparedStatementCallback(

this.lobHandler) {

protected void setValues(PreparedStatement ps,

LobCreator lobCreator) throws SQLException {

ps.setString(1, name);

lobCreator.setClobAsString(ps, 2, text);

ps.setInt(3, id);


}

});


}


/**//**查询clob字段的方法示例

* @return

* @throws DataAccessException

*/

public List getClob() throws DataAccessException {



return getJdbcTemplate().query(

"SELECT id,name,text FROM yuntest where id=? ",

new Object[] { new Integer(999999) }, new RowMapper() {

public Object mapRow(ResultSet rs, int rowNum)

throws SQLException {

int id = rs.getInt(1);

String name = rs.getString(2);

String text = lobHandler.getClobAsString(rs, 3);

YunTestBean vo = new YunTestBean();

vo.setId(id);

vo.setName(name);

vo.setText(text);

return vo;

}

});

}


public void deleteClob() {

String sql = "delete from yuntest where id = 999999";

getJdbcTemplate().execute(sql);

}


/**//**

* @return Returns the lobHandler.

*/

public LobHandler getLobHandler() {

return lobHandler;

}


/**//**

* @param lobHandler

* The lobHandler to set.

*/

public void setLobHandler(LobHandler lobHandler) {

this.lobHandler = lobHandler;

}

}


转至：http://blog.163.com/circle_man/blog/static/5060561020071026863490/