☰ Topics

Database Connectivity

Connectivity between front end (GUI) and backend(DBMS/RDBMS) is called Database Connectivity. To connect java with any RDMS, we use JDBC (Java Database Connectivity). It can be done by using either of the four ways:

  1. Type I (DSN)
  2. Type II (Thin Server)
  3. Type III (Server)
  4. Type IV (Distributed Server)
Here, we wil deal with Type I. DSN (Data Source Name) is a common platform for all drivers. It needs the help of another driver ODBC (Open Database Connectivity). It is written in C language. JDBC is written in java, C & C++.

Steps:

  1. Open Control Panel and click on Administrative Tools.
  2. Open ODBC Data Sources (32 bit).
  3. A dialog box opens, click on Add.
  4. Select "Oracle in XE" and click on finish.
  5. A dialog box opens, provide a Data Source Name and TNS Service Name as "XE"
  6. Before proving TNS Service name please ensure that Connection is tested by clicking on Test Connection and then providing user name and password of oracle.
The user id and password of oracle server is noted for its use in code. We are using JDBC for java code and ODBC for oracle code.
GUI+JDBC-ODBC+SQL
JDBC-ODBC is called bridge. This process is also known as bridge based connection.

Steps:

  1. import package java.sql.*
  2. Create bridge
  3. Class.forName("Sun.jdbc.odbc.JdbcOdbcDriver")
  4. Establish Connection, Connection class is used for it
  5. Insert/Update/Delete/Select We use Statement class when parameter is fixed i.e. query is fixed and PreparedStatement when query is not fixed. When we are using Statement class, we use executeQuery() method to execute the query and when we are using PreparedStatement class, we use executeQuery() to execute query and executeUpdate() for INSERT and UPDATE.
  6. We use ResultSet class to move particular row & column (Read only and forward only) with certain parameters, we can make ResultSet backward, both read and write. For movement of pointer, next() method is used.
Program to execute SELECT query:
import java.sql.*;
class exdata
{
	public void data()
	{
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");     //It internally combines with DriverManager
    			Connection con=DriverManager.getConnection("jdbc:odbc:myproj","hr","oracle");   
			//In above statemet, 'myproj' is DSN name, 'hr' is user id and 'oracle' is password of oracle
    			Statement st=con.createStatement();
    			ResultSet rs=st.executeQuery("select * from prty_mst");
    			while(rs.next())
    			{
      				System.out.print(rs.getString(1)+"  "+rs.getString(5)+" "+rs.getString(7)+" "+rs.getString(8));     
				//In avove statements, in place of getString(), getInt(), getChar(), getDouble() can be used as per the requirements
      				System.out.println();
			}
		}
		catch(Exception ex)
		{
			System.out.println(ex.toString());
		}
	}
  	public static void main(String[] arg)
  	{
    		exdata ob=new exdata();
		    ob.data();
    	}
}
                            

After executing this program, the data of specified columns of table "prty_mst" will be displayed.