Web Development
Using PHP

Simpson College
Computer Science

JDBC

These are progressively more complex examples showing how to hook Java code up to a database using JDBC. The full code, along with supporting libraries is available for download here: JDBCDemo.zip

To run this code, you'll need a database that it expects. Here is some SQL code to set up the database:

--
-- Database: `jdbctest`
--

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first` varchar(50) NOT NULL,
  `last` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
)  ;

--
-- Dumping data for table `person`
--

INSERT INTO `person` (`id`, `first`, `last`, `phone`) VALUES
(1, 'Tom', 'Baker', '555-555-0004'),
(2, 'Matt', 'Smith', '555-555-0011');

Example 1

This is a classic example of hooking up to the database and dumping data to the screen:

package edu.simpson.jdbctest.item01;

import java.sql.*;

public class Main01 {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/jdbctest";

	// Database credentials
	static final String USER = "jdbctest";
	static final String PASS = "password";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);

			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "select id, first, last, phone from person person ";
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				int id = rs.getInt("id");
				String first = rs.getString("first");
				String last = rs.getString("last");
				String phone = rs.getString("last");

				System.out.print("ID: " + id);
				System.out.print(", First: " + first);
				System.out.print(", Last: " + last);
				System.out.println(", Phone: "+ phone);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
		System.out.println("Goodbye!");
	}
}

Example 2

This includes a separate class called ConnectionManager that loads the database information in a ResourceBundle. Using this ConnectionManager allows the programmer to have just one spot to easily change database information. It also uses a Logger for better error messaging.

This is a text file from which the database information is loaded from. See the use of ResourceBundle in the ConnectionManager to see how it is used.

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost/jdbctest
user = jdbctest
password = password

Here is the ConnectionManager class that gets and closes connections.

package edu.simpson.jdbctest.item02;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ConnectionManager {
	
	private final static Logger log = Logger.getLogger(ConnectionManager.class.getName());
	private static String driver;
	private static String url;
	private static String username;
	private static String password;

	public static void setup() {
		try {
			ResourceBundle prop = ResourceBundle.getBundle("edu.simpson.jdbctest.resource.connection");
			
			driver = prop.getString("driver");
			url = prop.getString("url");
			username = prop.getString("user");
			password = prop.getString("password");
			
			Class.forName(driver);
		}
		catch (Exception e){
			log.log(Level.SEVERE, "Unable to load property file.",e);
		}
		 
	}
	public static Connection getConnection() {
		System.out.println("Connecting to database...");
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			log.log(Level.SEVERE, "Unable to get connection", e);
		}
		return conn;
	}

	public static void releaseConnection(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			log.log(Level.SEVERE,"Can't close connection",se);
		}		
	}

}

This is the main program.

package edu.simpson.jdbctest.item02;

import java.sql.*;

public class Main02 {

	public static void main(String[] args) {
		ConnectionManager.setup();
		
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = ConnectionManager.getConnection();
			
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "select id, first, last, phone from person person ";
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				int id = rs.getInt("id");
				String first = rs.getString("first");
				String last = rs.getString("last");
				String phone = rs.getString("last");

				System.out.print("ID: " + id);
				System.out.print(", First: " + first);
				System.out.print(", Last: " + last);
				System.out.println(", Phone: "+ phone);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}
			ConnectionManager.releaseConnection(conn);
		}
		System.out.println("Goodbye!");
	}
}

Example 3

This example is split into an Model-View-Controller (MVC) framework.

Model

This expands on the ConnectionManagerDS from the prior example by using DataSource. The database connections are pooled. Usually the DataSource is set up by the web server, but since this example doesn't use a web server we manually set it up.

package edu.simpson.jdbctest.item03.model;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

public class ConnectionManagerDS {

	private final static Logger log = Logger
			.getLogger(ConnectionManagerDS.class.getName());
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	private static DataSource dataSource;

	public static void setup() {
		try {
			ResourceBundle prop = ResourceBundle
					.getBundle("edu.simpson.jdbctest.resource.connection");

			driver = prop.getString("driver");
			url = prop.getString("url");
			username = prop.getString("user");
			password = prop.getString("password");
		} catch (Exception e) {
			log.log(Level.SEVERE, "Unable to load property file.", e);
		}

		try {
			Class.forName(driver);
		} catch (Exception e) {
			log.log(Level.SEVERE, "Unable to load driver " + driver, e);
		}
		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
				url, username, password);
		ObjectPool<Connection> connectionPool = new GenericObjectPool<Connection>(
				null);
		PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
				connectionFactory, connectionPool, null, null, false, true);
		dataSource = new PoolingDataSource(poolableConnectionFactory.getPool());
	}

	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			log.log(Level.SEVERE, "Unable to get connection", e);
		}
		return conn;
	}

	public static void releaseConnection(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			log.log(Level.SEVERE, "Can't close connection", se);
		}
	}

}

This is a simple business object to represent a person.

package edu.simpson.jdbctest.item03.model;

public class Person {

	private String first;
	private String last;
	private String phone;
	private int id;
	public String getFirst() {
		return first;
	}
	public void setFirst(String first) {
		this.first = first;
	}
	public String getLast() {
		return last;
	}
	public void setLast(String last) {
		this.last = last;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}

}

The Data Access Object has static functions to handle moving the Person object in and out of the database.

package edu.simpson.jdbctest.item03.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.sql.PreparedStatement;


public class PersonDAO {
	private final static Logger log = Logger
			.getLogger(PersonDAO.class.getName());

	public static void addPerson(Person person) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ConnectionManagerDS.getConnection();

			String sql;
			sql = "insert into person (first, last, phone) values (?,?,?)";
			
			stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1,person.getFirst());
			stmt.setString(2,person.getLast());
			stmt.setString(3,person.getPhone());
			
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				person.setId(rs.getInt(1));
			}
			rs.close();
			stmt.close();
			ConnectionManagerDS.releaseConnection(conn);
		} catch (SQLException se) {
			log.log(Level.SEVERE, "SQL Error", se );
		} catch (Exception e) {
			log.log(Level.SEVERE, "Error", e );
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
				log.log(Level.SEVERE, "Can't close statement", se2 );
			}
			ConnectionManagerDS.releaseConnection(conn);
		}
	}
	
	public static List<Person> getPeople() {
		List<Person> list = new LinkedList<Person>();
		
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = ConnectionManagerDS.getConnection();
			
			stmt = conn.createStatement();
			String sql;
			sql = "select id, first, last, phone from person person ";
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				Person person = new Person();
				person.setId(rs.getInt("id"));
				person.setFirst(rs.getString("first"));
				person.setLast(rs.getString("last"));
				person.setPhone(rs.getString("phone"));
				list.add(person);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			log.log(Level.SEVERE, "SQL Error", se );
		} catch (Exception e) {
			log.log(Level.SEVERE, "Error", e );
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
				log.log(Level.SEVERE, "Can't close statement", se2 );
			}
			ConnectionManagerDS.releaseConnection(conn);
		}
		return list;
	}

	public static void deletePerson(int id) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = ConnectionManagerDS.getConnection();

			String sql;
			sql = "delete from person where id = ?";
			
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1,id);
			
			stmt.executeUpdate();
			
			stmt.close();
			ConnectionManagerDS.releaseConnection(conn);

		} catch (SQLException se) {
			log.log(Level.SEVERE, "SQL Error", se );
		} catch (Exception e) {
			log.log(Level.SEVERE, "Error", e );
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
				log.log(Level.SEVERE, "Can't close statement", se2 );
			}
			ConnectionManagerDS.releaseConnection(conn);
		}
	}

}

View

The View layer in this case is just a class that prints out a person, or a list of people.

package edu.simpson.jdbctest.item03.view;

import java.util.List;

import edu.simpson.jdbctest.item03.model.Person;

public class PersonPrint {

	public static void printPeople(List<Person> people) {
		for( Person person : people ) {
			printPerson(person);
		}		
	}
	public static void printPerson(Person person) {
		System.out.format("ID: %4d First: %-10s Last: %-10s Phone: %s\r\n",
				person.getId(),
				person.getFirst(),
				person.getLast(),
				person.getPhone());
		
	}

}

Controller

The controller layer has all the main control logic. So here is our main program:

package edu.simpson.jdbctest.item03.controller;

import java.util.List;

import edu.simpson.jdbctest.item03.model.ConnectionManagerDS;
import edu.simpson.jdbctest.item03.model.Person;
import edu.simpson.jdbctest.item03.model.PersonDAO;
import edu.simpson.jdbctest.item03.view.PersonPrint;

public class Main03 {

	public static void main(String[] args) {
		ConnectionManagerDS.setup();

		// List all the people in the database
		List<Person> people = PersonDAO.getPeople();
		PersonPrint.printPeople(people);
		
		// Create a new person
		Person person = new Person();
		person.setFirst("David");
		person.setLast("Tennant");
		person.setPhone("555-555-0010");
		
		// Add to the database, and print. Note the ID number should be set
		PersonDAO.addPerson(person);
		PersonPrint.printPerson(person);
		
		// Delete the person
		PersonDAO.deletePerson(person.getId());
		
	}
}

Example 4 - Hibernate

Hibernate is one of several persistance frameworks for Java. It helps automatically manage the Data Access layer by assisting with the Java object to database table mapping.

There are a lot of library (JAR) files required to get this working. If you download the ZIP file it can help get you set up.

To begin with, we have a plain Java object with getters and setters. This is similar to the prior example:

package edu.simpson.jdbctest.item04;

public class Person {

	private String first;
	private String last;
	private String phone;
	private int id;
	public Person(String first, String last, String phone) {
		this.first = first;
		this.last = last;
		this.phone = phone;
	}
	public Person() {
		
	}
	public String getFirst() {
		return first;
	}
	public void setFirst(String first) {
		this.first = first;
	}
	public String getLast() {
		return last;
	}
	public void setLast(String last) {
		this.last = last;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}

}

Next is a configuration file for Hibernate telling it where the database is, and all the files that map a Java class to a database table.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM 
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
   <session-factory>
   <property name="hibernate.dialect">
      org.hibernate.dialect.MySQLDialect
   </property>
   <property name="hibernate.connection.driver_class">
      com.mysql.jdbc.Driver
   </property>

   <!-- Assume test is the database name -->
   <property name="hibernate.connection.url">
      jdbc:mysql://localhost/jdbctest
   </property>
   <property name="hibernate.connection.username">
      jdbctest
   </property>
   <property name="hibernate.connection.password">
      password
   </property>

   <!-- List of XML mapping files -->
   <mapping resource="Person.hbm.xml"/>

</session-factory>
</hibernate-configuration>

Here's a file that maps the Java Person class to a database table.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 

<hibernate-mapping>
   <class name="edu.simpson.jdbctest.item04.Person" table="Person">
      <meta attribute="class-description">
         This class contains the employee detail. 
      </meta>
      <id name="id" type="int" column="id">
         <generator class="native"/>
      </id>
      <property name="first" column="first" type="string"/>
      <property name="last" column="last" type="string"/>
      <property name="phone" column="phone" type="string"/>
   </class>
</hibernate-mapping>

This is the data access object that does the database operations on the Person table.

package edu.simpson.jdbctest.item04;

import java.util.List;
import java.util.Iterator;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class PersonDAO {

	private static SessionFactory factory;
	private static ServiceRegistry serviceRegistry;

	public static void setup() {
		try {
			Configuration configuration = new Configuration();
			configuration.configure();
			serviceRegistry = new ServiceRegistryBuilder().applySettings(
					configuration.getProperties()).buildServiceRegistry();
			factory = configuration.buildSessionFactory(serviceRegistry);
		} catch (Throwable ex) {
			System.err.println("Failed to create sessionFactory object." + ex);
			throw new ExceptionInInitializerError(ex);
		}
	}

	// Method to CREATE an person in the database
	public static Integer addPerson(String fname, String lname, String phone) {
		Session session = factory.openSession();
		Transaction tx = null;
		Integer employeeID = null;
		try {
			tx = session.beginTransaction();
			Person person = new Person(fname, lname, phone);
			employeeID = (Integer) session.save(person);
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		return employeeID;
	}

	// Method to READ all the employees
	public static void listEmployees() {
		Session session = factory.openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			@SuppressWarnings("unchecked")
			List<Person> employees = (List<Person>) session.createQuery(
					"FROM Person").list();
			for (Iterator<Person> iterator = employees.iterator(); iterator
					.hasNext();) {
				Person person = (Person) iterator.next();
				System.out.print("First Name: " + person.getFirst());
				System.out.print("  Last Name: " + person.getLast());
				System.out.println("  Phone: " + person.getPhone());
			}
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
	}

	// Method to UPDATE salary for an person
	public static void updatePerson(Integer EmployeeID, String phone) {
		Session session = factory.openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Person person = (Person) session.get(Person.class, EmployeeID);
			person.setPhone(phone);
			session.update(person);
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
	}

	// Method to DELETE an person from the records
	public static void deleteEmployee(Integer EmployeeID) {
		Session session = factory.openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Person person = (Person) session.get(Person.class, EmployeeID);
			session.delete(person);
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
	}
}

Here is the main code that calls directs what is happening.

package edu.simpson.jdbctest.item04;

public class Main04 {

	public static void main(String[] args) {
		PersonDAO.setup();

		// Add few person records in database
		Integer empID1 = PersonDAO.addPerson("Jon", "Pertwee", "555-555-0003");
		Integer empID2 = PersonDAO.addPerson("Colin", "Baker", "555-555-0006");
		Integer empID3 = PersonDAO.addPerson("Peter", "Davidson",
				"555-555-0005");

		// List down all the employees
		PersonDAO.listEmployees();

		// Update person's records
		PersonDAO.updatePerson(empID1, "555-555-1212");

		// Delete an person from the database
		PersonDAO.deleteEmployee(empID2);

		// List down new list of the employees
		PersonDAO.listEmployees();
		PersonDAO.deleteEmployee(empID1);
		PersonDAO.deleteEmployee(empID3);
	}

}

You are not logged in. Log in here and track your progress.