UNIT-4

Java Database Connectivity with MySQL

To connect Java application with the MySQL database, we need to follow 5

following steps.

In this example we are using MySql as the database. So we need to know

following informations for the mysql database:

1. Driver class: The driver class for the mysql database

is com.mysql.jdbc.Driver.
2. Connection URL: The connection URL for the mysql database
is jdbc:mysql://localhost:3306/\JAVAwhere jdbc is the API, mysql is the
database, localhost is the server name on which mysql is running, we may
also use IP address, 3306 is the port number and sonoo is the database name.
We may use any database, in such case, we need to replace the JAVA with
our database name.
3. Username: The default username for the mysql database is root.
4. Password: It is the password given by the user at the time of installing the
mysql database. In this example, we are going to use root as the password.

Let's first create a table in the mysql database, but before creating table, we need to
create database first.

1. create database sonoo;

2. use sonoo;
3. create table emp(id int(10),name varchar(40),age int(3));

Example to Connect Java Application with mysql database

In this example, sonoo is the database name, root is the username and password
both.

1. class MysqlCon{
2. public static void main(String args[]){
3. try{
4. Class.forName("com.mysql.jdbc.Driver");
5. Connection con=DriverManager.getConnection(
6. "jdbc:mysql://localhost:3306/sonoo","root","root");
7. //here sonoo is database name, root is username and password
8. Statement stmt=con.createStatement();
9. ResultSet rs=stmt.executeQuery("select * from emp");
10.while(rs.next())
11.System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
12.con.close();
13.}catch(Exception e){ System.out.println(e);}
14.}
15.}
download this example

The above example will fetch all the records of emp table.

To connect java application with the mysql database, mysqlconnector.jar file is

required to be loaded.

download the jar file mysql-connector.jar

Two ways to load the jar file:

1. Paste the mysqlconnector.jar file in jre/lib/ext folder

2. Set classpath

1) Paste the mysqlconnector.jar file in JRE/lib/ext folder:

Download the mysqlconnector.jar file. Go to jre/lib/ext folder and paste the

jar file here.

2) Set classpath:

There are two ways to set the classpath:

 o temporary
o permanent

How to set the temporary classpath

open command prompt and write:

1. C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;

How to set the permanent classpath

Go to environment variable then click on new tab. In variable name

write classpath and in variable value paste the path to the mysqlconnector.jar file
by appending mysqlconnector.jar;.; as C:\folder\mysql-connector-java-5.0.8-
bin.jar;.;

Connection interface
A Connection is a session between a Java application and a database. It helps to
establish a connection with the database.

The Connection interface is a factory of Statement, PreparedStatement, and

DatabaseMetaData, i.e., an object of Connection can be used to get the object of
Statement and DatabaseMetaData.

The Connection interface provide many methods for transaction management like
commit(), rollback(), setAutoCommit(), setTransactionIsolation(), etc.

By default, connection commits the changes after executing queries.

Commonly used methods of Connection interface:

1) public Statement createStatement(): creates a statement object that can be

used to execute SQL queries.

2) public Statement createStatement(int resultSetType,int

resultSetConcurrency): Creates a Statement object that will generate ResultSet
objects with the given type and concurrency.

Features of Java - Javatpoint

3) public void setAutoCommit(boolean status): is used to set the commit status.
By default, it is true.

4) public void commit(): saves the changes made since the previous
commit/rollback is permanent.

5) public void rollback(): Drops all changes made since the previous
commit/rollback.

6) public void close(): closes the connection and Releases a JDBC resources
immediately.

Connection Interface Fields

There are some common Connection interface constant fields that are present in
the Connect interface. These fields specify the isolation level of a transaction.

TRANSACTION_NONE: No transaction is supported, and it is indicated by this

constant.

TRANSACTION_READ_COMMITTED: It is a constant which shows that the

dirty reads are not allowed. However, phantom reads and non-repeatable reads can
occur.

TRANSACTION_READ_UNCOMMITTED: It is a constant which shows that

dirty reads, non-repeatable reads, and phantom reads can occur.

TRANSACTION_REPEATABLE_READ: It is a constant which shows that the

non-repeatable reads and dirty reads are not allowed. However, phantom reads and
can occur.

TRANSACTION_SERIALIZABLE: It is a constant which shows that the non-

repeatable reads, dirty reads as well as the phantom reads are not allowed.

DriverManager class
The DriverManager class is the component of JDBC API and also a member of
the java.sql package. The DriverManager class acts as an interface between users
and drivers.
 It keeps track of the drivers that are available and handles establishing a
connection between a database and the appropriate driver.

It contains all the appropriate methods to register and deregister the database driver
class and to create a connection between a Java application and the database.

The DriverManager class maintains a list of Driver classes that have registered
themselves by calling the method DriverManager.registerDriver().

Note that before interacting with a Database, it is a mandatory process to register

the driver; otherwise, an exception is thrown.

Methods of the DriverManager Class

Method Description

1) public static synchronized is used to register the given driver with

void registerDriver(Driver DriverManager. No action is
driver): performed by the method when the
given driver is already registered.

2) public static synchronized is used to deregister the given driver

void deregisterDriver(Driver (drop the driver from the list) with
driver): DriverManager. If the given driver has
been removed from the list, then no
action is performed by the method.

3) public static Connection is used to establish the connection with

getConnection(String url) the specified url. The SQLException is
throws SQLException: thrown when the corresponding Driver
class of the given database is not
registered with the DriverManager.

4) public static Connection is used to establish the connection with

getConnection(String the specified url, username, and
url,String userName,String password. The SQLException is
password) throws thrown when the corresponding Driver
SQLException: class of the given database is not
registered with the DriverManager.

5) public static Driver Those drivers that understand the

getDriver(String url) mentioned URL (present in the
parameter of the method) are returned
by this method provided those drivers
are mentioned in the list of registered
drivers.

6) pubic static int The duration of time a driver is

getLoginTimeout() allowed to wait in order to establish a
connection with the database is
returned by this method.

7) pubic static void The method provides the time in

setLoginTimeout(int sec) seconds. sec mentioned in the
parameter is the maximum time that a
driver is allowed to wait in order to
establish a connection with the
database. If 0 is passed in the
parameter of this method, the driver
will have to wait infinitely while trying
to establish the connection with the
database.

8) public static Connection A connection object is returned by this

getConnection(String URL, method after creating a connection to
Properties prop) throws the database present at the mentioned
SQLException URL, which is the first parameter of
this method. The second parameter,
which is "prop", fetches the
authentication details of the database
 (username and password.). Similar to
the other variation of the
getConnection() method, this method
also throws the SQLException, when
the corresponding Driver class of the
given database is not registered with
the DriverManager.

Statement interface
The Statement interface provides methods to execute queries with the database.
The statement interface is a factory of ResultSet i.e. it provides factory method to
get the object of ResultSet.

Commonly used methods of Statement interface:

The important methods of Statement interface are as follows:

1) public ResultSet executeQuery(String sql): is used to execute SELECT

query. It returns the object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query,
it may be create, drop, insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may
return multiple results.
4) public int[] executeBatch(): is used to execute batch of commands.

Example of Statement interface

Let’s see the simple example of Statement interface to insert, update and delete the
record.

1. import java.sql.*;
2. class FetchRecord{
3. public static void main(String args[])throws Exception{
4. Class.forName("oracle.jdbc.driver.OracleDriver");
5.

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521
:xe","system","oracle");
6. Statement stmt=con.createStatement();
7.
8. //stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");
9.

//int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 w

here id=33");
10.int result=stmt.executeUpdate("delete from emp765 where id=33");
11.System.out.println(result+" records affected");
12.con.close();
13.}}
ResultSet interface

The object of ResultSet maintains a cursor pointing to a row of a table. Initially,

cursor points to before the first row.

By default, ResultSet object can be moved forward only and it is not updatable.

But we can make this object to move forward and backward direction by passing
either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in
createStatement(int,int) method as well as we can make this object as updatable by:

1.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

2. ResultSet.CONCUR_UPDATABLE);

Commonly used methods of ResultSet interface

1) public boolean next(): is used to move the cursor to the one row
next from the current position.

2) public boolean is used to move the cursor to the one row

previous(): previous from the current position.

3) public boolean first(): is used to move the cursor to the first row
in result set object.

4) public boolean last(): is used to move the cursor to the last row in
result set object.

5) public boolean is used to move the cursor to the specified

absolute(int row): row number in the ResultSet object.

6) public boolean is used to move the cursor to the relative

relative(int row): row number in the ResultSet object, it may
be positive or negative.

7) public int getInt(int is used to return the data of specified

columnIndex): column index of the current row as int.

8) public int getInt(String is used to return the data of specified

columnName): column name of the current row as int.

9) public String is used to return the data of specified

getString(int column index of the current row as String.
columnIndex):

10) public String is used to return the data of specified

getString(String column name of the current row as String.
columnName):
 Example of Scrollable ResultSet

Let’s see the simple example of ResultSet interface to retrieve the data of 3rd row.

1. import java.sql.*;
2. class FetchRecord{
3. public static void main(String args[])throws Exception{
4.
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521
:xe","system","oracle");
7.

Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,Res
ultSet.CONCUR_UPDATABLE);
8. ResultSet rs=stmt.executeQuery("select * from emp765");
9.
10.//getting the record of 3rd row
11.rs.absolute(3);
12.System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
13.
14.con.close();
15.}}
PreparedStatement interface

The PreparedStatement interface is a subinterface of Statement. It is used to

execute parameterized query.

Let's see the example of parameterized query:

1. String sql="insert into emp values(?,?,?)";

As you can see, we are passing parameter (?) for the values. Its value will be set by
calling the setter methods of PreparedStatement.

Why use PreparedStatement?

Improves performance: The performance of the application will be faster if you

use PreparedStatement interface because query is compiled only once.

31.3M

698

Exception Handling in Java - Javatpoint

How to get the instance of PreparedStatement?

The prepareStatement() method of Connection interface is used to return the object

of PreparedStatement. Syntax:

1.

public PreparedStatement prepareStatement(String query)throws SQLException{

}

Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:

 Method Description

public void setInt(int sets the integer value to the given

paramIndex, int value) parameter index.

public void setString(int sets the String value to the given

paramIndex, String value) parameter index.

public void setFloat(int sets the float value to the given

paramIndex, float value) parameter index.

public void setDouble(int sets the double value to the given

paramIndex, double value) parameter index.

public int executeUpdate() executes the query. It is used for create,

drop, insert, update, delete etc.

public ResultSet executeQuery() executes the select query. It returns an

instance of ResultSet.

Example of PreparedStatement interface that inserts the record

First of all create table as given below:

1. create table emp(id number(10),name varchar2(50));

Now insert records in this table by the code given below:

1. import java.sql.*;
2. class InsertPrepared{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7.

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521
:xe","system","oracle");
8.
9. PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");
10.stmt.setInt(1,101);//1 specifies the first parameter in the query
11.stmt.setString(2,"Ratan");
12.
13.int i=stmt.executeUpdate();
14.System.out.println(i+" records inserted");
15.
16.con.close();
17.
18.}catch(Exception e){ System.out.println(e);}
19.
20.}
21.}
download this example
 Example of PreparedStatement interface that updates the record

1.

PreparedStatement stmt=con.prepareStatement("update emp set name=? where id=

?");
2. stmt.setString(1,"Sonoo");//1 specifies the first parameter in the query i.e. name
3. stmt.setInt(2,101);
4.
5. int i=stmt.executeUpdate();
6. System.out.println(i+" records updated");
download this example

Example of PreparedStatement interface that deletes the record

1. PreparedStatement stmt=con.prepareStatement("delete from emp where id=?");

2. stmt.setInt(1,101);
3.
4. int i=stmt.executeUpdate();
5. System.out.println(i+" records deleted");
 download this example

Example of PreparedStatement interface that retrieve the records of a table

1. PreparedStatement stmt=con.prepareStatement("select * from emp");

2. ResultSet rs=stmt.executeQuery();
3. while(rs.next()){
4. System.out.println(rs.getInt(1)+" "+rs.getString(2));
5. }
download this example

Example of PreparedStatement to insert records until user press n

1. import java.sql.*;
2. import java.io.*;
3. class RS{
4. public static void main(String args[])throws Exception{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521
:xe","system","oracle");
7.
8. PreparedStatement ps=con.prepareStatement("insert into emp130 values(?,?,?)");
9.
10.BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
11.
12.do{
13.System.out.println("enter id:");
14.int id=Integer.parseInt(br.readLine());
15.System.out.println("enter name:");
16.String name=br.readLine();
17.System.out.println("enter salary:");
18.float salary=Float.parseFloat(br.readLine());
19.
20.ps.setInt(1,id);
21.ps.setString(2,name);
22.ps.setFloat(3,salary);
23.int i=ps.executeUpdate();
24.System.out.println(i+" records affected");
25.
26.System.out.println("Do you want to continue: y/n");
27.String s=br.readLine();
28.if(s.startsWith("n")){
29.break;
30.}
31.}while(true);
32.
33.con.close();
34.}}
Java ResultSetMetaData Interface
 The metadata means data about data i.e. we can get further information from the
data.

If you have to get metadata of a table like total number of column, column name,
column type etc. , ResultSetMetaData interface is useful because it provides
methods to get metadata from the ResultSet object.

Commonly used methods of ResultSetMetaData interface

Method Description

public int getColumnCount()throws it returns the total number of

SQLException columns in the ResultSet
object.

public String getColumnName(int it returns the column name of

index)throws SQLException the specified column index.

public String getColumnTypeName(int it returns the column type

index)throws SQLException name for the specified index.

public String getTableName(int it returns the table name for the

index)throws SQLException specified column index.

How to get the object of ResultSetMetaData:

The getMetaData() method of ResultSet interface returns the object of

ResultSetMetaData. Syntax:

1. public ResultSetMetaData getMetaData()throws SQLException

Example of ResultSetMetaData interface :

1. import java.sql.*;
2. class Rsmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6. Connection con=DriverManager.getConnection(
7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
8.
9. PreparedStatement ps=con.prepareStatement("select * from emp");
10.ResultSet rs=ps.executeQuery();
11.ResultSetMetaData rsmd=rs.getMetaData();
12.
13.System.out.println("Total columns: "+rsmd.getColumnCount());
14.System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));
15.

System.out.println("Column Type Name of 1st column: "+rsmd.getColumnTypeN

ame(1));
16.
17.con.close();
18.}catch(Exception e){ System.out.println(e);}
19.}
20.}
Output:Total columns: 2
Column Name of 1st column: ID
Column Type Name of 1st column: NUMBER

Java DatabaseMetaData interface

DatabaseMetaData interface provides methods to get meta data of a database such

as database product name, database product version, driver name, name of total
number of tables, name of total number of views etc.

Commonly used methods of DatabaseMetaData interface

 o public String getDriverName()throws SQLException: it returns the name
of the JDBC driver.
o public String getDriverVersion()throws SQLException: it returns the
version number of the JDBC driver.
o public String getUserName()throws SQLException: it returns the
username of the database.
o public String getDatabaseProductName()throws SQLException: it
returns the product name of the database.
o public String getDatabaseProductVersion()throws SQLException: it
returns the product version of the database.
o public ResultSet getTables(String catalog, String schemaPattern, String
tableNamePattern, String[] types)throws SQLException: it returns the
description of the tables of the specified catalog. The table type can be
TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.

How to get the object of DatabaseMetaData:

The getMetaData() method of Connection interface returns the object of

DatabaseMetaData. Syntax:

1. public DatabaseMetaData getMetaData()throws SQLException

Simple Example of DatabaseMetaData interface :

1. import java.sql.*;
2. class Dbmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9. DatabaseMetaData dbmd=con.getMetaData();
10.
11.System.out.println("Driver Name: "+dbmd.getDriverName());
12.System.out.println("Driver Version: "+dbmd.getDriverVersion());
13.System.out.println("UserName: "+dbmd.getUserName());
14.

System.out.println("Database Product Name: "+dbmd.getDatabaseProductName())

;
15.

System.out.println("Database Product Version: "+dbmd.getDatabaseProductVersio

n());
16.
17.con.close();
18.}catch(Exception e){ System.out.println(e);}
19.}
20.}
Output:Driver Name: Oracle JDBC Driver
Driver Version: 10.2.0.1.0XE
Database Product Name: Oracle
Database Product Version: Oracle Database 10g Express Edition
Release 10.2.0.1.0 -Production
download this example

Example of DatabaseMetaData interface that prints total number of tables :

1. import java.sql.*;
2. class Dbmd2{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10.DatabaseMetaData dbmd=con.getMetaData();
11.String table[]={"TABLE"};
12.ResultSet rs=dbmd.getTables(null,null,null,table);
13.
14.while(rs.next()){
15.System.out.println(rs.getString(3));
16.}
17.
18.con.close();
19.
20.}catch(Exception e){ System.out.println(e);}
21.
22.}
23.}
download this example

Example of DatabaseMetaData interface that prints total number of views :

1. import java.sql.*;
2. class Dbmd3{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10.DatabaseMetaData dbmd=con.getMetaData();
11.String table[]={"VIEW"};
12.ResultSet rs=dbmd.getTables(null,null,null,table);
13.
14.while(rs.next()){
15.System.out.println(rs.getString(3));
16.}
17.
18.con.close();
19.
20.}catch(Exception e){ System.out.println(e);}
21.
22.}
23.}