Java Database Connectivity(JDBC) Tutorial

Java Database Connectivity:

JDBC is a Java database connectivity technology (Java Standard Edition platform) from Oracle Corporation. This technology is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the JVM host environment.

JDBC drivers:

JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the DBMS can understand.


There are commercial and free drivers available for most relational database servers. These drivers fall into one of the following types:
JDBC technology drivers fit into one of four categories.
1.     JDBC-ODBC bridge
2.     Native-API Driver
3.     Network-Protocol Driver(MiddleWare Driver)
4.     Database-Protocol Driver(Pure Java Driver

   1. JDBC-ODBC bridge:
The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.
Java Database Connectivity(JDBC) Tutorial

Sun provides a JDBC-ODBC Bridge driver: sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source.


  • Almost any database for which an ODBC driver is installed can be accessed, and data can be retrieved.


Performance overhead since the calls have to go through the jdbc Overhead bridge to the ODBC driver, then to the native db connectivity interface (thus may be slower than other types of drivers)

2.     Native-API Driver

The JDBC type 2 driver, also known as the Native-API driver, is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.For example: Oracle OCI driver is a Type 2 Driver.

As there is no implementation of jdbc-odbc bridge, its considerably faster than a type 1 driver.

The vendor client library needs to be installed on the client machine.

3 .     Network-Protocol Driver(MiddleWare Driver)

The JDBC type 3 driver, also known as the Pure Java Driver for Database Middleware, is a database driver implementation which makes use of a middle tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol.
JDBC tutorial


Since the communication between client and the middleware server is database independent, there is no need for the database vendor library on the client. The client need not be changed for a new database.


· The middleware layer added may result in additional latency, but is typically overcome by using better middleware services.

4.     Database-Protocol Driver(Pure Java Driver)

Schematic of the Native-Protocol driver.The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol.


· Completely implemented in Java to achieve platform independence.
· These drivers don't translate the requests into an intermediary format (such as ODBC).


  • Drivers are database dependent, as different database vendors use widely different (and usually proprietary) network protocols.

JDBC Basics – Java Database Connectivity Steps:

Step 1: Loading a database driver: The first thing you need to do before you can open a database connection is to load the JDBC driver for the database.


Step 2: Opening the Connection
To open a database connection you use the java.sql.DriverManager class.
String url      = "jdbc:h2:~/test";  
String user     = "sa";
String password = "";

Connection connection =
    DriverManager.getConnection(url, user, password);

Step 3: Creating a jdbc Statement object: Once a connection is obtained we can interact with the database.
Statement statement = connection.createStatement();
A statement object is used to send and execute SQL statements to a database.

Statement: Execute simple sql queries without parameters.
Statement createStatement()
Creates an SQL Statement object.

Prepared Statement: Execute precompiled sql queries with or without parameters.
PreparedStatement prepareStatement(String sql)
returns a new PreparedStatement object. PreparedStatement objects are precompiled
SQL statements.

Callable Statement: Execute a call to a database stored procedure.
CallableStatement prepareCall(String sql)
returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements.

Step 4: Closing the Connection
Once you are done using the database connection you should close it. This is done by calling the Connection.close() method, like this:


import java.sql.*;

class TestRetrieve
 public static void main(String[] args)
   Connection c=DriverManager.getConnection("Jdbc:Odbc:mydsn");
   Statement s=c.createStatement();
   ResultSet result1=s.executeQuery("select * from emp");
  }catch(SQLException e)
   catch(Exception i)

Spring MVC view layer- Thymeleaf vs. JSP

In this tutorial we will describe about two view layers for Spring MVC. One is JSP and another is Thymeleaf. Here we will compare the same page (a subscription form) created twice for the same Spring MVC application: once using Thymeleaf and another time using JSP, JSTL and the Spring tag libraries.

Common requirements-
Our customers need a form for subscribing new members to a message list, with two fields:
  • Email address
  • Type of subscription
Our application will have two @Controllers, which will contain exactly the same code but will forward to different view names:

  • SubscribeJsp for the JSP page (the subscribejsp view).
  • SubscribeTh for the Thymeleaf page (the subscribeth view).

We will have the following classes in our model:

  • Subscription form-backing bean with two fields: String email and String type.
  • Type has values EMAILS and DAILY_DIGEST.
Doing it with JSP

And this is our JSP code, making use of both JSTL (core) and Spring (tags and form) JSP tag libraries:


<%@ taglib prefix="sf" uri="http://www.springframework.org/tags/form" %>
<%@ taglib prefix="s" uri="http://www.springframework.org/tags" %>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
    <title>Spring MVC view layer: Thymeleaf vs. JSP</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <link rel="stylesheet" type="text/css" media="all" href="<s:url value='/css/thvsjsp.css' />"/>
    <h2>This is a JSP</h2>
    <s:url var="formUrl" value="/subscribejsp" />
    <sf:form modelAttribute="subscription" action="subscribeMe">
          <label for="email">Email: </label>
          <sf:input path="email" />
          <label>Type: </label>
                <sf:radiobutton path="type" value="Email" />
                <label for="subscriptionType1">
                  All emails
                <sf:radiobutton path="type" value="Digest" />
                <label for="subscriptionType2">
                  All Digests
        <div class="submit">
          <button type="submit" name="save">SUBSCRIBE ME</button>

Doing it with Thymeleaf

Spring MVC view layer- Thymeleaf vs. JSP

And this is our Thymeleaf code:


<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
    <title>Spring MVC view layer: Thymeleaf vs. JSP</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <link rel="stylesheet" type="text/css" media="all"
      href="../../css/thvsjsp.css" th:href="@{/css/thvsjsp.css}"/>
    <h2>This is a Thymeleaf template</h2>
    <form action="#" th:object="${subscription}" th:action="@{/subscribeth}">
          <label for="email">Email: </label>
          <input type="text" th:field="*{email}" />
          <label>Type: </label>
              <input type="radio" th:field="*{type}" th:value="email" />
              <label>All emails</label>
              <input type="radio" th:field="*{type}" th:value="digest" />
              <label>All Digest</label>
            <li th:remove="all"><input type="radio" /> <label>Second Type</label></li>
        <div class="submit">
          <button type="submit" name="save">Subscribe me!</button>

Things to notice here:
  • This looks much more HTML-ish than the JSP version – no strange tags, just some meaningful attributes.
  • Variable expressions (${...}) are Spring EL and execute on model attributes, asterisk expressions (*{...}) execute on the form backing bean, hash expressions (#{...}) are for internationalization and link expressions (@{...}) rewrite URLs. (If you want to know more about this, have a look at the “Getting started with the Standard Dialect in 5 minutes” guide).
  • We are allowed to have prototype code there: for example, we can set an Email: text in the label for the first field, knowing that Thymeleaf will substitute it with the internationalized text with key subscription.email when it executes the page.
  • We have even been able to add anfor a second radiobutton just for prototyping pleasure. It will be removed when Thymeleaf executes our page.

Change the page style--

Let’s see the steps we would have to take with each technology:

Changing the page style using JSP

Step 1: Deploy the application into our development server and start it up. Our JSP page will not render without starting the server, so this will be a requirement.

Step 2: Navigate through the pages until we find the one to change. Normally, the page to change will be one among several dozen pages in our application, and it is quite possible that in order to reach it we will need to click links, submit forms and/or query databases.

Step 3: Fire up firebug, dragonfly, or our favourite in-browser web development tool. This will allow us to modify our styles acting directly on the browser’s DOM, and thus see immediate results.

Step 4: Make the colour changes. Probably trying a couple of different tones of blue before deciding on the one we like.

Step 5: Copy-paste the changes into our CSS files.

Changing the page style using Thymeleaf

Step 1: Double-click on the .html template file itself and let our browser open it. Being a Thymeleaf template, it will show just fine, only with template/prototype data.

Step 2: Open the .css file with our favourite text editor. The template file statically links to the CSS in its tag (with an href that Thymeleaf substitutes when executing the template by the one generated by th:href). So any changes we make to that CSS will be applied to the static page our browser is displaying.

Step 3: Make the colour changes. As was the case with JSP, we will probably have to try several colour combinations, which will be refreshed in our browser just by pressing F5.

The difference in the number of steps is not really important here (we could also have used firebug with the Thymeleaf template). What is really important is the complexity, the effort and the time required by each of the steps required for JSP. Having to deploy and start the whole application made JSP just lose.

What’s more: think of how this difference would evolve if:

  • Our development server was not local but remote.
  • Changes didn’t involve only CSS but also adding/removing some HTML code.
  • We still hadn’t implemented the required logic in our application to actually reach that page once deployed.

Pros and Cons in JSP

Standard Tags have many benefits:

  • I can use them to do much more than just externalizing layout information. In the end, they can easily make your JSPs 5 times smaller than they would be otherwise.
  • Eclipse/STS works well with custom tags so you’re able to use CTRL+space for auto-completion.

On the down side:

  • Documentation is not the best.
  • Even though custom tags are pretty neat already, I can’t recall any improvement to them in the past few years.
  • Using Custom tags implies that you’re using a JSP Engine inside your web container. You can then expect some minor differences depending on the web container you’re using (Apache Tomcat, IBM Websphere, Oracle Weblogic…).
  • It is also harder to Unit Test your view layer. You can see Spring MVC Test framework if you’re interested in this topic.

Pros and Cons in Thymeleaf

On the bright side:

  • ThymeLeaf is a healthy open source project: new features coming up each month, good documentation, responsive user forums…
  • It is the ideal template engine if you want your web designer to be able to read your view files
  • The Expression Language used (actually called Standard Dialect) is much more powerful than JSP Expression Language.
  • Unlike JSPs, Thymeleaf works well for Rich HTML emails (see http://www.thymeleaf.org/springmail.html).

On the down side:

  • Thymeleaf does not have an equivalent to custom tags (.tagx files) yet.
  • At this stage, ThymeLeaf is not compatible with JSP tag libraries.


We’ve seen the JSP and Thymeleaf approaches side by side. If your application uses hundreds of JSPs, we are not saying that you should ditch them all and start over again using Thymeleaf. However you might consider Thymeleaf for HTML pages outside of the web container such as for Rich HTML emails.

If you are starting on a new project, we strongly encourage you to compare both Thymeleaf and JSPs in order to figure out which one is more suitable to your needs.

Gradle Spring MVC Web Project Example

In this tutorial we explain the spring mvc project and it is managed by Gradle. For this Gradle Spring MVC Web Project Example required following technologies.
  • gradle-2.2.1-all.zip
  • Spring 4.0.6.RELEASE
  • STS
Gradle Tutorial

Step 1. Add Gradle plugin to STS IDE

Step 2. Create Project Structure for Dynamic Web Project As following.

Gradle Spring MVC

Step 3. Gradle Build File
apply plugin: 'java'
apply plugin: 'war'
apply plugin: 'eclipse'

repositories {
dependencies {
   providedCompile 'javax.servlet:servlet-api:2.5'
   compile 'org.springframework:spring-webmvc:4.0.0.RELEASE'
   compile 'org.thymeleaf:thymeleaf-spring4:2.1.4.RELEASE'
   compile 'org.thymeleaf:thymeleaf:2.1.4.RELEASE'
   runtime 'javax.servlet:jstl:1.1.2'
Step 4. Go to the project folder, same level with build.gradle, issue the following command :

$ gradle cleanEclipse eclipse

Now, you can import the project into STS IDE.


Step 4. Right click on the project and goto the configure and click convert gradle project.


Step 5. Spring MVC Files Code

package com.doj.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

public class HomeController {
 @RequestMapping(value="/", method = RequestMethod.GET)
 public String printWelcome(ModelMap model) {
  model.addAttribute("message", "Spring 4 MVC Hello Gradle World!!! This is a Thymeleaf template ");
  return "home";

 <h1>Message : ${message}</h1> 

<?xml version="1.0" encoding="UTF-8"?>  
<beans xmlns="http://www.springframework.org/schema/beans"  
     <context:component-scan base-package="com.doj.controller" />  
     <bean id="viewResolver"  class="org.springframework.web.servlet.view.InternalResourceViewResolver">  
        <property name="prefix">  
        <property name="suffix">  

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">

Step 6: Run It
Start and deploy above web project.



Step 7. Create WAR File
Clean and build a WAR file with the following command :

$ gradle clean build
:compileTestJava UP-TO-DATE
:processTestResources UP-TO-DATE
:testClasses UP-TO-DATE
:test UP-TO-DATE
:check UP-TO-DATE


Total time: 4.012 secs

The generated WAR file is located at the build\libs folder.


Download Source Code


Building Java Projects with Gradle

This guide walks you through using Gradle to build a simple Java project. You’ll create a simple app and then build it using Gradle.

What you’ll need

About your 15 minutes
Gradle 2.0
A favorite text editor or IDE
JDK 6 or later

Step 1: Setup the Gradle to Machine
For this click here how setup

Step 2: Set up the project

First you set up a Java project for Gradle to build. To keep the focus on Gradle, make the project as simple as possible for now.

Create the directory structure

In a project directory of your choosing, create the following subdirectory structure; for example, with mkdir -p src/main/java/hello

└── src
         └── main
                 └── java
                          └── hello

Within the src/main/java/hello directory, you can create any Java classes you want. For simplicity’s sake and for consistency with the rest of this guide, Spring recommends that you create two classes: HelloWorld.java and Greeter.java.

package hello;

public class HelloWorld {
  public static void main(String[] args) {
    Greeter greeter = new Greeter();
package hello;

public class Greeter {
  public String sayHello() {
    return "Hello world!";

Step 3: Build Java code

Starting simple, create a very basic build.gradle file that has only one line in it:
apply plugin: 'java'
This single line in the build configuration brings a significant amount of power. Run gradle tasks again, and you see new tasks added to the list, including tasks for building the project, creating JavaDoc, and running tests.

You’ll use the gradle build task frequently. This task compiles, tests, and assembles the code into a JAR file. You can run it like this:

gradle build

After a few seconds, "BUILD SUCCESSFUL" indicates that the build has completed.

To see the results of the build effort, take a look in the build folder. Therein you’ll find several directories, including these three notable folders:

  • classes. The project’s compiled .class files.
  • reports. Reports produced by the build (such as test reports).
  • libs. Assembled project libraries (usually JAR and/or WAR files).

The classes folder has .class files that are generated from compiling the Java code. Specifically, you should find HelloWorld.class and Greeter.class.

At this point, the project doesn’t have any library dependencies, so there’s nothing in the dependency_cache folder.

Declare dependencies

The simple Hello World sample is completely self-contained and does not depend on any additional libraries. Most applications, however, depend on external libraries to handle common and/or complex functionality.

For example, suppose that in addition to saying "Hello World!", you want the application to print the current date and time. You could use the date and time facilities in the native Java libraries, but you can make things more interesting by using the Joda Time libraries.

First, change HelloWorld.java to look like this:

package mail.java.hello;

import org.joda.time.LocalTime;

public class HelloWorld {
  public static void main(String[] args) {
    LocalTime currentTime = new LocalTime();
    System.out.println("The current local time is: " + currentTime);

    Greeter greeter = new Greeter();

Here HelloWorld uses Joda Time’s LocalTime class to get and print the current time.

If you ran gradle build to build the project now, the build would fail because you have not declared Joda Time as a compile dependency in the build.

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'application'

mainClassName = 'hello.HelloWorld'

// tag::repositories[]
repositories {
// end::repositories[]

// tag::jar[]
jar {
    baseName = 'gs-gradle'
    version =  '0.1.0'
// end::jar[]

// tag::dependencies[]
dependencies {
    compile "joda-time:joda-time:2.2"
// end::dependencies[]

// tag::wrapper[]
task wrapper(type: Wrapper) {
    gradleVersion = '1.11'
// end::wrapper[]

How to Install Gradle

Hi in this post we will learn how to setup Gradle building tool to machine. Gradle is a dependency management / build tool that combines the best of Maven and Ant, making it an extremely powerful and customizable tool. It also uses a sleek Groovy DSL instead of the XML approach of Maven and Ant and is my personal tool-of-choice when I start a new project. Here’s how to install. I have some links to other gradle tutorials at the bottom of this post.

Gradle requires a Java JDK or JRE to be installed, version 6 or higher (to check, use java -version). Gradle ships with its own Groovy library, therefore Groovy does not need to be installed. Any existing Groovy installation is ignored by Gradle.

Gradle uses whatever JDK it finds in your path. Alternatively, you can set the JAVA_HOME environment variable to point to the installation directory of the desired JDK.
Step 1: Install Java

First you need to have the Java JDK (Java Development Kit) installed; having the JRE (Java Runtime Environment) is not enough. To check if you have the JDK installed, open a command prompt or terminal and type javac -version. If you have a JDK installed, you will see your javac version output, eg. javac 1.7.0_01. If you get an error that javac is not a recognized command, download and install the Java JDK.

Step 2: Gradle Download
You can download one of the Gradle distributions from the Gradle web site.

Step 3: Unpacking
The Gradle distribution comes packaged as a ZIP. The full distribution contains:

  • The Gradle binaries.
  • The user guide (HTML and PDF).
  • The DSL reference guide.
  • The API documentation (Javadoc and Groovydoc).
  • Extensive samples, including the examples referenced in the user guide, along with some complete and more complex builds you can use as a starting point for your own build.
  • The binary sources. This is for reference only. If you want to build Gradle you need to download the source distribution or checkout the sources from the source repository. See the Gradle web site for details.

Step 4: Environment variables for Window
For running Gradle, add GRADLE_HOME/bin to your PATH environment variable. Usually, this is sufficient to run Gradle.

Environment variables for Mac/Linux

  1. Extract the distribution archive, i.e. gradle-x.x-bin.tar.gz to the directory you wish to install Gradle. These instructions assume you chose /usr/local/gradle. The subdirectory gradle-x.x will be created from the archive.
  2. In a command terminal, add Gradle to your PATH variable: export PATH=/usr/local/gradle/gradle-x.x/bin:$PATH
  3. Make sure that JAVA_HOME is set to the location of your JDK, e.g. export JAVA_HOME=/usr/java/jdk1.7.0_06 and that $JAVA_HOME/bin is in your PATH environment variable.
  4. Run gradle –version to verify that it is correctly installed.

Step 5: Running and testing your installation
You run Gradle via the gradle command. To check if Gradle is properly installed just type gradle -v. The output shows the Gradle version and also the local environment configuration (Groovy, JVM version, OS, etc.). The displayed Gradle version should match the distribution you have downloaded.

To test the Gradle installation, run Gradle from the command-line:

If all goes well, you see a welcome message:


You now have Gradle installed.

Find out what Gradle can do

Now that Gradle is installed, see what it can do. Before you even create a build.gradle file for the project, you can ask it what tasks are available:

gradle tasks

ou should see a list of available tasks. Assuming you run Gradle in a folder that doesn’t already have a build.gradle file, you’ll see some very elementary tasks such as this:


Even though these tasks are available, they don’t offer much value without a project build configuration. As you flesh out the build.gradle file, some tasks will be more useful. The list of tasks will grow as you add plugins to build.gradle, so you’ll occasionally want to run tasks again to see what tasks are available.

Step 6: JVM options
JVM options for running Gradle can be set via environment variables. You can use either GRADLE_OPTS or JAVA_OPTS, or both. JAVA_OPTS is by convention an environment variable shared by many Java applications. A typical use case would be to set the HTTP proxy in JAVA_OPTS and the memory options in GRADLE_OPTS. Those variables can also be set at the beginning of the gradle or gradlew script.