Maven的JavaWeb网站开发之数据库数据的编辑操作（Layui框架的使用）

package dao.Impl; import dao.UserDAO; import dto.Result; import entity.User; import utils.DBUtils; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class UserDAOImpl implements UserDAO { @Override public User userLogin(String name, String password) throws SQLException { DBUtils db = new DBUtils(); String sql = "select * from user where name=? and password=?"; PreparedStatement ps = db.getPreparedStatement(sql); ps.setString(1,name); ps.setString(2,password); ResultSet rs = ps.executeQuery(); User user = new User(); while (rs.next()){ user.setId(rs.getInt("id")); user.setUsernick(rs.getString("usernick")); user.setName(rs.getString("name")); user.setPassword(rs.getString("password")); user.setCity(rs.getString("city")); user.setSex(rs.getString("sex")); user.setAge(rs.getString("age")); user.setQq(rs.getString("qq")); user.setTel(rs.getString("tel")); return user; } return null; } @Override public int register(User user) throws SQLException { DBUtils db = new DBUtils(); String sql = "insert into user(usernick,name,password) value(?,?,?)"; PreparedStatement ps = db.getPreparedStatement(sql); ps.setString(1, user.getUsernick()); ps.setString(2, user.getName()); ps.setString(3, user.getPassword()); int rs = ps.executeUpdate(); if (rs == 1){ db.commit(); return 1; }else { db.connectionRollback(); return 0; } } @Override public User selectUserName(String name) throws SQLException { DBUtils db = new DBUtils(); String sql = "select * from user where name=?"; PreparedStatement ps = db.getPreparedStatement(sql); ps.setString(1,name); System.out.println(ps); ResultSet rs = ps.executeQuery(); User user = new User(); while(rs.next()){ user.setName(rs.getString("name")); return user; } return null; } @Override public List<Object> selectUserAll(String page, String limit) throws SQLException { DBUtils db = new DBUtils(); String sql = "select * from `user` LIMIT ?,?"; PreparedStatement ps = db.getPreparedStatement(sql); int page1 = Integer.parseInt(page); int limit1 = Integer.parseInt(limit); ps.setInt(1, (page1 - 1) * limit1); ps.setInt(2, limit1); ResultSet rs = ps.executeQuery(); List<Object> list = new ArrayList<Object>(); while(rs.next()){ User user = new User(); user.setId(rs.getInt("id")); user.setUsernick(rs.getString("usernick")); user.setName(rs.getString("name")); user.setPassword(rs.getString("password")); user.setCity(rs.getString("city")); user.setSex(rs.getString("sex")); user.setAge(rs.getString("age")); user.setQq(rs.getString("qq")); user.setTel(rs.getString("tel")); list.add(user); } return list; } @Override public int userCount() throws SQLException { DBUtils db = new DBUtils(); String sql = "select count(*) as sum from user"; Statement st = db.getStatement(); ResultSet rs = st.executeQuery(sql); while (rs.next()) { return rs.getInt("sum"); } return 0; } @Override public int userDelete(int id) throws SQLException { DBUtils db = new DBUtils(); String sql = "delete from user where id=?"; PreparedStatement ps = db.getPreparedStatement(sql); ps.setInt(1,id); int s = ps.executeUpdate(); if (s == 1){ db.commit(); return 1; }else { db.connectionRollback(); return 0; } } @Override public int userEdit(User user) throws SQLException { DBUtils db = new DBUtils(); String sql = "update user set usernick=?,name=?,city=?,sex=?,age=?,qq=?,tel=? where id=?"; PreparedStatement ps = db.getPreparedStatement(sql); ps.setString(1,user.getUsernick()); ps.setString(2,user.getName()); ps.setString(3,user.getCity()); ps.setString(4,user.getSex()); ps.setString(5,user.getAge()); ps.setString(6,user.getQq()); ps.setString(7,user.getTel()); ps.setInt(8,user.getId()); int s = ps.executeUpdate(); if (s == 1){ db.commit(); return 1; }else { db.connectionRollback(); return 0; } } }