Unit - 2
Java Database Connectivity
Java Database Communication, or JDBC, is a standard Java API providing database-independent connectivity between the Java programming language and a variety of databases.
Each of the actions listed below that are often connected with database usage have APIs in the JDBC library.
● Making a database connection.
● Creating SQL or MySQL statements is a common task.
● In the database, running SQL or MySQL queries.
● Viewing and editing the records that result.
JDBC is a specification that defines a complete set of interfaces for accessing an underlying database from a mobile device. Java can be used to create a variety of executables, including
● Java Applications
● Java Applets
● Java Servlets
● Java ServerPages (JSPs)
● Enterprise JavaBeans (EJBs).
All of these executables can utilize a JDBC driver to connect to a database and use the information contained there.
JDBC is similar to ODBC in that it allows Java programmes to contain database-independent code.
Sun's trademark name for JDBC is JDBC. Java Database Connectivity is not an acronym. JDBC is a programming interface. We can create java programs that communicate with databases using this API. Two packages provide support for the JDBC API in Java applications.
- Java.sql
- Javax.sql
The java.sql package contains a set of basic interfaces and classes for communicating with databases in Java. For advanced Java-database operations, the extension package is utilized.
Why Should We Use JDBC
Before JDBC, the database API to connect to and run queries with the database was the ODBC API. However, the ODBC API makes use of the ODBC driver, which is written in C. (i.e. platform dependent and unsecured). As a result, Java has its own API (JDBC API) that makes use of JDBC drivers (written in Java language).
We can use the JDBC API to interact with databases using a Java programme and do the following tasks:
● Establish a connection to the database.
● Execute database queries and update commands.
● Get the database result that you were given.
For connecting with your database server, JDBC drivers implement the stated interfaces in the JDBC API.
JDBC drivers, for example, allow you to open database connections and interact with them by delivering SQL or database commands and then getting the results in Java.
The Java.sql package that comes with JDK has a number of classes that have their behavior defined but are implemented by third-party drivers. The java.sql.Driver interface is implemented by third-party database drivers.
JDBC Driver is a piece of software that allows a Java programme to interact with a database. JDBC drivers are divided into four categories:
● JDBC-ODBC bridge driver
● Native-API driver (partially java driver)
● Network Protocol driver (fully java driver)
● Thin driver (fully java driver)
Type 1 − JDBC-ODBC Bridge Driver
A JDBC bridge is used in a Type 1 driver to access ODBC drivers installed on each client system. ODBC requires that you set up a Data Source Name (DSN) on your system that reflects the target database.
This was a helpful driver when Java first came out because most databases only supported ODBC access, but it is currently only suggested for experimental use or when no other option is available.
Fig 1: JDBC-ODBC bridge driver
A good example of this type of driver is the JDBC-ODBC Bridge included with JDK 1.2.
Advantages:
● It is simple to use.
● It's simple to connect to any database.
Disadvantages:
● Because JDBC method calls are translated to ODBC function calls, performance suffers.
● On the client PC, the ODBC driver must be installed.
Type 2 − JDBC-Native API
JDBC API calls are transformed into native C/C++ API calls that are specific to the database in a Type 2 driver. Database suppliers often provide these drivers, which are used in the same way as the JDBC-ODBC Bridge. Each client machine must have the vendor-specific driver loaded.
If we update the database, we must also modify the native API, as it is database-specific. Although Type 2 drivers reduce ODBC's overhead, they may provide a speed boost.
Fig 2: Native API driver
Advantage:
● The performance of the JDBC-ODBC bridge driver has been improved.
Disadvantage:
● Each client PC must have the Native driver installed.
● On the client machine, the Vendor client library must be installed.
Fig 3: Example of type 2 driver
Type 3 − JDBC-Network Protocol driver
The Network Protocol driver makes use of middleware (application server) to convert JDBC calls into the vendor-specific database protocol, either directly or indirectly. It's written entirely in Java.
Fig 4: Network protocol driver
Advantage:
● Because of the application server, which can do numerous duties such as auditing, load balancing, and logging, there is no need for a client-side library.
Disadvantages:
● On the client system, network support is necessary.
● In the middle tier, database-specific coding is required.
● Network Protocol driver maintenance is costly since it necessitates database-specific coding in the middle tier.
Type 4 − JDBC- Thin driver
JDBC calls are converted directly into the vendor-specific database protocol via the thin driver. That is why it is referred to as a "thin driver." It is entirely written in the Java programming language.
Fig 5: Thin driver
Advantage:
● Better than all other drivers in terms of performance.
● There is no software required on either the client or server side.
Disadvantage:
● The Database is reliant on the drivers.
The following are the fundamental steps in the process of connecting to a database and running a query:
● Import JDBC packages.
● Load and register the JDBC driver.
● Open a connection to the database.
● Create a statement object to perform a query.
● Execute the statement object and return a query resultset.
● Process the resultset.
● Close the resultset and statement objects.
● Close the connection.
Import JDBC Packages
This is used to make the JDBC API classes available to the application software right away. Regardless of the JDBC driver used, the following import statement should be included in the programme:
Import java.sql.*;
Oracle-supplied JDBC packages may also need to be imported, depending on the functionality being used. For example, when using Oracle additions to JDBC, such as using sophisticated data types like BLOB, the following packages may need to be loaded.
Import oracle.jdbc.driver.*;
Import oracle.sql.*;
Load and Register the JDBC Driver
This is required for the JDBC programme to communicate with the Oracle database. This is accomplished by calling the DriverManager class's static registerDriver() function. This is accomplished with the following line of code:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
JDBC Driver Registration
The JDBC driver is only registered once in every database that has to be accessed for the entire Java application. Even if there are many database connections to the same data server, this is still true.
Alternatively, the forName() method of the java.lang.Class class can be used to load and register the JDBC driver:
Class.forName("oracle.jdbc.driver.OracleDriver");
The forName() method, on the other hand, is only valid for JDK-compliant Java Virtual Machines and generates an instance of the Oracle driver implicitly, whereas the registerDriver() method does so manually.
Connecting to a Database
A database connection must be made when the relevant packages have been imported and the Oracle JDBC driver has been loaded and registered. The getConnection() function of the DriverManager class is used to do this. An object instance of the java.sql.Connection class is created when this method is called. A connect string, a username, and a password are all required input parameters for getConnection(). The database instance to connect to and the JDBC driver to use should both be specified in the connect string.
The getConnection() function is an overloaded method that accepts a number of parameters.
● There are three parameters: one for the URL, one for the username, and one for the password.
● The database URL has only one argument. The username and password are included in the URL in this scenario.
The getConnection() method is demonstrated in the following lines of code:
Connection conn = DriverManager.getConnection(URL, username, passwd);
Connection conn = DriverManager.getConnection(URL);
Where the data types URL, username, and passwd are String.
We'll go over how to use the Oracle JDBC OCI and thin _drivers to open a connection.
The database can be defined using the TNSNAMES item in the tnsnames.ora file when using the OCI driver. Use the following code to connect to a database on a specific host as user oratest and password oratest that contains a TNSNAMES entry of oracle.world:
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:
@oracle.world", "oratest", "oratest");
Both the ":" and "@" are mandatory.
Querying the Database
The process of querying the database consists of two steps: first, building a statement object to execute the query, and then performing the query and producing a resultset.
Creating a Statement Object
This is used to create objects that execute the query against the database that is connected. The above-mentioned conn Connection object's createStatement() method does this. When this method is called, an object instance of the Statement class is created. This is demonstrated by the following line of code:
Statement sql_stmt = conn.createStatement();
Executing the Query and Returning a ResultSet
After you've created a Statement object, the following step is to run the query. The executeQuery() function of the Statement object is used to accomplish this. This method accepts a SQL SELECT query as an argument and returns a JDBC ResultSet object. Using the sql stmt object established earlier, the following line of code demonstrates this:
ResultSet rset = sql_stmt.executeQuery
("SELECT empno, ename, sal, deptno FROM emp ORDER BY ename");
Alternatively, the SQL statement can be encapsulated in a string and sent to the executeQuery() function. This is depicted below:
String sql = "SELECT empno, ename, sal, deptno FROM emp ORDER BY ename";
ResultSet rset = sql_stmt.executeQuery(sql);
Statement and ResultSet Objects
For SELECT and other DML statements, the Statement and ResultSet objects open a corresponding cursor in the database.
The above line performs the SELECT statement enclosed in double quotations and saves the rows in a ResultSet object named rset.
Processing the Results of a Database Query That Returns Multiple Rows
Following the execution of the query, there are two phases to be completed:
● Processing the output resultset to fetch the rows.
● Retrieving the column values of the current row.
The next() method of the ResultSet object is used for the first step. Each call to next() advances the control to the next accessible row in a loop, fetching the rows one at a time. When rows are still available for fetching, the next() method returns true, and when all rows have been fetched, it returns false.
The getXXX() methods of the JDBC rset object are used in the second phase. With XXX substituted with a Java datatype, getXXX() corresponds to getInt(), getString(), and so on.
The following code exemplifies the preceding steps:
String str;
While (rset.next())
{
Str = rset.getInt(1)+ " "+ rset.getString(2)+ "
"+rset.getFloat(3)+ " "rset.getInt(4)+ "\n";
}
Byte buf[] = str.getBytes();
OutputStream fp = new FileOutputStream("query1.lst");
Fp.write(buf);
Fp.close();
The numbers 1, 2, 3, and 4 are in rset.The positions of the columns in the SELECT statement, that is, the first column empno, second column ename, third column sal, and fourth column deptno, are represented by getInt(), rset.getString(), getFloat(), and getInt(), respectively.
Specifying get() Parameters
The getXXX() methods' parameters can be supplied by the position of the relevant columns, such as 1, 2, and so on, or by explicitly specifying the column names surrounded in double quotes, such as getString("ename") and so on, or by a mix of both.
Closing the ResultSet and Statement
The ResultSet and Statement objects must be explicitly closed after they have been utilized. Calls to the close() method of the ResultSet and Statement classes accomplish this. This is demonstrated by the following code:
Rset.close();
Sql_stmt.close();
If not specifically closed, there are two drawbacks:
● Memory leaks are possible.
● There is a limit to the number of open cursors that can be used.
The associated cursor in the database is freed when the ResultSet and Statement objects are closed.
Closing the Connection
After importing the packages and loading the JDBC drivers, the final step is to shut the database connection that was opened at the start. A call to the Connection class's close() function accomplishes this.
This is accomplished by the following line of code:
Conn.close();
Explicitly Close your Connection
The connection is not closed when the ResultSet and Statement objects are closed. The connection should be closed by calling the Connection class's close() function explicitly.
DriverManager class
The DriverManager class is a member of the java.sql package and is part of the JDBC API. The DriverManager class serves as a user-to-driver interface. It maintains track of the available drivers and manages the process of connecting a database to the proper driver. It includes all of the necessary methods for registering and deregistering the database driver class, as well as connecting a Java application to a database. The DriverManager class keeps track of which Driver classes have registered themselves by using the DriverManager.registerDriver function (). It is important to note that registering the driver is required before dealing with a database; otherwise, an exception will be issued.
Methods
● public static synchronized void registerDriver(Driver driver): It's used to tell DriverManager about a specific driver. When the specified driver is already registered, the procedure does nothing.
● public static synchronized void deregisterDriver(Driver driver): This method is used to deregister (or remove) a driver from the DriverManager list. The method takes no action if the specified driver has been removed from the list.
● public static Connection getConnection(String url) throws SQLException: It is used to make a connection to the supplied url. When the corresponding Driver class of the provided database is not registered with the DriverManager, a SQLException is thrown.
● public static Connection getConnection(String url,String userName,String password) throws SQLException: The method is used to connect to a server using the supplied url, login, and password. When the corresponding Driver class of the provided database is not registered with the DriverManager, a SQLException is thrown.
● public static Driver getDriver(String url): This function returns those drivers that comprehend the given URL (included in the method's parameter) if those drivers are listed in the list of registered drivers.
● public static int getLoginTimeout(): This method returns the amount of time a driver is allowed to wait before attempting to connect to the database.
● public static void setLoginTimeout(int sec): The time is given in seconds by the procedure. The maximum time a driver is allowed to wait to establish a connection with the database is specified by the parameter sec. If the parameter of this function is set to 0, the driver will have to wait indefinitely while attempting to connect to the database.
● public static Connection getConnection(String URL, Properties prop) throws SQLException: This function returns a connection object once it establishes a connection to the database at the specified URL, which is the method's first parameter. The second parameter, "prop," retrieves the database's authentication information (username and password.). When the relevant Driver class of the provided database is not registered with the DriverManager, this method, like the other variations of the getConnection() function, throws the SQLException.
Connection interface
A session between a Java application and a database is referred to as a Connection. It aids in the establishment of a database connection.
The Connection interface is a factory for Statement, PreparedStatement, and DatabaseMetaData objects, which means that an object of Connection can be used to get objects of Statement and DatabaseMetaData. Commit(), rollback(), setAutoCommit(), setTransactionIsolation(), and other transaction management methods are available through the Connection interface.
Methods
● public Statement createStatement(): produces a statement object for executing SQL queries.
● public Statement createStatement(int resultSetType,int resultSetConcurrency): Generates ResultSet objects of the specified type and concurrency using a Statement object.
● public void setAutoCommit(boolean status): It's used to change the status of a commit. It is true by default.
● public void commit(): The modifications made since the last commit/rollback are saved in a permanent state.
● public void rollback(): All changes made since the last commit/rollback are discarded.
● public void close(): terminates the connection and quickly releases JDBC resources.
Connection Interface Fields
The Connect interface contains a number of common Connection interface constant fields. These fields define a transaction's isolation level.
● TRANSACTION NONE: This constant indicates that no transaction is supported.
● TRANSACTION READ COMMITTED: It's a constant that indicates that dirty reads aren't permitted. However, non-repeatable readings and ghost reads are possible.
● TRANSACTION READ UNCOMMITTED: It's a constant that indicates the possibility of dirty readings, non-repeatable reads, and ghost reads.
● TRANSACTION REPEATABLE READ: This is a constant that indicates that unclean reads and non-repeatable reads are not permitted. Phantom reads, on the other hand, are possible.
● TRANSACTION SERIALIZABLE: This is a constant that indicates that non-repeatable, unclean, and phantom reads are not permitted.
Statement interface
The Statement interface provides methods for running database queries. The statement interface is a ResultSet factory, which means it provides factory methods for getting a ResultSet object.
Methods
The following are the most significant Statement interface methods:
● public ResultSet executeQuery(String sql): It is used to execute SELECT query. It returns the object of ResultSet.
● public int executeUpdate(String sql): is used to run a query, such as create, drop, insert, update, delete, and so on.
● public boolean execute(String sql): is used to execute queries that may return multiple results.
● public int[] executeBatch(): is used to execute batch of commands.
Example
Import java.sql.*;
Class FetchRecord{
Public static void main(String args[])throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
Statement stmt=con.createStatement();
//stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");
//int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 where id=33");
Int result=stmt.executeUpdate("delete from emp765 where id=33");
System.out.println(result+" records affected");
Con.close();
}
}
Preparedstatement interface
Statement has a subinterface called PreparedStatement. It's used to run queries with parameters.
Consider the following example of a parameterized query:
String sql="insert into emp values(?,?,?)";
As you can see, the values are passed as a parameter (?). Its value will be set by using PreparedStatement's setter methods.
Why use PreparedStatement?
Because the query is only compiled once, the performance of the application will be improved if you use the PreparedStatement interface.
How to get the instance of PreparedStatement?
The PreparedStatement object is returned using the Connection interface's prepareStatement() function. Syntax:
Public PreparedStatement prepareStatement(String query)throws SQLException{}
Methods
The following are the most essential methods of the PreparedStatement interface:
● public void setInt(int paramIndex, int value): sets the parameter index to an integer value.
● public void setString(int paramIndex, String value): sets the parameter index to the String value.
● public void setFloat(int paramIndex, float value): assigns the float value to the index of the parameter.
● public void setDouble(int paramIndex, double value): sets the supplied parameter index to a double value.
● public int executeUpdate(): carries out the query It's utilised to do things like create, drop, insert, update, and remove.
● public ResultSet executeQuery(): carries out the select query. It returns a ResultSet instance.
Example (insert the record)
To begin, make the table as follows:
Create table emp(id number(10),name varchar2(50));
Now use the following code to add records to this table:
Import java.sql.*;
Class InsertPrepared{
Public static void main(String args[]){
Try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");
Stmt.setInt(1,101);//1 specifies the first parameter in the query
Stmt.setString(2,"Ratan");
Int i=stmt.executeUpdate();
System.out.println(i+" records inserted");
Con.close();
}catch(Exception e){ System.out.println(e);}
}
}
ResultSet interface
A cursor pointing to a table row is maintained by the ResultSet object. The cursor is initially positioned before the first row.
However, we can make this object go forward and backward by supplying TYPE SCROLL INSENSITIVE or TYPE SCROLL SENSITIVE to the createStatement(int,int) method, and we can also make it updatable by:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Methods
● public boolean next(): is used to move the cursor to the next row from where it is currently.
● public boolean previous(): is used to move the cursor one row back from where it is currently.
● public boolean first(): The cursor is moved to the first row in the result set object.
● public boolean last(): The cursor is moved to the last row in the result set object.
● public boolean absolute(int row): The cursor is moved to the supplied row number in the ResultSet object with this method.
● public boolean relative(int row): is used to move the cursor to the ResultSet object's relative row number, which can be positive or negative.
● public int getInt(int columnIndex): is used to return the data of the current row's specified column index as an int.
● public int getInt(String columnName): is used to return the data of the current row's specified column name as an int.
● public String getString(int columnIndex): is used to return the data of the current row's specified column index as a String.
● public String getString(String columnName): is used to return the data of the current row's specified column name as a String.
Example
Import java.sql.*;
Class FetchRecord{
Public static void main(String args[])throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=stmt.executeQuery("select * from emp765");
//getting the record of 3rd row
Rs.absolute(3);
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
Con.close();
}
}
To connect a Java programme to a MySQL database, we must go through the different steps below.
We'll use MySql as the database in this example. As a result, we'll need to know the following details about the mysql database:
● Driver class: The com.mysql.jdbc.Driver class is the driver class for the MySQL database.
● URL connection: The mysql database connection URL is jdbc:mysql:/localhost:3306/sonoo, where jdbc is the API, mysql is the database, localhost is the server name (we may also use IP address), 3306 is the port number, and sonoo is the database name. We can use any database; in that case, we must change the sonoo with the name of the database.
● Username: The username for the mysql database is root by default.
● Password: It is the password entered by the user when the mysql database was installed. We're going to use root as the password in this case.
Let's start by creating a table in the mysql database; but, before we can do that, we must first build the database.
Create database sonoo;
Use sonoo;
Create table emp(id int(10),name varchar(40),age int(3));
Example of a Java Application Connected to a MySQL Database
The database name is sonoo, and the username and password are both root.
Import java.sql.*;
Class MysqlCon{
Public static void main(String args[]){
Try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/sonoo","root","root");
//here sonoo is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
While(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
Con.close();
}catch(Exception e){ System.out.println(e);}
}
}
The mysqlconnector.jar file is necessary to connect a Java programme to a MySQL database.
Two ways to load the jar file:
● Paste the mysqlconnector.jar file in jre/lib/ext folder
● Set classpath
1) Paste the mysqlconnector.jar file in JRE/lib/ext folder:
The mysqlconnector.jar file should be downloaded. Paste the jar file into the jre/lib/ext folder.
2) Set classpath:
The classpath can be specified in two ways:
● temporary
● permanent
How to set the temporary classpath
Open a command prompt and type the following:
C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;
How to set the permanent classpath
Navigate to environment variables and then to new tab. Write classpath in variable name and mysqlconnector.jar in variable value by appending mysqlconnector.jar;.; as C:foldermysql-connector-java-5.0.8-bin.jar;.; as C:foldermysql-connector-java-5.0.8-bin.jar;.; as C:foldermysql-connector-java-5.0.8-bin.jar;.; as C:foldermysql
When you need to construct your own JDBC application in the future, you can use this sample example as a template.
Import java.sql.*;
Public class FirstExample {
static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
static final String QUERY = "SELECT id, first, last, age FROM Employees";
public static void main(String[] args) {
// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(QUERY);) {
// Extract data from result set
while (rs.next()) {
// Retrieve by column name
System.out.print("ID: " + rs.getInt("id"));
System.out.print(", Age: " + rs.getInt("age"));
System.out.print(", First: " + rs.getString("first"));
System.out.println(", Last: " + rs.getString("last"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Compile the above example
C:\>javac FirstExample.java
C:\>
Output
C:\>java FirstExample
Connecting to database...
Creating statement...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>
References:
- Herbert Schildt, Java2: The Complete Reference, Tata McGraw-Hill
- Object Oriented Programming with JAVA Essentials and Applications , McGraw Hill
- Core and Advanced Java, Black Book- dreamtech
- Murach’s Java Servlets and JSP
