package dbutils;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import DTO.Goods;
import DTO.Types;
import dbutils.DBUtils;
import dbutils.DBUtils.CallBack;
public class ShowIOneTypeGoods {
/**
* 1.根据分类，显示分类下所有的商品信息，按照库存量从低到高排序
* (提供补货依据)
*/
public List<Goods> findTypeGoods(Types t ,int pageAt ,int pageSize) {
String sql = "select * from Goods where cid = ? order by count asc limit ?,?";
return DBUtils.queryList(sql,
new CallBack<Goods>() {
@Override
public List<Goods> getDatas(ResultSet rs) {
List<Goods> list = null;
try {
list = new ArrayList<Goods>();
Goods g = null;
while(rs.next()){
g= new Goods();
g.setId ( rs.getInt("id"));
g.setGoodsName( rs.getString("goodsname"));
g.setPrice( rs.getBigDecimal("price"));
g.setOffset(rs.getDouble("offset"));
g.setTime(rs.getDate("time"));
g.setCounts(rs.getInt("count"));
g.setCid(rs.getInt("cid"));
list.add(g);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}, t.getId(),(pageAt-1)*pageSize,pageSize);
}
public List<Goods> blurFind (String str ,int pageAt ,int pageSize){
String sql = "select * from $goods_Types where goodsname like ? or cname like ? limit ?,?";
return DBUtils.queryList(sql,
new CallBack<Goods>() {
@Override
public List<Goods> getDatas(ResultSet rs) {
List<Goods> list = null;
try {
list = new ArrayList<Goods>();
Goods g = null;
while(rs.next()){
g= new Goods();
g.setId ( rs.getInt("id"));
g.setGoodsName( rs.getString("goodsname"));
g.setPrice( rs.getBigDecimal("price"));
g.setOffset(rs.getDouble("offset"));
g.setTime(rs.getDate("time"));
g.setCounts(rs.getInt("count"));
g.setCid(rs.getInt("cid"));
list.add(g);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}, "%"+str+"%","%"+str+"%",(pageAt-1)*pageSize,pageSize);
}
public static void main(String[] args) {
// Types t = new Types();
// t.setId(36);
// List<Goods>list = new ShowIOneTypeGoods().findTypeGoods(t,1,5);
// for (Goods g : list) {
// System.out.println(g);
// }
List<Goods>list = new ShowIOneTypeGoods().blurFind("电",1,5);
for (Goods g : list) {
System.out.println(g);
}
}
}
package service;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import DAO.GoodsDAO;
import DAO.TypesDAO;
import DTO.Goods;
import DTO.Types;
import dbutils.DBUtils;
import dbutils.TypeTools;
public class LoginExcel {
/**
* @param args
*/
public static List<Goods> loadingGoods(File file ){
List<Goods> goods = new ArrayList<Goods>();
try {
//创建一个工作簿
Workbook workbook = Workbook.getWorkbook(file);
//获取所有表单对象
Sheet []sheets = workbook.getSheets();
//获取指定下标表单对象
Sheet sheet = workbook.getSheet(0);
//获取行数
int rows = sheet.getRows();
//获取行中的所有列
Goods g = null;
for(int i = 1 ; i < rows ;i++){
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();
//(int gno, String gname, BigDecimal price, double discount,int count
g = new Goods(TypeTools.toInt(s1),s2,TypeTools.toBigDecimal(s3),
TypeTools.toDouble(s4),TypeTools.toDate(s5),TypeTools.toInt(s6),TypeTools.toInt(s7));
goods.add(g);
}
} catch (BiffException | IOException e) {
e.printStackTrace();
}
return goods ;
}
public static List<Types> loadingTypes(File file ){
List<Types> types= new ArrayList<Types>();
try {
//创建一个工作簿
Workbook workbook = Workbook.getWorkbook(file);
//获取所有表单对象
Sheet []sheets = workbook.getSheets();
//获取指定下标表单对象
Sheet sheet = workbook.getSheet(0);
//获取行数
int rows = sheet.getRows();
//获取行中的所有列
Types t = null;
for(int i = 1 ; i < rows ;i++){
String s1= sheet.getCell(0, i).getContents();
String s2= sheet.getCell(1, i).getContents();
//(int gno, String gname, BigDecimal price, double discount,int count
t = new Types(TypeTools.toInt(s1),s2);
types.add(t);
}
} catch (BiffException | IOException e) {
e.printStackTrace();
}
return types ;
}
public void loding() throws SQLException{
File goodsfile = new File("src\\商品表.xls");
File typessfile = new File("src\\类别表.xls");
List<Goods> goods= loadingGoods(goodsfile);
List<Types> types= loadingTypes(typessfile);
TypesDAO typesdao = new TypesDAO();
GoodsDAO goodsdao = new GoodsDAO();
Connection conn = null;
for (Types t : types) {
conn = DBUtils.getConn();
typesdao.insert(conn,t);
conn.close();
}
for (Goods g : goods) {
conn = DBUtils.getConn();
goodsdao.insert(conn,g);
conn.close();
}
}
public static void main(String[] args) throws SQLException {
new LoginExcel().loding();
}
}
import java.util.regex.Pattern;public class TypeTools {private static SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy") ;public static int toInt(String s ){if(Pattern.matches("^\\d+$", s)){return Integer.parseInt(s);}return 0 ;}public static double
toDouble(String s){if(Pattern.matches("^\\d+(\\.\\d+)?$", s)){return Double.parseDouble(s);}return 0.0 ;}public static Date toDate(String s){Date d = null;try {if(s != null && !"".equals(s)){return sdf.parse(s);}} catch (ParseException e) {e.printStackTrace();}return
d;}public static BigDecimal toBigDecimal(String s){BigDecimal b = new BigDecimal("0.0");if(Pattern.matches("^\\d+(\\.\\d+)?$", s)){b = new BigDecimal(s);}return b;} public static String changeDate(Date d){ if(d != null ){ return sdf.format(d) ; }else return
0+""; }}
