用户管理_源代码_jsp+oracle经典实例

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); }