Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Effectively Work With a Relational Database Using Java JDBC

DZone's Guide to

How to Effectively Work With a Relational Database Using Java JDBC

In this article, explore a convenient class from the Java UjoTools library called JdbcBuilder.

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

If you don’t want to use any of the ORM frameworks to implement database queries and feel like even the JdbcTemplate Spring Tool isn’t right for you, try the JdbcBuilder class from the UjoTools project.

Anyone who’s ever programmed SQL queries through the JDBC library has to admit the interface isn’t very user-friendly. Maybe that's why a whole array of libraries has emerged, varying in both list of services provided and degree of complexity. In this article, I’d like to show you a convenient class from the Java UjoTools library called JdbcBuilder. Its purpose is to help with assembling and executing SQL statements — nothing more, nothing less. JdbcBuilder class doesn’t address mapping of results to JavaBeans, doesn’t address optimization, nor does it provide a database connection.

Key Benefits of Using JdbcBuilder Class

  • Using the JdbcBuiler class eliminates frequent errors of wrong ordered parameters, especially when the order of parameters is changed inside the SQL statement
  • The solution is based on a single class that easily allows you to get the PreparedStatement object
  • The possibility to get a preview of the assembled SQL query including parameter values using toString() at any time

Let’s take a look at few examples of JdbcBuilder use with some of the jUnit test methods:

We’ll Start With Using a SQL SELECT Statement:

JdbcBuilder sql = new JdbcBuilder()
    .write("SELECT")
    .column("t.id")
    .column("t.name")
    .write("FROM testTable t WHERE")
    .andCondition("t.name", "=", "A name")
    .andCondition("t.created", ">=", someDate);
for (ResultSet rs : sql.executeSelect(dbConnection)) {
    int id = rs.getInt(1);
    String name = rs.getString(2);
}

The JdbcBuilder class keeps fragments of the SQL statement internally and keeps the required SQL parameters as well. When demanded, both groups will be merged into a result type of PreparedStatement. The method write() inserts any text separated by the initial space. Each elementary condition (with the query parameter) is inserted using a separate method. The method toString() returns a rough preview of the SQL query including the attached parameters, but such result is not suitable for processing in JDBC using the method getSql(). The result of the SELECT statement can alternatively be obtained by calling the executeSelect() method, the resulting iteration can be used e.g. in the loop for(). If we pass the entire collection of results to the end, the Iterator closes all of its internal resources (this doesn’t concern the database connection). However, it isn’t a good idea to rely on automatic closure (the method close()) as it doesn’t cover cases of premature cycle termination due to application logic or eventual exception.

The Second Example Shows a SQL INSERT Statement:

JdbcBuilder sql = new JdbcBuilder()
    .write("INSERT INTO testTable (")
    .columnInsert("id", 10)
    .columnInsert("name", "A name")
    .columnInsert("created", someDate)
    .write(")");
sql.executeUpdate(dbConnection);

Here we can see a similar concept of inserting a parameter along with the name of the database column. None of the executive methods call the commit() explicitly. I thought that the SQL keywords are unnecessarily duplicated, so I moved them to a separate SQL interface as constants. An example with SQL constants is more resistant against typos but still can’t do anything about invalid syntax.

The Last Example Shows a SQL UPDATE Statement:

JdbcBuilder sql = new JdbcBuilder()
   .write(Sql.UPDATE)
    .write("testTable")
    .write(Sql.SET)
    .columnUpdate("created", someDate.plusDays(1))
    .write(Sql.WHERE)
    .andCondition("id", Sql.IN, 10, 20, 30)
    .andCondition("created = (?)", null, someDate)
    .andCondition("name", Sql.IS_NOT_NULL);
sql.executeUpdate(dbConnection);

The DELETE command works similarly. If we need to use an SQL function, we can write the entire SQL condition to the first argument and mark the position of SQL parameter with a question mark (?). The second parameter of the method is then assigned an undefined value null, the last argument remains unchanged. In case the proposed API still doesn’t suit us, it’s possible to add a custom method to a JdbcBuilder class child that gains access to all internal attributes. See more examples in a jUnit test class.

The JdbcBuilder class can be used by the Ujo-tools dependency on the Maven artifact. Current version 1.88 is available at the size of just 38kB.

<dependency>
    <groupId>org.ujorm</groupId>
    <artifactId>ujo-tools</artifactId>
    <version>1.88</version>
</dependency>

The author of this article is also an author of the ORM framework Ujorm built on KEY-VALUE. With that said, the module mentioned in this article isn’t dependent on such objects and neither is dependent on any other Maven module.

Related Links

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

Topics:
java ,jdbc ,database ,relational database ,java jdbc tutorial ,sql insert

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}