Class 12 Informatics Practices Notes Ch 7 Accessing MySQL Database Using ODBC/JDBC

Class 12 Informatics Practices Quick Revision notes Chapter 7 Accessing MySQL Database Using ODBC/JDBC

Classes Required for Database Connectivity
There are four main classes required for the database connectivity. These are as follows:
1. DriverManager Class
The JDBC DriverManager class, loads the JDBC driver needed to access a particular data source, located and logs on to the database and returns a Connection object. The DriverManager class works between the user and the drivers. The task of the DriverManager class is to keep track of the drivers that are available and handles in establishing a connection between a database and the appropriate driver. It even keeps track of the driver login time limits and printing of log and tracing messages. This class is mainly useful for the simple application. The most frequently used methods of this class is DriverManager.
getConnection( ). We can know by the name of the method, that this method establishes a connection to a database. The DriverManager class maintains the list of the Driver classes. Each driver has to get registered in the DriverManager class by calling the method DriverManager.registerDriver(). The driver is loaded by calling the Class.forName() method. JDBC drivers are designed to tell the DriverManager about themselves automatically, when their driver implementation class gets loaded.
This class has many methods. Some of the commonly used methods are given below:

1. deregisterDriver(Driver driver) It drops the driver from the list of drivers registered in the DriverManager class.
2. registerDriver(Driver driver) It registers the driver in the DriverManager class.
3. getConnection(String url) It establishes the connection to a given database URL.
4. getConnection(String url, String user, String password) It establishes the connection to a given database URL, with appropriate username and password.
5. getConnection(String url, Properties info) It establishes the connection to a given database URL, as per passed driver properties.
6. getDriver(String url) It attempts to locate the driver by the given string.
7. getDrivers( ) It retrieves the enumeration of the drivers, which has been registered with the DriverManager class.

2. Connection Class
The JDBC Connection class manages the communications between a Java client application and a specific database (e.g. the MySQL database), including passing SQL statements to the DBMS and managing transactions. The Connection class represents a single logical database connection. You can use the Connection class for sending a series of SQL statements to the database and managing the committing or aborting (rollback) of those statements.
3. Statement Class
The JDBC Statement class contains SQL strings that are submitted to the DBMS. An SQL SELECT statement returns a ResultSet object that contains the data retrieved as the result of SQL statement.
4. ResultSet Class
The JDBC ResultSet class provides predefined methods to access, analyse and convert data values returned by an executed SQL SELECT statement.
We can use a ResultSet object to access a table of data that was generated by running a query. The table rows are retrieved in sequence. Within a row, column values can be accessed in random order. The data stored in ResultSet is retrieved by using the various get methods, depending on the type of data being retrieved. The next() method is used to move to the next row. ResultSet allows us to get and update columns by name, although using the column index results in improved performance.
Prerequisites for Connecting to MySQL from Java
When we connect two different types of application, e.g. Java and MySQL, we need a software that can act as a bridge between the two. These software are known as MySQLConnector/J. Before connecting, you can determine whether MySQL connector is available with your NetBeans IDE or not, you need to perform the following steps:

1. Start NetBeans IDE.
2. Click on the Tools menu and then select Libraries. (The Library Manager dialog box appears.)
3. From the Libraries dialog box select MySQLJDBC Driver under class Libraries.
4. In the Library Manager dialog, click at Add JAR/Folder push button and then select the downloaded and extracted driver files and click Add JAR/Folder push button.
5. Select the desired file and click on Add JAR/Folder push button.

Procedure/Steps for Creating Database Connecting Applications
After the prerequisites for creating a database connecting application, we need to perform the following steps:
Step 1
Import the Package Required for Database Programming First of all, we need to import the library package containing the JDBC classes, i.e. Connection, DriverManager, Statement and ResultSet needed for database programming. For this purpose, we need to write the following statements:

1. import java.sql .Connection;

2. import java.sql .DriverManager;

3. import java.sql .Statement;

4. import java.sql .ResultSet;

However even we can write import java.sql.*; to import the entire package. Thereafter, we need to add MySQL JDBC connector from the projects window.
Step 2
Register the JDBC Driver To register the JDBC Driver, we initialise a driver to open a communication channel with the database from the Java application. For connecting to MySQL,
we need to write one of the following statements:

Class.forName("java.sql.Driver”) ; or Class.forName(“com.mysql.jdbc.Driver”);

Step 3
Open a Connection To open a Connection, we need to use the DriverManager.getConnection( ) method and create a connection object, which represent the physical connection with the database, that allows us to establish a physical connection to the data source for open a connection, we need to write following statement:

Connection conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD);

Step 4
Execute a Query To execute a query, we need to create an object of a type Statement for building and submitting an SQL statement to the database using createStatement() method of Connection type object.
Statement stmt = conn.createStatement( );
Thereafter, we need to execute the SQL statement using executeQuery() method, which returns a ResultSet that contains the resultant dataset of the executed query.

e.g. Stri ng MyString ; MyString = “SELECT roll, name, age FROM STUDENT”; ResultSet rs = stmt.executeQuery(MyString ) ; It however, the SQL statement is an UPDATE, INSERT or DELETE statement that you can execute SQL query using executeUpdate() method, e.g. Statement stmt = conn.createStatement( ); String MyString; MyString = “DELETE FROM STUDENT”; ResultSet rs = stmt.executeUpdate(MyString );

Step 5
Extract Data from ResultSet Extraction of data is required, if we are fetching data from the database, using SQL SELECT query. We can use the appropriate ResultSet.get( ) method to retrieve the data from the desired resultset.
The ResultSet object provide several methods, some example of these methods include getlnt( ), getLong( ), getString( ), getFloat(), getDate(), etc.

e.g. int roll = rs.getInt(“roll”); String name = rs.getString(“name”) ; int age = rs.getlnt(“age”);

Step 6
Clean up the Environment At last we have to clean the environment. For this purpose, we have to use the close( ) method to physically close all the database resources.

rs . close(); stmt. close(); conn.close();

ResultSet Methods
When a database is connected and data fetched into it, a ResultSet is maintained. A ResultSet object maintains a cursor, which points to its current row of data.

1. next( ) Moves the cursor in forward direction one row. If the cursor is in the last row, it will return false.
2. firstf( ) Moves the cursor to the first row in a ResultSet object. If the cursor is already present in the first row, it will return false.
3. last( ) Moves the cursor to the last row in the ResultSet object. If the cursor is already present in the last row, it will return false.
4. relative(int rows) Moves the cursor relatively, e.g. if the cursor is already present in the 4th row and we apply the relative(2), then it will place the cursor in the 6th row.
5. absolute(int mo) Moves the cursor on the specified number of row, irrespective of current cursor position.
6. getRow( ) Retrieve a specified number of rows from a ResultSet.

Java Program to retrieve Columns First name, Last name, id, age from table “Employees” in MySQL database

{ // STEP 1: Import required packages import java.sql.* ; public class firstExample { // JDBC driver name and database URL static final String J D B C_D R I V E R = “com . mysql . jdbc.Driver” ; static final String DB_URL = “jdbc:mysqllocalhostEmp” ; // Database username and password static final String USERNAME = “Username”; static final String PASSWORD = “Password”; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { // STEP 2: Register JDBC Driver Cl ass.forName (JDBC_DRIVER); // STEP 3: Open a Connection System.out.println(“Connecting to database...”); conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD); // STEP 4: Execute a query System.out.println(“Creating statement. . .”) ; stmt = conn.createStatement) ; String Sql; Sql = “SELECT Id, FirstName, LastName, Age FROM Employees”; ResultSet rs= stmt.executeQuery(Sql) ; // STEP 5; Extract data from resultset while(rs.next()) { // retrieve by column name int Id = rs.getlnt(“ld”); int Age = rs.get Int(“Age”) ; String FirstName = rs . getString(“FirstName” ) ; String LastName = rs.get String(“LastName” ) ; // Display values Systern.out.print(“ I d ”+Id); Systern.out.print(“Age ”+Age); System.out.print(“First Name ” +FirstName); Systern.out.Print(“Last Name ” + LastName); } // STEP 6: clean-up environment rs.close() ; stmt.close(); conn.close() ; } catch(Exception se) { // Handle error for JDBC JOptionPane.showMessageDialog(this, se.getMessage()); se.printStackTrace() ; } } }