Java SQL query execution, problems in reading the ResultSet rows

Question

I'm writing simple Java code and I have to execute an SQL query on my Postgresql database. The result of this database (I've checked manually) is a simple table made by one column and two integer numbers, like this:

enter image description here

My problem is that I cannot find any way to read these two rows. My reading code is:

_conn.setReadOnly(false);

_stmt = _conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

_stmt.setFetchSize(100);

List< Short > lista = new List< Short >();

_rs = _stmt.executeQuery(query);

while (_rs.next()) {

lista.add((short)_rs.getInt(1));

}

This code only reads the 1st row, but there's no way to make it read the 2nd row. The Short list "lista" contains always 1 element. It's like if the ResultSet does not proceed in reading the second row...

How to read both the rows?

EDIT: I've tried with ArrayList but it does not work. I've added the code part for the _conn connection and the _stmt statement. Here's the query:

            SELECT COUNT(bffK.biological_function_feature_oid) 
        FROM biological_function_feature AS bff 
        INNER JOIN biological_function_feature_unfolded AS bffu 
        ON bff.biological_function_feature_oid = bffu.term_oid 
        INNER JOIN (    SELECT DISTINCT ON (g.gene_oid, bffu.ancestor_oid) g.gene_oid AS ance_gene, bffu.ancestor_oid AS ance_oid, mf.feature_name AS feature_name, mf.feature_id AS feature_id, ef.name AS evidence, qf.name AS qualifier  
                FROM gene2biological_function_feature AS g2bff  
                INNER JOIN biological_function_feature AS bff  
                ON g2bff.biological_function_feature_oid = bff.biological_function_feature_oid   
                INNER JOIN biological_function_feature_unfolded AS bffu  
                ON bffu.term_oid = bff.biological_function_feature_oid  
                INNER JOIN metadata.feature AS mf ON bff.feature_type = mf.feature_id   
                INNER JOIN gene AS g ON g2bff.gene_oid = g.gene_oid  
                LEFT JOIN flag.evidence_flags AS ef ON g2bff.evidence = ef.id  
                LEFT JOIN flag.qualifier_flags AS qf ON g2bff.qualifier = qf.id     
                LEFT JOIN flag.inferred_flags AS fif ON fif.id = g2bff.inferred    
                WHERE g.gene_oid=(4060969) 
                AND (bff.is_obsolete NOT IN ('t') OR bff.is_obsolete IS NULL)    
                AND (g.is_obsolete NOT IN ('t') OR g.is_obsolete IS NULL)
                AND distance=1   
                 UNION 
                SELECT DISTINCT ON (g.gene_oid, bff.biological_function_feature_oid)   
                g.gene_oid AS ance_gene, bff.biological_function_feature_oid AS ance_oid, 
                mf.feature_name AS feature_name, mf.feature_id AS feature_id,  
                ef.name AS evidence, 
                qf.name AS qualifier   
                FROM gene2biological_function_feature AS g2bff  
                INNER JOIN biological_function_feature AS bff  
                ON g2bff.biological_function_feature_oid = bff.biological_function_feature_oid    
                INNER JOIN metadata.feature AS mf ON bff.feature_type = mf.feature_id    
                INNER JOIN gene AS g ON g2bff.gene_oid = g.gene_oid  
                LEFT JOIN flag.evidence_flags AS ef ON g2bff.evidence = ef.id  
                LEFT JOIN flag.qualifier_flags AS qf ON g2bff.qualifier = qf.id     
                LEFT JOIN flag.inferred_flags AS fif ON fif.id = g2bff.inferred    
                WHERE g.gene_oid=(4060969) 
                AND (bff.is_obsolete NOT IN ('t') OR bff.is_obsolete IS NULL)   
                AND (g.is_obsolete NOT IN ('t') OR g.is_obsolete IS NULL)  
                AND ef.name NOT IN ('IEA', 'ND') 
            ) 
        AS unf ON unf.ance_oid = bffu.ancestor_oid 
        INNER JOIN gene AS gK on gk.Gene_oid=ance_gene 
        INNER JOIN biological_function_feature AS bffK 
        ON bffK.biological_function_feature_oid = bffu.ancestor_oid 
        LEFT JOIN flag.evidence_flags AS efK  
        ON evidence = efK.name 
        LEFT JOIN flag.qualifier_flags AS qfK  
        ON qualifier = qfK.name 
        WHERE bffu.distance=1 AND (bff.biological_function_feature_oid) IN (217538)
        AND evidence NOT IN ('IEA', 'ND')   
        UNION 
        SELECT COUNT(bffK.biological_function_feature_oid) 
        FROM biological_function_feature AS bff 
        INNER JOIN biological_function_feature_unfolded AS bffu 
        ON bff.biological_function_feature_oid = bffu.term_oid 
        INNER JOIN (    SELECT DISTINCT ON (g.gene_oid, bffu.ancestor_oid) g.gene_oid AS ance_gene, bffu.ancestor_oid AS ance_oid, mf.feature_name AS feature_name, mf.feature_id AS feature_id, ef.name AS evidence, qf.name AS qualifier  
                FROM gene2biological_function_feature AS g2bff  
                INNER JOIN biological_function_feature AS bff  
                ON g2bff.biological_function_feature_oid = bff.biological_function_feature_oid   
                INNER JOIN biological_function_feature_unfolded AS bffu  
                ON bffu.term_oid = bff.biological_function_feature_oid  
                INNER JOIN metadata.feature AS mf ON bff.feature_type = mf.feature_id   
                INNER JOIN gene AS g ON g2bff.gene_oid = g.gene_oid  
                LEFT JOIN flag.evidence_flags AS ef ON g2bff.evidence = ef.id  
                LEFT JOIN flag.qualifier_flags AS qf ON g2bff.qualifier = qf.id     
                LEFT JOIN flag.inferred_flags AS fif ON fif.id = g2bff.inferred    
                WHERE g.gene_oid=(4060969) 
                AND (bff.is_obsolete NOT IN ('t') OR bff.is_obsolete IS NULL)    
                AND (g.is_obsolete NOT IN ('t') OR g.is_obsolete IS NULL)
                AND distance=1   
                 UNION 
                SELECT DISTINCT ON (g.gene_oid, bff.biological_function_feature_oid)   
                g.gene_oid AS ance_gene, bff.biological_function_feature_oid AS ance_oid, 
                mf.feature_name AS feature_name, mf.feature_id AS feature_id,  
                ef.name AS evidence, 
                qf.name AS qualifier   
                FROM gene2biological_function_feature AS g2bff  
                INNER JOIN biological_function_feature AS bff  
                ON g2bff.biological_function_feature_oid = bff.biological_function_feature_oid    
                INNER JOIN metadata.feature AS mf ON bff.feature_type = mf.feature_id    
                INNER JOIN gene AS g ON g2bff.gene_oid = g.gene_oid  
                LEFT JOIN flag.evidence_flags AS ef ON g2bff.evidence = ef.id  
                LEFT JOIN flag.qualifier_flags AS qf ON g2bff.qualifier = qf.id     
                LEFT JOIN flag.inferred_flags AS fif ON fif.id = g2bff.inferred    
                WHERE g.gene_oid=(4060969) 
                AND (bff.is_obsolete NOT IN ('t') OR bff.is_obsolete IS NULL)   
                AND (g.is_obsolete NOT IN ('t') OR g.is_obsolete IS NULL)  
                AND ef.name IN ('IEA', 'ND') 
            ) 
        AS unf ON unf.ance_oid = bffu.ancestor_oid 
        INNER JOIN gene AS gK on gk.Gene_oid=ance_gene 
        INNER JOIN biological_function_feature AS bffK 
        ON bffK.biological_function_feature_oid = bffu.ancestor_oid 
        LEFT JOIN flag.evidence_flags AS efK  
        ON evidence = efK.name 
        LEFT JOIN flag.qualifier_flags AS qfK  
        ON qualifier = qfK.name 
        WHERE bffu.distance=1 AND (bff.biological_function_feature_oid) IN (217538)
        AND evidence IN ('IEA', 'ND')

Hi guys. I did not solved my problem, but I changed the query in order to get 2 rows and two columns as output, and then the ResultSet worked fine. I changed the query SELECT parts this way: SELECT COUNT(bffK.biological_function_feature_oid), 'IEA ND' FROM [...] SELECT COUNT(bffK.biological_function_feature_oid), 'NON-IEA NON-ND' FROM [...]. The output became: first row 'IEA ND' | 0 ; second row: 'NON-IEA NON-ND'| 1. Thanks anyway — DavideChicco.it
– DavideChicco.it, Commented Nov 26, 2013 at 2:54

harryr · Accepted Answer · 2013-11-21 03:27:32Z

1

List is an interface, try using something like ArrayList

List< Short > lista = new ArrayList< Short >();

_rs = _stmt.executeQuery(query);

     while (_rs.next()) {

lista.add((short)_rs.getShort(1));

}

answered Nov 21, 2013 at 3:27

harryr

1266 bronze badges

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

1 Comment

harryr Over a year ago

That query is too scary for me. Its not the code snippet, which works for me.

indika · Accepted Answer · 2013-11-21 17:08:20Z

1

List is an interface, therefore you cannot instantiate directly rather than using its one of implementation like ArrayList. Also note that count is a keyword in PG, so avoid using keyword as identifiers. Working solution:

pst = connection.prepareStatement("SELECT * FROM demo");
List<Short> lista = new ArrayList<Short>();
rs = pst.executeQuery();

while (rs.next()) {
   lista.add((short)rs.getInt(1));
}

answered Nov 21, 2013 at 17:08

indika

9458 silver badges15 bronze badges

Comments

Michael · Accepted Answer · 2013-11-21 03:14:40Z

0

It doesn't seem to be an issue with syntax. Do you also get one row when you run the query against the database? Perhaps you can post your SQL query too.

answered Nov 21, 2013 at 3:14

Michael

4332 silver badges8 bronze badges

1 Comment

DavideChicco.it Over a year ago

I've inserted the query and some code details about connection and statement. Maybe are they the problem?

Collectives™ on Stack Overflow

Java SQL query execution, problems in reading the ResultSet rows

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related