HandlingSQL 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 justugly but alsoerror-prone. Did you notice the missing space betweenuser_table andwhere? 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 dynamicSQL Query Builder phenomenon by using the open-sourceSqlbuilder library. But before moving ahead let’s take a look and understand JDBC and Sqlbuilder library.

1.1 What is JDBC?

JDBC stands forJava 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 sendstatements to almost any relational database. Thus, JDBC is a Java API for executing theSQL statements and supports basicSQL 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 basicoperations 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 forCreate,Read,Update andDelete. 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, andDELETE 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 generatingSQL 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 theSQL syntax within very lightweight and easy to use Java objects which follow thebuilder paradigm (similar toStringBuilder in Java). This changes many commonSQL syntactical runtime errors intoJava 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 simpleSQL 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 usingSqlBuilder classes.

Modified Select Query

// Assuming These Objects Have Already Been CreatedTable 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 avalidate() method call slipped into the end of theSQL statement. This method call will verify that the columns and tables in the query actually make sense.Maintainability,readability, andverifiability all are wrapped into this easy to use package.

As a finalnote, theSqlBuilder 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 theSQL queries.

1.3.2 SqlBuilder Features

• Good portion of commonly usedSQL, including,
  • SELECT,UPDATE,DELETE,INSERT,CREATE,DROP,UNION.
  • Boolean Logic.
  • Numeric Expressions.
• SupportsQuery Validation for readability and verifiability.
• Helpers for managingPreparedStatement 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 watchthis 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 toFile -> 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 default0.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 apom.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 calledtutorialDb. 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 databasetutorialDb as:

CREATE DATABASE IF NOT EXISTS tutorialDb;

• Use the created databasetutorialDb 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. Theupdated 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 onsrc/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 asQueryBuilderDemo. 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 ofStatement,PreparedStatement, andDatabaseMetaData. This class also contains the code for creating dynamicSQL queries with the help ofSqlbuilder class. Let’s see the simple example of query management usingStatement. 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 Databasepublic 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 SQLBuilderprivate static void loadSQLBuilderSchema() {specficationObj = new DbSpec();schemaObj = specficationObj.addDefaultSchema();}// Helper Method #3 :: This Method To Used To Close The Connection With The Databasepublic 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 Databasepublic static void createDbTable() {logger.info("\n=======Creating '" +TABLE_NAME + "' In The Database=======\n");try {// Specifying Table Nametable_name = schemaObj.addTable(TABLE_NAME);// Specifying Column Names For The Tablecolumn_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 Databasepublic 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 Databasepublic 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 Databasepublic 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 Databasepublic 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 Tablepublic 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 Tablepublic 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 theSqlbuilder operations for performing theSQL transactions.

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBCUltimateGuideright 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 DatabaseQuerybuilder.connectDb();// Method #2 :: This Method Is Used To Create A Database Table Using SQLQueryBuilder UtilityQuerybuilder.createDbTable();// Method #3 :: This Method Is Used To Insert Records In A Table Using SQLQueryBuilder Utilityfor(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 UtilityQuerybuilder.displayRecords();// Method #5 :: This Method Is Used To Display A Specific Record From The Table Using SQLQueryBuilder UtilityQuerybuilder.displaySelectiveRecord(103);// Method #6 :: This Method Is Used To Update A Record In A Table Using SQLQueryBuilder UtilityQuerybuilder.updateRecord(101);// Method #7 :: This Method Is Used To Delete A Specific Record From The Table Using SQLQueryBuilder UtilityQuerybuilder.deleteSelectiveRecord(103);Querybuilder.displayRecords();// Method #8 :: This Method Is Used To Delete All Records From The Table Using SQLQueryBuilder UtilityQuerybuilder.deleteRecords();Querybuilder.displayRecords();// Method #9 :: This Method Is Used To Drop A Table From The Database Using SQLQueryBuilder UtilityQuerybuilder.dropTableFromDb();// Method #10 :: This Method Is Used To Disconnect From The Database Using SQLQueryBuilder UtilityQuerybuilder.disconnectDb();}}

4. Run the Application

To run the application, Right click on theQueryBuilderDemo 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 theSqlbuilder 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 aJava Rockstar?

Subscribe to our newsletter to start Rockingright now!

To get you started we give you our best selling eBooks forFREE!

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 theTerms andPrivacy Policy

Sign up

Thank you!

We will contact you soon.