Java database connectivity notes for undergraduate
1. Compiled by: Ramesh Bhatta Page 1
JDBC
Introduction
A database is an integrated collection of data. There are many different strategies for
organizing data to facilitate easy access and manipulation of the data. A database
management system (DBMS) provides mechanisms for storing and organizing data in a
manner consistent with the databases format. DBMS allows for the access and storage of
data without worrying about the internal representation of database.
Todays most popular database systems are relational databases. A language called
structured query language (SQL pronounced as its individual letters, or as sequel)
is used almost universally with relational database systems to define databases, to
perform queries, and to manipulate data. Some popular relational database systems are
Microsoft SQL Server, Oracle, Sybase, DB2, MySQL, and Microsoft Access.
Java programs communicate with databases and manipulate their data using the Java
Database Connectivity (JDBC) API. A JDBC driver implements the interface to a
particular database. This separation of the API from particular drivers enables developers
to change the underlying database without modifying Java code that accesses the
database. Most popular database management systems now include JDBC drivers. There
are also many third-party JDBC drivers available.
JDBC Architecture
The JDBC API supports both two-tier and three-tier processing models for database
access. In the two tier model, a Java applet or application talks directly to the data source.
This requires a JDBC driver that can communicate with the particular data source being
accessed. A users commands are delivered to the database or other data source, and the
results of those statements are sent back to the user. The data source may be located on
another machine to which the user is connected via a network. This is referred to as a
client/server configuration, with the user's machine as the client, and the machine housing
the data source as the server. The network can be an intranet, which, for example,
connects employees within a corporation, or it can be the Internet. The figure below
shows two-tier architecture.
Advanced Java Programming
2. Compiled by: Ramesh Bhatta Page 2
In the three-tier model, commands are sent to a "middle tier" of services, which then
sends the commands to the data source. The data source processes the commands and
sends the results back to the middle tier, which then sends them to the user. Three-tier
model is very attractive because the middle tier makes it possible to maintain control over
access and the kinds of updates that can be made to corporate data. Another advantage is
that it simplifies the deployment of applications. Finally, in many cases, the three-tier
architecture can provide performance advantages. The figure below shows three-tier
architecture.
Example
JDBC is used to execute common SQL statements like CREATE, INSERT, UPDATE,
DELETE, and SELECT. Every java program that handles database must import java.sql
package. The example below executes CREATE statement that creates a table named
STUDENTINFO with three attributes NAME, CLASS, and ROLL.
import java.sql.*;
public class CreateTable {
public static void main(String args[]) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //
loading the driver
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException:
"+ e.getMessage());
}
try {
Connection con =
DriverManager.getConnection("jdbc:odbc:students",
"nawaraj","hello"); //making connection
Statement stmt = con.createStatement();
//creating statement
String sqlString = "CREATE TABLE STUDENTINFO"+
"(NAME VARCHAR(32),"+
"CLASS VARCHAR(10),"+
Advanced Java Programming
3. Compiled by: Ramesh Bhatta Page 3
"ROLL INTEGER)";
stmt.executeUpdate(sqlString); //executing
statement
stmt.close(); //closing statement
con.close(); //closing connection
} catch(SQLException ex) {
System.err.println("SQLException: " +
ex.getMessage());
}
}
}
Establishing a Connection
First, you need to establish a connection with the DBMS you want to use. Establishing a
connection involves two steps: loading the driver and making the connection.
Loading the Driver - It involves just one line of code in your program. To use the
Java DB driver, add the following line of code:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Making Connection - The second step in establishing a connection is to have the
appropriate driver connect to the DBMS. Its general form is:
Connection con = DriverManager.getConnection(url,
"myLogin", "myPassword");
For example,
Connection con =
DriverManager.getConnection("jdbc:odbc:students",
"nawaraj","hello");
Connection object represents a physical connection to the database. Now you can
use this Connection object to create Statement objects. Statement objects are
JDBC's way of getting SQL statements to the database.
Creating JDBC Statements
We create a Statement object, from the Connection object. We can now use this
Statement object to execute queries and updates on the database. For example,
Statement stmt = con.createStatement();
Executing JDBC Statements
There are two main methods in the Statement class that are important. The first is
executeQuery. This method takes one argument, the SQL statement to be executed,
and returns an object of type ResultSet, which is discussed later. This method is
used for executing queries which will return a set of data back, for instance, a SELECT
statement. The ResultSet object returned represents the data resulting from the
query. For example,
stmt.executeQuery(selectString);
Here, selectString hold the SQL statement.
The other important method is executeUpdate. This method, again, takes one
argument, which is the SQL statement to be executed. The difference between
Advanced Java Programming
4. Compiled by: Ramesh Bhatta Page 4
executeQuery and executeUpdate is that executeUpdate is for executing
statements that change data in the database. For example, use executeUpdate to
execute a CREATE, an INSERT, an UPDATE, or a DELETE statement.
Closing Statement and Connection
After completing the database operations, we should close the statement and the
connection. For example,
stmt.close();
con.close();
Setting up Tables
Creating Tables
String createString = CREATE TABLE STUDENTINFO+
(NAME VARCHAR(32),+
CLASS VARCHAR(10),+
ROLL INTEGER);
stmt.executeUpdate(createString);
Entering Data into a Table
String inputString = INSERT INTO STUCENTINFO+
VALUES ('ramesh', msc,4);
stmt.executeUpdate(inputString);
Getting Data form a Table
String selectString = SELECT * FROM STUDENTINFO;
ResultSet rs = stmt.executeQuery(selectString);
while (rs.next()) {
String n = rs.getString("NAME");
String c = rs.getString("CLASS");
int r = rs.getInt("ROLL");
System.out.println(n + " " + c +" " + r);
}
We can also use index as follows:
String n = rs.getString(1);
String c = rs.getString(2);
int r = rs.getInt(3);
Updating Tables
String updateString = "UPDATE STUDENTINFO" +
"SET ROLL = 75 " +
"WHERE NAME= 'ramesh'";
stmt.executeUpdate(updateString);
Deleting form a Table
String deleteString = DELETE FROM STUDENTINFO +
WHERE NAME = ramesh;
stmt.executeUpdate(deleteString);
NOTE: You can also use other forms of SQL statements with JDBC.
Advanced Java Programming
![Compiled by: Ramesh Bhatta Page 2
In the three-tier model, commands are sent to a "middle tier" of services, which then
sends the commands to the data source. The data source processes the commands and
sends the results back to the middle tier, which then sends them to the user. Three-tier
model is very attractive because the middle tier makes it possible to maintain control over
access and the kinds of updates that can be made to corporate data. Another advantage is
that it simplifies the deployment of applications. Finally, in many cases, the three-tier
architecture can provide performance advantages. The figure below shows three-tier
architecture.
Example
JDBC is used to execute common SQL statements like CREATE, INSERT, UPDATE,
DELETE, and SELECT. Every java program that handles database must import java.sql
package. The example below executes CREATE statement that creates a table named
STUDENTINFO with three attributes NAME, CLASS, and ROLL.
import java.sql.*;
public class CreateTable {
public static void main(String args[]) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //
loading the driver
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException:
"+ e.getMessage());
}
try {
Connection con =
DriverManager.getConnection("jdbc:odbc:students",
"nawaraj","hello"); //making connection
Statement stmt = con.createStatement();
//creating statement
String sqlString = "CREATE TABLE STUDENTINFO"+
"(NAME VARCHAR(32),"+
"CLASS VARCHAR(10),"+
Advanced Java Programming](https://image.slidesharecdn.com/jdbc-241118133811-9329142b/85/Java-database-connectivity-notes-for-undergraduate-2-320.jpg)
