package com.sitech.ismp.coll;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.sitech.database.ConnectionPool;

public class SetParam {
	public ConnectionPool conpool = ConnectionPool.getInstance();

	private java.sql.Connection conn = null;

	public Statement stmt = null;

	static Logger logger = Logger.getLogger("LOGER");

	public SetParam() {
	}

	public void setConnection() throws SQLException {
		if (conn != null && !conn.isClosed())
			return;
		newConnection();
	}

	private Connection newConnection() throws SQLException {
		if (this.conn == null || conn.isClosed()) {
			this.conn = conpool.createConnection();
		}
		return conn;
	}

	public void freeConnection() throws SQLException {
		conpool.closeConn();
		conn = null;
	}

	public int setParam(String sql) {
		int result = -9;
		try {
			setConnection();
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
			stmt.close();
			freeConnection();

		} catch (Exception e) {
			logger.error("Exception setParam(String sql)", e);

		}
		return result;
	}

	public boolean setParam(String tableName, String keyName, String keyValue,
			String paramName, String paramValue) {
		String sqlUpdate = "update " + tableName + " set " + paramName + "='"
				+ paramValue + "' where " + keyName + " = '" + keyValue + "'";
		logger.debug(sqlUpdate);
		int result = -9;
		try {
			setConnection();
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sqlUpdate);
			logger.debug("update Result is " + result);
			if (result == 0) {
				String sqlInsert = "insert into " + tableName + "(" + keyName
						+ "," + paramName + ")" + " values('" + keyValue
						+ "','" + paramValue + "')";
				logger.debug(sqlInsert);
				result = stmt.executeUpdate(sqlInsert);
				logger.debug("insertResult is " + result);

			}
			stmt.close();
			freeConnection();
		} catch (Exception ex) {
			logger.error("Exception insert --", ex);

		}
		if (result == 1)
			return true;
		else
			return false;
	}

	public int deleteParam(String tableName, String keyName, String keyValue) {
		String sqlDelete = "delete FROM " + tableName + " where " + keyName
				+ " = '" + keyValue + "'";
		logger.debug(sqlDelete);
		int result = -9;
		try {
			setConnection();
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sqlDelete);

			stmt.close();
			freeConnection();
		} catch (Exception ex) {
			logger.error("Exception deleteParam --", ex);
		}
		return result;
	}
}