package studentinfo;

import java.sql.*;
/**
 * <p>
 * Title: StudentINFO
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2006.12.20
 * </p>
 * <p>
 * Company:buaa.cs
 * </p>
 * 
 * @author 朱亮 34060222
 * @version 1.0
 */

public class DBOperator {
	private Connection con;

	public DBOperator() {
		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			con = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/test", "root", "551061123");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}



	// 添加一個表
	public void addTable() {
		System.out.println("add table account");
		try {
			Statement stmt = con.createStatement();
			stmt.execute("create table table1(id varchar(20), "
					+ "name varchar(50),major varchar(20),"
					+ " class varchar(20),primary key id)");
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 在表中添加數據
	public void addRecord(String id, String name, String major, String cla) {
		System.out.println("add record");
		try {
			Statement stmt = con.createStatement();
			for (int i = 0; i < 10; i++) {
				stmt.execute("insert into account values('" + id + "','" + name
						+ "','" + major + "','" + cla + "'");
			}
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 打印表中內容
	public String printRecord(String id) {
		System.out.println("print record");
		String result = "";
		try {
			Statement stmt = con.createStatement();
			ResultSet rst = stmt.executeQuery("select * from account");
			while (rst.next()) {
				if (id.equals(rst.getString("id"))) {
					System.out.println("id: " + rst.getString("id"));
					System.out.println("name: " + rst.getString("name"));
					System.out.println("sex: " + rst.getString("sex"));
					result += "id: " + rst.getString("id") + "name: "
							+ rst.getString("name") + "sex: "
							+ rst.getString("sex");
				}
			}
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	// 刪除表中的記錄
	public  void deleteRecord(String id) {
		System.out.println("delete record");
		try {
			Statement stmt = con.createStatement();
			stmt.execute("delete from account where id='" + id + "'");
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void closeConnection()throws Exception{
		this.con.close();
	}
	public static void main(String[] args) {
		DBOperator f = new DBOperator();
		f.addTable();
		f.addRecord("1", "1", "1", "1");
		f.printRecord("1");
		f.deleteRecord("1");
	}
}