Share with others

SQL and JDBC(Java Database Connectivity)

This section of Java tutorial will deal with the connectivity of Java with databasis lik…MySql . Apart from connectivity we will discuss some basic concepts about Sql Language . 

For using SQL you can install MYsQL with its connector from official web Site of MySql here . You can use this Tutorial as source for connectiong database and Java applications .

Starting with the Topics that will be covered in this section of tutorial are :   

  • Introduction to SQL
  • DDL Statements
  • DML Statements
  • Relational Operators
  • JDBC Connectivity 

So , What is Sql ?

SQL is used to make a request to retrieve data from a database. Whenever you want to access data from database you should use Sql commands for this Purpose . 

On receiving the request, DBMS system processes that request by retrieving data from the database. This process of requesting data from a database is called  a database query and hence SQL.
A database query can be very simple to very complex query .

SQL Commands

Commanda under sql can divided into three sections as described Below 

  1. DDL Commands : Create, Alter, Drop, Truncate
  2. DML Commands : Insert, Update, Delete, Select
  3. DCL Commands : Grant, Revoke, Commit, Rollback.

Data Types

Now Discususing about Datatypes in Sql languge which will be very useful for making and executing the Queries for your requirements .

Char : Fixed Length Character String (Max range : 2000 bytes)
Varchar2(n) : Variable length Character String (Max range : 4000 bytes)
Long: Variable length Character String (max one column in a table) (Max range: 2GB)
Number (p, s): Numbers P for Precision and s for scale.
Date: Date and time (dd-mon-yy format)
Raw and Long Raw: Binary data

Object Naming Rules

Rules that must be followed for namimg conventions in Sql langauge are given below : 

  1. The first letter should be alphabet
  2. Oracle reserved words are not to be used.
  3. Maximum length 30 characters
  4. Only Underscore are allowed. 
  5. Not case sensitive.

More About SQL Commands 

Data Definition Language:

  1. Create : Create a new table
  2. Alter : Modify the existing table
  3. Drop : Drop a table
  4. Truncate : delete all rows from a table 

Create Table Command

If the user wants to create a new table you should use the following query  :

Create table  

Alter Table Command

If the user wants to add column to the existing table use the following query   :
Alter table add ( datatype);

If the user wants to modify column in the existing table use the following query  :
Alter table modify ( datatype);

If the user wants to drop column in existing table use the following query  : 
Alter table drop column :

Drop, Truncate Commands 

If the user wants to drop the table use :
drop table

If the user wants to delete all records from the table but not the table use :
truncate table

desc Command 

If the user wants to view the structure of the table use :

Other SQL Commands  

Data Manipulation Language Commands :

  1. Insert : Insert data in a table
  2. select : Display data in table
  3. Update : Update data in a table
  4. Delete : delete data in a table

Insert Table Command

If the user wants to insert data in a table use:
insert into values ( , , …..);
insert into (, ) values 
( , );

Select Table Command

If the user wants to display data in a table use:
select * from ;
select distinct from ;
select , from ;

Update Table Command

If the user wants to modify data in a table use:
update set field= value where condition;

Delete Table Command

If the user wants to delete data in a table use:
     delete from ;
    delete from where

Rest of SQL Commands

Data/Transaction Control Language Commands:

  1. Commit: fully save
  2. savepoint: create a savepoint
  3. Rollback: Undo 
  4. Grant: Grant permission
  5. Revoke : Revoke permission

Commit Command

If the user wants to save all work done use:

Savepoint Command

Savepoints are markers to divide a very lengthy transaction to smaller ones. They are used to identify a point in transaction to which we can later rollback;

Savepoint ;
Undo the work done

Rollback to savepoint ;
Grant permission to other users use:

Grant all on to
Grant select, update on to

Revoke Command

Revoke permission from other users use:
Revoke all on from
Revoke select, update on from

SQL Operators in Sql Language 

Arithmetic Operators:
+    –    *   /
Comparison Operators
=    !=   <   >   <=   >=
between, not between, in, like, not like, is null, is not null
Logical Operators:
and,  not,  or 
Concatenation Operators
SQL Constraints
Not Null
On delete cascade

JDBC(Java Database Connectivity)   

It is a software layer that allows developers to write real client-server projects in Java. 
JDBC was designed to be a very compact, simple interface focusing on the execution of raw SQL statements and retrieving the results. 
The components of JDBC are Application, Driver manager and Driver.

JDBC Components

Invokes methods to send SQL statements to the database and retrieve the results. 

Driver Manager
Loads specific drivers for the user application

Processes methods invocation, sends SQL statements to a data source & returns results-back to application.

JDBC Driver Models

Two Tier Model

Java Applications interact directly with the database. This type pf model is called as client-server configuration where user is the client and database machine is called as server. 

Three Tier Model

A middle tier is introduced, which is used to collect SQL from the client and handed over to the database and collect the results from the database and handed to the client. 

JDBC Vs. ODBC Important Differences

ODBC cannot be directly used with Java because it uses a C interface. ODBC makes use of pointers which have been totally removed from Java Langauge thats why ODBC cannot be used in Java.

ODBC requires manual installation of the ODBC driver manager and driver on all client machines. 

JDBC drivers are written in Java and JDBC code is automatically installable, secure and portable on all java platforms.

JDBC features 

The JDBC API defines a set of interfaces and classes to be used for communications with a database. These interfaces and classes are found in the java.sql package.

The results of the SQL statements are stored in ResultSet object and getXXX method used to retrieve the data from ResultSet. 

DSN(Data Source Name )

Types of DSN

  1. File DSN –  Accessibility is very low
  2. System DSN – Easy to access and by all users.
  3. User DSN –  Specific to a user but not portable.

Steps in Java Database Connectivity  

  1. Import the java.sql package
  2. Register the driver
  3. Connect to a database
  4. Create a statement
  5. Execute the statement
  6. Retrieve the results
  7. Close the statement and connection

Steps for SQL Commands (except select)

1. class.forName (”sun.jdbc.odbc.JdbcOdbcDriver”);
2. Connection c= DriverManager.getConnection (“Jdbc.odbc:DSN”);
3. Statement s = c.createStatement( );
4. s.executeUpdate (“ SQL INSERT/UPDATE”);
Steps Used for SELECT
1. class.forName (”sun.jdbc.odbc.JdbcOdbcDriver”);
2. Connection c= DriverManager.getConnection (“Jdbc.odbc:DSN”, “Username”, “Password”);
3. Statement s = c.createStatement( );
4. ResultSet rs =s.executeQuery (“ SQL INSERT/UPDATE”);

Steps Used for SELECT

ResultSetMetaData rsmd =rs.getMetaData( );
int count =rsmd.getColumnCount( );
for(int j=0; j<=count; j++)
System.out.println(rsmd.getColumnName( j)+”\t”);

Steps Used for SELECT

6. while ( ) );
for(int j=0; j<=count; j++)
System.out.println( rs.getString( j)+”\t”);

7. s.close( );
8. c.close( );
returns boolean value.

Returns nothing. 

Returns ResultSet (Applicable for select query)
Statement s = c.createStatement(, );

First Parameters is 

Second parameters is
ResultSet Methods
Cursor moves to the last record + 1

Cursor moves backward

absolute (int)
Cursor moves to absolute position

relative (int)
Cursor moves to relative position
ResultSet Methods
Cursor moves to the last record

Update some Column Value

Row could be inserted.

updateRow() / insertRow();
Update/Insert all changes done above this function.
Example : 
ResultSet rs=s.executeQuery (“select * from student’);
while (rs.previous( ) );
for(int j=0; j<=count; j++)
System.out.println( rs.getString( j)+”\t”);

Example : Update Values

ResultSet rs=s.executeQuery (“select * from student’);
rs.updateDouble (“Percentage”, 85.55);
rs.updateInt (“Marks”, 1255);
Example : Insert Values
ResultSet rs=s.executeQuery (“select * from student’);
rs.updateString (“Name”, “Amit”);
rs.updateDouble (“Percentage”, 85.55);
rs.updateInt (“Marks”, 1255);
Batch Update 

A batch update is a set of multiple update/insert statements that is submitted to the database for processing as a batch.

addBatch (“SQL Query”);
Create a new Batch which is a set of multiple insert or update statements. 

Int executeBatch();
Execute the batch. 

Batch Update : Example

All insert Statement get executed or none of it executed. 

Statement s= c.createStatement();
s.addBatch (“Insert Statement – 1”);
s.addBatch (“Insert Statement – 2”);
s.addBatch (“Insert Statement – 3”);
s.addBatch (“Insert Statement – 4”);
s.addBatch (“Insert Statement – 5”);

Questions for Revision of Above Topic?

What are different components of JDBC?
Differentiate between JDBC and ODBC.
Explain the concept of batch update in JDBC. 

Share with others