/**
 * 通過這個程序，向讀者展示比較完整的JDBC數據庫操作的順序，
 * 在這個例子中主要展示數據庫事務的處理與控制
 */

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet; 
import java.sql.DriverManager;
import java.sql.Date;
import java.util.Properties;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileNotFoundException;

public class DoUpdateTest
{
	
	private String url;//數據庫URL
	private String userName;//登錄數據庫用戶名
	private String password;//用戶密碼
	
	public static void main(String[] args)
	{		
		DoUpdateTest test = new DoUpdateTest();
		test.getProperty();		
		test.doUpdate();		
	}
	
	public void doUpdate()
	{
		try
		{
			Connection con = DriverManager.getConnection(url, userName, password);
			con.setAutoCommit(false);
			Statement st = con.createStatement();
			System.out.println("查詢插入前的記錄：");
			this.getStudent(st);
			String sql = "insert into student values('張麗','20021023','1978-6-10', '英語','山東')";
			st.executeUpdate(sql);
			System.out.println("\n查詢插入后的記錄：");
			this.getStudent(st);
			System.out.println("\n現在數據庫進行回滾后，查詢數據庫結果：");
			con.rollback();
			this.getStudent(st);
			con.commit();
			System.out.println("\n再插入剛才的記錄，事務提交后數據庫查詢的結果：");
			sql = "insert into student values('張麗','20021023','1978-6-10', '英語','山東')";
			st.executeUpdate(sql);
			con.commit();
			this.getStudent(st);
			System.out.println("\n現在數據再回滾，查詢數據訓結果：");
			con.rollback();
			this.getStudent(st);
			con.setAutoCommit(true);
					
			st.close();
			con.close();
				
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * 從表中查詢出所有記錄
	 */
	public void getStudent(Statement st)
	{
		try
		{
			String sql = "select * from student";
			ResultSet rs = st.executeQuery(sql);
			
			while(rs.next())
			{
				String name =rs.getString("姓名");
				String number = rs.getString("學號");
				Date date = rs.getDate("出生日期");
				String spe = rs.getString("專業");
				String address = rs.getString("籍貫");
				
				System.out.println( "\n姓名:" + name + "\t學號:" + number + 
									"\t出生日期:" + date + "\t專業:" +
									spe + "\t籍貫:" + address ); 				
			}
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 讀取屬性配置文件
	 */
	public void getProperty()
	{
		Properties prop = new Properties();
		try
		{
			FileInputStream in = new FileInputStream("Driver.properties");
			prop.load(in);
	
			String driver = prop.getProperty("drivers");
			if(driver != null)
				System.setProperty("jdbc.drivers", driver);
			url = prop.getProperty("url");
			userName = prop.getProperty("user");
			password = prop.getProperty("password");			
		}
		catch(FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}
}