package cn.ac.cintcm.spider.cmi;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import cn.ac.cintcm.spider.MSDb;

public class MSCmiReference {  // 創建表,并插入數據
	private MSDb db = null;
	
	public MSCmiReference(String file) {
		db = new MSDb(file);
	}
	
	public void createTable() throws ClassNotFoundException, SQLException {
		Connection conn = db.getConnection();
		Statement st = conn.createStatement();
		StringBuilder sql = new StringBuilder("CREATE TABLE cmireference");
		sql.append("(id AUTOINCREMENT, referencetitle TEXT(255),authors TEXT(50),");
		sql.append("enterprise TEXT(255),literature TEXT(255),referencenum TEXT(50),");
		sql.append("title TEXT(255), Constraint id Primary Key (id))");
		String sqlStr = sql.toString();
		//System.out.println(sqlStr);
		st.execute(sqlStr);
		st.close();
		conn.close();
	}
	
	public void dropTable() {
		Connection conn;
		try {
			conn = db.getConnection();
			Statement st = conn.createStatement();
			StringBuilder sql = new StringBuilder("drop TABLE cmireference");
			st.execute(sql.toString());
			st.close();
			conn.close();
		} catch (ClassNotFoundException e) {
		} catch (SQLException e) {
		}
	}
	
	public void addRecords(List<CmiReference> references) {
		if (references != null) {
			Connection conn;
			try {
				String sql = "insert into cmireference values(?,?,?,?,?,?,?)";
				conn = db.getConnection();
				int rowNum = getId(conn);
				PreparedStatement stmt = conn.prepareStatement(sql);
				for (CmiReference reference : references) {
					stmt.setInt(1, ++rowNum);
					stmt.setString(2, reference.getReferenceTitle());
					stmt.setString(3, reference.getAuthors());
					stmt.setString(4, reference.getEnterprise());
					stmt.setString(5, reference.getLiterature());
					stmt.setString(6, reference.getReferenceNum());
					stmt.setString(7, reference.getTitle());
					stmt.addBatch();
				}
				stmt.executeBatch();
			    stmt.close();
			    conn.close();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private int getId(Connection conn) throws SQLException {
		Statement st = conn.createStatement();
		String sqlstr = "select max(id) from cmireference";
		ResultSet rs = st.executeQuery(sqlstr);
		int rowNum = 0;
		if (rs.next()) {
			rowNum = rs.getInt(1);
		}
	    st.close();
		return rowNum;
	}

	public void addRecord(CmiReference reference) {
		if (reference != null) {
			Connection conn;
			try {
				conn = db.getConnection();
				int rowNum = getId(conn);
				StringBuilder sql = new StringBuilder("insert into cmireference values(");
				sql.append(++rowNum);
				sql.append(",'");
				sql.append(reference.getReferenceTitle());
				sql.append("','");
				sql.append(reference.getAuthors());
				sql.append("','");
				sql.append(reference.getEnterprise());
				sql.append("','");
				sql.append(reference.getLiterature());
				sql.append("','");
				sql.append(reference.getReferenceNum());
				sql.append("','");
				sql.append(reference.getTitle());
				sql.append("')");
				String sqlstr = sql.toString();
				System.out.println(sqlstr);
				Statement stmt = conn.createStatement();
				stmt.executeUpdate(sqlstr);
			    stmt.close();
			    conn.close();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}}