JDBC - Java Database Connectivity

Index

• Pre-requisite
• Introduction
• JDBC Architecture
• MySQL Queries
• Steps to write a JDBC program
• Types of JDBC statement
• Statement
• PreparedStatement

Pre-requisite

• MySql Download
• JDK 1.8 Download
• Eclipse Download

Introduction

• JDBC is used to interact with various type of Database such as Oracle, MS Access, My SQL and SQL Server.
• JDBC can also be defined as the platform-independent interface between a relational database and Java programming.
• It allows java program to execute SQL statement and retrieve result from database.

JDBC Architecture

MySQL Queries

• Drop (Delete) the database if exist

  DROP DATABASE IF EXISTS employee;

• Create new schema/database if not exist

  CREATE DATABASE IF NOT EXISTS employee;

• Setting the Default Database

USE southwind;

• Show all the tables in the current database.

SHOW TABLES;

• Create the table "products".

CREATE TABLE IF NOT EXISTS products (
productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
productCode CHAR(3) NOT NULL DEFAULT '',
name VARCHAR(30) NOT NULL DEFAULT '',
quantity INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
PRIMARY KEY (productID));

• Show all the tables to confirm that the "products" table has been created

SHOW TABLES;

• Describe the fields (columns) of the "products" table

DESCRIBE products;

• Show the complete CREATE TABLE statement used by MySQL to create this table

SHOW CREATE TABLE products \G

• Inserting Rows

INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);

• Insert multiple rows in one command
• Inserting NULL to the auto_increment column results in max_value + 1

INSERT INTO products VALUES (NULL, 'PEN', 'Pen Blue', 8000, 1.25),(NULL, 'PEN', 'Pen Black', 2000, 1.25);

• Insert value to selected columns
• Missing value for the auto_increment column also results in max_value + 1

INSERT INTO products (productCode, name, quantity, price) 
VALUES
 ('PEC', 'Pencil 2B', 10000, 0.48),
 ('PEC', 'Pencil 2H', 8000, 0.49);

• Missing columns get their default values

INSERT INTO products (productCode, name) VALUES ('PEC','Pencil HB');

• 2nd column (productCode) is defined to be NOT NULL

INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);

• show table

SELECT * FROM products;

• Remove the specific row

DELETE FROM products WHERE productID = 1006;

• List all rows for the specified columns

SELECT name, price FROM products;

Steps to write a JDBC program

Step 1: Load the Driver class Step 2: Establish the connection
Step 3: Create the required statement
Step 4: Prepare the Required SQL statement
Step 5: Submit the SQL statement to Database
Step 6: Process the Results
Step 7: Release the Resources

Types of JDBC statement

• There are three types of JDBC statements.
1) Statement
2) PreparedStatement
3) CallableStatement

Statement

• Statement is an interface available in java.sql package
• The statement object can be created using one of the following methods of connection interface:

Statement createStatement();
Statement createStatement(int,int);
Statement createStatement(int,int,int);

• Once the statement object is created, you can call one of the following methods of statement interface:

ResultSet executeQuery(String)
int executeUpdate(String)
boolean execute(String)

• The executeQuery() method can be used to submit the selected SQL statement to the SQL Engine.
• This method returns the Resultset object which contains the number of records returned by the given selected SQL statement.
• The executeUpdate() method can be used to submit insert, update, and delete SQL statement to SQL Engine.
• This method returns the integer number which represents the number of record affected by the given SQL statement.
• The execute() method can be used to submit insert, update, delete SQL statement to SQL Engine.
• This method returns the Boolean value which represents whether the given operation is insert/update/delete (false) OR Fetch (true).
• Using one statement object, you can submit one or more SQL statements
• When you submit the SQL statement to SQL Engine using statement object, the SQL statement will be compiled and executed every time.

PreparedStatement

• PreparedStatement is an interface available in java.sql package and it extends the Statement interface.
• The PreparedStatement object can be created using one of the following methods of connection interface:

PreparedStatement (String);
PreparedStatement (String, int, int);
PreparedStatement(String,int,int,int);

• Once the preparedStatement object is created, you can call one of the following methods of preparedStatement interface:

ResultSet executeQuery()
int executeUpdate()
boolean execute()

• Using one preparedStatement object, you can submit only one type of SQL statement.
• When you submit the SQL statement to SQL Engine using preparedStatement object, the SQL statement will be compiled only once the first time, and will be executed every time without compilation.
• PreparedStatement is also a sub interface of Statement. So, we pass (con,ps1,rs) in the cleanup method of DBUtil class.
• Where con is Connection object, rs is a ResultSet, ps1 is a Statement