borrowedbookdb.java

本系统完成简单的图书管理系统

/***********************************************************************
*                          湖南大学图书管理系统
* @author Administrator                                                         
* @since 2008.06                                                  
* @version 1.0                                                           															  
* @category 已借图书数据库操作
*************************************************************************/

package library.DBaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Vector;

import library.DBBase.JDBC;

public class BorrowedBookDB {

	public BorrowedBookDB() {
		super();
	}

	public Vector getBorrowedBook(String userName) {
		Vector v = new Vector();
		String userid = null;
		Statement stmt = null;
		JDBC jdbc = new JDBC();
		Connection con = jdbc.getConnection();

		PreparedStatement ps;
		try {

			String sql = "select USERID from user where USERNAME = '"
					+ userName + "'";
			// stmt = con.createStatement();
			ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				userid = String.valueOf(rs.getObject(1));
				System.out.println("userid:" + userid);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		String sql1 = "select BOOKCLASSID, BORROWDATE,RETURNDATE from borrow where USERID=" + userid;
		try {
			PreparedStatement ps1 = con.prepareStatement(sql1);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next()) {
				ResultSetMetaData rsmd1 = rs1.getMetaData();
				
				String bookclassid = null;
				bookclassid = rs1.getString(1);
				String sql2 = "select BOOKCLASSID,TITLE,AUTHOR,CATEGORY,PRESS,PRICE,PAGINATION,ISBN,CHECTDATE,COUNT,REMARK from BOOK where BOOKCLASSID = "
						+ bookclassid + "";
				// System.out.println(sql2);

				try {
					PreparedStatement ps2 = con.prepareStatement(sql2);
					ResultSet rs2 = ps2.executeQuery();
					while (rs2.next()) {
						Hashtable hs2 = new Hashtable();
						ResultSetMetaData rsmd2 = rs2.getMetaData();
						for (int i = 0; i < rsmd1.getColumnCount(); i++) {
							hs2.put(rsmd1.getColumnName(i + 1), rs1
									.getObject(i + 1) == null ? "" : rs1
									.getObject(i + 1));
						}
						for (int i = 0; i < rsmd2.getColumnCount(); i++) {
							hs2.put(rsmd2.getColumnName(i + 1), rs2
									.getObject(i + 1) == null ? "" : rs2
									.getObject(i + 1));
						}
						v.add(hs2);
					}
				} catch (SQLException e) {
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}

			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

		jdbc.closeConnection(con);
		return v;

	}

	/*public static void main(String[] args) {
		BorrowedBookDB bbd = new BorrowedBookDB();

		Vector vv = bbd.getBorrowedBook("caohao");
		for (int i = 0; i < vv.size(); i++) {
			System.out.println(((Hashtable) vv.get(i)).get("BORROWDATE"));
		}
	}*/

}