Permalink
Cannot retrieve contributors at this time
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
HowToKB/src/main/java/kb/howtokb/utils/SQLiteJDBCConnector.java
Go to fileThis commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
123 lines (103 sloc)
3.58 KB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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){ | |
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;"); | |
} | |
} | |
} | |
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!"); | |
} | |
} |