标准化考试系统java版（C/S）

package com.frogking.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.frogking.comm.JNDIDateSource; import com.frogking.comm.MyConnection; import com.frogking.domain.UserPages; import com.frogking.domain.Users; import com.frogking.domain.UsersException; public class UsersDaoMysql implements IDataDao<Users, UserPages> { Connection conn = null; Statement stmt = null; ResultSet rs = null; PreparedStatement pstmt = null; String sql = null; // 添加新的用户 @Override public boolean add(Users user) throws UsersException { boolean result = false; conn = JNDIDateSource.getConnByTomcat(); sql = "insert into user_info " + "(user_name,test_count,user_sex,user_birthday," + "user_occupation,user_nation,user_college," + "user_specialty,user_email,user_tel,user_mobile)" + " values (?,?,?,?,?,?,?,?,?,?,?)"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, user.getName()); pstmt.setInt(2, user.getExamCount()); pstmt.setString(3, user.getSex()); pstmt.setDate(4, user.getBirthday()); pstmt.setString(5, user.getOccupation()); pstmt.setString(6, user.getNation()); pstmt.setString(7, user.getCollege()); pstmt.setString(8, user.getSpecialty()); pstmt.setString(9, user.getEmail()); pstmt.setString(10, user.getTelephone()); pstmt.setString(11, user.getMobilephone()); System.out.println(sql); int re = pstmt.executeUpdate(); if (re > 0) { result = true; } } catch (SQLException e) { System.out.println("插入用户失败－－－－－－－－－"); e.printStackTrace(); } finally { try { MyConnection.closeDb(rs, pstmt, conn); } catch (SQLException e) { System.out.println("数据连接关闭失败........."); } } return result; } // 删除用户 @Override public boolean delete(int id) throws UsersException { boolean result = false; String sql; int i; try { conn = JNDIDateSource.getConnByTomcat(); sql = "delete from users where id=" + id; stmt = conn.createStatement(); i = pstmt.executeUpdate(sql); if (i > 0) { result = true; } } catch (SQLException e) { throw new UsersException(e); } finally { try { MyConnection.closeDb(rs, stmt, conn); } catch (SQLException e) { throw new UsersException(e); } } return result; } // 给定一些用户信息，查找相应的用户 @Override public List<Users> search(Users user, UserPages pages) throws UsersException { List<Users> list = new ArrayList<Users>(); conn = JNDIDateSource.getConnByTomcat(); sql = "select * from user_info where 1=1 "; getSearchSql(user); try { stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sql); rs.absolute(pages.getStartID() + 1); int c = 0; while (c++ < pages.getRecord_per_page()) { Users u = new Users(); u.setId(rs.getInt("user_id")); u.setName(rs.getString("user_name")); u.setExamCount(rs.getInt("test_count")); u.setSex(rs.getString("user_sex")); u.setBirthday(rs.getDate("user_birthday")); u.setOccupation(rs.getString("user_occupation")); u.setNation(rs.getString("user_nation")); u.setCollege(rs.getString("user_college")); u.setSpecialty(rs.getString("user_specialty")); u.setEmail(rs.getString("user_email")); u.setTelephone(rs.getString("user_tel")); u.setMobilephone(rs.getString("user_mobile")); list.add(u); if (!rs.next()) { break; } } pages.setList(list); } catch (SQLException e) { System.out.println("查找试题信息失败..........."); e.printStackTrace(); } finally { try { MyConnection.closeDb(rs, stmt, conn); } catch (SQLException e) { System.out.println("数据连接关闭失败........."); } } return list; } // 查找指定信息的用户的数目 public int searchUserCount(Users user) { int count = 0; conn = JNDIDateSource.getConnByTomcat(); sql = "select count(*) from user_info where 1=1 "; getSearchSql(user); try { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { System.out.println("查找试题信息失败..........."); } finally { try { MyConnection.closeDb(rs, stmt, conn); } catch (SQLException e) { System.out.println("数据连接关闭失败........."); } } return count; } // private void getSearchSql(Users user) { if (user.getId() > 0) { sql += " and user_id=" + user.getId(); } if (user.getName() != null && user.getName().length() > 0) { sql += " and user_name='" + user.getName() + "' "; } if (user.getExamCount() > 0) { sql += " and test_count=" + user.getExamCount(); } if (user.getSex() != null && user.getSex().length() > 0) { sql += " and user_sex='" + user.getSex() + "' "; } if (user.getBirthday() != null) { sql += " and user_birthday=" + user.getBirthday(); } if (user.getOccupation() != null && user.getOccupation().length() > 0) { sql += " and user_occupation='" + user.getOccupation() + "' "; } if (user.getNation() != null && user.getNation().length() > 0) { sql += " and user_nation='" + user.getNation() + "' "; } if (user.getCollege() != null && user.getCollege().length() > 0) { sql += " and user_college='" + user.getCollege() + "' "; } if (user.getSpecialty() != null && user.getSpecialty().length() > 0) { sql += " and user_specialty='" + user.getSpecialty() + "' "; } if (user.getEmail() != null && user.getEmail().length() > 0) { sql += " and user_email='" + user.getEmail() + "' "; } if (user.getTelephone() != null && user.getTelephone().length() > 0) { sql += " and user_tel='" + user.getTelephone() + "' "; } if (user.getMobilephone() != null && user.getMobilephone().length() > 0) { sql += " and user_mobile=" + user.getMobilephone(); } } // 修改用户信息 @Override public boolean update(Users user) throws UsersException { boolean result = false; conn = JNDIDateSource.getConnByTomcat(); String sql = "update USER_info set user_name=?," + "text_count=?,user_sex=?,user_birthday=?," + "user_occupation=?,user_nation=?,user_college=?," + "user_specialty=?,user_email=?,user_tel=?,user_mobile=? "; sql += " where id =" + user.getId(); try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, user.getName()); pstmt.setInt(2, user.getExamCount()); pstmt.setString(3, user.getSex()); pstmt.setDate(4, user.getBirthday()); pstmt.setString(5, user.getOccupation()); pstmt.setString(6, user.getNation()); pstmt.setString(7, user.getCollege()); pstmt.setString(8, user.getSpecialty()); pstmt.setString(9, user.getEmail()); pstmt.setString(10, user.getTelephone()); pstmt.setString(11, user.getMobilephone()); System.out.println(sql); int i = pstmt.executeUpdate(); if (i > 0) { result = true; } } catch (SQLException e) { throw new UsersException(e); } finally { try { MyConnection.closeDb(rs, pstmt, conn); } catch (SQLException e) { throw new UsersException(e); } } return result; } // 验证用户是否有效 public boolean isValiad(Users user) throws UsersException { boolean result = false; try { conn = JNDIDateSource.getConnByTomcat(); String sql = "SELECT * FROM USER_info WHERE user_name=? and user_email=? "; pstmt = conn.prepareStatement(sql); pstmt.setString(1, user.getName()); pstmt.setString(2, user.getEmail()); rs = pstmt.executeQuery(); if (rs.next()) result = true; } catch (SQLException e) {