import java.sql.*;
import javax.swing.*;
public class Book
{
	static Connection con,add,xg,xg2,shanchu,zhengli;
	static Statement sql,addsql,xgsql,xgsql2,sc,zl;
	static ResultSet rs,qs,xs,xs2,ss,rzl;
	static Data data;
	boolean boo;
	public static  String text;
	Book() throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		con=DriverManager.getConnection("jdbc:odbc:Book","","");
		sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	}

	
	public static Data search(Data data) throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		con=DriverManager.getConnection("jdbc:odbc:Book","","");   //打開數據庫
		sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		//this.data=data;    //傳遞變量
		rs=sql.executeQuery("SELECT * FROM book1 WHERE 書名 LIKE '%"+data.search+"%'");  //查找圖書，按書名
		rs.absolute(-1);
		data.chushihua(rs.getRow()); //將當前行的內容，傳遞給data
		if(false)
		{
			return data;
		}
		rs.beforeFirst();  //移動指針到第一行
		int i=0;
		while(rs.next()) //將指針下移一行，讀出數據
		{
			data.number[i]=rs.getString("編號");
			data.Bookname[i]=rs.getString("書名");
			
			data.date[i]=rs.getString("出版日期");
			data.author[i]=rs.getString("作者");
			data.company[i]=rs.getString("出版商");
			data.ex[i]=rs.getString("狀態");
			data.beizhu[i]=rs.getString("備注");
			i++;
		}
		con.close();  //關閉數據庫
		rs=null;
		return data;   
		
	}
	
	public static Data xiugai(Data data) throws Exception
	{ //修改數據
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		xg=DriverManager.getConnection("jdbc:odbc:Book","","");   //打開數據庫
		xgsql=xg.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		xs=xgsql.executeQuery("SELECT * FROM book1 WHERE 編號 LIKE '"+Long.valueOf(data.search)+"'");   //獲得修改的編號
		
		xs.next();    //保存修改后的內容
		data.chushihua(1);
		data.number[0]=xs.getString("編號");
		data.Bookname[0]=xs.getString("書名");
		data.date[0]=xs.getString("出版日期");
		data.author[0]=xs.getString("作者");
		data.company[0]=xs.getString("出版商");
		data.ex[0]=xs.getString("狀態");
		data.beizhu[0]=xs.getString("備注");
		xg.close();
		xs=null;
		return data;
	}
	
	public static void xiugai2(String num,String name,String author,String beizhu,String date,String company) throws Exception
	{//修改數據
	   // System.out.println(data.Bookname[0]);
	   String ex="在庫";
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		xg2=DriverManager.getConnection("jdbc:odbc:Book","","");
		xgsql2=xg2.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		xs2=xgsql2.executeQuery("SELECT * FROM book1 WHERE 編號 LIKE '"+Long.valueOf(num)+"'");
		String bf="UPDATE book1 SET 書名 = '"+name+"', 出版日期 = '"+date+"',作者 = '"+author+"',狀態 = '"+ex+"',出版商 = '"+company+"',備注 = '"+beizhu+"' WHERE 編號 LIKE '"+Long.valueOf(num)+"'";
		xgsql2.execute(bf);
		xg2.close();
		
	}
	
	public static void shanchu(Data data) throws Exception
	{//安編號刪除數據
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		shanchu=DriverManager.getConnection("jdbc:odbc:Book","","");  //打開數據庫
		sc=shanchu.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		//System.out.println(Long.valueOf(data.search));
		sc.executeUpdate("DELETE FROM book1 WHERE 編號="+Long.valueOf(data.search)+"" );
	}
	
	public static void jiechu(Data data) throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		xg2=DriverManager.getConnection("jdbc:odbc:Book","","");//打開數據庫
		xgsql2=xg2.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		String bf="UPDATE book1 SET 狀態 = '借出' WHERE 編號 LIKE '"+Long.valueOf(data.search)+"'";  //更新狀態 
		xgsql2.execute(bf);
		xg2.close();
		JOptionPane.showMessageDialog(Tuxing.tx,"操作完成。");
	}
	
	public static void guihuan(Data data) throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		xg2=DriverManager.getConnection("jdbc:odbc:Book","","");
		xgsql2=xg2.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		String bf="UPDATE book1 SET 狀態 = '在庫' WHERE 編號 LIKE '"+Long.valueOf(data.search)+"'";
		xgsql2.execute(bf);
		xg2.close();
		JOptionPane.showMessageDialog(Tuxing.tx,"操作完成。");
	}
	
	public static void beizhu(String num) throws Exception
	{//顯示數據庫中的備注
	    //System.out.println(num+"備注");
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		zhengli=DriverManager.getConnection("jdbc:odbc:Book","","");//打開數據庫
		zl=zhengli.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		rzl=zl.executeQuery("SELECT * FROM book1 WHERE 編號 LIKE '"+Long.valueOf(num)+"'");   //整理圖書	
	    rzl.next();
	    //System.out.println("備注"+rzl.getString("備注"));
	    text=rzl.getString("備注");
		rzl=null;
		zhengli.close();
		
	}
	
	public  static void adddata(String bname,String baouther,String bdate,String bcompany,String bbeizhu) throws Exception
	{//把數據添加到數據庫中
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");      //打開數據庫
		add=DriverManager.getConnection("jdbc:odbc:Book","","");
		addsql=add.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);//創建一個 Statement 對象，該對象將生成具有給定類型和并發性的 ResultSet 對象
		qs=addsql.executeQuery("SELECT * FROM book1");    //查詢數據庫中所有數據，
		long a=0;
		qs.absolute(-1);  //默認指針處于-1行  
		if(qs.getRow()!=0) //當前行編號不為0
		{
			a=Long.parseLong(qs.getString("編號")); //檢索編號列得值，并轉化為long型值 
			a++;
		}
		else 
		{
			a++;
		}
		String bf="INSERT INTO book1 VALUES ("+a+",'"+bname+"','"+bdate+"','"+baouther+"','在庫','"+bcompany+"','"+bbeizhu+"')";
		addsql.executeUpdate(bf);    //將數據添加到數據庫中去
		add.close();           //關閉數據庫
	}
}