Populate Java swing JComboBox with Oracle SQL resultSet

Question

I am working on a simple application that includes a JComboBox for users to select from. I am new to Java and am having trouble populating the JComboBox with results from an SQL select statement. This is due to my lack of knowledge!

I have written 3 classes, a dbConnection class to handle the db side of things. Another class that instantiates my GUI, and the last class contains my main method:

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

// Extends java.sql :~ describes connections to Oracle db
class dbConn {

    Connection conn;
    Statement stmt;
    ResultSet rs;
    String conString;
    String sqlString;

    void dbConn() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(conString);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        if (conn != null) {
            System.out.println("Connection established, database uplink is online.");
        } else {
            System.out.println("Connection failed, please check database status.");
        }
    }
}

Second class for the GUI:

import javax.swing.*;

public class CaseMoverUI {
    void testUI(){

        // Create a new JFrame container
        JFrame jfrm = new JFrame("CaseMover");
        jfrm.setSize(550, 450);
        jfrm.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        JComboBox jbox = new JComboBox();
        jfrm.add(jbox);
        jfrm.setVisible(true);
        SwingUtilities.invokeLater(new Runnable(){
            public void run(){
                new CaseMoverUI();
            }
        });
    }
}

And lastly:

public class sqlCaller {
    public static void main(String args[]){
        //instantiate db object and pass values to the constructor
        dbConn db = new dbConn();
        db.conString = "jdbc:oracle:thin:system/password123@127.0.0.1:1521:xe";
        db.sqlString = "SELECT true FROM dual";

        db.dbConn();
        CaseMoverUI ui = new CaseMoverUI();
        ui.testUI();
    }
}

The first thing I am not sure how to do is to retrieve my results and add them to the JComboBox. I know I need to write a method for the dbConn class, and pass an SQL query to it.

Using the HR test schema with Oracle XE, SQL query might be something like:

SELECT first_name FROM employees;

I'm not sure of the proper way to write this method however. Any help here would be much appreciated!

Answer 1

A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. For example, the selectDataFromDatabase() method creates a ResultSet, rs, when it executes the query through the Statement object, stmt. Note that a ResultSet object can be created through any object that implements the Statement interface, including PreparedStatement, CallableStatement, and RowSet.

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet. Initially, the cursor is positioned before the first row. The method ResultSet.next moves the cursor to the next row. This method returns false if the cursor is positioned after the last row. This method repeatedly calls the ResultSet.next method with a while loop to iterate through all the data in the ResultSet.

public Vector selectDataFromDatabase() {
        Vector vector = new Vector();
        Connection con = null;
        try {
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            Statement stmt = con.createStatement();
            String query = "SELECT first_name FROM employees";
            ResultSet rs = stmt.executeQuery(query);
                 while (rs.next()) {
                     String firstName = rs.getString("FIRST_NAME");
                     vector.add(firstName);
                 }
        } catch (ClassNotFoundException ex) {
            System.out.println("An error has occured! I cannot find driver!");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException ex) {
                System.out.println("An error has occured while closing database connection!");
            }
        }
        return vector;
    }

In your GUI:

Vector vector = selectDataFromDatabase();
JComboBox jbox = new JComboBox(vector);

Or just simply:

JComboBox jbox = new JComboBox(selectDataFromDatabase());

Answer 2

First of all you need to get a List of your result from database :

You need to return your connection to use it in other method like this :

public Connection dbConn() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(conString);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        if (conn != null) {
            System.out.println("Connection established, database uplink is online.");
        } else {
            System.out.println("Connection failed, please check database status.");
        }
      return conn;
    }

Then your method to get the list of your employee :

public List<String> listeEmployee() {
    List<String> list = new ArrayList<>();
    Connection con = dbConn();
    try {
        Statement stm = con.createStatement();

        String query = "SELECT first_name FROM employees";

        ResultSet resultat = stm.executeQuery(requete);
        while (resultat.next()) {
            list.add(resultat.getString("first_name"));
        }
    } catch (Exception e) {
        System.out.println("Exception = " + e);
    }
    return list;
}

Then you can add this results to your JComboBox like so :

//Empty your JComboBox
jbox.removeAllItems();
for (String employee : listeEmployee) {
    jbox.addItem(employee);
}