Images JDBC - PART I

JDBC
JDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBCis oriented towards relational databases.
The Java Database Connectivity (JDBC) is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases –SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC provides a call-level API forSQL-based database access.
JDBC technology allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data. With a JDBC technology-enabled driver, you can connect all corporate data even in a heterogeneous environment.
Relational Database Concepts Introduction An important part of every business is to keep records. We need to keep records of our customers, the employees of our company, the emails etc. To keep all the data indivually is quite difficult and hectic job, because whenever if we need the record of a particular customer or an employee we need to search manually. It takes lot of time and still not reliable. Here comes the concept of databases. Relational Database Concepts What is database? A database is an organized collection of information. A simple example of a database are like your telephone directory, recipe book etc. A Relational model is the basis for any relational database management system (RDBMS).A relational model has mainly three components: 1) A collection of objects or relations,. 2) Operators that act on the objects or relations. 3) Data integrity methods. To design a database we need three things: 1). Table 2). Rows 3). Columns A table is one of the most important ingredient to design the database. It is also known as a relation, is a two dimensional structure used to hold related information. A databaseconsists of one or more tables. A table contains rows : Rows is a collection of instance of one thing, such as the information of one employee. A table contains the columns: Columns contains all the information of a single type. Each column in a table is a category of information referred to as a field. One item of data, such as single phone number of a person is called as a Data Value. Relational Database Concepts ACID Properties and Normalization: ACID properties are one of the important concept for databases. ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties of a DBMS allow safe sharing of data. Without these properties the inaccuracy in the data will be huge. With the help of the ACID properties the accuracy can be maintained. Normalization is a design technique which helps the to design the relational databases. Normalization is essentially a two step process that puts data into tabular form by removing redundant data from the relational tables. A basic goal of normalization is to create a set of relational tables that are free of redundant data and the data should be consistent. Normalization has been divided into following forms. 1. First Normal Form: A relational table, by definition are in first normal form. All values of the columns are atomic. It means that it contains no repeating values. 2). A relationl table is in second normal form if it is in 1NF and every non- key column is fully dependent upon the primary key. 3). A relational table is in third normal form (3NF) if it is already in 2NF and every non- key column is non transitively dependent upon its primary key. The advantage of having table in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies. DATABASE CONNECTIVITY Introduction JDBC is Java application programming interface that allows the Java programmers to access database management system from Java code. It is developed by JavaSoft, a subsidiary of Sun Microsystems. Java Database Connectivity in short called as JDBC. It is a java API which enables the java programs to execute SQL statements. It is an application programming interface that defines how a java programmer can access the database in tabular format from Java code using a set of standard interfaces and classes written in the Java programming language. JDBC has been developed under the Java Community Process that allows multiple implementations to exist and be used by the same application. JDBC provides methods for querying and updating the data in Relational Database Management system such asAccess, SQL, Oracle etc. The Java application programming interface provides a mechanism for dynamically loading the correct Java packages and drivers and registering them with the JDBC DriverManager that is used as a connection factory for creating JDBC connections which supports creating and executing statements such as SQL INSERT, UPDATE and DELETE. Driver Manager is the backbone of the jdbc architecture. Generally all Relational Database Management System supports SQL and we all know that Java is platform independent, so JDBC makes it possible to write a single database application that can run on different platforms and interact with different Database Management Systems. Java Database Connectivity is similar to Open Database Connectivity (ODBC) which is used for accessing and managing database, but the difference is that JDBC is designed specifically for Java programs, whereas ODBC is not depended upon any language. In short JDBC helps the programmers to write java applications that manage these three programming activities: 1. It helps us to connect to a data source, like a database. 2. It helps us in sending queries and updating statements to the database and 3. Retrieving and processing the results received from the database in terms of answering to your query. Consider a scenario, where you have to develop an application for an Airlines Company to maintain a record of daily transactions. You install SQL Server, design the Airlines database, and ask Airlines personnel to use it. Will the database alone be of any use to the Airlines personnel? The answer is NO! The task of updating the data in SQL server by using SQL statementsalone will be a tedious process. An application will need to be developed that is user friendly and provides a client, the option to retrieve, add, and modify data at the touch of key. Thus you need to develop an application that communicates with a database to perform the following tasks: 1. Store and update the data in the database. 2. Retrieve the data stored in the database and present it to the user in a proper format. JDBC Components JDBC has four Components: 1. The JDBC API. 2. The JDBC Driver Manager. 3. The JDBC Test Suite. 4. The JDBC-ODBC Bridge. 1. The JDBC API. The JDBC application programming interface provides the facility for accessing the relational database from the Java programming language. The API technology provides the industrial standard for independently connecting Java programming language and a wide range of databases. The user not only execute the SQL statements, retrieve results, and update the data but can also access it anywhere within a network because of it's "Write Once, Run Anywhere" (WORA) capabilities. Due to JDBC API technology, user can also access other tabular data sources like spreadsheets or flat files even in the a heterogeneous environment. JDBC application programming interface is a part of the Java platform that have included Java Standard Edition (Java SE ) and the Java Enterprise Edition (Java EE) in itself. The JDBC API has four main interface: The latest version of JDBC 4.0 application programming interface is divided into two packages i-) java.sql ii-) javax.sql. Java SE and Java EE platforms are included in both the packages. 2. The JDBC Driver Manager. The JDBC Driver Manager is a very important class that defines objects which connect Java applications to a JDBC driver. Usually Driver Manager is the backbone of the JDBC architecture. It's very simple and small that is used to provide a means of managing the different types of JDBC database driver running on an application. The main responsibility of JDBC database driver is to load all the drivers found in the system properly as well as to select the most appropriate driver from opening a connection to a database. The Driver Manager also helps to select the most appropriate driver from the previously loaded drivers when a new open database is connected. 3. The JDBC Test Suite. The function of JDBC driver test suite is to make ensure that the JDBC drivers will run user's program or not . The test suite of JDBC application program interface is very useful for testing a driver based on JDBC technology during testing period. It ensures the requirement of Java Platform Enterprise Edition (J2EE). 4. The JDBC-ODBC Bridge. The JDBC-ODBC bridge, also known as JDBC type 1 driver is a database driver that utilize the ODBC driver to connect the database. This driver translates JDBC method calls into ODBC function calls. The Bridge implements Jdbc for any database for which an Odbc driver is available. The Bridge is always implemented as the sun.jdbc.odbc Java package and it contains a native library used to access ODBC. Now we can conclude this topic: This first two component of JDBC, the JDBC API and the JDBC Driver Manager manages to connect to the database and then build a java program that utilizes SQL commands to communicate with any RDBMS. On the other hand, the last two components are used to communicate with ODBC or to test web application in the specialized environment.
DATABASE CONNECTIVITY JDBC Architecture Two-tier and three-tier Processing Models The JDBC API supports both two-tier and three-tier processing models for database access. Two-tier Architecture for Data Access. In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet. In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages. Three-tier Architecture for Data Access. Until recently, the middle tier has often been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as EnterpriseJavaBeans™, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features. With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier. The JDBC architectures can be classified into two layers: 1. JDBC application layer: It signifies a Java application that uses the JDBC API to interact with the JDBC Drivers. A JDBC driver is a software that a Java application uses to access a database. The JDBC driver manager of JDBC API connects the Java Application to the driver. 2. JDBC driver layer: It acts as an interface between a Java application and a database. This layer contains a driver, such as a SQL Server driver or an Oracle driver, which enable connectivity to a database. A driver sends a request to a Java application to the database. After processing the request, the database sends the response back to the driver. The driver translates and sends the response to the JDBC API. The JDBC API forward it to the Java application. DATABASE CONNECTIVITY JDBC Driver JDBC Driver Manager The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple. This is a very important class. Its main purpose is to provide a means of managing the different types of JDBC database driver. On running an application, it is theDriverManager's responsibility to load all the drivers found in the system property jdbc. drivers. For example, this is where the driver for the Oracle database may be defined. This is not to say that a new driver cannot be explicitly stated in a program at runtime which is not included in jdbc.drivers. When opening a connection to a database it is the DriverManager' s role to choose the most appropriate driver from the previously loaded drivers. The JDBC API defines the Java interfaces and classes that programmers use to connect to databases and send queries. A JDBC driver implements these interfaces and classes for a particular DBMS vendor. A Java program that uses the JDBC API loads the specified driver for a particular DBMSbefore it actually connects to a database. The JDBC DriverManager class then sends allJDBC API calls to the loaded driver. JDBC Driver This topic defines the Java(TM) Database Connectivity (JDBC) driver types. Driver types are used to categorize the technology used to connect to the database. A JDBC driver vendor uses these types to describe how their product operates. Some JDBC driver types are better suited for some applications than others. Types of JDBC drivers JDBC drivers are divided into four types or levels. Each type defines a JDBC driverimplementation with increasingly higher levels of platform independence, performance, and deployment administration. The four types are: • Type 1: JDBC-ODBC Bridge • Type 2: Native-API/partly Java driver • Type 3: Net-protocol/all-Java driver • Type 4: Native-protocol/all-Java driver Type 1: JDBC-ODBC Bridge The type 1 driver, JDBC-ODBC Bridge, translates all JDBC calls into ODBC (Open DataBase Connectivity) calls and sends them to the ODBC driver. As such, the ODBC driver, as well as, in many cases, the client database code, must be present on the client machine. Figure shows a typical JDBC-ODBC Bridge environment. Type 1: JDBC-ODBC Bridge Functions: 1. Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver. 2. Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source. 3. Client -> JDBC Driver -> ODBC Driver -> Database 4. There is some overhead associated with the translation work to go from JDBC to ODBC. Pros The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC drivers are already available. Type 1 drivers may be useful for those companies that have an ODBC driver already installed on client machines. Cons • The performance is degraded since the JDBC call goes through the bridge to the ODBC driver, then to the native database connectivity interface. The result comes back through the reverse process. Considering the performance issue, type 1 drivers may not be suitable for large-scale applications. • The ODBC driver and native connectivity interface must already be installed on the client machine. Thus any advantage of using Java applets in an intranet environment is lost, since the deployment problems of traditional applications remain. Type 2: Native-API/partly Java driver JDBC driver type 2 -- the native-API/partly Java driver -- converts JDBC calls into database-specific calls for databases such as SQL Server, Informix, Oracle, or Sybase. The type 2 driver communicates directly with the database server; therefore it requires that some binary code be present on the client machine. Type 2: Native-API/partly Java driver Functions: 1. This type of driver converts JDBC calls into calls to the client API for that database. 2. Client -> JDBC Driver -> Vendor Client DB Library -> Database Pros Type 2 drivers typically offer significantly better performance than the JDBC-ODBC Bridge. Cons The vendor database library needs to be loaded on each client machine. Consequently, type 2 drivers cannot be used for the Internet. Type 2 drivers show lower performance than type 3 and type 4 drivers. Type 3: Net-protocol/all-Java driver JDBC driver type 3 -- the net-protocol/all-Java driver -- follows a three-tiered approach whereby the JDBC database requests are passed through the network to the middle-tier server. The middle-tier server then translates the request (directly or indirectly) to the database-specific native-connectivity interface to further the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or type 2 JDBC driver to do this. Type 3: Net-protocol/all-Java driver Functions: 1. Follows a three tier communication approach. 2. Can interface to multiple databases - Not vendor specific. 3. The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database. 4. Thus the client driver to middleware communication is database independent. 5. Client -> JDBC Driver -> Middleware-Net Server -> Any Database Pros The net-protocol/all-Java driver is server-based, so there is no need for any vendor database library to be present on client machines. Further, there are many opportunities to optimize portability, performance, and scalability. Moreover, the net protocol can be designed to make the client JDBC driver very small and fast to load. Additionally, a type 3 driver typically provides support for features such as caching (connections, query results, and so on), load balancing, and advanced system administration such as logging and auditing. Cons Type 3 drivers require database-specific coding to be done in the middle tier. Additionally, traversing the recordset may take longer, since the data comes through the backend server. Type 4: Native-protocol/all-Java driver The native-protocol/all-Java driver (JDBC driver type 4) converts JDBC calls into the vendor-specific database management system (DBMS) protocol so that client applications can communicate directly with the database server. Level 4 drivers are completely implemented in Java to achieve platform independence and eliminate deployment administration issues. Type 4: Native-protocol/all-Java driver Functions 1. Type 4 drivers are entirely written in Java that communicate directly with a vendor's database through socket connections. No translation or middleware layers, are required, improving performance. 2. The driver converts JDBC calls into the vendor-specific database protocol so that client applications can communicate directly with the database server. 3. Completely implemented in Java to achieve platform independence. 4. e.g include the widely used Oracle thin driver - oracle.jdbc.driver. OracleDriver which connect to jdbc:oracle:thin URL format. 5. Client Machine -> Native protocol JDBC Driver -> Database server Pros Since type 4 JDBC drivers don't have to translate database requests to ODBC or a native connectivity interface or to pass the request on to another server, performance is typically quite good. Moreover, the native-protocol/all-Java driver boasts better performance than types 1 and 2. Also, there's no need to install special software on the client or server. Further, these drivers can be downloaded dynamically. Cons With type 4 drivers, the user needs a different driver for each database. All 4 Types of Driver:- Types Of Drivers As shown above there are 4 types of JDBC drivers available. Using JDBC API Introduction When we need to use database drivers and the JDBC API while developing a Java application to retrieve or store data in database. The JDBC API classes and interfaces are available in the java.sql and the javax.sql package. The classes and interfaces perform a number of tasks, such as establish and close a connection with a database, send a request to a database, retrieve data from database, and update data in the database. The commonly used classes and interfaces in the JDBC API are: 1. DriverManager class: It Load the driver for a database. 2. Driver interface: It represents a database driver. All JDBC driver classes must implement the Driver interface. 3. Connection interface: It enables to establish a connection between a Java application and a database. 4. Statement interface: It enables to execute SQL statements. 5. ResultSet interface: It represents the information retrieve from a database. 6. SQLException class: It provides information about the exception that occurs while interacting with database. To query a database and display the result using Java applications, we need to: a. Load a Driver b. Connect to a database c. Create and execute JDBC statements d. Handle SQL exceptions Using JDBC API Loading a Driver The first step to develop a JDBC application is to load and register the required driver using the driver manager. We can load and register a driver: 1. Using the forName() method 2. Using the registerDriver() method 3. By setting system property Using the forName ( ) Method The forName ( ) method is available in the java.lang.Class class. The forName ( ) method loads the JDBC driver and registers the driver with the driver manager. The syntax to load a JDBC driver to ACCESS a database is: Class.forName(“”); Ex: Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); Using the registerDriver ( ) Method Here we can create an instance of the Driver class to load a JDBC driver. The syntax to declare an instance of the Driver class is: Driver d =new ; Ex: Driver d = new sun.jdbc.odbc.JdbcOdbcDriver ( ); Once we have created the Driver object, call the registerDriver ( ) method to register it with the DriverManager. We can register the JDBC-ODBC Bridge driver using the following method call to registerDriver ( ) method: DriverManager.registerDriver(d); Setting System Property Driver can also be loaded by setting System property for JDBC drivers. We add the driver name to the jdbc.driver System property to load a JDBC driver. We use the –D commandline option to set the system property on the command line. The command to set the system property is: Java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver SampleApplication In the preceding command, jdbc.driver is the property name andsun.jdbc.odbc.JdbcOdbcDriver is the value that we need to set for the property. After we load the driver, we need to establish the connection with the database. Using JDBC API Connecting to a Database We create an object of the Connection interface to establish a connection of the Java application with a database. We can create multiple Connection objects in a Java application to access and retrieve data from multiple databases. The DriverManager class provides the getConnection ( ) method to create a Connection object. The getConnection ( ) method is an overloaded method that has following three forms: 1. Connection getConnection(String ): Accept the JDBC URL of the database, which we need tom access , as a parameter. String url= “jdbc:odbc:MyDSN”; Connection con =DriverManager.getConnection(url); The syntax for a JDBC URL that is passed as a parameter to the getConnection ( ) method is: <protocol>:<subprotocal>:<subname> A JDBC URL has the following three components: Protocol name: It indicate the name of the protocol that is used to access a database. In JDBC the name of the access protocol is always jdbc. Sub-protocol name: It indicate the mechanism to retrieve data from a database. For example if we use the JDBC-ODBC Bridge driver to access a database, then the name of sub-protocol is odbc. Subname: It indicates the Data Source Name(DSN) that contain databases information, such as the name of a database, location of the database server, user name and password to access a database server. 2. Connection getConnection(String , String , String ): Ex: String url= “jdbc:odbc:MyDSN”; Driver d =new ; Connection con =DriverManager.getConnection(url, “ NewUser”, “NewPassword”); 3. Connection getConnection(String , Properties): Ex: String url= “jdbc:odbc:MyDSN”; Properties p new Properties( ); p.setProperty(“user”,”NewUser”); p.setProperty(“password”,”newPassword”); Connection con =DriverManager.getConnection(url, p); Using JDBC API Creating and Executing JDBC Statements We need to create a Statement object to send request and retrieve result from a database. The Connection object provide the createStatement ( ) method to create a Statement object. We can use the following code to create a Statement object: Connection con =DriverManager.getConnection (“jdbc:odbc:MyDSN”, “NewUser”, “NewPassword”); Statement stmt= con.createStatement ( ); We can use the static SQL statement to send request to a database. Here we can use the send the SQL statement to a database using the Statement object. The Statement interface contain the following methods to send static SQL statement to the database:----- 1. ResultSet executeQuery(String str):-- It execute an SQL statement and returns a single object of the type, ResultSet. The syntax to use the executeQuery ( ) method is : Statement stmt = con.createStatement ( ); ResultSet rs = stmt.executeQuery(<SQL statement>); 2. int executeUpdate (String str):-- It execute the SQL statements and returns the number of data rows that are affected after processing the SQL statement. The executeUpdate ( ) can be used when we need to INSERT, DELETE, and UPDATE the data. Statement stmt = con.createStatement ( ); ResultSet rs = stmt.execute(<SQL statement>); 3. Boolean execute(String str): This method execute an SQL statement and return a Boolean Statement stmt = con.createStatement ( ); stmt.execute(<SQL statement>); Using JDBC API Handling SQL Exceptions The java.sql package provide the SQLException class, which is derived from the java.lang.Exception class. The SQLException class provides the following error information: 1. Error Message: It is a string that describes the error. 2. Error code: It is an integer value that is associated with the error. 3. SQL state: It is an X/OPEN error code that identify the error. The SQLException class contains various methods that provide error information. The methods in the SQLException class are: a. int getErrorCode(); It return the error code that associated with the error occurred. b. String getSQLState(): It returns X/OPEN error code. c. SQLException getNextException(): It returns the next exception in the chain of the exceptions. Example: Try { String str=”DELETE FROM tablename WHERE au_id=’2323244’”; Statement stmt=con.createStatement(); int count=stmt.executeUpdate(str); } catch(SQLException sqlExceptionobject) { System.out.println(“Display error code”); System.out.println(“SQL Exception” +sqlExceptionobject.getErrorCode()); } Here if the DELETE statement at runtime throws an SQLException then it is handling using the try catch block. The sqlexceptionObject is an object of the SQLException class and is used to invoke the getErrorCode() method. JDBC Steps – Basic steps in writing a JDBC Application Here you get a brief description of JDBC Steps for making connection with the database, executing the query and showing the data to the user. In this application we have connected to the MySQL database and retrieved the employee names from the database. Here are the JDBC Steps to be followed while writing JDBC program: • Loading Driver • Establishing Connection • Executing Statements • Getting Results • Closing Database Connection Before explaining you the JDBC Steps for making connection to the database and retrieving the employee from the tables, we will provide you the structure of the database and sample data. Here is the sql script to create table and populate the table with data: -- Table structure for table `employee` CREATE TABLE `employee` ( `employee_name` varchar(50) NOT NULL, PRIMARY KEY (`employee_name`) ); INSERT INTO `employee` (`employee_name`) VALUES ('Deepak Kumar'), ('Harish Joshi'), ('Rinku roy'), ('Vinod Kumar'); Data inserting in MySQL database table: mysql> insert into employee values('Deepak Kumar'); Query OK, 1 row affected (0.24 sec) mysql> insert into employee values('Harish Joshi'); Query OK, 1 row affected (0.05 sec) mysql> insert into employee values('Harish Joshi'); ERROR 1062 (23000): Duplicate entry 'Harish Joshi' for key 1 mysql> insert into employee values('Rinku roy'); Query OK, 1 row affected (0.03 sec) mysql> insert into employee values('Vinod Kumar'); Query OK, 1 row affected (0.04 sec) mysql> select *from employee; +---------------+ | employee_name | +---------------+ | Deepak Kumar | | Harish Joshi | | Rinku roy | | Vinod Kumar | +---------------+ 4 rows in set (0.04 sec) Here is the code of java program that retrieves all the employee data from database and displays on the console: /* Import JDBC core packages. Following statement imports the java.sql package, which contains the JDBC core API. */ import java.sql.*; public class RetriveAllEmployees{ public static void main(String[] args) { System.out.println("Getting All Rows from employee table!"); Connection con = null; String url = "jdbc:mysql://localhost:3306/"; String db = "jdbc"; String driver = "com.mysql.jdbc.Driver"; String user = "root"; String pass = "root"; try{ Class.forName(driver); con = DriverManager.getConnection(url+db, user, pass); Statement st = con.createStatement(); ResultSet res = st.executeQuery("SELECT * FROM employee"); System.out.println("Employee Name: " ); while (res.next()) { String employeeName = res.getString("employee_name"); System.out.println(employeeName ); } con.close(); } catch (ClassNotFoundException e){ System.err.println("Could not load JDBC driver"); System.out.println("Exception: " + e); e.printStackTrace(); } catch(SQLException ex){ System.err.println("SQLException information"); while(ex!=null) { System.err.println ("Error msg: " + ex.getMessage()); System.err.println ("SQLSTATE: " + ex.getSQLState()); System.err.println ("Error code: " + ex.getErrorCode()); ex.printStackTrace(); ex = ex.getNextException(); // For drivers that support chained exceptions } } } } Explanation of JDBC Steps: • Loading Driver Loading Database driver is very first step towards making JDBC connectivity with the database. It is necessary to load the JDBC drivers before attempting to connect to the database. The JDBC drivers automatically register themselves with the JDBC system when loaded. Here is the code for loading the JDBC driver: Class.forName(driver).newInstance(); • Establishing Connection In the above step we have loaded the database driver to be used. Now its time to make the connection with the database server. In the Establishing Connection step we will logon to the database with user name and password. Following code we have used to make the connection with the database: con = DriverManager.getConnection(url+db, user, pass); • Executing Statements In the previous step we established the connection with the database, now its time to execute query against database. You can run any type of query against database to perform database operations. In this example we will select all the rows from employee table. Here is the code that actually execute the statements against database: ResultSet res = st.executeQuery( "SELECT * FROM employee" ); • Getting Results In this step we receives the result of execute statement. In this case we will fetch the employees records from the recordset object and show on the console. Here is the code: while (res.next()) { String employeeName = res.getInt( " employee_name " ); System.out.println( employeeName ); } • Closing Database Connection Finally it is necessary to disconnect from the database and release resources being used. If you don’t close the connection then in the production environment your application will fail due to hanging database connections. Here is the code for disconnecting the application from database: con.close(); In this section you learnt about the JDBC Steps necessary for performing database operations. Output of program: C:\vinod>javac RetriveAllEmployees.java C:\vinod>java RetriveAllEmployees Getting All Rows from employee table! Employee Name: Deepak Kumar Harish Joshi Rinku roy Vinod Kumar ACCESSING RESULT SETS Introduction When we execute a query to retrieve data from a table using java application, the output of the query is stored in ResultSet object in a tabular format. A ResultSet object maintains a cursor that enables us to move through the rows stored in the ResultSet object. By default the ResultSet object maintains a cursor that moves in the forward direction only. As a result it move from first row to last row in the ResultSet. We can not update theResultSet object. The cursor in the ResultSet object initially point before the first row. ACCESSING RESULT SETS Types of Result Sets We can create various type of ResultSet objects tostore the output returned by a database after executing SQL statements. Various types of ResultSet objects are: a. Read only: It allow us to only read the row in a ResultSet object. b. Forward only: It allow us to move the ResultSet cursor from first row to last row in forward direction only. c. Scrollable: It allow us to move the ResultSet cursor forward or backward through the result set. d. Updatable: It allow us to update the result set rows retrieve from the database. The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object. The sensitivity of the ResultSet object is determined by one of three different ResultSet types: • TYPE_FORWARD_ONLY — the result set is not scrollable i.e. the cursor moves only forward, from before the first row to after the last row. • TYPE_SCROLL_INSENSITIVE — the result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. • TYPE_SCROLL_SENSITIVE — the result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object: Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery("....."); The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that isTYPE_FORWARD_ONLY and CONCUR_READ_ONLY. Using the ResultSet Methods When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods: • next() - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row. • previous() - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row. • first() - moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows. • last() - moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows. • beforeFirst() - positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect. • afterLast() - positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect. • relative(int rows) - moves the cursor relative to its current position. • absolute(int row) - positions the cursor on the row-th row of the ResultSet object. Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom. ACCESSING RESULT SETS Methods of ResultSet Interface The ResultSet interface provides access to a table of data. A ResultSet object is usually generated by executing a Statement. A ResultSet maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. The next() method moves the cursor to the next row. The getXXX methods retrieve column values for the current row. You can retrieve values using either the index number of the column or the name of the column. In general, using the column index is more efficient. Columns are numbered from one. The JDBC driverconverts the underlying data to the Java™ type specified in the getter method and returns a suitable Java value. java.sql package public interface ResultSet ResultSet interface fields Field type Field static int CONCUR_READ_ONLY The constant indicating the concurrency mode for a ResultSet object that can NOT be updated. static int TYPE_FORWARD_ONLY The constant indicating the type for a ResultSet object whose cursor can move only forward. static int TYPE_SCROLL_INSENSITIVE The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes made by others. static int TYPE_SCROLL_SENSITIVE The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes made by others. boolean absolute(int row) JDBC 2.0. Moves the cursor to the given row number in the result set. void afterLast() JDBC 2.0. Moves the cursor to the end of the result set, just after the last row. void beforeFirst() JDBC 2.0. Moves the cursor to the front of the result set, just before the first row. void clearWarnings() Clears all warnings reported on this ResultSet object. void close() Releases this ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. int findColumn(String columnName) Maps the given ResultSet column name to its ResultSet column index. boolean first() JDBC 2.0. Moves the cursor to the first row in the result set. BigDecimal getBigDecimal(int columnIndex) JDBC 2.0. Gets the value of a column in the current row as a java.math.BigDecimal object with full precision. This method is not supported by the DB2 Everyplace JDBC driver for Palm OS. BigDecimal getBigDecimal(int columnIndex, int scale) Gets the value of the designated column in the current row of this ResultSet object as a java.math.BigDecimal object in the Java programming language. This method is not supported by the DB2 Everyplace JDBC driver for Palm OS. Deprecated. BigDecimal getBigDecimal(String columnName) JDBC 2.0. Gets the value of a column in the current row as a java.math.BigDecimal object with full precision. This method is not supported by the DB2 Everyplace JDBC driver for Palm OS. BigDecimal getBigDecimal(String columnName, int scale) Gets the value of the designated column in the current row of this ResultSet object as a java.math.BigDecimal object in the Java programming language. This method is not supported by the DB2 Everyplace JDBC driver for Palm OS. Deprecated. InputStream getBinaryStream(int columnIndex) InputStream getBinaryStream(String columnName) Blob getBlob(int columnIndex) JDBC 2.0. Gets a BLOB value in the current row of this ResultSet object. Blob getBlob(String columnName) JDBC 2.0. Gets a BLOB value in the current row of this ResultSet object. boolean getBoolean(int columnIndex) Gets the value of a column in the current row as a Java boolean. The driver first gets the value of the column as a Java short. If the value is equal to 1, true is returned. Otherwise, false is returned. boolean getBoolean(String columnName) Gets the value of a column in the current row as a Java boolean. The driver first gets the value of the column as a Java short. If the value is equal to 1, true is returned. Otherwise, false is returned. byte getByte(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language. byte getByte(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language. byte[] getBytes(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. byte[] getBytes(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. int getConcurrency() JDBC 2.0. Returns the concurrency mode of the result set. Date getDate(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a java.sql.Date object in the Java programming language. Date getDate(int columnIndex, Calendar cal) Returns the value of the designated column in the current row of this ResultSet object as a java.sql.Date object in the Java programming language. Date getDate(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a java.sql.Date object in the Java programming language. double getDouble(int columnIndex) Gets the value of a column in the current row as a Java double. double getDouble(String columnName) Gets the value of a column in the current row as a Java double. float getFloat(int columnIndex) getFloat(int columnIndex) Gets the value of a column in the current row as a Java float. float getFloat(String columnName) Gets the value of a column in the current row as a Java float. int getInt(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as an integer in the Java programming language. int getInt(String columnName) Gets the value of the designated column in the current row of this ResultSet object as an integer in the Java programming language. long getLong(int columnIndex) Gets the value of a column in the current row as a Java long. long getLong(String columnName) Gets the value of a column in the current row as a Java long. ResultSetMetaData getMetaData() Retrieves the number, types, and properties of this ResultSet object's columns. Object getObject(int columnIndex) Gets the value of a column in the current row as a Java object. Object getObject(String columnName) Gets the value of a column in the current row as a Java object. int getRow() JDBC 2.0. Retrieves the current row number. short getShort(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a short in the Java programming language. short getShort(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a short in the Java programming language. Statement getStatement() JDBC 2.0. Returns the Statement that produced this ResultSet object. String getString(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a String in the Java programming language. String getString(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a String in the Java programming language. Time getTime(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a java.sql.Time object in the Java programming language. Time getTime(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a java.sql.Time object in the Java programming language. Timestamp getTimestamp(String columnName) Gets the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language. Timestamp; getTimestamp(int columnIndex) Gets the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language. int getType() JDBC 2.0. Returns the type of this result set. SQLWarning getWarnings() The first warning reported by calls on this ResultSet is returned. boolean isAfterLast() JDBC 2.0. Indicates whether the cursor is after the last row in the result set. boolean isBeforeFirst() JDBC 2.0. Indicates whether the cursor is before the first row in the result set. boolean isFirst() JDBC 2.0. Indicates whether the cursor is on the first row of the result set. boolean isLast() JDBC 2.0. Indicates whether the cursor is on the last row of the result set. This method is not supported for result sets with type TYPE_FORWARD_ONLY. Boolean last() JDBC 2.0. Moves the cursor to the last row in the result set. boolean next() Moves the cursor down one row from its current position. boolean previous() JDBC 2.0. Moves the cursor to the previous row in the result set. boolean relative(int rows) JDBC 2.0. Moves the cursor a relative number of rows, either positive or negative. boolean wasNull() Reports whether the last column read had a value of SQL NULL.