SQL Query Will Not Execute SQLite, Java

Question

Problem Synopsis: When attempting to execute a SQL query in Java with a SQLite Database, the SQL statement fails to return from the execute() or executeQuery() method. In other words, the system "hangs" when executing this SQL statement.

Question: What am I doing wrong to explain why the ResultSet never "returns?"

TroubleShooting I tried to narrow the problem and the problem seems to be with the Java execute() or executeQuery(). A ResultSet never seems to return. For example, I tried executing exactly the same query directly in SQLite (that is, using a SQLite DB manager). The query (outside Java) executes in about 5ms and returns the valid result set.

NOTE: No exception is thrown. The system merely seems to "hang" and becomes unresponsive until a manual kill. (waiting more than 10 minutes.)

Code: I heavily edited this code to make the problem simpler to see. (In production, this uses Prepared Statements. But, the error occurs in both methods--straight Statement and prepared Statement versions.)

Basically, the SELECT returns a single DB item so the user can review that item.

Statement st = conn.createStatement() ;
ResultSet rs = st.executeQuery("SELECT DISTINCT  d1.id, d1.sourcefullfilepath, " +
    "d1.sourcefilepath, d1.sourcefilename, d1.classificationid, d1.classid, " +
    "d1.userid  FROM MatterDataset,   (SELECT MatterDataset.id, " + 
    "MatterDataset.sourcefullfilepath, MatterDataset.sourcefilepath, " +
    "MatterDataset.sourcefilename, MatterDataset.matterid   , " +
    "DocumentClassification.classificationid, DocumentClassification.classid," +
    " DocumentClassification.userid   FROM MatterDataset    " +
    "LEFT JOIN DocumentClassification ON " +
    "DocumentClassification.documentid = Matterdataset.id    " +
    "WHERE (   DocumentClassification.classid = 1 OR  " +
    "DocumentClassification.classid = 2 )   AND " +
    "DocumentClassification.userid < 0    AND " +
    "MatterDataset.matterid = \'100\'   ) AS d1    " +
    "LEFT JOIN PrivilegeLog ON " +
    "d1.id = PrivilegeLog.documentparentid AND " +
    "d1.matterid = PrivilegeLog.matterid  " +
    "WHERE PrivilegeLog.privilegelogitemid IS NULL  " +
    "AND MatterDataset.matterid = \'100\'   " +
    "ORDER BY d1.id  LIMIT 1 ;") ;

Configuration: Java 6, JDBC Driver = Xerial sqlite-jdbc-3.7.2, SQLite 3, Windows

Update Minor revision: as I continue to work with this, adding a MIN(d1.id) to the beginning of the SQL statement at least returns a ResultSet (rather than "hanging"). But, this is not really what I wanted as the MIN obviates the LIMIT function.

Statement st = conn.createStatement() ;
ResultSet rs = st.executeQuery("SELECT DISTINCT  MIN(d1.id), d1.id,
    d1.sourcefullfilepath, " +
    "d1.sourcefilepath, d1.sourcefilename, d1.classificationid, d1.classid, " +
    "d1.userid  FROM MatterDataset,   (SELECT MatterDataset.id, " + 
    "MatterDataset.sourcefullfilepath, MatterDataset.sourcefilepath, " +
    "MatterDataset.sourcefilename, MatterDataset.matterid   , " +
    "DocumentClassification.classificationid, DocumentClassification.classid," +
    " DocumentClassification.userid   FROM MatterDataset    " +
    "LEFT JOIN DocumentClassification ON " +
    "DocumentClassification.documentid = Matterdataset.id    " +
    "WHERE (   DocumentClassification.classid = 1 OR  " +
    "DocumentClassification.classid = 2 )   AND " +
    "DocumentClassification.userid < 0    AND " +
    "MatterDataset.matterid = \'100\'   ) AS d1    " +
    "LEFT JOIN PrivilegeLog ON " +
    "d1.id = PrivilegeLog.documentparentid AND " +
    "d1.matterid = PrivilegeLog.matterid  " +
    "WHERE PrivilegeLog.privilegelogitemid IS NULL  " +
    "AND MatterDataset.matterid = \'100\'   " +
    "ORDER BY d1.id  LIMIT 1 ;") ;

Is there something wrong here explaining why the SQL ResultSet will not return? — SaB
– SaB, Commented Jul 14, 2012 at 18:17
No offense, but there are dozens of questions you could think of based on your situation. I would suggest to dig into timeouting the execution of the statement from the DB level. see stackoverflow.com/questions/8388155/… — WeMakeSoftware
– WeMakeSoftware, Commented Jul 14, 2012 at 18:21
Does it help if you remove the semicolon at the end of the query string? — Luke Woodward
– Luke Woodward, Commented Jul 14, 2012 at 18:23
Thank you (to all--no offense taken). No, omitting the closing ';' does not help. — SaB
– SaB, Commented Jul 14, 2012 at 18:32

Set · Accepted Answer · 2012-07-14 19:33:34Z

1

What a messy SQL statement (sorry)! I don't know SQLite, but why not simplify to:

SELECT DISTINCT md.id, md.sourcefullfilepath, md.sourcefilepath, md.sourcefilename, 
                dc.classificationid, dc.classid, dc.userid
FROM MatterDataset md
LEFT JOIN DocumentClassification dc
       ON dc.documentid = md.id
      AND (dc.classid = 1 OR dc.classid = 2 )
      AND dc.userid < 0
LEFT JOIN PrivilegeLog pl
       ON md.id = pl.documentparentid 
      AND md.matterid = pl.matterid
WHERE pl.privilegelogitemid IS NULL
  AND md.matterid = \'100\'
ORDER BY md.id LIMIT 1 ;

I was uncertain whether you wanted to LEFT JOIN or INNER JOIN to DocumentClassification (using LEFT JOIN and then put requirements on classid and userid in the WHERE statement is - in my opinion - contradictory). If DocumentClassification has to exist, then change to INNER JOIN and put the references to classid and userid into the WHERE clause, if DocumentClassification may or may not exist in your result set, then keep the query as I suggested above.

answered Jul 14, 2012 at 19:33

Set

3911 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SaB Over a year ago

Thank you Set! I like you aliasing method. I will also try your method tomorrow to see how it compares. (I had revised my SQL statement by starting over--see below. Still messy--no offense taken byme, the initial IS a mess--but works and is surprisingly fast.)

SaB · Accepted Answer · 2012-07-14 20:44:31Z

I went back and started over. The SQL syntax, while it worked outside Java, simply seemed too complex for the JDBC driver. This cleaned-up revision seems to work:

SELECT DISTINCT 
  MatterDataset.id, MatterDataset.sourcefullfilepath, MatterDataset.sourcefilepath,
  MatterDataset.sourcefilename
FROM MatterDataset , DocumentClassification 
  ON DocumentClassification.documentid = MatterDataset.id  
    AND MatterDataset.matterid = DocumentClassification.matterid
LEFT JOIN PrivilegeLog ON MatterDataset.id = PrivilegeLog.documentparentid 
  AND MatterDataset.matterid = PrivilegeLog.matterid
WHERE PrivilegeLog.privilegelogitemid IS NULL  
  AND MatterDataset.matterid = '100' 
  AND (DocumentClassification.classid = 1 OR DocumentClassification.classid = 2) 
  AND DocumentClassification.userid = -99
ORDER BY MatterDataset.id LIMIT 1;

A nice lesson in: just because you can in SQL doesn't mean you should.

What this statement does is essentially locates items in the MatterDataset Table that are NOT in the PrivilegeLog table. The LEFT JOIN and IS NULL syntax locate the items that are "missing." That is, i want to find items that are in MatterDataset but not yet in PrivilegeLog and return those items.

Collectives™ on Stack Overflow

SQL Query Will Not Execute SQLite, Java

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related