D-Net

package eu.dnetlib.data.claims.sql;

import com.sun.rowset.CachedRowSetImpl;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import javax.sql.rowset.CachedRowSet;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

/**

 * @author eri Sql Store class to connect to the DB backend

 */

public class SqlStore {

	private Logger log = LogManager.getLogger(this.getClass());

	private String dbUrl;

	private String dbUser;

	private String dbPassword;

	private Connection connection;

	private String dbDriver;

	public SqlStore(String dbUrl, String dbUser, String dbPassword, String dbDriver)  {

		this.dbDriver = dbDriver;

		this.dbUser = dbUser;

		this.dbUrl = dbUrl;

		this.dbPassword = dbPassword;

	}

	public SqlStore() throws Exception {

	}

	public void init() throws ClassNotFoundException, SQLException {

 			Class.forName(dbDriver);

//			this.getConnection();

	}

	public void destroy() throws SQLException {

		this.closeConnection();

	}

	public void close() throws SQLException {

		closeConnection();

	}

	/**

	 * Establishes a JDBC connection to the database server without a given DB.

	 * Used in order to create or drop databases.

	 *

	 * @throws SQLException

	 */

	public void getConnectionNoDatabase() throws SQLException {

		String parsedUrl = this.parseUrl(dbUrl);

		log.info("Connecting to " + parsedUrl + dbUser );

		connection = (Connection) DriverManager.getConnection(parsedUrl, dbUser, dbPassword);

		log.info("Connected to " + parsedUrl);

	}

	/**

	 * Establishes a connection to a given DB.

	 *

	 * @throws SQLException

	 */

	public void getConnection() throws SQLException {

			log.info("Attempting connection to : " + this.dbUrl + " for " + this.dbUser );

			connection = (Connection) DriverManager.getConnection(this.dbUrl, dbUser, dbPassword);

			log.info("Connected to " + this.dbUrl);

	}

	/**

	 * Closes Database Statement.

	 *

	 * @throws SQLException

	 */

	public void closeStatement(Statement st) throws SQLException {

		log.debug("Closing Statement...");

		if (st != null) {

			st.close();

		}

	}

	/**

	 * Closes Database Resultset.

	 *

	 * @throws SQLException

	 */

	public void closeResultset(ResultSet rs) throws SQLException {

		log.debug("Closing Resultset...");

		if (rs != null) {

			rs.close();

		}

	}

	/**

	 * Closes Database connections.

	 *

	 * @throws SQLException

	 */

	public void closeConnection() throws SQLException {

		log.debug("Closing Connection...");

		if (connection != null) {

			connection.close();

		}

	}

	/**

	 * Executes the @param command stored procedure in the Database. Stored

	 * procedure parameters are specified in values.

	 *

	 * @param command

	 * @param values

	 * @return

	 * @throws SQLException

	 */

	public ResultSet executeStoredProcedure(String command, ArrayList<String> values) throws SQLException {

		java.sql.CallableStatement callableStatement = null;

		log.debug("  Executing   Query ..." + command.substring(0,(command.length()>150?150:command.length())));

			callableStatement = this.connection.prepareCall(command);

			int pos = 1;

			for (String v : values) {

				callableStatement.setObject(pos, v);

				pos++;

			}

			callableStatement.execute();

			return callableStatement.getResultSet();

	}

	/**

	 * Executes the stored query procedure specified by @param command.

	 *

	 * @param command

	 * @return

	 * @throws SQLException

	 */

	public ResultSet executeStoredProcedure(String command) throws SQLException {

		log.debug("  Executing   Query ..." + command.substring(0,(command.length()>150?150:command.length())));

		java.sql.CallableStatement callableStatement = null;

			callableStatement = this.connection.prepareCall(command);

			callableStatement.execute();

			return callableStatement.getResultSet();

	}

	/**

	 * Executes the @param query

	 *

	 * @param command

	 * @return

	 * @throws SQLException

	 */

	public ResultSet executeQuery(String command) throws SQLException, SQLStoreException {

		log.debug("  Executing   Query: " + command.substring(0,(command.length()>150?150:command.length()))+"....");

		Statement st = null;

		ResultSet rs = null;

		try{

			this.getConnection();

 			st = connection.createStatement();

			if (st.execute(command)) {

				rs = st.getResultSet();

				CachedRowSet rowset = new CachedRowSetImpl();

				rowset.populate(rs);

				return rowset;

				//st.close();

				//return rs;

			} else {

				log.error("Fail to execute command " + st.getWarnings());

				throw  new SQLStoreException("Fail to execute command " + st.getWarnings());

			}

		} finally {

			this.closeResultset(rs);

			this.closeStatement(st);

			this.closeConnection();

		}

	}

	/**

	 * Executes the @param  prepared query with the @values as parameters

	 * Returns a result set.

	 *

	 * @param command

	 * @param values

	 * @return

	 * @throws SQLException

	 */

	public CachedRowSet executeQuery(String command, ArrayList<Object> values) throws SQLException, SQLStoreException {

		log.debug("  Executing   Query ...\n" + command.substring(0,(command.length()>150?150:command.length())) +"\n"+values);

		PreparedStatement st = null;

		ResultSet rs = null;

		try{

			this.getConnection();

			st = connection.prepareStatement(command);

			int pos = 1;

			for (Object v : values) {

				st.setObject(pos, v);

				pos++;

			}

			if (st.execute()) {

				rs = st.getResultSet();

				CachedRowSet rowset = new CachedRowSetImpl();

				rowset.populate(rs);

				return rowset;

				//return rs;

			} else {

				log.error("Fail to execute command " + st.getWarnings());

				throw  new SQLStoreException("Fail to execute command " + st.getWarnings());

			}

		} finally {

			this.closeResultset(rs);

			this.closeStatement(st);

			this.closeConnection();

		}

/*

		ResultSet rs = null;

		PreparedStatement st = null;

		this.getConnection();

		try {

			st = connection.prepareStatement(command);

			int pos = 1;

			for (Object v : values) {

				st.setObject(pos, v);

				pos++;

			}

			try {

				st.execute();

				try {

					rs = st.getResultSet();

					CachedRowSetImpl rowset = new CachedRowSetImpl();

					rowset.populate(rs);

					return rowset;

				} finally {

					try {

						rs.close();

					} catch (SQLException e) {

						log.error("Cannot close result set", e);

					}

				}

			} finally {

				try {

					st.close();

				} catch (SQLException e) {

					log.error("Fail to execute command " + st.getWarnings());

					log.error("Cannot close statement", e);

				}

			}

		} finally {

			try {

				log.debug("Closing Connection...");

				this.closeConnection();

			} catch (SQLException e) {

				log.error("Cannot close connection", e);

			}

		}

*/

	}

	/**

	 * Executes the @param command Update query

	 *

	 * @param command

	 * @return

	 * @throws SQLException

	 */

	public void executeUpdate(String command) throws SQLException {

		log.debug("  Executing   Query ..." + command.substring(0,(command.length()>150?150:command.length())));

		Statement st = null;

		try{

			this.getConnection();

 			st = connection.createStatement();

			st.executeUpdate(command);

			//st.close();

		} finally {

			this.closeStatement(st);

			this.closeConnection();

		}

	}

    public void executeUpdateWithRollback(List<String> commandS)  throws  SQLException{

        log.debug("  Executing   Query ..." + commandS);

            Statement st = connection.createStatement();

            connection.setAutoCommit(false);

            for(String command : commandS) {

                if(command.equals("EXCEPTION")){

                    throw new SQLException("Force rollback ");

                }

                st.executeUpdate(command);

            }

            //st.close();

            connection.commit();

            connection.setAutoCommit(true);

    }

	/**

	 * Executes the @param command  Prepared Update query  with the

	 *

	 * @param command

	 * @param values

	 * @return

	 * @throws Exception

	 * @values as parameters

	 */

	public boolean executeUpdate(String command, ArrayList<Object> values) throws SQLException {

		log.debug("  Executing   Query ..." + command.substring(0,(command.length()>(command.length()>150?150:command.length())?150:command.length())));

		PreparedStatement st = null;

		try{

			this.getConnection();

			st = connection.prepareStatement(command);

			int pos = 1;

			for (Object v : values) {

				st.setObject(pos, v);

				pos++;

			}

			boolean executed = st.execute();

			if (executed) {

				log.error("Fail to execute update command " + st.getWarnings());

				throw new SQLException("Fail to execute update command " + st.getWarnings());

			}

			return !executed;

		} finally {

			this.closeStatement(st);

			this.closeConnection();

		}

	}

	/**

	 * Turns autocommit on and off.

	 *

	 * @param on

	 * @throws Exception

	 */

	public void setautoCommit(boolean on) throws SQLException {

			this.connection.setAutoCommit(on);

	}

	/**

	 * Creates a serialized string from the ResultSet returned from a query

	 *

	 * @throws Exception

	 */

	public String getResults(ResultSet rs) throws SQLException {

		String data = new String();

		try {

			ResultSetMetaData rsmd = rs.getMetaData();

			while (rs.next()) {

				for (int i = 1; i < rsmd.getColumnCount() - 1; i++)

				{

					log.debug(rsmd.getColumnName(i) + "," + rs.getString(i));

					data += rsmd.getColumnName(i) + "," + rs.getString(i) + "\n";

				}

			}

			log.debug(" Generated Data :" + data);

			rs.close();

			return data;

		} finally {

			this.closeConnection();

		}

	}

	private String parseUrl(String url) {

		if (url.endsWith("/")) {

			url = url.substring(0, url.lastIndexOf('/'));

		}

		url = url.substring(0, url.lastIndexOf('/') + 1);

		log.debug(" db url  parsed" + url);

		return url;

	}

	public String getDbDriver() {

		return dbDriver;

	}

	public void setDbDriver(String dbDriver) {

		this.dbDriver = dbDriver;

	}

	public String getDbUrl() {

		return dbUrl;

	}

	public void setDbUrl(String dbUrl) {

		this.dbUrl = dbUrl;

	}

	public String getDbUser() {

		return dbUser;

	}

	public void setDbUser(String dbUser) {

		this.dbUser = dbUser;

	}

	public String getDbPassword() {

		return dbPassword;

	}

	public void setDbPassword(String dbPassword) {

		this.dbPassword = dbPassword;

	}

	public void setConnection(Connection connection) {

		this.connection = connection;

	}

}