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
- DDL Commands : Create, Alter, Drop, Truncate
- DML Commands : Insert, Update, Delete, Select
- 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 :
- The first letter should be alphabet
- Oracle reserved words are not to be used.
- Maximum length 30 characters
- Only Underscore are allowed.
- Not case sensitive.
More About SQL Commands
Data Definition Language:
- Create : Create a new table
- Alter : Modify the existing table
- Drop : Drop a table
- 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
If the user wants to modify column in the existing table use the following query :
Alter table
If the user wants to drop column in existing table use the following query :
Alter table
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 :
- Insert : Insert data in a table
- select : Display data in table
- Update : Update data in a table
- Delete : delete data in a table
Insert Table Command
If the user wants to insert data in a table use:
insert into
insert into
(
Select Table Command
If the user wants to display data in a table use:
select * from
select distinct from
select
Update Table Command
If the user wants to modify data in a table use:
update
Delete Table Command
If the user wants to delete data in a table use:
delete from
delete from
Rest of SQL Commands
Data/Transaction Control Language Commands:
- Commit: fully save
- savepoint: create a savepoint
- Rollback: Undo
- Grant: Grant permission
- 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
Grant select, update on
Revoke Command
Revoke permission from other users use:
Revoke all on
Revoke select, update on
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
- File DSN – Accessibility is very low
- System DSN – Easy to access and by all users.
- User DSN – Specific to a user but not portable.
Steps in Java Database Connectivity
- Import the java.sql package
- Register the driver
- Connect to a database
- Create a statement
- Execute the statement
- Retrieve the results
- 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.