先创建实体类,封装数据库中的字段信息
以下按照三层开发创建的目录结构,仅测试分页查询,所以随便写了一些类
GetSql类通过传递对象,生成增删改的SQL语句,CommodityServiceImpl中只写了一个分页查询需要的方法
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.pojo; public class Commodity { private int c_id; private int classify_id; private String c_name; private double price; private int sales; private int inventory; public int getC_id() { return c_id; } public void setC_id(int c_id) { this.c_id = c_id; } public int getClassify_id() { return classify_id; } public void setClassify_id(int classify_id) { this.classify_id = classify_id; } public String getC_name() { return c_name; } public void setC_name(String c_name) { this.c_name = c_name; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public int getSales() { return sales; } public void setSales(int sales) { this.sales = sales; } public int getInventory() { return inventory; } public void setInventory(int inventory) { this.inventory = inventory; } @Override public String toString() { return "Commodity{" + "c_id=" + c_id + ", classify_id=" + classify_id + ", c_name='" + c_name + '\'' + ", price=" + price + ", sales=" + sales + ", inventory=" + inventory + '}'; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.util; import com.pojo.Commodity; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; public class GetSql { private Object obj;// 获取某个类的信息 private Class cla;//反射获取传过来的类的信息 private String tableName;// 数据库表名 public GetSql(Object obj){ this.obj = obj; cla = obj.getClass(); tableName= cla.getName().substring(cla.getName().lastIndexOf(".")+1); } public static void main(String[] args) { GetSql gs = new GetSql(new Commodity()); Commodity com = new Commodity(); com.setC_name("sadfsaf"); System.out.println(gs.getAlter(com));; } // 通过字段名获取并拼接对象对应的属性值 private String getValues() { String str = ""; String[] fields = getFields().split(","); try { for (int i = 0; i < fields.length; i++) { // a: 将属性名的首字母替换为大写,再加上get即为get方法名 String a = "get" + fields[i].replaceFirst(fields[i].substring(0, 1), fields[i].substring(0, 1).toUpperCase()); Method method = cla.getMethod(a); if (fields.length == 1) { if (method.toString().contains("String")) { str += "'" + method.invoke(obj, null) + "'";// 通过调用对象的方法获取返回值 } else { str += method.invoke(obj, null); } } else if (i == fields.length - 1) { if (method.toString().contains("String")) { str += "'" + method.invoke(obj, null) + "'";// 通过调用对象的方法获取返回值 } else { str += method.invoke(obj, null); } } else { if (method.toString().contains("String")) { str += "'" + method.invoke(obj, null) + "'" + ",";// 通过调用对象的方法获取返回值 } else { str += method.invoke(obj, null) + ","; } } } } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return str; } // 获取并拼接对象的所有字段名 private String getFields() { String str = ""; // 获取所有的属性名 Field[] fields = cla.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { String name = fields[i].getName(); if (fields.length == 1) { str += name; } else if (i == fields.length - 1) { str += name.substring(name.lastIndexOf(".") + 1); } else { str += name.substring(name.lastIndexOf(".") + 1) + ","; } } return str; } // 将字段名与值进行拼接 private String getKeyValues (){ String[] fieldsName = getFields().split(","); String[] values = getValues().split(","); String str = ""; for (int i = 0; i < fieldsName.length; i++) { if(fieldsName.length==1){ str += fieldsName[i]+"="+values[i]; }else if(i==fieldsName.length-1){ str += fieldsName[i]+"="+values[i]; }else { str += fieldsName[i]+"="+values[i]+" and "; } } return str; } // 获取添加的SQL语句 public String getAdd(){ return "insert into " +tableName+"("+getFields()+")"+" values("+getValues()+")"; } // 获取删除的SQL语句 public String getDelete(){ return "delete from "+ tableName+" where "+getKeyValues(); } // 获取修改的SQL语句,需传一个想要修改 public String getAlter(Object o){ String str = new GetSql(o).getKeyValues().replace("and",","); return "update "+tableName+" set "+ str +" where "+getKeyValues(); } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.dao; import com.pojo.Commodity; import com.util.GetSql; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class BaseDao { Connection connection; PreparedStatement pstm; ResultSet rs; public Connection getConnection() { try { // 初始化上下文 Context context = new InitialContext(); //获取数据源 DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/shop"); // 获取连接, connection = dataSource.getConnection(); } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return connection; } // 增 public boolean add(Object obj) { getConnection(); GetSql gs = new GetSql(obj); try { pstm = connection.prepareStatement(gs.getAdd()); if (pstm.executeUpdate() > 0) { return true; } } catch (SQLException e) { return false; }finally { closeAll(); } return false; } // 删 public boolean delete(Object obj) { getConnection(); try { pstm = connection.prepareStatement(new GetSql(obj).getDelete()); if (pstm.executeUpdate() > 0) { return true; } } catch (SQLException e) { return false; } finally { closeAll(); } return false; } // 改 public boolean alter(Object obj, Object obj2) { getConnection(); try { pstm = connection.prepareStatement(new GetSql(obj).getAlter(obj2)); if (pstm.executeUpdate() > 0) { return true; } } catch (SQLException e) { return false; } finally { closeAll(); } return false; } // 查询数据总条数 public int nums() { getConnection(); String sql = "select count(1) from commodity"; int num = 0; PreparedStatement pstm = null; try { pstm = connection.prepareStatement(sql); ResultSet rs = pstm.executeQuery(); if (rs.next()) { num = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(); } return num; } // 查 public List<Commodity> display(String sql) { getConnection(); // String sql = "select * from commodity"; Commodity com = null; List<Commodity> list = new ArrayList<Commodity>(); try { PreparedStatement pstm = connection.prepareStatement(sql); ResultSet rs = pstm.executeQuery(); while (rs.next()) { com = new Commodity(); com.setC_id(rs.getInt(1)); com.setClassify_id(rs.getInt(2)); com.setC_name(rs.getString(3)); com.setPrice(rs.getDouble(4)); com.setSales(rs.getInt(5)); com.setInventory(rs.getInt(6)); list.add(com); } } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(); } return list; } // 释放资源 public void closeAll() { try { if (connection != null) { connection.close(); } if (pstm!=null){ pstm.close(); } if(rs!=null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.service.impl; import com.dao.BaseDao; import com.pojo.Commodity; import com.service.CommodityService; import java.util.List; public class CommodityServiceImpl implements CommodityService { BaseDao bd = new BaseDao(); // 分页获取商品信息列表 public List<Commodity> getPageList(int pageNo, int pageSize) { String sql = "select * from Commodity limit "+ (pageNo-1)*pageSize+ ","+pageSize; List<Commodity> list = bd.display(sql); return list; } // 查询商品信息 public List<Commodity> displayAll() { return null; } // 增加商品信息 public boolean add(Commodity com) { return false; } // 根据商品id查询商品信息 public Commodity getById() { return null; } // 获取商品总数量 public int getTotalCount() { return 0; } }
jsp页面
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<%@ page import="com.util.PageSupport" %> <%@ page import="com.dao.BaseDao" %> <%@ page import="com.pojo.Commodity" %> <%@ page import="com.service.impl.CommodityServiceImpl" %> <%@ page import="java.util.List" %><%-- Created by IntelliJ IDEA. User: zengt Date: 2019-09-28 Time: 14:02 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> <% String currentPage = request.getParameter("pageIndex"); if(currentPage==null){ // 首次进入 currentPage = "1"; } PageSupport ps = new PageSupport(); BaseDao bd = new BaseDao(); int pageIndex = Integer.parseInt(currentPage);//当前页码 out.println(pageIndex); // 获取新闻总数量 int totalCount = bd.nums(); //每页显示几条新闻,页面容量 int pageSize = 10; // 获取总页数 ps.setCurrentRageNo(pageIndex); ps.setPageSize(pageSize); ps.setTotalCount(totalCount); int totalPage = ps.getTotalPageCount(); List<Commodity> newsList = new CommodityServiceImpl().getPageList(ps.getCurrentRageNo(),pageSize); int i= 0; for(Commodity news:newsList) { i++; %> <h5> <%=news.getC_id() %> <%=news.getC_name() %> <%=news.getPrice() %> </h5> <%} %> <a href="index.jsp?pageIndex=<%=pageIndex-1%>">上一页</a> <a href="index.jsp?pageIndex=<%=pageIndex+1%>">下一页</a> </body> </html>
仅测试,所以前台不做,简单看看页面效果
点击下一页,可正确显示数据信息
分页查询完成,仅测试所有很多问题未完善