JDBC - Run a query and use its return as a parameter for another query

Question

I am learning to code in java, alone with the help of tutorials and I am trying to do a procedure where the result of a query will be a clause where for a second consultation.

In the example below, I have a connection that performs a query, and takes the result and stores it in a variable String hash.

I want to take this result and create a new query.

I can’t do it. I tried, but it’s a mistake.

package br.com.jdbc;

import java.sql.*;

public class ConectaBanco {

    public static void main(String [] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");             
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:user/pass@host:1522/base");

            Statement stmt = con.createStatement();
            String sql ="Select * from acesso where nrdocumento ='999999999999'";
            ResultSet res = stmt.executeQuery(sql);

            while (res.next()); {
                String hash = res.getString(3);
                System.out.println(hash);
            }

            con.close();
        } catch (Exception e ) {
            e.printStackTrace();
        }
    }
}

Answer 1

The first point to note is an extra semicolon in your while:

            while (res.next()); {
                String hash = res.getString(3);
                System.out.println(hash);
            }

Look at that semicolon after the (res.next())? So he’s gonna make your show go crazy, he’s gonna consume all the ResultSet doing nothing with it and then trying to produce the hash already being beyond the last position, popping a SQLException.

By the way, why do you wear one while instead of a if? There is not much point in reading several results of this if only the last will matter.

The second point is that you should use Try-with-Resources. See in this link the reasons for this and how to do.

Also, give to move the process to make the connection in a method to part, because you will surely need to do this several times and it is not good to stay copying-and-cheating codes.

The driver boot process, using the Class.forName is no longer required in the latest versions of Java. However, in doing so, you immediately denounce the presence of eventual classpath errors. Also, it only needs to be done once, while loading the program, and if it fails, the program is hopelessly broken. Therefore, this process can be kept in a static boot block.

I don’t know what query you’re going to use the hash on, but I’m going to assume it’s a "SELECT nome, idade FROM tabela WHERE campo = <hash>". The ideal is to use PreparedStatements to avoid problems of injection of SQL. The idea is that you already have a SQL ready and parameterizable, with parts denoted with ? representing wildcards to be filled and specified at the appropriate time. Here’s how to do:

package br.com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConectaBanco {
    private static final String SQL_ACESSO_DOC ="SELECT * FROM acesso WHERE nrdocumento ='999999999999'";

    private static final String SQL_HASH ="SELECT nome, idade FROM tabela WHERE campo = ?";

    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitilizerError(e);
        }
    }

    private static Connection conectar() throws SQLException {
        return DriverManager.getConnection("jdbc:oracle:thin:user/pass@host:1522/base");
    }

    public static void main(String[] args) throws SQLException {
        try (
            Connection con = conectar();
            PreparedStatement stmt = con.prepareStatement(SQL_ACESSO_DOC);
            PreparedStatement stmtHash = con.createStatement(SQL_HASH);
        ) {
            String hash = "";
            try (ResultSet res = stmt.executeQuery()) {
                if (res.next()) {
                    hash = res.getString(3);
                    System.out.println(hash);
                }
            }
            stmtHash.setString(1, hash);
            try (ResultSet res = stmtHash.executeQuery()) {
                while (res.next()) {
                    String nome = res.getString(1);
                    int idade = res.getInt(2);
                    System.out.println(nome + " tem " + idade + " anos.");
                }
            }
        }
    }
}