工具类,用来整合各种资源
package com.softeem.dbutils;
import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import java.util.Properties; import org.apache.commons.dbcp2.BasicDataSource; /** * DBCP数据库连接工具类 * 依赖 * 1.mysql驱动 * 2.dbcp相关插件 * @author * */ public class DBUtils { //连接数据库基本属性 private static String driverClass; private static String url; private static String username; private static String password; //连接池属性 private static int initSize; private static int maxSize; private static int maxIdle; private static long maxWait; //数据源 private static BasicDataSource bds; //初始化数据源配置 static{ init(); } public static void init(){ try { //创建数据源对象 bds = new BasicDataSource(); //加载属性文件，获取属性信息 Properties props = new Properties(); props.load(DBUtils.class.getResourceAsStream("jdbc.properties")); driverClass = props.getProperty("driver"); url = props.getProperty("url"); username = props.getProperty("user"); password = props.getProperty("password"); initSize = Integer.parseInt(props.getProperty("initSize")); maxSize = Integer.parseInt(props.getProperty("maxSize")); maxIdle = Integer.parseInt(props.getProperty("maxIdle")); maxWait = Long.parseLong(props.getProperty("maxWait")); //设置驱动类路径 bds.setDriverClassName(driverClass); //设置url bds.setUrl(url); //设置用户名 bds.setUsername(username); //设置密码 bds.setPassword(password); //设置初始连接数 bds.setInitialSize(initSize); //设置最大连接 bds.setMaxTotal(maxSize); //设置最大闲置连接数 bds.setMaxIdle(maxIdle); //等待获取连接的最大时间(MS) bds.setMaxWaitMillis(maxWait); } catch (IOException e) { e.printStackTrace(); } } //获取连接 public static Connection getConn(){ try { if(bds == null || bds.isClosed()){ init(); } return bds.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null; } //封装资源回收的方法 public static void close(ResultSet rs,Statement stat,Connection conn){ try { if(rs != null) rs.close(); if(stat != null) stat.close(); if(conn != null)conn.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 通用增删改 * @param conn * @param sql * @param objs * @return */ public static boolean execUpdate(Connection conn,String sql,Object ...objs){ try { PreparedStatement ps = conn.prepareStatement(sql); for (int i = 0; i < objs.length; i++) { ps.setObject(i+1, objs[i]); } int i = ps.executeUpdate(); return i>0 ? true:false; } catch (SQLException e) { e.printStackTrace(); } return false; } /** * C++钩子函数 回调函数 * 集合查询 * @param sql * @param call * @param params * @return */ public static <T> List<T> queryList(String sql,CallBack<T> call,Object...params){ Connection conn = getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); for(int i = 0;i<params.length;i++) { ps.setObject(i+1, params[i]); } ResultSet rs = ps.executeQuery(); return call.getDatas(rs); } catch (SQLException e) { e.printStackTrace(); } return null; } /** * 查询一条记录 * @param sql * @param call * @param params * @return */ public static <T> T queryOne(String sql,CallBack<T> call,Object...params) { Connection conn = getConn(); PreparedStatement ps = null; try { ps = conn.prepareStatement(sql); for(int i = 0;i<params.length;i++) { ps.setObject(i+1, params[i]); } ResultSet rs = ps.executeQuery(); return call.getData(rs); } catch (SQLException e) { e.printStackTrace(); } return null; } //查询返回接口 jdk1.8支持 // public interface CallBack<T>{ // default List<T> getDatas(ResultSet rs){ // return null; // } // default T getData(ResultSet rs){ // return null; // } // } //jdk1.8以下使用抽象类 public static abstract class CallBack<T>{ public List<T> getDatas(ResultSet rs){ return null; } public T getData(ResultSet rs){ return null; } }}
properties文件
####mysql connection info#### driver = com.mysql.jdbc.Driver url = jdbc:mysql://127.0.0.1:3306/mydb user = root password = 123456 ####MSSQLServer connection info#### #driver = com.microsoft.sqlserver.jdbc.SQLServerDriver #url = jdbc:sqlserver://127.0.0.1:1433;databaseName=test #user = sa #password = 123456 ####Oracle connection info#### #driver = com.oracle.driver.OracleDriver #url = jdbc:oracle:thin:@127.0.0.1:1521:orcl #user = scott #password = 123456 ##pool config### initSize = 10 maxSize = 200 maxIdle = 30 maxWait = 10000
DTO文件(GOODS)
package com.softeem.goodsManager; import java.math.BigDecimal; import java.util.Date; public class Goods { private int id; //商品id private String goodsname;//商品名字 private BigDecimal price;//商品价格 private double offset;//商品折扣 private Date time;//上架时间 private int counts;//商品数量 private int cid;//商品所属类别 public Goods() { } public Goods(int id, String goodsname, BigDecimal price, double offset, Date time, int counts, int cid) { super(); this.id = id; this.goodsname = goodsname; this.price = price; this.offset = offset; this.time = time; this.counts = counts; this.cid = cid; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getGoodsname() { return goodsname; } public void setGoodsname(String goodsname) { this.goodsname = goodsname; } public BigDecimal getPrice() { return price; } public void setPrice(BigDecimal price) { this.price = price; } public double getOffset() { return offset; } public void setOffset(double offset) { this.offset = offset; } public Date getTime() { return time; } public void setTime(Date time) { this.time = time; } public int getCounts() { return counts; } public void setCounts(int counts) { this.counts = counts; } public int getCid() { return cid; } public void setCid(int cid) { this.cid = cid; } @Override public String toString() { return "Goods [id=" + id + ", goodsname=" + goodsname + ", price=" + price + ", offset=" + offset + ", time=" + time + ", counts=" + counts + ", cid=" + cid + "]"; } }
DTO文件(types)
package com.softeem.goodsManager; public class Types { private int id; private String cname; public Types() { // TODO Auto-generated constructor stub } public Types(int id, String cname) { super(); this.id = id; this.cname = cname; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } @Override public String toString() { return "Types [id=" + id + ", cname=" + cname + "]"; } }
用来读取excel文件的类(GOODS类)
package com.softeem.goodsManager; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; /** * 利用JXL实现对于excel-2000/2003版本的文件进行读写操作 * @author */ public class GoodsExcel { public static List<Goods> readGoods(File file){ List<Goods> list = new ArrayList<>(); Goods goods = null; Workbook workbook = null; try { //创建一个工作簿 workbook = Workbook.getWorkbook(file); //获取指定索引的表单 Sheet sheet = workbook.getSheet(0); //获取总行数 int rows = sheet.getRows(); for (int i = 1; i < rows; i++) { goods = new Goods(); String s1 = sheet.getCell(0, i).getContents();//编号 String s2 = sheet.getCell(1, i).getContents();//商品名 String s3 = sheet.getCell(2, i).getContents();//单价 String s4 = sheet.getCell(3, i).getContents();//折扣 String s5 = sheet.getCell(4, i).getContents();//时间 String s6 = sheet.getCell(5, i).getContents();//库存 String s7 = sheet.getCell(6, i).getContents();//类别 goods.setId(TypeTools.getInt(s1)); goods.setGoodsname(s2); goods.setPrice(TypeTools.getBigDecimal(s3)); goods.setOffset(TypeTools.getDouble(s4)); goods.setTime(TypeTools.getDate(s5)); goods.setCounts(TypeTools.getInt(s6)); goods.setCid(TypeTools.getInt(s7)); list.add(goods); } } catch (BiffException | IOException e) { e.printStackTrace(); } finally{ if(workbook != null) workbook.close(); } return list; } }
用来读取excel文件的类(TYPES类)
package com.softeem.goodsManager; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; public class TypesExcel { public static List<Types> readTypes(File file){ List<Types> list = new ArrayList<>(); Types types = null; Workbook workbook = null; try { //创建一个工作簿 workbook = Workbook.getWorkbook(file); //获取指定索引的表单 Sheet sheet = workbook.getSheet(0); //获取总行数 int rows = sheet.getRows(); for (int i = 1; i < rows; i++) { types = new Types(); String s1 = sheet.getCell(0, i).getContents();//编号 String s2 = sheet.getCell(1, i).getContents();//类别名 types.setId(TypeTools.getInt(s1)); types.setCname(s2); list.add(types); } } catch (BiffException | IOException e) { e.printStackTrace(); } finally{ if(workbook != null) workbook.close(); } return list; } }
工具类,用来在导入各种各种数据时的转换
package com.softeem.goodsManager; import java.math.BigDecimal; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.regex.Pattern; public class TypeTools { static SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy"); public static int getInt(String s){ if(Pattern.matches("^\\d*$",s)){ return Integer.parseInt(s); } return 0; } public static double getDouble(String s){ if(Pattern.matches("^\\d+\\.??\\d+$", s)){ return Double.parseDouble(s); } return 0.0; } //"" != null public static Date getDate(String s) { Date date = null; try { if(s != null && !"".equals(s)){ date = sdf.parse(s); }else{ date = sdf.parse("1/1/2016"); } } catch (ParseException e) { e.printStackTrace(); } return date; } //把日期类型对象转为字符串对象 public static String getStringDate(Date d){ if(d != null){ return sdf.format(d); } return ""; } public static BigDecimal getBigDecimal(String s) { if(Pattern.matches("^\\d+\\.??\\d+$", s)){ return new BigDecimal(s); } return new BigDecimal("0.0"); } }
添加DTO中的数据到数据库
package com.softeem.test; import java.io.File; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.softeem.dbutils.DBUtils; import com.softeem.goodsManager.Goods; import com.softeem.goodsManager.GoodsExcel; import com.softeem.goodsManager.Types; import com.softeem.goodsManager.TypesExcel; public class addDatas { public static List<Goods> goods = new ArrayList<>(); public static List<Types> types = new ArrayList<>(); static{ goods = GoodsExcel.readGoods(new File("src/商品表.xls")); types = TypesExcel.readTypes(new File("src/类别表.xls")); } public void insertDatasGoods(){ PreparedStatement ps = null; try { ps = DBUtils.getConn().prepareStatement("insert into goods(id,goodsname,price,offset,time,counts,cid) values(?,?,?,?,?,?,?)"); for(Goods g : goods) { ps.setInt(1, g.getId()); ps.setString(2, g.getGoodsname()); ps.setBigDecimal(3, g.getPrice()); ps.setDouble(4, g.getOffset()); ps.setDate(5, new java.sql.Date(g.getTime().getTime())); ps.setInt(6, g.getCounts()); ps.setInt(7, g.getCid()); ps.addBatch(); } ps.executeBatch(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { if(ps != null)ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public void insertDatasTypes(){ PreparedStatement ps = null; try { ps = DBUtils.getConn().prepareStatement("insert into types(id,cname) values(?,?)"); for(Types t : types) { ps.setInt(1, t.getId()); ps.setString(2, t.getCname()); ps.addBatch(); } ps.executeBatch(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { if(ps != null)ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public void deleteAll(){ String sql = "delete from goods where id > 0"; DBUtils.execUpdate(DBUtils.getConn(), sql); } public static void main(String[] args) { //需要分开添加数据,先添加Types new addDatas().insertDatasGoods(); // new addDatas().insertDatasTypes(); } }
测试,使用方法对数据库数据进行操作
package com.softeem.test; import java.math.BigDecimal; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import com.softeem.dbutils.DBUtils; public class Test { /** * 2.完成商品的检索相关功能 1.根据分类，显示分类下所有的商品信息，按照库存量从低到高排序(提供补货依据) * 2.模糊搜索,根据商品信息(名称或类别) * * 注:以上操作全部需要分页显示 * * @param args */ public void searchInformation(int cid, int currentPage, int pageSize) { PreparedStatement ps = null; try { ps = DBUtils.getConn().prepareStatement("select g.id,g.goodsname,g.price,g.offset,g.time,g.counts,g.cid,t.cname " + " from goods g,types t where g.cid = t.id and g.cid = ? " + "order by counts ASC " + "limit ?,?"); ps.setInt(1, cid); ps.setInt(2, (currentPage - 1) * pageSize); ps.setInt(3, pageSize); ResultSet rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); String goodsname = rs.getString("goodsname"); BigDecimal price = rs.getBigDecimal("price"); double offset = rs.getDouble("offset"); Date time = rs.getDate("time"); int counts = rs.getInt("counts"); int cidd = rs.getInt("cid"); String cname = rs.getString("cname"); System.out.println("商品id:" + id + " 商品名:" + goodsname + " 商品价格:" + price + " 商品折扣:" + offset + " 上架时间:" + time + " 库存" + counts + " 种类id:" + cidd + " 种类名:" + cname); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (ps != null) try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public void find(String name1,String name2, int currentPage, int pageSize) { PreparedStatement ps = null; try { ps = DBUtils.getConn().prepareStatement("select distinct g.id,g.goodsname,g.price,g.offset,g.time,g.counts,g.cid from goods g,types t where " + " g.goodsname like \"%\"?\"%\" or t.cname like \"%\"?\"%\" and g.cid = t.id order by g.counts asc limit ?,?"); ps.setString(1, name1); ps.setString(2, name2); ps.setInt(3, (currentPage - 1) * pageSize); ps.setInt(4, pageSize); ResultSet rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); String goodsname = rs.getString("goodsname"); BigDecimal price = rs.getBigDecimal("price"); double offset = rs.getDouble("offset"); Date time = rs.getDate("time"); int counts = rs.getInt("counts"); int cidd = rs.getInt("cid"); System.out.println("商品id:" + id + " 商品名:" + goodsname + " 商品价格:" + price + " 商品折扣:" + offset + " 上架时间:" + time + " 库存" + counts + " 种类id:" + cidd); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (ps != null) try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public static void main(String[] args) { //测试 // new Test().searchInformation(1, 1, 10); new Test().find("手机","手机", 1, 20); } }
