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.

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

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

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:

setup the connection with the Database

execute SQL commands

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.

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.

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

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

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.

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.

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

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.

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.

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.

Newsletter

Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

Email address:

Receive Java & Developer job alerts in your Area

Leave this field empty if you're human:

Join Us

With 1,240,600 monthly unique visitors and over 500 authors we are placed among the top Java related sites around. Constantly being on the lookout for partners; we encourage you to join us. So If you have a blog with unique and interesting content then you should check out our JCG partners program. You can also be a guest writer for Java Code Geeks and hone your writing skills!

Disclaimer

All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners. Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries. Examples Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.