获取选定的JComboBox值并添加到SQL查询中以获取第二个JComboBox [英] Get selected JComboBox value and add to SQL query for second JComboBox

问题描述

我正在设计一个简单的数据库应用程序，在GUI中具有2个jComboBoxes.第一个jComboBox填充有SQL查询的结果.我希望第二个jComboBox填充第二个查询的结果，该查询在第一个框中合并了用户选择的值，但是我无法完全使用它.

I am designing a simple database application that features 2 jComboBoxes in the GUI. The first jComboBox is populated with the results of an SQL query. I would like the second jComboBox to populate with the results of a second query that incorporates the user selected value in the first box, but I can't quite get it to work.

我创建了2个类，一个类绘制GUI并包含main方法，第二个类查询我的Oracle数据库.

I have created 2 classes, one that draws the GUI and contains the main method, and a second class that queries my Oracle database.

我的GUI类:

public class TestUI extends javax.swing.JFrame {
     // Create new form TestUI
    public TestUI() {
        initComponents();
    }

    @SuppressWarnings("unchecked")

    private void initComponents() {

        jPanel1 = new javax.swing.JPanel();
        jComboBox1 = new javax.swing.JComboBox<>();
        jTextField1 = new javax.swing.JTextField();
        jComboBox2 = new javax.swing.JComboBox<>();
        jButton1 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();
        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        // Combo box 1 population

        jComboBox1.removeAllItems();
        createConnection c1 = new createConnection();
        c1.getEmployee().forEach((employee) -> {
            jComboBox1.addItem(employee);
        });
        jComboBox1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jComboBox1ActionPerformed(evt);
            }
        });

        // ComboBox 2 population

        jComboBox2.removeAllItems();


    }                     

    private void jComboBox1ActionPerformed(java.awt.event.ActionEvent evt) {                                           
        // TODO add handling code here:
    }                                    

    public static void main(String args[]) {
        DRAW GUI
    }
}

还有我的数据库类:

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

public class createConnection {

    String empName;

    public Connection createConnection() {
        try {
            Class.forName(driver);
            java.sql.Connection conn = DriverManager.getConnection(DB_URL, DB_username, DB_password);
            return conn;
        } catch (ClassNotFoundException | SQLException e) {
            return null;
        }
    }

    // ComboBox 1
    public List<String> getEmployee() {
        List<String> list = new ArrayList();
        Connection conn = createConnection();
        try {
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM hr.employees ORDER BY last_name";
            ResultSet results = stmt.executeQuery(query);
            while (results.next()) {
                list.add(results.getString("last_name"));
            }
        } catch (Exception e) {
            System.out.println("Exception = " + e);
        }
        return list;
    }

    // Combo Box 2
    public List<String> getEmpLocation() {
        List<String> list = new ArrayList();
        Connection conn = createConnection();
        try {
            Statement stmt = conn.createStatement();
            String query = "SELECT country_id FROM hr.location WHERE hr.location.emp_name = " + empName;
            ResultSet results = stmt.executeQuery(query);
            while (results.next()) {
                list.add(results.getString("last_name"));
            }
        } catch (Exception e) {
            System.out.println("Exception = " + e);
        }
        return list;
    }    
}

我遗漏了不相关的代码，例如数据库连接变量和GUI坐标等.

I have left out irrelevant code like db connection variables and GUI coordinates etc.

我想知道如何在数据库类中正确获取getEmpLocation()方法以填充第二个ComboBox.这将涉及将代码添加到两个类中并传递变量值，但我无法弄清楚！任何帮助将不胜感激.

I am wondering how to properly get the getEmpLocation() method in the database class to populate the 2nd ComboBox. This will involve adding code to both classes and passing the variable value but I can't figure it out! Any help would be greatly appreciated here.

推荐答案

我假设您想从第一个JComboBox中选择一个值，然后单击一个按钮来处理所选数据并将新数据加载到第二个中JComboBox.

I'm assuming that you'd like select a value from your first JComboBox then click on a button to process your selected data and load new data to your second JComboBox.

在这种情况下，您需要JButton而不是JComboBox的ActionListener:

In this case you need an ActionListener to your JButton instead of your JComboBox:

jButton1.addActionListener(new ActionListener() {
    @Override
    public void actionPerformed(ActionEvent e) {
        selectedName = (String) jComboBox1.getSelectedItem();                
    }
});

您还需要将所选值存储在变量中. getSelectedItem()方法返回一个Object，因此根据您的情况需要将其强制转换为String.

You also need to store your selected value in a variable. The getSelectedItem() method returns an Object so it needs to be cast to a String in your case.

自从我们向按钮添加了ActionListener以来，您不需要此按钮:

Since we added an ActionListener to a button you dont need this one:

jComboBox1.addActionListener(new java.awt.event.ActionListener() {
    public void actionPerformed(java.awt.event.ActionEvent evt) {
        jComboBox1ActionPerformed(evt);
    }
});

在您的 createConnection类中(通过命名约定，类名应以大写字母开头):

In your createConnection class (by naming convention class names should start with a capital letter):

如果您不使用try-with-resources语句，则应在catch块之后关闭连接.

If you are not using try-with-resources statement you should close your connections after the catch block.

    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
    }

您需要将您的selectedName变量传递给getEmpLocation()方法:

You need to pass your selectedName variable to getEmpLocation() method:

public List<String> getEmpLocation(String name) {

您应该使用PreparedStatement而不是Statement:

You should use a PreparedStatement instead of Statement:

String query = "SELECT first_name FROM employees WHERE last_name = ?";
PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, name);            
ResultSet results = ps.executeQuery();

说实话，我不知道您要通过选择查询实现什么.首先，此选择查询将不起作用.表名是LOCATIONS而不是位置，并且它没有称为emp_name的列.

To be honest I don't know what you'd like to achieve with your select queries. First, this select query won't work. Table name is LOCATIONS instead of location, and it doesnt have a column called emp_name.

"SELECT country_id FROM hr.location WHERE hr.location.emp_name = ?"

如果您想获取位置，则应使用如下查询:

If you'd like to get locations you should use a query like this:

"SELECT dep.department_name, loc.city, cou.country_name
 FROM employees emp, departments dep, locations loc, countries cou
 WHERE emp.last_name = ?
 AND emp.department_id = dep.department_id 
 AND dep.location_id = loc.location_id
 AND loc.country_id = cou.country_id"

您可以选择要使用部门，城市或国家/地区名称的位置.但是我的主要问题是，如果您首先选择姓氏并将其放在JComboBox中，则很可能只会获得一行数据，因此使用第二个JComboBox毫无意义.让我们从另一侧解决这个问题.如果先选择位置然后再选择员工该怎么办.那可以解决这个问题.

You can choose which location you'd like to use department, city or country name. But my main problem is that if you select last names first and put them in a JComboBox it is most likely you will get only one row of data, so there is no point in using the second JComboBox. Let's approach this problem from the other side. What if you select location first and then select your employee. That could solve this issue.

快速示例: 您可以从数据库中选择所有姓氏，然后可以选择适当的姓氏.

Quick Example: You select all first names from database, then you can select proper last name.

从数据库中选择所有名字:

Selecting all first name from database:

    public List<String> getEmpFirstName() {
        List<String> list = new ArrayList();
        Connection conn = createConnection();

        try {
            Statement stmt = conn.createStatement();
            String query = "SELECT DISTINCT first_name "
                            + "FROM hr.employees "
                            + "ORDER BY first_name";
            ResultSet results = stmt.executeQuery(query);

            while (results.next()) {
                list.add(results.getString("first_name"));
            }
        } catch (Exception e) {
            System.out.println("Exception = " + e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }                    
            }
        }
        return list;
    }

使用PreparedStatement根据姓氏选择姓氏:

Selecting last name(s) based on first name using PreparedStatement:

    public List<String> getEmpLastName(String name) {
        List<String> list = new ArrayList();
        Connection conn = createConnection();

        try {
            String query = "SELECT last_name "
                            + "FROM employees "
                            + "WHERE first_name = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, name);            
            ResultSet results = ps.executeQuery();

            while (results.next()) {
                list.add(results.getString("last_name"));
            }
        } catch (Exception e) {
            System.out.println("Exception = " + e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }                    
            }
        }
        return list;
    }

更新您的ActionListener:

Update your ActionListener:

    jButton1.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // Store selected value
                selectedName = (String) jComboBox1.getSelectedItem();

                // Create Connection and pass selected value to getEmpLastName
                createConnection c1 = new createConnection();
                names = c1.getEmpLastName(selectedName);

                // Clear your second comboBox and fill with data
                jComboBox2.removeAllItems();                
                for (String lastName : names) {
                    jComboBox2.addItem(lastName);
                }              
            }
        });    

尝试选择常用名称，例如Alexander，David，James，John，Julia等.

Try to select common names like Alexander, David, James, John, Julia and so on.

这篇关于获取选定的JComboBox值并添加到SQL查询中以获取第二个JComboBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！