DBConnector.java

package util;

import java.io.FileReader;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

import javatools.administrative.Announce;
import javatools.database.Database;
import javatools.database.PostgresDatabase;

/**
 * DID NOT WORK USING LOAD. Created ~6 files of 200MB each, to \copy from psql.
 * 
 * @author ntandon
 * 
 */
public class DBConnector {

	private static Database db;
	private static CopyManager cm;

	public static ResultSet q(String sql, boolean setAutoCommit) throws SQLException {
		Statement st = DBConnector.getDB().getConnection().createStatement();
		st.setFetchSize(1000);
		db.getConnection().setAutoCommit(setAutoCommit);

		try {
			return st.executeQuery(sql);
		} catch (Error | Exception e) {
			System.out.println("Exception (" + e.getMessage() + ") " + "while initializing the DB. \n "
					+ "Trying to automatically resolve...");

			// returning empty result set for problematic query
			return q("SELECT NULL LIMIT 0;", true);
		}
	}

	public static ResultSet qNoTryCatch(String sql, boolean setAutoCommit) throws SQLException {
		Statement st = DBConnector.getDB().getConnection().createStatement();
		st.setFetchSize(1000);
		db.getConnection().setAutoCommit(setAutoCommit);
		return st.executeQuery(sql);
	}

	public static ResultSet q(String sql) throws SQLException {

		// Most of our queries are select queries (in batch size of 1000,
		// these select queries are considered as batch queries.
		return q(sql, false);
	}

	public static void resetDb() throws SQLException {
		q("SELECT NULL LIMIT 0;", true);
	}

	// This is a check to ensure that the DB doesn't fail.
	// Specific word level queries (e.g. niket's hands) should be done
	// by the application and the logic should not be here.
	private static String verifySQLQuery(String sql) {
		if (sql == null)
			return "";

		int countSingleQuotes = Util.countChar(sql, '\'', false);
		if (countSingleQuotes % 2 == 1) {
			return ""; // Too complicated to
						// handle.
			// select * where field like '%'%' would not terminate the query
			// sql = sql.replaceFirst("'", "''"); // just need to make it odd.
		}

		return sql;

	}

	/*
	 * public static Database getDB() { if (db != null) return db; try { db =
	 * new PostgresDatabase("ntandon", "postgres@123", "www13",
	 * "postgres2.d5.mpi-inf.mpg.de", null);
	 * 
	 * // Turn off autocommit mode db.getConnection().setAutoCommit(false); }
	 * catch (Exception e) { try { e.printStackTrace(); db = new
	 * PostgresDatabase("postgres", "postgres@123", "postgres", null, null); }
	 * catch (Exception e1) { e1.printStackTrace(); } } return db; }
	 */
	public static Database getDB() {
		return getDB("ntandon", "postgres@123", "www13", "postgres2.d5.mpi-inf.mpg.de", null);
	}

	public static Database getDB(String user, String password, String database, String host, String port) {
		if (db != null)
			return db;
		try {
			db = new PostgresDatabase(user, password, database, host, port);

			// Turn off autocommit mode
			db.getConnection().setAutoCommit(false);
		} catch (Exception e) {
			try {
				e.printStackTrace();
				db = new PostgresDatabase("postgres", "postgres@123", "postgres", null, null);
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return db;
	}

	public static void closeConnections() {
		try {
			if (!db.getConnection().isClosed())
				db.close();
			db = null;
			cm = null;
		} catch (Exception e) {
			db = null;
			cm = null;
		}
	}

	/**
	 * cm.copyIn("COPY mytable FROM STDIN WITH DELIMITER '\t'", new
	 * FileReader(fileToLoadFrom.tsv));
	 */
	public static void load(String tbName, String fileToCopyFrom, boolean clearTable, char delim) throws Exception {
		// TODO set autocommit true to have an effect.
		if (cm == null)
			cm = new CopyManager((BaseConnection) getDB().getConnection());
		if (clearTable)
			getDB().executeUpdate("DELETE FROM " + tbName);
		cm.copyIn("COPY " + tbName + " FROM STDIN WITH DELIMITER '" + delim + "'", new FileReader(fileToCopyFrom));
	}

	public static List<String> singletonColumn(String sql, int MAX_OUTPUT) throws SQLException {
		List<String> l = new ArrayList<>();
		ResultSet rs = q(sql);
		while (rs.next())
			if (l.size() <= MAX_OUTPUT)
				l.add(rs.getString(1));
			else
				break;

		return l;
	}

	public static List<String> singletonColumn(String sql) throws SQLException {
		return singletonColumn(sql, Integer.MAX_VALUE);
	}

	public static Set<String> singletonColumnToSet(int colStartingWith1ToFetch, String sql, int MAX_OUTPUT)
			throws SQLException {
		Set<String> l = new HashSet<>();
		ResultSet rs = q(sql);
		while (rs.next())
			if (l.size() <= MAX_OUTPUT)
				l.add(rs.getString(colStartingWith1ToFetch));
			else
				break;

		return l;
	}

	public static Set<String> singletonColumnToSet(int colStartingWith1ToFetch, String sql){
		try{
			return singletonColumnToSet(colStartingWith1ToFetch, sql, Integer.MAX_VALUE);
		}catch(SQLException e){
			e.printStackTrace();//TODO later on better exception messaging using e.g. Announce			
			return Collections.emptySet();
		}
	}

}