package common;

import database.DBPoolManager;
import java.sql.*;

public class SingleIndex {
	private static SingleIndex instance;

	private SingleIndex() {
	}

	public synchronized static SingleIndex getInstance() {
		if (instance == null) {
			instance = new SingleIndex();
		}
		return instance;
	}

	public long getId(String table, String field) {
		long res = 0;
		DBPoolManager dbpool = new DBPoolManager();
		dbpool.getConnection();
		dbpool.setAutoCommit(false);
		try {
			table = table.toUpperCase();
			field = field.toUpperCase();

			String sql = "select codes from Code_Index where table_name = '"
					+ table + "' and field_name = '" + field + "'";
			Statement selectStatement = dbpool.conn.createStatement();
			ResultSet rest = selectStatement.executeQuery(sql);
			if (rest.next()) {
				res = rest.getLong(1) + 1;
			}
			rest.close();
			selectStatement.close();

			if (res <= 0) {
				sql = "insert into Code_Index (code_id, table_name, field_name, codes, memo) "
						+ "values (CODEID.nextval, '"
						+ table
						+ "', '"
						+ field
						+ "', 1, '" + table + "編號')";
				res = 1;
			} else {
				sql = "update Code_Index set codes = " + res
						+ "where table_name = '" + table
						+ "' and field_name = '" + field + "'";
			}
			Statement updateStatement = dbpool.conn.createStatement();
			int rowCount = updateStatement.executeUpdate(sql);
			updateStatement.close();
			if (rowCount > 0) {
				dbpool.commit();
			} else {
				dbpool.rollback();
			}
		} catch (SQLException ex) {
			res = 0;
			dbpool.rollback();
			System.err.println("Create " + table + "-" + field
					+ " Id Exception : " + ex.getMessage());
		} finally {
			dbpool.freeConnection();
		}
		return res;
	}
}