package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.entity.CommentInfo;
import com.entity.GoodsInfo;
/**
* 商品操作类
* @author Administrator
*
*/
public class GoodsCtrl {
/**
* 添加商品方法
* @param goods
* @return
*/
public int addGoods(GoodsInfo goods){
int res =0;
Connection conn =ConnDB.getConn();
PreparedStatement stmt =null;
String sql ="insert into GoodsInfo values(?,?,?,?,?,?,?,?,?,?,?,?)";
try {
stmt =conn.prepareStatement(sql);
stmt.setString(1, goods.getGoodsname());
stmt.setString(2, goods.getGoodssort());
stmt.setDouble(3, goods.getGoodsprice());
stmt.setDouble(4, goods.getGoodscarr());
stmt.setString(5, goods.getGoodsaddr());
stmt.setString(6, goods.getGoodsimg());
stmt.setString(7, goods.getGoodstuijian());
stmt.setString(8, goods.getGoodsxianliang());
stmt.setString(9, goods.getGoodstejia());
stmt.setString(10, goods.getGoodscuxiao());
stmt.setString(11, goods.getGoodszt());
stmt.setString(12, goods.getGoodsintro());
res =stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(null, stmt, conn);
}
return res;
}
/*==商品列表数据分页==*/
/**
* 根据当前页数和每页显示的行数来查询相应的结果
*
* @param pageSize
* 每页显示的行数
* @param pageNow
* 当前的页数
* @return
*/
public ArrayList<GoodsInfo> getGoodsForPage(int pageSize, int pageNow) {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select Top " + pageSize
+ " * from GoodsInfo where goodsId not in(select Top (" + pageSize
+ " * (" + pageNow + "- 1)) goodsId from GoodsInfo)";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodssort(rs.getString("goodsSort"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodscarr(rs.getDouble("goodsCarr"));
goods.setGoodsaddr(rs.getString("goodsAddr"));
goods.setGoodsimg(rs.getString("goodsImg"));
goods.setGoodszt(rs.getString("goodsZt"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 获取总页数
* @param pageSize
* @return
*/
public int getGoodsCount(int pageSize){
int pageCount = 0;
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
//页数计算
if(goodsCount % pageSize == 0){
pageCount = goodsCount / pageSize;
}else{
pageCount = goodsCount / pageSize + 1;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return pageCount;
}
/**
* 根据ID查询商品方法
* @param goodsId
* @return
*/
public GoodsInfo selgoods(int goodsId){
GoodsInfo goods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from GoodsInfo where goodsId=" + goodsId;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodssort(rs.getString("goodsSort"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodscarr(rs.getDouble("goodsCarr"));
goods.setGoodsaddr(rs.getString("goodsAddr"));
goods.setGoodsimg(rs.getString("goodsImg"));
goods.setGoodstuijian(rs.getString("goodsTuijian"));
goods.setGoodsxianliang(rs.getString("goodsXianliang"));
goods.setGoodstejia(rs.getString("goodsTejia"));
goods.setGoodscuxiao(rs.getString("goodsCuxiao"));
goods.setGoodszt(rs.getString("goodsZt"));
goods.setGoodsintro(rs.getString("goodsIntro"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return goods;
}
/**
* 修改商品方法
*/
public int updateGoods(GoodsInfo goods){
int res = 0;
Connection conn =ConnDB.getConn();
PreparedStatement stmt = null;
String sql = "update GoodsInfo set goodsName=?,goodsSort=?,goodsPrice=?,goodsCarr=?,goodsAddr=?,goodsImg=?,goodsTuijian=?,goodsXianliang=?,goodsTejia=?,goodsCuxiao=?,goodsZt=?,goodsIntro=?" +" where goodsId=?";
try {
stmt =conn.prepareStatement(sql);
stmt.setString(1, goods.getGoodsname());
stmt.setString(2, goods.getGoodssort());
stmt.setDouble(3, goods.getGoodsprice());
stmt.setDouble(4, goods.getGoodscarr());
stmt.setString(5, goods.getGoodsaddr());
stmt.setString(6, goods.getGoodsimg());
stmt.setString(7, goods.getGoodstuijian());
stmt.setString(8, goods.getGoodsxianliang());
stmt.setString(9, goods.getGoodstejia());
stmt.setString(10, goods.getGoodscuxiao());
stmt.setString(11, goods.getGoodszt());
stmt.setString(12, goods.getGoodsintro());
stmt.setInt(13, goods.getGoodsId());
res = stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(null, stmt, conn);
}
return res;
}
/**
* 根据ID删除商品
* @param userId
* @return
*/
public int deleteGoods(int goodsId){
int res = 0;
String sql = "delete GoodsInfo where goodsId=?";
Connection conn = ConnDB.getConn();
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1, goodsId);
res = stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(null, stmt, conn);
}
return res;
}
/*==推荐、限量、特价、促销商品搜索数据分页==*/
/**
* 根据导航栏连接搜索商品 *
* 根据当前页数和每页显示的行数来查询相应的结果
*
* @param pageSize
* 每页显示的行数
* @param pageNow
* 当前的页数
* @param tj
* 查询条件：（推荐、特价等）
* @return
*/
public ArrayList<GoodsInfo> getDaohlGoods(int pageSize, int pageNow,String tj) {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select Top " + pageSize
+ " * from GoodsInfo where goodsId not in(select Top (" + pageSize
+ " * (" + pageNow + "- 1)) goodsId from GoodsInfo) and "+tj+"='true' and goodsZt='true' order by goodsId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 获取导航栏连接搜索商品的总页数
* @param pageSize
* @param tj
* @return
*/
public int getDaohlGoodsCount(int pageSize,String tj){
int pageCount = 0;
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where "+tj+"='true' and goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
//页数计算
if(goodsCount % pageSize == 0){
pageCount = goodsCount / pageSize;
}else{
pageCount = goodsCount / pageSize + 1;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return pageCount;
}
/**
* 共找到多少件商品方法
* @param tj
* @return
*/
public int getSumGoodsCount(String tj){
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where "+tj+"='true' and goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return goodsCount;
}
/*==最新商品数据分页==*/
/**
* 根据导航栏最新商品搜索商品
* 根据当前页数和每页显示的行数来查询相应的结果
*
* @param pageSize
* 每页显示的行数
* @param pageNow
* 当前的页数
* @return
*/
public ArrayList<GoodsInfo> getToGoods(int pageSize, int pageNow) {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select Top " + pageSize
+ " * from GoodsInfo where goodsId not in(select Top (" + pageSize
+ " * (" + pageNow + "- 1)) goodsId from GoodsInfo) and goodsZt='true' order by goodsId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 最新商品的总页数
* @param pageSize
* @return
*/
public int getToGoodsCount(int pageSize){
int pageCount = 0;
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
//页数计算
if(goodsCount % pageSize == 0){
pageCount = goodsCount / pageSize;
}else{
pageCount = goodsCount / pageSize + 1;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return pageCount;
}
/**
* 最新商品的总个数
* @return
*/
public int getToGoodsCount2(){
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return goodsCount;
}
/**
* 首页今日新品
* @return
*/
public ArrayList<GoodsInfo> getToGoods2() {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from GoodsInfo where goodsZt='true' order by goodsId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 首页精品推荐
* @return
*/
public ArrayList<GoodsInfo> getjptjGoods() {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from GoodsInfo where goodsTuijian='true' and goodsZt='true' order by goodsId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 首页人气热卖
* @return
*/
public ArrayList<GoodsInfo> getxlGoods() {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from GoodsInfo where goodsXianliang='true' and goodsZt='true' order by goodsId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/*==根据商品分类查询商品数据分页==*/
/**
* 根据商品分类查询商品
* 根据当前页数和每页显示的行数来查询相应的结果
*
* @param pageSize
* 每页显示的行数
* @param pageNow
* 当前的页数
* @param sort
* 商品分类
* @return
*/
public ArrayList<GoodsInfo> getSortGoods(int pageSize, int pageNow,int sortId) {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select Top " + pageSize
+ " * from GoodsInfo where goodsId not in(select Top (" + pageSize
+ " * (" + pageNow + "- 1)) goodsId from GoodsInfo) and GoodsSort='"+sortId+"' and goodsZt='true'";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 获取分类商品总页数
* @param pageSize
* @param sort
* @return
*/
public int getSortGoodsCount(int pageSize,int sortId){
int pageCount = 0;
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where GoodsSort='"+sortId+"' and goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
//页数计算
if(goodsCount % pageSize == 0){
pageCount = goodsCount / pageSize;
}else{
pageCount = goodsCount / pageSize + 1;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return pageCount;
}
/**
* 分类商品总个数
* @param pageSize
* @param sort
* @return
*/
public int getSortGoodsCount2(int sortId){
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where GoodsSort='"+sortId+"' and goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return goodsCount;
}
/*==搜索商品数据分页==*/
/**
* 根据用户输入关键字查询商品
* 根据当前页数和每页显示的行数来查询相应的结果
*
* @param pageSize
* 每页显示的行数
* @param pageNow
* 当前的页数
* @param GoodsName
* 商品搜索关键字
* @return
*/
public ArrayList<GoodsInfo> SearchGoods(int pageSize, int pageNow,String GoodsName) {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select Top " + pageSize
+ " * from GoodsInfo where goodsId not in(select Top (" + pageSize
+ " * (" + pageNow + "- 1)) goodsId from GoodsInfo) and goodsName like '%"+GoodsName+"%' and goodsZt='true'";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 获取搜索商品总页数
* @param pageSize
* @param sort
* @param GoodsName
* @return
*/
public int getSearchGoodsCount(int pageSize,String GoodsName){
int pageCount = 0;
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where goodsName like '%"+GoodsName+"%' and goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
//页数计算
if(goodsCount % pageSize == 0){
pageCount = goodsCount / pageSize;
}else{
pageCount = goodsCount / pageSize + 1;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return pageCount;
}
/**
* 搜索商品的总个数
* @return
*/
public int getSearchGoodsCount2(String GoodsName){
int goodsCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCount from GoodsInfo where goodsName like '%"+GoodsName+"%' and goodsZt='true'";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
goodsCount = rs.getInt("goodsCount");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return goodsCount;
}
/**
* 相关商品
*
* @param sortId
* 商品分类Id
* @return
*/
public ArrayList<GoodsInfo> xgGoods(int sortId) {
ArrayList<GoodsInfo> agoods = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from GoodsInfo where GoodsSort ='"+sortId+"' and goodsZt='true' order by goodsId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(agoods==null){
agoods=new ArrayList<GoodsInfo>();
}
GoodsInfo goods = new GoodsInfo();
goods.setGoodsId(rs.getInt("goodsId"));
goods.setGoodsname(rs.getString("goodsName"));
goods.setGoodsprice(rs.getDouble("goodsPrice"));
goods.setGoodsimg(rs.getString("goodsImg"));
agoods.add(goods);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return agoods;
}
/**
* 添加商品评论方法
* @param comment
* @return
*/
public int addComment(CommentInfo comment){
int res =0;
Connection conn =ConnDB.getConn();
PreparedStatement stmt =null;
String sql ="insert into CommentInfo values(?,?,?,default)";
try {
stmt =conn.prepareStatement(sql);
stmt.setInt(1, comment.getGoodsid());
stmt.setString(2, comment.getCommentname());
stmt.setString(3, comment.getCommentContent());
res =stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(null, stmt, conn);
}
return res;
}
/**
* 查看商品评论方法
* @param goodsId
* @return
*/
public ArrayList<CommentInfo> goodsComment(int goodsId) {
ArrayList<CommentInfo> acomment = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from CommentInfo where goodsId="+goodsId;
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(acomment==null){
acomment=new ArrayList<CommentInfo>();
}
CommentInfo comment = new CommentInfo();
comment.setCommentname(rs.getString("commentName"));
comment.setCommentContent(rs.getString("commentContent"));
comment.setCommentDate(rs.getString("commentDate"));
acomment.add(comment);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return acomment;
}
/*==评论列表数据分页==*/
/**
* 根据当前页数和每页显示的行数来查询相应的结果
*
* @param pageSize
* 每页显示的行数
* @param pageNow
* 当前的页数
* @return
*/
public ArrayList<CommentInfo> selgoodsComment(int pageSize, int pageNow) {
ArrayList<CommentInfo> acomment = null;
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select Top " + pageSize
+ " * from CommentInfo where commentId not in(select Top (" + pageSize
+ " * (" + pageNow + "- 1)) commentId from CommentInfo) order by commentId desc";
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
if(acomment==null){
acomment=new ArrayList<CommentInfo>();
}
CommentInfo comment = new CommentInfo();
comment.setCommentId(rs.getInt("commentId"));
comment.setGoodsid(rs.getInt("goodsId"));
comment.setCommentname(rs.getString("commentName"));
comment.setCommentContent(rs.getString("commentContent"));
comment.setCommentDate(rs.getString("commentDate"));
acomment.add(comment);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return acomment;
}
/**
* 获取商品评论总页数
* @param pageSize
* @return
*/
public int goodsCommentCount(int pageSize){
int pageCount = 0;
int CommentCount = 0;//获取到商品的总数
Connection conn = ConnDB.getConn();
Statement stmt = null;
ResultSet rs = null;
String sql = "select count(*) as goodsCommentCount from CommentInfo";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs.next()){
CommentCount = rs.getInt("goodsCommentCount");
}
//页数计算
if(CommentCount % pageSize == 0){
pageCount = CommentCount / pageSize;
}else{
pageCount = CommentCount / pageSize + 1;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(rs, stmt, conn);
}
return pageCount;
}
/**
* 删除商品评论
* @param commentId
* @return
*/
public int deleteGoodsComment(int commentId){
int res = 0;
String sql = "delete CommentInfo where commentId=?";
Connection conn = ConnDB.getConn();
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1, commentId);
res = stmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnDB.close(null, stmt, conn);
}
return res;
}
}
最近下载更多
最近浏览