几个使用JDBC Template常用的工具类
第一:IntegerRowMapper
代码:
public classIntegerRowMapperimplementsRowMapper {
publicObject mapRow(ResultSet rs,intindex)throwsSQLException {
Integer c =newInteger(0);
c = rs.getInt(1);
returnc;
}
}
用途:
如SQL只是取数量时,可以采用这个类减少一些代码,示例如下:
StringBuilder sb=new StringBuilder();
sb.append("SELECT");
sb.append(" count(*)");
sb.append("FROM");
sb.append(" tb_contract");
sb.append("WHERE");
sb.append(" contract_id='"+id+"' ");
String sql=sb.toString();
List> ls = this.getJdbcTemplate().query(sql, (new IntegerRowMapper()));
Integer i = (Integer) ls.get(0);
第二:StringRowMapper
代码:
public class StringRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index) throws SQLException {
String c=new String(rs.getString(1));
return c;
}
}
用途:当SQL语句只返回一个字符串类型的定值时,采用这个类能减少部分代码,示例如下:
StringBuilder sb=new StringBuilder();
sb.append("select");
sb.append(" user_name as name");
sb.append("from");
sb.append(" TB_SYS_USER");
sb.append("where");
sb.append(" user_id='"+userId+"'");
String sql=sb.toString();
List> ls = this.getJdbcTemplate().query(sql, (new StringRowMapper()));
String usrName=(String)ls.get(0);
第三:RecordCounter
代码:
public class RecordCounter{
private String sql;
private JdbcTemplate jdbcTemplate;
/**
*构造函数
* @param sql
* @param jdbcTemplate
*/
public RecordCounter(String sql,JdbcTemplate jdbcTemplate){
this.sql=sql;
this.jdbcTemplate=jdbcTemplate;
}
/**
*得到SQL语句查询到的记录数,对外的关键语句
* @author:何杨(heyanghy@cn.ibm.com)
* @date : Apr 23, 2011
* @time : 11:09:35 AM
* @return
*/
public int getCount() throws Exception{
StringBuilder sb=new StringBuilder();
sb.append(" select ");
sb.append(" count(*) as recordCount ");
sb.append(" from ("+sql+") t ");
String sql=sb.toString();
class MyRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index) throws SQLException {
Integer c = new Integer(0);
c=rs.getInt("recordCount");
return c;
}
}
List> ls = jdbcTemplate.query(sql, (new MyRowMapper()));
Integer i=(Integer)ls.get(0);
return i.intValue();
}
}
用途:分页时常需要得到SQL语句查询得到的总记录数,采用这个类可以减少部分代码。
示例:略
第四:MapRowMapper
代码:
public class MapRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index) throws SQLException {
List> ls=new ArrayList>();
int n=rs.getMetaData().getColumnCount();
for(int i=1;i<=n;i++){
try{
Map map=new HashMap();
map.put(rs.getMetaData().getColumnName(i).toLowerCase(), rs.getString(i));
ls.add(map);
}
catch(Exception ex){
continue;
}
}
return ls;
}
}
用途:一般来说,当查询只会返回一条记录时,如按ID得到一条记录,会使用这个Mapping器。得到的对象可以用来给对象赋值。示例如下:
StringBuilder sb=new StringBuilder();
sb.append("select");
sb.append(" *");
sb.append("from");
sb.append(" TB_CONTRACT ");
sb.append("where");
sb.append(" contract_id='"+id+"'");
String sql=sb.toString();
List> ls = this.getJdbcTemplate().query(sql, (new MapRowMapper()));
Map map = new HashMap();
List> ls2=(List>)ls.get(0);
for(Object obj:ls2){
Map mapTemp=(Map)obj;
map.putAll(mapTemp);
}
第五:NameValueRowMapper
代码:
public class NameValueRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index) throws SQLException {
List ls=new ArrayList();
int n=rs.getMetaData().getColumnCount();
for(int i=1;i<=n;i++){
NameValue nv=new NameValue(rs.getMetaData().getColumnName(i).toLowerCase(),rs.getString(i));
ls.add(nv);
}
return ls;
}
}
public class NameValue extends BaseDomainObj{
private String name;
private String value;
/**
*无参数构造函数
*/
public NameValue(){
}
/**
*双参数构造函数
* @param name
* @param value
*/
public NameValue(String name,String value){
this.name=name;
this.value=value;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public String asXML() {
StringBuilder sb=new StringBuilder();
sb.append("");
sb.append(StringUtils.isBlank(value)?"-":value);
sb.append(""+name+">");
return sb.toString();
}
}
public class NameValueList extends BaseDomainObj{
//内含NameValue的链表
private List> list;
/**
*无参数构造函数
*/
public NameValueList(){
}
/**
*带参数构造函数
* @param list
*/
public NameValueList(List> list){
this.list=list;
}
@SuppressWarnings("unchecked")
public String asXML() {
StringBuilder sb=new StringBuilder();
for(Object obj:list){
List ls=(List)obj;
sb.append("");
for(NameValue nv:ls){
sb.append(nv.asXML());
}
sb.append("");
}
return sb.toString();
}
public void setList(List> list) {
this.list = list;
}
public List> getList() {
return list;
}
}
说明:将一行记录转化成一个包含键值对的链表,在NameValueList的帮助下能方便的把从数据库得到的行集转化为一段XML;
StringBuilder sb=new StringBuilder();
sb.append("SELECT ");
sb.append(" *");
sb.append("FROM tb_contract ");
sb.append("WHERE contract_id='"+id+"'");
String sql=sb.toString();
List> ls=this.getJdbcTemplate().query(sql,new NameValueRowMapper());
NameValueList list=new NameValueList(ls);
String xml=list.asXML();