How do I update my Sql database using Java

Question

public void actionPerformed(ActionEvent e) {
    String action = e.getActionCommand();
    if(action.equals("Reset")){
        title.setSelectedIndex(0);
        forename.setText(null);
        surname.setText(null);
        age.setText(null);
        contactNo.setText(null);
        houseNo.setText(null);
        streetName.setText(null);
        districtName.setText(null);
        cityName.setText(null);
        postcode.setText(null);
        healthPlan.setSelectedIndex(0);
        treatment.setSelectedIndex(0);  

    }

How do I update my sql database with data from the textfield when I click the "Submit" button. I have written this code so far but nothing seems to be happening when I click submit, but the reset button works when it is clicked.

    if(action.equals("Submit")){
        Connection con = null; 
        String db="jdbc:mysql://stusql.dcs.shef.ac.uk/team009?user=team009&password=********";

        try { 

           con = DriverManager.getConnection(db);
           Statement bil = con.createStatement();

           bil.execute("INSERT INTO Patient (ForeName, SurName, DateOfBirth, PhoneNumber, HouseNumber, PostCode) " + 
            "VALUES (forename.getText(),surname.getText,age.getText,contactNo.getText(),houseNo.getText(),postcode.getText())");

           JOptionPane.showMessageDialog(null,"Inserted Successfully!");

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

                e1.printStackTrace();
            }

        }

Please any help would be appreciated thanks.

Your current "query" should throw an exception - define "but nothing seems to be happening when I click submit" — MadProgrammer
– MadProgrammer, Commented Dec 2, 2014 at 23:46
when I click submit the form remains the same nothing changes and the database isn't updated. — theBean
– theBean, Commented Dec 2, 2014 at 23:48
Assuming that no exception is printed to the console and that the "Inserted Successfully!" is not displayed, you actionCommand could be wrong. Start by adding some debugging statements to track the flow through your code (printing the actionCommand to start with and some message about where you are in the code), if that doesn't seem to help, add break points and begin debugging your code... — MadProgrammer
– MadProgrammer, Commented Dec 2, 2014 at 23:51

MadProgrammer · Accepted Answer · 2014-12-02 23:46:01Z

1

Basically, you current query String

"INSERT INTO Patient (ForeName, SurName, DateOfBirth, PhoneNumber, HouseNumber, PostCode) " + 
        "VALUES (forename.getText(),surname.getText,age.getText,contactNo.getText(),houseNo.getText(),postcode.getText())"

Is literally asking the database to insert the text you have supplied and NOT the text from text fields, in fact, the database should pretty much have a fit over this and throw back an Exception. At best, it's trying to find table/column values matching things like surname.getText at worst it's looking for functions called forename.getText()

Start by taking a look at:

For example...

try (Connection con = DriverManager.getConnection(null)) {
    try (PreparedStatement stmt = con.prepareStatement("INSERT INTO Patient (ForeName, SurName, DateOfBirth, PhoneNumber, HouseNumber, PostCode) VALUES (?,?,?,?,?,?)")) {
        stmt.setString(1, forename.getText());
        stmt.setString(2, surname.getText());
        stmt.setString(3, age.getText());
        stmt.setString(4, contactNo.getText());
        stmt.setString(5, houseNo.getText());
        stmt.setString(6, postcode.getText());
        // In case you care, you can get the number of rows that were updated...
        int rowsUpdated = stmt.executeUpdate();
    }
} catch (SQLException ex) {
    Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
}

And you might find The try-with-resources Statement useful...

answered Dec 2, 2014 at 23:46

MadProgrammer

348k22 gold badges243 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

theBean Over a year ago

This triggers the message but the database isn't updated

MadProgrammer Over a year ago

Then you probably have autocommit turned off. Try adding con.commit() after the executeUpdate statement

afzalex · Accepted Answer · 2014-12-03 01:05:06Z

0

You were not sending the values of forename, surname etc. but instead sending the string for calling these values in java. You are needed to that part of your code with this : -

bil.execute("INSERT INTO Patient (ForeName, SurName, DateOfBirth, PhoneNumber, HouseNumber, PostCode) VALUES ("'
    + forename.getText() + "', '"
    + surname.getText() + "', '"
    + age.getText() + "', '"
    + contactNo.getText() + "', '"
    + houseNo.getText() + "', '"
    + postcode.getText() + "')");

edited Dec 3, 2014 at 1:05

answered Dec 2, 2014 at 23:58

afzalex

8,6602 gold badges39 silver badges66 bronze badges

8 Comments

theBean Over a year ago

thanks for the help but I just wanted to say the last line + postcode.getText())");xt,contactNo.getText(),houseNo.getText(),postcode.getText())"); is giving me an error

afzalex Over a year ago

I am sorry. I mistakenly didn't marked that in hurry.

theBean Over a year ago

after trying this answer i am getting a com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'alex' in 'field list',when i run the code and input alex in the name textfield

afzalex Over a year ago

To debug your code write following line just before execution and check the output :

System.out.println("INSERT INTO Patient (ForeName, SurName, DateOfBirth, PhoneNumber, HouseNumber, PostCode) VALUES ("     + forename.getText() + ", "     + surname.getText() + ", "     + age.getText() + ", "     + contactNo.getText() + ", "     + houseNo.getText() + ", "     + postcode.getText() + ")")

afzalex Over a year ago

And I wanna remark that if MadProgrammer's answer is working than this will also work. You might be doing some other mistake.

|

Collectives™ on Stack Overflow

How do I update my Sql database using Java

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related