How to insert foreign key values from ComboBox into MYSQL database

Question

Fairly new to java development and I am having some issues with inserting ComboBox values into a database. My ComboBox consists of values from tblcourses with the columns of course_title and a primary key of course_id and I display the value of course_title onto it. With that in mind I want to insert the primary key of course_id into tblusers. I have used ObservableList to populate my ComboBox but I can not figure out how to insert the foreign key. I have also tried to display the foreign keys onto the ComboBox using getInt but it still gives me the error shown below.

Here is my insertion code:

Statement stmt = conn.createStatement();
            String query = "INSERT INTO tblusers (first_name, middle_initial, last_name, course_id, school_id, username,"
                    + " password, privilege) VALUES ('txtFn', 'txtMi', 'txtLn', 'cmbSchool', 'cmbCourse', 'txtUser',"
                    + "  'txtPass', 'student')";
            stmt.executeUpdate(query);

But it gives me an error of:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect integer value: 'cmbSchool' for column 'course_id' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4118)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1816)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1730)
at elibraryserver.NewStudentController.registerButtonAction(NewStudentController.java:158)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8413)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:381)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$353(GlassViewEventHandler.java:417)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:416)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:937)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
at java.lang.Thread.run(Thread.java:748)

What I understand from the error is that I am trying to insert a String into an Integer, hence the need to get the primary key of my tblcourses. How can I make this work?

For reference here is my code to populate the ComboBox:

public void populateCourse() throws SQLException {

    try {
        ObservableList data = FXCollections.observableArrayList();
        String query = "SELECT course_title FROM tblcourses";
        rs = conn.createStatement().executeQuery(query);

        while (rs.next()) {
            data.add(rs.getString("course_title"));
        }
        cmbCourse.setItems(data);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Answer 1

In your insert statement, you are still inserting the string value 'cmbSchool'. You need to replace that with the integer variable that holds the key value. Also watch the order of the insert values. Your insert should look more like

Statement stmt = conn.createStatement();
            String query = "INSERT INTO tblusers "
+ "(first_name, middle_initial, last_name, course_id, school_id, username, password, privilege)"
+ " VALUES "
+ "('" + txtFn + "','" + txtMi + "','" + txtLn + "'," + cmbCourseKey + "," + cmbSchoolKey + ","
+ "'" + txtUser + "','" + txtPass + "','student')";
            stmt.executeUpdate(query);

Note that the insert statement is a string so you need to convert your variable values to a string, hence the concatenation. If you insert 'txtFn' as a value, your database will have the student listed with the first name "txtFn". But if txtFn = "Bob", then the string above will be created as "...Values('Bob','...".

In terms of the combo box and dealing with key/value objects, understand that the combo box is a way of selecting one of a number of objects. What those objects are is up to you. If you want the object to be a key/value pair, you'll need to define or use a suitable class to hold the data and then fill the combobox with that data. You can then override the renderer so the combo box only shows what you want it to show, for example, the value. In this way, you can use complex objects with the combo box.

Below are two examples that use the java.util.AbstractMap.SimpleEntry<K,V> as the object, the first using Swing, the second using javafx. The Swing demo uses a renderer set to show only the value. You can extract the selected object in the Action Listener or Property Listener and then extract the key from the SimpleEntry object. The Swing demo does this in the Action Listener and displays the "Key : Value" in another text field.

The javafx demo uses a cell factory to customize the buttoncell (what the combo box appear as) and the listcell (the pull down list). The key value can be extracted by adding a listener to the value property.

import java.awt.AWTEvent;
import java.awt.Component;
import java.awt.Cursor;
import java.awt.Dimension;
import java.awt.GraphicsConfiguration;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.Rectangle;
import java.awt.Toolkit;
import java.awt.Window;
import java.util.AbstractMap;
import java.util.AbstractMap.SimpleEntry;

import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.plaf.basic.BasicComboBoxRenderer;

/**
 * This class demonstrates overriding the combo box renderer so that
 * a given object can be displayed in the combo box in a particular way.
 * In this demo, the class java.util.AbstractMap.SimpleEntry<K,V> will be used
 * as the object and only the value will be shown in the combo box. The
 * {@link SimpleEntryRenderer} class is used to override the standard renderer
 * to do this.
 *
 */
@SuppressWarnings("serial")
public class ComboBoxKeyValueDemo extends JFrame {

    private JPanel          contentPanel    = null;
    private JTextField      fKeyValue       = new JTextField();
    private GridBagLayout   gridBagLayout   = new GridBagLayout();
    private JLabel          lComboBox       = new JLabel();
    private JLabel          lKeyValue       = new JLabel();

    private JComboBox<SimpleEntry<Integer, String>> fComboBox = new JComboBox<>();

    /**
     * The SimpleEntryRenderer class is used to override the component
     * renderer of the combo box and display the SimpleEntry<Integer,String>
     * as the String value.  Normally, the display would appear as "K=V" 
     *
     */
    public class SimpleEntryRenderer extends BasicComboBoxRenderer {
      @SuppressWarnings({ "rawtypes", "unchecked" })
      @Override
      public Component getListCellRendererComponent(JList list, Object value,
          int index, boolean isSelected, boolean cellHasFocus) {
        super.getListCellRendererComponent(list, value, index, isSelected,
            cellHasFocus);
        if (value != null) {
          AbstractMap.SimpleEntry<Integer, String> item = (AbstractMap.SimpleEntry<Integer, String>) value;
          setText(item.getValue());
        }
        return this;
      }  
    }

    public ComboBoxKeyValueDemo() {

        enableEvents(AWTEvent.WINDOW_EVENT_MASK);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        try {
            // Initialize the fields
            jbInit();
        } catch(Exception e) {
            e.printStackTrace();
            System.exit(1);
        }

        try {
            setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
            showCenterScreen(this);
            setCursor(Cursor.getDefaultCursor());
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
        }   
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    private void jbInit() {

        contentPanel = (JPanel) this.getContentPane();
        contentPanel.setLayout(gridBagLayout);

        lComboBox.setText("Select entry:");

        // Fill the comboBox with SimpleEntry objects
        fComboBox.addItem(new SimpleEntry<Integer, String>(1, "-"));
        fComboBox.addItem(new SimpleEntry<Integer, String>(2, "X"));
        fComboBox.addItem(new SimpleEntry<Integer, String>(3, "Y"));
        fComboBox.setMaximumRowCount(3);

        // This is the important bit. The renderer determines how the 
        // the combo box displays the object it has selected. Here
        // we'll replace the renderer with a custom one that will only
        // display the value of the SimpleEntry object.
        fComboBox.setRenderer(new SimpleEntryRenderer());

        // For the demo, when the value of the comboBox changes, 
        // display the Key - Value in another field.
        fComboBox.addActionListener(e -> {
            JComboBox c = (JComboBox) e.getSource();
            SimpleEntry<Integer, String> item = (SimpleEntry<Integer, String>) c.getSelectedItem();
            fKeyValue.setText("Item " + item.getKey() + " : " + item.getValue());
        });

        lKeyValue.setText("Key : Value");
        fKeyValue.setColumns(10);
        fKeyValue.setEditable(false);

        fComboBox.setSelectedIndex(0);  // Will fire event to update the fKeyValue field.

        // Layout the fields
        contentPanel.add(lComboBox,    new GridBagConstraints(0, 0, 1, 1, 0.0, 0.0
            ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 5, 2, 5), 0, 0));
        contentPanel.add(fComboBox,    new GridBagConstraints(1, 0, 1, 1, 0.5, 0.0
            ,GridBagConstraints.WEST, GridBagConstraints.BOTH, new Insets(5, 2, 2, 5), 0, 0));

        contentPanel.add(lKeyValue,     new GridBagConstraints(0, 1, 1, 1, 0.0, 0.0
            ,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(2, 5, 2, 5), 0, 0));
        contentPanel.add(fKeyValue,     new GridBagConstraints(1, 1, 1, 1, 0.5, 0.0
            ,GridBagConstraints.WEST, GridBagConstraints.BOTH, new Insets(2, 2, 2, 5), 0, 0));
    }


    public static void main(String[] args) {
      new ComboBoxKeyValueDemo();
    }

    /**
     *  Show in the center of the screen.
     *  (pack, set location and set visibility)
     *  @param window Window to position
     */
    public static void showCenterScreen(Window window) {
        positionScreen (window);
        window.setVisible(true);
        window.toFront();
    }   //  showCenterScreen

    /**
     *  Position window in center of the screen
     *  @param window Window to position
     */
    public static void positionScreen (Window window)
    {
        window.pack();
        // take into account task bar and other adornments
        GraphicsConfiguration config = window.getGraphicsConfiguration();
        Rectangle bounds = config.getBounds();
        Dimension sSize = bounds.getSize();
        Insets insets = Toolkit.getDefaultToolkit().getScreenInsets(config);
        sSize.width -= (insets.left + insets.right);
        sSize.height -= (insets.top + insets.bottom);

        Dimension wSize = window.getSize();
        //  fit on window
        if (wSize.height > sSize.height)
            wSize.height = sSize.height;
        if (wSize.width > sSize.width)
            wSize.width = sSize.width;
        window.setSize(wSize);
        //  Center
        int x = (sSize.width - wSize.width) / 2;
        int y = (sSize.height - wSize.height) / 2;
        //
        window.setLocation(bounds.x + x + insets.left, bounds.y + y + insets.top);
    }   //  positionScreen
}

Here is the javafx demo:

import java.util.AbstractMap.SimpleEntry;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javafx.application.Application;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.geometry.Insets;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.control.ComboBox;
import javafx.scene.control.Label;
import javafx.scene.control.ListCell;
import javafx.scene.control.ListView;
import javafx.scene.control.TextField;
import javafx.scene.layout.GridPane;
import javafx.scene.paint.Color;
import javafx.scene.paint.Paint;
import javafx.stage.Stage;
import javafx.util.Callback;

/**
 * This class demonstrates custom cell factory so that
 * a given object can be displayed in the combo box button cell and list view
 * in a particular way.  In this demo, the class java.util.AbstractMap.SimpleEntry<K,V> 
 * will be used as the object and only the value will be shown in the combo box. 
 *
 */
@SuppressWarnings("serial")
public class FXComboBoxHashMapDemo extends Application {
    public static void main(String[] args) {
        launch(args);
    }

    private final TextField         fKeyValue        = new TextField();
    private final TextField         fKeyValueDefault = new TextField();
    private final Label             lComboBox        = new Label();
    private final Label             lComboBoxDefault = new Label();
    private final Label             lKeyValue        = new Label();
    private final Label             lKeyValueDefault = new Label();

    private ComboBox<Entry<Integer,String>> fComboBox = new ComboBox<>();
    private ComboBox<Entry<Integer,String>> fComboBoxDefault = new ComboBox<>();

    @SuppressWarnings("unchecked")
    @Override
    public void start(Stage stage) {

        lComboBoxDefault.setText("Combo with default renderer: ");
        fComboBoxDefault.getItems().setAll(populateHashMap());
        fComboBoxDefault.setPrefWidth(100.0);
        fComboBoxDefault.valueProperty().addListener(new ChangeListener() {
            @SuppressWarnings("rawtypes")
            @Override
            public void changed(ObservableValue ov, Object arg1,
                    Object arg2) {
                if (arg2 instanceof SimpleEntry<?,?>) {
                    SimpleEntry<Integer, String> entry = (SimpleEntry<Integer, String>) arg2;
                    fKeyValueDefault.setText("Key: " + entry.getKey() + " Value: " + entry.getValue());
                }
            }    
        });

        lComboBox.setText("Combo with custom renderer: ");
        fComboBox.getItems().setAll(populateHashMap());

        // Customize the cell appearance
        Callback<ListView<Map.Entry<Integer, String>>, ListCell<Map.Entry<Integer, String>>> customCallBack
            = new Callback<ListView<Map.Entry<Integer, String>>, ListCell<Map.Entry<Integer, String>>>() {
             @Override public ListCell<Map.Entry<Integer, String>> call(ListView<Map.Entry<Integer, String>> list) {
                 return new KeyValueFormatCell();
             }
         };
        fComboBox.setButtonCell(customCallBack.call(null));
        fComboBox.setCellFactory(customCallBack);

        fComboBox.valueProperty().addListener(new ChangeListener<Object>() {
            @SuppressWarnings("rawtypes")
            @Override
            public void changed(ObservableValue ov, Object arg1,
                    Object arg2) {
                if (arg2 instanceof SimpleEntry<?,?>) {
                    SimpleEntry<Integer, String> entry = (SimpleEntry<Integer, String>) arg2;
                    fKeyValue.setText("Key: " + entry.getKey() + " Value: " + entry.getValue());
                }
            }    
        });
        fComboBox.setPrefWidth(100.0);

        lKeyValueDefault.setText("Key : Value selected: ");
        lKeyValue.setText("Key : Value selected: ");

        GridPane grid = new GridPane();
        grid.setVgap(4);
        grid.setHgap(10);
        grid.setPadding(new Insets(5, 5, 5, 5));
        grid.add(lComboBoxDefault, 0, 0);
        grid.add(fComboBoxDefault, 1, 0);
        grid.add(lKeyValueDefault, 2, 0);
        grid.add(fKeyValueDefault, 3, 0);
        grid.add(lComboBox, 0, 1);
        grid.add(fComboBox, 1, 1);
        grid.add(lKeyValue, 2, 1);
        grid.add(fKeyValue, 3, 1);

        stage.setTitle("FX ComboBox HashMap Demo");
        Scene scene = new Scene(new Group(), 600,100);
        Group root = (Group)scene.getRoot();
        root.getChildren().add(grid);
        stage.setScene(scene);
        stage.show();
    }

    private List<SimpleEntry<Integer, String>> populateHashMap() {

        List<SimpleEntry<Integer, String>> data = new ArrayList<SimpleEntry<Integer, String>>();
        data.add(new SimpleEntry<Integer, String>(1, "Red"));
        data.add(new SimpleEntry<Integer, String>(2, "Blue"));
        data.add(new SimpleEntry<Integer, String>(3, "Green"));
        return data;
    }

    public class KeyValueFormatCell extends ListCell<Map.Entry<Integer, String>> {

        public KeyValueFormatCell() { }

        @Override protected void updateItem(Map.Entry<Integer, String> item, boolean empty) {
            super.updateItem(item, empty);

            setText(item == null ? "" : item.getValue());

            // For fun, change the text color to match the word
            if (item != null) {
                Paint fillColor = Color.BLACK;
                String color = item.getValue();
                if (color.equals("Red"))
                    fillColor = Color.RED;
                else if (color.equals("Blue"))
                    fillColor = Color.BLUE;
                if (color.equals("Green"))
                    fillColor = Color.GREEN;
                this.setTextFill(fillColor);
            }
        }
    }
}