userdaoimpl.java

基于Java的地图数据管理软件。使用MySQL数据库管理系统。

		query += log.getUserId() + ",'";
		query += log.getOperator() + "',CURRENT_TIMESTAMP())";
		try {
			System.out.println(query);
			dbo.setPrepareStatement(query);
			dbo.executeUpdate();
			return true;
		} catch (Exception e) {
		} finally {
			dbo.close();
		}
		return false;
	}
    /**
     * 分页显示用户操作记录信息列表
     */
	public Page getLogList(int start, int count) {
		Connection conn = null;
		DBConnectionManager dbmanager = DBConnectionManager.getInstance();
		Page page = null;
		try {
			conn = dbmanager.getConnection("DB");
			String sqlStr = "select * from tt_log order by createtime desc";
			System.out.println(sqlStr);
			PreparedStatement pstmt = conn.prepareStatement(sqlStr,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = pstmt.executeQuery();
			if (start >= 0 && rs.absolute(start + 1)) {
				boolean hasNext = false;
				List logList = new ArrayList();
				do {
					LogInfo loginfo = new LogInfo();
					loginfo.setUserId(rs.getInt("userId"));
					loginfo.setOperator(rs.getString("operator"));
					loginfo.setCreateTime(rs.getDate("createTime"));
					logList.add(loginfo);
				} while ((hasNext = rs.next()) && (--count > 0));
				page = new Page(logList, start, hasNext);
			} else {
				page = Page.EMPTY_PAGE;
			}
			rs.close();
			pstmt.close();
			//CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			dbmanager.freeConnection(conn, "DB");
			// dbo.close();
		}

		return page;
	}

	/**
	 * 获取操作记录总数
	 */
	public int getLogCount() {
		  ArrayList articlelist=new ArrayList();
	        int count=0;
	        DBOperator dbo = new DBOperator();
	        try {
	        	String sqlStr="select count(userId) as maxCount from tt_log  ";
	            dbo.setPrepareStatement(sqlStr);
	            ResultSet rs = dbo.executeQuery();
	            while (rs.next()) {
	            	count = rs.getInt("maxCount");
	                }
	            rs.close();
	            return count;
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            dbo.close();
	        }

	        return count;
	}

	/**
	 * 获取所有用户总数
	 */
	public int getAllUserCount() {
		  ArrayList articlelist=new ArrayList();
	        int count=0;
	        DBOperator dbo = new DBOperator();
	        try {
	        	String sqlStr="select count(userId) as maxCount from tt_user  ";
	            dbo.setPrepareStatement(sqlStr);
	            ResultSet rs = dbo.executeQuery();
	            while (rs.next()) {
	            	count = rs.getInt("maxCount");
	                }
	            rs.close();
	            return count;
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            dbo.close();
	        }

	        return count;
	}
	//添加新县（区）
	public boolean addCounty(String countyCode,String name) {
		DBOperator dbo = new DBOperator();
		String query = "insert into tt_county(countyCode,countyName) values('";
		query += countyCode + "','";
		query += name + "')";
		try {
			System.out.println(query);
			dbo.setPrepareStatement(query);
			dbo.executeUpdate();
			return true;
		} catch (Exception e) {
		} finally {
			dbo.close();
		}
		return false;
	}

	/**
	 * 根据市编码获取县区信息
	 */
	public List getCountyListByCityCode(String cityCode) {
		DBOperator dbo = new DBOperator();
		List countyList = new ArrayList();
		try {
			String sqlStr = "select * from tt_county where left(countyCode,4) = '" + cityCode + "'";
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				County county = new County();
				county.setCountyCode(rs.getString("countyCode"));
				county.setCountyName(rs.getString("countyName"));
				countyList.add(county);
			}
			rs.close();
			return countyList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	/**
	 * 根据县区编码得到县区名称
	 */
	public String getCountyNameByCode(String code) {
		DBOperator dbo = new DBOperator();
		String countyName = "";
		try {
			String sqlStr = "select countyName from tt_county where countyCode = '" + code + "'";
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				countyName = rs.getString("countyName");
			}
			rs.close();
			return countyName;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	/**
	 * 根据用户ID获取用户信息
	 */
	public UserInfo getUserInfoById(int userId) {
		DBOperator dbo = new DBOperator();
		UserInfo ui = new UserInfo();
		try {
			String sqlStr = "select * from tt_user where userId= " + userId;
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				
				ui.setCity(rs.getString("city"));
				ui.setCounty(rs.getString("county"));
				ui.setDepartment(rs.getString("department"));
				ui.setHeadShip(rs.getString("headship"));
				ui.setPassWord(rs.getString("password"));
				ui.setUserId(rs.getInt("userId"));
				ui.setUserName(rs.getString("username"));
				ui.setPersonName(rs.getString("personname"));
				ui.setTelephone(rs.getString("telephone"));
				ui.setFax(rs.getString("fax"));
				ui.setRecorder(rs.getString("recorder"));
				ui.setCityName(City.getCityNameByNum(rs.getString("city")));
				ui.setCountyName(City.getCountyName(rs.getString("county")));
				ui.setCreateTime(CurrentUser.getDateToString(rs.getDate("createTime")));
				if (rs.getDate("modifiedTime") != null )
				   ui.setModifiedTime(CurrentUser.getDateToString(rs.getDate("modifiedTime")));
				else 
					ui.setModifiedTime("");
				ui.setMemo(rs.getString("memo"));
			}
			rs.close();
		//	CurrentUser.saveLog(sqlStr); //保存当前用户操作记录
			return ui;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return null;
	}

	/**
	 * 判断用户名是否存在
	 */
	public int IsHasUser(String userName) {
		DBOperator dbo = new DBOperator();
		int isHas = 0;
		try {
			String sqlStr = "select userId as maxInt from tt_user where userName = '" + userName + "'";
			System.out.println(sqlStr);
			dbo.setPrepareStatement(sqlStr);
			ResultSet rs = dbo.executeQuery();
			while (rs.next()) {
				isHas = rs.getInt("maxInt");
			}
			rs.close();
			return isHas;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbo.close();
		}
		return 0;
	}

	public boolean ChangePassWord(int userId ,String newPass) {
		DBOperator dbo = new DBOperator();
		String query = "update tt_user set password = '" + newPass + "' where userId=" + userId;
		try {
			System.out.println(query);
			dbo.setPrepareStatement(query);
			dbo.executeUpdate();
			return true;
		} catch (Exception e) {
		} finally {
			dbo.close();
		}
		return false;
	}
	public boolean UpdateCurrentUser(int userId, UserInfo ui) {
		DBOperator dbo = new DBOperator();
		String query = "update tt_user set username = '";
		      query += ui.getUserName() + "',password = '";
		      query += ui.getPassWord() + "',personname = '";
		      query += ui.getPersonName() + "',headship = '";
		      query += ui.getHeadShip() + "',department = '";
		      query += ui.getDepartment() + "',telephone = '";
		      query += ui.getTelephone() + "',memo='";
		      query += ui.getMemo() + "',recorder='";
		      query += ui.getRecorder() +"',fax='";
		      query += ui.getFax() + "',";
		      query += " modifiedTime = curdate() where userId = " + userId;
	
		try {
			System.out.println(query);
			dbo.setPrepareStatement(query);
			dbo.executeUpdate();
			return true;
		} catch (Exception e) {
		} finally {
			dbo.close();
		}
		return false;
	}

}