I am trying to find regular expression and if there are some duplicates, keep the unique ones and put the rest in a trash table. but I get this Erro which I do not know what it is! Here is my code:
public class RegexRemoverMain {
public static void main(String[] args) throws SQLException, ClassNotFoundException{
//Connection Parameters and Connect to Postgres Database
String data = "jdbc:postgresql://localhost:5432/postgres";
Class.forName("org.postgresql.Driver");
Connection conn = null;
//Connect to DB
conn = DriverManager.getConnection(
data, "username", "password");
//statements to get distinct owners
Statement ownerSt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//statement to get Image Ids of a user
Statement ownersImagesIdsSt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String insertSQL;
//an arraylist to store unique titles+tags reported by user
ArrayList<List<String>> result = new ArrayList<List<String>>();
//list for storing those Ids of a users which are filtered
List<String> filteredIds = new ArrayList<String>();
//list for storing those Ids of a users which are kept
List<String> ids = new ArrayList<String>();
//get the list of all the users
ResultSet distinctOwner = ownerSt.executeQuery("select distinct owner from \"flickrData_bulkUploadedFree\"");
distinctOwner.last();
distinctOwner.beforeFirst();
int count=0;
//RegularExpression Pattern
String theRegex= "((DSC)?(dsc)?(img)?(IMG)?(\\s?)(\\_?)((\\-?))[0-9]{1,9})";
Pattern checkRegex = Pattern.compile(theRegex);
//loop is going through all user's Images and check whether their the titles is one of the patterns if yes, check their title+description which are unique or not
//if yes, we keep them; if not; we throw them away or store in another place
while(distinctOwner.next()){
count = count++;
Statement insertSt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//store filtered images
String insertString = "INSERT INTO regexIamges"
+ "( id , owner, descriptio, title, tags) VALUES"
+ "(?,?,?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(insertString);
//for each user exist in "flickrData_bulkUploadedFree"
String owner = distinctOwner.getString("owner");
ResultSet ownersImages;
ownersImages = ownersImagesIdsSt.executeQuery("select id, title, tags, descriptio from \"flickrData_bulkUploadedFree\" where owner = '" + owner +"';");
ownersImages.last();
ownersImages.beforeFirst();
//an list of images of a user's with the information about id, title, tags and descriptions in order to find unique Images
ArrayList<List<String>> bulkUploadList = new ArrayList<List<String>>();
while(ownersImages.next()){
String id = ownersImages.getString("id");
String title = ownersImages.getString("title");
String tags = ownersImages.getString("tags");
String description = ownersImages.getString("descriptio");
Matcher regexMatcher = checkRegex.matcher(title);
if (regexMatcher.find()){
if(regexMatcher.group().length() != 0){
List<String> rowsList = new ArrayList<String>();
rowsList.add(id);
rowsList.add(title);
rowsList.add(tags);
rowsList.add(description);
bulkUploadList.add(rowsList);
bulkUploadList.add(rowsList);
}
}
else{
insertSQL = "INSERT INTO \"regBulkfreeFlickrData\" SELECT * FROM \"flickrData_bulkUploadedFree\" where id ='"+id+"';";
insertSt.addBatch(insertSQL);
}
}
HashSet<String> hashSet = new HashSet<String>();
for(List<String> item : bulkUploadList) {
String title, tags, id, desc, uniqueString;
title = item.get(1);
tags = item.get(2);
id = item.get(0);
desc = item.get(3);
uniqueString = (tags + "#" + desc).trim().toUpperCase();
System.out.println(item);
if(!hashSet.contains(uniqueString)) {
result.add(item);
hashSet.add(uniqueString);
insertSQL = "INSERT INTO \"regBulkfreeFlickrData\" SELECT * FROM \"flickrData_bulkUploadedFree\" where id ='"+id+"';";
insertSt.addBatch(insertSQL);
} else {
// System.out.println("Filtered element " + uniqueString + "id " + id);
filteredIds.add(id);
preparedStatement.setString(1, id);
preparedStatement.setString(2, owner);
preparedStatement.setString(3, desc);
preparedStatement.setString(4, title);
preparedStatement.setString(5, tags);
preparedStatement.addBatch();
}
}
preparedStatement.executeBatch();
preparedStatement.close();
insertSt.executeBatch();
insertSt.close();
}
}
and the Error is this:
Exception in thread "main" java.sql.BatchUpdateException: Batch entry 0 INSERT INTO regexIamges( id , owner, descriptio, title, tags) VALUES('4292220054.0000000000000','23352125@N07','NoValue','IMG_2720','NoValue') was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1928)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
at uzh.textmining.RegexRemoverMain.main(RegexRemoverMain.java:116)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
and the table is:
CREATE TABLE "RegexImages"
(id numeric,
owner character varying(254),
descriptio character varying(254),
title character varying(254),
tags character varying(254),
PRIMARY KEY (id)
)
thanks hasnae; I used try catch and I got that the tableName in my code does not match the table Name in database. another problem was the name of the table: I change all the letters to lower case to solve all the errors.
A problem that looks similar: https://stackoverflow.com/a/39227828/755804
(spoiler: The total number of values, that is, the number of columns multiplied by the number of rows must not exceed 32767 for a single INSERT statement. You can divide 32767 by the number of columns to get the maximal number of rows per one SQL INSERT statement.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With