entitymanager.java

java数据库操作工具类

package com.baosight.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.StringTokenizer;

/**
 * 数据库操作类
 * @author steven
 * @version 2005-1-20
 */
public class EntityManager {
	public static final String ELEMENT_TOKEN="#";
	public static final String TYPE_STRING="string";
	public static final String TYPE_INT="int";
	public static final String TYPE_DATE="date";
	public static final String TYPE_FLOAT="float";
	public static final String TYPE_DOUBLE="double";
	public static final String TYPE_BOOLEAN="boolean";
	public static final String TYPE_LONG="long";
    /**
     * 执行一个存储过程，返回查询结果为list
     * @param sp 存储过程名字
     * @param parameters 查询参数
     * @param handler 处理存储过程结果集的回调函数
     * @return
     */
    public List executeQuery(String sql,
            ResultSetHandler handler) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Logger.log("sql="+sql);
        try {

            conn = DBFactory.borrowConnection();
            stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);
            List result = new ArrayList();
            while (rs.next()) {
                //针对查询结果调用回调类进行处理
                if (handler != null) {
                    Object obj = handler.processRow(rs);
                    result.add(obj);
                }
            }
            return result;

        } catch (Exception e) {
            Logger.log("EM.executeUpdate:"+e);

        } finally {

            try {
                if(rs!= null ) rs.close();
                if(stmt!= null ) stmt.close();
                if(conn!= null ) conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 从数据库中查询一条记录
     * @param sp
     * @param parameters
     * @param handler
     * @return
     */
    public Object loadObject(String sql,
            ResultSetHandler handler) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Logger.log("sql="+sql);
        try {

            conn = DBFactory.borrowConnection();
            stmt = conn.createStatement();

            rs = stmt.executeQuery(sql);
            Logger.log(sql);
            Object obj=null;
             while (rs.next()) {
                if (handler != null) {
                    obj = handler.processRow(rs);
                }
            }

            return obj;

        } catch (Exception e) {
            Logger.log("EM.executeUpdate:"+e);
        } finally {

            try {
                if(rs!= null ) rs.close();
                if(stmt!= null ) stmt.close();
                if(conn!= null ) conn.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 执行一个存储过程进行更新操作
     * @param sp
     * @param parameters
     * @return
     */
    public int executeUpdate(String sql) {
        Connection conn = null;
        Statement stmt = null;
        Logger.log("sql="+sql);
        Log.createLogger(this.getClass(),"sql=",sql);
        try {
            conn = DBFactory.borrowConnection();
            stmt = conn.createStatement();
            int rowCount = stmt.executeUpdate(sql);
            return rowCount;
        } catch (Exception e) {
            Logger.log("EM.executeUpdate:"+e);
            e.printStackTrace();
            return 0;
        } finally {

            try {
                stmt.close();
                conn.close();
            } catch (SQLException e1) {
                Logger.log("EM.executeUpdate:"+e1);
                e1.printStackTrace();
            }
        }

    }

    public int executeUpdate(String sql,HashMap paras) {
        Connection conn = null;
        PreparedStatement stmt = null;
        Logger.log("sql="+sql);
        String rsSql = null;
		List psa = new ArrayList();
		List pst = new ArrayList();

		StringTokenizer parser = new StringTokenizer(sql, ELEMENT_TOKEN, true);
		StringBuffer newSql = new StringBuffer();

		String token = null;
		String lastToken = null;
		while (parser.hasMoreTokens()) {
			token = parser.nextToken();
			if (ELEMENT_TOKEN.equals(lastToken)) {
				if (ELEMENT_TOKEN.equals(token)) {
					newSql.append(ELEMENT_TOKEN);
					token = null;
				} else {
					if (token != null) {
						String[] ov =token.split(":");
						Logger.log( ov[0] );
						psa.add( ov[0] );
						if( ov.length >1){
							pst.add(ov[1]);
						}else{
							pst.add(TYPE_STRING);
						}
						newSql.append("?");
					}
					token = parser.nextToken();
					if (!ELEMENT_TOKEN.equals(token)) {
						throw new RuntimeException("error sql (" + sql + ").");
					}
					token = null;
				}
			} else {
				if (!ELEMENT_TOKEN.equals(token)) {
					newSql.append(token);
				}
			}

			lastToken = token;
		}
		rsSql = newSql.toString();
		Logger.log(rsSql);

        try {
            conn = DBFactory.borrowConnection();
            stmt = conn.prepareStatement( rsSql );

            for( int j=0;j<psa.size();j++){
            	String name = (String)psa.get(j);
            	String type = (String)pst.get(j);
            	Object value = paras.get(name);
            	setValue(stmt, j, type, value);

            }//end for
            int rowCount = stmt.executeUpdate();
            return rowCount;
        } catch (Exception e) {
            Logger.log("EM.executeUpdate:"+e);
            e.printStackTrace();
            return 0;
        } finally {

            try {
                stmt.close();
                conn.close();
            } catch (SQLException e1) {
                Logger.log("EM.executeUpdate:"+e1);
                e1.printStackTrace();
            }
        }

    }

	/**
	 * @param stmt
	 * @param j
	 * @param type
	 * @param value
	 * @param jt
	 * @throws SQLException
	 * @throws NumberFormatException
	 */
	private void setValue(PreparedStatement stmt, int j, String type, Object value) throws SQLException, NumberFormatException {
    	int jt = java.sql.Types.VARCHAR;
    	jt = getJdbcType(type, jt);
		if( value == null ) {
			stmt.setNull(j+1,jt);
		}else{
			if( type.equalsIgnoreCase(TYPE_INT) ){
				if( value instanceof Integer) {
					stmt.setInt(j+1, ((Integer)value).intValue());
				}else{
					stmt.setInt(j+1, new Integer(value.toString()).intValue() );
				}

			}else if(type.equalsIgnoreCase(TYPE_DOUBLE) ){
				if( value instanceof Double) {
					stmt.setDouble(j+1, ((Double)value).doubleValue());
				}else{
					stmt.setDouble(j+1, new Double(value.toString()).doubleValue() );
				}
			}else if(type.equalsIgnoreCase(TYPE_FLOAT) ){
				if( value instanceof Float) {
					stmt.setFloat(j+1, ((Float)value).floatValue());
				}else{
					stmt.setFloat(j+1, new Float(value.toString()).floatValue() );
				}
			}else if(type.equalsIgnoreCase(TYPE_DATE) ){
				if( value instanceof java.util.Date) {
					long t =((java.util.Date)value).getTime();
					stmt.setDate(j+1, new java.sql.Date(t) );
				}else{
					long t =((java.util.Date)value).getTime();
					stmt.setDate(j+1, new java.sql.Date(t) );
				}
			}else if(type.equalsIgnoreCase(TYPE_LONG) ){
				if( value instanceof Integer) {
					stmt.setInt(j+1, ((Integer)value).intValue());
				}else{
					stmt.setInt(j+1, new Integer(value.toString()).intValue() );
				}
			}else{
				stmt.setString(j+1,value.toString());
			}
		}
	}

	/**
	 * @param type
	 * @param jt
	 * @return
	 */
	private int getJdbcType(String type, int jt) {
		if( type.equalsIgnoreCase(TYPE_INT) ){
			jt = Types.INTEGER;
		}else if(type.equalsIgnoreCase(TYPE_DOUBLE) ){
			jt = Types.DOUBLE;
		}else if(type.equalsIgnoreCase(TYPE_FLOAT) ){
			jt = Types.DOUBLE;
		}else if(type.equalsIgnoreCase(TYPE_DATE) ){
			jt = Types.DATE;
		}else if(type.equalsIgnoreCase(TYPE_LONG) ){
			jt = Types.INTEGER;
		}
		return jt;
	}
}