Fetch data from database into Jtextfield when One Jtextfield is Typed

Question

Hi Guys I have a Swing Application that is connected to an oracle database, I want it such that Once I type a Value into the JTextField, the other JTextfields on the JFrame are loaded with subsequent data from the database but I do not seem to achieve this. I have tried the Following code but it fetched nothing.

txtNo.addKeyListener(new KeyAdapter() {
        public void keyTyped(KeyEvent ke) {
            Connection conn = null;
            try{
                Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "Username", "Password");
        Statement st = conn.createStatement();
        String load = "Select * from Store_info_table where PART_NUMBER = '" + txtNo.getText() + "'";
        ResultSet rs = st.executeQuery(load);
        while(rs.next()){
            txtName.setText(rs.getString("SPARE_DESC"));
        }
            }catch(Exception ae){

            }
        }
    });

Answer 1

Both Robin and Nick are right.

Here's an example of how to implement what they are discussing...

public class TestForm02 {

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

    public TestForm02() {
        EventQueue.invokeLater(new Runnable() {

            @Override
            public void run() {

                try {
                    UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
                } catch (ClassNotFoundException ex) {
                } catch (InstantiationException ex) {
                } catch (IllegalAccessException ex) {
                } catch (UnsupportedLookAndFeelException ex) {
                }

                JFrame frame = new JFrame();
                frame.setLayout(new BorderLayout());
                frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                frame.add(new MyForm());
                frame.pack();
                frame.setLocationRelativeTo(null);
                frame.setVisible(true);

            }
        });
    }

    protected class MyForm extends JPanel {

        private JTextField txtNo;
        private JTextField txtName;
        private String partToLoad;
        private Timer loadTimer;

        public MyForm() {

            setLayout(new GridBagLayout());
            GridBagConstraints gbc = new GridBagConstraints();
            gbc.gridx = 0;
            gbc.gridy = 0;
            gbc.insets = new Insets(2, 2, 2, 2);
            gbc.anchor = GridBagConstraints.WEST;

            txtName = new JTextField(12);
            txtNo = new JTextField(12);
            txtName.setEnabled(false);

            add(new JLabel("Parts #:"), gbc);
            gbc.gridx++;
            add(txtNo, gbc);

            gbc.gridy++;
            gbc.gridx = 0;
            add(new JLabel("Description:"), gbc);
            gbc.gridx++;
            add(txtName, gbc);

            txtNo.addFocusListener(new FocusAdapter() {
                @Override
                public void focusLost(FocusEvent e) {
                    loadParts();
                }
            });

            txtNo.getDocument().addDocumentListener(new DocumentListener() {
                protected void update() {
                    loadTimer.restart();
                }

                @Override
                public void insertUpdate(DocumentEvent e) {
                    update();
                }

                @Override
                public void removeUpdate(DocumentEvent e) {
                    update();
                }

                @Override
                public void changedUpdate(DocumentEvent e) {
                    update();
                }
            });

            loadTimer = new Timer(250, new ActionListener() {
                @Override
                public void actionPerformed(ActionEvent e) {
                    loadParts();
                }
            });
            loadTimer.setRepeats(false);
            loadTimer.setCoalesce(true);

        }

        protected void loadParts() {

            String text = txtNo.getText();
            // Don't want to trigger this twice...
            if (text == null ? partToLoad != null : !text.equals(partToLoad)) {
                partToLoad = text;
                txtNo.setEnabled(false);
                txtName.setEnabled(false);
                BackgroundWorker worker = new BackgroundWorker();
                worker.execute();
            }

        }

        protected class BackgroundWorker extends SwingWorker<String, String> {

            @Override
            protected String doInBackground() throws Exception {

                // Do you're database load here.  Rather then updating the text
                // field, assign it to variable and return it from here

                String desc = "Some description"; // load me :D

                // Inserted delay for simulation...
                Thread.sleep(2000);

                return desc;

            }

            @Override
            protected void done() {
                try {
                    String value = get();
                    txtName.setText(value);

                    txtName.setEnabled(true);
                    txtNo.setEnabled(true);
                } catch (InterruptedException exp) {
                    exp.printStackTrace(); // Log these some where useful
                } catch (ExecutionException exp) {
                    exp.printStackTrace(); // Log these some where useful
                }
            }
        }
    }
}

Answer 2

Thank you guys for the Help. I got what I wanted Using a FocusListener as suggested by Nick:

 txtNo.addFocusListener(new FocusAdapter() {
        public void focusLost(FocusEvent e) {
            Connection conn = null;
            try{
                Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "Username", "Password");
        Statement st = conn.createStatement();
        String load = "Select * from Store_info_table where PART_NUMBER = '" + txtNo.getText().trim() + "'";
        ResultSet rs = st.executeQuery(load);
        while(rs.next()){
            txtName.setText(rs.getString("SPARE_DESC"));

        }
            }catch(Exception ae){

            }
        }
    });

Answer 3

• Replace the KeyListener by a DocumentListener
• The database connection on the EDT is a bad idea (too slow). Consult the concurrency in Swing guide for more information
• You are vulnerable to SQL injection
• Avoid empty catch statements or you have no idea that something is going wrong. If you do not opt for decent error handling, at least log the stack trace (either by just printing it, or by using a Logger).

Answer 4

Do you know if you're database connection is working? For example, can you run the database portion outside of the listener and it works?

If so, I would suggest using ActionListeneror a FocusListener instead. KeyListeners (while sometimes necessary) are generally clumsy - there's usually a better approach (see Java Swing: Using ActionMap for and explaination):

import java.awt.event.*;
import javax.swing.*;

public class Example extends Box{

    JLabel txtName = new JLabel("Nothing Entered");

    public temp(){
        super(BoxLayout.Y_AXIS);
        // Add FocusListener to first field
        final JTextField txtNo = new JTextField(20);
        txtNo.addFocusListener(new CustomFocusListener(txtNo));
        add(txtNo);

        // Add TextListener to first field
        final JTextField txtNo2 = new JTextField(20);
        txtNo2.addFocusListener(new CustomFocusListener(txtNo2));
        add(txtNo2);

        // Add TextListener to first field
        final JTextField txtNo3 = new JTextField(20);
        txtNo3.addFocusListener(new CustomFocusListener(txtNo3));
        add(txtNo3);

        add(new JButton("Do Something"));

        add(txtName);

    }


    public static void main(String[] args){
        final JFrame frame = new JFrame();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.add(new Example());
        frame.pack();
        frame.setLocationRelativeTo(null);
        frame.setVisible(true);
    }

    /**
     * Your custom focust listener that does all your SQL Work
     */
    public class CustomFocusListener extends FocusAdapter{
        JTextField field;

        public CustomFocusListener(JTextField field){
            this.field = field;
        }

        @Override
        public void focusLost(FocusEvent e) {
            //Assuming your database connection works, this gives you an example to follow
            txtName.setText(field.getText());
            /*Connection conn = null;
            try{
                Class.forName("oracle.jdbc.driver.OracleDriver");

                conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "Username", "Password");
                Statement st = conn.createStatement();
                String load = "Select * from Store_info_table where PART_NUMBER = '" + field.getText() + "'";
                ResultSet rs = st.executeQuery(load);
                while(rs.next()){
                   txtName.setText(rs.getString("SPARE_DESC"));
                }
            }catch(Exception ae){

            }*/
        }
    }
}