Java Database Connectivity (JDBC) ,Steps to connect to the database in Java

 

JDBC

Java Database Connectivity (JDBC) API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.

 

JDBC Driver

JDBC Driver is a software component that enables java application to interact with the database.

 

There are 4 types of JDBC drivers:

 

1.  JDBC-ODBC bridge driver-Type 1

2.  Native-API driver (partially java driver)-Type 2

3.  Network Protocol driver (fully java driver)-Type 3

4.  Thin driver (fully java driver)-Type 4

 

JDBC classes are enclosed in java.sql package. This package contains following set of classes and interfaces.

 

Classes/interface	Description
java.sql.BLOB	Provide support for BLOB(Binary Large Object) SQL type.
java.sql.Connection	creates a connection with specific database
java.sql.CallableStatement	Execute stored procedures
java.sql.CLOB	Provide support for CLOB(Character Large Object) SQL Type
java.sql.Date	Provide support for Date SQL type.
java.sql.Driver	create an instance of a driver with the DriverManager.
java.sql.DriverManager	This class manages database drivers.

java.sql.PreparedStatement	Used to create and execute parameterized query.
java.sql.ResultSet	It is an interface that provide methods to access the result row-by-row.
java.sql.Savepoint	Specify savepoint in transaction.
java.sql.SQLException	Encapsulate all JDBC related exception.
java.sql.Statement	This interface is used to execute SQL statements.

 

For postgresql, use the driver: org.postgresql.Driver

 Steps to connect to the database in java:

There are 5 steps to connect any java application with the database in java using JDBC.

·       Register the driver class

·       Creating connection

·       Creating statement

·       Executing queries

·       Closing connection

1.     Register the driver Class:

The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.

Syntax

public static void forName(String className)throws ClassNotFoundException

2.     Create the connection object:

The getConnection() method of DriverManager class is used to establish connection with the database.

Syntax

i.          public static Connection getConnection(String url)throws SQLException

ii.          public static Connection getConnection(String url,String name,String password) throws SQLException

Commonly used methods of Connection interface:

 

createStatement()	Creates a statement object that can be used to execute SQL queries
createStatement(int resultSetType, int resultSetConcurrency) setAutoCommit(boolean status)	Creates a Statement object that will generate ResultSet objects with the given type and concurrency. Is used to set the commit status.By default it is true.
commit()	saves the changes made since the previous

Rollback() Close()

commit/rollback permanent Drops all changes made since the previous commit/rollback. closes the connection and Releases a JDBC resources immediately.

 

                  3.     Create the Statement object:

The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database

Syntax

public Statement createStatement()throws SQLException eg. Statement stmt=con.createStatement();

4.     Execute the query:

The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax

public ResultSet executeQuery(String sql)throws SQLException eg. ResultSet rs=stmt.executeQuery("select * from employee"); while(rs.next())

{

System.out.println(rs.getInt(1)+" "+rs.getString(2));

}

5.     Close the connection:

Closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

Syntax

public void close()throws SQLException

 

Classes and Interfaces of JDBC:

1.  DriverManager: This class will attempt to load the driver classes referenced in the jdbc.drivers system property.

2.     Statement: Object of this interface is used for firing a static SQL or PL/SQL queries       returning the results it has produced.

3. CallableStatement: This interface is used to execute SQL stored procedures. The stored procedures are a group of queries with variables in it.

Defined as: CallableStatement cs=con.prepareCall();

The developer can prepare stored procedure for multiple uses and hence it allows variable declaration also. Executing a SQL statement with the Statement object, and returning a jdbc resultSet.

 To execute a query, call an execute method from Statement:

·       execute(): Use this method if the query could return one or more ResultSet objects.

·       executeQuery(): Returns one ResultSet object.

·       executeUpdate(): Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.

 4.     ResultSet interface: In case of select query, it returns set of selected records, a ResultSet object maintains cursor pointing to its current row of data.

 Methods of Resulset interface:

Method	Description
boolean next()	is used to move the cursor to the one row next from the current position
boolean previous()	is used to move the cursor to the one row previous from the current position.
boolean first()	is used to move the cursor to the first row in result set object
boolean last()	is used to move the cursor to the last row in result set object.
boolean absolute(int row)	is used to move the cursor to the specified row number in the ResultSet object.
boolean relative(int row)	is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.

 

MetaData:

MetaData is data of data, i.e. we can get further information from the data.

1.     ResultSetMetaData:

In case of ResultSet, metadata of retrieved ResultSet called as ResultSetMetaData. This interface provides an object that can be used to get information about the types and properties of the columns in a ResultSet object.

 

            Methods:

Method	Description
int getColumnCount()	Returns the number of columns available in ResultSet object.
String getColumnType(int col_num)	Returns the SQL type of the column.
getPrecision(int col_num)	Returns the max size for the column
Boolean isAutoIncrement(int col_num)	Checks & returns whether the specified 70 column is set for auto increment or not.
String getTableName(int col_num)	Returns the table name to which the specified column belongs.
boolean isNullable()	Checks and returns whether the specified column is allowed to keep null or not.
boolean isReadOnly()	Checks and returns whether specified column is read only or not.

2.     DatabaseMetaData: In case of Database, metadata of database called as DatabaseMetaData. An instance of this interface provides comprehensive information about the database.

 

            Methods:

Method	Description
boolean allProceduresAreCallable()	Retrieves whether all the stored procedures are callable or not
Boolean allTablesAreSelectable()	Retrieves whether the user can fire SELECT query on all connected tables.
int getDatabaseMinorVersion()	Retrieves the minor version of connected database.
int getDatabaseMajorVersion()	Retrieves the major version of connected database.
String getDatabaseProductName()	Retrieves the name of this database product.
String getDatabaseProductVersion()	Retrieves the product version of the connected database

 1.     Program to insert data into student table.

Program:

import java.sql.*;

public class Demo1

{

public static void main(String args[])

{ 

    try{

Class.forName("org.postgresql.Driver");

Connection con=DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/dbbsc","postgres","123");Statement stmt=con.createStatement();

stmt.executeUpdate("insert into student(rno,name)values(1,'Nandini')");

stmt.close();

        con.close();

                }

                catch(Exception ex)

{

System.out.println(ex);

}

}

}

Program to update data from student table.

Program:

import java.sql.*; 

public class Demo2

{

public static void main(String args[])

{

try

{

Class.forName("org.postgresql.Driver");

Connection con=DriverManager.getConnection ("jdbc:postgresql://127.0.0.1:5432/dbbca","postgres","123"); stmt.executeUpdate("update student set rno=5 where name='Rahul'"); stmt.close();

con.close();

}

catch(Exception ex)

{

System.out.println(ex);

}

}

}

Program to delete data from student table

Program: import java.sql.*;  import java.io.*; public class  Demo3 {     public static void main(String args[])     {         try { Class.forName("org.postgresql.Driver"); Connection con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/dbbca","postgres","123"); Statement stmt=con.createStatement();  stmt.executeUpdate("delete from student where rno=1");  stmt.close(); con.close(); } catch(Exception ex) { System.out.println(ex); } } }

Program to display information of student(rno,name,marks).

Program: import java.sql.*;  import java.io.*; public class  Demo4 { public static void main(String[] args) throws SQLException {     Class.forName("org.postgresql.Driver");     Connection conn = null;                     Statement stmt = null;                    ResultSet rs = null;                        try                             { Connection con=DriverManager.getConnection ("jdbc:postgresql://127.0.0.1:5432/dbbca","postgres","123"); if(conn==null) System.out.println("Connection failed "); else { System.out.println("Connection successful"); stmt = conn.createStatement(); rs = stmt.executeQuery("Select * from student"); while(rs.next()) { System.out.print("RollNo = " + rs.getInt(1)); System.out.println("Name = " + rs.getString(2)); System.out.println("Marks = " + rs.getInt(3)); } conn.close(); } } catch(Exception ex)s { System.out.println(ex); }   } }

Java Lab Programs:SPPU

SET A

1.     Write a JDBC program to display all the details of the Person table in proper format on the screen. Create a Person table with fields as PID, name, gender, birth_year in PostgreSQL. Insert values in Person table.

2.     Write a program to display information about the ResultSet like number of columns available in the ResultSet and SQL type of the column. Use Person table. (Use ResultSetMetaData).

3.     Write a JDBC program to display all the countries located in West Region. Create a table Country in PostgreSQL with fields (Name, continent, Capital,Region). Insert values in the table.

4.     Write a JDBC program to insert the records into the table Employee(ID,name,salary) using PreparedStatement interface. Accept details of Employees from user.

 

SET B

 1.     Write a JDBC program to perform search operation on Person table.

        1.     Search all the person born in the year 1986.

        2.     Search all the females born between 2000- 2005.

2.     Write a JDBC program to update number_of_students of “BCA Science” to 1000. Create a table Course (Code,name, department,number_of_students). Insert values in the table.

3.     Write a menu driven program to perform the following operations on District(Name, area, population) table.

1.  Insert

2.  Modify

3.  Delete

4.  Search

5.  View All

6.  Exit

SET C

1.     Create a table Student with fields roll number, name, percentage using Postgresql. Insert values in the table. Write a JDBC program to display all the details of the student table in a tabular format on the screen. (Using Swing)