Webucator blogs about online learning and training.

I was asked to go into Java’s database API (the JDBC) using the open source database, MySQL. The JDBC is included in the Java SE and the Java EE distributions in the java.sql package. It’s designed to hide the specifics of a RDBMS from business logic. In this article I’ll cover the basics of the JDBC and what it requires to use MySQL.

Before we get started we need to install the software:

  1. Of course you will need a current revision of the JDK or Java EE installed on your computer.
  2. Download MySQL and install it. The MySQL download for Windows comes in two types: as an Installer, and as a simple ZIP file. I use the ZIP version, mysql-noinstall-5.1.48-win32.zip. Other OS platforms will require matching versions of MySQL and the installation procedures may vary.
  3. Download the MySQL jConnector. I used mysql-connector-java-5.1.13.zip. Unzip the archive and locate the mysql-connector-java-5.1.13-bin.jar file. You’ll need to point your CLASSPATH to this JAR. It contains the MySQL driver, com.mysql.jdbc.Driver.

The mysql text console utility in the bin directory of MySQL can be used to execute SQL statements. See the documentation for this utility for it’s commands and login requirements. In this example, I used the following SQL statements to establish a database and user:

create database test;
use test;

create table person (
id integer,
name varchar(30),
job_id integer,
location varchar(30)
);

grant all on test.* to roger@localhost identified by 'toast';

I populated the test database with the following statements:

insert into person (id, name, job_id, location) 
	values (1, 'Bill Smith', 1183, 'OH');
insert into person (id, name, job_id, location) 
	values (2, 'Sue Jones', 529, 'NJ');
insert into person (id, name, job_id, location) 
	values (3, 'Tom Swift', 84443, 'FL');
insert into person (id, name, job_id, location) 
	values (4, 'Bob the Builder', 34693, 'MA');
insert into person (id, name, job_id, location) 
	values (5, 'Homer Simpson', 39, 'NE');
insert into person (id, name, job_id, location) 
	values (7, 'Margo Lane', 2222, 'TX');
insert into person (id, name, job_id, location) 
	values (8, 'Bud Abbot', 5202, 'RI');

It’s not much to look at, but it does give us something to work against.

The JDBC defines five classes that our example will use:

  • java.sql.DriverManager: serves as a factory for creating Connection objects.
  • java.sql.Connection: fosters the database connection and serves as a factory for creating Statement objects.
  • java.sql.Statement: represents the SQL statement to be executed by the database.
  • java.sql.ResultSet: represents the rows and fields obtained through a SQL select statement.
  • java.sql.SQLException: all JDBC operations require this exception to be caught.

The following code serves as my Data Access Object (DAO). The block comments explain how it works:

package com.webucator.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DAO {

   /* These variable values are used to setup
      the Connection object */

   static final String URL = "jdbc:mysql://localhost:3306/test";
   static final String USER = "roger";
   static final String PASSWORD = "toast";
   static final String DRIVER = "com.mysql.jdbc.Driver";

   /* This method is used to create a connection using 
      the values listed above. Notice the throws clause 
      in the method signature. This allows the calling method 
      to deal with the exception rather than catching it in 
      both places. The ClassNotFoundException must be caught 
      because the forName method requires it. */

   public Connection getConnection() throws SQLException {
      Connection con = null;
      try {
         Class.forName(DRIVER); 
         con = DriverManager.getConnection(URL, USER, PASSWORD);
      }
      catch(ClassNotFoundException e) {
         System.out.println(e.getMessage());
         System.exit(-1);
      }
      return con;
   }

   /* This method does most of the work. Note the try 
      and catch. Virtually all JDBC methods throw a 
      SQLException that must be tended to. The Connection 
      object is used to create a Statement object. 
      The executeQuery method is used to submit a 
      SELECT SQL query. The executeUpdate method can be 
      used to delete, change, or insert records. 
      The executeQuery method returns a ResultSet object. 
      It contains methods to navigate through the records 
      in the ResultSet object (the next method for 
      example moves the cursor to the next row; it 
      returns false when it runs out of rows) as well 
      as methods to access fields in those rows. Notice 
      that the id and job_id fields are long data types 
      while name and location are Strings. The ResultSet 
      object provides methods to deal with most common data 
      types, but it’s a good idea to review how Java data 
      types align with database data types. The report is 
      formatted using the format method introduced in Java 5.  */
	
   public void getEmployees() {
      ResultSet rs = null;
      try {
         Statement s = getConnection().createStatement();
         rs = s.executeQuery("SELECT * FROM PERSON");
         System.out.format("%3s %-15s %-7s %-7s%n", 
            "ID", "NANE", "JOB ID", 
			"LOCATION");
         System.out.format("%3s %15s %7s %7s%n", 
            "---", "---------------", 
			"-------", "--------");

         while(rs.next()) {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            long job = rs.getLong("job_id");
            String location = rs.getString("location");
            System.out.format("%-3d %-15s %7d %5s%n", 
               id, name, job, location);
         }
      }
      catch(SQLException e) {
         System.out.println(e.getMessage());
         System.exit(-1);
      }
   }
}

The following class just exercises the DOA object:

package com.webucator.jdbc;

public class JDBCClient {

	public static void main(String[] args) {
		DAO dao = new DAO();
		dao.getEmployees();
	}
}

The output of this program is shown below:

ID NANE            JOB ID  LOCATION
--- --------------- ------- --------
1   Bill Smith         1183    OH
2   Sue Jones           529    NJ
3   Tom Swift         84443    FL
4   Bob the Builder   34693    MA
5   Homer Simpson        39    NE
7   Margo Lane         2222    TX
8   Bud Abbot          5202    RI

Check out our JDBC course for more information.

4 Responses to “Java Database Connectivity (JDBC) Basics using MySQL”

  1. Very good tutorial

  2. Very Good Explaination……..

  3. Well explained!!

  4. thank you very much…..

Tweetbacks

© Webucator, Inc. All rights reserved. | Toll Free: 877-932-8228 | Outside the USA: 315-849-2724 | Fax: 315-849-2723