import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 
 */

/**
 * @author Administrator
 * 
 */
public class PrepareStateMentTest {

	/**
	 * ORACLE 鏈接驅動
	 */
	public static String DRIVERNAME = "oracle.jdbc.driver.OracleDriver";

	/**
	 * 鏈接
	 */
	private Connection conn = null;

	/**
	 * 控制函數
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		PrepareStateMentTest t = new PrepareStateMentTest();
		try {
			// 鏈接創建
			t.initConn();

			// 數據插入
			t.insertTable();
			// 數據檢索
			t.selectTable();

			// 數據更新
			t.updateTable();
			// 數據檢索
			t.selectTable();

			// 數據刪除
			t.deleteTable();
			// 數據檢索
			t.selectTable();

			t.conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				t.conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			// 鏈接刪除
			try {
				t.closeConn();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 鏈接關閉
	 * 
	 * @throws SQLException
	 */
	private void closeConn() throws SQLException {
		this.conn.close();
	}

	/**
	 * 鏈接初始化
	 * 
	 * @throws Exception
	 */
	private void initConn() throws Exception {

		Class.forName(DRIVERNAME);
		String url = "jdbc:oracle:thin:@192.168.1.103:1521:training";
		this.conn = DriverManager.getConnection(url, "train", "train");
		this.conn.setAutoCommit(false);
	}

	/**
	 * 數據插入
	 * 
	 * @throws Exception
	 */
	public void insertTable() throws Exception {
		String sql = null;
		PreparedStatement sp = null;
		try {
			sql = "INSERT INTO Student(name, sex, address, birthday) "
					+ "VALUES (?, ?, ?, ?)";
			sp = conn.prepareStatement(sql);
			sp.setString(1, "lisi");
			sp.setInt(2, 0);
			sp.setString(3, "NJ");
			sp.setDate(4, Date.valueOf("2000/01/01"));

			int count = sp.executeUpdate();
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 數據刪除
	 * 
	 * @throws Exception
	 */
	public void deleteTable() throws Exception {
		String sql = null;
		PreparedStatement sp = null;
		try {
			sql = "DELETE FROM Student WHERE name = ? ";
			sp = conn.prepareStatement(sql);
			sp.setString(1, "lisi");
			int count = sp.executeUpdate();
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 數據更新
	 * 
	 * @throws Exception
	 */
	public void updateTable() throws Exception {
		String sql = null;
		PreparedStatement sp = null;
		try {
			sql = "UPDATE Student SET sex = ?, address= ?, birthday = ? "
					+ "WHERE name = ?)";
			sp = conn.prepareStatement(sql);
			sp.setInt(1, 1);
			sp.setString(2, "SH");
			sp.setDate(3, Date.valueOf("2001/02/02"));
			sp.setString(4, "lisi");
			int count = sp.executeUpdate();
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 數據檢索
	 * 
	 * @throws Exception
	 */
	public void selectTable() throws Exception {
		String sql = null;
		PreparedStatement sp = null;
		try {
			sql = "SELECT * FROM Student WHERE name=? ";
			sp = conn.prepareStatement(sql);
			sp.setString(1, "lisi");
			ResultSet rs = sp.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString("name"));
				System.out.println(rs.getInt("sex"));
				System.out.println(rs.getString("address"));
				System.out.println(rs.getDate("birthday"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

}