struts2+JDBC+mysql增删改分页功能

package impl; 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 dao.UserDao; import db.MyConnection; import entities.User; /** * 具体实现UserDao接口方法的UserImple类 * @author 宋波 * */ public class UserImpl implements UserDao { /* * 创建数据库连接 */ private Connection connection = MyConnection.getConnection(); /* * 用于执行sql语句的类 */ private PreparedStatement preparedStatement; /* * 返回结果集 */ private ResultSet resultSet; @Override public void insert(String userName, String password) { // TODO Auto-generated method stub String sql="insert into t_user(user_name,password) value(?,?)"; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, userName); preparedStatement.setString(2, password); preparedStatement.execute(); preparedStatement.close(); System.out.println("EXCUTE SQL：insert" + " into t_user(user_name,password) " + "value('"+userName+"','"+password+"');"); System.out.println("Save user success \n" +"User Name:"+userName+"\nUserPassword:" +password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println("Save user failure"); } } @Override public void delete(int id) { // TODO Auto-generated method stub String sql="delete from t_user where id=?"; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, id); preparedStatement.executeUpdate(); preparedStatement.close(); System.out.println("EXCUTE SQL：delete" + " from t_user " + "where id='"+id+"');"); System.out.println("Delete user success"); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); System.out.println("Delete user failure"); } } @Override public List<User> select() { // TODO Auto-generated method stub List<User> users = new ArrayList<User>(); String sql = "select * from t_user"; try { preparedStatement = connection.prepareStatement(sql); System.out.println("EXCUTE SQL：select * from t_user"); resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ User user = new User(); user.setUsername(resultSet.getString("user_name")); user.setPassword(resultSet.getString("password")); System.out.println("DataBase ID:"+resultSet.getInt("id")); System.out.println("User Name:"+resultSet.getString("user_name")); System.out.println("User Passwrod:"+resultSet.getString("password")); users.add(user); } } catch (Exception e) { // TODO: handle exception } return users; } @Override public void update(String password,Integer id) { // TODO Auto-generated method stub String sql="update t_user set password=? where id=? "; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, password); preparedStatement.setInt(2, id); preparedStatement.executeUpdate(); preparedStatement.close(); System.out.println("EXCUTE SQL：update" + " t_user set password=" + "'"+password+"' where id='"+id+"';"); System.out.println("Update password success"); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); System.out.println("Upate password failure"); } } @Override public List<User> select(int currPage) { // TODO Auto-generated method stub List<User> users = new ArrayList<User>(); //总页数计算方法 其中10代表每页最大显示条数 int totalPage = (users.size()+10-1)/10; //每页第一条数据计算方法 其中10代表每页最大显示条数 int fisrtResult = (currPage-1)*10; if(currPage<1){ currPage=1; }else if(currPage>totalPage){ currPage=totalPage; } String sql = "select * from t_user order by id limit ?,?"; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, fisrtResult); preparedStatement.setInt(2, 5); System.out.println("EXCUTE SQL：select * from t_user order by id limit "+fisrtResult+",5;"); resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ User user = new User(); user.setId(resultSet.getInt("id")); user.setUsername(resultSet.getString("user_name")); user.setPassword(resultSet.getString("password")); System.out.println("DataBase ID:"+resultSet.getInt("id")); System.out.println("User Name:"+resultSet.getString("user_name")); System.out.println("User Passwrod:"+resultSet.getString("password")); users.add(user); } } catch (Exception e) { // TODO: handle exception } return users; } @Override public List<User> select(int currPage, String userName, String password) { // TODO Auto-generated method stub List<User> users = new ArrayList<User>(); //总页数计算方法 其中2代表每页最大显示条数 int totalPage = (users.size()+2-1)/2; //每页第一条数据计算方法 其中2代表每页最大显示条数 int fisrtResult = (currPage-1)*2; if(currPage<1){ currPage=1; }else if(currPage>totalPage){ currPage=totalPage; } String sql = "select * from t_user where user_name=? or password=? order by id limit ?,?"; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, userName); preparedStatement.setString(2, password); preparedStatement.setInt(3, fisrtResult); preparedStatement.setInt(4, 2); System.out.println("EXCUTE SQL：select * from t_user where user_name="+userName+" or password="+password+" order by id limit "+fisrtResult+",5;"); resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ User user = new User(); user.setId(resultSet.getInt("id")); user.setUsername(resultSet.getString("user_name")); user.setPassword(resultSet.getString("password")); System.out.println("DataBase ID:"+resultSet.getInt("id")); System.out.println("User Name:"+resultSet.getString("user_name")); System.out.println("User Passwrod:"+resultSet.getString("password")); users.add(user); } } catch (Exception e) { // TODO: handle exception } return users; } }