JDBC - Execute a query and use its return as a parameter to another query

Question

JDBC - Execute a query and use its return as a parameter to another query

Navigation

#1 by (3 votes)

2

I am learning to program 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 where clause for a second query.

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

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

I can not do this. I tried, but it only makes 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();
        }
    }
}

java jdbc

asked by anonymous 14.02.2017 / 22:40

1 answer

What is the purpose and use of the GuidAttribute? Insert MySQL data with .txt file

score 3 · Answer 1

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

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

Do you see that semicolon after (res.next()) ? Then it will make your program look crazy, it will consume all ResultSet without doing anything with it and then try to produce the hash already being beyond the last position, popping a SQLException .

Incidentally, why do you use while instead of if ? There is not much sense in reading up various results of this if only the latter will matter.

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

In addition, you have to move the process to make the connection in a separate method, as you will probably need to do this several times and it is not good to keep copying-and-pasting codes.

The driver initialization process, using Class.forName is no longer required in newer versions of Java. However, in doing so anyway, you denounce immediately for the presence of any classpath errors. In addition, 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 maintained in a static boot block.

I do not know what query you're going to use the hash, but I'll assume it's a "SELECT nome, idade FROM tabela WHERE campo = <hash>" . Ideally, % s of% s should be used to avoid SQL injection . The idea is you already have a prepared and parameterizable SQL, with parts denoted with PreparedStatement representing wildcards to be filled and specified in a timely manner. Here's how:

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.");
                }
            }
        }
    }
}