JDBC [Java Database Connectivity]
Introduction to Databases:
A database is an organized collection of related data. Traditional file systems are inefficient in retrieving and managing data. A database server helps in organizing the data efficiently and retrieving it. Examples of database servers are DB2, mysql, Sybase etc.
Let us see how to work with mysql database. (Install mysql —an open source software)
Generally, in any Relational database, data is stored in the form of a table. For example, if the student id, name, total and cgpa details of 10000 students have to be stored, they are stored in a table. The details of one student form a single row (termed tuple). So, for the example stated above, the table will be containing 10000 rows (tuples). The 4 fields that describe the student (id, name, total and cgpa) constitute the columns of the table and called as attributes.
In mysql, a database has to be created first, inside which any number of tables can be constructed. Operations to be performed on a relational database are expressed in a language that was specifically designed for this purpose, the SQL (Structured Query Language).
1. Creating a database: create database databasename;
Eg., create database university;
2. To work inside the newly created database: use databasename;
Eg., use university;
3. Creating a table: create table tablename(attribute1, datatype, attr2 datatype, attr3 datatype);
Eg., create table student(studid int(3), studname varchar(20), total int(3), cgpa decimal(3,1));
4. Inserting values in to the table: insert into tablename values(value1, value2, value3,value);
Eg., insert into student values(1,"Anu",89,9.3); insert into student values(2,"Ajay", 92, 9.5);
5. A) Retrieving the data from the table: select * from tablename;
Eg., select * from student;
6. B) Retrieving the data based on condition: select * from tablename where condition;
Eg: select * from student where studid=2;
A Java application can be made to work with these databases. A Java program can be written to manipulate the tables that we have created. Eg., Another row of student details can be inserted or the table contents can be displayed through the Java code.
JDBC (Java Database Connectivity)
- A library consisting of a set of classes and interfaces that help for establishing and maintaining the connection between the Database and the Java application program. Once the connection is established, SQL (Structured Query Language) can be used to access and process the contents of the database.
- These classes and interfaces (which constitute the JDBC package. So, import this.
Steps to follow:
Load the appropriate Driver
JDBC application program does not have to be modified for connecting to various types of Database servers.
Then, how does JDBC manage to communicate with any type of database server?
By implementing the JDBC interface for specific databases which are called Drivers. A specific Driver is available for each database. (i.e., individual drivers exist for mysql, Oracle, DB2 and other DB servers)
To load the appropriate driver explicitly, the static forName() method in the predefined class named 'Class' can be called by passing the driver class name as the String argument. Class.forName("com.mysqljdbc.Driver"); // this is the driver for my mysql database.
This method can throw an exception of type ClassNotFoundException, if the driver class cannot be found. So, monitor it inside a try block and handle it.
Before executing any SQL statements, a Connection object must first be created, which represents an established connection (session) to a particular data source.
Creating a Connection object:
The DriverManager class helps in this regard.
Defines the URL that identifies where the database is located
A)When the getConnection() method of DriverManager class is called, the DriverIVIanager iterates through the various drivers that are registered with the DriverManager and asks each one in turn, if it can handle the URL that has been passed to it.
B) The first driver that can handle the URL, passed to it, creates a Connection object and returns it to the application by way of DriverManager.
Note: The Java application need not implement code to interact with the various driver objects individually. The DriverManager class takes care of communicating with all the drivers.This method throws SQLException which has to be handled or forwarded. So, include this statement inside a try-catch block.
Creation of Statement object which provides the workspace to create an SQL query execute it and retrieve any results that are returned.
- Created by calling the createStatementO method of a valid Connection object.
- Statement s=c.createStatement();
The results of executing an SQL Query are returned in the form of an object that implements the Result Set interface.
ResultSet rs=s.executeQuery("select * from student");
This object contains a cursor that can be manipulated to refer to any particular row in the result set. It Th an initially points to a position immediately preceding the first row. Calling the next() method for the ResultSet object will move the cursor to the next row.
The next() method returns true if the move is to a valid row and false if there are no more rows(i.e., has reached the end)
Accessing data in a ResultSet:
Using the ResultSet reference, the value of any column for the current row can be retrieved by name or by position. The class implementing the ResultSet interface contains methods to read all types of data. -getBoolean(), getByte(), getDouble(), getFloat(), getlnt(), getShort(), getLong(), getString().
Eg., String sid=rs.getString("studid");