通用分页
一,核心思想:将上一次查询的请求再重新发一次,只不过页码改变了
二,分页三要素:(PageBean)
page 页码
rows 每页多少条数据
total 总数据 数据库查询出来
三,后台
书本实体
dao方法
四,视图层(jsp页面)
1.导入jia包
建包建类
1.首先要连接数据库(DBUtil)
jdbc.properties
driver.name = com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/t274?useUnicode=true&characterEncoding=utf-8&useSSL=false
db.user=root
db.password=1234
DBUtil类 (解析出jdbc.properties中的字段然后连接数据库)
package com.zking.mymvc.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author Administrator
* @create 2020-05-0811:43
*/
public final class DBUtil {
private static String DRIVER_NAME;
private static String DB_URL;
private static String DB_USER;
private static String DB_PASSWORD;
private DBUtil() {
}
static {
try {
InputStream in = DBUtil.class.getResourceAsStream("/jdbc.properties");
Properties properties = new Properties();
properties.load(in);
DRIVER_NAME = properties.getProperty("driver.name");
DB_URL = properties.getProperty("db.url");
DB_USER = properties.getProperty("db.user");
DB_PASSWORD = properties.getProperty("db.password");
Class.forName(DRIVER_NAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConection() throws SQLException {
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
return connection;
}
public static void closeDB(ResultSet rs, Statement ps, Connection con) {
try {
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (ps != null && !ps.isClosed()) {
ps.close();
}
if(con != null && !con.isClosed()) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeDB(ResultSet rs, Statement ps) {
try {
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (ps != null && !ps.isClosed()) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
Connection conection = DBUtil.getConection();
DBUtil.closeDB(null, null, conection);
System.out.println("------- -------------");
}
}
2.中文乱码处理—EncodingFilter
package com.zking.mymvc.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.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* 中文乱码处理
*/
//@WebFilter("/*") //该过滤器过滤所有的请求
public class EncodingFilter implements Filter {
private String encoding = "UTF-8";// 默认字符集
public EncodingFilter() {
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);
res.setCharacterEncoding(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();
}
}
}
将EncodingFilter类配置到xml文件中实现中文转换
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>mymvc</display-name>
<filter>
<filter-name>encoding</filter-name>
<filter-class>com.zking.mymvc.util.EncodingFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>encoding</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
</web-app>
3.分页工具类—PageBean
package com.zking.mymvc.util;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import com.mysql.jdbc.StringUtils;
public class PageBean {
//private int PreviousPage;
/**
* 页码
*/
private int page = 1;
/**
* 每页显示的记录数
*/
private int rows = 10;
/**
* 总记录数
*/
private int total = 0;
/**
* 是否分页
*/
private boolean pagination = true;
/**
* 记录查询的url,以便于点击分页时再次使用
*
*/
private String url;
/**
* 存放请求参数,用于生成隐藏域中的元素
* 查询条件
*/
private Map<String,String[]> parameterMap;
/**
* 根据传入的Request初始化分页对象
* @param request
*/
public void setRequest(HttpServletRequest request) {
if(!StringUtils.isNullOrEmpty(request.getParameter("page"))) {
this.page = Integer.valueOf(request.getParameter("page"));
}
if(!StringUtils.isNullOrEmpty(request.getParameter("rows"))) {
this.rows = Integer.valueOf(request.getParameter("rows"));
}
if(!StringUtils.isNullOrEmpty(request.getParameter("pagination"))) {
this.pagination = Boolean.valueOf(request.getParameter("pagination"));
}
this.url = request.getRequestURI();
this.parameterMap = request.getParameterMap();
request.setAttribute("pageBean", this);
}
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 boolean isPagination() {
return pagination;
}
public void setPagination(boolean pagination) {
this.pagination = pagination;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public Map<String, String[]> getParameterMap() {
return parameterMap;
}
public void setParameterMap(Map<String, String[]> parameterMap) {
this.parameterMap = parameterMap;
}
//计算起始页码
public int getStartIndex() {
return (this.page - 1) * this.rows;
}
//获取总页数
public int getTotalPage() {
if (this.getTotal() % this.rows == 0) {
return this.getTotal() / this.rows;
} else {
return this.getTotal() / this.rows + 1;
}
}
//上一页
public int getPreviousPage() {
return this.page - 1 > 0 ? this.page - 1 : 1;
}
//下一页
public int getNextPage() {
return this.page + 1 > getTotalPage() ? getTotalPage() : this.page + 1;
}
}
4.写一个Student实体类
package com.zking.mymvc.model;
public class Student {
private Integer sid;
private String sname;
private Integer age;
private String remark;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", age=" + age + ", remark=" + remark + "]";
}
}
5.dao方法StudentDao02
package com.zking.mymvc.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import com.zking.mymvc.model.Student;
import com.zking.mymvc.util.DaoTempalte;
import com.zking.mymvc.util.DaoTempalte.Convert;
import com.zking.mymvc.util.PageBean;
public class StudentDao02 {
public List<Student> getStudents(String sname, PageBean pageBean) {
//sql语句(差异部分)
String sql = "select * from t_student t ";
List<Object> param =new ArrayList<Object>();
if(sname !=null && !"".equals(sname)) {
sql +="where t.sname like ?";//通过名称来执行模糊查询
param.add(sname+"%");
}
List<Student> student= DaoTempalte.query(sql, param.toArray(), pageBean, new StudentConvert());
return student;
}
class StudentConvert implements Convert<Student>{
@Override
public List<Student> convert(ResultSet rs) throws SQLException {
List<Student> list =new ArrayList<>();
//开始转换
while(rs.next()) {
Student stu = new Student();
stu.setSid(rs.getInt("sid"));
stu.setSname(rs.getString("sname"));
stu.setAge(rs.getInt("age"));
stu.setRemark(rs.getString("remark"));
list.add(stu);
}
return list;
}
}
@Test
public void testStudentDao02() {
//调用dao方法
StudentDao02 stu= new StudentDao02();
//
PageBean pageBean =new PageBean();
//每页两条数据
pageBean.setRows(10);
List<Student> students = stu.getStudents(null, pageBean);
students.forEach(t->System.out.println(t));
//查询出来的数据有多少条
System.out.println("查询出来的数据:"+pageBean.getTotal()+"个学生");
//页数1
System.out.println("分页数量:"+pageBean.getTotalPage()+"页");
}
}
DaoTemplate类
package com.zking.mymvc.util;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.zking.mymvc.model.Student;
/**
*是一个工具类一般不用继承
* @author Administrator
*
*/
public final class DaoTempalte {
//阻止实例化
private void DaoTemplate() {
}
//转换接口
public static interface Convert<T>{
List<T> convert(ResultSet rs) throws SQLException;
}
//泛型--参数化类型
/**
*
* @param sql sql语句
* @param args 查询参数
* @param pageBean 分页参数
* @param convert 转换器
* @return
*/
public static <T> List<T> query(String sql,
Object[] args,
PageBean pageBean,
Convert convert
){
//list集合用来存放数据(相同)
List<T> datas = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
//判断PageBean为null 参数isPagination不为false
//如果不用分页直接查询
if(pageBean ==null || !pageBean.isPagination()) {
try {
//----相同部分
con =(Connection) DBUtil.getConection();
ps=(PreparedStatement) con.prepareStatement(sql);
//设置参数
setparams(args, ps);
//开始执行
rs=ps.executeQuery();
//----1
//-----不一样
//只有业务功能的开发者才知道要转换成什么对象
//-------2
//获取到的数据库的结果给自己去转换
//回调业务类传入的转换器执行转换
datas =convert.convert(rs);
return datas;
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关闭连接
DBUtil.closeDB(rs, ps, con);
}
}else {
//如果需要分页
//------相同
//1.总记录数
String countsql="select COUNT(*) from ("+sql+") t";
try {
con =(Connection) DBUtil.getConection();
ps=(PreparedStatement) con.prepareStatement(countsql);
//获取总记录数需要和查询具体记录数参数要一致
//设置查询参数
setparams(args, ps);
//开始执行
rs=ps.executeQuery();
//为总记录数赋值
while(rs.next()) {
pageBean.setTotal(rs.getInt(1));//获取第一个数据
}
/*
* 如果统计的 getTotal(总记录数)为0,则表示没有符合条件的记录,直接返回一个空结果集即可。
*/
if(pageBean.getTotal() == 0) {
return datas;
}
//-----3
//---------相同
//查询当前页数据的sql
String pagingSql = sql + " limit " + pageBean.getStartIndex() + ", " + pageBean.getRows();
ps = (PreparedStatement) con.prepareStatement(pagingSql);
//设置查询参数
setparams(args, ps);
//开始执行
rs=ps.executeQuery();
//------4
//转换器
datas =convert.convert(rs);
return datas;
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.closeDB(rs, ps, con);
}
}
return null;
}
private static void setparams(Object[] args, PreparedStatement ps) throws SQLException {
if(args!=null) {
for(int i=1;i<args.length;i++) {
//如果i=0时记得加1,因为ps在设置参数时下标从一开始
ps.setObject(i, args[i]);
}
}
}
}
DbTemplate(用于操作数据库的工具类)
package com.zking.mymvc.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.commons.beanutils.BeanUtils;
import com.mysql.jdbc.NotUpdatable;
/**
* 用于操作数据库的工具类
* @author Administrator
*/
public final class DbTemplate {
private DbTemplate() {
}
//用于缓存数据库字段到实体属性名的映射关系: 列名 --> 属性名
private static Map<String, Map<String, String>> columnFieldMapCache = new ConcurrentHashMap<>();
//用于缓存实体属性名到数据库字段名的映射关系: 属性名 --> 列名
private static Map<String, Map<String, String>> fieldColumnMapCache = new ConcurrentHashMap<>();
/**
* 分页查询功能
* @param sql sql语句
* @param args 查询参数,对象数组
* @param pageBean 分页对象
* @param clazz 数据记录对应的实体对象类型
* @return
*/
public static <E> List<E> query(String sql,
Object[] args,
PageBean pageBean,
Class<E> clazz) {
List<E> datas = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
//如果需要分页,则统计总记录数
if(pageBean != null && pageBean.isPagination()) {
String sqlCount = "SELECT COUNT(*) FROM (" + sql + ") t";
try {
con = DBUtil.getConection();
ps = con.prepareStatement(sqlCount);
//设置查询参数
int i = 1;
for(Object arg: args) {
ps.setObject(i, arg);
i++;
}
rs = ps.executeQuery();
while(rs.next()) {
pageBean.setTotal(rs.getInt(1));
}
} catch (SQLException e) {
DBUtil.closeDB(rs, ps, con);
throw new RuntimeException("统计总记录数异常", e);
} finally {
DBUtil.closeDB(rs, ps);
}
if(pageBean.getTotal()== 0) {
return datas;
}
}
try {
String pagingSql = sql;
if(pageBean != null && pageBean.isPagination()) {
pagingSql = sql + " limit "
+ pageBean.getStartIndex() + "," + pageBean.getRows();
}
con = con == null ? DBUtil.getConection() : con;
ps = con.prepareStatement(pagingSql);
//设置查询参数
int i = 1;
for(Object arg: args) {
ps.setObject(i, arg);
i++;
}
rs = ps.executeQuery();
Map<String, String> columnFieldMap = getColumnFieldMap(clazz);
int columnNum = rs.getMetaData().getColumnCount();
while(rs.next()) {
E bean = clazz.newInstance();
for(int index = 1; index <= columnNum; index++) {
String cn = rs.getMetaData().getColumnName(index);
//如果实体类中没有定义与列名对应的属性,则直接放弃
if(!columnFieldMap.containsKey(cn) || rs.getObject(index) == null) continue;
//通过反射机制进行赋值
BeanUtils.setProperty(bean, columnFieldMap.get(cn), rs.getObject(index));
}
datas.add(bean);
}
} catch (SQLException e) {
throw new RuntimeException("查询分页数据异常", e);
} catch (InstantiationException | IllegalAccessException e) {
throw new RuntimeException("查询分页数据异常", e);
} catch (InvocationTargetException e) {
throw new RuntimeException("查询分页数据异常", e);
} finally {
DBUtil.closeDB(rs, ps, con);
}
return datas;
}
/**
* 获取数据库字段和实体属性之间的映射
* @param clazz 类型
* @return Map<String, String>
*/
private static <E> Map<String, String> getColumnFieldMap(Class<E> clazz) {
if(columnFieldMapCache.containsKey(clazz.getName())) {
return columnFieldMapCache.get(clazz.getName());
}
Map<String, String> map = new HashMap<>();
Field[] fields = clazz.getDeclaredFields();
for(Field f: fields) {
//如果具有Ignore注解则表示忽略
if(f.getAnnotation(Ignore.class) != null) {
continue;
}
if(f.getAnnotation(Column.class) == null) {
map.put(f.getName(), f.getName());
}else {
map.put(f.getAnnotation(Column.class).value(), f.getName());
}
}
columnFieldMapCache.put(clazz.getName(), map);
return map;
}
/**
* 执行查询,不分页
* @param sql sql语句
* @param args 查询参数,对象数组
* @param clazz 数据记录对应的实体对象类型
* @return list
*/
public static <E> List<E> query(String sql,
Object[] args,
Class<E> clazz) {
return query(sql, args, null, clazz);
}
/**
* 执行查询,不分页,没有条件
* @param sql 查询语句
* @param clazz 存放数据的实体类型
* @return list
*/
public static <E> List<E> query(String sql,
Class<E> clazz) {
return query(sql, new Object[]{}, null, clazz);
}
/**
* 执行查询,分页,没有条件
* @param sql 查询语句
* @param pageBean 分页条件
* @param clazz 存放数据的实体类型
* @return list
*/
public static <E> List<E> query(String sql,
PageBean pageBean,
Class<E> clazz) {
return query(sql, new Object[]{}, pageBean, clazz);
}
/**
* 保存数据实体,如果传入连接,则使用传入的数据库连接。如果为空创建一个连接,
* 如果调用者自己传入连接对象,则需要自行处理连接的关闭,需要传入调用者自行
* 传入连接的情况主要出现的需要事务控制的时候。
* @param connection 数据库连接
* @param entity
* @return
*/
public static <E> int save(Connection connection, E entity) {
Table table = entity.getClass().getAnnotation(Table.class);
if(table == null) {
throw new SaveEntityException("需要在实体类上需要使用@Table来标记表名");
}
String tableName = entity.getClass().getAnnotation(Table.class).value();
String sql = buildInsertSqlByEntity(entity);
Connection con = null;
PreparedStatement ps = null;
try {
con = (connection == null || connection.isClosed()) ? DBUtil.getConection() : connection;
ps = con.prepareStatement(sql);
int i = 1;
for(Field f: entity.getClass().getDeclaredFields()) {
f.setAccessible(true);
if(f.getAnnotation(AutoIncrement.class) != null
|| f.getAnnotation(Ignore.class) != null) {
continue;
}
if(f.getAnnotation(Key.class) != null) {
if(f.get(entity) == null) {
throw new SaveEntityException("保存"+tableName+"记录时,"+f.getName() +"为主键属性不允许为空");
}
}
if (f.getAnnotation(NotNull.class) != null) {
if(f.get(entity) == null) {
throw new SaveEntityException("保存"+tableName+"记录时,"+f.getName() +"属性不允许为空");
}
}
ps.setObject(i, f.get(entity));
i++;
}
return ps.executeUpdate();
} catch (SQLException e) {
throw new SaveEntityException("保存"+tableName+"记录时报异常",e );
} catch (IllegalArgumentException | IllegalAccessException e) {
throw new SaveEntityException("保存"+tableName+"记录时报异常",e );
} finally {
//外部传入的数据库连接,由外部程序自行关闭
if(connection != null) {
DBUtil.closeDB(null, ps);
} else {
DBUtil.closeDB(null, ps, con);
}
}
}
/**
* 保存实体中的数据到对应的表中去,实体对应的表可以通过在实体类上使用
* 注解@Table来进行标记,对应自增长的字段可以通过@AutoIncrement
* 来进行注解
* @param entity 实体类
* @return int 影响行数
*/
public static <E> int save(E entity) {
return save(null, entity);
}
/**
* 通过实体类构造insert语句
* @param entity 需要持久化的实体bean
* @return string
*/
private static <E> String buildInsertSqlByEntity(E entity) {
String tableName = entity.getClass().getAnnotation(Table.class).value();
StringBuilder sql = new StringBuilder("insert into "+tableName + "(");
Map<String, String> fieldColumnMap = getFieldColumnMap(entity);
Field[] fields = entity.getClass().getDeclaredFields();
for(int i = 0; i < fields.length; i++) {
//如果字段表明是自增长的或者是忽略的属性,则不需要构造到insert语句中
if(fields[i].getAnnotation(AutoIncrement.class) != null
|| fields[i].getAnnotation(Ignore.class) != null) {
if(fields.length == (i+1)) {
sql.deleteCharAt(sql.length()-1);
sql.append(")");
}
continue;
}
//除最后一列外,各列中间用","分割
if(fields.length == (i+1)) {
sql.append(fieldColumnMap.get(fields[i].getName())+")");
} else {
sql.append(fieldColumnMap.get(fields[i].getName())+",");
}
}
sql.append("VALUES (");
for(int i = 0; i < fields.length; i++) {
//排除自增长的字段及忽略的属性
if(fields[i].getAnnotation(AutoIncrement.class) != null
|| fields[i].getAnnotation(Ignore.class) != null) {
if(fields.length == (i+1)) {
sql.deleteCharAt(sql.length()-1);
sql.append(")");
}
continue;
}
//除最后一列外,各列中间用","分割
if(fields.length == (i+1)) {
sql.append("?)");
} else {
sql.append("?,");
}
}
System.out.println("生成Insert语句如下: " + sql.toString());
return sql.toString();
}
/**
* 构建 属性 -> 数据库字段名 映射
* @param entity 实体
* @return Map<String, String>
*/
private static <E> Map<String, String> getFieldColumnMap(E entity) {
if(fieldColumnMapCache.containsKey(entity.getClass().getName())) {
return fieldColumnMapCache.get(entity.getClass().getName());
}
Map<String, String> fieldColumnMap = new HashMap<>();
Field[] fields = entity.getClass().getDeclaredFields();
for(Field f: fields) {
if(f.getAnnotation(Ignore.class) != null) continue;
if(f.getAnnotation(Column.class) == null) {
fieldColumnMap.put(f.getName(), f.getName());
}else {
fieldColumnMap.put(f.getName(), f.getAnnotation(Column.class).value());
}
}
fieldColumnMapCache.put(entity.getClass().getName(), fieldColumnMap);
return fieldColumnMap;
}
/**
* 新增,更新,或删除记录
* @param sql 更新sql语句
* @param args 参数数组
* @return int 影响的行数
*/
public static <E> int update(String sql, Object[] args) {
Connection con = null;
PreparedStatement ps = null;
try {
con = DBUtil.getConection();
ps = con.prepareStatement(sql);
int i = 1;
for(Object arg: args) {
ps.setObject(i, arg);
i++;
}
return ps.executeUpdate();
} catch (SQLException e) {
throw new UpdateRecordException("执行:"+ sql, e);
} finally {
DBUtil.closeDB(null, ps, con);
}
}
/**
* 更新,适合针对摸个实体类进行整体更新的情况
* @param entity 需要跟新的实体对象
* @return 影响的行数,如果成功则返回1
*/
public static <E> int update(E entity) {
Table table = entity.getClass().getAnnotation(Table.class);
if(table == null) {
throw new SaveEntityException("需要在实体类上需要使用@Table来标记表名");
}
String tableName = entity.getClass().getAnnotation(Table.class).value();
Field[] fields = entity.getClass().getDeclaredFields();
Map<String, String> fcMap = getFieldColumnMap(entity);
List<Object> args = new ArrayList<>();
StringBuilder setStr = new StringBuilder();
int i = 0;
try {
for(Field f: fields) {
if(f.getAnnotation(Ignore.class) != null) continue;
if(f.getAnnotation(Key.class) == null) {
if (i == 0) {
setStr.append(" set "+ fcMap.get(f.getName()) + "=?");
f.setAccessible(true);
args.add(f.get(entity));
} else {
setStr.append(","+fcMap.get(f.getName()) + "=?");
f.setAccessible(true);
args.add(f.get(entity));
}
i++;
}
}
} catch (IllegalArgumentException | IllegalAccessException e) {
throw new RuntimeException("在生成update语句时发生异常",e);
}
if(setStr.toString().length() == 0) {
throw new RuntimeException("在使用update(E entity)方法执行更新没有指定任何需要更新的字段...");
}
StringBuilder whereStr = new StringBuilder();
int j = 0;
try {
for(Field f: fields) {
if(f.getAnnotation(Ignore.class) != null) continue;
if(f.getAnnotation(Key.class) != null) {
if(j == 0) {
whereStr.append(" where "+fcMap.get(f.getName()) + "=?");
f.setAccessible(true);
args.add(f.get(entity));
} else {
whereStr.append(" and " + fcMap.get(f.getName()) + "=?");
f.setAccessible(true);
args.add(f.get(entity));
}
j++;
}
}
} catch (IllegalArgumentException | IllegalAccessException e) {
throw new RuntimeException("在生成update语句时发生异常",e);
}
if(whereStr.toString().length() == 0 ) {
throw new RuntimeException("在使用update(E entity)方法执行更新时需要指定@key");
}
Object[] argArr = args.toArray();
String updateSql = "update " + tableName + setStr + whereStr;
System.out.println("生成的update语句:" + updateSql);
return update(updateSql, argArr);
}
public static void main(String[] args) {
/*Student student = new Student();
student.setAge(39);
student.setSname("欧阳晓峰");
student.setRemark("测试save");
DbTemplate.save(student);*/
/*String sql = "update t_student set sname=? where sid=?";
DbTemplate.update(sql, new Object[] {"欧阳乔峰456", 170});
String del = "delete from t_student where sid=?";
DbTemplate.update(del, new Object[] {169});*/
/*String sql = "select * from test";
List<TestOrm> list = DbTemplate.query(sql, TestOrm.class);
for(TestOrm orm: list) {
System.out.println(orm);
}
*/
TestOrm orm = new TestOrm();
orm.setTaType("类型3");
orm.settName("许志强");
orm.setRemark("测试保存");
DbTemplate.save(orm);
DbTemplate.update(orm);
String sql = "select * from test where t_id = ?";
List<TestOrm> list= DbTemplate.query(sql, new Object[] {1},TestOrm.class);
TestOrm testOrm = list.get(0);
testOrm.setRemark("测试update(E entity)方法");
DbTemplate.update(testOrm);
}
}
前端jsp页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- 目前没有定义自定义的标签库 -->
<%-- <%@taglib prefix="z" uri="/zking" %> --%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>学生信息</h1>
<!-- 查询条件 -->
<form action="<%=request.getContextPath()%>/students" method="post">
<input type="text" name="sname">
<input type="submit" value="查询">
</form>
<table border="1" style="width: 98%;">
<tr>
<td>学号</td>
<td>姓名</td>
<td>年龄</td>
<td>备注</td>
</tr>
<c:forEach items="${students}" var="student">
<tr>
<td>${student.sid}</td>
<td>${student.sname}</td>
<td>${student.age}</td>
<td>${student.remark}</td>
</tr>
</c:forEach>
</table>
<!-- 分页工具条 -->
<div style="text-align: right; width:98%;">
第${pageBean.page}页
共${pageBean.total}条记录
<a href="javascript: goPage(1)">首页</a>
<a href="javascript: goPage(${pageBean.previousPage})">上页</a>
<a href="javascript: goPage(${pageBean.nextPage})">下页</a>
<a href="javascript: goPage(${pageBean.totalPage})">尾页</a>
第<input type="text" id="specifiedPageNum" size="2" onkeypress="goSpecifiedPage(event);"/>
<a href="javascript: goPage(document.getElementById('specifiedPageNum').value)">GO</a>
</div>
<!-- 隐藏表单,用来翻页时保存查询参数 -->
<form action="${pageBean.url}" id="pagingForm" method="post">
<input type="hidden" name="page" value="${pageBean.page}"/>
<!-- 先只考虑本功能的查询参数,没有考虑公用性(不同功能的参数不同) -->
<input type="hidden" name="sname" value="<%=request.getParameter("sname")%>"/>
</form>
<script>
function goPage(page) {
//获取隐藏的表单
var form = document.getElementById("pagingForm");
form.page.value = page;
form.submit();
}
function goSpecifiedPage(event) {
if(event.keyCode == 13) {
let pageNum = document.getElementById("specifiedPageNum").value;
var form = document.getElementById("pagingForm");
form.page.value = pageNum;
form.submit();
}
}
function name() {
}
</script>
</body>
</html>