Spring JDBC模版基本操作示例

Insert/Update/Delete：

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

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; } }