www.pudn.com > shop.rar > Goods_SearchDao.java

package com.soft136.shop.DAO; 
 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.util.ArrayList; 
import javax.sql.rowset.CachedRowSet; 
import com.soft136.shop.model.GoodsBean; 
import oracle.jdbc.rowset.OracleCachedRowSet; 
import java.sql.DriverManager; 
 
public class Goods_SearchDao { 
	// public OracleCachedRowSet getGoodsSearch(String goods_price_search) 
	// throws SQLException, ClassNotFoundException { 
	// Connection conn = null; 
	// OracleCachedRowSet ocrs = null; 
	// PreparedStatement ps = null; 
	// ResultSet rs = null; 
	// try { 
	// Class.forName("oracle.jdbc.driver.OracleDriver"); 
	// conn = DriverManager.getConnection( 
	// "jdbc:oracle:thin:@192.168.1.250:1521:coach", "shop", 
	// "shop"); 
	// if (goods_price_search.equals("价格从高到低查询")) { 
	// ps = conn 
	// .prepareStatement("select 
	// GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url 
	// from goods ORDER BY GOODS_PRICE desc"); 
	// rs = ps.executeQuery(); 
	// ocrs = new OracleCachedRowSet(); 
	// ocrs.populate(rs); 
	// return ocrs; 
	// } else if (goods_price_search.equals("价格从低到高查询")) { 
	// ps = conn 
	// .prepareStatement("select 
	// GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url 
	// from goods ORDER BY GOODS_PRICE asc"); 
	// rs = ps.executeQuery(); 
	// rs = ps.executeQuery(); 
	// ocrs = new OracleCachedRowSet(); 
	// ocrs.populate(rs); 
	// return ocrs; 
	// } else { 
	// return null; 
	// } 
	// } finally { 
	// if (ps != null) { 
	// ps.close(); 
	// } 
	// if (conn != null) { 
	// conn.close(); 
	// } 
	// } 
	// } 
	// 
	// public OracleCachedRowSet getGoodsSearch(int startPrice, int endPrice) 
	// throws SQLException, ClassNotFoundException { 
	// Connection conn = null; 
	// OracleCachedRowSet ocrs = null; 
	// PreparedStatement ps = null; 
	// ResultSet rs = null; 
	// try { 
	// if (endPrice < 400) { 
	// Class.forName("oracle.jdbc.driver.OracleDriver"); 
	// conn = DriverManager.getConnection( 
	// "jdbc:oracle:thin:@192.168.1.250:1521:coach", "shop", 
	// "shop"); 
	// ps = conn 
	// .prepareStatement("select 
	// GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url 
	// from goods WHERE GOODS_PRICE BETWEEN " 
	// + startPrice + " AND " + endPrice); 
	// rs = ps.executeQuery(); 
	// } 
	// ocrs = new OracleCachedRowSet(); 
	// ocrs.populate(rs); 
	// return ocrs; 
	// } finally { 
	// if (ps != null) { 
	// ps.close(); 
	// } 
	// if (conn != null) { 
	//				conn.close(); 
	//			} 
	//		} 
	//	} 
 
	public OracleCachedRowSet getGoodsSearch(String goods_price_search, 
			int startPrice, int endPrice) throws SQLException, 
			ClassNotFoundException { 
		Connection conn = null; 
		OracleCachedRowSet ocrs = null; 
		PreparedStatement ps = null; 
		ResultSet rs = null; 
		try { 
			Class.forName("oracle.jdbc.driver.OracleDriver"); 
			conn = DriverManager.getConnection( 
					"jdbc:oracle:thin:@192.168.1.250:1521:coach", "shop", 
					"shop"); 
			if (endPrice < 400) { 
				if (goods_price_search.equals("价格从高到低查询")) { 
					ps = conn 
							.prepareStatement("select GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url from goods ORDER BY GOODS_PRICE desc"); 
					rs = ps.executeQuery(); 
				} else if (goods_price_search.equals("价格从低到高查询")) { 
					ps = conn 
							.prepareStatement("select GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url from goods ORDER BY GOODS_PRICE asc"); 
					rs = ps.executeQuery(); 
				} else { 
					ps = conn 
							.prepareStatement("select GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url from goods ORDER BY GOODS_id asc"); 
					rs = ps.executeQuery(); 
				} 
			} else { 
				if (goods_price_search.equals("价格从高到低查询")) { 
					ps = conn 
							.prepareStatement("select GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url from goods WHERE GOODS_PRICE BETWEEN " 
									+ startPrice 
									+ " AND " 
									+ endPrice 
									+ "ORDER BY GOODS_PRICE desc"); 
					rs = ps.executeQuery(); 
				} else if (goods_price_search.equals("价格从低到高查询")) { 
					ps = conn 
							.prepareStatement("select GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url from goods WHERE GOODS_PRICE BETWEEN " 
									+ startPrice 
									+ " AND " 
									+ endPrice 
									+ "ORDER BY GOODS_PRICE asc"); 
					rs = ps.executeQuery(); 
				} else { 
					ps = conn 
							.prepareStatement("select GOODS_ID,GOODS_NAME,TYPE_ID,GOODS_PRICE,GOODS_DICOUNT,goods_desc,supplier,qty,ispreferred,pic_url from goods WHERE GOODS_PRICE BETWEEN " 
									+ startPrice 
									+ " AND " 
									+ endPrice 
									+ "ORDER BY GOODS_id asc"); 
					rs = ps.executeQuery(); 
				} 
			} 
			ocrs = new OracleCachedRowSet(); 
			ocrs.populate(rs); 
			return ocrs; 
		} finally { 
			if (ps != null) { 
				ps.close(); 
			} 
			if (conn != null) { 
				conn.close(); 
			} 
		} 
	} 
}