jdbc statement

The Java Tutorials have been written for JDK 8. Examples and practices described in this page don't take advantage of improvements introduced in later releases and might use technology no longer available. See Java Language Changes for a summary of updated language features in Java SE 9 and subsequent releases. See JDK Release Notes for information about new features, enhancements, and removed or deprecated options for all JDK releases.

Lesson: JDBC Introduction

The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a relational database.

JDBC helps you to write Java applications that manage these three programming activities:

• Connect to a data source, like a database
• Send queries and update statements to the database
• Retrieve and process the results received from the database in answer to your query

The following simple code fragment gives a simple example of these three steps:

This short code fragment instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results. It's that simple.

JDBC Product Components

JDBC includes four components:

The JDBC API  —  The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.

The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.

JDBC Driver Manager  —  The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.

The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface ™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.

JDBC Test Suite  —  The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.

JDBC-ODBC Bridge  —  The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

This Trail uses the first two of these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test relational database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.

About Oracle | Contact Us | Legal Notices | Terms of Use | Your Privacy Rights

Copyright © 1995, 2022 Oracle and/or its affiliates. All rights reserved.

Java Guides

Search this blog, jdbc tutorial.

• Java JDBC API Overview  - Overview of JDBC API, java.sql and javax.sql package.
• Steps to Connect a Java Application to Database (JDBC and MySQL)  -  In this post, I will demonstrate the fundamental steps involved in the process of connecting to a database and executing a query using JDBC API.
• JDBC CRUD Example Tutorial  - In this post, you will learn how to CREATE, INSERT, UPDATE, SELECT and DELETE operations using JDBC API.

Examples using JDBC Statement 

• JDBC Statement Create a Table Example  - Example to create a table using a Statement interface.
• JDBC Statement - Insert Multiple Records Example  - Example to insert multiple records in a table using the Statement interface.
• JDBC Statement - Update a Record Example  - Example to update a record in a table using the Statement interface.
• JDBC Statement Select Records Example  - Example to retrieve records from a table using Statement interface.
• JDBC Statement - Delete a Record Example  - Example to delete a record from a table using a Statement interface.
• JDBC Statement - Batch Insert Example  - Example to insert records in a batch process via Statement interface.
• JDBC Statement - Batch Update Example  - Example to update records in a batch process via Statement interface.

Examples using JDBC PreparedStatement

• JDBC PreparedStatement - Insert a Record Example  - Example to insert a record in a table using the PreparedStatement interface.
• JDBC PreparedStatement - Update a Record Example  - Example to update a record in a table using the PreparedStatement interface.
• JDBC PreparedStatement - Select Records Example  - Example to retrieve records from a table using the PreparedStatement interface.
• JDBC PreparedStatement with a list of values in an IN clause  - Example to pass a list of values to IN clause using PreparedStatement interface.
• JDBC PreparedStatement - Batch Insert Example  - Example to insert records in a batch process via the PreparedStatement interface.
• JDBC PreparedStatement - Batch Update Example  - Example to update records in a batch process via PreparedStatement interface.

Examples using JDBC CallableStatement

• JDBC CallableStatement Stored Procedures Example  - Create and use Stored Procedure examples using the CallableStatement interface.

Insert and Retrieve Image Examples

• Insert and Retrieve Images from MySQL Table Using Java [JDBC]
• How to Retrieve Auto-Generated Keys in JDBC

JDBC Transactions

• JDBC Transactions with Example  - In this article, you will learn how to use JDBC transactions with examples.

JDBC SQLExceptions Handling

• JDBC Handling SQLExceptions  - In this article, we will learn how to handle SQLExceptions while working with JDBC.

JDBC java.sql Package Interfaces and Classes

• JDBC Connection Interface
• JDBC Statement Interface
• JDBC PreparedStatement Interface
• JDBC CallableStatement Interface
• JDBC ResultSet Interface with Examples
• JDBC ResultSetMetaData Interface
• JDBC DatabaseMetaData Interface
• JDBC DriverManager Class

JDBC Batch Processing

• JDBC Batch Update MySQL Example  - Example to update records in a batch process using Statement and PreparedStatement interfaces.
• JDBC Batch Insert MySQL Example  - Example to insert records in a batch process using Statement and PreparedStatement interfaces.

JDBC with In-Memory Databases

• JDBC H2 Database Create, Read, Update and Delete Example Tutorial
• Java HSQLDB Tutorial - Create, Read, Update and Delete JDBC Examples

JSP with JDBC and MySQL Database

• JSP + JDBC + MySQL Example  -    In this article, we will build a simple  Employee Registration  module using  JSP ,  JDBC , and the  MySQL  database.
• JSP Registration Form + JDBC + MySQL Example  -  In this article, we will build a simple  Employee Registration  module using  JSP ,  JDBC , and the  MySQL  database.
• JSP Login Form + JDBC + MySQL Example  -  In this article, we will build a simple Login Form using  JSP ,  JDBC,  and the  MySQL database .

Servlet + JSP + JDBC + MySQL Examples

• JSP Servlet JDBC MySQL CRUD Example Tutorial
• Servlet + JSP + JDBC + MySQL Example
• Registration Form using JSP + Servlet + JDBC + Mysql Example
• Login Form using JSP + Servlet + JDBC + MySQL Example

Web app using JSP, Servlet, MySQL and Hibernate

• JSP Servlet Hibernate CRUD Example   // Web application
• JSP Servlet Hibernate Web Application    // Web application
• Hibernate Registration Form Example with JSP, Servlet, MySQL   // Web application
• Login Form using JSP + Servlet + Hibernate + MySQL Example   // Web application
• JDBC Dynamically Insert Multiple Rows to MySQL Example  - Example to dynamically insert rows using StringBuilder and PreparedStatement placeholders ?.
• Retrieve column names from java.sql.ResultSet  - This article provides how-to to retrieve the column names of a table using the getMetaData() method.
• JDBC DataSource Connection MySQL Example

Resource and Useful Links

• Difference between Statement and PreparedStatement
• How can I get the SQL of a PreparedStatement?
• java.util.Date vs java.sql.Date
• How to establish a connection pool in JDBC?
• Closing JDBC Connections in Pool
• How does a PreparedStatement avoid or prevent SQL injection?
• Check if the table exists
• Why do we use a DataSource instead of a DriverManager?
• How to get row count using ResultSet in Java?
• ResultSet is not closed when the connection is closed?
• How to get database URL from java.sql.Connection?
• JDBC 4.1 Features (Oracle Doc)
• JDBC 4.2 Features (Oracle Doc)
• JDBC Tutorial Oracle Official Site
• Package java.sql JavaDoc 8
• Package javax.sql JavaDoc 8

Post a Comment

Leave Comment

My Top and Bestseller Udemy Courses

• Spring 6 and Spring Boot 3 for Beginners (Includes Projects)
• Building Real-Time REST APIs with Spring Boot
• Building Microservices with Spring Boot and Spring Cloud
• Full-Stack Java Development with Spring Boot 3 & React
• Testing Spring Boot Application with JUnit and Mockito
• Master Spring Data JPA with Hibernate
• Spring Boot Thymeleaf Real-Time Web Application - Blog App

Check out all my Udemy courses and updates: Udemy Courses - Ramesh Fadatare

Copyright © 2018 - 2025 Java Guides All rights reversed | Privacy Policy | Contact | About Me | YouTube | GitHub

• Java Arrays
• Java Strings
• Java Collection
• Java 8 Tutorial
• Java Multithreading
• Java Exception Handling
• Java Programs
• Java Project
• Java Collections Interview
• Java Interview Questions
• Spring Boot
• Java Tutorial

Overview of Java

• Introduction to Java
• The Complete History of Java Programming Language
• C++ vs Java vs Python
• How to Download and Install Java for 64 bit machine?
• Setting up the environment in Java
• How to Download and Install Eclipse on Windows?
• JDK in Java
• How JVM Works - JVM Architecture?
• Differences between JDK, JRE and JVM
• Just In Time Compiler
• Difference between JIT and JVM in Java
• Difference between Byte Code and Machine Code
• How is Java platform independent?

Basics of Java

• Java Basic Syntax
• Java Hello World Program
• Java Data Types
• Primitive data type vs. Object data type in Java with Examples
• Java Identifiers

Operators in Java

• Java Variables
• Scope of Variables In Java

Wrapper Classes in Java

Input/output in java.

• How to Take Input From User in Java?
• Scanner Class in Java
• Java.io.BufferedReader Class in Java
• Difference Between Scanner and BufferedReader Class in Java
• Ways to read input from console in Java
• System.out.println in Java
• Difference between print() and println() in Java
• Formatted Output in Java using printf()
• Fast I/O in Java in Competitive Programming

Flow Control in Java

• Decision Making in Java (if, if-else, switch, break, continue, jump)
• Java if statement with Examples
• Java if-else
• Java if-else-if ladder with Examples
• Loops in Java
• For Loop in Java
• Java while loop with Examples
• Java do-while loop with Examples
• For-each loop in Java
• Continue Statement in Java
• Break statement in Java
• Usage of Break keyword in Java
• return keyword in Java
• Java Arithmetic Operators with Examples
• Java Unary Operator with Examples
• Java Assignment Operators with Examples
• Java Relational Operators with Examples
• Java Logical Operators with Examples
• Java Ternary Operator with Examples
• Bitwise Operators in Java
• Strings in Java
• String class in Java
• Java.lang.String class in Java | Set 2
• Why Java Strings are Immutable?
• StringBuffer class in Java
• StringBuilder Class in Java with Examples
• String vs StringBuilder vs StringBuffer in Java
• StringTokenizer Class in Java
• StringTokenizer Methods in Java with Examples | Set 2
• StringJoiner Class in Java
• Arrays in Java
• Arrays class in Java
• Multidimensional Arrays in Java
• Different Ways To Declare And Initialize 2-D Array in Java
• Jagged Array in Java
• Final Arrays in Java
• Reflection Array Class in Java
• util.Arrays vs reflect.Array in Java with Examples

OOPS in Java

• Object Oriented Programming (OOPs) Concept in Java
• Why Java is not a purely Object-Oriented Language?
• Classes and Objects in Java
• Naming Conventions in Java
• Java Methods

Access Modifiers in Java

• Java Constructors
• Four Main Object Oriented Programming Concepts of Java

Inheritance in Java

Abstraction in java, encapsulation in java, polymorphism in java, interfaces in java.

• 'this' reference in Java
• Inheritance and Constructors in Java
• Java and Multiple Inheritance
• Interfaces and Inheritance in Java
• Association, Composition and Aggregation in Java
• Comparison of Inheritance in C++ and Java
• abstract keyword in java
• Abstract Class in Java
• Difference between Abstract Class and Interface in Java
• Control Abstraction in Java with Examples
• Difference Between Data Hiding and Abstraction in Java
• Difference between Abstraction and Encapsulation in Java with Examples
• Difference between Inheritance and Polymorphism
• Dynamic Method Dispatch or Runtime Polymorphism in Java
• Difference between Compile-time and Run-time Polymorphism in Java

Constructors in Java

• Copy Constructor in Java
• Constructor Overloading in Java
• Constructor Chaining In Java with Examples
• Private Constructors and Singleton Classes in Java

Methods in Java

• Static methods vs Instance methods in Java
• Abstract Method in Java with Examples
• Overriding in Java
• Method Overloading in Java
• Difference Between Method Overloading and Method Overriding in Java
• Differences between Interface and Class in Java
• Functional Interfaces in Java
• Nested Interface in Java
• Marker interface in Java
• Comparator Interface in Java with Examples
• Need of Wrapper Classes in Java
• Different Ways to Create the Instances of Wrapper Classes in Java
• Character Class in Java
• Java.Lang.Byte class in Java
• Java.Lang.Short class in Java
• Java.lang.Integer class in Java
• Java.Lang.Long class in Java
• Java.Lang.Float class in Java
• Java.Lang.Double Class in Java
• Java.lang.Boolean Class in Java
• Autoboxing and Unboxing in Java
• Type conversion in Java with Examples

Keywords in Java

• Java Keywords
• Important Keywords in Java
• Super Keyword in Java
• final Keyword in Java
• static Keyword in Java
• enum in Java
• transient keyword in Java
• volatile Keyword in Java
• final, finally and finalize in Java
• Public vs Protected vs Package vs Private Access Modifier in Java
• Access and Non Access Modifiers in Java

Memory Allocation in Java

• Java Memory Management
• How are Java objects stored in memory?
• Stack vs Heap Memory Allocation
• How many types of memory areas are allocated by JVM?
• Garbage Collection in Java
• Types of JVM Garbage Collectors in Java with implementation details
• Memory leaks in Java
• Java Virtual Machine (JVM) Stack Area

Classes of Java

• Understanding Classes and Objects in Java
• Singleton Method Design Pattern in Java
• Object Class in Java
• Inner Class in Java
• Throwable Class in Java with Examples

Packages in Java

• Packages In Java
• How to Create a Package in Java?
• Java.util Package in Java
• Java.lang package in Java
• Java.io Package in Java
• Java Collection Tutorial

Exception Handling in Java

• Exceptions in Java
• Types of Exception in Java with Examples
• Checked vs Unchecked Exceptions in Java
• Java Try Catch Block
• Flow control in try catch finally in Java
• throw and throws in Java
• User-defined Custom Exception in Java
• Chained Exceptions in Java
• Null Pointer Exception In Java
• Exception Handling with Method Overriding in Java
• Multithreading in Java
• Lifecycle and States of a Thread in Java
• Java Thread Priority in Multithreading
• Main thread in Java
• Java.lang.Thread Class in Java
• Runnable interface in Java
• Naming a thread and fetching name of current thread in Java
• What does start() function do in multithreading in Java?
• Difference between Thread.start() and Thread.run() in Java
• Thread.sleep() Method in Java With Examples
• Synchronization in Java
• Importance of Thread Synchronization in Java
• Method and Block Synchronization in Java
• Lock framework vs Thread synchronization in Java
• Difference Between Atomic, Volatile and Synchronized in Java
• Deadlock in Java Multithreading
• Deadlock Prevention And Avoidance
• Difference Between Lock and Monitor in Java Concurrency
• Reentrant Lock in Java

File Handling in Java

• Java.io.File Class in Java
• Java Program to Create a New File
• Different ways of Reading a text file in Java
• Java Program to Write into a File
• Delete a File Using Java
• File Permissions in Java
• FileWriter Class in Java
• Java.io.FileDescriptor in Java
• Java.io.RandomAccessFile Class Method | Set 1
• Regular Expressions in Java
• Regex Tutorial - How to write Regular Expressions?
• Matcher pattern() method in Java with Examples
• Pattern pattern() method in Java with Examples
• Quantifiers in Java
• java.lang.Character class methods | Set 1
• Java IO : Input-output in Java with Examples
• Java.io.Reader class in Java
• Java.io.Writer Class in Java
• Java.io.FileInputStream Class in Java
• FileOutputStream in Java
• Java.io.BufferedOutputStream class in Java
• Java Networking
• TCP/IP Model
• User Datagram Protocol (UDP)
• Differences between IPv4 and IPv6
• Difference between Connection-oriented and Connection-less Services
• Socket Programming in Java
• java.net.ServerSocket Class in Java
• URL Class in Java with Examples

JDBC - Java Database Connectivity

• Introduction to JDBC (Java Database Connectivity)
• JDBC Drivers
• Establishing JDBC Connection in Java
• Types of Statements in JDBC

JDBC Tutorial

• Java 8 Features - Complete Tutorial

JDBC stands for Java Database Connectivity . JDBC is a Java API or tool used in Java applications to interact with the database. It is a specification from Sun Microsystems that provides APIs for Java applications to communicate with different databases. Interfaces and Classes for JDBC API comes under java.sql package. In this Java JDBC tutorial , we will be learning about what is Java Database Connectivity with suitable example.

In the present world, where we mostly deal with websites and databases it is as if they matter the most but if we closely look over it is data that matters the most. If we look closer that all developers are revolving and working over the web and mobile technologies but what remains the same is the data.

Hence, there is an urgency to figure out how data is being handled for which this concept comes into play.

Let us do have an overview of why do we need this term which can be better interpreted with the image provided below.

Why do we need to learn advanced java?

As we are well versed with all the concepts of Core Java now comes the time to build projects that will results out in building an application as a final product.  So if we do are only aware of core java we can only build applications that can be run on the machine where the components of it are stored(in simpler words one can say where all the codes are written). These types of applications are known as standalone applications .

Note: Standalone applications being one of two types of applications in the programming world only holds for 5% of the applications in the real world.  

Hence, the scope is very constricted if we only do build standalone applications resulting out in the very little scope of development and scalability which gives birth to advance java which in turn gives birth to 3 components primarily namely JSP , Servlets , and JDBC in order to make the applications run on multiple machines. With the evolution of these 3 components, it removes the constraint of running only a single machine as it was impractical to users in the real-world application is made but forcing users to install then use them. Now, these types of applications are known as web applications that are the applications providing the services to the end-users without installing them. These applications are known as web applications which are not standalone applications.

Now you must be wondering what is JSP and Servlet and how JDBC is interrelated with them. For the time being, just focus only on JDBC in order to get started with what exactly it is and how to use it by refer r ing to the image depicted below as follows:

Note: Conclusion drawn from Image is as follows: JSP stands for Java Server Pages where these pages are considered as View Components as Presentation logic . Servlet is meant for internal processing going on such as reading, fetching values from the database, comparison, etc in the backend as Processing Logic in our process servlet. Servlets do also contain java classes for this background processing for processing logic. JDBC is meant for Connectivity of Java Application in servlets as discussed to our Database .

What is JDBC?

Let us try to discuss this from scratch till now in the java domain we were simply writing codes(programming) and interacting with the database. Have you ever wonder imagining how internally our program can be linked to a database giving it enormous power to make projects and applications out of our program. Yes, it is possible with the help of this mechanism. Let us discuss in a real-world scenario which later on we will be mapping with technical terms to get an absolute technical understanding of the same with a pictorial representation.

Real-world example:

Consider two places A and B. Now people residing on A do not know the language of people residing on place B and vice-versa which we will be going to map with technical terms to discuss out major components inside the JDBC.

Mapping the same with technological terms to illustrate above real illustration. Here place A is referred to as a java application and place B is therefore referred to as a database. Now the translator as discussed above which is responsible for converting java application calls to database calls and database calls to java application calls is nothing but a ‘Driver Software’. The road between applications is ‘Connection’ and lastly the vehicle is our ‘statement object’.  Now ‘requirement’ above is our SQL query and the ‘small block’ generated after processing the SQL statement by the database engine has been passed in nothing but ‘ResultSet’. Now , this technology by which java applications is communicating with database is ‘Java Database Connectivity. By now we are done with a crystal clear overview of JDBC and geek you will be surprised that we already have covered it, let dig down to get to know the working of components which will be described up to greater depth in the environment setup of JDBC. 

Now we will penetrate down into the JDBC component which as discussed above is a technology that is used to connect Java applications with the database. It is an API consisting of lots of classes and interfaces by which with the help of some classes and interfaces we are able to connect the java application to the database as pictorially depicted above. It has mainly two packages namely as follows: java.sql package and javax.sql package.

Note: For reference above SQL is taken as database to illustrate packages names.  

Interfaces and Classes of JDBC API

Some popular interfaces of JDBC API are listed below:

• Driver interface
• Connection interface
• Statement interface
• PreparedStatement interface
• CallableStatement interface
• ResultSet interface
• ResultSetMetaData interface
• DatabaseMetaData interface
• RowSet interface

Some important Classes of JDBC API are listed below:

• DriverManager class
• Types class

So here is how we are going to cover JDBC ailments in order to get how technically the whole process revolves.

• What is JDBC
• Need of JDBC
• JDBC Architecture

JDBC Environment Setup

Steps to connect jdbc.

• JDBC example as implementation

JDBC Architecture 

To know in-depth about the above diagram, you can refer this article Architecture of JDBC .

Now let us do discuss out various types of drivers in JDBC. So basically there are 4 types of standard drivers listed below:

• Type-1 driver or JDBC-ODBC bridge driver (Bridge Driver)
• Type-2 driver or Native-API driver (Native API)
• Type-3 driver or Network Protocol driver (Network Protocol)
• Type-4 driver or Thin driver (Native protocol)

Now, you must be wondering about they are their point do come out which drivers to be used. The solution to this is as simple as we need to use the driver as per accordance with the database with which we are interacting. 

Note: Drivers to be used are directly propionate to which kind of database with which we are dealing.

Drivers are mentioned in the tabular format below corresponding to the database which is as follows:

Now you must be wondering out from where these listed drivers are to be imported. So in order to import the drivers, you need to download a JAR file from the web. Considering the most frequently used database namely MySQL you need to download a JAR file named ‘ mysql.connector ‘ and so on it goes for other databases.

Note: It is not an essential step as different IDE’s possesses different traits. In some IDE these connectors are inbuilt such as NetBeans while in Eclipse you need to import this JAR file as discussed above by simply downloading corresponding JAR file from the internet and load in the project.  If you do not know how to import JAR files to libraries in java IDE, first go through this step.

There are basically seven steps to get your java application connected to deal with the database. Here database could be structured or non-structured, we have to deal accordingly for the same which will be clear by the end of sequential steps execution. In generic mostly we deal with either MySQL or Oracle database as far it is concerned for our java application. Here we will be dealing out with the MySQL database for our java application wherein processing we just have to execute these listed steps below in sequential order to connect JDBC which is as follows:

• Import the required package for the corresponding database.
• First load then register the same
• Establish the connection
• Create a statement
• Execute the query
• Process the results
• Close the connections

Let us discuss the above steps by picking up cases from the real world to get a clear sake of understanding our concept. Considering an illustration of mobile networking which is as follows: 

Real-life implementation of JDBC is most frequently seen in mobile networking which is illustrated as follows:

• Consider you want to talk to somebody far away. For which one needs a device known as cell-phone/telecom which signifies our Step 1.
• Now user needs a network and a SIM card which is equivalent to the loading and registering process signifying Step 2.
• Now with a working SIM card, physical labor is required to dial numbers and pressing the call button signifying step 3 as we are trying to establish the connection to commute.
• Whatever message we need to deliver is equivalent to creating a statement.
• Now once connected we need to speak the message as we have thought off, which is equivalent to the execution of the query.
• Now a response will be received which was our goal signifies step 6 of processing results technically in the case of JDBC.
• Lastly, we need to press the button on the device to close communication, similarly, it is a good practice of closing the connections in the technical aspect which here is JDBC.

Implementation (Working of JDBC)

• Suppose the database with which we want to deal is MySQL. So first we need to import the package, for SQL database it is namely ‘ java.sql.* ‘.
• Now as per IDE(we need to load its corresponding JAR file in our project in IDE where JAR is manually supposed to be inserted in the IDE, taking ‘ eclipse IDE ‘ for reference as an IDE in order to illustrate the same in the below example.
• By far we are done with importing the JAR to our project. Now in order to load the driver, we will be using forName() method by writing “ com.mysql.driver_name ” which belongs to class ‘ class ‘
• Now in order to establish the connection, we have to instantiate an interface named ‘Connection’
• Normal statements:
• Prepared statements:
• Callable statements: Stored procedures
• Once the statement is created, now we will be executing the query in our database as per the perceived response from the database. Suppose if we want to fetch data from the database then the response is table structure here or if we are simply inserting the values then response if the number of rows effectively.
• Lastly, whatever we are getting either a table or simply rows, now we simply need to process the results.
• Also, it is recommended to good practice by closing the connections to release and free up memory resources by closing the object of interface and statement objects.

Illustration: Setting up JDBC

Now let us move ahead and discuss its interaction with the real database after successfully setting up with the database how internally things are getting managed. Here we will be having to create two classes, one is our connection class and the other one where we will be implementing in application.java class using the connector object. We are done with the connection class in the above illustration, so let us now finally discuss the application class with help of an example. Considering the database ‘MySQL here to interpret and code out the same.

Implementation: Query in our MySQL database is as follows:

Please Login to comment...

Similar reads.

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

Java JDBC CRUD Tutorial: SQL Insert, Select, Update, and Delete Examples

• Prerequisites
• Creating a sample MySQL database
• Understand the principal JDBC interfaces and classes
• Connecting to the database
• Executing INSERT statement
• Executing SELECT statement
• Executing UPDATE statement
• Executing DELETE statement

1. Prerequisites

• JDK ( download JDK 7 ).
• MySQL ( download MySQL Community Server 5.6.12 ). You may also want to download MySQL Workbench - a graphical tool for working with MySQL databases.
• JDBC Driver for MySQL ( download MySQL Connector/J 5.1.25 ). Extract the zip archive and put the mysql-connector-java-VERSION-bin.jar file into classpath (in a same folder as your Java source files).

2. Creating a sample MySQL database

source Path\To\The\Script\File\SQLScript.sql

3. Understand the main JDBC interfaces and classes

• DriverManager : this class is used to register driver for a specific database type (e.g. MySQL in this tutorial) and to establish a database connection with the server via its getConnection() method.
• Connection : this interface represents an established database connection (session) from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc.
• Statement and PreparedStatement : these interfaces are used to execute static SQL query and parameterized SQL query, respectively. Statement is the super interface of the PreparedStatement interface. Their commonly used methods are:
• boolean execute(String sql) : executes a general SQL statement. It returns true if the query returns a ResultSet , false if the query returns an update count or returns nothing. This method can be used with a Statement only.
• int executeUpdate(String sql) : executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e.g. 1 row inserted, or 2 rows updated, or 0 rows affected).
• ResultSet executeQuery(String sql) : executes a SELECT statement and returns a ResultSet object which contains results returned by the query.

A prepared statement is one that contains placeholders (in form question marks ?) for dynamic values will be set at runtime. For example:

SELECT * from Users WHERE user_id=?

Here the value of user_id is parameterized by a question mark and will be set by one of the setXXX() methods from the PreparedStatement interface, e.g. setInt(int index, int value) .

• ResultSet : contains table data returned by a SELECT query. Use this object to iterate over rows in the result set using next() method, and get value of a column in the current row using getXXX() methods (e.g. getString() , getInt() , getFloat() and so on). The column value can be retrieved either by index number (1-based) or by column name.
• SQLException : this checked exception is declared to be thrown by all the above methods, so we have to catch this exception explicitly when calling the above classes’ methods.  

4. Connecting to the database

5. jdbc execute insert statement example.

• username: bill
• password: secretpass
• fullname: Bill Gates
• email: [email protected]
• setBoolean(int parameterIndex, boolean x)
• setDate(int parameterIndex, Date x)
• setFloat(int parameterIndex, float x)

6. JDBC Execute SELECT Statement Example

User #1: bill - secretpass - Bill Gates - [email protected]

• getString()
• getTimestamp()

7. JDBC Executing UPDATE Statement Example

8. jdbc execute delete statement example.

• Using a Statement for a static SQL query.
• Using a PreparedStatement for a parameterized SQL query and using setXXX() methods to set values for the parameters.
• Using execute() method to execute general query.
• Using executeUpdate() method to execute INSERT, UPDATE or DELETE query
• Using executeQuery() method to execute SELECT query.
• Using a ResultSet to iterate over rows returned from a SELECT query, using its next() method to advance to next row in the result set, and using getXXX() methods to retrieve values of columns.

JDBC API References:

• DriverManager Class Javadoc
• Connection Interface Javadoc
• Statement Interface Javadoc
• PreparedStatement Interface Javadoc
• ResultSet Interface Javadoc

Related JDBC Tutorials:

• JDBC Transaction Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC
• How to insert binary data into database with JDBC
• How to read binary data from database with JDBC
• How to use Scrollable ResultSet
• How to use Updatable ResultSet
• How to use CachedRowSet

About the Author:

Add comment

Notify me of follow-up comments

Comments