dbutils.java

自己写的一个struts+spring+hibernate测试程序

/*
 * Created on 2006-2-18
 */
package com.common.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.beanutils.DynaBean;
import org.springframework.jdbc.core.JdbcTemplate;

import com.common.SearchResult;
import com.common.utils.ConvertUtil;
import com.common.utils.Page;
import com.common.utils.PagerUtil;



/**
 * <p>Title: 数据库操作公用类</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: </p>
 * @author tommy.zeng
 * @version 1.0
 */
public class DBUtils {
    private static boolean hasSetDataSource = false;
    
    private DataSource dataSource;
    private static DBUtils instance = new DBUtils();
    
    private DBUtils() {
        
    }
    
    public Connection getConnection() throws Exception {
    	return dataSource.getConnection();
    }
    
    /**
     * close connection,statement or resultset quietly
     * @param o
     */
    public static void closeQuietly(Object o){
    	try {
    		if(o instanceof Connection) {
    			((Connection)o).close();
    		}
    		else if(o instanceof Statement) {
    			((Statement)o).close();
    		}
    		else if(o instanceof ResultSet) {
    			((ResultSet)o).close();
    		}
    	}
    	catch(Exception e) {
    		
    	}
    }

    public void setDataSource(DataSource ds ) {
        if(false==hasSetDataSource) {
          this.dataSource = ds;
          hasSetDataSource = true;
        }
    }
    
    public JdbcTemplate getInitJdbcTemplate(){
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt;
    }
    
    
    public static DBUtils getInstance() {
        return instance;
    }
    
    public int queryForInt(final String sql,final Object []params) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.queryForInt(sql,params);
    }
    
    public int queryForInt(final String sql) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.queryForInt(sql);
    }
    
    public long queryForLong(final String sql,final Object []params) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.queryForLong(sql,params);
    }
    
    public long queryForLong(final String sql) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.queryForLong(sql);
    }
    
    public DynaBean queryForBean(final String sql,final Object []params) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        Map map = null;
        try {
            map = jt.queryForMap(sql,params);
        }
        catch(Exception e){
            map = null;
        }
        
        return ConvertUtil.convertToDynaBean(map);
    }
    public DynaBean queryForBean(final String sql) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        Map map = null;
        try {
            map = jt.queryForMap(sql);
        }
        catch(Exception e){
            map = null;
        }
        
        return ConvertUtil.convertToDynaBean(map);
    }
    
    public List queryForList(final String sql,final Object []params) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        
        List list = jt.queryForList(sql,params);
        
        return ConvertUtil.convertToDynaBeanList(list);
    }
    
    public List queryForList(final String sql) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        
        List list = jt.queryForList(sql);
        
        return ConvertUtil.convertToDynaBeanList(list);
    }
    /**
     * 分页 有页和页面大小
     * @param sql
     * @param params
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List pagedQueryForList(final String sql,final Object []params,int pageNo,int pageSize) {
        String newSql = PagerUtil.getPageSql(sql,pageNo,pageSize);
        
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        List list = jt.queryForList(newSql,params);
        
        return ConvertUtil.convertToDynaBeanList(list);
    }
    /**
     * 分页，只有页面，页面大小为系统默然大小
     * @param sql
     * @param params
     * @param pageNo
     * @param pageSize
     * @return
     */
    public SearchResult getSearchResult(final String sql,final Object []params,int pageNo,int pageSize){
    	SearchResult sr = new SearchResult();
        List voList = pagedQueryForList(sql,params,pageNo,pageSize);
        int totalCnt = 0;
        if(voList.size()>0) {
            totalCnt = getTotalCnt(sql,params);
        }
        sr.setCount(totalCnt);
        sr.setResultList(voList);
        return sr;
    }
    /**
     * 
     * @param sql
     * @param params
     * @param pageNo
     * @return
     */
    public SearchResult getSearchResult(final String sql,final Object []params,int pageNo){
    	SearchResult sr = new SearchResult();
    	int pageSize=Page.DEFUALT_PAGE_SIZE;
        List voList = pagedQueryForList(sql,params,pageNo,pageSize);
        int totalCnt = 0;
        if(voList.size()>0) {
            totalCnt = getTotalCnt(sql,params);
        }
        sr.setCount(totalCnt);
        sr.setResultList(voList);
        return sr;
    }
    public SearchResult getSearchResult(final String sql,final List params,int pageNo,int pageSize){
        return getSearchResult(sql,params.toArray(),pageNo,pageSize);
    }
    
    public int update(final String sql,final Object[] params) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.update(sql,params);
    }
    
    public int update(final String sql) {
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.update(sql);
    }
    
    public int update(final String sql,final List params) {
        Object para[] = null;
        if(null!=params) {
            para = params.toArray();
        }
        return update(sql,para);
    }
    
    
    private int getTotalCnt(String sql,Object []params) {
        String newSql = PagerUtil.getQueryCountSql(sql);
        JdbcTemplate jt = new JdbcTemplate(dataSource);
        return jt.queryForInt(newSql,params);
    }
}