A Deep Dive into the realm of JDBC and J2EE Overview
Technological solutions due to increased demand must be fulfilled quickly either for customers or enterprises
There is a tremendous need for enterprise application (especially transactional applications) for enterprises.
These distributed enterprise applications must be designed , implemented and deployed in less time , with few resources and greater speed
J2EE provides a set of tools that helps us build a scalable enterprise applications
J2EE provides component based technology for design, development , assembly and deployment of low-cost and fast-track enterprise application
J2EE offers:
- Multi-tiered distributed application model
- Reusable Components
- Flexible Transaction Control
- Web services support through integrated data interchange on Extensible markup language(XML)-based open standards and protocols.
- Unified Security Model
J2EE is a not single technology, it consist of many technology which are mentioned as follows:
- Enterprise Java Beans
- Java Servlets
- Java Server pages(JSP)
- Java Message Service(JMS)
- Java Naming and Directory Interface
- Java-XML
- J2EE Connector Architecture
- Java Mail
- Java Database Connectivity(JDBC)
- Remote Method Invocation(RMI)
- CORBA
- RMI-IIOP
As, each topic has its own vastness, I'll be only covering JDBC in this particular article.
However, I'll be linking all other topics as well, the minute , I finished writing on it.
JDBC: The Complete Reference
Java Database Connectivity allows us to access databases through Java Programs.
Note:- Most people think, that JDBC stands for Java Database Connectivity. However, according to javasoft (developer of JDBC) , JDBC is not an acronym.
It provides java interfaces and classes to fire SQL/PL SQL statements, process results (if any) and perform other operations common to databases (especially CRUD operations).
PL/SQL is a combination of SQL along with the procedural features of programming languages
The classes and interfaces for database connectivity comes under a separate package, java.sql.
JDBC Drivers
A java application can almost access every type of database such as relational, object, object-relational.
- The access to a specific database is accomplished using a set of java interfaces, each of which is implemented by different vendors differently.
- A Java class that provides interfaces to a specific database is called JDBC driver.
- JDBC hides the underlying database architecture.
- JDBC drivers provided by database vendors convert database access requests into database-specific APIs.
JDBC Drivers is classified into four categories:
- JDBC-ODBC Bridge (Type-1)
- Native-API, Partly Java (Type-2)
- Middleware, Pure Java (Type -3)
- Pure Java Driver (Type-4)
JDBC-ODBC Bridge (Type-1):
This driver needs an intermediate ODBC (OpenDataBase Connectivity) driver, in order to talk to database, it cannot talk to database directly. it translates JDBC function calls to ODBC method calls. ODBC makes use of native libraries of Operating System and hence it is platform-dependent. For this mechanism to function correctly, the ODBC driver must be installed in the client machine and configured correctly, which is generally a long and a tedious process. For this reason, it is used only for experimental purposes or when no other JDBC driver is available. Sun provides a Type-1 JDBC driver with JDK1.1 or later.
Note:-Type-1 driver has been removed from java 8 due to its low speed and several other issues.
Native-API, Partly Java (Type-2):
This one is similar to Type-1. However, it does not forward JDBC calls to ODBC driver, instead it translates JDBC calls to database-specific native API calls. This driver is not a pure java driver as it interfaces with non-java APIs that communicate with the database.
This approach is little-bit faster than the previous one, as it interfaces directly with database through the native APIs. However, it has limitations similar to the previous one. This means client must have vendor-specific native APIs installed and configured in it.
Middleware , Pure Java (Type-3):
In this case, the JDBC driver forwards the JDBC calls to some middleware server using a database-independent network protocol. The middleware server acts as a gateway to multiple ( possibly different) database servers and can use different database-specific protocols to connect to different database servers. The intermediate server sends each client request to specific database. The results are then sent back to the intermediate server , which in turn sends the result back to the client. This approach hides the connection implementation to a database servers and makes it possible to change the database servers without affecting the client.
Pure Java Driver (Type-4):
These types of drivers makes direct connection to database by making a socket connections. It has several distinct advantages over other mechanism in terms of performance and development time. Since it talks with the database directly, no other intermediary driver is needed.
JDBC Architecture:
The JDBC architecture is classified as : two-tier and three-tier. Type-1 and Type-3 uses three-tier architecture and Type-2 and Type-4 uses two-tier architecture.
JDBC classes and Interfaces
BASIC STEPS TO WORK WITH JDBC:
- Loading the Driver
- Making a connection
- Executing the SQL statement
Before you proceed further, make sure you have MySQL database server configured and running, otherwise, you can follow this article
Loading the driver:
First, you need to install intellij idea and create a new project like this:
Create a new Project
Name your Project:
Now, you need to download the appropriate driver depending upon which database you want to connect to. as, I'll be using MySql jdbc driver, because I am using Mysql database server which is already running in the background.
Go to this site and select platform independent if you are on mac or windows and then download the zip file and unzip it and place that folder wherever you can find it easily.
In order to use that downloaded driver we need to place it in our Project, which we have created using intellij like this:
Go to File, click on Project Structure
Then. click on Libraries and then on '+' icon and choose java.
it will ask you to provide a path, provide the path of sql-connector jar file that you have downloaded recently.
In my case, I have placed my jar file under tomcat/lib/ directory
Now, we are ready to use our Database within our project.
Create a package named main and under main package create a java class JDBCExecutor. you can see mysql-connector jar file is exported in your External Libraries. Have a look by clicking on it.
So far we have downloaded JDBC driver and put it inside our project directory.
For it to start functioning, an instance of a driver has to be created and registered with the DriverManager Class so it can translate JDBC calls to the appropriate database call. The JDBC class DriverManager is an important class in java.sql package. It interfaces between the java application and the JDBC Driver. It has many other useful methods which we’ll be discussing along the article.
//Register your driver like this with DriverManager class
Class.forName("com.mysql.jdbc.Driver");The method forName creates an instance of the class whose name is specified as an argument using its default constructor. The .jar file for MySQL contains two driver class files with the name Driver.class, one in the com.mysql.jdbc package and other in the org.gjt.mm.mysql package
//you can use this as well
Class.forName("org.gjt.mm.mysql.Driver");Now, the driver is ready to translate the JDBC calls.
Making a Connection
DriverManager class provides these methods to establish a connection to Database.
public static Connection getConnection(String url, String login, String passwd)
public static Connection getConnection(String url)
public static Connection getConnection(String url, Properties)The Connection object encapsulates (wrap) the session/connection to a specific database. It is used to fire SQL statements as well as to commit and rollback database transactions.It also allows us to collect different useful information about the database dynamically and to write custom applications. Many connection can be established to a single database server or different database servers.
The primary argument that getConnection method takes is a database URL which uniquely identifies a database.
jdbc:mysql://[host]:[port]/[database]The URL always starts with jdbc:. The format of the rest of the JDBC URL varies widely for different databases.
Here, host is the the name(or Ip address) of the machine running the database at the port number port and database is a name of a database.
String url="jdbc:mysql://localhost:3306/saraansh?user=root&password=9136489422";The second overloaded version of the getConnection() method takes only a string argument. This argument must contain URL information, together with other parameters such as user name and password. which you can see in the above code snippet defined as url variable.
//we'll be using this version of getConnection() method//you can see that it returns a Connection class type object which can be only stored in Connection class type reference variable.public static Connection getConnection(String url)
A database connection can be established like this:
Connection connection= DriverManager.getConnection(url);First, we are creating a Connection class reference variable and assigning it a Connection class object which will be returned by the DriverManager.getConnection(url) method.
Execute SQL Statement
The Connection interface defines the following methods to obtain statement objects.
Statement createStatement()Statement createStatement(int resultSetType,
int resultSetConcurrency)Statement createStatement(int resultSetType,
int resultSetConcurrency,
int resultSetHoldability)PreparedStatement prepareStatement(java.lang.String)PreparedStatement prepareStatement(String sql,
int resultSetType,
int resultSetConcurrency)PreparedStatement prepareStatement(String sql,
int resultSetType,
int resultSetConcurrency
int resultSetHoldability)CallableStatement prepareCall(java.lang.String)CallableStatement prepareCall(String sql,
int resultSetType,
int resultSetConcurrency)CallableStatement prepareCall(String sql,
int resultSetType,
int resultSetConcurrency
int resultSetHoldability)
The Statement, PreparedStatement and CallableStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from the database.
Starting from Simple Statement
The Statement interface is used to execute static SQL statements. A Statement object is instantiated using the createStatement() method on the Connection object ( which we have already created in the above section)
Statement statement=connection.createStatement();This Statement object defines the following methods to fire different types of SQL commands as follows:
executeUpdate(), executeQuery() and execute()
//first writing some statements to create a table and inserting some //data on it, if you are familiar with SQL, you must know thisString insert = "INSERT INTO accounts VALUES(1,'Uttam K. Roy', 10000)";
String create="CREATE TABLE accounts ( "+ " accNum integer primary key, "+ " holderName varchar(20), "+ " balance integer "+ ")";
//then we are calling the executeUpdate() method on it by passing //create string which contains our table creating SQL statementstatement.executeUpdate(create);//again call it by passing insert statement stringstatement.executeUpdate(insert);//create one more insert string by reassigning a new value to itinsert = "INSERT INTO accounts VALUES(2,'Saraansh Mishra', 20000)";
statement.executeUpdate(insert);
The complete code for JDBCExecutor class:
package main;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExecutor {
public static void main(String... args) throws ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String insert = "INSERT INTO accounts VALUES(1,'Uttam K. Roy', 10000)";
String create="CREATE TABLE accounts ( "+ " accNum integer primary key, "+ " holderName varchar(20), "+ " balance integer "+ ")";
String url="jdbc:mysql://localhost:3306/saraansh?user=root&password=9136489422";
try {
Connection connection= DriverManager.getConnection(url);
Statement statement=connection.createStatement();
statement.executeUpdate(create);
statement.executeUpdate(insert);
insert = "INSERT INTO accounts VALUES(2,'Saraansh Mishra', 20000)";
statement.executeUpdate(insert);
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Now, Right-click on JDBCExecutor class and select Run ‘JDBC…’
Now again open your terminal and retrieve results from the database.
if you still have any doubt accessing your database, refer to this article shown above.
Types of SQL Commands:
DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
DML is short name of Data Manipulation Language which deals with data manipulation.
DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system
TCL is short name of Transaction Control Language which deals with a transaction within a database
executeUpdate() method is used to execute DDL,DML,DCL statements. In general if a SQL command changes the database, executeUpdate() is used. the return value of this method is the number of rows affected.
executeQuery() it is used for DQL statements, that only reads data from database.So the return value of this method is a set of rows that is represented as a ResultSet object. This result set object looks very much similar to a table and hence has a number of rows. A particular row is selected by setting a cursor associated with this result set.
A cursor is something like a pointer which points to the rows. Once the cursor is set to a particular row , individuals columns are retrieved by using the methods provided by ResultSet interface.
To retrieve data from a column, methods of the form getX() are used, where X is the data type of the column.
next() is used to move the cursor only in the forward direction. it returns true/false based on whether there are more rows in the resultSet.
String read="select * from accounts";
ResultSet resultSet=statement.executeQuery(read);
while(resultSet.next()){
System.out.println("name is"+resultSet.getString("holderName"));
System.out.println("name is"+resultSet.getInt("balance"));
}
//holderName and balance is the column name in a table of type //String and Integer.Output associated with this code snippet:-
Remember, when you run that same code again, you don’t need to execute create table statement, you can comment that line because that statement will try to create a table which already exists in the database and you can also change the insert statement for new data insertion or comment those existing insertion statement.
execute(), it is used to execute any SQL commands and hence can used in a situation when you don’t know the statement type in advance because you may be getting that statement from a different program.Since it allows to execute any SQL commands, the result can be a ResultSet Object or an Integer. However, how does a user know it ? Fortunately, this method returns a boolean value which indicates its return type. if returns true then return type is ResultSet Object which can be retrived using getResultSet() method and if it returns false, the result is an update count, which can be obtained by calling getUpdateCount() method.
String update="UPDATE accounts SET holderName='Saraansh' WHERE accNum=1";
statement.executeUpdate(update);
String delete="DELETE FROM accounts WHERE accNum=1";
statement.executeUpdate(delete);
So far, we have performed all CRUD operations on our database which is Create, Read, Update and Delete.
ATOMIC TRANSACTION:
The database transaction made by executeUpdate() committed (reflect permanent changes)automatically. This may lead to data inconsistency if a series of related statements are executed.
Consider the Banking Application:
The BankManager wants to write a java program that will transfer money from source account to destination account for which he’ll subtract the transferring amount from source account balance and add it to the destination account balance so he writes the following SQL Statements:
query = "UPDATE accounts SET balance=" + srcBal + " WHERE accNum=" + src;
stmt.executeUpdate(query);query = "UPDATE accounts SET balance=" + dstBal + " WHERE accNum=" + dst;
//If anything goes wrong here, destination account will //contain wrong result.stmt.executeUpdate(query);
This problem can be solved using autoCommit() method available on Connection object.
First the autoCommit flag method of the Connection object is set to False. At the end of execution of all related statements, the transaction is committed.
If anything goes wrong during the execution of those statements, it can be caught and the transaction gets rolled back accordingly.
Same statements now can be written like this:
try{con.setAutoCommit(false);query = "UPDATE accounts SET balance=" + srcBal + " WHERE accNum=" + src;
stmt.executeUpdate(query);
query = "UPDATE accounts SET balance=" + dstBal + " WHERE accNum=" + dst;
stmt.executeUpdate(query);con.commit();return true;
}
catch(SQLException e){
try{
conn.rollback();}catch(SQLException e1){}
return false;}
This way a set of related operations can be performed atomically.
excuteBatch()
This method allows us to execute a set of related commands as a whole. Commands to be fired on the database are added to the Statement object one by one using the method addBatch(). It is always safe to clear the Statement object by calling the clearBatch() method on it, before adding a command to statement object. Once all commands are added , excuteBatch() is called to send them as unit to the database.
con.setAutoCommit(false);
stmt.clearBatch();
query = “UPDATE accounts SET balance=” + srcBal + “ WHERE accNum=” + src; stmt.addBatch(query);query = “UPDATE accounts SET balance=” + dstBal + “ WHERE accNum=” + dst; stmt.addBatch(query);
stmt.executeBatch();
con.commit();
It returns an array of update counts. To allow correct error handling , we should always set auto-commit mode to false before beginning a batch command.
Pre-Compiled Statement
When an SQL statement is fired to the database for execution using the Statement object the following steps gets executed:
- DBMS checks the syntax of the statement being submitted.
- if the syntax is correct , it executes the statement.
DBMS compiles every statement unnecessarily, even if user wants to execute the same statement with difeferent data items. This ceates significant overhead which can be avoided using PreparedStatement Object.
For Complete Demonstration of PreparedStatement Object Check this Article.
A PreparedStatement object is created using the prepareStatement() method of the Connection Object. An SQL statement with placeholders (?) is supplied to the method Connection. This SQL statements together with the placeholders is sent to the DBMS. DBMS, in turn, compiles the statement and if everything is correct , a PreparedStatement object is created. This means that PreparedStatement object contains an SQL statement whose syntax has already been checked and hence it is called pre-compiled statement.
PreparedStatement object has another important role in executing parameterized SQL statements.
//example of parameterized SQL statement which you'll see in above //article frequentlyprivate static final String INSERT=
“INSERT into customer(first_name,last_name,email,phone,address,city,state,zipcode) VALUES(?,?,?,?,?,?,?,?)”;
and that brings us to an end, thank you for reading and if you liked it, you can clap for it.
