D-Net

package eu.dnetlib.data.claimsDemo;

import org.apache.log4j.Logger;

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 = Logger.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 connections.

	 *

	 * @throws SQLException

	 */

	public void closeConnection() throws SQLException {

			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);

			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);

		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);

		try{

			this.getConnection();

 			Statement st = connection.createStatement();

			if (st.execute(command)) {

				ResultSet rs = st.getResultSet();

				//st.close();

				return rs;

			} else {

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

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

			}

		} finally {

			this.closeConnection();

		}

	}

	/**

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

	 * Returns a result set.

	 *

	 * @param command

	 * @param values

	 * @return

	 * @throws SQLException

	 */

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

		log.debug("  Executing   Query ...\n" + command +"\n"+values);

		try{

			this.getConnection();

			PreparedStatement st = connection.prepareStatement(command);

			int pos = 1;

			for (Object v : values) {

				st.setObject(pos, v);

				pos++;

			}

			if (st.execute()) {

				ResultSet rs = st.getResultSet();

			//	st.close();

				return rs;

			} else {

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

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

			}

		} finally {

			this.closeConnection();

		}

	}

	/**

	 * Executes the @param command Update query

	 *

	 * @param command

	 * @return

	 * @throws SQLException

	 */

	public void executeUpdate(String command) throws SQLException {

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

		try{

			this.getConnection();

 			Statement st = connection.createStatement();

			st.executeUpdate(command);

			//st.close();

		} finally {

			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);

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

		try{

			this.getConnection();

			PreparedStatement 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.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;

	}

}