Home » Core Java » sql » JDBC Query Builder Tutorial

About Yatin Batra

Yatin Batra
Yatin has graduated in Electronics & Telecommunication. During his studies, he has been involved with a large number of projects ranging from programming and software engineering to telecommunications analysis. He works as a software developer in the information technology sector where he is mainly involved with projects based on Java and J2EE technologies platform.

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.
Fig. 1: JDBC Architecture

Fig. 1: JDBC Architecture

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.

Fig. 2: CRUD (Create, Read, Update, Delete) Operations

Fig. 2: CRUD (Create, Read, Update, Delete) Operations

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!

Fig. 3: JDBC Query Builder Application Project Structure

Fig. 3: JDBC Query Builder Application Project Structure

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.

Fig. 4: Create Maven Project

Fig. 4: Create 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.

Fig. 5: Project Details

Fig. 5: Project Details

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.

Fig. 6: Archetype Parameters

Fig. 6: Archetype Parameters

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.

Fig. 7: Database Creation

Fig. 7: Database Creation

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.

Fig. 8: Java Package Creation

Fig. 8: Java Package Creation

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

Fig. 9: Java Package Name (com.jcg.jdbc.sql.query.builder)

Fig. 9: Java Package Name (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.

Fig. 10: Java Class Creation

Fig. 10: Java Class Creation

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.

Fig. 11: Java Class (Querybuilder.java)

Fig. 11: Java Class (Querybuilder.java)

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.

Fig. 12: Java Class (QueryBuilderDemo.java)

Fig. 12: Java Class (QueryBuilderDemo.java)

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.

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.

Fig. 13: Run Application

Fig. 13: Run Application

5. Project Demo

The code shows the following status as output.

  • Create Query
Fig. 14: Creating Table in the Database

Fig. 14: Creating Table in the Database

  • Insert Query
Fig. 15: Inserting Records in the Table

Fig. 15: Inserting Records in the Table

  • Select Query
Fig. 15: Displaying All Records

Fig. 15: Displaying All Records

 

Fig. 16: Displaying Specific Record

Fig. 16: Displaying Specific Record

  • Update SQL Query
Fig. 17: Updating Record for Employee Id – 103

Fig. 17: Updating Record for Employee Id – 103

  • Delete SQL Query
Fig. 18: Delete Record for Employee Id – 101

Fig. 18: Delete Record for Employee Id – 101

 

Fig. 19: Displaying All Records

Fig. 19: Displaying All Records

  • Drop SQL Query
Fig. 20: Dropping Table from the Database

Fig. 20: Dropping Table from the Database

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.

Download
You can download the full source code of this example here: Jdbc QueryBuilder

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 ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz