JDBC Sample Code

The following java files provide a short example of connecting to the csdb oracle server and performing some simple queries.  In addition to these java files, you will need an appropriate jdbc driver, like this one

TestOrcl.java

package schumeyer;

import java.io.*;
import java.sql.*;
import java.util.Iterator;

import oracle.jdbc.pool.OracleDataSource;

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

import java.util.*;

public class TestOrcl {

    /**
     * @param args
     */
    public static void main(String[] args) {
        try {
            File file = new File("C:/db.txt");
            FileReader fr = new FileReader(file);
            BufferedReader in = new BufferedReader(fr);
            String username;
            String password;
            username = in.readLine();
            password = in.readLine();

            Sql.getInstance().Initialize(username, password);
            
            Aircraft.Create(17, "Cessna 172", 300);
            
            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());
            }
            
        } 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 aid");
        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<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 aid");
        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;
    }
}