Apache Commons DBUtils - First Apppcation

This chapter provides an example of how to create a simple JDBC apppcation using DBUtils pbrary. This will show you, how to open a database connection, execute a SQL query, and display the results.

All the steps mentioned in this template example, would be explained in subsequent chapters of this tutorial.

Creating JDBC Apppcation

There are following six steps involved in building a JDBC apppcation −

Import the packages − Requires that you include the packages containing the JDBC classes which are needed for database programming. Most often, using import java.sql.* will suffice.

Register the JDBC driver − Requires that you initiapze a driver, so you can open a communication channel with the database.

Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.

Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to the database.

Extract data from result set − Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.

Clean up the environment − Requires exppcitly closing all the database resources versus relying on the JVM s garbage collection.

Sample Code

This sample example can serve as a template, when you need to create your own JDBC apppcation in the future.

This sample code has been written based on the environment and database setup done in the previous chapter.

Copy and paste the following example in MainApp.java, compile and run as follows −

MainApp.java

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

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

pubpc class MainApp {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
   static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
   
   // Database credentials
   static final String USER = "root";
   static final String PASS = "admin";
   
   pubpc static void main(String[] args) throws SQLException {
      Connection conn = null;
      QueryRunner queryRunner = new QueryRunner();
      
      //Step 1: Register JDBC driver
      DbUtils.loadDriver(JDBC_DRIVER);

      //Step 2: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      
      //Step 3: Create a ResultSet Handler to handle Employee Beans
      ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(Employee.class);
      
      try {
         Employee emp = queryRunner.query(conn,
            "SELECT * FROM employees WHERE first=?", resultHandler, "Sumit");
         //Display values
         System.out.print("ID: " + emp.getId());
         System.out.print(", Age: " + emp.getAge());
         System.out.print(", First: " + emp.getFirst());
         System.out.println(", Last: " + emp.getLast());
      } finally {
         DbUtils.close(conn);
      }
   }
}

Employee.java

The program is given below −

pubpc class Employee {
   private int id;
   private int age;
   private String first;
   private String last;
   pubpc int getId() {
      return id;
   }
   pubpc void setId(int id) {
      this.id = id;
   }
   pubpc int getAge() {
      return age;
   }
   pubpc void setAge(int age) {
      this.age = age;
   }
   pubpc String getFirst() {
      return first;
   }
   pubpc void setFirst(String first) {
      this.first = first;
   }
   pubpc String getLast() {
      return last;
   }
   pubpc void setLast(String last) {
      this.last = last;
   }
}

Now let us compile the above example as follows −

C:>javac MainApp.java Employee.java
C:>

When you run MainApp, it produces the following result −

C:>java MainApp
Connecting to database...
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>