y.java

教材订购系统

package program;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Y {

	public Y(String bno,String bcount) throws NumberFormatException, ClassNotFoundException, SQLException
	{
		work(bno,Integer.parseInt(bcount));
	}
	public void work(String bno,int bcount) throws ClassNotFoundException, SQLException
	{
		String DBDriver="sun.jdbc.odbc.JdbcOdbcDriver";
		String connectionStr="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D:/Java_Work/BookManager/program/db/bookmanage.mdb";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int kucun,yigong,queshu,sheng;
		
		Getdate now =new Getdate();
		Class.forName(DBDriver);	 //加载驱动器
		con=DriverManager.getConnection(connectionStr,"",""); //连接数据库
		stmt=con.createStatement();   //创建Statement对象
		
		stmt.executeUpdate("INSERT INTO 入库(书号,数量,日期) VALUES " +
						 "('"+bno+"',"+bcount+",'"+now.Getdate()+"')");
//		添加出库表
		
		rs=stmt.executeQuery("select * from 库存 where 书号='"+bno+"'");
		if(rs.next())//库存中有该书
		{
			kucun=rs.getInt("数量");
			yigong=kucun+bcount;
			stmt.executeUpdate("update 库存 set 数量="+yigong+" where 书号='"+bno+"'");			
		}
		else//库存中没有该书
		{
			stmt.executeUpdate("insert into 库存 values( '"+bno+"',"+bcount+")");//在库存中添入该书记录
		}	
//		添入库存		
		
		rs=stmt.executeQuery("select * from 缺书 where 书号='"+bno+"'");
		if(rs.next())
		{
			queshu=rs.getInt("数量");
			if(bcount>=queshu)
			{
				stmt.executeUpdate("delete from 缺书 where 书号='"+bno+"'");
			}
			else
			{
				sheng=queshu-bcount;
				stmt.executeUpdate("update 缺书 set 数量="+sheng+" where 书号='"+bno+"'");
			}
		}
//		修改缺书
	}
}