SQLiteJDBCConnector.java

package kb.howtokb.utils;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLiteJDBCConnector {

	public static Connection c = null;
	public static Statement st = null;

	public static boolean check = false;

	public static String db = "wikihowDB";
	
	public static ResultSet q(String sql) throws SQLException, ClassNotFoundException, IOException {
		try{
			if (check == false){
				System.out.println("Initializing db ...");
				createDB();
				System.out.println("Initializing db successfully!");
			}
			try {
				return st.executeQuery(sql);
			} catch (Error | Exception e1) {
				System.out.println("Exception (" + e1.getMessage() + ") " + "while initializing the DB. \n "
						+ "Trying to automatically resolve...");
	
				// returning empty result set for problematic query
				return q("SELECT NULL LIMIT 0;");
			}
		} catch (Error | Exception e){
			System.out.println("Database is already created!");
			try {
				return st.executeQuery(sql);
			} catch (Error | Exception e1) {
				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;");
				return null;
			}
		}
	}

	public static void createDB() throws SQLException, ClassNotFoundException, IOException {
		try {
			
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:" + db);
			
			st = c.createStatement();
			String sql = "CREATE TABLE IF NOT EXISTS frameidtostrongactsurface " + "(ID INT PRIMARY KEY     NOT NULL,"
					+ " task           TEXT    NOT NULL);";
			st.executeUpdate(sql);
			
			
//			
//			sql = "DROP TABLE categoryjson";
//			st.executeUpdate(sql);
			
			sql = "CREATE TABLE IF NOT EXISTS categoryjson " + "(ID INT PRIMARY KEY     NOT NULL,"
					+ " json           TEXT    NOT NULL);";
			st.executeUpdate(sql);
			//System.out.println("Test");
			
			
//			ResultSet rs = st.executeQuery("select task from frameidtostrongactsurface where id=1;");
//			if (!rs.next()){
//				String input = "/var/tmp/cxchu/clustering-result/for-database/frame-id-to-strong-surface";
//				System.out.println("Updating data into table 'frameidtostrongactsurface'......");
//				update(st, "frameidtostrongactsurface", input);
//				}
//			
//			
//			rs = st.executeQuery("select json from categoryjson where id=1;");
//			if (!rs.next()){
//				String input = "/var/tmp/cxchu/wikihow-id-category.json";
//				System.out.println("Updating data into table 'category'.....");
//				update(st, "categoryjson", input);
//			}
			
			check = true;
		} catch (SQLException e) {
			check = true;
			
		}

	}
	
	public static void closeDB(){
		try {
			if (!c.isClosed())
				c.close();
			c = null;
			st = null;
		} catch (Exception e) {
			c = null;
			st = null;
		}
	}

	public static void update(Statement st, String table, String file) throws IOException, SQLException {
		BufferedReader br = new BufferedReader(new FileReader(file));
		String line;
		int tmp = 0;
		while ((line = br.readLine()) != null) {
			String[] values = line.split("\\t"); // your seperator
			try{
				// Convert String to right type. Integer, double, date etc.
				st.executeUpdate("INSERT INTO " + table + " VALUES(" + values[0] + ",'" + values[1] + "');");
				// Use a PeparedStatemant, it´s easier and safer
			}catch (SQLException e){
				System.out.println(e.getMessage());
//				System.out.println(values[0]);
			}
			tmp++;
		}
		br.close();
		System.out.println("Updated " + tmp + " records!");
	}

}