SQL and JDBC(Java Database Connectivity) in Java

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  
(
datatype,
datatype,
datatype
);


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 :
desc


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:
Commit;

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 ;
Rollback
Undo the work done


Rollback;
Rollback to savepoint ;
Grant
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
Check
Unique
Primary
Foreign
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


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


Driver Manager
Loads specific drivers for the user application


Driver
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


5.
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 (rs.next( ) );
{
for(int j=0; j<=count; j++)
{
System.out.println( rs.getString( j)+”\t”);
}
}


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


executeUpdate();
Returns nothing. 


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


First Parameters is 
TYPE_SCROLL_SENSITIVE
TYPE_SCROLL_INSENSITIVE
TYPE_FORWARD_ONLY (default)


Second parameters is
CONCUR_READ_ONLY (default)
CONCUR_UPDATABLE
ResultSet Methods
afterLast();
Cursor moves to the last record + 1


previous();
Cursor moves backward


absolute (int)
Cursor moves to absolute position


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


updateDouble(,
Update some Column Value


moveToInsertRow();
Row could be inserted.


updateRow() / insertRow();
Update/Insert all changes done above this function.
Example : 
ResultSet rs=s.executeQuery (“select * from student’);
rs.afterLast();
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.last();
rs.updateDouble (“Percentage”, 85.55);
rs.updateInt (“Marks”, 1255);
rs.updateRow();
Example : Insert Values
ResultSet rs=s.executeQuery (“select * from student’);
rs.moveToInsertRow();
rs.updateString (“Name”, “Amit”);
rs.updateDouble (“Percentage”, 85.55);
rs.updateInt (“Marks”, 1255);
rs.insertRow();
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”);
s.executeBatch();




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.