Insert data into mySQL table with java

Question

I have a predefined table in a mySQL database:

I am working on saving data inputted from the user to the database but I cant seem to any data to save in the database. With the following code I am trying to update the first row of the database (ID: 1 through OTHER 2: 0). What am I doing wrong?

private java.sql.Connection con = null;
private PreparedStatement pst = null;
private ResultSet rs = null;
private String url = "jdbc:mysql://localhost:8889/deliveryEarn";
private String user = "root";
private String password = "root";

try {
     con = DriverManager.getConnection(url, user, password);
     Statement st = (Statement) con.createStatement(); 

     st.executeUpdate("INSERT INTO incomeCalc " + "VALUES (3, 75, 6, 25, 18.50)");

     con.close();
}

catch (SQLException ex) {
     Logger lgr = Logger.getLogger(deliveryMain.class.getName());
     lgr.log(Level.SEVERE, ex.getMessage(), ex);

 } 

Answer 1

I think it will not work because the number of values is less than the number of columns in your table. What you have to do is to specify the name of columns to match the number of your values.

INSERT INTO incomeCalc VALUES (3, 75, 6, 25, 18.50)  // error
// the only way this will work is when you have only 5 columns in 
// your table but in your case you have 7 that is why it will not work

it should be

INSERT INTO incomeCalc(specify columns here to the values bound to)
VALUES (3, 75, 6, 25, 18.50)

w3School: (INSERT)

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Answer 2

Your insert statement should be:

"INSERT INTO incomeCalc(ID, TIPS, HOURS, GAS, HOURLY_EARNINGS) " +
"VALUES (3, 75, 6, 25, 18.50)"

In other words your statement is missing column names.

Answer 3

You need to specify the column names for example:

"INSERT INTO incomeCalc (ID, TIPS, HOURS, GAS, HOURLY_EARNINGS) VALUES (3, 75, 6, 25, 18.50)"