// Chapter 05, sample 61
// JDBC_ODBC_Bridge.java
// Connect database with JDBC-ODBC bridge
package javadatabase;
import java.sql.*;
public class DatabaseBasicOperation
{
	private Statement sta;			//用于數據庫操作的Statement
	public static void main(String args[])
	{
		try
		{
			JDBCODBCBridge bridge=new JDBCODBCBridge();
			DatabaseBasicOperation operator=new DatabaseBasicOperation();
			ResultSet rs;				//結果集
			String sqlCommand;		//用于操作的SQL命令
			bridge.setURL("jdbc:odbc:sample");
			bridge.setUser("");
			bridge.setPassword("");
			Connection con=bridge.getConnection();
			operator.setStatement(con);		//設置Satement
			//查詢操作
			sqlCommand="SELECT * FROM STUDENTS";
			System.out.println("\n#########查詢操作#########");
			System.out.println("輸入SQL命令"+sqlCommand+"\n");
			rs=operator.executeQuery(sqlCommand);
			rs.next();				
			for(int i=0;i<rs.getRow();i++)
			{
				System.out.println(rs.getString("NAME"));
				
				rs.next();
			}
			//修改操作
			sqlCommand=
				"UPDATE STUDENTS SET AGE=22 WHERE NAME=\'巧玲\'";
			System.out.println("\n#########修改操作#########");
			System.out.println("輸入SQL命令"+sqlCommand+"\n");
			operator.executeUpdate(sqlCommand);
			sqlCommand="SELECT * FROM STUDENTS WHERE NAME=\'巧玲\'";
			rs=operator.executeQuery(sqlCommand);
			rs.next();
			for(int i=0;i<rs.getRow();i++)
			{
				System.out.println(rs.getString("NAME")+
					"年齡修改為"+rs.getInt("AGE"));
				rs.next();
			}
			//添加操作
			sqlCommand="INSERT INTO STUDENTS(ID,NAME,AGE)"+
				"VALUES(\'5\',\'小貓\',1)";
			System.out.println("\n#########添加操作#########");
			System.out.println("輸入SQL命令"+sqlCommand+"\n");
			operator.executeInsert(sqlCommand);
			sqlCommand="SELECT * FROM STUDENTS WHERE ID=\'5\'";
			rs=operator.executeQuery(sqlCommand);
			rs.next();
			for(int i=0;i<rs.getRow();i++)
			{
				System.out.println("ID為"+rs.getString("ID")+
					"處已添加"+rs.getString("NAME"));
				rs.next();
			}
			//刪除操作
			sqlCommand="DELETE FROM STUDENTS WHERE ID=\'5\'";
			System.out.println("\n#########刪除操作#########");
			System.out.println("輸入SQL命令"+sqlCommand+"\n");
			operator.executeDelete(sqlCommand);
			sqlCommand="SELECT * FROM STUDENTS WHERE ID=\'5\'";
			rs=operator.executeQuery(sqlCommand);
			rs.next();
			if(!rs.next())
			{
				System.out.println("不存在ID為5的數據，此數據已被刪除");
			}
			//關閉所有連接,釋放資源
			rs.close();
			operator.closeStatement();
			con.close();
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
	public void setStatement(Connection con)
	{
		try
		{
			this.sta=con.createStatement();		//Statement中有關數據庫查詢的函數
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
	public ResultSet executeQuery(String sqlCommand)
	{
		try
		{
			//Statement中有關修改數據庫的函數
			return sta.executeQuery(sqlCommand); 
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
		return null;
	}
	public void executeUpdate(String sqlCommand)
	{
		try
		{
			sta.executeUpdate(sqlCommand);
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
	public void executeInsert(String sqlCommand)
	{
		try
		{
			sta.executeUpdate(sqlCommand);
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	} 
	public void executeDelete(String sqlCommand)
	{
		try
		{
			sta.executeUpdate(sqlCommand);
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}  
	public void closeStatement()
	{
		try
		{
			sta.close();
			
		}
		catch(Exception e)
		{
			System.out.println(e.toString());
		}
	}
}