25 JDBC –Part II
Dr M. Vijayalakshmi
SQL
It stands for Structured Query Language. SQL means Standardized syntax for “querying” or accessing a Relational database.
SQL Syntax
Before we study about working JDBC with SQL, let us have a basic understanding of how to write SQL queries. Some of the SQL statements which would appear in the example programs are given below.
This command can insert a new record into the named table.
INSERT INTO table name( field1, field2 ) VALUES ( value1, value2 )
This command can change an existing record or records.
UPDAT E tablename SET( field1 = value1, field2 = value2 ) WHERE condition
This command can remove all records that match condition.
DELET E FROM tablename WHERE condition
This command can retrieve all records that matches the condition.
SELECT field1, field2 FROM tablename WHERE condition
Install Mysql
Let us now see the steps of installing MySQL to work with JDBC.
Step 1: We need to Install Mysql Database from MYSQL official website dev.mysql.com. Figure 27.1 shows the Google search for Download of MySQL database.
Step 2: Select the Platform type (i.e. Windows or Mac) and download the Installer. Figure 27.1 shows selecting the platform for Windows OS.
Step 3: Create Database
Once MySQL is installed in your machine, Open MySQL Comand Line and connect to MySQL server by specifying the user name and password in the command line arguments to mysql command.Then create a Database by using the below SQLstatement.
Syntax
CREAT E DATABASE databasename;
Figure 27.3 shows creating the database in the MySQL command line using the above statement.
Step 4: USE Database
From the list of database created, to use a particular database we use the command ‘USE database name’.
Syntax
USE databasename;
Figure 27.4 shows how to use the database using the above statement.
Step 5: Create Table
Now we can create Tables in Database using the create statement.
Syntax
CREAT E TABLE tablename(attribute name datatypes);
Figure 27.5 shows how to create tables using the above statement.
Step 6: Insert Query in Table
Once the table is created we can now Insert the values in the table. Note: For varchar data type provide the values within quotes . Syntax
INSERT INTO tablename VALUES(data);
Figure 27.6 shows how to insert records into the table using the above statement.
Step 7: Select Query in Table
The Select statement can be used now to list all the values entered into the Table.
Syntax
SELECT * FROM tablename;
Figure 27.7 shows how to select the records from the table using the above statement.
Step 8: Delete Query in Table
To delete a particular Row from the table.
Syntax
DELET E FROM tablename WHERE attributename=‘value’;
Figure 27.8 shows how to delete a record from the table using the above statement.
Step 9: Update Query in Table
To update values in a Row.
Syntax
UPDAT E tablename SET attributename=newvalue WHERE attributename=value;
Figure 27.9 shows how to update records in the table using the above statement.
Transactions
A Transaction means more than one statement which must all succeed (or all fail) together. If one fails, the system must reverse all previous actions. Also a transaction cannot leave database in an inconsistent state halfway through a transaction. The operation COMMIT is done for a complete transaction and ROLLBACK is done for an aborted transaction.
Mapping Java Types to SQL Types
Table27.1 shows the existence of Java types for each SQL types.
Database Time
Times in SQL are notoriously not standard.
Java defines three classes to represent Date and Time.
- java.sql.Date
This class includes year, month, day.
- java.sql.Time
This class includes hours, minutes, seconds.
- java.sql.Timestamp
This class includes year, month, day, hours, minutes, seconds and nanoseconds.
JDBC Interface classes
JDBC Interface classes are contained in java.sql.* package. This consists of the following classes.
- DriverManager
- Connection
- Statement
- CallableStatement
- PreparedStatement
- Resultset
- ResultSetMetaData
- DatabaseMetaData
The JDBC Steps
1. Importing Packages.
2. Registering the JDBC Drivers.
3. Opening a Connection to a Database.
4. Creating a Statement Object.
5. Executing a Query and Returning a Result Set Object.
6. Processing the Result Set.
7. Closing the Result Set and Statement Objects.
8. Closing the Connection.
Simple Example
import java.sql;
class SimpleExam ple {
public static void main(String args[]) {
String url = “jdbc:odbc:jnf”;
try {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); Connection myConnection = DriverManager.getConnection(url,”IT”,”it@123”); myConnection.close();
}
catch(Exception e) {
e.printStackTrace();
}
}
}
This example code explains about registering for the driver an loading the driver using the URL, user name and password.
Sending SQL statements
……
String query= “Select name,id,salary FROM employees ORDER By salary”; Connection myConnection = DriverManager.getConnection(…..);
Statement myStatement = myConnection.createStatement();
ResultSet rs = myStatement.executeQuery(query);
while(rs.next)
{
String empName = rs.getString(1);
String empId = rs.getString(2);
String empSalary = rs.getString(3);
System.out.println(“Em ployee ” + empName + “ with id ” + empId + “ earns ” + empSalary);
}
myStatement.close();
myConne ction.close ();
…….
Once Connection is established, Statement object is created. This Statement object is used to execute the query. The SQL query that has been tried in this example is an ‘Select’ query. Hence the retrieved records are read sequentially and displayed. Then the connection is closed.
Simple Program
The complete executing code for the example is given below.
import java.sql.*;
public class JDBC
{
public static void main(String []args)throws Exception
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection
con=DriverManager.getConnection(“jdbc:odbc:sample”);
Statement st=con.createStatement();
String query=”select * from Student”;
ResultSet rs=st.executeQuery(query);
while(rs.next())
{
System.out.println(rs.getString(“name”));
System.out.println(rs.getInt(“rollno”));
System.out.println(rs.getInt(“mark1”));
System.out.println(rs.getInt(“mark2”));
}
con.close();
}
}
Example Program
This is another example program where the user is allowed to do all database operations such as select, insert, update, delete and clearing the screen. A connection is established to the URL jdbc :odbc:jnf2. The database name is jnf2 and the table name is Student. The user is displayed with a menu of operations. Upon selecting the choice, its corresponding function is called. Functions like display(), clrscr(), insert(), modify() and delete() have been coded.
In the function display() the method executeQuery() is called with the Statement object by passing the SQL SELECT statement which displays the records from the table.
In the function insert() the method executeUpdate() is called with the Statement object by passing the SQL INSERT statement which inserts a record into the table.
In the function modify() the method executeUpdate() is called with the Statement object by passing the SQL UPDATE statement which updates a single record in the table.
In the function delete() the method executeUpdate() is called with the Statement object by passing the SQL DELETE statement which deletes a record from the table.
In the function clrscr(), a for loop is executed which displays blank for 25 lines thereby it clears the screen.
Within each function a Connection object is created and closed at the end of the function. This is avoid connection errors while executing.
import java.sql.*;
import java.io.*;
public class JDBC2
{
public static void main(String []args)throws Exception
{
int ch;
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
InputStreamReader i = new InputStreamReader(System.in); BufferedReader b = new BufferedReader(i); try
{
Connection c;
c = DriverManager.getConnection(“jdbc:odbc:jnf2”);
do
{
System.out.println(“\t\t\t\t Menu”);
System.out.println(“\t\t\t\t 1.View”);
System.out.println(“\t\t\t\t 2.Clear”);
System.out.println(“\t\t\t\t 3.Insert”);
System.out.println(“\t\t\t\t 4.Modify”);
System.out.println(“\t\t\t\t 5.Delete”);
System.out.println(“\t\t\t\t 6.Exit”);
System.out.println(“Enter your choice:”);
ch= Integer.parseInt(b.readLine());
switch(ch)
{
case 1:
{
display();
break;
}
case 2:
{
clrscr();
break;
}
case 3:
{
insert();
display();
break;
}
case 4:
{
modify();
display();
break;
}
case 5:
{
delete();
display();
break;
}
case 6:
{
System.exit(0);
break;
}
default:
System.out.println(“invalid input”);
}}while(ch!=6);}
catch(Exception e){}
}
static void display()
{ try
{
ResultSet rs;
int row=0;
Connection c1;
c1 = DriverManager.getConnection(“jdbc:odbc:jnf2”); Statement st=c1.createStatement();
rs=st.executeQuery(“select * from student”);
System.out.println(“\nRegno\tName\tClass\tMark”);
while(rs.next())
System.out.println(rs.getInt(1)+”\t”
+rs.getString(2)+”\t”+rs.getString(3)+”\t”+rs.getInt(4); c1.close();
}
catch(Exception e){}
}
static void insert()
{
try
{
int row1;
Statement insst;
Connection c2;
c2 = DriverManager.getConnection(“jdbc:odbc:jnf2”);
insst=c2.createStatement();
System.out.println(“9,jnf,vbj,30”);
row1=insst.executeUpdate(“insert into Student” + ” values (9,’jnf’,’vbj’,100)”);
c2.commit();
System.out.println(“No of rows inserted = ” + row1);
}
catch(SQLException e){System.out.println(“error” + e);}
}
static void modify()
{
try
{
System.out.println(“6,jnf,vbj,change 30 to 80”);
int row2;
Statement modst;
Connection c3;
c3 = DriverManager.getConnection(“jdbc:odbc:jnf2”); modst=c3.createStatement();
row2=modst.executeUpdate(“update student set mark=90 where regno=6”);
c3.commit();
System.out.println(“No of rows update d = ” + row2);
}
catch(SQLException e){System.out.println(“error” + e);}
}
static void delete()
{
try
{
System.out.println(“6,jnf,vbj,90”);
int row3;
Statement delst;
Connection c4;
c4 = DriverManager.getConnection(“jdbc:odbc:jnf2”);
delst=c4.createStatement();
row3=delst.executeUpdate(“delete from student where regno=6”);
if(row3>0)
{
c4.commit();
System.out.println(“No of rows update d = ” + row3);
}
else
{ System.out.println(“record not found”); }
}
catch(SQLException e){System.out.println(“error” + e);}
}
static void clrscr()
{
for(int k=1;k<25;k++)
System.out.println();
}
}
The output of this code is shown in the below Figure 27.10 and Figure 27.11.
Results
Transaction Management
Transactions are not explicitly opened and closed. Instead, the connection has a state called AutoCommit mode. If AutoCommit is true, then every statement is automatically committed. The default case is true.
setAutoCommit
Connection.setAutoCommit(boolean)
If AutoCommit is false, then every statement is added to an ongoing transaction. It must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback().
Statements
There are three types of statements called,
- Statement
- PreparedStatement
- CallableStatement
The Statement object is used for executing a static SQL statement and obtaining the results produced by it as we have seen earlier.
Prepared Statements
Prepared Statements are used for queries that are executed many times. They are parsed (compiled) by the DBMS only once. The column values can be set after compilation. Instead of values, ‘?’ is used and it can be substituted later with actual values.
Querying with PreparedStatement
Example
String queryStr =
“SELECT * FROM employee ” +
“WHERE id = ? and salary > ?”;
PreparedStatement pstmt = con.pre pareStatement(queryStr);
pstmt.setString(1, “E2001″);
pstmt.setInt(2, 48000);
ResultSet rs = pstmt.executeQuery();
In this example code, the SELECT statement is given with ‘?’ for the two attributes ‘id’ and ‘salary’. PreparedStatement object is created by passing this query string. Then we use methods like setString() and setInt() to pass values for id and salary. Then the query is executed using executeQuery() method.
Updating with PreparedStatement
Example
String deleteStr =
“DELET E FROM employee ” +
“WHERE id = ? and salary > ?”;
PreparedStatement pstmt = con.pre pareStatement(deleteStr); pstmt.setString(1, “E2001″);
pstmt.setDouble(2, 48000);
int delnum = pstmt.executeUpdate();
Statements vs. PreparedStatements
Example
The difference in syntax is given with an example in the below Table 27.2.
Table 27.2 Difference between Statement and PreparedStatement
CallableStatement
A CallableStatement is used to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.
A CallableStatement object is created using the Connection.prepareCall() method.
Example
CallableStatement cstmt = null;
try {
String SQL = “{call getEmpName (?, ?)}”;
cstmt = conn.prepareCall(SQL);
. . .
}
catch (SQLException e) { . . . }
finally { . . . }
ResultSet Meta-Data
A ResultSetMetaData is an object that can be used to get information about the properties of the columns in a ResultSet object.
Example
ResultSetMetaData rsmd = rs.getMetaData();
int numcols = rsmd.getColumnCount();
for (int i = 1 ; i <= numcols; i++)
{
System.out.print(rsmd.getColumnLabel(i)+” “);
}
The method getMetaData() is used with the ResultSet object which returns a ResultSetMetaData object. With the ResultSetMetaData object, use the method getColumnCount() which returns the number of columns in every record. Then we can use a for loop that executes for the number of columns to display the column label using the method getColumnLabel().
Summary
This module explains about how to work with SQL. It has explored the working of JDBC with simple programming illustrations. This section also discusses about the transaction management in JDBC and its basic operations that can be performed.
Web Links
- https://pawangkp.files.wordpress.com/2009/10/deawsj-6_ppt_1b.ppt
- https://www.tutorialspoint.com/jdbc/jdbc-statements.htm