JDBC Query Builder Tutorial

Handling SQL within a Java application can be tricky. For one thing, Java does not support multi-line string constants, so developers can end up with code that looks like this:
 
 
 
 
 
 
 
 
 

 
Sample Code

String sql_query = "select *" + "from user_table" + "where name like 'Fred%'";

This code is not just ugly but also error-prone. Did you notice the missing space between user_table and where? A further challenge when working with SQL in Java is that we often need to build the SQL dynamically.

1. Introduction

In this JDBC Query Builder example, we will see how to achieve dynamic SQL Query Builder phenomenon by using the open-source Sqlbuilder library. But before moving ahead let’s take a look and understand JDBC and Sqlbuilder library.

1.1 What is JDBC?

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. Using JDBC one can send statements to almost any relational database. Thus, JDBC is a Java API for executing the SQL statements and supports basic SQL functionality.

The JDBC library includes API for each of the tasks commonly associated with the database usage,

• Making a connection to the database.
• Creating SQL statements.
• Executing SQL queries in the database.
• Viewing and Modifying the resulting records.

1.2 What are JDBC CRUD Operations?

CRUD means the basic operations to be done in a data repository. We directly handle records or data objects; apart from these operations, the records are passive entities. CRUD stands for Create, Read, Update and Delete. The CRUD functions are the user interfaces to databases, as they permit users to create, view, modify and alter data. CRUD works on entities in databases and manipulates these entities.

For instance, a student database table adds (creates) new student details, accesses (reads) existing student details, modifies (updates) existing student data such as subjects, and deletes student details when students leave the school.

The commands corresponding to these operations in SQL are INSERT, SELECT, UPDATE and DELETE. INSERT adds new records, SELECT retrieves or selects existing records based on selection conditions, UPDATE modifies existing records, and DELETE removes tables or records in a table.

1.2.1 CRUD Benefits

Using the database operations in an application has some advantages i.e.

• Improves data security and data access to users by using host and query languages.
• Greater data integrity and independence of applications programs.
• Improves application performance by reducing the data redundancy.

1.3 SqlBuilder

SqlBuilder is a library which attempts to take the pain out of generating SQL queries within the Java programs. Using one programming language (Java) to generate code for another language (i.e. SQL) is always a challenge.

There are always issues with the escaping characters within the string literals, getting spaces in the right place, and getting the parentheses to match up. And often, even after the code is debugged and fully tested, it is still very fragile. The slightest change will throw things out of balance and require another round of testing and tweaking.

SqlBuilder changes that whole scenario by wrapping the SQL syntax within very lightweight and easy to use Java objects which follow the builder paradigm (similar to StringBuilder in Java). This changes many common SQL syntactical runtime errors into Java compile-time errors.

Let’s dive right into some quick examples to see how it all works.

1.3.1 Sqlbuilder SelectQuery Example

A fairly simple SQL select query embedded in a Java program might currently look something like this:

Select Query

String selectQuery = "SELECT " + T1_COL1 + "," + T1_COL2 + "," + T2_COL1 + " FROM " + TABLE1 + " " + T1 + " INNER JOIN " + TABLE2 + " " + T2 + " ON (" + T1_IDCOL + " = " + T2_IDCOL + ") ORDER BY " + T1_COL1;

Whenever this query is modified, developers will need to make sure there are sufficient commas, parentheses, spaces to generate the correct query, and the correct columns for the given tables and the correct aliases for those tables.

In this technique matching up the placeholders with the arguments is no simple task and simple rearrangements can easily mess up the resulting query string. Additionally, this is still not a viable solution for any sort of dynamic query generation.

Now, let’s see how this query looks using SqlBuilder classes.

Modified Select Query

// Assuming These Objects Have Already Been Created
Table table1, table2;
Column t1Col1, t1Col2, t2Col1;
Join joinOfT1AndT2;
 
String select_query = (new SelectQuery()).addColumns(t1Col1, t1Col2, t2Col1).addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2).addOrderings(t1Col1).validate().toString();

See how easy that was? Not a single embedded comma, space, or parenthesis to be seen! This is a much more readable version as compared to a previous version.

On top of that, we have a validate() method call slipped into the end of the SQL statement. This method call will verify that the columns and tables in the query actually make sense. Maintainability, readability, and verifiability all are wrapped into this easy to use package.

As a final note, the SqlBuilder package does not abstract away the knowledge necessary to deal with a database but instead provides tools for avoiding the error-prone parts of generating the SQL queries.

1.3.2 SqlBuilder Features

• Good portion of commonly used SQL, including,
  • SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, UNION.
  • Boolean Logic.
  • Numeric Expressions.
• Supports Query Validation for readability and verifiability.
• Helpers for managing PreparedStatement parameters (Query Preparer) and reading results (Query Reader).
• JDBC Escape syntax support.

1.4 Download and Install MySQL

In this example, we are using the MySQL database to perform the JDBC Query Builder operations. You can watch this video in order to download and install the MySQL database on your Windows operating system.

Now, open up the Eclipse IDE and let’s start building the application!

2. JDBC Query Builder Tutorial

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8, MySQL Database and Maven. Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

2.3 Project Creation

This section will demonstrate on how to create a Java Maven project with Eclipse. In Eclipse IDE, go to File -> New -> Maven Project.

In the New Maven Project window, it will ask you to select a project location. By default, ‘Use default workspace location‘ will be selected. Select the ‘Create a simple project (skip archetype selection)‘ checkbox and just click on next button to proceed.

It will ask you to ‘Enter the group and the artifact id for the project.’ We will input the details as shown in the below image. The version number will be by default 0.0.1-SNAPSHOT.

Click on Finish and the creation of a maven project will be completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcQueryBuilder</groupId>
	<artifactId>JdbcQueryBuilder</artifactId>
	<version>0.0.1-SNAPSHOT</version>
</project>

We can start adding the dependencies that developers want like MySQL, Log4J, and Sqlbuilder etc. Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application.

3.1 Database

This tutorial uses a database called tutorialDb. The database is not included when you create the project in eclipse so you need to first create the database to follow this tutorial:

• Create a new database tutorialDb as:

CREATE DATABASE IF NOT EXISTS tutorialDb;

• Use the created database tutorialDb to create table as:

USE tutorialDb;

If everything goes well, the database will be shown as below in the MySQL workbench.

3.2 Maven Dependencies

In this example, we are using the latest MySQL version (i.e. mysql-connector-java-5.1.21), Sqlbuilder and Log4J dependencies. The updated file will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcQueryBuilder</groupId>
	<artifactId>JdbcQueryBuilder</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.healthmarketscience.sqlbuilder/sqlbuilder -->
		<dependency>
			<groupId>com.healthmarketscience.sqlbuilder</groupId>
			<artifactId>sqlbuilder</artifactId>
			<version>2.1.7</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.16</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
	</build>
</project>

3.3 Java Class Creation

Let’s create the required Java files. Right click on src/main/java folder, New -> Package.

A new pop window will open where we will enter the package name as: com.jcg.jdbc.sql.query.builder.

Once the package is created, we will need to create the database operations and implementation classes. Right click on the newly created package, New -> Class.

A new pop window will open and enter the file name as: Querybuilder. The database operations class will be created inside the package: com.jcg.jdbc.sql.query.builder.

Repeat the step (i.e. Fig. 10) and enter the filename as QueryBuilderDemo. The implementation class will be created inside the package: com.jcg.jdbc.sql.query.builder.

3.3.1 Implementation of Db Operations Class

In JDBC, Connection is the session between Java application and database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData. This class also contains the code for creating dynamic SQL queries with the help of Sqlbuilder class. Let’s see the simple example of query management using Statement. Add the following code to it:

Querybuilder.java

package com.jcg.jdbc.sql.query.builder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;

import org.apache.log4j.Logger;

import com.healthmarketscience.sqlbuilder.BinaryCondition;
import com.healthmarketscience.sqlbuilder.CreateTableQuery;
import com.healthmarketscience.sqlbuilder.DeleteQuery;
import com.healthmarketscience.sqlbuilder.DropQuery;
import com.healthmarketscience.sqlbuilder.InsertQuery;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.healthmarketscience.sqlbuilder.UpdateQuery;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;

public class Querybuilder implements DbProperties {

	static ResultSet resObj;
	static Statement stmtObj;
	static Connection connObj;

	static DbSchema schemaObj;
	static DbSpec specficationObj;

	static DbTable table_name;
	static DbColumn column_1, column_2, column_3, column_4;

	public final static Logger logger = Logger.getLogger(Querybuilder.class);

	// Helper Method #1 :: This Method Is Used To Create A Connection With The Database
	public static void connectDb() {
		try {
			Class.forName(JDBC_DRIVER);
			connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS);
			logger.info("\n=======Database Connection Open=======\n");

			stmtObj = connObj.createStatement();
			logger.info("\n=======Statement Object Created=======\n");

			loadSQLBuilderSchema();
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
	}

	// Helper Method #2 :: This Method Is Used To Create Or Load The Default Schema For The SQLBuilder
	private static void loadSQLBuilderSchema() {
		specficationObj = new DbSpec();
		schemaObj = specficationObj.addDefaultSchema();
	}

	// Helper Method #3 :: This Method To Used To Close The Connection With The Database
	public static void disconnectDb() {
		try {
			stmtObj.close();
			connObj.close();
			logger.info("\n=======Database Connection Closed=======\n");
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
	}

	// SQLQueryBuilder #1 :: This Method Is Used To Perform The Create Operation In The Database
	public static void createDbTable() {
		logger.info("\n=======Creating '" +TABLE_NAME + "' In The Database=======\n");
		try {
			// Specifying Table Name
			table_name = schemaObj.addTable(TABLE_NAME);

			// Specifying Column Names For The Table
			column_1 = table_name.addColumn(COLUMN_ONE, Types.INTEGER, 10);
			column_2 = table_name.addColumn(COLUMN_TWO, Types.VARCHAR, 100);
			column_3 = table_name.addColumn(COLUMN_THREE, Types.INTEGER, 200);

			String createTableQuery = new CreateTableQuery(table_name, true).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ createTableQuery + "\n");
			stmtObj.execute(createTableQuery);
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n=======The '" + TABLE_NAME + "' Successfully Created In The Database=======\n");
	}

	// SQLQueryBuilder #2 :: This Method Is Used To Perform The Insert Operation In The Database
	public static void insertDataInTable(int id, String name, int salary) {
		String insertTableQuery;
		logger.info("\n=======Inserting Record In The '" + TABLE_NAME + "'=======\n");
		try {
			insertTableQuery = new InsertQuery(table_name).addColumn(column_1, id).addColumn(column_2, name).addColumn(column_3, salary).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ insertTableQuery + "\n");
			stmtObj.execute(insertTableQuery);
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n=======Record Sucessfully Inserted  In The '" + TABLE_NAME + "'=======\n");
	}

	// SQLQueryBuilder #3 :: This Method Is Used To Display All Records From The Database
	public static void displayRecords() {
		String displayRecordsQuery;
		logger.info("\n=======Displaying All Records From The '" + TABLE_NAME + "'=======\n");
		try {
			displayRecordsQuery = new SelectQuery().addColumns(column_1).addColumns(column_2).addColumns(column_3).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ displayRecordsQuery + "\n");

			resObj = stmtObj.executeQuery(displayRecordsQuery);
			if(!resObj.next()) {
				logger.info("\n=======No Records Are Present In The '" + TABLE_NAME + "'=======\n");
			} else {
				do {
					logger.info("\nId?= " + resObj.getString(COLUMN_ONE) + ", Name?= " + resObj.getString(COLUMN_TWO) + ", Salary?= " + resObj.getString(COLUMN_THREE) + "\n");
				} while (resObj.next());
				logger.info("\n=======All Records Displayed From The '" + TABLE_NAME + "'=======\n");
			}
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
	}

	// SQLQueryBuilder #4 :: This Method Is Used To Display A Specific Record From The Database
	public static void displaySelectiveRecord(int emp_id) {
		String selectiveRecordQuery;
		logger.info("\n=======Displaying Specific Record From The '" + TABLE_NAME + "'=======\n");
		try {
			selectiveRecordQuery = new SelectQuery().addColumns(column_1).addColumns(column_2).addColumns(column_3).addCondition(BinaryCondition.equalTo(column_1, emp_id)).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ selectiveRecordQuery + "\n");

			resObj = stmtObj.executeQuery(selectiveRecordQuery);
			if(!resObj.next()) {
				logger.info("\n=======No Record Is Present In The '" + TABLE_NAME + "'=======\n");
			} else {
				do {
					logger.info("\nId?= " + resObj.getString(COLUMN_ONE) + ", Name?= " + resObj.getString(COLUMN_TWO) + ", Salary?= " + resObj.getString(COLUMN_THREE) + "\n");
				} while (resObj.next());
			}
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n=======Specific Record Displayed From The '" + TABLE_NAME + "'=======\n");
	}

	// SQLQueryBuilder #5 :: This Method Is Used To Update A Record In The Database
	public static void updateRecord(int update_record_id) {
		String updateRecord, editorName = "Java Code Geek";
		logger.info("\n=======Updating Record In The '" + TABLE_NAME + "'=======\n");
		try {
			updateRecord = new UpdateQuery(table_name).addSetClause(column_2, editorName).addCondition(BinaryCondition.equalTo(column_1, update_record_id)).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ updateRecord + "\n");
			stmtObj.execute(updateRecord);
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n=======Record Updated In The '" + TABLE_NAME + "' =======\n");
	}

	// SQLQueryBuilder #6 :: This Method Is Used To Delete A Specific Record From The Table
	public static void deleteSelectiveRecord(int delete_record_id) {
		String deleteSelectiveRecordQuery;
		logger.info("\n=======Deleting Specific Record From The '" + TABLE_NAME + "'=======\n");
		try {
			deleteSelectiveRecordQuery = new DeleteQuery(table_name).addCondition(BinaryCondition.equalTo(column_1, delete_record_id)).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ deleteSelectiveRecordQuery + "\n");
			stmtObj.execute(deleteSelectiveRecordQuery);
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n=======Selective Specific Deleted From The '" + TABLE_NAME + "'=======\n");
	}

	// SQLQueryBuilder #7 :: This Method Is Used To Delete All Records From The Table
	public static void deleteRecords() {
		String deleteRecordsQuery;
		logger.info("\n=======Deleting All Records From The '" + TABLE_NAME + "'=======\n");
		try {
			deleteRecordsQuery = new DeleteQuery(table_name).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ deleteRecordsQuery + "\n");
			stmtObj.execute(deleteRecordsQuery);
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n=======All Records Deleted From The '" + TABLE_NAME + "'=======\n");
	}

	// SQLQueryBuilder #8 :: This Method Is Used To Drop A Table From The Database
	@SuppressWarnings("static-access")
	public static void dropTableFromDb() {
		String dropTableQuery;
		logger.info("\n=======Dropping '" + TABLE_NAME + "' From The Database=======\n");
		try {
			dropTableQuery = new DropQuery(DropQuery.Type.TABLE, table_name).dropTable(table_name).validate().toString();
			logger.info("\nGenerated Sql Query?= "+ dropTableQuery + "\n");
			stmtObj.execute(dropTableQuery);
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
		logger.info("\n======='" + TABLE_NAME + "' Is Dropped From The Database=======\n");
	}
}

3.3.2 Implementation of Main Class

In this class, we will be establishing a connection to the database using JDBC API and will be performing the Sqlbuilder operations for performing the SQL transactions.

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

Querybuilder.java

package com.jcg.jdbc.sql.query.builder;

import java.util.Random;

public class QueryBuilderDemo {

	public static void main(String[] args) {

		// Method #1 :: This Method Is Used To Connect With The Database
		Querybuilder.connectDb();

		// Method #2 :: This Method Is Used To Create A Database Table Using SQLQueryBuilder Utility
		Querybuilder.createDbTable();

		// Method #3 :: This Method Is Used To Insert Records In A Table Using SQLQueryBuilder Utility
		for(int count = 101; count < 106; count++) {
			int randomSalary = 1000 + new Random().nextInt(500);
			Querybuilder.insertDataInTable(count, "Editor" + count, randomSalary);
		}

		//  Method #4 :: This Method Is Used To Display All Records From The Table Using SQLQueryBuilder Utility
		Querybuilder.displayRecords();

		// Method #5 :: This Method Is Used To Display A Specific Record From The Table Using SQLQueryBuilder Utility
		Querybuilder.displaySelectiveRecord(103);

		// Method #6 :: This Method Is Used To Update A Record In A Table Using SQLQueryBuilder Utility
		Querybuilder.updateRecord(101);

		// Method #7 :: This Method Is Used To Delete A Specific Record From The Table Using SQLQueryBuilder Utility
		Querybuilder.deleteSelectiveRecord(103);

		Querybuilder.displayRecords();

		// Method #8 :: This Method Is Used To Delete All Records From The Table Using SQLQueryBuilder Utility
		Querybuilder.deleteRecords();

		Querybuilder.displayRecords();

		// Method #9 :: This Method Is Used To Drop A Table From The Database Using SQLQueryBuilder Utility
		Querybuilder.dropTableFromDb();

		// Method #10 :: This Method Is Used To Disconnect From The Database Using SQLQueryBuilder Utility
		Querybuilder.disconnectDb();
	}
}

4. Run the Application

To run the application, Right click on the QueryBuilderDemo class, Run As -> Java Application.

5. Project Demo

The code shows the following status as output.

• Create Query

• Insert Query

• Select Query

• Update SQL Query

• Delete SQL Query

• Drop SQL Query

That’s all for this post. Happy Learning!!

6. Conclusion

Here, in this example we tried to understand JDBC Operations through dynamic SQL queries and how to can consume the Sqlbuilder library for maintaining readability and verifiability.

7. Download the Eclipse Project

This was an example of JBDC Query Builder Example.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.