yn.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 YN {
	
	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;
	ResultSet rs1 = null;
	int kucun;
 	int cha;
	
	public YN(String bclass,String bno,String bcount) throws NumberFormatException, ClassNotFoundException, SQLException
	{
		work(bclass,bno,Integer.parseInt(bcount));
	}
	public void work(String bclass,String bno,int bcount) throws ClassNotFoundException, SQLException
	{
		
		
		Getdate now =new Getdate();
		Class.forName(DBDriver);	 //加载驱动器
		con=DriverManager.getConnection(connectionStr,"",""); //连接数据库
		stmt=con.createStatement();   //创建Statement对象
	
		rs=stmt.executeQuery("select * from 库存 where 书号='"+bno+"'");

		if(rs.next())//库存中还有但是不够
		{
			kucun=rs.getInt("数量");
			cha=bcount-kucun;
			
			stmt.executeUpdate("INSERT INTO 出库(书号,数量,日期,班号) VALUES " +
					"('"+bno+"',"+kucun+",'"+now.Getdate()+"',"+"'"+bclass+"')");
//			添加出库表
			
			stmt.executeUpdate("INSERT INTO 订_inf(书号,班号,数量) VALUES " +
					 		 "('"+bno+"','"+bclass+"',"+cha+")");
//          添加 订_inf表
			
			rs=stmt.executeQuery("select * from 缺书 where 书号='"+bno+"'");
			if(rs.next())//缺书表中本来已经有了该书记录
			{
				int benlai = rs.getInt("数量");
				int yigong = benlai+cha;
				stmt.executeUpdate("update  缺书 set 数量="+yigong+" where 书号='"+bno+"'");//数量相加
			}	
			else//缺书表本来没有该书的记录
			{
				stmt.executeUpdate("INSERT INTO 缺书(书号,数量) VALUES " + //插入缺书信息
								 "('"+bno+"',"+cha+")");
			}
//          添加缺书表
			
			stmt.executeUpdate("delete from 库存 where 书号='"+bno+"'");
//			从库存记录中删除			
		}
		else//该书没有库存(库存为0)
		{
			stmt.executeUpdate("INSERT INTO 订_inf(书号,班号,数量) VALUES " +
			 		 		 "('"+bno+"','"+bclass+"',"+bcount+")");
// 			添加 订_inf表
	
			rs=stmt.executeQuery("select * from 缺书 where 书号='"+bno+"'");
			if(rs.next())//缺书表中本来已经有了该书记录
			{
				System.out.println("缺书表中本来已经有了该书记录,修改数量即可");
				int benlai = rs.getInt("数量");
				int yigong = benlai+bcount;
				System.out.println("benlai="+benlai);
				stmt.executeUpdate("update  缺书 set 数量="+yigong+" where 书号='"+bno+"'");//
			}	
			else//缺书表本来没有该书的记录
			{
				stmt.executeUpdate("INSERT INTO 缺书(书号,数量) VALUES " +
								 "('"+bno+"',"+bcount+")");
			}
//          添加缺书表
		}	
	}
	
	
	
	
	
}