jsp 企业管理 web源码+SQL

package qyxx; import java.sql.*; public class userDB { String DBDriver = "oracle.jdbc.driver.OracleDriver"; String DBUrl = "jdbc:oracle:thin:@localhost:1521:ORACLE"; String DBUser = "jerry"; String DBPsw = "123456"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; private Connection initDB() { try { Class.forName(DBDriver); return DriverManager.getConnection(DBUrl,DBUser,DBPsw); } catch (Exception e) { e.printStackTrace(); return null; } } public userTable loginCheck(String username, String password) { try { this.conn = null; conn = this.initDB(); String sql = "select DISTINCT priority,name,age,sex, depart_name, depart_id from QYXX_USER a, QYXX_DEPARTMENT b where a.username='"+username+"' and a.password = '"+password+"' and b.id = a.depart_id"; this.stmt = null; this.rs = null; this.stmt = conn.prepareStatement(sql); rs = this.stmt.executeQuery(); if(rs.next()) { userTable ut = new userTable(); ut.setUsername(username); ut.setPriority(rs.getString(1)); ut.setName(rs.getString(2)); ut.setAge(rs.getString(3)); ut.setSex(rs.getString(4)); ut.setDepartname(rs.getString(5)); ut.setDepartid(String.valueOf(rs.getInt(6))); return ut; } else { return null; } } catch (Exception e) { e.printStackTrace(); return null; } finally { try { conn.close(); stmt.close(); rs.close(); } catch(Exception e) { } } } public userTable getUserInfo(String username) { try { String sql = "select * from QYXX_USER where username = '"+username+"'"; this.conn = null; conn = this.initDB(); this.stmt = null; this.stmt = conn.prepareStatement(sql); rs = this.stmt.executeQuery(); if(rs.next()) { userTable ut = new userTable(); ut.setUsername(rs.getString("username")); ut.setPassword(rs.getString("password")); ut.setName(rs.getString("name")); ut.setAge(rs.getString("age")); ut.setSex(rs.getString("sex")); ut.setDepartid(String.valueOf(rs.getInt("depart_id"))); return ut; } else { return null; } } catch (Exception e) { e.printStackTrace(); return null; } finally { try { conn.close(); stmt.close(); rs.close(); } catch(Exception e) { } } } public int addUser(qyxx.userTable utable) { try { this.conn = null; conn = this.initDB(); String sql = "insert into QYXX_USER(username,password,priority,name,age,sex,depart_id) values(?,?,?,?,?,?,?)"; this.stmt = null; this.stmt = conn.prepareStatement(sql); stmt.setString(1,utable.getUsername()); stmt.setString(2,utable.getPassword()); stmt.setString(3,utable.getPriority()); stmt.setString(4,utable.getName()); stmt.setString(5,utable.getAge()); stmt.setString(6,utable.getSex()); stmt.setString(7,utable.getDepartid()); this.stmt.executeUpdate(); return 1; } catch (Exception e) { e.printStackTrace(); return 0; } finally { try { conn.close(); stmt.close(); } catch(Exception e) { } } } public qyxx.userTable editUser(qyxx.userTable utable) { try { this.conn = null; conn = this.initDB(); String sql = "update QYXX_USER set password = ?, name=?, age=?, sex=?, depart_id=? where username=?"; this.stmt = null; this.rs = null; this.stmt = conn.prepareStatement(sql); stmt.setString(1,utable.getPassword()); stmt.setString(2,utable.getName()); stmt.setString(3,utable.getAge()); stmt.setString(4,utable.getSex()); stmt.setString(5,utable.getDepartid()); stmt.setString(6,utable.getUsername()); stmt.executeUpdate(); sql = "select DISTINCT name,age,sex,depart_name,depart_id from QYXX_USER a,QYXX_DEPARTMENT b where a.username=? and b.id = a.depart_id"; this.stmt = null; this.stmt = conn.prepareStatement(sql); stmt.setString(1,utable.getUsername()); rs = this.stmt.executeQuery(); if(rs.next()) { utable.setName(rs.getString(1)); utable.setAge(rs.getString(2)); utable.setSex(rs.getString(3)); utable.setDepartname(rs.getString(4)); utable.setDepartid(String.valueOf(rs.getInt(5))); return utable; } else { return null; } } catch (Exception e) { e.printStackTrace(); return null; } finally { try { conn.close(); stmt.close(); rs.close(); } catch(Exception e) { } } } public int deleteUser(String [] username) { try { this.conn = null; conn = this.initDB(); for(int i=0; i<username.length; i++) { String sql = "delete from QYXX_USER where username = '"+username[i]+"'"; this.stmt = null; this.stmt = conn.prepareStatement(sql); this.stmt.executeUpdate(); } return 1; } catch (Exception e) { e.printStackTrace(); return 0; } finally { try { conn.close(); stmt.close(); } catch(Exception e) { } } } public int editUserPriority(String [] username,int choice) { try { if(choice == 1) { this.conn = null; conn = this.initDB(); for(int i=0; i<username.length; i++) { String sql = "update QYXX_USER set priority='2' where username = '"+username[i]+"'"; this.stmt = null; this.stmt = conn.prepareStatement(sql); this.stmt.executeUpdate(); } } else { if(choice == 2) { this.conn = null; conn = this.initDB(); for(int i=0; i<username.length; i++) { String sql = "update QYXX_USER set priority='1' where username = '"+username[i]+"'"; this.stmt = null; this.stmt = conn.prepareStatement(sql); this.stmt.executeUpdate(); } } else { rs = null; conn = this.initDB(); for(int i=0; i<username.length; i++) { String sql = "select priority from QYXX_USER where username = '"+username[i]+"'"; this.stmt = null; this.stmt = conn.prepareStatement(sql); rs = this.stmt.executeQuery(); if(rs.next()) { String pr = rs.getString(1); int p = Integer.parseInt(pr) + 1; sql = "update QYXX_USER set priority = '"+String.valueOf(p)+"' where username = '"+username[i]+"'"; this.stmt = null; this.stmt = conn.prepareStatement(sql); stmt.executeUpdate(); } else { return 0; } } } } return 1; } catch (Exception e) { e.printStackTrace(); return 0; } finally { try { conn.close(); stmt.close(); } catch(Exception e) { } } } }