JDBC Sample Code

The following java files provide a simple menu based program to query a sample database.

AircraftEx.java

package schumeyer; 
 
import java.io.BufferedReader; 
import java.io.File; 
import java.io.FileNotFoundException; 
import java.io.FileReader; 
import java.io.IOException; 
import java.io.InputStreamReader; 
import java.sql.SQLException; 
import java.util.Iterator; 
import java.util.List; 
import java.util.Scanner; 
 
public class AircraftEx { 
 
 /** 
  * @param args 
  */ 
 public static void main(String[] args) { 
  try { 
   // read the username and password from a file 
   File file = new File("D:\\db.txt"); 
   BufferedReader in = new BufferedReader(new FileReader(file)); 
   String username = in.readLine(); 
   String password = in.readLine(); 
   in.close(); 
 
   Sql.getInstance().Initialize(username, password); 
 
   int choice = 0; 
 
   do { 
 
    System.out.printf("1) Add Aircraft\n"); 
    System.out.printf("2) Delete Aircraft\n"); 
    System.out.printf("3) List Aircraft\n"); 
    System.out.printf("4) List Employees\n"); 
    System.out.printf("5) List Qual by Aircraft\n"); 
    System.out.printf("0) Quit\n"); 
    System.out.printf("==>"); 
 
    InputStreamReader isr = new InputStreamReader(System.in); 
    BufferedReader br = new BufferedReader(isr); 
    String s = br.readLine(); 
    choice = Integer.parseInt(s); 
 
    switch (choice) { 
    case 0: 
     System.out.printf("exiting"); 
     break; 
    case 1: // add aircraft 
    { 
     System.out.printf("   Aircraft id: "); 
     int aid = Integer.parseInt(br.readLine()); 
     System.out.printf(" Aircraft name: "); 
     String name = br.readLine(); 
     System.out.printf("Aircraft range: "); 
     int range = Integer.parseInt(br.readLine()); 
     Aircraft.Create(aid, name, range); 
    } 
     break; 
    case 2: // delete aircraft 
    { 
     System.out.printf("   Aircraft id: "); 
     int aid = Integer.parseInt(br.readLine()); 
     Aircraft.Destroy(aid); 
    } 
     break; 
    case 3: // list aircraft 
    { 
     List<Aircraft> aircraft = Aircraft.List(); 
     Iterator<Aircraft> itr = aircraft.iterator(); 
     for (int i = 0, n = aircraft.size(); i < n; i++) { 
      Aircraft a = itr.next(); 
      System.out.printf("%2d %30s %5d\n", a.getAid(), a 
        .getAname(), a.getCruisingrange()); 
     } 
    } 
     break; 
    case 4: // list employees 
    { 
     List<Employee> employees = Employee.List(); 
     Iterator<Employee> itr = employees.iterator(); 
     for (int i = 0, n = employees.size(); i < n; i++) { 
      Employee a = itr.next(); 
      System.out.printf("%2d %30s %5d\n", a.getEid(), a 
        .getEname(), a.getSalary()); 
     } 
    } 
     break; 
    case 5: // list qual by aircraft 
    { 
     System.out.printf("   Aircraft id: "); 
     int aid = Integer.parseInt(br.readLine()); 
     List<Employee> employees = Aircraft.getQualiedEmployees(aid); 
      
     Iterator<Employee> itr = employees.iterator(); 
     for (int i = 0, n = employees.size(); i < n; i++) { 
      Employee a = itr.next(); 
      System.out.printf("%2d %30s %5d\n", a.getEid(), a 
        .getEname(), a.getSalary()); 
     } 
    } 
     break; 
    default: 
     System.out.printf("Invalid Choice"); 
     break; 
    } 
 
   } while (choice != 0); 
  } catch (FileNotFoundException e) { 
   System.out.println(e.getMessage()); 
  } catch (IOException e) { 
   System.out.println(e.getMessage()); 
  } catch (SQLException e) { 
   System.out.println(e.getMessage()); 
  } finally { 
   Sql.getInstance().Close(); 
  } 
 } 
} 

Sql.java

package schumeyer;

import java.sql.*;

import oracle.jdbc.pool.OracleDataSource;

import oracle.core.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class Sql {
    private static Sql instance = new Sql();

    Connection conn = null;

    private Sql() {
    }

    public static Sql getInstance() {
        return instance;
    }

    public void Initialize(String username, String password)
            throws SQLException {

        java.util.Properties info = new java.util.Properties();
        OracleDataSource ods = new OracleDataSource();

        ods.setUser(username);
        ods.setPassword(password);
        ods.setURL("jdbc:oracle:thin:@csdb.csc.villanova.edu:1521:csdb");

        conn = ods.getConnection();
    }

    public void Close() {
        try {
            if (conn != null)
                conn.close();
            System.out.printf("Closed connection.\n");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public Connection getConn() {
        return conn;
    }

}

Aircraft.java

package schumeyer; 
 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.*; 
 
public class Aircraft { 
 
 int aid; 
 String aname; 
 int cruisingrange; 
 
 public Aircraft(int _aid, String _aname, int _cruisingrange) { 
  aid = _aid; 
  aname = _aname; 
  cruisingrange = _cruisingrange; 
 } 
 
 public static List<Aircraft> List() throws SQLException { 
  ArrayList<Aircraft> aircraft = new ArrayList<Aircraft>(); 
 
  Statement st = Sql.getInstance().getConn().createStatement(); 
  ResultSet rs = st.executeQuery("SELECT aid,aname,cruisingrange " 
    + "FROM aircraft " + "ORDER BY aname"); 
  while (rs.next()) { 
   aircraft.add(new Aircraft(rs.getInt(1), rs.getString(2), rs 
     .getInt(3))); 
  } 
  rs.close(); 
  st.close(); 
 
  return aircraft; 
 } 
 
 public static Aircraft Create(int aid, String aname, int cruisingrange) 
   throws SQLException { 
  Aircraft a = new Aircraft(aid, aname, cruisingrange); 
  PreparedStatement st = Sql.getInstance().getConn().prepareStatement( 
    "INSERT INTO aircraft (aid, aname, cruisingrange) " 
      + "VALUES (?,?,?)"); 
  st.setInt(1, aid); 
  st.setString(2, aname); 
  st.setInt(3, cruisingrange); 
  st.execute(); 
  st.close(); 
  return a; 
 } 
 
 public static List<Employee> getQualiedEmployees(int aid) throws SQLException { 
  ArrayList<Employee> employees = new ArrayList<Employee>(); 
 
  PreparedStatement st = Sql.getInstance().getConn().prepareStatement( 
    "SELECT e.eid,e.ename,e.salary " + "FROM certified c, employees e " 
      + "WHERE c.aid=? AND c.eid=e.eid"); 
  st.setInt(1, aid); 
  ResultSet rs = st.executeQuery(); 
  while (rs.next()) { 
   employees.add(new Employee(rs.getInt(1), rs.getString(2), rs 
     .getInt(3))); 
  } 
  rs.close(); 
  st.close(); 
 
  return employees; 
 } 
  
 public static void Destroy(int aid) throws SQLException { 
  PreparedStatement st = Sql.getInstance().getConn().prepareStatement( 
    "DELETE FROM aircraft WHERE aid=?"); 
  st.setInt(1, aid); 
  st.executeUpdate(); 
 } 
 
 public static List<Aircraft> findByAname(String aname) throws SQLException { 
  ArrayList<Aircraft> aircraft = new ArrayList<Aircraft>(); 
 
  PreparedStatement st = Sql.getInstance().getConn().prepareStatement( 
    "SELECT aid,aname,cruisingrange " + "FROM aircraft " 
      + "WHERE aname LIKE ? " + "ORDER BY aname"); 
  st.setString(1, aname + '%'); 
 
  ResultSet rs = st.executeQuery(); 
  while (rs.next()) { 
   aircraft.add(new Aircraft(rs.getInt(1), rs.getString(2), rs 
     .getInt(3))); 
  } 
  rs.close(); 
  st.close(); 
 
  return aircraft; 
 } 
 
 public int getAid() { 
  return aid; 
 } 
 
 public void setAid(int aid) { 
  this.aid = aid; 
 } 
 
 public String getAname() { 
  return aname; 
 } 
 
 public void setAname(String aname) { 
  this.aname = aname; 
 } 
 
 public int getCruisingrange() { 
  return cruisingrange; 
 } 
 
 public void setCruisingrange(int cruisingrange) { 
  this.cruisingrange = cruisingrange; 
 } 
} 

Employee.java

package schumeyer; 
 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.*; 
 
public class Employee { 
 
 int eid; 
 public int getEid() { 
  return eid; 
 } 
 
 public void setEid(int eid) { 
  this.eid = eid; 
 } 
 
 public String getEname() { 
  return ename; 
 } 
 
 public void setEname(String ename) { 
  this.ename = ename; 
 } 
 
 public int getSalary() { 
  return salary; 
 } 
 
 public void setSalary(int salary) { 
  this.salary = salary; 
 } 
 
 String ename; 
 int salary; 
 
 public Employee(int _eid, String _ename, int _salary) { 
  eid = _eid; 
  ename = _ename; 
  salary = _salary; 
 } 
 
 public static List<Employee> List() throws SQLException { 
  ArrayList<Employee> employees = new ArrayList<Employee>(); 
 
  Statement st = Sql.getInstance().getConn().createStatement(); 
  ResultSet rs = st.executeQuery("SELECT eid,ename,salary " 
    + "FROM employees " + "ORDER BY ename"); 
  while (rs.next()) { 
   employees.add(new Employee(rs.getInt(1), rs.getString(2), rs 
     .getInt(3))); 
  } 
  rs.close(); 
  st.close(); 
 
  return employees; 
 } 
 
 public static Employee Create(int eid, String ename, int salary) 
   throws SQLException { 
  Employee a = new Employee(eid, ename, salary); 
  PreparedStatement st = Sql.getInstance().getConn().prepareStatement( 
    "INSERT INTO employees (eid, ename, salary) " 
      + "VALUES (?,?,?)"); 
  st.setInt(1, eid); 
  st.setString(2, ename); 
  st.setInt(3, salary); 
  st.execute(); 
  st.close(); 
  return a; 
 } 
 
 public static void Destroy(int eid) throws SQLException { 
  PreparedStatement st = Sql.getInstance().getConn().prepareStatement( 
    "DELETE FROM employees WHERE eid=?"); 
  st.setInt(1, eid); 
  st.executeUpdate(); 
 } 
}