Company management app: Extracting data with JDBC and java.sql

Question

I'm currently working on a company management system as a university project, and I have to get my hands in java.sql.
I have come to struggle with extracting a query result from a ResultSet object, as I find its usage is a bit complex. I have written this code to automatically extract all the entries of a ResultSet in a List<Map<String, String>>; this solution surely works, but the usage of this API looks really complicated, wordy and not so intuitive to me.

The code you are seeing below is the one related to checking if id, name and password inserted by the user matches the data stored in the database (and is the one using the API discussed above):

/**
 * Estrae tutte le righe del resultSet specificato, convertendole in mappe (nome_colonna, valore_colonna).
 */
private List<HashMap<String, String>> extractResults(ResultSet resultSet) throws SQLException {
 var results = new ArrayList<HashMap<String, String>>();
 /* Fino a quando c'è un'altra riga, vacci ed estrai i risultati in una mappa */
 while (resultSet.next()) {
 var rowMap = extractRow(resultSet);
 results.add(rowMap); /* Aggiungi la mappa all'array di mappe */
 }
 return results;
}
/**
 * Estrae una riga dal resultSet specificato e la converte in una mappa (nome_colonna, valore_colonna).
 */
private HashMap<String, String> extractRow(ResultSet resultSet) throws SQLException {
 var labels = getColumnLabels(resultSet);
 var rowMap = new HashMap<String, String>();
 /* Per ogni colonna del risultato */
 for (String label : labels) {
 rowMap.put(label, resultSet.getString(label));
 }
 return rowMap;
}
/**
 * Ottiene i nomi delle colonne del resultSet specificato.
 */
private String[] getColumnLabels(ResultSet resultSet) throws SQLException {
 var meta = resultSet.getMetaData();
 var labels = new ArrayList<String>();
 /* 1 ... il numero delle colonne + 1, poiché gli indici vanno da 1 */
 for (var i = 1; i < meta.getColumnCount() + 1; i++) {
 labels.add(meta.getColumnLabel(i));
 }
 return labels.toArray(new String[0]);
}
/**
 * Ritorna vero se il resultSet specificato è vuoto.
 */
private boolean isResultEmpty(ResultSet resultSet) {
 try {
 return !resultSet.isBeforeFirst();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return true;
} 

This one is instead the method that fetches the db data and compares it with the inserted one:

 /**
 * Verifica che le credenziali specificate esistano e corrispondano con quelle nel database.
 * @param id la matricola da controllare
 * @param name il nome da controllare
 * @param surname il cognome da controllare
 * @return true se le credenziali corrispondono, false altrimenti
 * @throws SQLException se si verifica un errore di qualunque tipo, in relazione al database
 */
public boolean checkCredentials(String id, String name, String surname) throws SQLException {
 try (
 var st = connection.prepareStatement("""
 select w.ID, w.workerName, w.workerSurname
 from worker w
 where w.ID = ?
 """)
 ) {
 st.setString(1, id);
 var resultSet = st.executeQuery();
 if (isResultEmpty(resultSet)) {
 /* Se la query ha ritornato l'insieme vuoto, la matricola non esiste */
 return false;
 } else {
 /* Altrimenti ottieni le credenziali dal resultSet e controlla che corrispondano */
 List<HashMap<String, String>> maps = extractResults(resultSet);
 assert maps.size() == 1; /* Dovrebbe esserci solo una tupla nel risultato */
 HashMap<String, String> result = maps.get(0);
 var dbId = result.get("ID");
 var dbName = result.get("workerName");
 var dbSurname = result.get("workerSurname");
 return id.equals(dbId) && name.equals(dbName) && surname.equals(dbSurname);
 }
 }
}

Just to summarize, my question is: does this code follow common best practices in the java.sql word, or is this solution a bad one? Am I not seeing a much simpler way to extract and store the db data in a Java application?

• Margon: What you are looking for is literally a DTO (document to object) method to convert a document (ResultSet) to an actual list of object In your example you are doing this manually, but there are methods that does it for you for free (see reflection like this) If you do something like this it'll be more clear and re-usable
• Eric Stein: DTO stands for "Data Transfer Object". A ResultSet is not a document by any reasonable English definition of "document". That said, I agree that transferring ResultSet data into model objects is preferable to putting them in a Map. An ORM is probably too heavy for this application, but the accepted answer in the linked question is a good way to go about solving the problem.
• pacmaninbw: Welcome to the Code Review Community. The title of the question should be what the code does rather than your concerns about the code, an example may be company management system. Please read How do I ask a good question?. Questions that ask specifically about best practices may be considered off-topic and closed. We provide general code reviews to help you improve your coding skills. You might get a better response if you translated the comments to English.
• Mast: In what language are your comments, Italian?
• Thomas Herondale: Yes, I am Italian

Answer 1

I have a couple of more direct alternatives. Depending of a larger context.

public boolean checkCredentials(String id, String name, String surname) throws SQLException {
 var sql = """
 select w.workerName, w.workerSurname
 from worker w
 where w.ID = ?
 """;
 try (var st = connection.prepareStatement(sql)) {
 st.setString(1, id);
 try (var resultSet = st.executeQuery()) {
 record Worker(String id, String name, String surname) { };
 if (resultSet.next()) {
 Worker worker = new Worker(id, resultSet.getString(2), resultSet.getString(3));
 return worker.equals(new Worker(id, name, surname));
 }
 return false;
 }
 }
}
public boolean checkCredentials(String id, String name, String surname) throws SQLException {
 var sql = """
 select 1
 from worker w
 where w.ID = ? and m.name = ? and surname = ?
 """;
 try (var st = connection.prepareStatement(sql)) {
 st.setString(1, id);
 st.setString(2, name);
 st.setString(3, surname);
 try (var resultSet = st.executeQuery()) {
 return resultSet.next();
 }
 }
}
public boolean checkCredentials(String id, String name, String surname) throws SQLException {
 var sql = """
 exists (select *
 from worker w
 where w.ID = ? and m.name = ? and surname = ?)
 """;
 try (var st = connection.prepareStatement(sql)) {
 st.setString(1, id);
 st.setString(2, name);
 st.setString(3, surname);
 try (var resultSet = st.executeQuery()) {
 return resultSet.next() && resultSet.getBoolean(1);
 }
 }
}

• try-with-resources will ensure closing of the variable, even when an exception is thrown, or a return happens. Needed for statement and result set.
• Shuffling a record into a Map results in an ugly usage, often "improved" by constants for map keys (fields). It also poses an overhead. Granted a map is more generally usable, but it is awkward when needing to do something with the fields.
• A List is also not needed. Assuming that ID is the primary key. BTW use interfaces Map and List rather than the implementing classes. This allows combinations of List.of(...) and is more versatile.
• Here I offer an alternative of a record class, which also offers an equals.
• Best would be to leave the check to the database.

The other methods are not needed. Laudable is the multiline string for the SQL. Of course the alias w is not needed here.

One questionable issue is whether the ID does not suffice. I assume that name and surname are extra checks against stealing others' IDs or such.

• Thomas Herondale: I thought of leaving the check to the database too. I assume from your answer that it would be best.
• Thomas Herondale: This solution works perfectly for checking the credentials. But for other tables, should I create a data class for each one of them? Because I thought of that too, but for things like safety question and its answer It seemed to me a bit of a Stretch...
• Thomas Herondale: The List Is there in a view of reuse, as I wanted to use these methods each time I had to extract some result, e.g. a list of all the workers
• Joop Eggen: For almost every query Generation a DTO, Data Transfer Object, is a common practice. So many classes. Unfortunately, but it is named and _typed _ (by field), as opposed to Map.
• Joop Eggen: I empathize with the thought of reuse, but the common code is small: there is extra SQL string, parameters, and (maybe at the call) type conversion. I would also like point to the JPA alternative (eclipseLink, hibernate).