通用分页1
1.连接数据库的config.properties配置文件（这里使用的是mysq数据库）
#mysql5 driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/my-data?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT user=root pwd=123
my-data:连接的数据库名
user:用户名
pwd:密码
2.新建工具类DBAccess.java连接数据库（别忘了到入jar包）
package com.zking.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.jdbc.Driver".equals(driver);
}
public static void main(String[] args) {
Connection conn = DBAccess.getConnection();
DBAccess.close(conn);
System.out.println("isOracle：" + isOracle());
System.out.println("isSQLServer：" + isSQLServer());
System.out.println("isMysql：" + isMysql());
System.out.println("数据库连接(关闭)成功");
}
}
在控制台输出的结果为:
Thu Jun 20 14:15:08 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. isOracle：false isSQLServer：false isMysql：true 数据库连接(关闭)成功
就表示数据库已连上。
3.创建与表对应的实体类（Book.java）
package com.zking.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;
}
public Book(int bid, String bname, float price) {
super();
this.bid = bid;
this.bname = bname;
this.price = price;
}
public Book() {
super();
}
}
4.编写过滤器处理中文乱码
package com.zking.util;
import java.io.IOException;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* 中文乱码处理
*
*/
public class EncodingFiter implements Filter {
private String encoding = "UTF-8";// 默认字符集
public EncodingFiter() {
super();
}
public void destroy() {
}
public void doFilter(ServletRequest request, ServletResponse response,
FilterChain chain) throws IOException, ServletException {
HttpServletRequest req = (HttpServletRequest) request;
HttpServletResponse res = (HttpServletResponse) response;
// 中文处理必须放到 chain.doFilter(request, response)方法前面
res.setContentType("text/html;charset=" + this.encoding);
if (req.getMethod().equalsIgnoreCase("post")) {
req.setCharacterEncoding(this.encoding);
} else {
Map map = req.getParameterMap();// 保存所有参数名=参数值(数组)的Map集合
Set set = map.keySet();// 取出所有参数名
Iterator it = set.iterator();
while (it.hasNext()) {
String name = (String) it.next();
String[] values = (String[]) map.get(name);// 取出参数值[注：参数值为一个数组]
for (int i = 0; i < values.length; i++) {
values[i] = new String(values[i].getBytes("ISO-8859-1"),
this.encoding);
}
}
}
chain.doFilter(request, response);
}
public void init(FilterConfig filterConfig) throws ServletException {
String s = filterConfig.getInitParameter("encoding");// 读取web.xml文件中配置的字符集
if (null != s && !s.trim().equals("")) {
this.encoding = s.trim();
}
}
}
5.创建分页实体类PageBean.java
分页三要素
page 页码 视图层传递过来
rows 页大小 视图层传递过来
total 总记录数 后台查出来
package com.zking.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 + "]";
}
}
6.编写通用的dao层进行查询分页
1、将原有的查询方法进行反射优化，转变成一个可 以被所有实体类dao层所继承的通用查询方法
2、考虑该方法可以进行分页
2.1 算总记录数
2.2 查询出当前页的结果集
package com.zking.util;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* T代表你要对哪个实体类对应表进行查询
*
* @author Administrator
*
* @param <T>
*/
public class BaseDao<T> {
/**
*
* @param sql 查询不同的实体类，那么对应的sql不同，所以需要传递
* @param clz 生产出不同的实体类对应的实例，然后装进list容器中返回
* @param pageBean 决定是否分页
* @return
* @throws Exception
*/
public List<T> executeQuery(String sql,Class clz,PageBean pageBean) throws Exception{
Connection con=DBAccess.getConnection();
PreparedStatement ps=null;
ResultSet rs=null;
if(pageBean!=null&&pageBean.isPagination()) {
//需要分页(算符合条件的总记录数)
String countSql=getCountSql(sql);
ps=con.prepareStatement(countSql);
rs=ps.executeQuery();
if(rs.next()) {
pageBean.setTotal(rs.getLong(1)+"");
}
//查询出符合条件的结果集
String pageSql=getpageSql(sql,pageBean);
ps=con.prepareStatement(pageSql);
rs=ps.executeQuery();
}else {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
}
List<T> list=new ArrayList<T>();
T t;
while(rs.next()) {
/**
* 1.实例化一个book对象（该对象是空的）
* 2.取book的所有的属性，然后给其赋值
* 2.1获取所有属性对象
* 2.2给属性对象赋值
* 3.赋完值的book对象装进list容器中
*/
t=(T)clz.newInstance();
Field[] fields=clz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
field.set(t, rs.getObject(field.getName()));
}
list.add(t);
}
DBAccess.close(con, ps, rs);
return list;
}
/**
* 利用原生sql拼接出符合条件的结果集的sql
* @param sql
* @param pageBean
* @return
*/
private String getpageSql(String sql, PageBean pageBean) {
// TODO Auto-generated method stub
return sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows();
}
/**
* 获取符合条件的总记录数的sql
* @param sql
* @return
*/
private String getCountSql(String sql) {
return "SELECT COUNT(1)FROM ("+sql+")t";
}
}
7.编写BookDao并继承BaseDao进行分页查询测试
package com.zking.dao;
import java.util.List;
import com.zking.entity.Book;
import com.zking.util.BaseDao;
import com.zking.util.PageBean;
import com.zking.util.StringUtils;
public class BookDao extends BaseDao< Book>{
public List<Book> list(Book book,PageBean pageBean) throws Exception{
String sql="select * from t_mvc_book where true";
String bname=book.getBname();
if(StringUtils.isNotBlank(bname)) {
sql+=" and bname like '%"+bname+"%'";
}
return super.executeQuery(sql, Book.class, pageBean);
}
public static void main(String[] args) {
BookDao bookDao=new BookDao();
Book book=new Book();
PageBean pageBean=new PageBean();
//book.setBname("圣墟");
try {
List<Book> list=bookDao.list(book, pageBean);
for (Book b : list) {
System.out.println(b);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
测试结果如下
Thu Jun 20 14:49:32 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. Book [bid=11, bname=圣墟第11章, price=11.0] Book [bid=12, bname=圣墟第12章, price=12.0] Book [bid=13, bname=斗破第13章, price=13.0] Book [bid=16, bname=圣墟第16章, price=16.0] Book [bid=18, bname=圣墟第18章, price=18.0] Book [bid=19, bname=圣墟第19章, price=19.0] Book [bid=20, bname=圣墟第20章, price=20.0] Book [bid=21, bname=圣墟第21章, price=21.0] Book [bid=22, bname=圣墟第22章, price=22.0] Book [bid=23, bname=仙逆第23章, price=23.0]
因为分页实体类默认显示每页是10条数据，所以结果为10条数据。
