2202Aweb2.rar

package com.ty.dao; import com.mysql.cj.util.StringUtils; import com.ty.bean.PageInfo; import com.ty.bean.User; import com.ty.utils.JdbcUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class UserDao { private QueryRunner qr; public UserDao() { qr = new QueryRunner(JdbcUtils.getDataSource()); } //添加 public void insert(User user){ try { String sql = "insert into user values(null,?,?,?,?,?,?)"; qr.update(sql,user.getUsername(),user.getPassword(),user.getCreatedate(),user.getSex(),user.getLike(),user.getPhoto()); System.out.println("注册成功"); } catch (SQLException throwables) { throwables.printStackTrace(); } } //登录查询 public User login(String username,String password){ User user = null; try { String sql = "select * from user where username=? and password=?"; List<User> users = qr.query(sql, new BeanListHandler<User>(User.class),username,password); if (users.size()>0){//表示查到了匹配的账号数据 user = users.get(0); } } catch (SQLException throwables) { throwables.printStackTrace(); } return user; } //查询，按条件查询 public PageInfo query(String username, String sex,int pageNum,int pageSize){ PageInfo pageInfo = null; try { // String sql = "select * from user where username like ? and sex=?"; // users = qr.query(sql,new BeanListHandler<User>(User.class),"%" +username+ "%",sex); StringBuilder sb = new StringBuilder("select * from user where 1=1");//用StringBuilder拼装sql字符串 ArrayList<Object> params = new ArrayList<>();//收集?对应的数据 if(!StringUtils.isNullOrEmpty(username)){//用户名非空，则参与sql查询 sb.append(" and username like ?"); params.add("%"+username+"%"); } if (!StringUtils.isNullOrEmpty(sex)){ sb.append(" and sex=?"); params.add(sex); } //查询所有满足条件的数据 String sql = sb.toString();//得到最终的sql语句 List<User> totalUsers = qr.query(sql, new BeanListHandler<User>(User.class), params.toArray());//params可以做为参数传入 int total = totalUsers.size();//获得满足条件的数据的总数量 //分页的sql 0 1 2 3 4 5 6 7 sb.append(" limit ?,?");//添加分页 params.add((pageNum-1)*pageSize);//分页第一个?赋值：开始下标 params.add(pageSize);//分页第二个?赋值：每页显示条数 sql = sb.toString(); //查询当前页的数据 List<User> users = qr.query(sql, new BeanListHandler<User>(User.class), params.toArray()); pageInfo = new PageInfo(pageNum,pageSize,0,total,users);//创建分页对象 } catch (SQLException throwables) { throwables.printStackTrace(); } return pageInfo; } public void delete(int id) { String sql = "delete from user where id=?"; try { qr.update(sql,id); System.out.println("删除成功"); } catch (SQLException throwables) { throwables.printStackTrace(); } } public User findByid(int id) { String sql = "select * from user where id=?"; User user = null; try { user = qr.query(sql, new BeanHandler<User>(User.class), id);//通过id查询修改的数据 } catch (SQLException throwables) { throwables.printStackTrace(); } return user;//返回查询的修改对象 } //实现最终的修改 public void update(User user) { String sql = "update user set username=?,password=?,createdate=?,sex=?,`like`=? where id=?"; try { qr.update(sql,user.getUsername(),user.getPassword(),user.getCreatedate(),user.getSex(),user.getLike(),user.getId()); } catch (SQLException throwables) { throwables.printStackTrace(); } System.out.println("修改成功"); } //通过账户名查询数据库，得到结果集 public List<User> checkName(String username) { List<User> users = null; String sql = "select * from user where username=?"; try { users = qr.query(sql,new BeanListHandler<User>(User.class),username); } catch (SQLException throwables) { throwables.printStackTrace(); } return users; } }