database access with JDBC and Java - without modelling tables and columns

Question

I want to read and write database tables via JDBC without having to tell my Java code anything about the tables except their names.

The only processing that I need to do with the data is comparison and read/write. See below for details.

I'm essentially looking for something like:

A = connect_db();
B = connect_db();
while (row = read_next_row(A)) {
    if (A.something == whatever) {
        insert_row(B, row);
    }
}

In something like PHP that would be easy, I'd read a row into an associative array, done. In Java, this seems overly complex, but I'm not good in Java so I might be missing an obvious, easy solution.

This is all for a low-level, database-first tool that is comparing and copying data between multiple databases. I can assume that the tables are identical among the DBs, with the exception of data types (one DB might call it String and the other Varchar) and additional (internal calculation) columns in some sources that I can safely ignore. The reason I'm looking for a coding solution is that the database systems are different (but all have JDBC connectors).

However, my users are able to change the database structure, so my tool needs to be independent of the column names and types, except for a few columns like a primary ID which is always guaranteed to exist. But, for example, the table about people contains first and last names (and other things) and I cannot guarantee that it won't in the future contain a middle name column. I'd like to not have to change the very simply copy tool for every DB structure change.

What am I missing? Is this possible in Java/JDBC?

@Abra I thought that ResultSetMetaData is probably the way to go. I'm not sure how to build a dynamically typed list, dictionary or array to put the stuff in, however. Not doing much Java development. — Tom
– Tom, Commented Mar 6, 2021 at 12:47

Mark Rotteveel · Accepted Answer · 2021-03-06 14:56:39Z

2

When querying, you can inspect ResulSetMetaData to discover the column names (or labels) of a query.

The equivalent of a PHP associative array is a Map.

A simple example where you already know the table name:

public List<Map<String, Object>> getValues(String tableName, Connection connection) {
  try (var stmt = connection.createStatement();
       var rs = stmt.executeQuery("select * from " + stmt.enquoteIdentifier(tableName, true))) {
    var result = new ArrayList<Map<String, Object>>();
    var rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    while (rs.next()) {
      var row = new HashMap<String, Object>>(columnCount);
      for (int idx = 1; idx <= columnCount; idx++) {
        // or use getColumnLabel if you use a query that can have aliases
        row.put(rsmd.getColumnName(idx), rs.getObject(idx));
      }
      result.add(row);
    }
    return result;
  }
}

Note that this returns all the rows of a table, so for very large tables, this is not efficient in terms of memory. So for processing large tables, you'll need to look at different solutions, like returning a Stream, or passing a consumer for rows (e.g a Consumer<Map<String, Object>>).

For more advanced forms you can use DatabaseMetaData.getTables to discover the available tables, and optionally DatabaseMetaData.getColumns to discover the columns of tables.

edited Mar 6, 2021 at 14:56

answered Mar 6, 2021 at 14:51

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Over a year ago

This works brilliantly. I'd like to add that you can also get the column type by accessing getColumnType and getColumnTypeName.

Basil Bourque Over a year ago

Looks like >> is a typo in new HashMap<String, Object>>

Collectives™ on Stack Overflow

database access with JDBC and Java - without modelling tables and columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related