结合JDBC包装类的商品管理系统(Beta版本)

工具类,用来整合各种资源

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);
}
}