SQL and JDBC(Java Database Connectivity) in Java

This part of Java tutorial will cope with the connectivity of Java with databasis lik…MySql . Aside from connectivity we’ll talk about some fundamental ideas about Sql Language . 


For utilizing SQL you’ll be able to set up MYsQL with its connector from official net Web site of MySql right here . You should utilize this Tutorial as source for connectiong database and Java functions .


Beginning with the Matters that will probably be lined on this part of instructional are :   

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

So , What’s Sql ?


SQL is used to make a request to retrieve knowledge from a database. Everytime you wish to entry knowledge from database it is best to use Sql instructions for this Objective . 


On receiving the request, DBMS system processes that request by retrieving knowledge from the database. This means of requesting knowledge from a database is known as  a database question and therefore SQL.
A database question might be quite simple to very complicated question .


SQL Instructions


Commanda underneath sql can divided into three sections as described Under 

1. DDL Instructions : Create, Alter, Drop, Truncate
2. DML Instructions : Insert, Replace, Delete, Choose
3. DCL Instructions : Grant, Revoke, Commit, Rollback.

Information Varieties


Now Discususing about Datatypes in Sql languge which will probably be very helpful for making and executing the Queries on your necessities .


Char : Mounted Size Character String (Max vary : 2000 bytes)
Varchar2(n) : Variable size Character String (Max vary : 4000 bytes)
Lengthy: Variable size Character String (max one column in a desk) (Max vary: 2GB)
Quantity (p, s): Numbers P for Precision and s for scale.
Date: Date and time (dd-mon-yy format)
Uncooked and Lengthy Uncooked: Binary knowledge


Object Naming Guidelines


Guidelines that have to be adopted for namimg conventions in Sql langauge are given beneath : 

1. The primary letter needs to be alphabet
2. Oracle reserved phrases should not for use.
3. Most size 30 characters
4. Solely Underscore are allowed. 
5. Not case delicate.

Extra About SQL Instructions 


Information Definition Language:

1. Create : Create a brand new desk
2. Alter : Modify the present desk
3. Drop : Drop a desk
4. Truncate : delete all rows from a desk 

Create Desk Command


If the person desires to create a brand new desk it is best to use the next question  :

Create desk  
(
datatype,
datatype,
datatype
);


Alter Desk Command


If the person desires so as to add column to the present desk use the next question   :
Alter desk add ( datatype);


If the person desires to change column within the current desk use the next question  :
Alter desk modify ( datatype);


If the person desires to drop column in current desk use the next question  : 
Alter desk drop column :


Drop, Truncate Instructions 


If the person desires to drop the desk use :
drop desk


If the person desires to delete all information from the desk however not the desk use :
truncate desk


desc Command 


If the person desires to view the construction of the desk use :
desc


Different SQL Instructions  


Information Manipulation Language Instructions :

1. Insert : Insert knowledge in a desk
2. choose : Show knowledge in desk
3. Replace : Replace knowledge in a desk
4. Delete : delete knowledge in a desk

Insert Desk Command


If the person desires to insert knowledge in a desk use:
insert into values ( , , …..);
insert into (, ) values 
( , );


Choose Desk Command


If the person desires to show knowledge in a desk use:
choose * from ;
choose distinct from ;
choose , from ;


Replace Desk Command


If the person desires to change knowledge in a desk use:
replace set subject= worth the place situation;


Delete Desk Command


If the person desires to delete knowledge in a desk use:
     delete from ;
    delete from the place


Remainder of SQL Instructions


Information/Transaction Management Language Instructions:

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

Comm
it Command


If the person desires to save lots of all work completed use:
Commit;

Savepoint Command


Savepoints are markers to divide a really prolonged transaction to smaller ones. They’re used to determine a degree in transaction to which we are able to later rollback;


Savepoint ;
Rollback
Undo the work completed


Rollback;
Rollback to savepoint ;
Grant
Grant permission to different customers use:


Grant all on to
Grant choose, replace on to


Revoke Command


Revoke permission from different customers use:
Revoke all on from
Revoke choose, replace on from


SQL Operators in Sql Language 


Arithmetic Operators:
+    –    *   /
Comparability Operators
=    !=   <   >   <=   >=
between, not between, in, like, not like, is null, will not be null
Logical Operators:
and,  not,  or 
Concatenation Operators
||
SQL Constraints
Not Null
Test
Distinctive
Main
Overseas
On delete cascade




JDBC(Java Database Connectivity)   


It’s a software program layer that permits builders to jot down actual client-server projects in Java. 
JDBC was designed to be a really compact, easy interface specializing in the execution of uncooked SQL statements and retrieving the outcomes. 
The elements of JDBC are Software, Driver supervisor and Driver.


JDBC Parts


Software
Invokes strategies to ship SQL statements to the database and retrieve the outcomes. 


Driver Supervisor
Masses particular drivers for the person software


Driver
Processes strategies invocation, sends SQL statements to an information source & returns results-back to software.


JDBC Driver Fashions


Two Tier Mannequin


Java Purposes work together straight with the database. This kind pf mannequin is known as as client-server configuration the place person is the consumer and database machine is known as as server. 


Three Tier Mannequin


A center tier is launched, which is used to gather SQL from the consumer and handed over to the database and acquire the outcomes from the database and handed to the consumer. 


JDBC Vs. ODBC Vital Variations


ODBC can’t be straight used with Java as a result of it makes use of a C interface. ODBC makes use of pointers which have been completely faraway from Java Langauge thats why ODBC can’t be utilized in Java.


ODBC requires handbook set up of the ODBC driver supervisor and driver on all consumer machines. 


JDBC drivers are written in Java and JDBC code is routinely installable, safe and transportable on all java platforms.

JDBC options 


The JDBC API defines a set of interfaces and courses for use for communications with a database. These interfaces and courses are discovered within the java.sql bundle.


The outcomes of the SQL statements are saved in ResultSet object and getXXX technique used to retrieve the info from ResultSet. 


DSN(Information Supply Identify )


Kinds of DSN

1. File DSN –  Accessibility could be very low
2. System DSN – Simple to entry and by all customers.
3. Person DSN –  Particular to a person however not transportable.

Steps in Java Database Connectivity  

1. Import the java.sql bundle
2. Register the driving force
3. Hook up with a database
4. Create an announcement
5. Execute the assertion
6. Retrieve the outcomes
7. Shut the assertion and connection

Steps for SQL Instructions (besides choose)




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


Steps Used for SELECT


5.
ResultSetMetaData rsmd =rs.getMetaData( );
int depend =rsmd.getColumnCount( );
for(int j=0; j<=depend; j++)
{
System.out.println(rsmd.getColumnName( j)+”t”);
}


Steps Used for SELECT


6. whereas (rs.subsequent( ) );
{
for(int j=0; j<=depend; j++)
{
System.out.println( rs.getString( j)+”t”);
}
}


7. s.shut( );
8. c.shut( );
ResultSet
execute();
returns boolean worth.


executeUpdate();
Returns nothing. 


executeQuery();
Returns ResultSet (Relevant for choose question)
Assertion 
Assertion s = c.createStatement(, );


First Parameters is 
TYPE_SCROLL_SENSITIVE
TYPE_SCROLL_INSENSITIVE
TYPE_FORWARD_ONLY (default)


Second parameters is
CONCUR_READ_ONLY (default)< /span>
CONCUR_UPDATABLE
ResultSet Strategies
afterLast();
Cursor strikes to the final file + 1


earlier();
Cursor strikes backward


absolute (int)
Cursor strikes to absolute place


relative (int)
Cursor strikes to relative place
ResultSet Strategies
final();
Cursor strikes to the final file


updateDouble(,
Replace some Column Worth


moveToInsertRow();
Row might be inserted.


updateRow() / insertRow();
Replace/Insert all modifications completed above this perform.
Instance : 
ResultSet rs=s.executeQuery (“choose * from scholar’);
rs.afterLast();
whereas (rs.earlier( ) );
{
for(int j=0; j<=depend; j++)
{
System.out.println( rs.getString( j)+”t”);
}
}




Instance : Replace Values


ResultSet rs=s.executeQuery (“choose * from scholar’);
rs.final();
rs.updateDouble (“Share”, 85.55);
rs.updateInt (“Marks”, 1255);
rs.updateRow();
Instance : Insert Values
ResultSet rs=s.executeQuery (“choose * from scholar’);
rs.moveToInsertRow();
rs.updateString (“Identify”, “Amit”);
rs.updateDouble (“Share”, 85.55);
rs.updateInt (“Marks”, 1255);
rs.insertRow();
Batch Replace 


A batch replace is a set of a number of replace/insert statements that’s submitted to the database for processing as a batch.


addBatch (“SQL Question”);
Create a brand new Batch which is a set of a number of insert or replace statements. 


Int executeBatch();
Execute the batch. 




Batch Replace : Instance


All insert Assertion get executed or none of it executed. 

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




Questions for Revision of Above Subject?


What are completely different elements of JDBC?
Differentiate between JDBC and ODBC.
Clarify the idea of batch replace in JDBC.