online shopping

package com.cnsuning.shopping.show; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Random; import com.cnsuning.shopping.dao.ConnectionUtil; import com.cnsuning.shopping.pojo.Item; public class Show { private static int randomCount = 6; public static List<Item> GetProductsList() { Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "select * from tbl_item"; ArrayList<Item> result = new ArrayList<Item>(); try { ConnectionUtil util = new ConnectionUtil(); conn = util.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); ArrayList<Integer> list = new ArrayList<Integer>(); while (rs.next()) { list.add(new Integer(rs.getInt(1)));// 将所有编号 存入动态数组: } int count = list.size(); rs.close(); if (count <= randomCount) { rs = stmt.executeQuery(sql); } else { int flag = count / randomCount; int ran = new Random().nextInt(flag);// 动态取可用编码中的随机数; String condition = ""; for (int j = 0; j < randomCount; j++) { condition += (list.get(ran) + ","); ran += flag; // 随机数种子移动; } condition = condition.substring(0, condition.lastIndexOf(',')); sql = "select * from tbl_item where ITEM_ID in(" + condition + ")"; rs = stmt.executeQuery(sql); while (rs.next()) { Item tbl = new Item(rs.getString(1), rs.getString(2), rs .getLong(3), rs.getString(4), rs.getString(5), rs .getString(6), rs.getString(7), rs.getString(8), rs .getString(9)); result.add(tbl); } return result; } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return null; } public static List<Item> shownew(String sql) { Connection conn = null; Statement stmt = null; ResultSet rs = null; String sqll = "select * from tbl_item where " + sql; ArrayList<Item> result = new ArrayList<Item>(); try { ConnectionUtil util = new ConnectionUtil(); conn = util.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sqll); while (rs.next()) { Item tbl = new Item(rs.getString(1), rs.getString(2), rs .getLong(3), rs.getString(4), rs.getString(5), rs .getString(6), rs.getString(7), rs.getString(8), rs .getString(9)); result.add(tbl); } return result; } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return null; } public static List<Tuijian> tuijian(){ Connection conn; String sqll = "select b.item_id as id ,item_name ,a.item_price ,a.item_img,sum(ORDER_QUANTITY) as sumCount " + "from tbl_item a,tbl_orderItem b " + "where a.item_id = b.item_id " + "group by b.item_id " + "order by sumCount " + "desc " + "limit 0,3"; ArrayList<Tuijian> result = new ArrayList<Tuijian>(); try { ConnectionUtil util = new ConnectionUtil(); conn = util.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sqll); while (rs.next()) { Tuijian tuijian = new Tuijian(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)); result.add(tuijian); } rs.close(); stmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return result; } public static List<Item> newproduct(){ Connection conn; String sqll = "select * from tbl_item order by item_id desc limit 3"; ArrayList<Item> result = new ArrayList<Item>(); try { ConnectionUtil util = new ConnectionUtil(); conn = util.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sqll); while (rs.next()) { Item newprodect = new Item(rs.getString(1), rs.getString(2), rs .getLong(3), rs.getString(4), rs.getString(5), rs .getString(6), rs.getString(7), rs.getString(8), rs .getString(9)); result.add(newprodect); } rs.close(); stmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return result; } }