I am using JDBC to query a PostgreSQL database. And I want to populate a column called wiki_url based on the value of another column, level which will determine which value of 5 columns to concatenate to a value in wiki_url. I think the big issue is setting null values to something other than null programmatically without postgresql as the null value expression to not null was a common problem that caused my same error. From print statements I also figured out that the handleEntries function seems to be stuck on name3 and continually repeating.
However I still don't want to use another sql query to check values because it might cause a transaction lock.
I am trying to perform batch updates without using a connection pool to prevent deadlocks so I am using only 1 prepared statement. The control flow will be first checking if the wiki_url doesn't have anything after a slash and then populating it, else checking for duplicate entries after that slash and making them unique.
I am fairly certain that I probably don't have the batch updates correctly coded, but I would like to resolve the error I stated in the question first. I have tried changing the columns to lower case, but it doesn't seem to work. The error and my code is below. The only relevant debug assisting statement I get is:
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'
Which continues over and over again till it hits a certain number of rows. Its not supposed to have null at the end.
SQLState C: 42703 java.util.logging.Logger@28ca1c
VendorError C: 0
Dec 18, 2012 1:16:11 PM Shapefile_Repair main
SEVERE: Batch entry 0 UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null' was aborted. Call getNextException to see the cause.org.postgresql.util.PSQLException: ERROR: column "wiki_url" of relation "adminboundaries" does not exist
Position: 28
public static void handleEntries(Long level, PreparedStatement w_ustmt,
String base_url, String name5, String name4, String name3,
String name2, String name1, String name0) throws SQLException {
String wiki_url_entry = "";
try {
if (level.equals((long) 5)) {
// wiki_url = wiki_url + name5;
wiki_url_entry = base_url + name5;
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (level.equals((long) 4)) {
// wiki_url = wiki_url + name4;
wiki_url_entry = base_url + name4;
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (level.equals((long) 3)) {
// wiki_url = wiki_url + name3;
wiki_url_entry = base_url + name3;
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (level.equals((long) 2)) {
// wiki_url = wiki_url + name2;
wiki_url_entry = base_url + name2;
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (level.equals((long) 1)) {
// wiki_url = wiki_url + name1;
wiki_url_entry = base_url + name1;
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else {
// wiki_url = wiki_url + name0;
wiki_url_entry = base_url + name0;
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
}
w_ustmt.addBatch();
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
lgr.log(Level.SEVERE, ex.getMessage());
while (ex != null) {
System.out.println(ex.getNextException());
}
// System.out.println("SQLState: A " + ex.getSQLState());
// System.out.println("VendorError A: " + ex.getErrorCode());
}
}
/* this function will handle all the urls which have same entries */
public static void handleDups(String wiki_url, String base_url,
String name5, String name4, String name3, String name2,
String name1, String name0, PreparedStatement w_ustmt) {
String wiki_url_entry = "";
try {
if (wiki_url.toString().equals(base_url + name5)) {
wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + "."
+ name2 + "." + name3 + "." + name4 + ")";
// rewriting here, yet it is only printing ?, ?
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (wiki_url.toString().equals(base_url + name4)) {
wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + "."
+ name2 + "." + name3 + ")";
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (wiki_url.toString().equals(base_url + name3)) {
wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + "."
+ name2 + ")";
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (wiki_url.toString().equals(base_url + name2)) {
wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + ")";
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
} else if (wiki_url.toString().equals(base_url + name1)) {
wiki_url_entry = wiki_url + " (" + name0 + ")";
w_ustmt.setString(1, wiki_url_entry);
w_ustmt.addBatch();
System.out.println(w_ustmt.toString());
}
w_ustmt.addBatch();
// System.out.println(w_ustmt.toString());
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
lgr.log(Level.SEVERE, ex.getMessage());
System.out.println("SQLState: B " + ex.getSQLState());
System.out.println("VendorError B :" + ex.getErrorCode());
}
}
public static void main(String[] args) throws InstantiationException,
IllegalAccessException, SQLException {
// TODO Auto-generated method stub
Connection conn = null;
// use the log4jdbc4 wrapper for the connection object
// conn = new net.sf.log4jdbc.ConnectionSpy(conn);
Statement stmt = null;
ResultSet rs = null;
try {
// conn = makeConnection(args[0], args[1], args[2], args[3], "");
String host = args[0];
String port = args[1];
String database = args[2];
String user = args[3];
String password = args[4];
String sql_query = "SELECT \"NAME_0\", \"NAME_1\", \"NAME_2\", \"NAME_3\", \"NAME_4\", \"NAME_5\", \"WIKI_URL\", \"LEVEL_DEPT\" FROM"
+ " AdminBoundaries WHERE \"WIKI_URL\" IN(SELECT \"WIKI_URL\" FROM AdminBoundaries"
+ " GROUP By \"WIKI_URL\" HAVING (count (\"WIKI_URL\") > 1)) ORDER BY \"WIKI_URL\";";
Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://" + host + ":" + port + "/"
+ database;
conn = DriverManager.getConnection(url, user, password);
// if(conn.equals(null)){
// System.err.println("Connection complete");
// }
DatabaseMetaData meta;
try {
if (conn.isClosed()) {
System.out.println("closed");
}
System.out.println(conn.getWarnings());
meta = conn.getMetaData();
System.out.println("Does database support batch processes?:"
+ meta.supportsBatchUpdates());
// after checking the table, adminboundaries was in fact
// evident, so the issue now is changing the sql queries to
// lower case
/*
* ResultSet tables = meta.getTables(null, null, "%", null);
* while (tables.next()){
* System.out.println(tables.getString(3)); }
*/
// trying to check if column exists, which it turns out does
// not?
ResultSet columns = meta.getColumns(null, null,
"adminboundaries", "%");
while (columns.next()) {
System.out.println(columns.getString(4));
}
ResultSetMetaData rsmd = columns.getMetaData();
int numCol = rsmd.getColumnCount();
for (int i = 1; i < numCol + 1; i++) {
while (columns.next()) {
if (rsmd.getColumnName(i).equals("WIKI_URL")
|| (rsmd.getColumnName(i).equals("NAME_0"))) {
System.out.println(columns.getString(4));
} else {
System.out.println("Did not find column");
}
}
}
} finally {
System.out.println(conn.getTransactionIsolation());
System.out.println(sql_query);
stmt = conn.createStatement();
System.out.println(stmt.getFetchSize());
stmt.setMaxRows(555);
System.out.println(conn.getTypeMap());
// stmt.addBatch(sql_query);
rs = stmt.executeQuery(sql_query);
// batch_counter is for the row count of the SQL statements
// executed
// int batch_counter = pstmt.executeUpdate();
// update dup_sql's arguments being set as it will vary
// String wiki_sql =
// "UPDATE AdminBoundaries SET \"WIKI_URL\" = ?";
PreparedStatement w_ustmt = conn
.prepareStatement("UPDATE adminboundaries SET WIKI_URL = ?");
// int counter = 0;
// for (counter = 0; counter < 1000; counter++) {
conn.setAutoCommit(false);
// stmt.addBatch(sql_query);
// stmt.setQueryTimeout(30);
System.out.println(rs.getFetchSize());
SQLWarning resultsetWarning = rs.getWarnings();
System.out.println(resultsetWarning);
// consider changing tables/columns to lower case as sources say
// to do
while (rs.next()) {
String base_url = "http://127.0.0.1/mediawiki/index.php/";
String wiki_url = rs.getString("WIKI_URL");
Long level = rs.getLong("LEVEL_DEPT");
String name0 = rs.getString("NAME_0");
String name1 = rs.getString("NAME_1");
String name2 = rs.getString("NAME_2");
String name3 = rs.getString("NAME_3");
String name4 = rs.getString("NAME_4");
String name5 = rs.getString("NAME_5");
// use 127.0.0.1, not ncsirad-pc b/c wiki_urls are coming
// back 127.0.0.1
// TO DO: rethink this if statement to include in function
// and execute
// both functions without conditionals above. So it will run
// through first
// then run through second.
if (wiki_url.toString().equals(
base_url)) {
// Savepoint savepoint1 =
// conn.setSavepoint("wiki_entry");
// w_ustmt.setQueryTimeout(30);
// System.out.println("Getting into duplicates loop");
handleEntries(level, w_ustmt, base_url, name5, name4,
name3, name2, name1, name0);
// add to batch
// w_ustmt.addBatch(wiki_sql);
// int counts2[] = w_ustmt.executeBatch();
// make sure to try the below
// w_ustmt.executeUpdate();
// conn.rollback(savepoint1);
} else {
handleDups(wiki_url, base_url, name5, name4, name3,
name2, name1, name0, w_ustmt);
w_ustmt.setQueryTimeout(30);
// dup_pstmt.addBatch(dup_sql);
// int count3[] = dup_pstmt.executeBatch();
}
}
int counts[] = w_ustmt.executeBatch();
System.out.println("here is " + counts);
conn.commit();
conn.setAutoCommit(true);
// counter = 0;
// }
}
System.out.println("finished queries");
rs.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
// System.exit(1);
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
lgr.log(Level.SEVERE, ex.getMessage());
System.out.println("SQLState C: " + ex.getSQLState() + " " + lgr);
System.out.println("VendorError C: " + ex.getErrorCode());
if (ex != null) {
System.out.println(ex.getNextException());
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
}
}
// conn.close();
}
}
