dboperate.java

网上商城一个购物网站具有登陆会员注册添加购物车

package com.hit.persistence;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
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 java.util.List;

import com.hit.bean.*;

public class DBOperate {
	public DBOperate(){}
	
	public String clobToString(Clob clob){
		String content = "";
		if (clob!=null) {
		try {
			Reader read = clob.getCharacterStream();
			BufferedReader br = new BufferedReader(read);
			String s = null;
			while((s = br.readLine())!=null){
				content += s;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		}
		return content;
	}
	
	public AdminUser getAdminUser(String userId) {
		AdminUser adminuser = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			sql = "select * from adminuser where username='"+userId+"'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				adminuser = new AdminUser();
				adminuser.setId(rs.getInt("id"));
				adminuser.setUsername(rs.getString("username"));
				adminuser.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return adminuser;
	}
	
	public Sort getSort(int  Id) {
		Sort sort = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			sql = "select * from sort where id="+Id;
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				sort = new Sort();
				sort.setId(Id);
				sort.setName(rs.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sort;
	}
	
	public Member getMember(int  Id) {
		Member member = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			sql = "select * from Member where id="+Id;
			rs = stmt.executeQuery(sql);
			
			if (rs.next()) {
				member = new Member();
				member.setId(rs.getInt("id"));
				member.setUsername(rs.getString("username"));
				member.setPassword(rs.getString("password"));
				member.setRealname(rs.getString("realname"));
				member.setTel(rs.getString("tel"));
				member.setAddress(rs.getString("address"));
				member.setEmail(rs.getString("email"));
				member.setZip(rs.getString("zip"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs!=null){
					rs.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return member;
	}

	public Product getProduct(int  Id) {
		Product  product = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			sql = "select * from product where id="+Id;
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				product = new Product();
				product.setId(rs.getInt("id"));
				product.setSort(getSort(rs.getInt("sortid")));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getDouble("price"));
				product.setSaleprice(rs.getDouble("saleprice"));
				product.setDescript(clobToString(rs.getClob("descript")));
				product.setContents(clobToString(rs.getClob("contents")));
				product.setSaledate(rs.getString("saledate"));
				product.setSalecount(rs.getInt("salecount"));
				product.setImage(rs.getString("image"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return product;
	}

	
	public List getMatchProducts(String keyword) {
		String sql="select * from Product where name like '%"  +  keyword + "%' ";
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List list = null;
		Product product = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				product = new Product();
				product.setId(rs.getInt("id"));
				product.setSort(getSort(rs.getInt("sortid")));
				product.setName(rs.getString("name"));
				product.setPrice(rs.getDouble("price"));
				product.setSaleprice(rs.getDouble("saleprice"));
				product.setDescript(clobToString(rs.getClob("descript")));
				product.setContents(clobToString(rs.getClob("contents")));
				product.setSaledate(rs.getString("saledate"));
				product.setSalecount(rs.getInt("salecount"));
				product.setImage(rs.getString("image"));
				list.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list; 
	}
	
	public List getMatchOrders(String keyword) {
		String sql = "select * from Orders where tag =0 and orderno like '%"  +  keyword + "%' ";
		List list=null;
		Order order = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				order = new Order();
				order.setAddress(rs.getString("address"));
				order.setEmail(rs.getString("email"));
				order.setId(rs.getInt("id"));
				order.setMemo(clobToString(rs.getClob("memo")));
				order.setOrderno(rs.getString("orderno"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setRealname(rs.getString("realname"));
				order.setTag(rs.getInt("tag"));
				order.setTel(rs.getString("tel"));
				order.setTime(rs.getString("time"));
				order.setUserid(rs.getInt("userid"));
				order.setZip(rs.getString("zip"));
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs!=null){
					rs.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
		return list; 
	}
	
	
	public List getMatchProcessOrders(String keyword) {
		String sql = "select * from Orders where tag=1 and orderno like '%"  +  keyword + "%' ";
		List list=null;
		Order order = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				order = new Order();
				order.setAddress(rs.getString("address"));
				order.setEmail(rs.getString("email"));
				order.setId(rs.getInt("id"));
				order.setMemo(clobToString(rs.getClob("memo")));
				order.setOrderno(rs.getString("orderno"));
				order.setPayment(rs.getString("payment"));
				order.setPrice(rs.getDouble("price"));
				order.setRealname(rs.getString("realname"));
				order.setTag(rs.getInt("tag"));
				order.setTel(rs.getString("tel"));
				order.setTime(rs.getString("time"));
				order.setUserid(rs.getInt("userid"));
				order.setZip(rs.getString("zip"));
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs!=null){
					rs.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			try {
				if (conn!=null&&!conn.isClosed()){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}