borrowdb.java

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

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

package library.DBaccess;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Calendar;
import java.math.*;
import java.sql.*;
import java.lang.*;

import library.DBBase.JDBC;

public class BorrowDB {

	public BorrowDB() {
		// TODO Auto-generated constructor stub
		super();
	}

	public void BorrowBook(String isbn, String username) {
		String userid = null;
		String bookclassid = null;
		int count = 0;

		JDBC jdbc = new JDBC();
		Connection con = jdbc.getConnection();

		String sql = "select USERID from user where USERNAME = '" + username
				+ "'";
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String temp=rs.getString(1);
				userid = temp;
				System.out.println(userid);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		String sql1 = "select BOOKCLASSID,COUNT from book where ISBN = '"
				+ isbn + "'";
		try {
			PreparedStatement ps1 = con.prepareStatement(sql1);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next()) {
				bookclassid = String.valueOf((rs1.getObject(1)));
				System.out.println(bookclassid);
				String count1 = String.valueOf((rs1.getObject(2)));
				count = Integer.parseInt(count1);
				System.out.println("count:"+count);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}catch(NumberFormatException ex)
		{
			ex.printStackTrace();
		}

		java.text.SimpleDateFormat date = new java.text.SimpleDateFormat(
				"yyyy-MM-dd");
		String currentDate = date.format(new Date(System.currentTimeMillis()));
		String returnDate = getNMonthAfterCurrentDay(1);

		String sql2 = "update book set COUNT = " + (count - 1)
				+ " where ISBN = '" + isbn + "'";
		String sql3 = "insert into borrow(USERID,BOOKCLASSID,BORROWDATE,RETURNDATE) values("
				+ userid
				+ ","
				+ bookclassid
				+ ",'"
				+ currentDate
				+ "','"
				+ returnDate + "')";
		try {
			PreparedStatement ps2 = con.prepareStatement(sql2);
			PreparedStatement ps3 = con.prepareStatement(sql3);
			ps2.executeUpdate(sql2);
			ps3.executeUpdate(sql3);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		jdbc.closeConnection(con);

	}

	// 取得系统当前时间后n个月的相对应的一天
	public String getNMonthAfterCurrentDay(int n) {
		Calendar c = Calendar.getInstance();
		c.add(c.MONTH, n);
		return "" + c.get(c.YEAR) + "-" + (c.get(c.MONTH) + 1) + "-"
				+ c.get(c.DATE);

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		BorrowDB bdb = new BorrowDB();
		bdb.BorrowBook("9787121034985", "caohao");

	}

}