Unit V – Interacting with Database

TEACHING HOURS – 12 TOTAL MARKS – 12

Course Outcome (CO-5) – Develop programs using database.

=========== Sample JDBC Program for MySQL Database ======================

import java.sql.*;

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

//Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a statement
Statement stmt = con.createStatement();

// Execute a query
ResultSet rs = stmt.executeQuery("SELECT * FROM student");

// Process the result set

while (rs.next()) {
System.out.print(rs.getInt("rollno") + " ");
System.out.print(rs.getString("sname") + " ");
System.out.print(rs.getString("sclass") + " ");
System.out.print(rs.getString("sbranch") + " ");
System.out.println();
}

// Close the connection

rs.close();
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 stmt.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

OUTPUT :

5. Explain Driver interface in Java-

Explanation:
 In Java, the Driver interface is part of the JDBC (Java Database Connectivity) API and is
used to establish a connection between a Java application and a database.
 The Driver interface defines the methods that a JDBC driver must implement to enable
communication with a specific database management system (DBMS). Sample example
is shown below.
// This is a simplified example. Actual JDBC drivers are more complex.
public class MyDriver implements Driver {
public Connection connect(String url, Properties info) throws SQLException {
// Implementation to connect to the database
}
// Other methods required by the Driver interface
}
 The Driver interface is located in the java.sql package.
Methods:

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
The key methods of the Driver interface include:
 public Connection connect(String url, Properties info):
o This method attempts to establish a connection to the database specified by the
URL and the provided properties.
 public boolean acceptsURL(String url):
o This method checks whether the driver can connect to the given database URL.
 public int getMajorVersion():
o Returns the driver's major version number.
 public int getMinorVersion():
o Returns the driver's minor version number.

6. Explain DriverManager class in Java-

Explanation:
 In Java, the DriverManager class is a part of the JDBC (Java Database Connectivity) API
and acts as the manager for a set of JDBC drivers.
 It provides a way for your Java application to connect to various databases by managing
the available drivers and establishing connections.
 The DriverManager class is located in the java.sql package.
 Important functions of DriverManager are:
o Driver Management:
It keeps track of the available JDBC drivers, which are responsible for interacting
with specific database systems.
o Connection Establishment:
It provides the getConnection() method, which is used to establish a connection to
a database using the appropriate driver.
o Driver Loading:
It automatically attempts to load the driver classes specified in the jdbc.drivers
system property during its initialization.
o Driver Selection:
It selects the appropriate driver based on the URL provided in the getConnection()
method.
Methods:
 public static void registerDriver(Driver driver)
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 o Registers the given driver with the DriverManager.
 public static void deregisterDriver(Driver driver)
o Removes the specified driver from the DriverManager's list of registered drivers.
 public static Connection getConnection(String url)
o Attempts to establish a connection to the given database URL.
 public static Connection getConnection(String url, Properties info)
o Attempts to establish a connection to the given database URL.
 public static Connection getConnection(String url, String user, String password)
o Attempts to establish a connection to the given database URL.
 public static int getLoginTimeout()
o Gets the maximum time in seconds that a driver can wait when attempting to log
in to a database.

7. Explain Connection interface in Java-

Explanation:
 The Connection interface represents a session between java application and database.
 All SQL statements are executed and results are returned within the context of a
Connection object. Connection interface is mainly used to create java.sql.Statement,
java.sql.PreparedStatement and java.sql.CallableStatement objects.
 You can also use it to retrieve the metadata of a database like name of the database
product, name of the JDBC driver, major and minor version of the database etc.
 By default, the Connection automatically commits changes after executing each
statement. If auto commit has been disabled, an explicit commit must be done or
database changes will not be saved.
Methods:
 public Statement createStatement() throws SQLException
o SQL statements without parameters are normally executed using Statement
objects. If the same SQL statement is executed many times, it is more efficient to
use a PreparedStatement
 public Statement createStatement(int resultSetType, int resultSetConcurrency) throws
SQLException
o Creates a Statement object that will generate ResultSet objects with the given
type and concurrency.
o resultSetType can be one of the following
 ResultSet.TYPE_FORWARD_ONLY
 ResultSet.TYPE_SCROLL_INSENSITIVE
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
  ResultSet.TYPE_SCROLL_SENSITIVE
o resultSetConcurrency can be one of the following
 ResultSet.CONCUR_READ_ONLY
 ResultSet.CONCUR_UPDATABLE

 public PreparedStatement prepareStatement(String sql) throws SQLException

o A SQL statement with or without IN parameters can be pre-compiled and stored in
a PreparedStatement object. This object can then be used to efficiently execute
this statement multiple times.
 public PreparedStatement prepareStatement(String sql, int resultSetType, int
resultSetConcurrency) throws SQLException
o Creates a PreparedStatement object that will generate ResultSet objects with the
given type and concurrency.
 public CallableStatement prepareCall(String sql) throws SQLException
o A SQL stored procedure call statement is handled by creating a CallableStatement
for it. The CallableStatement provides methods for setting up its IN and OUT
parameters, and methods for executing it.
 public CallableStatement prepareCall(String sql, int resultSetType, int
resultSetConcurrency) throws SQLException
o Creates a PreparedStatement object that will generate ResultSet objects with the
given type and concurrency.
 public void setAutoCommit(boolean autoCommit) throws SQLException
o If a connection is in auto-commit mode, then all its SQL statements will be
executed and committed as individual transactions. Otherwise, its SQL statements
are grouped into transactions that are terminated by either commit() or rollback().
By default, new connections are in auto-commit mode.
 public void commit() throws SQLException
o Commit makes all changes made since the previous commit/rollback permanent
and releases any database locks currently held by the Connection. This method
should only be used when auto commit has been disabled.
 public void rollback() throws SQLException
o Rollback drops all changes made since the previous commit/rollback and releases
any database locks currently held by the Connection. This method should only be
used when auto commit has been disabled.
 public void close() throws SQLException
o In some cases, it is desirable to immediately release a Connection's database and
JDBC resources instead of waiting for them to be automatically released; the close
method provides this immediate release.
 public DatabaseMetaData getMetaData() throws SQLException
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 o A Connection's database is able to provide information describing its tables, its
supported SQL grammar, its stored procedures, the capabilities of this connection,
etc. This information is made available through a DatabaseMetaData object.

8. Explain Statement interface in Java-

Explanation:
A Statement object is used for executing a static SQL statement and obtaining the results
produced by it.
Methods:
 public boolean execute(String sql) throws SQLException
o Execute a SQL statement that may return multiple results. Under some
(uncommon) situations a single SQL statement may return multiple result sets
and/or update counts.
o It returns true if the first result is a ResultSet object; false if it is an update count
or there are no results.
 public ResultSet executeQuery(String sql) throws SQLException
o Execute a SQL statement that returns a single ResultSet. Typically it is used to
execute static SQL SELECT statement.
 public int executeUpdate(String sql) throws SQLException
o Execute a SQL INSERT, UPDATE or DELETE statement. In addition, SQL statements
that return nothing such as SQL DDL statements can be executed.
 public void close() throws SQLException
o In many cases, it is desirable to immediately release a Statements's database and
JDBC resources instead of waiting for this to happen when it is automatically
closed; the close method provides this immediate release.When a Statement is
closed, its current ResultSet, if one exists, is also closed.

9. Explain ResultSet interface in Java-

Explanation:
 A ResultSet provides access to a table of data generated by executing a Statement. The
table rows are retrieved in sequence. Within a row its column values can be accessed in
any order.
 A ResultSet maintains a cursor pointing to its current row of data. Initially the cursor is
positioned before the first row. The 'next' method moves the cursor to the next row.

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 The getXXX methods retrieve column values for the current row. You can retrieve values
either using the index number of the column, or by using the name of the column. In
general using the column index will be more efficient. Columns are numbered from 1.
 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:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Methods:
 public boolean next() throws SQLException
o A ResultSet is initially positioned before its first row; the first call to next move the
cursor one row next from the current position. It returns true if there is a row
available in the resultset; otherwise returns false.
 public boolean previous() throws SQLException
o It is used to move the cursor to the one row previous from the current position. It
returns true if there is a row available in the resultset; otherwise returns false.
o This method is usable only if the ResultSet is either TYPE_SCROLL_SENSITIVE or
TYPE_SCROLL_INSENSITIVE.
 public boolean first() throws SQLException
o It is used to move the cursor to the first row in result set object. It returns true if
there is a row available in the resultset; otherwise returns false.
o This method is usable only if the ResultSet is either TYPE_SCROLL_SENSITIVE or
TYPE_SCROLL_INSENSITIVE.
 public boolean last() throws SQLException
o It is used to move the cursor to the last row in result set object. It returns true if
there is a row available in the resultset; otherwise returns false.
o This method is usable only if the ResultSet is either TYPE_SCROLL_SENSITIVE or
TYPE_SCROLL_INSENSITIVE.
 public boolean absolute(int row) throws SQLException
o It is used to move the cursor to the specified row number in the ResultSet object.
It returns true if there is a row available in the resultset; otherwise returns false.
o This method is usable only if the ResultSet is either TYPE_SCROLL_SENSITIVE or
TYPE_SCROLL_INSENSITIVE.
 public boolean relative(int row) throws SQLException

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 o It is used to move the cursor to the relative row number in the ResultSet object, it
may be positive or negative. It returns true if there is a row available in the
resultset; otherwise returns false..
o This method is usable only if the ResultSet is either TYPE_SCROLL_SENSITIVE or
TYPE_SCROLL_INSENSITIVE.
 public void beforeFirst() throws SQLException
o Moves the cursor to the front of this ResultSet object, just before the first row.
This method has no effect if the result set contains no rows.
 public void afterLast() throws SQLException
o Moves the cursor to the end of this ResultSet object, just after the last row. This
method has no effect if the result set contains no rows.
 public void moveToInsertRow() throws SQLException
o Moves the cursor to the insert row. The current cursor position is remembered
while the cursor is positioned on the insert row. The insert row is a special row
associated with an updatable result set.
 public void moveToCurrentRow() throws SQLException
o Moves the cursor to the remembered cursor position, usually the current row.
This method has no effect if the cursor is not on the insert row.
 public void insertRow() throws SQLException
o Inserts the contents of the insert row into this ResultSet object and into the
database. The cursor must be on the insert row when this method is called.
 public void updateRow() throws SQLException
o Updates the underlying database with the new contents of the current row of this
ResultSet object. This method cannot be called when the cursor is on the insert
row.
 public int getInt(int columnIndex)
o It is used to return the data of specified column index of the current row as int.
 public int getInt(String columnName)
o It is used to return the data of specified column name of the current row as int.
 public String getString(int columnIndex)
o It is used to return the data of specified column index of the current row as String.
 public String getString(String columnName)
o It is used to return the data of specified column name of the current row as String.
 public Type getType(int columnIndex)
o It is used to return the data of specified column index of the current row as Type.
 public Type getType(String columnName)
o It is used to return the data of specified column name of the current row as Type.
 public void updateInt(int columnIndex, int x) throws SQLException
o Updates the designated column with an int value.
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 public void updateInt(String columnName, int x) throws SQLException
o Updates the designated column with an int value.
 public void updateString(int columnIndex, String x) throws SQLException
o Updates the designated column with a String value.
 public void updateString(String columnName, String x) throws SQLException
o Updates the designated column with a String value.
 public void updateType(int columnIndex, Type x) throws SQLException
o Updates the designated column with a Type value.
 public void updateType(String columnName, Type x) throws SQLException
o Updates the designated column with a Type value.
 public ResultSetMetaData getMetaData() throws SQLException
o Retrieves the number, types and properties of this ResultSet object's columns.
================================================================
Program to execute DDL statements (Create Table, Alter Table and Drop Table ) using
MySQL and Java
================================================================
Aim : To create employee={eno,ename,salary} table in college database in MySQL
import java.sql.*;
class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

//Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a statement
Statement stmt = con.createStatement();

// Execute a query

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 boolean result = stmt.execute("CREATE TABLE employee(eno int, ename
varchar(30), salary int)");

// Check if the table is created...

if (result==false) {
System.out.println("Table Created...");
}

// Close the connection

stmt.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

OUTPUT:

Verifying result in MySQL:

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
================================================
Aim : To alter employee table in college database in MySQL to add new column
designation.
import java.sql.*;

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

//Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.cj.jdbc.Driver");

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 // Establish the connection with college database
String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a statement
Statement stmt = con.createStatement();

// Execute a query
boolean result = stmt.execute("ALTER TABLE employee ADD(designation
varchar(30))");

// Check if the table is altered...

if (result==false) {
System.out.println("Table Altered...");
}

// Close the connection

stmt.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

Verifying result in MySQL

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
=======================================================
Aim : To delete employee table from college database in MySQL.
import java.sql.*;
class Mydatabase {
public static void main(String[] args) {
try {
// create driver and register it
//Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a statement
Statement stmt = con.createStatement();

// Execute a query
boolean result = stmt.execute("DROP TABLE employee");

// Check if the table is dropped...

if (result==false) {
System.out.println("Table Deleted...");
}

// Close the connection

stmt.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

Verifying result in MySQL

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate