bookdb.java

JSP+Tomcat开发的一个网上书店系统

/** access mysql database through JDBC Driver */
package mypack;

import java.sql.*;
import javax.naming.*;
import javax.sql.*;
import java.util.*;

public class BookDB {

	private ArrayList books;

	private String dbUrl = "jdbc:odbc:bookDb";

	private String dbUser = "sa";

	private String dbPwd = "";
	
	public int totalPage = 1;
	
	
	public BookDB() throws Exception {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	}

	public Connection getConnection() throws Exception {
		return java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);
	}

	public void closeConnection(Connection con) {
		try {
			if (con != null)
				con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void closePrepStmt(PreparedStatement prepStmt) {
		try {
			if (prepStmt != null)
				prepStmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void closeResultSet(ResultSet rs) {
		try {
			if (rs != null )
				rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getNumberOfBooks() throws Exception {
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		books = new ArrayList();

		try {
			con = getConnection();
			String selectStatement = "select * " + "from books";
			prepStmt = con.prepareStatement(selectStatement);
			rs = prepStmt.executeQuery();

			while (rs.next()) {
				BookDetails bd = new BookDetails(rs.getString(1), rs
						.getString(2), rs.getString(3), rs.getFloat(4), rs
						.getInt(5), rs.getString(6), rs.getInt(7));
				books.add(bd);
			}

		} finally {
			closeResultSet(rs);
			closePrepStmt(prepStmt);
			closeConnection(con);
		}
		return books.size();
	}

	 
	public Collection getBooks() throws Exception {
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		List books = new ArrayList();
//		String count = getValue("select count(*) from books");
		try {
			con = getConnection();
			 
			String selectStatement = "select * " + "from books";
			prepStmt = con.prepareStatement(selectStatement);
			rs = prepStmt.executeQuery();

			while (rs.next()) {

				BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs
						.getInt(5), rs.getString(6), rs.getInt(7));
				books.add(bd);
			}

		} finally {
			closeResultSet(rs);
			closePrepStmt(prepStmt);
			closeConnection(con);
		}

		Collections.sort(books);
		return books;
	}
	
	public int getTotalPage(int pageSize)  
	{
		int count;
		int allPage =1; 
		try {
			count = getNumberOfBooks();
			if(count>allPage)
				allPage = (count + pageSize-1)/pageSize;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.toString();
		}
		
		 return allPage;
		 
	}
	public Collection getBooks(int curPage,int pageSize) throws Exception {
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		List books = new ArrayList(); 
		totalPage = getTotalPage(pageSize);
		if(curPage>=totalPage)
		{
			curPage =totalPage;
		}
		
		
		try {
			con = getConnection(); 
			String selectStatement = "select  top "+ pageSize +" *  from books where id not in (select top "+ (curPage-1)*pageSize   +" id from books )";
			prepStmt = con.prepareStatement(selectStatement);
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				
				BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getInt(5), rs.getString(6), rs.getInt(7));
				books.add(bd);
			}
			
		} finally {
			closeResultSet(rs);
			closePrepStmt(prepStmt);
			closeConnection(con);
		}
 
		Collections.sort(books);
		return books;
	}

	
	 
	public BookDetails getBookDetails(String bookId) throws Exception {
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			String selectStatement = "select * " + "from books where id = ? ";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setString(1, bookId);
			rs = prepStmt.executeQuery();

			if (rs.next()) {
				BookDetails bd = new BookDetails(rs.getString(1), rs
						.getString(2), rs.getString(3), rs.getFloat(4), rs
						.getInt(5), rs.getString(6), rs.getInt(7));
				//        prepStmt.close();

				return bd;
			} else {
				return null;
			}
		} finally {
			closeResultSet(rs);
			closePrepStmt(prepStmt);
			closeConnection(con);
		}
	}

	public void buyBooks(ShoppingCart cart) throws Exception {
		Connection con = null;
		Collection items = cart.getItems();
		Iterator i = items.iterator();
		try {
			con = getConnection();
			con.setAutoCommit(false);
			while (i.hasNext()) {
				ShoppingCartItem sci = (ShoppingCartItem) i.next();
				BookDetails bd = (BookDetails) sci.getItem();
				String id = bd.getBookId();
				int quantity = sci.getQuantity();
				buyBook(id, quantity, con);
			}
			con.commit();
			con.setAutoCommit(true);

		} catch (Exception ex) {
			con.rollback();
			throw ex;
		} finally {
			closeConnection(con);
		}
	}

	public void buyBook(String bookId, int quantity, Connection con)
			throws Exception {
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		try {
			String selectStatement = "select * " + "from books where id = ? ";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setString(1, bookId);
			rs = prepStmt.executeQuery();

			if (rs.next()) {
				prepStmt.close();
				String updateStatement = "update books set saleamount = saleamount + ? where id = ?";
				prepStmt = con.prepareStatement(updateStatement);
				prepStmt.setInt(1, quantity);
				prepStmt.setString(2, bookId);
				prepStmt.executeUpdate();
				//          prepStmt.close();
			}

		} finally {
//			closeResultSet(rs);
			closePrepStmt(prepStmt);
		}
	}
public static void main(String[] args) {
	try {
		System.out.println(new BookDB().getTotalPage(2));
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public int getTotalPage() {
	return totalPage;
}

public void setTotalPage(int totalPage) {
	this.totalPage = totalPage;
}

 
}