数据库 表数据添加&分页查询 --Java实现

分页数据实体类

public class PageBean implements Serializable {
    private int rowsPerPage = 15;
    private int rowsNum;//行数
    private int maxPage;//页数
    private int pageNum;//页码
}

Emp实体类

@Data
public class Emp implements Serializable {
    private Long id;
    private String username;
    private String realname;
    private transient String password;
    private Date birth;
    private Boolean sex;
    private Long deptId;
    private Dept dept;
}

Dept实体类

@Data
public class Dept implements Serializable {
    private Long id;
    private String name;
    private String address;
}

JdbcUtil工具类

public class JdbcUtil {
    private JdbcUtil() {
    }

    private static DataSource dataSource;

    static {
        try {
            Properties properties = new Properties();
            InputStream inputStream = JdbcUtil.class.getResourceAsStream("jdbc.properties");
            properties.load(inputStream);
            DruidDataSource ds = new DruidDataSource();
            ds.setDriverClassName(properties.getProperty("jdbc.driver"));
            ds.setUrl(properties.getProperty("url"));
            ds.setUsername(properties.getProperty("username"));
            ds.setPassword(properties.getProperty("password"));
            dataSource = ds;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception {
        if (dataSource != null)
            return dataSource.getConnection();
        return null;
    }

    public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) throws Exception {
        try {
            if (resultSet != null)
                resultSet.close();
        } finally {
            try {
                if (preparedStatement != null)
                    preparedStatement.close();
            } finally {
                if (connection != null)
                    connection.close();
            }
        }
    }

    public static PreparedStatement createPreparedStatement(Connection connection, String sql, Object... params) throws Exception {
        if (connection != null) {
            PreparedStatement ps = connection.prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            return ps;
        }
        return null;
    }

    public static ResultSet executeQurey(Connection connection, String sql, Object... params) throws Exception {
        ResultSet res = null;
        if (connection != null) {
            PreparedStatement ps = createPreparedStatement(connection, sql, params);
            if (ps != null) {
                res = ps.executeQuery();
            }
        }
        return res;
    }
    public static int executeUpdate(Connection connection, String sql, Object... params) throws Exception {
        int res = 0;
        if (connection != null) {
            PreparedStatement ps = createPreparedStatement(connection, sql, params);
            if (ps != null) {
                res = ps.executeUpdate();
            }
        }
        return res;
    }
}

StringUtil工具类

public class StringUtil {
    private StringUtil(){}
    public static boolean isBlank(String str){
        return str==null||str.trim().length()<1;
    }
    public static boolean nonBlank(String str){
        return !isBlank(str);
    }
}

 

Emp 数据添加

public int insert(Emp data) {
    int res = 0;
    if (data == null){
        throw new RuntimeException("参数异常");
    }
    if (StringUtil.isBlank(data.getUsername()) || StringUtil.isBlank(data.getRealname())||StringUtil.isBlank(data.getPassword())){
        throw new RuntimeException("参数不合法");
    }
    if (data.getDept()==null){
        throw new RuntimeException("参数不合法");
    }
    ArrayList<Object> params = new ArrayList<>();
    StringBuffer sb = new StringBuffer("insert into tbl_emp(username,realname,password,dept_id");
    if (data.getBirth()!=null){
        sb.append(",birth");
    }
    if (data.getSex()!=null) {
        sb.append(",sex");
    }
    sb.append(") values (?,?,?,?");
    params.add(data.getUsername());
    params.add(data.getRealname());
    params.add(data.getUsername());
    params.add(data.getDeptId());
    if (data.getBirth()!=null){
        sb.append(",?");
        params.add(data.getBirth());
    }
    if (data.getSex()!=null){
        sb.append(",?");
        params.add(data.getSex());
    }
    String sql = sb.toString();
    Connection conn =null;
    try {
        conn = JdbcUtil.getConnection();
        res = JdbcUtil.executeUpdate(conn,sql,params);
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        try {
            JdbcUtil.close(null,null,conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    return res;
}

Emp 分页查询

public List<Emp> selectByPage(Emp data, PageBean pages) {
    List<Emp> res = new ArrayList<>();
    List<Object> params = new ArrayList<>();
    StringBuffer sb = new StringBuffer("select e.*,d.name,d.address from tbl_emp e join tbl_dept d on e.dept_id = d.id where 1=1");
    if (data!=null){
        if (data.getId()!=null){
            sb.append(" and id = ?");
            params.add(data.getId());
        }
        if (StringUtil.nonBlank(data.getUsername())){
            sb.append(" and username like ?");
            params.add(data.getUsername());
        }
        if (StringUtil.nonBlank(data.getRealname())){
            sb.append(" and realname like ?");
            params.add(data.getRealname());
        }
        if (StringUtil.nonBlank(data.getPassword())){
            sb.append(" and password like ?");
            params.add(data.getPassword());
        }
        if (data.getDeptId()!=null){
            sb.append(" and dept_id = ?");
            params.add(data.getDeptId());
        }
        if (data.getSex()!=null){
            sb.append(" and sex = ");
            params.add(data.getSex());
        }
        if (data.getBirth() != null){
            sb.append(" and birth = ?");
            params.add(data.getBirth());
        }
    }
    String sql = sb.toString();
    Connection conn = null;
    ResultSet rs = null;
    try {
        conn = JdbcUtil.getConnection();
        if (pages!=null&& pages.getRowsPerPage()>0){
            if (pages.getPageNum()<1){
                pages.setRowsNum(1);
            }
            if (pages.getMaxPage()<1){
                String sql2 = "select count(*) "+sql.substring(sql.indexOf(" from "));
                rs = JdbcUtil.executeQurey(conn,sql2,params.toArray());
                if (rs.next()){
                    int rowsNum = rs.getInt(1);
                    if (rowsNum<1){
                        return res;
                    }
                    int maxPage = rowsNum/pages.getRowsPerPage();
                    if (rowsNum%pages.getRowsPerPage()!=0)
                        maxPage++;
                    pages.setRowsNum(rowsNum);
                    pages.setMaxPage(maxPage);
                }
            }
            if (pages.getPageNum()>pages.getMaxPage()){
                pages.setPageNum(pages.getMaxPage());
            }
            int begin = (pages.getPageNum()-1)*pages.getRowsPerPage();
            sql = sql + " limit " + begin + ","+pages.getRowsPerPage();
        }
        rs = JdbcUtil.executeQurey(conn,sql,params.toArray());
        while (rs.next()){
            Emp emp = new Emp();
            emp.setId(rs.getLong("id"));
            emp.setUsername(rs.getString("username"));
            emp.setRealname(rs.getString("realname"));
            emp.setPassword(rs.getString("password"));
            emp.setDeptId(rs.getLong("dept_id"));
            emp.setBirth(rs.getDate("birth"));
            emp.setSex(rs.getBoolean("sex"));
            Dept dept = new Dept();
            dept.setId(rs.getLong("dept_id"));
            dept.setName(rs.getString("name"));
            dept.setAddress(rs.getString("address"));
            emp.setDept(dept);
            res.add(emp);
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }finally {
        try {
            JdbcUtil.close(rs,null,conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    return res;
}