JAVA DATABASE CONNECTIVITY

Chapter 6: Database Connectivity A real life application needs to manipulate data stored in a Database. A Java application can also intract with a database designed in DBMS software like MySQL, Oracle, MS SQL Server etc.  A database is a collection of related data in the form of Tables. Most of the database uses SQL (Structured Query Language) to Insert, Delete, Update or retrieve stored records.  In order to connect a Java application (Front-End) to a Database (Back-End) designed in MySQL, Oracle, Sybase, MS SQL Server etc, you need a Interface Driver Program.  Java Provides JDBC API (Java Database Connection -Application Program Interface) and JDBC Driver for MySQL to connect a MySQL database. What is JDBC: JDBC is JAVA’s Database connection driver interface which performs the following task for the application.  Establish a connection with a Database.  Send SQL request (Query) to a Database Server.  Returns Result obtained against Query.
Classes used for Database Connectivity: The Core element of JDBC is JDBC API, which consists of a set of Java classes equipped with predefined methods to handle various data access functions such as Selecting appropriate database driver, establishing connection, submitting SQL query and processing results.JDBC API offers four main classes, which are-  Driver Manager Class: It loads the JDBC driver to locate, logs and access a database.  Connection Class: It manages communication between Java Client Application and Database, through SQL statements.  Statement Class: It contains SQL commands which is submitted to the Database Server and returns ResultSet object containing the result of SQL statement.  Result Set Class: It provides predefined mehods to access and convert data values returned by the executed SQL statement. Connecting Java Application with Database: After installing JDBC Driver, you may access MySQL database through JAVA Application. The Following Six steps may be followed to establish a connection with MySQL database.  Step 1: Import Required package/classes in the application.  Step 2: Register the JDBC Driver to JDBC Driver Manager.  Step 3: Open a Connection.  Step 4: Execute a Query.  Step 5: Extract data from Result set  Step 6: Close Connection. Step 1:Importing Required package/classes To Import Java.sql Library package in the Application you need to give following import statements. import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet;

Step 2: Registering the JDBC Driver To open a Communication channel, you require to initialize driver by registering the JDBC driver with JDBC Driver Manager using Class.forName() method of java.lang package. Class.forName(“java.sql.DriverManager”); Step 3: Opening a Connection DriverManager.getConnection() method is used to create a connection object that represents a physical connection with database. It requires the complete address/path of the database (Database URL), user name and password as a parameter. A database URL can be formed as- jdbc:mysql :// localhost/ Suppose school is a database designed in MySQL, then Database URL will be as follows- “jdbc:mysql://localhost/school” You can assign this string on a variable, which can be used later with DriverManager.getConnection() method. String DB_URL = “jdbc:mysql://localhost/school”; Connection con = DriverManager.getConnection(DB_URL,”root”, ”abc”) Step 4: Executing a Query You must create a Statement object for building and submitting a SQL query, using CreateStatement() method of Connection object created in Step 3. Statement stmt = con.createStatement(); To execute a query executeQuery() method along with a valid SQL statement is used, which returns the records from the database (Result Set) on ResultSet type object. ResultSet rs = stmt.executeQuery(“”); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(“select

Step 5: Extracting Data from ResultSet object To retrieve the data from the ResultSet object, which contains records, You may use the following method. .get(); Where may be Int, Long, String, Float etc. depending on the type of column the table. Generally, the data values are assigned on the variables and later used in the TextField controls of the Form using setText() method. int r= rs.getInt(“roll”); String n= rs.getString(“name”); int c= rs.getInt(“class”); Since a ResultSet object may contain more than one records (when SQL query may return multiple records) , so a loop is required to process all the records. A while… loop is generally used to read all records. Step 6: Closing connection After all the processing , the final step is to close the environment by closing ResultSet, Statement and Connection objects using close() method. rs.close(); stmt.close(); con.close(); At last, to handle errors during establishing connection all the required statements are kept in a try{…} catch (){…} block like this– try{…………………… } catch ( Exception ) { ; }

Sample Code- Putting all steps together: All these six steps can be combined and written as- // Step 1- import package at the top// import java.sql.*; //* The following code may be placed in ActionPerformed event of a button*// String db=“jdbc:mysql://loacalhost/school”); // Database URL String qr= “select roll, name, class from student”; // Query try{ //Step 2- Register Driver // Class.forName(“java.sql.DriverManager”); // Step 3-Open Connection Connection con=Driver.getConnection(db, ”root”, ”xyz”); // Step 4- Execute Query Statement stmt=con.createStatement(); ResultSet rs= stmt.executeQuery( qr); int r, c; String n; // 5. Extract Data// while (rs.next()) { r= rs.getInt(“roll”); n= rs.getString(“name”); c= rs.getInt(“class”); ……………………………….; // Code to manipulate data//
//6.Close Environment// rs.close(); stmt.close(); con.close(); } // end of try block// catch (Exception e) { JOptionPane.showMessageDialog(null, e.getMessage()); } // end of catch block Commonly used Result Set method: A Result set object maintains a cursor, which points to its current rowof data. When it is created, cursor is positioned before the first row. You can move the cursor using the following methods. Method Purpose next () Moves the cursor forward one row. It returns false when cursor is positioned after the last record. previous() Moves cursor to previous record from current position. It returns false when cursor is positioned before the first record. first() Moves cursor to first record. It returns true if it positioned at first record otherwise returns false. last() Moves cursor to last record. It returns true if it positioned at last record otherwise returns false. relative(n) Moves cursor relative to its current position i.e if it is on 2nd row, then relative(3) places cursor at 5th record. absolute(n) Moves cursor at nth record of result set irrespective to its current position. getRow() Returns the current row number where cursor is positioned.