SQLUtility.java

/**
 * ClarescoExperienceAPI
 * Copyright 
 *
 * This code is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License version 2 only, as
 * published by the Free Software Foundation.
 *
 * Please contact Claresco, www.claresco.com, if you have any questions.
 **/

package com.claresco.tinman.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Properties;

import org.joda.time.DateTime;

import com.claresco.tinman.lrs.XapiStatement;

/**
 * SqlUtility.java
 *
 * Description:
 * 	Utility file to help write and read from and to the databse
 *
 *
 *
 * @author rheza
 * on Feb 20, 2014
 * 
 */

public abstract class SQLUtility {

	protected static PreparedStatement createInsertStatement(Connection conn, String databaseName, 
			String[] fieldNames) throws SQLException{
		String insertString = createInsertString(databaseName, fieldNames);
		
		return conn.prepareStatement(insertString);
	}
	
	
	
	private static String createInsertString(String databaseName, String[] fieldNames){
		String insertString = String.format("insert into %s (", databaseName);
		
		for (int i = 0; i < fieldNames.length; i++) {
			if(i == fieldNames.length - 1){
				insertString = insertString.concat(fieldNames[i]).concat(") ");
			}else{
				insertString = insertString.concat(fieldNames[i]).concat(",");
			}
		}
		
		insertString = insertString.concat("values (");
		
		for (int j = 0; j < fieldNames.length; j++) {
			if(j == fieldNames.length - 1){
				insertString = insertString.concat("?);");
			}else{
				insertString = insertString.concat("?,");
			}
		}
				
		return insertString;
	}
	
	
	
	protected static PreparedStatement createRetrievalStatement(Connection conn, String databaseName,
			String[] fieldNames) throws SQLException{
		String retrieveString = createRetrieveString(databaseName, fieldNames);
		
		return conn.prepareStatement(retrieveString);
	}
	
	
	
	protected static PreparedStatement createRetrievalStatement(Connection conn, String databaseName,
			String fieldName) throws SQLException{
		String retrieveString = createRetrieveString(databaseName, fieldName);
		
		return conn.prepareStatement(retrieveString);
	}
	
	
	
	protected static PreparedStatement createRetrievalStatement(Connection conn, String tableName,
			String[] fieldNames, String[] fieldsToRetrieve) throws SQLException{
		String s = createRetrievalString(tableName, fieldNames, fieldsToRetrieve);
		
		return conn.prepareStatement(s);
	}
	
	
	
	protected static PreparedStatement createRetrievalStatement(Connection conn, String tableName, 
			String[] fieldNames, String[] fieldsToRetrieve, String[] operations) throws SQLException{
		String s = createRetrievalString(tableName, fieldNames, fieldsToRetrieve, operations);
		
		return conn.prepareStatement(s);
	}
	
	
	
	private static String createRetrieveString(String databaseName, String fieldName){
		String retrieveString = String.format("select * from %s where %s = ?", databaseName, fieldName);
		//String retrieveString = "select * from ? where ? = ?";
		return retrieveString;
	}
	
	
	
	private static String createRetrievalString(String tableName, String[] fieldNames, String[] fieldsToRetrieve){
		String[] operation = new String[fieldNames.length];
		
		for(int i = 0; i < fieldNames.length; i++){
			operation[i] = "=";
		}
		
		return createRetrievalString(tableName, fieldNames, fieldsToRetrieve, operation);
	}
	
	
	private static String createRetrievalString(String tableName, String[] fieldNames, String[] fieldsToRetrieve,
			String[] operations){
		String baseString = "select %s from %s where %s";
		
		String selectionString = "";
		for(int i = 0; i < fieldNames.length; i++){
			selectionString += String.format("and %s %s ? ", fieldNames[i], operations[i]);
		}
		
		String fieldString = "";
		for(String fs : fieldsToRetrieve){
			fieldString += String.format(", %s", fs);
		}
		
		String fullString = String.format(baseString, fieldString, tableName, selectionString);
		
		fullString = fullString.replace("select ,", "select");
		fullString = fullString.replace("where and", "where");
		
		fullString = fullString + ";";
		
		return fullString;
	}
	
	
	
	private static String createRetrieveString(String databaseName, String[] fieldNames){
		String retrieveString = createRetrieveString(databaseName, fieldNames[0]);
		for(int i = 1; i < fieldNames.length; i++){
			retrieveString = retrieveString.concat(String.format(" and %s = ?", fieldNames[i]));
		}
		return retrieveString;
	}
	
	
	
	protected static PreparedStatement createUpdateStatement(Connection conn, String databaseName, 
			String[] fieldsToUpdate, String[] selectionFields) throws SQLException{
		String updateString = createUpdateString(databaseName, fieldsToUpdate, selectionFields);
		return conn.prepareStatement(updateString);
	}
	
	
	
	private static String createUpdateString(String databaseName, String[] fieldsToUpdate, String[] selectionFields){
		String baseString = "update %s set %s where %s;";
		
		String setFieldsString = "";
		for (String s : fieldsToUpdate){
			setFieldsString += s + " = ?, ";
		}
		setFieldsString = setFieldsString.substring(0, setFieldsString.length() - 2);
		//System.out.println(setFieldsString);
		
		String selectionString = "";
		for (String s : selectionFields){
			selectionString += s + " = ? and ";
		}
		selectionString = selectionString.substring(0, selectionString.length() - 4);
		//System.out.println(selectionString);
		
		return String.format(baseString, databaseName, setFieldsString, selectionString);
	}
	
	

	protected static void closeStatement(PreparedStatement theStatement) throws SQLException{
		if(theStatement != null){
			theStatement.close();
		}
	}
	
	
	
	protected static void closeResultSet(ResultSet theResultSet) throws SQLException{
		if(theResultSet != null){
			theResultSet.close();
		}
	}
	
	
	
	protected static Connection establishDefaultConnection() throws SQLException{
		String myUser = "username";
		String myPassword = "password";	
		String myUrl = "jdbc:";
		
		Properties props = new Properties();
		props.setProperty("user", myUser);
		props.setProperty("password", myPassword);
		
		return DriverManager.getConnection(myUrl, props);
	}
	

	
	protected static boolean isResultEmpty(ResultSet theResult) throws SQLException{
		if(!theResult.isBeforeFirst()){
			return true;
		}
		return false;
	}
	
	
	
	protected static ResultSet executeRetrievalByIDQuery(PreparedStatement theStatement, int theID)
			throws SQLException{
		theStatement.setInt(1, theID);
		return theStatement.executeQuery();
	}
	
	
	
	protected static ResultSet setStringAndExecute(PreparedStatement theStatement, String theString,
			int theIndex)throws SQLException{
		theStatement.setString(theIndex, theString);
		return theStatement.executeQuery();
	}
	
	
	
	protected static ResultSet setIntAndExecute(PreparedStatement theStatement, int theID, int 
			theIndex)throws SQLException{
		theStatement.setInt(theIndex, theID);
		return theStatement.executeQuery();
	}

	
	
	protected static void printHashMap(HashMap<Integer, XapiStatement> theMap){
		System.out.println("statement id");
		for(Integer i : theMap.keySet()){
			System.out.println(i.toString());
		}
	}
	
	
	
	protected static Timestamp getTimestamp(DateTime theTS){
		return new Timestamp(theTS.getMillis());
	}
	
	
	
	protected static DateTime getDatetime(Timestamp theTS){
		return new DateTime(theTS.getTime());
	}
	
	
	
	public static void main(String[] args) {
		System.out.println(SQLUtility.createRetrieveString("actor", "actorid"));
		
		String[] a = {"accthomepage", "acctname"};
		String[] operation = {">=", "<="};
		
		System.out.println(SQLUtility.createRetrieveString("account", a));
		
		System.out.println(SQLUtility.createInsertString("account", a));
		
		System.out.println(SQLUtility.createRetrievalString("account", a, a, operation));
		
		System.out.println(SQLUtility.createUpdateString("statement", a, a));
	}

}