package Bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

public class BuyBookQuery {

	public ArrayList buybookquery()
	{
		String SQLString = "";
		
		java.sql.ResultSet sqlRst = null;
		java.sql.Statement Stmt = null;
//		Users user = new Users();                 //--------這句話寫在是錯誤的-----
		ArrayList<BookBean> arraylist = new ArrayList<BookBean>();
		
		Connection conn=new DBConn().getConn();
		try 
		{
			SQLString = "select * from buybook,book,users where buybook.username = users.username and buybook.bookID = book.bookID";	
			PreparedStatement ps=conn.prepareStatement(SQLString);
			
//			ps.setString(1,username);
			sqlRst = ps.executeQuery();
//			Stmt = conn.createStatement(); //語句對象 
//			sqlRst=Stmt.executeQuery ( SQLString); ; //結果集對象 
			
			while(sqlRst.next())
			{
				Bean.BookBean book = new Bean.BookBean();   //---這句話必須寫在這------
				
				book.setBookID(sqlRst.getString("bookID"));
				book.setBookName(sqlRst.getString("bookName"));
				book.setAuthor(sqlRst.getString("author"));
				book.setPrice(sqlRst.getDouble("price"));
				book.setDiscount(sqlRst.getDouble("discount"));
				book.setPress(sqlRst.getString("press"));
				book.setSum(sqlRst.getInt("count"));
				book.setPicroot(sqlRst.getString("picroot"));
				
				arraylist.add(book);   //----把BookBean放入ArrayList中
			}
//				Stmt.close ();
				ps.close();
				conn.close();
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		
		return arraylist;
	}
}