数据库课程设计——物业管理系统

package com.xing.manager; 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 com.xing.tool.BankCardBean; import com.xing.tool.NoticeBean; import com.xing.tool.StaticFunc; import com.xing.tool.StaticValue; import com.xing.tool.UserInfoBean; import com.xing.tool.WuInfoBean; public class DBManager { private Connection conn; private static DBManager dbManager = null; public static DBManager getInstance() { if (dbManager == null) { dbManager = new DBManager(); } return dbManager; } /** * 连接数据库 * * @return */ public boolean connect() { try { Class.forName(StaticValue.DB_DRIVER); conn = DriverManager.getConnection(StaticValue.DB_URL, StaticValue.DB_USER_NAME, StaticValue.DB_PASSWD); return true; } catch (Exception e) { e.printStackTrace(); return false; } } /** * 添加普通用户 * @param name * @param ID * @param room * @param build * @param job * @param tel * @param pwd * @param sex */ public boolean addUser(String name, String ID, String room, String build, String job, String tel, String pwd, String sex,String bankID, String monthAmount) { try{ /** * 创建账号 */ String psql = "insert into account (UserID,PassWD,RoleID) VALUES (?, ?, ?)"; PreparedStatement ps = conn.prepareStatement(psql); //关闭自动提交开启事务 conn.setAutoCommit(false); ps.setString(1, ID); ps.setString(2, pwd); ps.setInt(3, StaticValue.ROLE_USER); ps.executeUpdate(); /** * 创建用户 */ psql = "insert into user (UserID,UserName,Tel,Sex,Job) VALUES (?, ?, ?, ?, ?)"; ps = conn.prepareStatement(psql); ps.setString(1, ID); ps.setString(2, name); ps.setString(3, tel); ps.setString(4, sex); ps.setString(5, job); ps.executeUpdate(); /** * 添加房间 */ psql = "insert into room (UserID,RoomID,BuildID) VALUES (?, ?, ?)"; ps = conn.prepareStatement(psql); ps.setString(1, ID); ps.setString(2, room); ps.setString(3, build); ps.executeUpdate(); /** * 添加银行卡 */ psql = "insert into card (UserID,CardID,Remain) VALUES (?, ?, ?)"; ps = conn.prepareStatement(psql); ps.setString(1, ID); ps.setString(2, bankID); ps.setDouble(3, 0);; ps.executeUpdate(); /** * 初始化用量 */ Statement stmt = conn.createStatement(); String sqlStr="select max(AmountID) from amount group by AmountID"; ResultSet rs=stmt.executeQuery(sqlStr); String AmountIDLast = ""; while(rs.next()){ AmountIDLast=rs.getString(1); System.out.println(AmountIDLast); } if(AmountIDLast.equals("")){ AmountIDLast = "0"; } psql = "insert into amount (AmountID,UserID,AmountYear,AmountMonth,Water,Elect,Gas) VALUES (?, ?, ?,?,?,?,?)"; ps = conn.prepareStatement(psql); ps.setString(1, (Integer.parseInt(AmountIDLast)+1)+""); ps.setString(2, ID); ps.setInt(3, StaticFunc.getYear()); ps.setInt(4, StaticFunc.getMonth()); ps.setDouble(5, 0); ps.setDouble(6, 0); ps.setDouble(7, 0); ps.executeUpdate(); /** * 添加贷款信息 */ psql = "insert into Loan (UserID,LoanMonth,LoanRemainMonth) VALUES (?, ?, ?)"; ps = conn.prepareStatement(psql); ps.setString(1, ID); ps.setInt(2, Integer.parseInt(monthAmount)); ps.setInt(3, Integer.parseInt(monthAmount)); ps.executeUpdate(); /** * 初始化费用信息 */ stmt = conn.createStatement(); sqlStr="select max(FeeID) from Fee group by FeeID"; rs=stmt.executeQuery(sqlStr); String FeeIDLast = ""; while(rs.next()){ FeeIDLast=rs.getString(1); System.out.println(FeeIDLast); } if(FeeIDLast.equals("")){ FeeIDLast = "0"; } psql = "insert into Fee (FeeID,UserID,water,elect,gas,tv,nuan,wu,FeeYear,FeeMonth) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; ps = conn.prepareStatement(psql); ps.setString(1, (Integer.parseInt(FeeIDLast)+1)+""); ps.setString(2, ID); ps.setDouble(3, 0); ps.setDouble(4, 0); ps.setDouble(5, 0); ps.setDouble(6, 0); ps.setDouble(7, 0); ps.setDouble(8, 0); ps.setInt(9, StaticFunc.getYear()); ps.setInt(10, StaticFunc.getMonth()); ps.executeUpdate(); //如果你不提交那么本次操作不会对数据库造成任何 conn.commit(); return true; }catch(Exception e){ e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } return false; } } /** * 根据登陆用户的ID，获取用户的权限ID * @param ID * @return */ public int getRoleID(String ID){ try{ Statement stmt = conn.createStatement(); stmt = conn.createStatement(); String sqlStr="select RoleID from account where UserID='"+ID+"'"; ResultSet rs=stmt.executeQuery(sqlStr); String Role = ""; while(rs.next()){ Role=rs.getString(1); System.out.println(Role); } return Integer.parseInt(Role); }catch(Exception e){ e.printStackTrace(); return -1; } } public String[][] wuUserSearch1(String str, boolean flag){ try{ Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); stmt = conn.createStatement(); if(str.equals("姓名")){ str = " order by user.UserName"; }else if(str.equals("身份证号")){ str = " order by user.UserID"; }else if(str.equals("楼号")){ str = " order by room.BuildID"; }else if(str.equals("房号")){ str = " order by room.RoomID"; }else if(str.equals("工作单位")){ str = " order by Job"; }else if(str.equals("联系电话")){ str = " order by Tel"; }else if(str.equals("建筑面积号")){ str = " order by area"; }else{ str = " order by user.UserName"; } String sqlStr; if(flag){ sqlStr="select user.UserName,user.UserID,room.BuildID,room.RoomID,Job,Tel,area"+ " from user,room,Area"+ " where user.UserID=room.UserID and room.RoomID=Area.RoomID and room.BuildID=Area.BuildID"+str+";"; }else{ sqlStr="select user.UserName,user.UserID,room.BuildID,room.RoomID,Job,Tel,area"+ " from user,room,Area"+ " where user.UserID=room.UserID and room.RoomID=Area.RoomID and room.BuildID=Area.BuildID"+str+" desc;"; } ResultSet rs=stmt.executeQuery(sqlStr); /** * 获取行数和列数 */ rs.last(); int rowCount = rs.getRow(); int colCount = rs.getMetaData().getColumnCount(); rs.first(); String[][] result = new String[rowCount][colCount]; do{ for(int i=0;i<colCount;i++){ result[rs.getRow()-1][i] = rs.getString(i+1); } }while(rs.next()); return result; }catch(Exception e){ e.printStackTrace(); return null; } } public String[][] wuUserSearch1(){ try{ Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); stmt = conn.createStatement(); String sqlStr="select user.UserName,user.UserID,room.BuildID,room.RoomID,Job,Tel,area"+ " from user,room,Area"+ " where user.UserID=room.UserID and room.RoomID=Area.RoomID and room.BuildID=Area.BuildID;"; ResultSet rs=stmt.executeQuery(sqlStr); /** * 获取行数和列数 */ rs.last(); int rowCount = rs.getRow();