abstractdao.java

一个留言板 系统平台： JSP+SQLServer 支持开通无限个数的留言板

/*
 * AbstractDao.java
 *
 * Created on 2006年7月25日, 下午4:32
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package tot.dao;

import java.io.StringReader;
import java.sql.*;
import java.util.*;

import tot.bean.DataBean;
import tot.bean.DataField;
import tot.db.*;
import tot.util.*;
import tot.exception.*;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
 *
 * @author Administrator
 */
public class AbstractDao {
    private static Log log = LogFactory.getLog(AbstractDao.class);
    /**
     * Creates a new instance of AbstractDao
     */
    public AbstractDao() {
    }
    /** get data by sql
     * @param sqlStr  查询数据库所用SQL语句.
     * @param fieldArr 需要返回的字段，多个字段以逗号分隔.
     * @return DataField[].
     */
    public Collection getData(String sqlStr,String fieldArr){
        Connection conn = null;
        Statement stmt=null;
        ResultSet rs = null;
        Collection returnList = new ArrayList();
        try{
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();
            rs=stmt.executeQuery(sqlStr);
            DataField df;
            String[] splitStr=null;
            splitStr=fieldArr.split(",");
            while(rs.next()) {
                df=new DataField();
                for(int i=0;i<splitStr.length;i++){
                    df.setField(splitStr[i], rs.getString(i+1),0);
                }
                returnList.add(df);
            }            
        } catch(SQLException e){
            e.printStackTrace();
            log.info("catch exception on call method:getdata();\n sql:"+sqlStr);
        } finally{
            DBUtils.closeResultSet(rs);
            //DBUtils.resetStatement(stmt);
            DBUtils.closeStatement(stmt);
            DBUtils.closeConnection(conn);
        }
        return returnList;
    }
    /**
     * 数据显示分页函数
     * @param sqlStr 数据库查询SQL语句.
     * @param fieldArr 需要返回的字段.
     * @param pagesize 每页显示数据行数.
     * @param page 当前页数.
     * @return DataField[].
     */
    public Collection getDataList_mysqlLimit(String sqlStr,String fieldArr,int pagesize, int page){
        StringBuffer sb=new StringBuffer(512);
        sb.append(sqlStr);
        sb.append(" limit "+(page-1)*pagesize+","+pagesize);
        return this.getData(sb.toString(),fieldArr);
    }
    /**
     * 据SQL语句查询获得指定字段的数据
     * @param sqlStr SQL查询语句
     * @param fieldArr 需要返回的字段名称
     * @return DataFild.
     */
    public DataField getFirstData(String sqlStr,String fieldArr){
        Connection conn = null;
        Statement stmt=null;
        ResultSet rs = null;
        DataField df=null;
        try{
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();
            rs=stmt.executeQuery(sqlStr);
            String[] splitStr=null;
            splitStr=fieldArr.split(",");
            if(rs.next()) {
                df=new DataField();
                for(int i=0;i<splitStr.length;i++){
                    df.setField(splitStr[i], rs.getString(i+1),0);
                }
            }
        } catch(SQLException e){
            e.printStackTrace();
            log.info("catch exception on call method:getdata(); \n sql:"+sqlStr);
        } finally{
            DBUtils.closeResultSet(rs);
            //DBUtils.resetStatement(stmt);
            DBUtils.closeStatement(stmt);
            DBUtils.closeConnection(conn);
        }
        return df;
    }
    /**
     * get data count.
     * @param sqlStr SQL查询语句.
     * @return int.
     */
    public int getDataCount(String sqlStr){
        Connection conn = null;
        Statement stmt=null;
        ResultSet rs = null;
        int returnInt=0;
        try{
            conn = DBUtils.getConnection();           
            ResultSet resultSet = null;
            stmt = conn.createStatement();
            rs=stmt.executeQuery(sqlStr);
            if(rs.next()) {
                returnInt=rs.getInt(1);
            }
        } catch(SQLException e){
            e.printStackTrace();
            log.info("catch exception on call method:getdata(); \n sql:"+sqlStr);
        } finally{
            DBUtils.closeResultSet(rs);
            DBUtils.closeStatement(stmt);
            DBUtils.closeConnection(conn);
        }
        return returnInt;
    }
    /**
     * 添加、更新数据库
     * @param sqlStr 添加、更新数据库SQL语句如：<code>update table set a=?,b=?</code>
     * @param df DataField型数据
     * @return true/false.
     */
    public synchronized boolean insertOrUpdate(String sqlStr,DataField df){
        Connection conn = null;
        PreparedStatement ps = null;
        Hashtable hs=df.getFields();
        Enumeration enumerat= hs.keys();
        boolean returnValue=true;
        int i=1;
        try{
            ps=conn.prepareStatement(sqlStr);
            while(enumerat.hasMoreElements()) {
                String s = (String)enumerat.nextElement();
                DataBean dfm = (DataBean)hs.get(i+"");
                //System.out.println(dfm.DataType+":"+dfm.FieldName+"="+dfm.FieldValue);
                if(dfm.DataType==1) {
                    ps.setInt(i,Integer.parseInt(dfm.FieldValue));
                } else if(dfm.DataType==2){
                    java.sql.Date today=null;
                    try{
                        today=GetDate.string2Date(dfm.FieldValue);
                    } catch(Exception e){
                        e.printStackTrace();
                    }
                    ps.setDate(i,today);
                } else {
                    ps.setString(i,dfm.FieldValue);
                }
                i++;
            }
            if(ps.executeUpdate() != 1){
                returnValue=false;
            }
        } catch(SQLException e){
            log.info("catch exception when call insertOrUpdate method: \n"+sqlStr);
            e.printStackTrace();
            return false;
        } finally{
            DBUtils.closePrepareStatement(ps);
            DBUtils.closeConnection(conn);
        }
        return true;
    }
    /**
     * 执行更新操作
     * @param sqlStr 执行更新的SQL语句
     * @return boolean
     */
    public boolean exe(String sqlStr){
        Connection conn = null;
        Statement stmt=null;
        boolean returnValue=true;
        try{
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();
            if (stmt.executeUpdate(sqlStr) != 1) {
                returnValue=false;
            } else{
                returnValue=true;
            }
        } catch(SQLException e){
            e.printStackTrace();
            log.info("catch exception on call method:getdata(); \n sql:"+sqlStr);
        } finally{
            DBUtils.closeStatement(stmt);
            DBUtils.closeConnection(conn);
        }
        return returnValue;
    }
}