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：

Stringsql="select*from"+tableName+"wherecpermissionid=?andipproleid=?";

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

Listlist=this.getJdbcTemplate().query(sql,values,

newBaseRowMapper(M2PpuserPermission.class));

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

.get(0));

publicfinalclassBaseRowMapperimplementsRowMapper{

privateClasscls;

/**//**

*@paramdao

*/

publicBaseRowMapper(Classcls){

this.cls=cls;

}

publicObjectmapRow(ResultSetrs,introw)throwsSQLException{

if(rs!=null){

returnRSUtil.toBean(rs,cls);

}else{

returnnull;

}

}

}

批量更新：

finalObject[]propValue=getColumnPropValue(domain);

finalObject[]propName=getColumnPropName();

StringINSERTPROPSQL="insertintoM_COMMON_COLUMN_PROP"

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

getJdbcTemplate().batchUpdate(INSERTPROPSQL,

newBatchPreparedStatementSetter(){

publicintgetBatchSize(){

returnpropName.length;

}

publicvoidsetValues(PreparedStatementps,inti)

throwsSQLException{

ps.setInt(1,id);

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

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

}

});

Clob/Blob：

/**//**

*@authorYunHongTao

*/

publicclassExampleSpringLobDaoextendsJdbcDaoSupport{

/**//**

*Commentfor<code>lobHandler</code>

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

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

*<beanid="nativeJdbcExtractor"

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

*<beanid="oracleLobHandler"

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

*<propertyname="nativeJdbcExtractor"><reflocal="nativeJdbcExtractor"/></property>

*</bean>

*<beanid="ExampleSpringLobDao"class="test.ExampleSpringLobDao">

*<propertyname="dataSource">

*<reflocal="dataSource"/>

*</property>

*<propertyname="lobHandler">

*<refbean="oracleLobHandler"/>

*</property>

*</bean>

*/

privateLobHandlerlobHandler;

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

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

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

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

*@paramid

*@paramname

*@paramtext

*@throwsDataAccessException

*/

publicvoidinsertClob(finalintid,finalStringname,finalStringtext)

throwsDataAccessException{

getJdbcTemplate().execute(

"INSERTINTOyuntest(id,name,text)VALUES(?,?,?)",

newAbstractLobCreatingPreparedStatementCallback(

this.lobHandler){

protectedvoidsetValues(PreparedStatementps,

LobCreatorlobCreator)throwsSQLException{

ps.setInt(1,id);

ps.setString(2,name);

lobCreator.setClobAsString(ps,3,text);

}

});

}

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

*@paramname

*@paramtext

*@paramid

*@throwsDataAccessException

*/

publicvoidupdateClob(finalStringname,finalStringtext,finalintid)

throwsDataAccessException{

getJdbcTemplate().execute(

"UPDATEyuntestsetname=?,text=?whereid=?",

newAbstractLobCreatingPreparedStatementCallback(

this.lobHandler){

protectedvoidsetValues(PreparedStatementps,

LobCreatorlobCreator)throwsSQLException{

ps.setString(1,name);

lobCreator.setClobAsString(ps,2,text);

ps.setInt(3,id);

}

});

}

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

*@return

*@throwsDataAccessException

*/

publicListgetClob()throwsDataAccessException{

returngetJdbcTemplate().query(

"SELECTid,name,textFROMyuntestwhereid=?",

newObject[]{newInteger(999999)},newRowMapper(){

publicObjectmapRow(ResultSetrs,introwNum)

throwsSQLException{

intid=rs.getInt(1);

Stringname=rs.getString(2);

Stringtext=lobHandler.getClobAsString(rs,3);

YunTestBeanvo=newYunTestBean();

vo.setId(id);

vo.setName(name);

vo.setText(text);

returnvo;

}

});

}

publicvoiddeleteClob(){

Stringsql="deletefromyuntestwhereid=999999";

getJdbcTemplate().execute(sql);

}

/**//**

*@returnReturnsthelobHandler.

*/

publicLobHandlergetLobHandler(){

returnlobHandler;

}

/**//**

*@paramlobHandler

*ThelobHandlertoset.

*/

publicvoidsetLobHandler(LobHandlerlobHandler){

this.lobHandler=lobHandler;

}

}