My Java Camp: JDBC

Introduction to JDBC
JDBC is a Java API (Application Programming Interface) that documents a standard framework for dealing with tabular and, generally, relational data. While JDBC 2.0 begins a move to make SQL semi-transparent to the programmer, SQL is still the lingua franca of the standard database engines and represents a major industry victory in the effort to separate data from code
SQL
SQL is a standardized language used to create, manipulate, examine, and manage
relational databases.
However, you must connect to a database before sending SQL commands, and each database vendor has a different interface to do so, as well as different extensions of SQL. Enter ODBC.
ODBC
ODBC (Open Database Connectivity), a C-based interface to SQL-based database engines, provides a consistent interface for communicating with a database and for accessing database metadata (information about the database system vendor, how the data is stored, and so on). Individual vendors provide specific drivers or "bridges" to their particular database management system.
Consequently, thanks to ODBC and SQL, you can connect to a database and manipulate it in a standard way. It is no surprise that, although ODBC began as a PC standard, it has become nearly an industry standard.
The Java Programming Language and JDBC
A Java program,written properly and according to specification, can run on any Java technology-enabled platform without recompilation. The Java programming language is completely specified and, by definition, a Java technology-enabled platform must support a known core of libraries. One such library is the java.sql package or JDBC, which you can think of as a portable version of ODBC, and is itself a major standard. Using the Java programming language in conjunction with JDBC provides a truly portable solution to writing database applications.
A JDBC driver is a class that implements the JDBC Driver interface and understands how to convert program (and typically SQL) requests for a particular database. Clearly, the driver is what makes it all work. There are four different driver types. 
Type 1 driver:
A bridge driver, allows JDBC to communicate with any database that uses ODBC. Nowadays bridge driver is not recommended beyond testing purposes.
Type 2 driver:
A native API driver, converts JDBC calls into native API calls unique to the database. Typically provided by the database vendor. 
Type 3 driver:
A JDBC – Net driver, converts JDBC calls into a database independent net protocol, which is then translated into the database specific calls. This driver is advantages because it does not require anything of client, and the same driver can be used for multiple databases.   
Type 4 driver:
A native protocol driver: JDBC calls are converted directly into native calls to the database. They are pure java drivers that do not require native code on the client side. The JDBC drivers provided by the database vendor are typically type 4 drivers. 
This uses type 4 drivers because of their nearly zero installation requirements and dynamic nature. Another driver type may make more sense for your particular project. Most database vendors now provide drivers to implement the JDBC API for their particular systems. These are generally provided free of charge. Third party drivers are also available, ranging in cost from free to very expensive. 
Establishing a Connection
First, you need to establish a connection with the DBMS you want to use. Typically,a JDBC™ application connects to a target data source using one of two mechanisms:  
DriverManager:   This fully implemented class requires an application to load a specific driver, using a hardcoded URL.As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications. 
  
DataSource:   This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.
Establishing a connection involves two steps: Loading the driver, and making the connection. 

Loading the Driver

Loading the driver you want to use is very simple. It involves just one line of code in your program. To use the mysql driver, add the following line of code:


Class.forName("com.mysql.jdbc.Driver");

Your driver documentation provides the class name to use. 

Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. If you were to create your own instance, you would be creating an unnecessary duplicate, but it would do no harm.

After you have loaded a driver, it can make a connection with a DBMS. 

Making the Connection
The second step in establishing a connection is to have the appropriate driver connect to the DBMS. 
Using the DriverManager Class
The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source. Connection URLs have the following form: 


jdbc:mysql://<host name>:<db port><dbName>
eg. "jdbc:mysql://localhost:3306/test"


The dbName portion of the URL identifies a specific database. A database can be in one of many locations: in the current working directory, on the classpath, in a JAR file, in a specific Java DB database home directory, or in an absolute location on your file system.

If you are using a vendor-specific driver, such as Oracle, the documentation will tell you what subprotocol to use, that is, what to put after jdbc: in the JDBC URL. For example, if the driver developer has registered the name OracleDriver as the subprotocol, the first and second parts of the JDBC URL will be jdbc.driver.OracleDriver . The driver documentation will also give you guidelines for the rest of the JDBC URL. This last part of the JDBC URL supplies information for identifying the data source.

The getConnection method establishes a connection: 
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",”username”,”password”);


In place of "username" you insert the name you use to log in to the DBMS; in place of “Password“ you insert your password for the DBMS. So, if you log in to your DBMS with a login name of “Fernando " and a password of " J8, " just these two lines of code will establish a connection:


String url = " jdbc:mysql://localhost:3306/test ";
Connection con = DriverManager.getConnection(url, "Fernando", "J8");

If one of the drivers you loaded recognizes the JDBC URL supplied to the method DriverManager.getConnection, that driver establishes a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are
writing a driver, you probably won't use any of the methods in the interface Driver, and the only DriverManager method you really need to know is DriverManager.getConnection 

The connection returned by the method DriverManager.getConnection is an open connection you can use to create JDBC statements that pass your SQL statements to the DBMS. In the previous example, con is an open connection, and you use it in the examples that follow.

Retrieving Values from Result Sets

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. A table of data representing a database result set is usually generated by executing a statement that queries the database. 

Now, you'll see how to send the above SELECT statements from a program written in the Java™ programming language and how you get the results we showed.

JDBC™ returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. In addition, the Statement methods executeQuery and getResultSet both return a ResultSet object, as do various DatabaseMetaData methods. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it by using the executeQuery method.

Statement stmt = con.createStatement();
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
Using the ResultSet Methods
The variable srs, which is an instance of ResultSet, contains the rows of coffees and prices shown in the result set example above. In order to access the names and prices. A ResultSet object maintains a cursor, which points to its current row of data.

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. 


Example:

import java.awt.*;
import javax.swing.*;
import java.awt.event.*;import java.sql.*;
public class DBUI {
    static JButton addbtn,deletebtn,updatebtn,viewbtn;static JTextField eidtxt,enametxt,agetxt;
   showGUI();
    }
public static void main(String[] arg){
  
 static void showGUI() {
   JFrame f = new JFrame("Welcome");JPanel p = new JPanel();
t(7,3));
   p.add(new JLabel("Employp.setLayout(new GridLayo
uee ID"));
   eidtxt = new JTextField(10);
   p.add(eidtxt);
tField(10);
   p.add(enametxt);
   p.ap.add(new JLabel("Employee Name"));
   enametxt = new JTe
xdd(new JLabel("Employee Age"));
   agetxt = new JTextField(10);
   p.add(agetxt);
etebtn = new JButton("DELETE");addbtn = new JButton("ADD");
   addbtn.addActionListener(new DBEvents());
   de
l
   deletebtn.addActionListener(new DBEvents());
   updatebtn = new JButton("UPDATE");
wbtn.addActionListener(new DBEvents());

   p.aupdatebtn.addActionListener(new DBEvents());
   viewbtn = new JButton("VIEW");
   vi
edd(addbtn);
   p.add(deletebtn);
   p.add(updatebtn);
   p.add(viewbtn);
   
   f.add(p);
   f.setSize(300,200);

f.setVisible(true);
   }
}
class DBEvents extends DBUI implements ActionListener{
  Connection con;
public void actionPerformed(ActionEvent e){   if(e.getSource()==addbtn){try{
       add();
oundException ex)
      {
      JOption}catch(ClassNot
FPane.showMessageDialog(null,""+ex.getMessage(),"Message",0);
Dialog(null,""+ex.getMessage()}catch(SQLException ex){
        JOptionPane.showMessag
e,"Message",0);
        }
      }
   if(e.getSource()==deletebtn){
   try{
      delete();
,""+ex.getMessage(),"Message",0);
     }catch(ClassNotFoundException ex)
       {
        JOptionPane.showMessageDialog(nul
l  }catch(SQLException ex){
        JOptionPane.showMessageDialog(null,""+ex.getMessage(),"Message",0);
       }
     }
ane.showMessageDialog(null,""+exif(e.getSource()==updatebtn){
   try{
      update();
      }catch(ClassNotFoundException ex)
      {
       JOption
P.getMessage(),"Message",0);
      }catch(SQLException ex){
       JOptionPane.showMessageDialog(null,""+ex.getMessage(),"Message",0);
      }
      }
Message(),"Message",0);
      if(e.getSource()==viewbtn){
   try{
      view();
      }catch(ClassNotFoundException ex)
      {
      JOptionPane.showMessageDialog(null,""+ex.ge
t}catch(SQLException ex){
      JOptionPane.showMessageDialog(null,""+ex.getMessage(),"Message",0);
      }

   }
  }
public void add()throws ClassNotFoundException, SQLException{
    int eid=Integer.parseInt(eidtxt.getText());    String ename = enametxt.getText();
Text());
    connectDB();
    String ins = "ins    int age=Integer.parseInt(agetxt.ge
tert into emp values(?,?,?)";
    PreparedStatement pst=con.prepareStatement(ins);
execute();
    JOptionP    pst.setInt(1, eid);
    pst.setString(2,ename);
    pst.setInt(3,age);
    pst
.ane.showMessageDialog(null,"Record Inserted ","Message",1);

}
public void delete()throws ClassNotFoundException, SQLException{
   int eid=Integer.parseInt(eidtxt.getText());   connectDB();
delete from emp where eid="+eid;
   Statement st=   String del = 
"con.createStatement();
   st.executeUpdate(del);
ted ","Message",1);

}
   JOptionPane.showMessageDialog(null,"Record Del
epublic void update()throws ClassNotFoundException, SQLException{
  int eid=Integer.parseInt(eidtxt.getText());
    String ename = enametxt.getText();
ext());
   connectDB();

   String up = "updat   int age=Integer.parseInt(agetxt.get
Te emp set ename='"+ename+"',age="+age+" where eid="+eid;
(up);
   JOptionPane.showMessageDialog   Statement st=con.createStatement();
   st.executeUpdat
e(null,"Record Updated ","Message",1);

}
public void view()throws ClassNotFoundException, SQLException{
  int eid=Integer.parseInt(eidtxt.getText());
 
   connectDB();

LECT * FROM emp where eid="+eid;
   Statement st=co   String sel = "S
En.createStatement();
   ResultSet rs=st.executeQuery(sel);
t.setText(""+   rs.next();
   enametxt.setText(rs.getString(2));
   aget
xrs.getInt(3));

}
public void connectDB(){
   String driver="sun.jdbc.odbc.JdbcOdbcDriver";   String url="jdbc:odbc:BooksDSN";
   try{
assNotFoundException e){
         Class.forName(driver);
      }catch(C
l    JOptionPane.showMessageDialog(null,""+e.getMessage(),"Message",0);
ch(SQ    }
   try{
       con = DriverManager.getConnection(url);

      }ca
tLException e){
ane.showMessageDialog(null,""+e.getMessage(),"Message",0);
        }

            JOption
P}


}

Lab Assignment 01

Write a Java program to manage a class database. The class database takes as input a sequence of records containing students name (String), social security number (int), and a letter grade (char). Define this as an object. The main method reads and adds records until the user indicates that he is done with the input. The user inputs "done" as the user name and -1 as the social security number. The grade corresponding to this last record is ignored and this record is not added. The student records are stored in a database. The database itself should support methods: NumberOfEntries, Print, Add and Delete. The user is then presented with a menu of options that allow him to examine the number of entries, print the records, add a record, delete a record, or exit the program. The functions of each of these menu items is self explanatory.

A sample session is as follows:

Record 1:

Enter Student Name: BRAD JOHNSON

Enter ssn: 12345678 Enter Grade: C Record 2: Enter Student Name: CHRIS CARTER Enter ssn: 23456781 Enter Grade: A Record 3: Enter Student Name: JAKE REED Enter ssn: 23456718 Enter Grade: A Record 4: Enter Student Name: done Enter ssn: -1 Enter Grade: A

** Three records inserted into the database: Please select from the following list of options:

1. Print the Records