今天我将我早晨领取到的任务完成。
对数据库进行操作的接口和类。
package team.WHATEVER.Math.dao; import java.util.List; import team.WHATEVER.Math.model.ErrorQuestion; import team.WHATEVER.Math.model.Operation; import team.WHATEVER.Math.model.User; public interface IUserDao { String login(String nickname,String password);//登录时填入昵称和密码 void add_user(String name,String password,String nickname,int Type);//注册时填写真实姓名时调用 void add_user(String password,String nickname,int Type);//注册时没填写真实姓名时调用 void delete_user(String nickname); //删除 void update_user(User user);//修改 User find_user(String nickname);//根据昵称查找 List<User> find_users();//查找表中的全部信息 List<User> find_group(String group);//查找一个小组内的全部用户 void add_ErrorQuestion(ErrorQuestion errorQuestion);//向数据库中增加错题 List<ErrorQuestion> find_ErrorQuestions(int U_id);//查找表中的U-id的全部错题 void delete_ErrorQuestion(int id);//根据错题题目的id来删除题目 void add_operation(Operation operation);//向历史记录表中添加题目 List<Operation> find_Operations(int U_id);//查找表中的U-id的全部错题 void delete_Operation(int id);//根据历史记录题的id来删除题目 }
package team.WHATEVER.Math.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import team.WHATEVER.Math.Util.DBUtil; import team.WHATEVER.Math.Util.UserException; import team.WHATEVER.Math.model.ErrorQuestion; import team.WHATEVER.Math.model.Operation; import team.WHATEVER.Math.model.User; public class UserDaoImpl implements IUserDao{ @Override public String login(String nickname, String password) {//用户密码不正确时,返回用户密码不正确,用户不存在返回“用户不存在”,用户昵称及密码正确,返回“ ” Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select * from p_user where u_nickname = ?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; User user = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, nickname); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { user = new User(); user.setU_Nickname(resultSet.getString("username")); user.setU_Password(resultSet.getString("password")); } if (user == null) { return "该用户不存在"; } if (!user.getU_Password().equals(password)) { return "密码不正确"; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return " "; } @Override public void add_user(String name, String password, String nickname, int Type) {//注册时填写真实姓名时调用 //获得链接对象 Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select count(*) from p_user where u_nickname = ?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,nickname); //接收结果集 resultSet = preparedStatement.executeQuery(); //遍历结果集 while(resultSet.next()) { if (resultSet.getInt(1) > 0) { throw new UserException("用户已存在") ; } } sql = "insert into p_user(u_nickname,u_Name,u_password,u_Type) value (?,?,?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,nickname ); preparedStatement.setString(2, name); preparedStatement.setString(3, password); preparedStatement.setInt(4, Type); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //关闭资源 DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void add_user(String password, String nickname, int Type) {//注册时没填写真实姓名时调用 Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select count(*) from p_user where u_nickname = ?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,nickname); //接收结果集 resultSet = preparedStatement.executeQuery(); //遍历结果集 while(resultSet.next()) { if (resultSet.getInt(1) > 0) { throw new UserException("用户已存在") ; } } sql = "insert into p_user(u_nickname,u_password,u_Type) value (?,?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,nickname ); preparedStatement.setString(2, password); preparedStatement.setInt(3, Type); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //关闭资源 DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void delete_user(String nickname) {//删除 Connection connection = DBUtil.getConnection(); String sql = "delete from p_user where u_nickname= ?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, nickname); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void update_user(User user) {//更改 Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "update t_user set u_nickanme = ? , u_Name=? ,u_password=?,u_Type=?,u_group=? where u_Id = ?"; //创建语句传输对象 PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, user.getU_Nickname()); preparedStatement.setString(2, user.getU_Name()); preparedStatement.setString(3, user.getU_Password()); preparedStatement.setInt(4, user.getU_Type()); preparedStatement.setString(5, user.getU_Group()); preparedStatement.setInt(5, user.getU_Id()); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public User find_user(String nickname) {//查找用户,若用户存在则返回用户,若不存在则将用户的编号和类型设为10,其他属性设置为“空”, //若用户名还没填,则设置用户名为"空",若还没加入小组,则设置小组名为”空“ Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select * from p_user where u_nickanme = ?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; User user = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, nickname); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { user = new User(); user.setU_Id(resultSet.getInt("u_Id")); user.setU_Nickname(nickname); user.setU_Name(resultSet.getString("u_Name")); user.setU_Password(resultSet.getString("u_password"));; user.setU_Type(resultSet.getInt("u_Type")); user.getU_Group(); if (user == null) { user.setU_Id(-10); user.setU_Nickname("空"); user.setU_Name("空"); user.setU_Password("空");; user.setU_Type(-10); user.setU_Group("空"); } if (" ".equals(user.getU_Name())||user.getU_Name()==null) { user.setU_Name("空"); } if (" ".equals(user.getU_Group())||user.getU_Group()==null) { user.setU_Group("空"); } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return user; } @Override public List<User> find_users() {//查询所有用户的信息//若用户名还没填,则设置用户名为"空",若还没加入小组,则设置小组名为”空“ Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select * from p_user "; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; //集合中只能放入user对象 List<User> users = new ArrayList<User>(); User user = null; try { preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { user = new User(); user.setU_Id(resultSet.getInt("u_Id")); user.setU_Nickname(resultSet.getString("u_nickname")); user.setU_Name(resultSet.getString("u_Name")); user.setU_Password(resultSet.getString("u_password")); user.setU_Type(resultSet.getInt("u_Type")); user.setU_Group(resultSet.getString("u_group")); if (" ".equals(user.getU_Name())||user.getU_Name()==null) { user.setU_Name("空"); } if (" ".equals(user.getU_Group())||user.getU_Group()==null) { user.setU_Group("空"); } users.add(user); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return users; } @Override public List<User> find_group(String group) {//查询组名为:group的小组成员//若用户名还没填,则设置用户名为"空" Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select * from p_user where u_group = ?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; //集合中只能放入user对象 List<User> users = new ArrayList<User>(); User user = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, group); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { user = new User(); user.setU_Id(resultSet.getInt("u_Id")); user.setU_Nickname(resultSet.getString("u_nickname")); user.setU_Name(resultSet.getString("u_Name")); user.setU_Password(resultSet.getString("u_password"));; user.setU_Type(resultSet.getInt("u_Type")); user.getU_Group(); if (" ".equals(user.getU_Name())||user.getU_Name()==null) { user.setU_Name("空"); } users.add(user); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return users; } @Override public void add_ErrorQuestion(ErrorQuestion errorQuestion) {//向数据库中增加错题 //获得链接对象 Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { String sql = "insert into p_errorquestion(u_Id,q_Question,q_RorrectResult,q_Date,q_Type) value (?,?,?,NOW(),?)";//将数据库里的时间设为系统现在的时间 preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, errorQuestion.getU_Id()); preparedStatement.setString(2,errorQuestion.getQ_Question() ); preparedStatement.setString(3, errorQuestion.getQ_CorrectResult()); preparedStatement.setInt(4, errorQuestion.getQ_Type()); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //关闭资源 DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void delete_ErrorQuestion(int id) {//通过错题的id来删除错题 Connection connection = DBUtil.getConnection(); String sql = "delete from p_errorquestion where q_Id= ?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,id); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void add_operation(Operation operation) { Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { String sql = "insert into p_histoty(u_id,q_Question,q_RorrectResult,q_Date,q_Type,q_Status,q_inputResult) value (?,?,?,NOW(),?,?,?)";//将数据库里的时间设为系统现在的时间 preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, operation.getU_Id()); preparedStatement.setString(2,operation.getQ_Question() ); preparedStatement.setString(3, operation.getQ_CorrectResult()); preparedStatement.setInt(4, operation.getQ_Type()); preparedStatement.setInt(5, operation.getQ_Status()); preparedStatement.setString(6, operation.getQ_InputResult()); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //关闭资源 DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void delete_Operation(int id) {//通过题目的id来删除题目 Connection connection = DBUtil.getConnection(); String sql = "delete from p_history where q_id= ?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,id); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public List<ErrorQuestion> find_ErrorQuestions(int U_id) {//查询同一个用户的所有错题 Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select * from p_errorquestion where u_Id=?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; //集合中只能放入user对象 List<ErrorQuestion> errorQuestions = new ArrayList<ErrorQuestion>(); ErrorQuestion errorQuestion= null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,U_id); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { errorQuestion = new ErrorQuestion(); errorQuestion.setQ_Id(resultSet.getInt("q_Id")); errorQuestion.setU_Id(resultSet.getInt("u_Id")); errorQuestion.setQ_Question(resultSet.getString("q_Question")); errorQuestion.setQ_CorrectResult(resultSet.getString("q_RorrectResult")); errorQuestion.setQ_Date(resultSet.getString("q_Date")); errorQuestion.setQ_Type(resultSet.getInt("q_Type")); errorQuestions.add(errorQuestion); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return errorQuestions; } @Override public List<Operation> find_Operations(int U_id) {//查询同一个用户的所有题目 Connection connection = DBUtil.getConnection(); //准备sql语句 String sql = "select * from p_history where u_id=?"; //创建语句传输对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; //集合中只能放入user对象 List<Operation> operations = new ArrayList<Operation>(); Operation operation = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,U_id); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { operation = new Operation(); operation.setQ_id(resultSet.getInt("q_id")); operation.setU_Id(resultSet.getInt("u_Id")); operation.setQ_Question(resultSet.getString("q_Question")); operation.setQ_CorrectResult(resultSet.getString("q_RorrectResult")); operation.setQ_Date(resultSet.getString("q_Date")); operation.setQ_Type(resultSet.getInt("q_Type")); operation.setQ_Status(resultSet.getInt("q_Status")); operation.setQ_InputResult(resultSet.getString("q_inputResult")); operations.add(operation); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return operations ; } }
没遇到什么困难,但是在完成任务时发现原来准备在数据库里填写的东西和题目类一些不完善,所以花很长时间又去改数据库表和类。