JDBC

Dr. Mareeswari V
School of Computer Science Engineering and Information Systems
(SCORE)
VIT University, Vellore

Cabin No:SJT 210-A30

[Link] V, SCORE,VIT, VELLORE
 JDBC
 Java Database Connectivity (JDBC) is a technology that enables the
java program to manipulate data stored into the database.
 JDBC is Java application programming interface that allows the Java
programmers to access database management system from Java code.
 JDBC is consists of four Components:
1. The JDBC API
2. JDBC Driver Manager
3. The JDBC Test Suite
4. JDBC-ODBC Bridge.

[Link] V, SCORE,VIT, VELLORE

The JDBC library includes APIs for each of the tasks commonly
associated with database usage:
 Making a connection to a database
 Creating SQL or MySQL statements
 Executing that SQL or MySQL queries in the database
 Viewing & Modifying the resulting records

[Link] V, SCORE,VIT, VELLORE

[Link] V, SCORE,VIT, VELLORE
Understanding Common SQL statements
 The commonly used SQL statements are: create, select, insert,
update and delete
 CREATE TABLE table_name(field_name):
 The SELECT statement is used to select data from a table.
SELECT column_names FROM table_name;
 The INSERT statement allows you to insert a single or multiple
records into the database. We can specify the name of the column
in which we want to insert the data.
INSERT INTO table_name VALUES (value1, value2..);
[Link] V, SCORE,VIT, VELLORE
Understanding Common SQL statements
 The Update statement is used to modify the data in the table.
Whenever we want to update or delete a row then we use the
Update statement.
UPDATE table_name SET colunm_name = new_value WHERE
column_name = some_name;
 The delete statement is used to delete rows in a table.
DELETE FROM table_name WHERE column_name =
some_name;

[Link] V, SCORE,VIT, VELLORE

 Creating JDBC Application
 Step 1: Import the packages that containing the JDBC classes
needed for database programming.
import [Link].*;
 Step 2: Register the JDBC driver
This requires that you initialize a driver so you can open a
communications channel with the database.
[Link]("[Link]");

[Link] V, SCORE,VIT, VELLORE

 Step 3 : Open a connection
 This requires using the [Link]() method to
create a Connection object, which represents a physical connection with
the database as follows:
String url = "jdbc:mysql://localhost:3306/mydatabase";
String uname = "myusername";
String pwd = "mypassword";
Connection conn = [Link](url, uname, pwd);
 Connection object creates the Statement, PreparedStatement and
CallableStatement objects for executing the SQL statements. It helps us to
Commit or roll back a jdbc transaction.
[Link] V, SCORE,VIT, VELLORE
 Step 4 : Execute a query
This requires using an object of type Statement or PreparedStatement
for building and submitting an SQL statement to the database as
follows:
Statement s=[Link]();
String sql ="Select * from students“;
ResultSet rs=[Link](sql);
 String sql = "CREATE TABLE students (id INTEGER not NULL, name
VARCHAR(50), age INTEGER PRIMARY KEY ( id ))";
 [Link](sql); // returns the 0/1 of result set created
[Link] V, SCORE,VIT, VELLORE
Step 4 : Execute a query
 If there is an SQL UPDATE,INSERT or DELETE statement
required, then following code snippet would be required:
String sql = "DELETE FROM Employees";
ResultSet rs = [Link](sql);
[Link](2); extract 2nd record
 [Link](sql); // returns the [Link] rows affected

[Link] V, SCORE,VIT, VELLORE

 Statement Object
 Provides workspace for creating an SQL query, execute it, and
retrieve the results that are returned.
 Statement objects are created by calling the createStatement() method
of a valid connection object
 JDBC Provides two other kinds of objects to execute SQL statement:
 PreparedStatement -> extends Statement class
 CallableStatement -> extends PreparedStatement class

[Link] V, SCORE,VIT, VELLORE

Step 5: insert the row
 String sql = "INSERT INTO Students (id, name, age)
VALUES (?, ?, ?)";
 PreparedStatement pstmt = [Link](sql);
 [Link](1, 1001); // Set 'id' to 1001
 [Link](2, "John"); // Set 'name' to "John"
 [Link](3, 18); // Set 'age' to 18
 int rowsAffected = [Link]();

[Link] V, SCORE,VIT, VELLORE

Step 6 : Extract data from result set
 This step is required in case you are fetching data from the database.
while([Link]()) {
int id = [Link]("id"); //Retrieve by column name
int age = [Link]("age");
String first = [Link]("first"); //[Link](3);
String last = [Link]("last");
//Display values
[Link]("ID: " + id + ", Age: " + age );
[Link](", First: " + first + ", Last: " + last);
}
[Link] V, SCORE,VIT, VELLORE
 When retrieving data from the ResultSet, use the appropriate
getXXX() method
 getString()
 getInt()
 getDouble()
 getLong()
 getObject()
 There is an appropriate getXXX method of each [Link] datatype
 String value1 = [Link](1); Column number starts from 1
 int value2 = [Link](2);
 int value3 = [Link](“ADDR_PIN");
[Link] V, SCORE,VIT, VELLORE
 Column names are NOT case sensitive
Step 7 : Clean up the environment
 You should explicitly close all database resources versus
relying on the JVM's garbage collection as follows:
[Link]();
[Link]();
[Link]();

[Link] V, SCORE,VIT, VELLORE

 PreparedStatement
String sql = "SELECT title,year_made FROM movies WHERE
year_made >= ? AND year_made <= ?";
PreparedStatement prest = [Link](sql);
[Link](1,1980);
[Link](2,2004);
ResultSet rs = [Link]();

[Link] V, SCORE,VIT, VELLORE

Callable statement
 JDBC Callable statement provides a way to call the stored
procedure of the database. These procedures stored in the
database. This calls are written in escape syntax that may take
one or two forms. The CallableStatement object is created by
calling a prepareCall() method of connection.
CallableStatement cs = [Link]("CALL HI()");

[Link] V, SCORE,VIT, VELLORE

JDBC Component Interaction

[Link] V, SCORE,VIT, VELLORE

 BASIC COMMANDS
 CREATE DATABASE VIT_DB
 USE VIT_DB
 CREATE TABLE EMPLOYEE(EMPID NUMBER(3), EMPNAME
VARCHAR(20), SALARY FLOAT);
 INSERT INTO EMPLOYEE VALUES(100,”RAM”,10000.50);
 UPDATE EMPLOYEE SET SALARY=20000.75 WHERE
EMPID=100;
 DELETE FROM EMPLOYEE WHERE EMPID=100;
 SELECT EMPID, EMPNAME, SALARY FROM EMPLOYEE;
[Link] V, SCORE,VIT, VELLORE
 Mysql connector
1. Open your project’s [Link] file
2. Add the dependency inside the <dependencies> section:
3. NetBeans will automatically download the mysql connector from Maven Central.

[Link] V, SCORE,VIT, VELLORE

 Java JDBC with MySQL
import [Link]. *;
public class JDBCMySQL { public static void main(String[] args) {
// JDBC URL, username, and password of MySQL server
String url = "jdbc:mysql://localhost:3306/mareesdb";
String user = "root"; String password = "";
try { // Load MySQL JDBC Driver
[Link]("[Link]");
Connection con = [Link](url, user, password); // Establish connection
Statement stmt = [Link](); // Create statement
String query = "SELECT id, name, email FROM students"; // Execute query
ResultSet rs = [Link](query);

[Link] V, SCORE,VIT, VELLORE

 // Display results
[Link]("ID\tName\tEmail");
while ([Link]()) {
int id = [Link]("id");
String name = [Link]("name");
String email = [Link]("email");
[Link](id + "\t" + name + "\t" + email); }
[Link](); [Link](); [Link](); }
catch (Exception e) {
[Link](); } }}

[Link] V, SCORE,VIT, VELLORE

Output in Netbeans and Mysql shell

[Link] V, SCORE,VIT, VELLORE

 Exercise
 A company maintains a database called EmployeeDB with a table
Attendance.
 Each record in Attendance contains:
 emp_id (integer) – unique employee ID
 status (string) – either "Present" or "Absent"
 Write a Java program using JDBC to connect to the MySQL
database and display the list of employees who are marked "Absent"
for the day.

[Link] V, SCORE,VIT, VELLORE

 Exercise
A university maintains a database UniversityDB with a table Courses having the
following structure:
 course_id (INT, Primary Key)
 course_name (VARCHAR)
 enrolled_students (INT)
1. A new elective course "AI for Beginners" with course_id = 105 is introduced.
Write a Java JDBC program to insert this record into the Courses table with
enrolled_students = 0.
2. After registration, 25 students enrolled in "AI for Beginners". Write a Java JDBC
program to update the enrolled_students field for course_id = 105.
3. The course "AI for Beginners" is discontinued. Write a Java JDBC program to
delete the record with course_id = 105.
[Link] V, SCORE,VIT, VELLORE
 [Link]

[Link] V, SCORE,VIT, VELLORE