简易通讯录

package DB_Controll; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import bean.Staff; import bean.Loginbean; public class DatabaseOp { public DatabaseOp(){ } /** * 取得数据库连接 * */ public Connection getConnection() { Connection conn = null; try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); String url = "jdbc:microsoft:sqlserver://"; String serverName = "localhost"; String portNumber = "1433"; String databaseName = "DB_user"; String user = "sa"; String password = "123"; String URL = url + serverName + ":" + portNumber + ";databaseName=" + databaseName + ";"; conn = DriverManager.getConnection(URL,user,password); System.out.println("hahahahhahaahhaahh"); } catch (ClassNotFoundException e) { System.err.println("驱动程序加载失败！"); } catch (SQLException e) { e.getSQLState(); e.getMessage(); } return conn; } /** * 关闭数据库连接 */ public void close(Connection conn){ if(conn != null){ try { conn.close(); } catch (SQLException e) { System.err.println("数据库不能正常关闭"); } } } /** * 查询当前管理员用户 * @param id * @param pwd * @return */ public Loginbean queryadmin(String name,String pwd){ Connection conn = getConnection(); Statement st = null; ResultSet rs = null; Loginbean loginbean = null; String sltSql = "select * from admin_user where userName='" + name + "' AND userPwd='" + pwd + "'"; try { st = conn.createStatement(); System.out.println("daole"); rs = st.executeQuery(sltSql); System.out.println("daole"); while(rs.next()){ loginbean = new Loginbean(rs.getString("userName"),rs.getString("userPwd")); } rs.close(); st.close(); } catch (SQLException e) { e.printStackTrace(); return null; }finally{ close(conn); } return loginbean; } /** * 显示当前指定通讯录记录 * @param ID * @return */ public Staff showStaff(String ID){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sltSql = "select * from staff,dep where sid=? AND staff.depid=dep.depid"; Staff staff=new Staff(); try { conn=getConnection(); pstmt=conn.prepareStatement(sltSql); pstmt.setInt(1,Integer.parseInt(ID)); rs=pstmt.executeQuery(); if(rs.next()){ staff.setId(rs.getInt(1)); staff.setName(rs.getString(2)); staff.setAge(rs.getInt(3)); staff.setJob(rs.getString(4)); staff.setTelephone(rs.getString(5)); staff.setMobilephone(rs.getString(6)); staff.setEmail(rs.getString(7)); staff.setDepname(rs.getString("depname")); staff.setDepid(rs.getInt("depid")); } return staff; } catch (SQLException e) { e.printStackTrace(); } finally{ try { rs.close(); pstmt.close(); close(conn); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return null; } /** * 显示通讯录里面的所有记录 * @return */ public ArrayList<Staff> showAllUsers(){ Connection conn = null; ArrayList<Staff> arr = new ArrayList<Staff>(); Statement st= null; ResultSet rs = null; String sltSql = "select staff.sid,staff.sname,staff.age,staff.job,staff.telephone,staff.mobilephone," + "staff.email,staff.depid,dep.depname from staff,dep where staff.depid=dep.depid"; conn = getConnection(); try { st = conn.createStatement(); rs = st.executeQuery(sltSql); while(rs.next()){ Staff staff = new Staff(); staff.setId(rs.getInt(1)); staff.setName(rs.getString(2)); staff.setAge(rs.getInt(3)); staff.setJob(rs.getString(4)); staff.setTelephone(rs.getString(5)); staff.setMobilephone(rs.getString(6)); staff.setEmail(rs.getString(7)); staff.setDepname(rs.getString("depname")); staff.setDepid(rs.getInt("depid")); arr.add(staff); } return arr; } catch (SQLException e) { e.printStackTrace(); }finally{ close(conn); } return null; } /** * 删除指定通讯录记录 * @param ID */ public boolean deletStuff(String ID){ Connection conn = getConnection(); try { conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement("delete from staff where sid=?"); pstmt.setInt(1, Integer.parseInt(ID)); return pstmt.executeUpdate() > 0; } catch (SQLException e) { e.printStackTrace(); }finally{ close(conn); } return false; } /** * 添加通讯录 * @param staff */ public boolean addStaff(Staff staff){ Connection conn = getConnection(); PreparedStatement pstmt=null; try { conn=getConnection(); pstmt=conn.prepareStatement("insert into staff values(?,?,?,?,?,?,?)"); pstmt.setString(1, staff.getName()); pstmt.setInt(2, staff.getAge()); pstmt.setString(3, staff.getJob()); pstmt.setString(4, staff.getTelephone()); pstmt.setString(5, staff.getMobilephone()); pstmt.setString(6, staff.getEmail()); pstmt.setInt(7, staff.getDepid()); System.out.println("test0"+staff.getAge()+staff.getName());// return pstmt.executeUpdate() > 0; } catch (SQLException e) { e.printStackTrace(); return false; } finally{ try { pstmt.close(); close(conn); } catch (SQLException e) { e.printStackTrace(); } } } /** * 更新通讯录信息 * @param id * @param staff */ public boolean updateStaff(String id,Staff staff){ Connection conn = getConnection(); PreparedStatement pstmt = null; System.out.println(staff.getName()); try { conn = getConnection(); pstmt = conn.prepareStatement("update staff set sname=?,age=?,job=?,telephone=?,mobilephone=?,email=?,depid=? where sid=?"); pstmt.setString(1, staff.getName()); pstmt.setInt(2, staff.getAge()); pstmt.setString(3, staff.getJob()); pstmt.setString(4, staff.getTelephone()); pstmt.setString(5, staff.getMobilephone()); pstmt.setString(6, staff.getEmail()); pstmt.setInt(7, staff.getDepid()); pstmt.setInt(8, staff.getId()); return pstmt.executeUpdate() > 0; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ try { pstmt.close(); close(conn); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return false; } }