Java开发学习(6)

人事管理系统从无到有5

把昨天的测试写入DAO层 增删改查
条件查询通过主键（id）来查询只会返回一个， 非主键来查询会返回多个

//findById//

 public User findById(int id) {
                    User user = null;//声明对象
        try {
            Connection conn = MySqlDbUtil.getConnect();
            String sql ="SELECT * FROM user WHERE id=? ";
            PreparedStatement pstmt =conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs =pstmt.executeQuery();//结果集 excel表格
            if(rs.next()){
                user = new User();//实例化
                user.setId(rs.getInt("id"));
                user.setLoginName(rs.getString("loginName"));//常量改成变量
                user.setLoginPwd(rs.getString("loginPwd"));
                user.setNickname(rs.getString("nickname"));
                user.setIden(rs.getString("iden"));
//                System.out.println(" id="+ id + " loginName=" + loginName + " loginPwd=" + loginPwd +" nickname=" + nickname +" iden=" + iden);
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }

        return user;//返回user
    }

//findAll//需要遍历

public List<User> findAll() {//遍历
        List<User> users = new ArrayList<>();
        try {
            Connection conn = MySqlDbUtil.getConnect();
            String sql = "SELECT * FROM user";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setLoginName(rs.getString("loginName"));
                user.setLoginPwd(rs.getString("loginPwd"));
                user.setNickname(rs.getString("nickname"));
                user.setIden(rs.getString("iden"));
                users.add(user);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return users;
    }

写测试类测试一下方法。。。

要测试的时候出错了IDEACould not load class with name
无法运行这个类，按照网上方法不行，自己改了文件
通过右键选中Mark Directory as，接着设置为Test Sources Root(绿色)即可

条件查询

    @Override
    public List<User> find(User user) {
        String sql = "SELECT * FROM user WHERE 1=1";
        List<Object> params =new ArrayList<>();
        if(user!=null){
         if(user.getLoginName()!=null&&!"".equals(user.getLoginName())){
             sql = sql  +"AND loginName LIKE ?";
             params.add("%" +user.getLoginName() +"%");
         }
            if(user.getIden()!=null&&!"".equals(user.getIden())){
                sql = sql  +"AND iden LIKE ?";
                params.add("%" +user.getIden() +"%");
            }
        }

        List<User> users = new ArrayList<>();
        try {
            Connection conn = MySqlDbUtil.getConnect();
//            String sql = "SELECT * FROM user";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            int i = 0;
            for(Object obj:params){
                i ++;
                pstmt.setObject(i,obj);
            }
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                User user1 = new User();
                user1.setId(rs.getInt("id"));
                user1.setLoginName(rs.getString("loginName"));
                user1.setLoginPwd(rs.getString("loginPwd"));
                user1.setNickname(rs.getString("nickname"));
                user1.setIden(rs.getString("iden"));
                users.add(user1);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return users;
    }

   @Test
    public void testFind(){
        IUserDao dao =new UserDao();
        User u = new User();
      u.setLoginName("多");
        List<User> users = dao.find(u);
        for(User user : users){
            System.out.println(user.toString());
        }
    }

     String sql = "SELECT * FROM user WHERE 1=1";
     sql = sql  +" AND loginName LIKE ? ";

引号前面要有空格。。。

分页查询的util

package cn.edu.mju.wjy29.utils;

import java.util.List;

/**
分页查询
 */
public class Pager {
    private int page = 1;
    private int pageSize = 10;
    private int rows;
    private int pages;
    private boolean first = true;
    private boolean last = false;
    private int prev = 1;
    private int next = 1;
    private List<Object> data;

    public Pager() {
    }

    public Pager(int page, int pageSize) {
        this.page = page;
        this.pageSize = pageSize;
    }

    public int getPage() {
        if(page > pages){
            return pages;
        }else {
            return page;
        }
    }

    public void setPage(int page) {
        if(page < 1){
            page = 1;
        }
        this.page = page;
    }

    public int getPrev() {
        prev = page -1;
        if(prev<1){
            prev = 1;
        }
        return prev;
    }

    public int getNext() {
        getPages();
        next = page + 1;
        if(next > pages){
            next = pages;
        }
        return next;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        if(pageSize<1){
            pageSize = 10;
        }
        this.pageSize = pageSize;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
        getPages();
    }

    public int getPages() {
        pages = (rows / pageSize);
        if(pages*pageSize < rows){
            pages = pages + 1;
        }
        return pages;
    }

    public boolean isFirst() {
        if(page == 1){
            return true;
        }else{
            return false;
        }
    }

    public boolean isLast() {
        if(page == pages){
            return true;
        }else {
            return false;
        }
    }

    public List<Object> getData() {
        return data;
    }

    public void setData(List<Object> data) {
        this.data = data;
    }
}

    @Override
    public Pager paginate(int page, int pagesize, User user) {

        Pager pager =new Pager();
        pager.setPageSize(pagesize);
        pager.setPage(page);
        String sql = "SELECT COUNT(*) AS A FROM user";//取总的记录数
        StringBuilder _where = new StringBuilder(" WHERE 1=1");//按条件来取(动态字符串)
        List<Object> params =new ArrayList<>();
        if(user!=null){
            if(user.getLoginName()!=null&&!"".equals(user.getLoginName())){
                _where.append(" AND loginName LIKE ? ") ;
                params.add("%" +user.getLoginName() +"%");
            }
            if(user.getIden()!=null&&!"".equals(user.getIden())){
                _where.append(" AND iden LIKE ? ");
                params.add("%" +user.getIden() +"%");
            }
        }
        Connection conn = null;
        try {
            conn = MySqlDbUtil.getConnect();
            PreparedStatement pst = conn.prepareStatement(sql + _where.toString());
            for(int i=0;i < params.size();i++ ){
                pst.setObject(i+1,params.get(i));
            }
            ResultSet rs =pst.executeQuery();
            int count = rs.getInt("A");
            rs.close();
            pst.close();

            pager.setRows(count);
            page =pager.getPage();
            sql ="SELECT *FROM user";
            pst = conn.prepareStatement(sql + _where.toString() +"LIMIT ?,?");
           int index =0;
            for(int i=0;i < params.size();i++ ){
                index ++;
                pst.setObject(i+1,params.get(i));
            }

         pst.setInt(index +1,(pager.getPage()-1)*pager.getPageSize());
            pst.setInt(index++,pager.getPageSize());
         rs = pst.executeQuery();//读数据
            List<Object> users = new ArrayList<>();
            while(rs.next()){
                User user1 = new User();
                user1.setId(rs.getInt("id"));
                user1.setLoginName(rs.getString("loginName"));
                user1.setLoginPwd(rs.getString("loginPwd"));
                user1.setNickname(rs.getString("nickname"));
                user1.setIden(rs.getString("iden"));
                users.add(user1);
            }
            pager.setData(users);
            rs.close();
            pst.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }