sellbookdao.java

网上书店 源代码和数据库网上书店 源代码和数据库

package com.shop.model.dao;

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 com.shop.model.utils.DataBaseConn;
import com.shop.vo.BookInfoVo;
import com.shop.vo.MultipSellVO;

public class SellBookDAO {

	public MultipSellVO SellBookList(String iisbn) {
		Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		MultipSellVO mulsell=new MultipSellVO();
		String select = "select * from BookInfo bf,SellBook sf,UserInfo uf where bf.ISBN=sf.ISBN and uf.USERID=sf.USERID and bf.ISBN='"+iisbn+"'";
		List selllist=new ArrayList();
		System.out.println(select);
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		try {
			st = conn.createStatement();
			rst=st.executeQuery(select);
			while(rst.next()){
				mulsell.setIsbn(rst.getString("ISBN"));
				mulsell.setBookname(rst.getString("BOOKNUM"));	
                mulsell.setPrice(rst.getString("PRICE"));
                mulsell.setSellprice(rst.getString("SELLPRICE"));
                mulsell.setInnumber(rst.getInt("INBOOKNUMBER"));
                mulsell.setRemainNum(rst.getInt("REMAINNUMBER"));
                mulsell.setId(rst.getString("ID"));
                mulsell.setBookNum(rst.getInt("BOOKNUMBER"));
                mulsell.setSumMoney(rst.getString("SUMMONEY"));
                mulsell.setAdddate(rst.getDate("ADDDATE"));
                mulsell.setRemark(rst.getString("REMARK"));
                mulsell.setPosted(rst.getString("POSTED"));
                mulsell.setUserid(rst.getString("USERNAME"));
                mulsell.setPostcode(rst.getString("POSTALCODE"));
                mulsell.setAddress(rst.getString("ADDRESS"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rst!=null){
				try {
					rst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return mulsell;
		
	}
	public List<BookInfoVo> findBookVO(String sql) {
		Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		String select = "select * from SellBook sf,BookInfo bf,UserInfo uf where sf.ISBN=bf.ISBN and uf.USERID=sf.USERID and ";
		List<BookInfoVo> booklist=new ArrayList<BookInfoVo>();
		
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		try {
			st = conn.createStatement();
			rst=st.executeQuery(select+sql);
			System.out.println(select+sql);
			while(rst.next()){
				BookInfoVo book=new BookInfoVo();
				   book.setIsbn(rst.getString(9));
					book.setBookNum(rst.getString(10));
					book.setAuthor(rst.getString(11));
					book.setPrintAddr(rst.getString(12));
					book.setPrice(rst.getString("PRICE"));
					book.setPrintNum(rst.getInt(15));
					book.setPrintDate(rst.getDate(16));
					book.setRemainNum(rst.getInt(18));
				booklist.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rst!=null){
				try {
					rst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return booklist;
		
	}
	
	public void updatePosted(String[] updateStr){
		StringBuffer sbuf=new StringBuffer();
		sbuf.append("update SellBook set POSTED='YES' where ISBN in ( '0'");
		for(int i=0;i<updateStr.length;i++){
			sbuf.append(",'").append(updateStr[i]).append("'");
		}
		sbuf.append(")");
		System.out.println(sbuf.toString());
		Connection conn = null;
		Statement st = null;
		String update=sbuf.toString();
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		try {
			st = conn.createStatement();
			st.executeUpdate(update);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}