querybook.java

JAVA图书管理软件源码,是我师兄亲自参与编的毕业设计

package com.ivan.menu;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import com.ivan.db.Database;

public class QueryBook 
{

	public static Vector<String> getBookInfo(String bookid)
	{
		Vector<String> v = new Vector<String>();
//		mode.addColumn("图书编号");
//		mode.addColumn("书名");
//		mode.addColumn("作者");
//		mode.addColumn("出版社");
//		mode.addColumn("版次");
//		mode.addColumn("类型");
//		mode.addColumn("语种");
		String sql="select bookId, bookName,author,press,edition,type,language " +
				   "from book,bookitem where bookId='"+bookid+"' and bookitem.ISBN=book.ISBN and status='未借出'";
		Connection connection;
		ResultSet rs=null;
		connection = Database.getConnection();            //建立数据库连接
		Statement stmt=null;
		
		try 
		{
			stmt= connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next())
			{
				for(int i=1;i<=7;i++)						
					v.addElement(rs.getString(i));
			}
			return v;
		} 
		catch (SQLException e) 
		{
			
			e.printStackTrace();
		}

		finally
		{
			try 
			{
				connection.close();
			} 
			catch (SQLException e)
			{
				
				e.printStackTrace();
			}
        }

	return null;
	}
	
	
	
	public static Vector<String> getBorrowInfo(String bookid)
	{
		Vector<String> v = new Vector<String>();
//		mode.addColumn("图书编号");
//		mode.addColumn("书名");
//		mode.addColumn("作者");
//		mode.addColumn("读者编号");
//		mode.addColumn("姓名");
//		mode.addColumn("超期天数");
//		mode.addColumn("超期罚款");
		Connection connection;
		ResultSet rs=null;
		connection = Database.getConnection();            //建立数据库连接
		Statement stmt=null;
		
//		select borrowinfo.bookid,borrowinfo.bookName,borrowinfo.borrowTime,book.author,
//	       borrowinfo.readerId,reader.readerName ,MaxDay
//	from borrowinfo,book,reader, rule
//	where rule.readerClass=reader.readerClass and
//	      borrowinfo.bookName=book.bookName and
//				reader.readerId=(
//						select readerId from borrowinfo where bookId='03001')
//	       and bookid='03001';
		
		
		String sql="select borrowinfo.bookid,borrowinfo.bookName,book.author,borrowinfo.borrowTime," +
				    "borrowinfo.readerId,reader.readerName, MaxDay ,sysdate(),rule.eachDayMoney " +
				    "from borrowinfo,book,reader, rule " +
					"where rule.readerClass=reader.readerClass and " +
					"borrowinfo.bookName=book.bookName and " +
					"reader.readerId=(" +
					"select readerId from borrowinfo where bookId='"+bookid+"') " +
					" and bookid='"+bookid+"' and returnTime is NULL";
		
		
		try 
		{
			stmt= connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next()==false)
				return null;
			else
				
			{
				v.add(rs.getString("bookid"));
				v.add(rs.getString("bookName"));
				v.add(rs.getString("author"));				
				v.add(rs.getString("borrowTime"));
				v.add(rs.getString("readerId"));
				v.add(rs.getString("readerName"));
				
				int byear =Integer.parseInt( rs.getString("borrowTime").substring(0, 4));
				int bmonth =Integer.parseInt( rs.getString("borrowTime").substring(5, 7));
				int bday =Integer.parseInt( rs.getString("borrowTime").substring(8, 10));
				
				
				int ryear =Integer.parseInt( rs.getString("sysdate()").substring(0, 4));
				int rmonth =Integer.parseInt( rs.getString("sysdate()").substring(5, 7));
				int rday =Integer.parseInt( rs.getString("sysdate()").substring(8, 10));
				
				int area=(ryear-byear)*12+(rmonth-bmonth)*31+(rday-bday);
				int maxday=Integer.parseInt( rs.getString("MaxDay"));
				if(area>maxday)
				{
					v.add(String.valueOf((area-maxday)));
					v.add(String.valueOf((area-maxday)*rs.getDouble("eachDayMoney")));
				}
				else
				{
					v.add("0");
					v.add("0");
				}
				
				return v;
			}
			
			
		} 
		catch (SQLException e) 
		{
			
			e.printStackTrace();
		}

		finally
		{
			try 
			{
				connection.close();
			} 
			catch (SQLException e)
			{
				
				e.printStackTrace();
			}
        }

	return null;
	}
}