package com.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.entity.Goods;
import com.entity.SmallClass;
import com.util.DBUtil;
public class GoodsDao {
/**
* 添加商品信息
* @param good
* @return k
*/
public static int add(Goods good){
String sql="insert into goods(g_name,gb_id,gs_id,g_bpic,g_bpic1,g_bpic2,g_spic1,"+
"g_spic2,g_desc,g_carry,g_count,g_price,g_dis,g_type) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
Object obj[]={good.getG_name(),good.getGb_id(),good.getGs_id(),good.getG_bpic(),good.getG_bpic1(),
good.getG_bpic2(),good.getG_spic1(),good.getG_spic2(),good.getG_desc(),good.getG_carry(),
good.getG_count(),good.getG_price(),good.getG_dis(),good.getG_type()};
int k=DBUtil.Update(sql, obj);
return k;
}
/**
* 修改商品信息
* @param good
* @return k
*/
public static int update(Goods good,String g_id){
String sql="update goods set g_name=?,gb_id=?,gs_id=?,g_bpic=?,g_bpic1=?,g_bpic2=?,g_spic1=?,"+
"g_spic2=?,g_desc=?,g_carry=?,g_count=?,g_price=?,g_dis=?,g_type=? where g_id=?";
Object obj[]={good.getG_name(),good.getGb_id(),good.getGs_id(),good.getG_bpic(),good.getG_bpic1(),
good.getG_bpic2(),good.getG_spic1(),good.getG_spic2(),good.getG_desc(),good.getG_carry(),
good.getG_count(),good.getG_price(),good.getG_dis(),good.getG_type(),g_id};
int k=DBUtil.Update(sql, obj);
return k;
}
/**
* 删除商品信息
* @param good
* @return k
*/
public static int delete(String g_id){
String sql="delete from goods where g_id=?";
Object obj[]={g_id};
int k=DBUtil.Update(sql, obj);
return k;
}
/**
* 查询商品信息总数
* @return count
*/
public static int queryCount(){
int count=0;
String sql="select count(*) from goods";
Object obj[]={};
ResultSet res=DBUtil.query(sql, obj);
try {
if (res.next()) {
count=res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return count;
}
/**
* 分页查询商品信息的编号、商品名称、价格、折扣、库存、图片展示gb_pic
* @param pageStart
* @param pageSize
* @return
*/
public static List<Goods> queryAll(Integer pageStart,Integer pageSize){
String sql="select * from goods limit ?,?";
Object obj[]={pageStart,pageSize};
List<Goods> list=new ArrayList<Goods>();
ResultSet res=DBUtil.query(sql, obj);
try {
while(res.next()){
Goods good=new Goods(res.getInt(1), res.getString(2), res.getString(5),res.getDouble(11), res.getInt(12), res.getDouble(13), res.getDouble(14));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 查询商品名称、图片展示gb_pic
* @param pageStart
* @param pageSize
* @return
*/
public static List<Goods> queryAll(){
String sql="select g_id,g_name,g_bpic from goods limit 0,9";
Object obj[]={};
List<Goods> list=new ArrayList<Goods>();
ResultSet res=DBUtil.query(sql, obj);
try {
while(res.next()){
Goods good=new Goods();
good.setG_id(res.getInt(1));
good.setG_name(res.getString(2));
good.setG_bpic(res.getString(3));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 查询g_type为最新的商品名称、图片展示gb_pic
* @param pageStart
* @param pageSize
* @return
*/
public static List<Goods> queryType(){
String sql="select g_id,g_name,g_bpic from goods where g_type='新品'";
Object obj[]={};
List<Goods> list=new ArrayList<Goods>();
ResultSet res=DBUtil.query(sql, obj);
try {
while(res.next()){
Goods good=new Goods();
good.setG_id(res.getInt(1));
good.setG_name(res.getString(2));
good.setG_bpic(res.getString(3));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 模糊查询商品信息总数
* @return count
*/
public static int queryCount(String serch){
int count=0;
String sql="select count(*) from goods where g_name like ?";
Object obj[]={"%"+serch+"%"};
ResultSet res=DBUtil.query(sql, obj);
try {
if (res.next()) {
count=res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return count;
}
/**
* 模糊分页查询商品信息的编号、商品名称、价格、折扣、库存、图片展示gb_pic
* @param pageStart
* @param pageSize
* @param serch
* @return
*/
public static List<Goods> queryGoods(Integer pageStart,Integer pageSize,String serch){
String sql="select * from goods where g_name like ? limit ?,?";
Object obj[]={"%"+serch+"%",pageStart,pageSize};
List<Goods> list=new ArrayList<Goods>();
ResultSet res=DBUtil.query(sql, obj);
try {
while(res.next()){
Goods good=new Goods(res.getInt(1), res.getString(2), res.getString(5),res.getDouble(11), res.getInt(12), res.getDouble(13), res.getDouble(14));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 根据编号查询所有的商品信息
* @return
*/
public static List<Goods> queryAll(String g_id){
String sql="select g_name,b_name,s_name,g_bpic,g_bpic1,g_bpic2,g_spic1,g_spic2," +
"g_desc,g_carry,g_count,g_price,g_dis,g_type,g_id from goods,bigclass," +
"smallclass where b_id=(select gb_id from goods where g_id=?) and " +
"s_id=(select gs_id from goods where g_id=?) and g_id=?";
Object obj[]={g_id,g_id,g_id};
List<Goods> list=new ArrayList<Goods>();
ResultSet res=DBUtil.query(sql, obj);
try {
if(res.next()){
Goods good=new Goods(res.getString(1),res.getString(2),res.getString(3),res.getString(4), res.getString(5), res.getString(6),res.getString(7),res.getString(8),res.getString(9),res.getDouble(10), res.getInt(11),res.getDouble(12),res.getDouble(13),res.getString(14),res.getInt(15));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 根据小分类模糊查询商品信息的编号
* @param pageStart
* @param pageSize
* @param serch
* @return
*/
public static List<SmallClass> serchSmall(String serch){
String sql="select s_id from smallclass where s_name like ?";
Object obj[]={"%"+serch+"%"};
List<SmallClass> list=new ArrayList<SmallClass>();
ResultSet res=DBUtil.query(sql, obj);
try {
int t=0;
while(res.next()){
t=1;
SmallClass sm=new SmallClass();
sm.setS_id(res.getInt(1));
list.add(sm);
}
if (t==0) {
SmallClass sm=new SmallClass();
sm.setS_id(-1);
list.add(sm);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 根据小分类模糊分页查询商品信息的编号、商品名称、价格、折扣、图片展示gb_pic
* @param pageStart
* @param pageSize
* @param serch
* @return
*/
public static List<Goods> serchGoods(Integer pageStart,Integer pageSize,String serch){
List<SmallClass> li=serchSmall(serch);
List<Goods> list=new ArrayList<Goods>();
for (SmallClass sm : li) {
String sql="select distinct g_id,g_name,g_price,g_dis,g_bpic from goods where g_name like ? or gs_id=? limit ?,?";
Object obj[]={"%"+serch+"%",sm.getS_id(),pageStart,pageSize};
ResultSet res=DBUtil.query(sql, obj);
try {
while(res.next()){
Goods good=new Goods();
good.setG_id(res.getInt(1));
good.setG_name(res.getString(2));
good.setG_price(res.getDouble(3));
good.setG_dis(res.getDouble(4));
good.setG_bpic(res.getString(5));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
}
return list;
}
/**
* 根据小分类模糊搜索商品信息总数
* @return count
*/
public static int serchCount(String serch){
int count=0;
String sql="select distinct count(g_id) from goods where g_name like ? or gs_id=(select s_id from smallclass where s_name like ?)";
Object obj[]={"%"+serch+"%","%"+serch+"%"};
ResultSet res=DBUtil.query(sql, obj);
try {
if (res.next()) {
count=res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return count;
}
/**
* 模糊分页查询所属大分类商品信息的编号、商品名称、价格、折扣、图片展示gb_pic
* @param pageStart
* @param pageSize
* @param serch
* @return
*/
public static List<Goods> serchBigGoods(Integer pageStart,Integer pageSize,String serch){
String sql="select distinct g_id,g_name,g_price,g_dis,g_bpic from goods where gb_id=(select b_id from bigclass where b_name like ?) limit ?,?";
Object obj[]={"%"+serch+"%",pageStart,pageSize};
List<Goods> list=new ArrayList<Goods>();
ResultSet res=DBUtil.query(sql, obj);
try {
while(res.next()){
Goods good=new Goods();
good.setG_id(res.getInt(1));
good.setG_name(res.getString(2));
good.setG_price(res.getDouble(3));
good.setG_dis(res.getDouble(4));
good.setG_bpic(res.getString(5));
list.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return list;
}
/**
* 模糊搜索所属大分类商品信息总数
* @return count
*/
public static int serchBigCount(String serch){
int count=0;
String sql="select distinct count(g_id) from goods where gb_id=(select b_id from bigclass where b_name like ?)";
Object obj[]={"%"+serch+"%"};
ResultSet res=DBUtil.query(sql, obj);
try {
if (res.next()) {
count=res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return count;
}
/**
* 查询商品编号为ID的库存
* @return count
*/
public static int buyCount(String i_id){
String sql="select g_count from goods where g_id=(select ig_id from indent where i_id=?)";
Object obj[]={i_id};
ResultSet res=DBUtil.query(sql, obj);
int count=0;
try {
if (res.next()) {
count=res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close();
}
return count;
}
}
最近下载更多
最近浏览