Database connectivity

Database connectivity

Unit Structure

15.1 Introduction
15.2 A connection can be open with the help of following steps
15.3 Connecting to an ODBC Data Source
15.4 JDBC Programs

15.1 Introduction :

A Database connection is a facility in computer science that allows client software to communicate with database server software, whether on the same machine or not. A connection is required to send commands and receive answers.
Connections are built by supplying an underlying driver or provider with a connection string, which is a way of addressing a specific database or server and instance as well as user authentication credentials (for example, Server=sql_box;Database=Common;User ID=uid;Pwd=password;). Once a connection has been built it can be opened and closed at will, and properties (such as the command time-out length, or transaction, if one exists) can be set. The Connection String is composed of a set of key/value pairs as dictated by the data access interface and data provider being used.

15.2 A connection can be open with the help of following steps

1. Importing Packages
2. Registering the JDBC Drivers
3. Opening a Connection to a Database
4. Creating a Statement Object
5. Executing a Query and Returning a Result Set Object
6. Processing the Result Set
7. Closing the Result Set and Statement Objects
8. Closing the Connection

Step 1. Importing Packages
The following JDBC packages will be imported for creating connection. java.sql.
java.math. java.io. oracle.jdbc.driver.

Step 2. Registering the JDBC Drivers
Following four parameters are required to register JDBC Drivers.
o Database URL
o JDBC Driver name
o User Name
o Password
JDBC Drivers can be register using following methods.
o Class drvClass=Class.forName(m_driverName);
o DriverManager.registerDriver((Driver)drvClass.newInstance());

Step 3 : Opening a Connection to a Database
Connection to the underlying database can be opened using
Connection m_con=DriverManager.getConnection(m_url,m_userName,m_password);

Step 4 : Creating a Statement Object
SQL Statements
Once a connection is established, It is used to pass SQL statements to its underlying database. JDBC provides three classes for sending SQL Statements to the database, where PreparedStatement extends from Statement, and CallableStatement extends from PreparedStatement:
o Statement : For simple SQL statements ( no parameter )
o PreparedStatement : For SQL statements with one or more IN parameters, or simple SQL statements that are executed frequently.
o CallableStatement : For executing SQL stored procedures.
The statement interface provides three different methods for executing SQL statements :
o executeQuery : For statements that produce a single result set.
o executeUpdate : For executing INSERT, UPDATE, or DELETE statements and also SQL DDL (Data Definition Language) statements.
o execute : For executing statements that return more than one result set, more than one update count, or a combination of the two.
A Statement object is used with following steps:
Statement
Statement stmt=m_con.createStatement();
Statement stmt=m_con.createStatement(int resultSetType, int resultSetConcurrency);
PreparedStatement
PreparedStatement pstmt=m_con.prepareStatement(String sql);
PreparedStatement pstmt=m_con.prepareStatement(String sql, int resultSetType, int resultSetConcurrency),
Note:
The SQL parameter could contain one or more ‗?‘ in it. Before a PreparedStatement object is executed, the value of each ‗?‘ parameter must be set by calling a setXXX method, where XXX stands for appropriate type for the parameter. For ex. If the parameter has a java type of String, the method to use is setString.
CallableStatement
CallableStatemet csmt=m_con.prepareCall(String sql);
CallableStatemet csmt=m_con.prepareCall(String sql, int resultSetType, int resultSetConcurrency),);
Note :
The sql parameter is in the form of ―{call <stored_procedure_name>[(arg1, arg2,...)]} ― or‖ { ?=call <stored_procedure_name>[(arg1,arg2...)]}‖. It could contain one or more ‗?‘s in it, which indiacates IN, OUT or INOUT parameters. The value of each IN parameter is set by calling a setXXX mehod, while each OUT parameter should be registered by calling a registerOutParameter method.
Step 5: Executing a Query and Returning a Result Set Object AND
Step 6: Processing the Result set
Execute the Statement
Statement :
ResultSet res=stmt.executeQuery(String sql);
int rowCount=stmt.executeUpdate(String sql);
boolean result=stmt.execute(String sql);
PrepaedStatement :
ResultSet res=pstmt.executeQuery();
int rowCount=pstmt.executeUpdate();
boolean result=pstmt.execute();
CallableStatement :
ResultSet res=cstmt.executeQuery();
int rowCount=cstmt.executeUpdate();
boolean result=cstmt.execute();
Processing the Result set
A result set contains all of the rows which satisfied the conditions in an SQL statement and it provides access to the data in those rows through getXXX mehods that allow access to the various columns of the current row.
The ResultSet.next() method is used to move to the next row of the ResultSet, making the next row become the current row. ResultSet.next() returns true if the new current row is valid, false if there are no more rows. After all the works have been done, the ResultSet should be closed with ResultSet.close() method.
Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results generated by the execution of a CallableStatement object should be retrieved before OUT parameters are retrieved using CallableStatement.getXXX methods.
Step 7: Closing the Result Set and Statement Objects
Close the statement
After all the works have been done, the result set and statement should be closed with the following code :
Resultset : rset.close();
Statement : stmt.close();
PrepaedStatement : pstmt.close();
CallableStatement : cstmt.close();
Step 8: Closing the Connection
After all the works have been done, the Connection should be closed with the following code:
(Connection name)m_con.close();

15.3 Connecting to an ODBC Data Source

A database can be created and managed through Java applications. Java application that uses a JDBC-ODBC bridge to connect to a database file either a dbase, Excel, FoxPro, Access, SQL Server, Oracle or any other. Open the ODBC Data source from the control panel. A database can be created and managed through Java applications.
Follow the following steps to connect to an ODBC Data Source for ―ORACLE‖.
1. Select Control Panel.

2. Select Administrative Tool

3. Select “Data Sources (ODBC)” icon

4. Select the MS-ODBC for oracle or any other driver that felt it required.

5. Once clicking the finish button, the following window appears asking for Data Source name, description etc.

6. Provide “Data Source Name”, “Discription”,”Username” and “Server” name. The username and Server name can be obtained from the Administrator. Click on ok button.

The DSN is now ready and the Java code can be written to access the database‘s tables.

15.4 JDBC Programs

1. Example for creating Table.
// Create Table
import java.sql.*; // imports all classes that belongs to the package java.sql.*
public class CreateTab
{
public static void main(String args[])
{
try
{
Class.forName(―sun.jdbc.odbc.JdbcOdbcDriver‖);
Connectioncon= DriverManager.getConnection (jdbc:odbc:nitin‖ ,scott‖,‖tiger‖);
// specifies the type of driver as JdbcOdbcDriver.
Statement stat= con.createStatement();
String str=‖Create table T1(Rno number(2), Stdname varchar2(20))‖;
Stat.executeUpdate(str);
System.out.println(―Table created successfully‖);
}
Catch(SQLExecution e 1)
{
System.out.println(―Errors‖ + e 1);
}
Catch(ClassNotFoundException e 2)
{
System.out.println(―Errors‖ + e 2);
}
}
}

2.Example for inserting records into a Table
// Insert into table
import java.sql.*;
public class InsertTab
{
public static void main(String args[])
{
ResultSet result;
try
{
Class.forName(―sun.jdbc.odbc.JdbcOdbcDriver‖);
Connectioncon= DriverManager.getConnection (jdbc:odbc:nitin‖ ,scott‖,‖tiger‖);
Statement stat= con.createStatement();
Stat.executeUpdate(―Insert into T1 values(20,‘Smith‘)‖);
Stat.executeUpdate(―Insert into T1 values(21,‘John‘)‖);
Stat.executeUpdate(―Insert into T1 values(22,‘Kate‘)‖);
Stat.executeUpdate(―Insert into T1 values(23,‘Stive‘)‖);
System.out.println(Rows Inserted successfully‖);
result=stat.executeQuery(―Select * from T1‖);
while(result.next())
{
System.out.println(result.getInt(I)+result.getString(2));
}
}
catch(Exception e)
{
System.out.println(―Errors‖+e);
}
}
}

3.Example for viewing rows from a table
// viwing from emp table
import java.sql.*;
public class SelectEmp
{
public stativ void main(String args[])
{
String url=‖jdbc:odbc:nitin‖;
Connection con;
String s= ―select ename from emp 1‖;
Statement stmt;
try
{
Class.forName(―sun.jdbc.odbc.JdbcOdbcDriver‖);
}
catch(java.lang.ClassNotFoundException e)
{
System.err.println(―ClassNotFoundException:‖);
System.err.println(e.getMessage());
}
try
{
con=DriverManager.getConnection(url,‖Scott‖,‖Tiger‖);
stmt=con.createStatement();
resultSet rs=stmt.executeQuery(s);
while(rs.next())
{
String s1=rs.getString(―ename‖);
System.out.println(―Employee name:‖ +s1);
}
stmt.close();
con.close();
}
catch(SQLException ex)
{
System.err.println(―SQLException:‖+ex.getMessage());
}
}
}

4. Example using prepared statements
import java.sql.*;
public class PreStExample
{
public static void main(String[] args)
{
Connection con = null;
PreparedStatement prest;
try{
Class.forName(―sun.jdbc.odbc.JdbcOdbcDriver‖);
Connectioncon= DriverManager.getConnection(jdbc:odbc:nitin‖ ,scott‖,‖tiger‖);
try{
String sql = "SELECT stdname FROM T1 WHERE Rno = ?";
prest = con.prepareStatement(sql);
prest.setInt(1,21);
ResultSet rs1 = prest.executeQuery();
while (rs1.next())
{
String stname = rs1.getString(1);
System.out.println("student name is: "+stname);
}
prest.setInt(1,23);
ResultSet rs2 = prest.executeQuery();
while (rs2.next())
{
String stname1 = rs2.getString(1);
System.out.println("student name is: "+stname1);
}
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}