/**
 * 通過(guò)這個(gè)程序，向讀者展示比較完整的JDBC數(shù)據(jù)庫(kù)操作的順序，
 * 在這個(gè)例子中主要展示通過(guò)屬性文件加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序、表結(jié)構(gòu)的創(chuàng)建與記錄的插入。
 */

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 CreatTableTest
{
	private Connection con;
	private String url;//數(shù)據(jù)庫(kù)URL
	private String userName;//登錄數(shù)據(jù)庫(kù)用戶名
	private String password;//用戶密碼
	
	public static void main(String[] args)
	{		
		CreatTableTest test = new CreatTableTest();
		test.getProperty();		
		Connection con = test.getConnection();
		test.createTable(con);
		test.getStudent(con);
		
	}
	
	/**
	 * 在數(shù)據(jù)庫(kù)studentmanager中創(chuàng)建一個(gè)表student，并向表中插入一條記錄
	 */
	public void createTable(Connection con)
	{
		try
		{
			Statement st = con.createStatement();
			String sql = "CREATE TABLE SUTDENT(姓名 varchar(12) NOT NULL," +
						 "學(xué)號(hào) varchar(10) NOT NULL," +
						 "出生日期 datetime NOT NULL,專業(yè) varchar(10) NULL," +
						 "籍貫 varchar(30) NULL)";
			System.out.println("輸出的SQL語(yǔ)句是：");
			System.out.println(sql);
			st.execute(sql);
			
			sql = "insert into student values('王成','20021022','1977-6-10', '英語(yǔ)','河北')";
			st.executeUpdate(sql);
			st.close();
				
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * 從表中查詢出所有記錄
	 */
	public void getStudent(Connection con)
	{
		try
		{
			Statement st = con.createStatement();
			String sql = "select * from student";
			ResultSet rs = st.executeQuery(sql);
			
			while(rs.next())
			{
				String name =rs.getString("姓名");
				String number = rs.getString("學(xué)號(hào)");
				Date date = rs.getDate("出生日期");
				String spe = rs.getString("專業(yè)");
				String address = rs.getString("籍貫");
				
				System.out.println( "\n姓名:" + name + "\t學(xué)號(hào):" + number + 
									"\t出生日期:" + date + "\t專業(yè):" +
									spe + "\t籍貫:" + address ); 				
			}
			
			st.close();
			con.close();		
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 返回到數(shù)據(jù)庫(kù)的一個(gè)連接，在一個(gè)系統(tǒng)或類中，如果經(jīng)常進(jìn)行數(shù)據(jù)庫(kù)的相關(guān)操作
	 * 會(huì)把建立數(shù)據(jù)庫(kù)的連接作為一個(gè)單獨(dú)的方法。
	 */
	public Connection getConnection()
	{
		try
		{
			con = DriverManager.getConnection(url, userName, password);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
				
		return con;
	}
	
	/**
	 * 讀取屬性配置文件
	 */
	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();
		}
	}
}