原生servlet_CRUD

package com.hqyj.gyq.dao.impl; import com.hqyj.gyq.dao.UserDao; import com.hqyj.gyq.entity.User; import com.hqyj.gyq.utils.JDBCUtils; import java.sql.*; import java.util.ArrayList; import java.util.List; public class UserDaoImpl implements UserDao { private static Connection conn = null; private static PreparedStatement pre = null; private static ResultSet rs = null; @Override public List<User> queryUserAll() { try { conn = JDBCUtils.getConnection(); String sql = "select * from user"; pre = conn.prepareStatement(sql); rs = pre.executeQuery(); List<User> users = new ArrayList<>(); while(rs.next()){ User u = new User(); u.setId(rs.getInt("user_id")); u.setName(rs.getString("user_name")); u.setAge(rs.getString("user_age")); u.setSex(rs.getString("user_sex")); u.setTel(rs.getString("tel")); users.add(u); } return users; } catch (Exception e) { e.printStackTrace(); return null; }finally { JDBCUtils.close(conn,pre); JDBCUtils.close(rs); } } @Override public User queryUserById(int id) { try { conn = JDBCUtils.getConnection(); String sql = "select * from user where user_id=?"; pre = conn.prepareStatement(sql); pre.setInt(1,id); rs = pre.executeQuery(); while(rs.next()){ User u = new User(); u.setId(rs.getInt("user_id")); u.setName(rs.getString("user_name")); u.setAge(rs.getString("user_age")); u.setSex(rs.getString("user_sex")); u.setTel(rs.getString("tel")); return u; } } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtils.close(conn,pre); JDBCUtils.close(rs); } return null; } @Override public boolean removeUserById(int id) { try { conn = JDBCUtils.getConnection(); String sql = "delete from user where user_id = ?"; pre = conn.prepareStatement(sql); pre.setInt(1,id); int i = pre.executeUpdate(); if(i!=0) { return true; } else {return false;} } catch (SQLException e) { e.printStackTrace(); return false; }finally { JDBCUtils.close(conn,pre); JDBCUtils.close(rs); } } @Override public int updateUser(User u) { try { conn = JDBCUtils.getConnection(); String sql = "UPDATE user set user_name = ?,user_age = ?,user_sex = ?,tel = ? where user_id = ?"; pre = conn.prepareStatement(sql); pre.setString(1,u.getName()); pre.setString(2,u.getAge()); pre.setString(3,u.getSex()); pre.setString(4,u.getTel()); pre.setInt(5,u.getId()); int i = pre.executeUpdate(); return i; } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.close(conn,pre); JDBCUtils.close(rs); } return 0; } @Override public int addUser(User u) { try { conn = JDBCUtils.getConnection(); String sql = "insert into user values(null,?,?,?,?)"; pre = conn.prepareStatement(sql); pre.setString(1,u.getName()); pre.setString(2,u.getSex()); pre.setString(3,u.getAge()); pre.setString(4,u.getTel()); int i = pre.executeUpdate(); return i; } catch (Exception e) { e.printStackTrace(); return 0; }finally { JDBCUtils.close(conn,pre); } } @Override public List<User> searchUser(User user) { try { conn = JDBCUtils.getConnection(); String sql = "select * from user where 1=1 "; List<Object> params = new ArrayList<>(); if(!(user.getName().equals("")||user.getName()==null)){ sql+="and user_name = ?"; params.add(user.getName()); } if(!(user.getTel().equals("")||user.getTel()==null)){ sql+="and tel = ?"; params.add(user.getTel()); } pre = conn.prepareStatement(sql); for (int i = 0; i <params.size() ; i++) { pre.setObject(i+1,params.get(i)); } rs = pre.executeQuery(); List<User> users = new ArrayList<>(); while (rs.next()){ User u = new User(); u.setId(rs.getInt("user_id")); u.setName(rs.getString("user_name")); u.setSex(rs.getString("user_sex")); u.setAge(rs.getString("user_age")); u.setTel(rs.getString("tel")); users.add(u); } return users; } catch (SQLException throwables) { throwables.printStackTrace(); return null; }finally { JDBCUtils.close(conn,pre); JDBCUtils.close(rs); } } @Override public List<User> queryUserPage(Integer currentPage, Integer pageSize) { try { conn = JDBCUtils.getConnection(); String sql = "select * from user limit ?,?"; pre = conn.prepareStatement(sql); pre.setInt(1,(currentPage-1)*pageSize); pre.setInt(2,pageSize); rs = pre.executeQuery(); List<User> users = new ArrayList<>(); while(rs.next()){ User u = new User(); u.setId(rs.getInt("user_id")); u.setName(rs.getString("user_name")); u.setAge(rs.getString("user_age")); u.setSex(rs.getString("user_sex")); u.setTel(rs.getString("tel")); users.add(u); } return users; } catch (Exception e) { e.printStackTrace(); return null; } } @Override public int countUserAll() { try { conn = JDBCUtils.getConnection(); String sql = "select count(*) as countAll from user"; pre = conn.prepareStatement(sql); rs = pre.executeQuery(); if(rs.next()){ int count = rs.getInt("countAll"); return count; } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.close(conn,pre); JDBCUtils.close(rs); } return 0; } }