jsp+servlet+dtree+oracle+js+jdbc 员工管理系统

package com.skytech.dao; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; public class DBI { private static DBI dbi = new DBI(); private static DataSource ds = null; public static DBI getInstance() { return dbi; } private Connection getConnection() throws SQLException { String datasource = "java:comp/env/o9i"; Connection conn = null; try { if (ds == null) { InitialContext ctx = new InitialContext(); ds = (DataSource) ctx.lookup(datasource); } } catch (NamingException ne) { throw new SQLException("Lookup Datasource fail." + ne.getMessage()); } // OracleDataSource ds = new OracleDataSource(); // ds.setURL("jdbc:oracle:thin:trans/trans@localhost:1521:test"); conn = ds.getConnection(); return conn; } public ArrayList<ArrayList> query(String sql) { ArrayList<ArrayList> alRS = new ArrayList<ArrayList>(); Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); int i = 0; while (rs.next() != false) { ArrayList<String> alRow = new ArrayList<String>(); for (i = 1; i <= cols; ++i) { if (rs.getString(i) == null) { alRow.add(""); } else { alRow.add(rs.getString(i)); } } alRS.add(alRow); } } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return alRS; } public ArrayList<ArrayList> query(String sql, List alParam) { ArrayList<ArrayList> alRS = new ArrayList<ArrayList>(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); setParameters(pstmt, alParam); rs = pstmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); int i = 0; while (rs.next()) { ArrayList<String> alRow = new ArrayList<String>(); for (i = 1; i <= cols; ++i) { if (rs.getString(i) == null) { alRow.add(""); } else { alRow.add(rs.getString(i)); } } alRS.add(alRow); } } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return alRS; } public ArrayList<ArrayList> queryTopN(String sql, int topn) { ArrayList<ArrayList> alRS = new ArrayList<ArrayList>(); Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConnection(); stmt = conn.createStatement(); String sql_topn = "select rownum as myrownum,a.* from (" + sql + ") a where rownum<=" + topn + ")"; rs = stmt.executeQuery(sql_topn); ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); int i = 0; int j = 0; while (rs.next()) { ArrayList<String> alRow = new ArrayList<String>(); for (i = 1; i <= cols; ++i) { if (rs.getString(i) == null) { alRow.add(""); } else { alRow.add(rs.getString(i)); } } alRS.add(alRow); j++; } } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return alRS; } public String queryColumn(String colName, String sql) { Connection conn = null; Statement stmt = null; ResultSet rs = null; String colData = null; try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); colData = rs.next() ? rs.getString(colName) : null; rs.close(); stmt.close(); } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return colData; } public double queryMax(String column, String sqlstr) { Connection conn = null; Statement stmt = null; ResultSet rs = null; double max = 0; String sql = "select max(" + column + ") maxID from (" + sqlstr + ")"; try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); max = rs.next() ? rs.getDouble("maxID") : 0; rs.close(); stmt.close(); } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return max; } public int queryCount(String sqlstr, List alParam) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; int i_count = 0; String sql = "select count(*) Count from (" + sqlstr + ")"; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); setParameters(pstmt, alParam); rs = pstmt.executeQuery(); i_count = rs.next() ? rs.getInt("Count") : 0; rs.close(); pstmt.close(); } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return i_count; } public int queryCount(String sqlstr) { Connection conn = null; Statement stmt = null; ResultSet rs = null; int i_count = 0; String sql = "select count(*) Count from (" + sqlstr + ")"; try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); i_count = rs.next() ? rs.getInt("Count") : 0; rs.close(); stmt.close(); } catch (SQLException sqle) { sqle.printStackTrace(); throw new RuntimeException("数据库访问异常"); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException sqle2) { sqle2.printStackTrace(); throw new RuntimeException("资源关闭异常"); } } return i_cou