dboperate.java

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

package 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 hit.bean.*;

public class DBOperate {
	public DBOperate(){}
	
	public String formatDate (String sdate) {
		sdate = sdate.substring(0,10);
		return sdate;
	}
	
	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 Member getMember(String userId){
		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 username='"+userId+"'";
			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.setSortid(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();
		} 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 product;
	}
	
	public List getTopProducts(int type){
		Product product = null;
		List list = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		int disNum;//需要几行
		
		try {
			stmt = conn.createStatement();
			if (type == 1) {
				disNum = 5;
				sql = "select * from Product order by saledate desc limit 0,"+disNum;
			} else {
				disNum = 11;
				sql = "select * from Product order by salecount desc limit 0,"+disNum;
			}
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				product = new Product();
				product.setId(rs.getInt("id"));
				product.setSortid(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();
		} 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 getMatchProducts(int sortId,String keyword){
		Product product = null;
		List list = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			if (sortId == -1) {
				sql = "select * from Product where name like '%"  +  keyword + "%' ";
			} else {
				sql = "select * from Product where sortid='" + sortId +"'";
			}
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				product = new Product();
				product.setId(rs.getInt("id"));
				product.setSortid(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();
		} 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 getSorts(){
		Sort sort = null;
		List list = null;
		Connection conn = DBConn.getConnection();
		Statement stmt = null;
		String sql = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			sql = "select * from Sort";
			rs = stmt.executeQuery(sql);
			list = new ArrayList();
			while (rs.next()) {
				sort = new Sort();
				sort.setId(rs.getInt("id"));
				sort.setName(rs.getString("name"));
				list.add(sort);
			}
		} 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();
			}