CPAN322 Java Application Programming
Lecture #7: JDBC
JDBC is a technology that provides Java API to connect Java applications with SQL based databases. The java.sql package contains Java classes that support JDBC API.
JDBC also provide a bridge to Open Database Connectivity through JDBC-ODBC driver. ODBC is Microsoft C-based technology to interface with numerous SQL-based databases.
Steps for connecting to any database engine involve:
· Loading java driver class for a particular database.
· Obtaining a connection using DriverManager class.
· Creating a Statement object from the Connection.
· Using the Statement Object to dispatch commands to the database.
In this module we will explain how to access Oracle Database server. An Oracle account is created for you under SUNSERV1 server.
To connect to Oracle database, you need to download the JDBC thin driver from http://oracle.com or you can click here
Once you have the driver you can add it to your class path. Following is an example of a batch file that I run on my machine to compile and execute my Java applications that access Oracle database server :
set path=%path%;C:\j2sdk1.4.0_03\bin;set classspath=.;C:\JDBC-ORACLE\classes12.zip;
javac db.java
java db
pause
Loading the Oracle driver in our Java code is accomplished using the following statements:
String driver="oracle.jdbc.OracleDriver";
Class.forName( driver);
After that we need to establish connection to the database. To do that we use the following statements:
String url =""jdbc:oracle:thin:@sunserv1.humber.org:1521:orcl" ;
Connection con = DriverManager.getConnection(url,"user","password");
Where user represents your Oracle user ID and password represents your password.
Creating a statement based on the connection is done as follows:
Statement stmt = con.createStatement();
To query a table, we call executeQuery method and pass the proper query string. For example, if we want to query a table called customer, we can write the following code:
String query="select * from customer";
ResultSet rs=stmt.executeQuery(query);
The following statement loops through the ResultSet object:
while (rs.next())
{
…
}
To get information about the types and properties of the columns in a ResultSet object, we use the ResultSetMetaData interface. We will see how to use this interface in example1.
To manipulate a table we call executeUpdate method and pass the proper SQL command. This can be any of the commands: insert, update, delete, create table, or drop table. executeUpdate method returns the number of rows that were affected by the command. For example:
String command=”delete from customer”;
int count stmt.exeuteUpdate(command);
All changes are committed automatically by default. To change that, use the statement:
con.setAutoCommit(false);
Then you can commit or rollback manually using con.commit() and con.rollback() . The SQL commands commit and rollback are used to manage transactions. A transaction is a set of database updates that should all succeed, or not happen at all. Therefore it is recommended to set autoCommit to false. When all the SQL update statements succeed, we issue a commit command, otherwise we issue a rollback command.
To close the connection, we use:
con.close();
Another useful interface is PreparedStatement. This interface enables us to execute the same SQL statement repeatedly with different parameter values. The following example shows how to execute a parametric query statement:
String query="select * from customer where cust_lname= ? and city=? ";
PreparedStatement ps=connection.prepareStatement(query);
The question mark is a placeholder for a value that will be passed as part of this query. This value must be set using setString method before the query is executed. For example:
ps.setString(1,"Smith");
ps.setString(2,"Toronto");
The first parameter of setString method represents the parameter number. The first parameter in the query is assigned the number (1), the second is assigned the number (2), and so on. The second parameter represents the value for this parameter.
After that we can execute this query using the statement:
ResultSet rs=ps.executeQuery();
The same procedure apply for manipulating database tables, except that we have to executeUpdate method instead of executeQuery method.
Examples:
The following examples assumes that we have an Oracle table called emp which has the following columns:
Field Name
/Field Type
Id / NUMBER(6) ( primary key)LastName / VARCHAR2(10)
FirstName / VARCHAR2(10)
Ex1:TestDB.java
This is a Java Console application that queries the emp table under SUNSERV1 server. We will use ResultSetMetaData interface to obtain information about this table (the table Meta Data), and display the result set in a tabular format.
import java.sql.*;
import oracle.jdbc.driver.*;
public class TestDB {
public static void main(String[] args) {
String driver="oracle.jdbc.OracleDriver";
try
{
// load Oracle thin JDBC driver
Class.forName(driver);
//create connection
String url="jdbc:oracle:thin:@sunserv1.humberc.on.ca:1521:orcl";
Connection con = DriverManager.getConnection(url,"user","password");
// Create a statement
Statement statement = con.createStatement();
// Define query
String query ="SELECT * from emp where ID=1";
// Execute query and save results
ResultSet results = statement.executeQuery(query);
// Print column names
ResultSetMetaData metadata;
metadata=results.getMetaData();
int col=metadata.getColumnCount();
for(int j=1;j<=col;j++)
System.out.print(fillSpace("-",metadata.getColumnName(j).length()*3)+"|");
System.out.print("\n");
for(int j=1;j<=col;j++)
System.out.print(addSpace(metadata.getColumnName(j),metadata.getColumnName(j).length()*5));
System.out.print("\n");
for(int j=1;j<=col;j++)
System.out.print(fillSpace("-",metadata.getColumnName(j).length()*3)+"|");
System.out.print("\n");
while(results.next())
{
System.out.println
(addSpace(results.getString(1), metadata.getColumnName(1).length()*5) +
addSpace(results.getString(2), metadata.getColumnName(2).length()*4) +
addSpace(results.getString(3), metadata.getColumnName(3).length()*4) );
for(int j=1;j<=col;j++)
System.out.print(fillSpace("-",metadata.getColumnName(j).length()*3)+"|");
System.out.print("\n");
System.out.println("table name is"+ metadata.getTableName(1));
}
System.out.println("Query was successful");
con.close();
}
catch(ClassNotFoundException cnfe)
{
System.out.println("No such class: " + driver);
}
catch(SQLException se)
{
System.out.println("SQLException: " + se);
}
catch(Exception e)
{
System.out.println("Unhandled Exception: " + e);
}
}
/*
addSpace method produce equally aligned strings.
*/
private static String addSpace(String val, int spaces) {
while(val.length() < spaces)
val = val + " ";
return(val);
}
/*
fillSpace method procure equally separated lines
*/
private static String fillSpace(String val, int spaces) {
while(val.length() < spaces)
val = val + val;
return(val);
}
}
Ex2:UpdateDB.java
This example will try to update the emp table and then request the user to confirm whether to commit or not.
import java.sql.*;
import javax.swing.*;
public class UpdateDB {
public static void main(String[] args)
{
String driver="oracle.jdbc.OracleDriver";
System.out.println("Welcome to Database Updating");
try {
// load the driver
Class.forName(driver);
String url="jdbc:oracle:thin:@sunserv1.humber.org:1521:orcl";
//create connection
Connection con = DriverManager.getConnection(url,"user","password");
// disable auto Commit
con.setAutoCommit(false);
// Create a statement
Statement statement;
statement=con.createStatement();
// Define query
String update ="update emp set lastName='Robert'here id=100";
// Execute query and save results
int count=statement.executeUpdate(update);
/*
Check if the user wants to commit the changes or rollback
*/
int answer=JOptionPane.showConfirmDialog(null,
count+" Record(s) was affected.Do you want to commit"," Commit Confirmation",
JOptionPane.YES_NO_OPTION);
if(answer==JOptionPane.YES_OPTION)
{
con.commit();
System.out.println("Update was successfull");
}
else
{
con.rollback();
System.out.println("Update was rollback");
}
// close the connection
con.close();
}
catch(ClassNotFoundException cnfe)
{
System.out.println("No such class: " + driver);
}
catch(SQLException se)
{
con.rollback();
System.out.println("SQLException: " + se);
}
catch(Exception e)
{
System.out.println("Unhandled Exception: " + e);
}
}
}
Ex3:InsertToDB.java
This application will use PreparedStatement interface to build parametric insert statement, where the user must enter the id, last name, and the first name for 3 employees from the console. The application then set each set of the parameter values, and execute the insert statement.
import java.sql.*;
import java.io.*;
public class InsertToDB {
public static void main(String[] args) {
String driver="oracle.jdbc.OracleDriver";
System.out.println("Welcome to Database Inserting");
try {
// load Oracle thin JDBC driver
Class.forName(driver);
//create connection
//String url="jdbc:oracle:oci8:@sunserv1.humber.org:1521:orcl";
String url="jdbc:oracle:thin:@sunserv1.humber.org:1521:orcl";
Connection con = DriverManager.getConnection(url,"abdullah","ali123");
// Define a parametric insert statement
String insert ="insert into emp values (?,?,?)";
// Create a PreparedStatement
PreparedStatement ps=con.prepareStatement(insert);
// Get the user input , set the parameters, then execute the insert statement
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
String id,last,first;
for(int i=0;i<3;i++)
{
System.out.println("Enter customer id");
id=br.readLine();
System.out.println("Enter customer last name");
last=br.readLine();
System.out.println("Enter customer first name");
first=br.readLine();
ps.setString(1,id);
ps.setString(2,first);
ps.setString(3,last);
ps.executeUpdate();
}
System.out.println("Insert was successful");
con.close();
}
catch(ClassNotFoundException cnfe)
{
System.out.println("No such class: " + driver);
}
catch(SQLException se)
{
System.out.println("SQLException: " + se);
}
catch(IOException ioe)
{
System.out.println("Exception:"+ioe.toString());
}
catch(Exception e)
{
System.out.println("Unhandled Exception:"+e.toString());
}
}
}
Ex4:DeleteFromDB.java
This application enables the user to delete from an Oracle database table. When the user enter table name, the field name, and the field value from the Console, the application will delete all the matching rows from that table based on the selected field value.
import java.io.*;
import java.sql.*;
public class DeleteFromDB {
public static void main(String[] args)
{
String driver = "oracle.jdbc.OracleDriver";
try {
// get input from the user
BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
String table,field,value;
System.out.println("Welcome to Database Deleting");
System.out.println("Please enter table name");
table=in.readLine();
System.out.println("Please enter filed name");
field=in.readLine();
System.out.println(
"Please enter filed value.Non Numeric field should be enclosed within single quote ");
value=in.readLine();
// load Oracle thin JDBC driver
Class.forName(driver);
//create connection
String url="jdbc:oracle:thin:@sunserv1.humber.org:1521:orcl";
Connection con = DriverManager.getConnection(url,"user","password");
// Create a statement
Statement statement=con.createStatement();
// Define a delete statement
String delete ="delete from "+table+" where "+field+" = "+value;
// Execute query and save results
int count=statement.executeUpdate(delete);
System.out.println(count+" Record(s) was deleted");
// close the connection
con.close();
}
catch(ClassNotFoundException cnfe)
{
System.out.println("No such class: " + driver);
}
catch(SQLException se)
{
System.out.println("SQLException: " + se);
}
catch(IOException ioe)
{
System.out.println(ioe.toString());
}
}
}
Ex5:DeleteFromDB.java
This application query Oracle database and display the names of all the tables owned by a certain user. This is accomplished by using the method getTables of the Java class DatabaseMetaData:
import java.sql.*;
import javax.swing.*;
public class QueryTableNames {
public static void main(String[] args) {
String driver="oracle.jdbc.OracleDriver";
try
{
// load Oracle thin JDBC driver
Class.forName(driver);
//create connection
String url="jdbc:oracle:thin:@sunserv1.humber.org:1521:orcl";
String user=JOptionPane.showInputDialog(null,"Enter an Oracle user ID");
String password=JOptionPane.showInputDialog(null,"Enter an Oracle user password");
Connection con = DriverManager.getConnection(url,user,password);
// Create a statement
Statement statement = con.createStatement();
DatabaseMetaData dmd = con.getMetaData();
String[] tableTypes = { "TABLE" };
//rs = ResultSet object
// user id must be in upper case
ResultSet rs = dmd.getTables(null, user.toUpperCase(), null, tableTypes);
//mdta = ResultSetMetaData object
while (rs.next())
{
String table_name = rs.getString("TABLE_NAME");
System.out.println(table_name);
}
System.out.println("Query was successful");
con.close();
}
catch(ClassNotFoundException cnfe)
{
System.out.println("No such class: " + driver);
}
catch(SQLException se)
{
System.out.println("SQLException: " + se);
}
catch(Exception e)
{
System.out.println("Unhandled Exception: " + e);
}
}
}
Ex5:DBGUI.java
This is a Java GUI application that loads an Oracle database table into a scrollable JTable. The user has to provide a valid userID/password and select an existing table on the database server.
import java.sql.*;
import javax.swing.*;
import java.awt.event.*;
import java.util.*;
import java.awt.*;
import javax.swing.table.*;
public class DBGUI extends JFrame{
private Connection connection;
private JTable myTable;
private DefaultTableModel dtm;
public DBGUI()
{
Container c=getContentPane();
String driver="oracle.jdbc.OracleDriver";
try
{
// load Oracle thin JDBC driver
Class.forName(driver);
//create connection
String url="jdbc:oracle:thin:@sunserv1.humber.org:1521:orcl";
String user=JOptionPane.showInputDialog(this,"Enter an Oracle user ID");
String password=JOptionPane.showInputDialog(this,"Enter an Oracle user password");
String table =JOptionPane.showInputDialog(this,"Enter a table name");
Connection con = DriverManager.getConnection(url,user,password);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection =DriverManager.getConnection(url,user,password);
Statement statement;
ResultSet result;
String query="SELECT * FROM "+table;
statement =connection.createStatement();
result=statement.executeQuery(query);
ResultSetMetaData metadata;
metadata=result.getMetaData();
/*
construct a default table model which is a table of zero rows and zero columns
*/
dtm=new DefaultTableModel();
/*
add columns with a specific headers to the table
*/
int col=metadata.getColumnCount();
for(int j=1;j<=col;j++)
dtm.addColumn(metadata.getColumnName(j));
Vector v=null;
while(result.next())
{
v=new Vector();
for(int j=1;j<=col;j++)
{
switch (metadata.getColumnType(j))
{
case Types.VARCHAR:
case Types.CHAR:
v.addElement(result.getString(j));
break;
case Types.NUMERIC :
v.addElement(new Long (result.getLong(j)));
break;
case Types.DOUBLE:
v.addElement(new Double (result.getDouble(j)));
break;
case Types.DATE:
v.addElement((result.getDate(j)));
break;
default:
v.addElement(result.getString(j));
}
}
dtm.addRow(v);
}
myTable=new JTable(dtm);
c.add(new JScrollPane(myTable));
setSize(400,300);
setVisible(true);
}
catch(ClassNotFoundException cnfe)
{
System.out.println("No such class: " + driver);
}
catch(SQLException se)
{
System.out.println("SQLException: " + se);
}
catch(Exception e)
{
System.out.println("Unhandled Exception: " + e);
}
}
public static void main(String args[])
{
DBGUI app=new DBGUI();
app.addWindowListener(new WindowAdapter(){
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
}
);
}
}
1