1.通用分页的优势
1.简化代码
：Java中通用的分页工具类可以集成到项目中，避免每个分页查询都需要手动编写分页代码，可以减少重复代码的编写量。
2.可维护性
：使用通用的分页工具类可以使代码更加可维护，当需要改变分页参数时，只需要修改分页工具类，而不需要修改每个分页查询代码。
3.可移植性
：通用分页工具类可以应用于不同的数据库和ORM框架，具有较强的可移植性。
4更高的效率
：通用的分页工具类可以通过封装分页参数，减少分页查询的重复代码量，提高代码执行效率。
5.良好的扩展性
：使用通用的分页工具类可以支持更多的分页参数设置，例如默认分页大小、最大分页大小、排序规则等。
2.反射完成通用查询功能
1.实体类
package com.niyin.entity; public class Book { private int bid; private String bname; private float price; @Override public String toString() { return "Book [bid=" + bid + ", bname=" + bname + ", price=" + price + "]"; } public int getBid() { return bid; } public void setBid(int bid) { this.bid = bid; } public String getBname() { return bname; } public void setBname(String bname) { this.bname = bname; } public float getPrice() { return price; } public void setPrice(float price) { this.price = price; } }
1.定义分页工具类
package com.niyin.util; /** * 分页工具类 * */ public class PageBean { private int page = 1;// 页码 private int rows = 10;// 页大小 private int total = 0;// 总记录数 private boolean pagination = true;// 是否分页 public PageBean() { super(); } public int getPage() { return page; } public void setPage(int page) { this.page = page; } public int getRows() { return rows; } public void setRows(int rows) { this.rows = rows; } public int getTotal() { return total; } public void setTotal(int total) { this.total = total; } public void setTotal(String total) { this.total = Integer.parseInt(total); } public boolean isPagination() { return pagination; } public void setPagination(boolean pagination) { this.pagination = pagination; } /** * 获得起始记录的下标 * * @return */ public int getStartIndex() { return (this.page - 1) * this.rows; } @Override public String toString() { return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]"; } }
3. 连接数据库帮助类
package com.niyin.util; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * 提供了一组获得或关闭数据库对象的方法 * */ public class DBAccess { private static String driver; private static String url; private static String user; private static String password; static {// 静态块执行一次，加载 驱动一次 try { InputStream is = DBAccess.class .getResourceAsStream("config.properties"); Properties properties = new Properties(); properties.load(is); driver = properties.getProperty("driver"); url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("pwd"); Class.forName(driver); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } /** * 获得数据连接对象 * * @return */ public static Connection getConnection() { try { Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } public static void close(ResultSet rs) { if (null != rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } public static void close(Statement stmt) { if (null != stmt) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } public static void close(Connection conn) { if (null != conn) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } public static void close(Connection conn, Statement stmt, ResultSet rs) { close(rs); close(stmt); close(conn); } public static boolean isOracle() { return "oracle.jdbc.driver.OracleDriver".equals(driver); } public static boolean isSQLServer() { return "com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driver); } public static boolean isMysql() { return "com.mysql.cj.jdbc.Driver".equals(driver); } public static void main(String[] args) { Connection conn = DBAccess.getConnection(); System.out.println(conn); DBAccess.close(conn); System.out.println("isOracle：" + isOracle()); System.out.println("isSQLServer：" + isSQLServer()); System.out.println("isMysql：" + isMysql()); System.out.println("数据库连接(关闭)成功"); } }
4.定义bookdao
package Dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.niyin.entity.Book; import com.niyin.util.DBAccess; import com.niyin.util.PageBean; import com.niyin.util.StringUtils; public class BookDao { public List<Book> list(Book book,PageBean pageBean) throws Exception { List<Book>list=new ArrayList<Book>(); Connection conn = DBAccess.getConnection(); String sql="select * from t_mvc_book where 1=1 "; String bname = book.getBname(); if (StringUtils.isNotBlank(bname)) { sql +=" and bname like '%"+bname+"%'"; } PreparedStatement pst = conn.prepareStatement(sql); ResultSet rs = pst.executeQuery(); while(rs.next()) { Book b=new Book(); b.setBid(rs.getInt("bid")); b.setBname(rs.getString("bname")); b.setPrice(rs.getFloat("price")); list.add(b); } return list; } public static void main(String[] args) throws Exception { BookDao bookdao=new BookDao(); Book book=new Book(); book.setBname("圣墟"); PageBean pageBean=new PageBean(); List<Book> list = bookdao.list(book, pageBean); for (Book book2 : list) { System.out.println(book2); } } }
5定义basedao
package com.niyin.util; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class BaseDao<T> { public List<T> executeQuery(String sql, PageBean pageBean, Class clz) throws Exception { List<T> lst = new ArrayList<T>(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; if (pageBean != null && pageBean.isPagination()) { String countSQL = getcountSQL(sql); conn = DBAccess.getConnection(); pst = conn.prepareStatement(countSQL); rs = pst.executeQuery(); if (rs.next()) { pageBean.setTotal(rs.getObject("n").toString()); } String pageSQl = getpageSQl(sql, pageBean); conn = DBAccess.getConnection(); pst = conn.prepareStatement(pageSQl); rs = pst.executeQuery(); } else { conn = DBAccess.getConnection(); pst = conn.prepareStatement(sql); rs = pst.executeQuery(); } while (rs.next()) { T t = (T) clz.newInstance(); Field[] fields = clz.getDeclaredFields(); for (Field f : fields) { f.setAccessible(true); // System.out.println(f.getName()); // rs.getObject(f.getName()); f.set(t, rs.getObject(f.getName())); } lst.add(t); // Book b=new Book(); // b.setBid(rs.getInt("bid")); // b.setBname(rs.getString("bname")); // b.setPrice(rs.getFloat("price")); // list.add(b); } return lst; } private String getpageSQl(String sql, PageBean pageBean) { return sql +=" limit " + pageBean.getStartIndex() + "," + pageBean.getRows(); } private String getcountSQL(String sql) { return "select count(1) as n from ("+sql+") t"; } }
运行结果
数据库的资源我会放在我的资源，可以自行下载。
3通用分页查询
结合上面的代码和例子，只要修改bookdao即可
代码如下
1.Bookdao
package Dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.niyin.entity.Book; import com.niyin.util.BaseDao; import com.niyin.util.DBAccess; import com.niyin.util.PageBean; import com.niyin.util.StringUtils; public class BookDao2 extends BaseDao<Book> { // public List<Book> list(Book book,PageBean pageBean) throws Exception { // List<Book>list=new ArrayList<Book>(); // Connection conn = DBAccess.getConnection(); // String sql="select * from t_mvc_book where 1=1"; // String bname = book.getBname(); // if (StringUtils.isNotBlank(bname)) { // sql +="and bname like '%"+bname+"%'"; // } // PreparedStatement pst = conn.prepareStatement(sql); // ResultSet rs = pst.executeQuery(); // while(rs.next()) { // Book b=new Book(); // b.setBid(rs.getInt("bid")); // b.setBname(rs.getString("bname")); // b.setPrice(rs.getFloat("price")); // list.add(b); // } // return list; // // } public List<Book> list(Book book,PageBean pageBean) throws Exception { List<Book>list=new ArrayList<Book>(); Connection conn = DBAccess.getConnection(); String sql="select * from t_mvc_book where 1=1 "; String bname = book.getBname(); if (StringUtils.isNotBlank(bname)) { sql +=" and bname like '%"+bname+"%'"; } return super.executeQuery(sql, pageBean, Book.class); } public static void main(String[] args) throws Exception { BookDao2 bookdao=new BookDao2(); Book book=new Book(); PageBean pageBean=new PageBean(); book.setBname("圣墟"); List<Book> list = bookdao.list(book, pageBean); for (Book book2 : list) { System.out.println(book2); } } }
结果如下
通过分页工具类，只取了前面10条数据。
