Ways to get bank values with ResultSet

Question

Ways to get bank values with ResultSet

Navigation

#1 by (3 votes)

3

Ways to get Java database values (JDBC).

 public static Connection getConnection(){
        /* 1. Faz a conexao com a base de dados */
        /* 2. Retorna qual banco ira trabalhar */

        Connection connection=null;

        try {
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/base", "postgres", "postgres");
            System.out.print("Conexao com o banco de dados efetuada com sucesso!");

        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            System.out.print("Erro durante a conexao com o banco de dados!");
        }

        return connection;
    }

Map<String,String> fields_values = new HashMap<String,String>(); // Chave: Valor

String sql = "SELECT id, nome, idade, endereco FROM PESSOA";

Connection conexao = getConnection(); // Chama método para retornar conexão com o banco
prepared = conexao.prepareStatement(sql); // Prepara o SQL para ser executado no banco

ResultSet result = prepared.executeQuery(sql); // Retorno do banco de dados

I believe there must be another way to get the values from the database without having to use methods like result.getInt(), result.()getString, result.getFloat() .

Would I be able to get the values from the database otherwise?

Can I get the values in a simpler way to store in the variable fields_values ?

java query jdbc

asked by anonymous 20.06.2015 / 00:47

1 answer

Hibernate - duplicate key value violates unique constraint Python 3.5.0 ::: Erase a Label on Tkinter

score 3 · Accepted Answer

Yes, you can retrieve ResultSetMetaData from ResultSet and using getColumnCount() you would retrieve the number of columns to then retrieve the values of ResultSet by the position of the column.

To recover the ResultSetMetaData and the amount of columns you should do this:

As you are using a strings map to store the values a form of values would be this:

final ResultSetMetaData metaRS = rs.getMetaData();
final int columnCount = metaRS.getColumnCount();

Then we will have to retrieve all the values of the columns as a Object and call toString of it, because we may not know the exact data type in the database. As we do not have the name either, we will pick up the indice, something like this:

while (rs.next()) {
    for (int i = 1; i <= columnCount; i++) {
        final Object value = rs.getObject(i);
        fieldsValues.put(metaRS.getColumnName(i), value.toString());
    }
}

If you want to print your map values to see if they are OK you can do something like this:

final Set<Map.Entry<String, String>> entries = fieldsValues.entrySet();
for (final Map.Entry<String, String> entry : entries) {
    System.out.println(String.format("Key: %s | Value: %s", entry.getKey(), entry.getValue()));
}

That will generate something like this:

Key: id | Value: 1
Key: idade | Value: 25
Key: nome | Value: Nome 1
Key: endereco | Value: Endereco 1

Note also that in this case it is only possible to store to a line, since it will always overwrite the values of your map.

A complete example would be this:

final Map<String, String> fieldsValues = new HashMap<>();

final Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/base", "postgres", "postgres");
final String sql = "SELECT id, nome, idade, endereco FROM PESSOA";
final PreparedStatement ps = conn.prepareStatement(sql);
final ResultSet rs = ps.executeQuery();

final ResultSetMetaData metaRS = rs.getMetaData();
final int columnCount = metaRS.getColumnCount();

while (rs.next()) {
    for (int i = 1; i <= columnCount; i++) {
        final Object value = rs.getObject(i);
        fieldsValues.put(metaRS.getColumnName(i), value.toString());
    }
}