Databases
StackOverflow2013
Postsdbo
POJava SQL query execution, problems in reading the ResultSet rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJava SQL query execution, problems in reading the ResultSet rows
    primarykey
    data
    text
    <p>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:</p> <p><img src="https://i.stack.imgur.com/1OASu.png" alt="enter image description here"></p> <p>My problem is that I cannot find any way to read these two rows. My reading code is:</p> <blockquote> <p>_conn.setReadOnly(false);</p> <p>_stmt = _conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);</p> <p>_stmt.setFetchSize(100);</p> <p>List&lt; Short > lista = new List&lt; Short >();</p> <p>_rs = _stmt.executeQuery(query);</p> <p>while (_rs.next()) {</p> <p>lista.add((short)_rs.getInt(1)); </p> <p>}</p> </blockquote> <p>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...</p> <p>How to read both the rows?</p> <p><strong>EDIT</strong>: 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:</p> <pre><code> 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') </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDavideChicco.it
    1. USDavideChicco.it
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat's the `query` look like?
      singulars
      1. POJava SQL query execution, problems in reading the ResultSet rows
      1. USMadProgrammer
    2. COHi 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
      singulars
      1. POJava SQL query execution, problems in reading the ResultSet rows
      1. USDavideChicco.it
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload