dbmanager.java

JSP常用模块源代码之用户管理模块的全部源代码

package service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author myte
 *
 */
public abstract class DBManager {

	protected String getInsertSql(Object o) {
        StringBuffer sb = new StringBuffer();

        sb.append("insert into ").append(getTableName()).append("(");
        for (int i = 0; i < getFields().length; i++) //遍历所有的字段
        	if (getKeyFields()!=getFields()[i]){ //主键不插入
        		sb.append(getFields()[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(") values(");
        for (int i = 0; i < getFields().length; i++) //遍历所有的字段
        	if (getKeyFields()!=getFields()[i]){ //主键不插入
        		sb.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");

        return sb.toString();
    }

    protected String getUpdateSql(Object o) {
        StringBuffer sb = new StringBuffer();
        sb.append("update ").append(getTableName()).append(" set ");
        for (int i = 0; i < getFields().length; i++)
        	if (getKeyFields()!=getFields()[i]){//主键不更新
        		sb.append(getFields()[i]).append("=?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" where ").append(getKeyFields()).append("=?");
        return sb.toString();
    }

    protected String getDeleteSql() {
        StringBuffer sb = new StringBuffer();

        sb.append("delete from ").append(getTableName());
        sb.append(" where ").append(getKeyFields()).append("=?");

        return sb.toString();
    }
    
    protected Object[] getInsertParams(Object o) {
        List ret = new ArrayList();
        for (int i = 1; i < getFieldValues(o).length; i++){
            ret.add(getFieldValues(o)[i]);
        }
        return ret.toArray(new Object[ret.size()]);
    }
    protected Object[] getUpdateParams(Object o) {
        List ret = new ArrayList();
        for (int i = 1; i < getFieldValues(o).length; i++){
            ret.add(getFieldValues(o)[i]);
        }
        ret.add(getKeyValues(o)[0]);
        return ret.toArray(new Object[ret.size()]);
    }    

    public int executeUpdate(String sql, Object[] params) throws Exception{
        Connection con = null;
        PreparedStatement ps = null;
        try {
        	con= getConn();
            ps = con.prepareStatement(sql);
            if (params != null) {
                for (int i = 0,j = 1; i < params.length; i++) {
                    setParameter(ps, j++, Util.strToGBK(params[i].toString()));
                }
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new Exception(e);
        } finally {
            if (ps != null)
                ps.close();
            if (con != null)
            	con.close();
        }
    }
    public List executeSelect(String sql) throws Exception{
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List result = new ArrayList();
        try {
        	con= getConn();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                int cc = rs.getMetaData().getColumnCount();
                do {
                    Object[] row = new Object[cc];
                    for (int i = 0; i < row.length; i++) {
                        row[i] = rs.getObject(i + 1);
                    }
                    result.add(row);
                } while (rs.next());
            }
        } catch (SQLException e) {
        	System.out.println("Info... sql=" + sql);
            throw new Exception(e);
        } finally {
            if (ps != null)
                ps.close();
            if (con != null)
            	con.close();
        }
        return result;
    }
    
	private static Connection getConn() throws Exception {
    	Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
    	String url="jdbc:microsoft:sqlserver://vmyte:1433;DatabaseName=modules";
    	String user="sa";
    	String password="123";
    	return DriverManager.getConnection(url,user,password);
	}

	public void insert(Object o) throws Exception {
        if (executeUpdate(getInsertSql(o), getInsertParams(o)) != 1)
            throw new Exception();
    }

    public void update(Object o) throws Exception {
        if (executeUpdate(getUpdateSql(o), getUpdateParams(o)) != 1)
            throw new Exception();
    }
    
    public void delete(Object e) throws Exception {
        if (executeUpdate(getDeleteSql(), getKeyValues(e)) != 1)
            throw new Exception();
    }
//    public void select(Object o) throws Exception {
//        if (executeUpdate(getDeleteSql(), getKeyValues(e)) != 1)
//            throw new Exception();    	
//    }
    private static void setParameter(PreparedStatement ps, int i, Object o) throws SQLException {
    	if (o == null) {
    		ps.setNull(i, Types.NULL);
    	}
    	else if (o instanceof String) {
    		ps.setString(i, (String)o);
    	}
    	else if (o instanceof Boolean) {
    		ps.setString(i, ((Boolean)o).booleanValue()? "1" : "0");
    	}
    	else if (o instanceof Date) {
    		ps.setDate(i, (Date)o);
    	}
    	else if (o instanceof Timestamp) {
    		ps.setTimestamp(i, (Timestamp)o);
    	}
    	else if (o instanceof java.util.Date) {
    		ps.setDate(i, new Date(((java.util.Date)o).getTime()));
    	}
    	else if (o instanceof Integer) {
    		ps.setInt(i, ((Integer)o).intValue());
    	}
    	else if (o instanceof BigDecimal) {
    		ps.setDouble(i, ((BigDecimal)o).doubleValue());
    	}
    	else {
    		ps.setObject(i, o);
    	}
    }
    //~ 抽象方法
    
    protected abstract String getTableName();

    //返回字段名列表,不包含主键字段
    protected abstract String[] getFields();

    protected abstract Object[] getFieldValues(Object o);
    
    protected abstract String getKeyFields();

    protected abstract Object[] getKeyValues(Object o);    
}