DB2 and Java. Adding data to database through GUI.

Question

First I create a search function in my program and I implemented the same logic in adding data into the database but the search function works and the add function didn't (SQLException). I created a table from DB2 named Names with only one column FullName. Do you still need to create a new query to add data into the table? or not? I want to add data into the database through GUI.

Here is my Java Code:

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

public class ConnectAndSearchDB extends JFrame implements ActionListener
{
    private JTextField fieldSearch,fieldAdd;
    private JButton searchB,addB;
    private Connection connection;
    private String name;
    private ResultSet rs,rs1;

    public ConnectAndSearchDB() throws SQLException,ClassNotFoundException
    {
        setLocationRelativeTo(null);
        setDefaultCloseOperation(this.EXIT_ON_CLOSE);
        setLayout(new GridLayout(2,2));

        fieldSearch = new JTextField(20);
        searchB = new JButton("Search");
        fieldAdd = new JTextField(20);
        addB = new JButton("Add");

        add(searchB);
        add(fieldSearch);
        add(addB);
        add(fieldAdd);

        searchB.addActionListener(this);
        addB.addActionListener(this);

        establishConnection();

        pack();

        setResizable(false);
        setVisible(true);
    }

    public void actionPerformed(ActionEvent e)
    {
        Object act = e.getSource();

        if(act.equals(searchB))
        {
            name = fieldSearch.getText();
            searchData();
        }else if(act.equals(addB))
        {
            try {
                addData();
            } catch (ClassNotFoundException e1)
            {
                e1.printStackTrace();
                System.out.println("ClassNotFound");
            } catch (SQLException e1)
            {   
                e1.printStackTrace();
                System.out.println("SQLError");
            }
        }   
    }

    public void establishConnection() throws SQLException , ClassNotFoundException
    {
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        connection = DriverManager.getConnection("jdbc:db2://localhost:50000/COLINN", "Colinn","ezioauditore");     
    }


    private void searchData()
    {
        try
        {
            PreparedStatement s = null;
            String query;
            query = "SELECT * from NAMES";

            s=connection.prepareStatement(query);
            rs = s.executeQuery();

            boolean matchfound = false;

            while(rs.next())
            {
                if(rs.getString(1).equals(name))
                {
                    matchfound = true;
                    System.out.println("The name "+name+" is found in the Database");
                    break;
                }
            }

            if(matchfound == false)
            {
                System.out.println("Match Not Found");
            }   
        }
        catch(SQLException e)
        {
            e.printStackTrace();

        }
    }

    public void addData() throws ClassNotFoundException,SQLException
    {
        PreparedStatement ps = null;
        String query;
        query = "INSERT INTO NAMES VALUES('"+fieldAdd.getText()+"')";

        ps = connection.prepareStatement(query);
        rs1 = ps.executeQuery();

        System.out.println("Written Successfully");
    }

    public static void main (String args[]) throws SQLException,ClassNotFoundException
    {
        EventQueue.invokeLater(new Runnable() 
        {
            public void run()
            {
                try 
                {
                    new ConnectAndSearchDB();

                } catch (ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        });
    }
}

Here is the Names table:

Answer 1

Not an answer per se, but a series of extended comments about some issues with the overall approach...

Lesson #1 - Resource management

If you open it, you should close it...

String query = "SELECT * from NAMES";
try (PreparedStatement stmt = connection.prepareStatement(query)) {
    try (ResultSet rs = s.executeQuery()) {
        boolean matchfound = false;

        while(rs.next())
        {
            if(rs.getString(1).equals(name))
            {
                matchfound = true;
                System.out.println("The name "+name+" is found in the Database");
                break;
            }
        }

    if(matchfound == false)
    {
        System.out.println("Match Not Found");
    }   
}
catch(SQLException e)
{
    e.printStackTrace();
}

See The try-with-resources Statement for more details...

I'd also reconsider using a single Connection, while a Connection pool might be overkill for this problem, you could simply open and close a Connection as need, but remember, there is an overhead incurred when doing this...

Lesson #2 - Don't do what the database can do better

String query = "SELECT count(*) from NAMES where name=?";
try (PreparedStatement stmt = connection.prepareStatement(query)) {
    stmt.setString(1, name);
    try (ResultSet rs = s.executeQuery()) {
        boolean matchfound = false;

        if (rs.next())
        {
            if(rs.getLong(1) > 0)
            {
                matchfound = true;
                System.out.println("The name "+name+" is found in the Database");
            }
        }

    if(!matchfound)
    {
        System.out.println("Match Not Found");
    }   
}
catch(SQLException e)
{
    e.printStackTrace();
}

Lesson #3 - Make use of your PreapredStatement...

Instead of...

query = "INSERT INTO NAMES VALUES('"+fieldAdd.getText()+"')";

Use...

query = "INSERT INTO NAMES VALUES(?)";
//...
ps.setString(1, fieldAdd.getText());

See Using Prepared Statements

Answer 2

I highly suspect that using rs1 = ps.executeQuery(); to insert/update the database is the course of your issue, you should be using int count = ps.executeUpdate();

See PreparedStatement#executeUpdate for more details