Spring JDBC模版基本操作示例

Insert/Update/Delete：

Stringsql = " insertinto " + tableName + " (id,ipproleid,cpermissionid,iisdelete) " + " values( " + getSeqID(tableName) + " ,?,?,?) " ; Object[]values = new Object[] {domain.getIpproleid(), domain.getCpermissionid(),domain.getIisdelete()} ; this .getJdbcTemplate().update(sql,values);

Select：

Stringsql = " select*from " + tableName + " wherecpermissionid=?andipproleid=? " ; Object[]values = new Object[] {permissionID,pproleType} ; Listlist = this .getJdbcTemplate().query(sql,values, new BaseRowMapper(M2PpuserPermission. class )); return (list == null || list.size() == 0 ? null :(M2PpuserPermission)list . get ( 0 ));

public final class BaseRowMapperimplementsRowMapper { 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, new BatchPreparedStatementSetter() { 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 */ public class ExampleSpringLobDaoextendsJdbcDaoSupport { /**//** *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; } }