Robert February 2016

How can I check that Java JDBC code uses the right column names?

I have a test suite of end-to-end tests. They are supposed to catch typos in SQL statements, bad table or column names (anything where DB schema and Java code disagree), or missing DB permissions. I don't want to rely on data in the database (too complicated to set up); this is just a basic test.

import java.sql.*;
import org.junit.Test;

public class TypoTest {
    private Connection getConnection() throws Exception {
        String connectionString = "jdbc:postgresql://127.0.0.1:5432/db";
        String driverClassName = "org.postgresql.ds.PGConnectionPoolDataSource";
        Class.forName(driverClassName).newInstance();
        return DriverManager.getConnection(connectionString, "robert", "");
    }

    @Test
    public void runQuery() throws Exception {
        try (Connection connection = getConnection();
             PreparedStatement ps = connection.prepareStatement("SELECT relname FROM pg_catalog.pg_class");
             ResultSet data = ps.executeQuery()) {
            while (data.next()) {
                data.getString("relname");
            }
        }
    }
}

When I run the above test, it fails if I have a typo in the SELECT statement. (Good.) If I have a typo in the column name in data.getString("typo here"), that won't get caught if the table queried does not have data because then the loop is never entered. To keep the test (setup) simple, I don't want to insert data into my tables first.

I guess I could make the column names into constants and DRY up my code and get rid of the problem.

However, I am wondering if there is an easier way... I am lazy and don't want to edit all my queries. Is there a better way to unit-test my SQL?

I am using Postgres 9.5 and JDBC 4.

Answers


Ravindra HV February 2016

I guess you already have the answer you seek but just for the sake of answering, you can try using result-set-metadata by using a select * from table and then checking the column names against your query (you'd have to parse the query string I guess...).

I believe it will work for empty tables as well but do note that I have not tested the empty table scenario.

Post Status

Asked in February 2016
Viewed 1,172 times
Voted 10
Answered 1 times

Search




Leave an answer