Home » Enterprise Java » spring » JDBC » Jdbc Example For Beginners

About Ernő Gárday

Erno Garday has graduated from electrical engineering. Since then he has been working as a software engineer developing large scale distributed systems mainly in java. During his professional carrier he has gained significant experience in areas like performance optimisation creating resilient, maintainable software.

Jdbc Example For Beginners

1. Introduction

In this example we will learn how to use JDBC shipped with Java SE. The purpose of JDBC is to make possible interaction with any database in a generic way. This is to hide such details which are specific to the database vendor and expose a generic interface for client applications. We will see how to connect to a database and how to manipulate data stored in it. We will create a register which stores personal contact data like email and phone number.

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!

2. Environment and project setup

2.1 Tools used

We will use MySQL as the database and MySQL Workbench as the client. Eclipse as IDE and Java 8 SE.

2.2 Database setup

We will use MySQL Workbench to manage the database status and create table. If you haven’t done this already, download MySQL from here https://dev.mysql.com/downloads/mysql/ and install it. Having successfully installed MySQL you can start up the database and connect to it via Workbench.

Start Workbench and click on MySQL connections to create a new connection. Make sure you are connecting to the locally installed instance hostname: localhost port: 3306 and then click OK.

Connect to Database

Followed a successful connection the instance can be managed. Probably this is the first time you’re trying to connect hence the database needs to be started up. Go to the left menu and under INSTACE you will find Startup / Shutdown.

Start database server

As a next step create a schema. The schema is a logical unit which groups database entities. To create one go to the empty area under SCHEMAS and right click on it. Name it arbitrarily e.g. jcg.

Create Schema

Now you need to create a table within that schema. To create a table called register execute the below SQL expression in workbench.

create register table

CREATE TABLE `jcg`.`register` (
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NULL,
`email` VARCHAR(45) NULL,
PRIMARY KEY (`first_name`,`last_name`));

We are done with the preparation. We can start setup the Eclipse project and do the programming part.

2.3 Eclipse project setup

Download maven from here: https://maven.apache.org/download.cgi . We will use it for dependency management. Create a java project called example and add a pom.xml to the project. The pom.xml describes the project dependencies. We use a single dependency which is the driver of the MySql database. Add the below snippet to the dependency section in the pom.xml

MySql dependency

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.6</version>
</dependency>

Start a terminal on windows. Go to the directory where your project is located. Execute the mvn eclipse:eclipse command. The dependencies are now downloaded and added to your eclipse project. Finally, create a package jcg and within the jcg package, create a class named register.java. Your project should look like the below picture now.

Eclipse project

Eclipse project

3. The JDBC register example

There are three main activities we will cover and demonstrate the implementation of them, with the help of an example application which can be download from here. These are the most typical use-cases which you would face in a real-word project:

  1. setup the connection with the Database
  2. execute SQL commands
  3. process the result of queries

3.1 Common part of the application

The application can handle INSERT, SELECT or UPDATE requests. You can select one of them with the help of the first argument which can be accordingly INSERT , SELECT or UPDATE. We have an enum which represents these values. We convert the input string argument to an enum type. If the input parameter does not match with any of the enum entries, then an IllegalArgumentException is thrown and, as a result, the application terminates.

Handling arguments

	public enum SQLType {
		INSERT, UPDATE, SELECT, DELETE;
	}
	
	public static void main(String [] args) {
		
		if (args.length == 0) {
			System.err.println("Specify cmd type and parameters: INSERT|UPDATE|SELECT [columns ...]");
			System.exit(1);
		}
		SQLType type = SQLType.valueOf(args[0]);
		
		switch(type) {
		case INSERT: 
			insert(args);
			break;
		case SELECT:
			select();
			break;
		case UPDATE:
			update(args);
                       break;
		case DELETE:
			delete(args);
		}
	}

The next step is when a switch statement decides which functionality (insert, select, update) to execute.

Whatever you choose, the first thing which will be done is an attempt to make a database connection. Connecting to the database is implemented in the connect method. In the connect method we compose the connection string and pass it to the getConnection method. In the connection string jdbc:mysql://localhost:3306/jcg?user=test&password=test we specify the protocol, address of the Database, user and password. Finally, it is the DriverManager responsibility to select an appropriate driver to the Database. Having successfully connected, arbitrary SQL commands can be issued.

Create a Connection object

private static Connection connect() throws SQLException {
	String connectStr = "jdbc:mysql://localhost:3306/jcg?user=test&password=test";
	System.out.println("Connecting to: "+ connectStr);
	return DriverManager.getConnection(connectStr);
}

The application demonstrates different approaches how you can execute INSERT, SELECT, UPDATE, DELETE SQL requests. We start with INSERT.

3.2 Insert data to the database

To start up the application go to Run and click on Run Configuration. As a result Run Configuration menu comes up:

Run Configuration

Run Configuration

Next step is to specify the program arguments: SQL command type, first name, last name, phone number, mail:

Specify arguments

Specify Arguments

You have just specified the content you want to insert to the database as arguments, finally click on run. Followed successfully execution a new entry is inserted to the database which you can verify via workbench. Or as the next step do a SELECT query.
See the whole code below which was executed. We go through the steps in the next section where we can compare with the flow of querying data.

Insert an entry to the database

public static void insert(String[] args) {
	System.out.println("insert");
	try(Connection dbc =  connect() ) {
		PreparedStatement ppStmt = dbc.prepareStatement("INSERT INTO jcg.register VALUES (?,?,?,?)");
		ppStmt.setString(1,args[1]);
		ppStmt.setString(2,args[2]);
		ppStmt.setString(3,args[3]);
		ppStmt.setString(4,args[4]);
		ppStmt.execute();
	} catch (SQLException se) {
		System.out.println(se);
	}
}

3.3 Select data from the database

The flow is petty much as it was before, but the type of the SQL command in the first argument is different. If you take a closer look at the implementation of the select and insert method, you will realise that something is fundamentally different. JDBC provides a hierarchy of Statement interfaces which consequently provides different level of control i.e. on the SQL parameters. In the SELECT statement we do not use parameters therefore a static query is appropriate consequently we use Statement.

Execute select with Statement

public static void select() {
	try(Connection dbc =  connect() ) {
		Statement stmt = dbc.createStatement();
		ResultSet result = stmt.executeQuery("SELECT * from jcg.register");
		while(result.next()) {
			for(int i = 1; i <= result.getMetaData().getColumnCount(); i++) {
				System.out.println("Column Name: " + result.getMetaData().getColumnName(i) + 
				" Value: " +result.getObject(i));
			}
			System.out.println();
		}
	} catch (SQLException se) {
		System.out.println(se);
	}
}

However if you want to reuse the SQL query with different parameters then use PreparedStatement as we did it in the insert method.

Usage of PreparedStatement

		PreparedStatement ppStmt = dbc.prepareStatement("INSERT INTO jcg.register VALUES (?,?,?,?)");
		ppStmt.setString(1,args[1]);
		ppStmt.setString(2,args[2]);
		ppStmt.setString(3,args[3]);
		ppStmt.setString(4,args[4]);
		ppStmt.execute();

Parameters are specified via setter methods where the first argument is the parameter index and the second parameter is the value. If you happen to use Spring JDBC there is a much convenient way to define the query parameters. You can define them as key value pairs where the key it the name of the column. To learn more about JDBC Named Parameters read : https://examples.javacodegeeks.com/enterprise-java/spring/jdbc/jdbc-named-parameters-example-spring-namedparameterjdbctemplate/

Let’s move back to the end of the select method. In the final part of the method we process the returned ResultSet object. On one hand the ResultSet contains the actual values in tabular format, on the other hand it contains metadata. To iterate over the result simply call next on it. The call to next, moves the cursor one row from its current position.

If you run the application with a single argument SELECT, your output should look like below

Output of the select query

Column Name: first_name Value: Mary
Column Name: last_name Value: Lou
Column Name: phone Value: +36309562487
Column Name: email Value: ml@xmail.com

3.4 Update database

Updating the database not necessarily means directly issuing an UPDATE SQL command. Let’s see a different way of updating the database. In this example we select data using the first_name and last_name column content. If columns data is matching with the vale specified in the arguments then we do the update.

Select and filter data

PreparedStatement ppStmt = dbc.prepareStatement("SELECT * FROM jcg.register WHERE first_name = ? AND last_name = ?", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ppStmt.setString(1, args[1]);
ppStmt.setString(2, args[2]);

Note the additional parameters in the prepareStatement call. The first parameter is the SQL query itself the second instructs the library to reflect underlying database changes and the third one makes possible to update the ResutSet.
We update the ResultSet object using key value pairs where the key is the column label. Finally, an updateRow call updates the underlying database.

Update database

result.updateString("phone", args[3]);
result.updateString("email", args[4]);
result.updateRow();

If you run the application with the parameters: UPDATE Mary Lou +3630111111 ml@ymail.com then it will update Mary Lou’s phone number and email. The output is:

Database update output

Before update:
Column Name: first_name Value: Mary
Column Name: last_name Value: Lou
Column Name: phone Value: +36309562487
Column Name: email Value: ml@xmail.com

After update:
Column Name: first_name Value: Mary
Column Name: last_name Value: Lou
Column Name: phone Value: +3630111111
Column Name: email Value: ml@ymail.com

3.5 Update database

To delete data from the database specify DELETE as the first argument. Additionally we use the second and the third arguments in the WHERE condition. If first_name and last_name matches then the entry will be deleted from the database. We use PreparedStatement to build the query. From JDBC point of view there isn’t any new to thing to explore in the below code. Only The SQL statement differs from the previously described constructs.

Delete an entry from the database

public static void delete(String[] args) {
	System.out.println("delete");
	try(Connection dbc =  connect() ) {
		PreparedStatement ppStmt = dbc.prepareStatement("DELETE FROM jcg.register  WHERE first_name = ? AND last_name = ?");
		ppStmt.setString(1,args[1]);
		ppStmt.setString(2,args[2]);
		ppStmt.execute();
	} catch (SQLException se) {
		System.out.println(se);
	}
}

4. Summary

In this post we focused on demonstrating the usage of JDBC with the help of an example.
We covered the below areas:

  • set up the proper driver for a RDBMS
  • connecting to a database, content of the connection url
  • we saw how to INSERT, SELECT, UPDATE, DELETE data in or from a database
  • difference between Statement and PreparedStatement
  • usage of the ResultSet object
(No Ratings Yet)
Start the discussion Views Tweet it!

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

avatar
  Subscribe  
Notify of