Insert data into mySQL table with java

Question

I have a predefined table in mySQL database:

I am working on saving the data entered by the user into the database, but I can not find any data to save in the database. In 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 this will not work, because the number of values ​​is less than the number of columns in your table. You need to specify the column name according to 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 

he should be

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

w3School: (INSERT)

You can write an INSERT INTO statement in two forms.

The first form does not specify the names of the columns into which the data will be inserted, only their values:

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

The second form defines the column names and 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 application is missing column names.

Answer 3

You need to specify column names, for example:

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

Answer 4

A. Or pass values ​​for all columns in the table in the order in which they are defined in the MYSQL database. Here we can see 7 columns in your table, and you provide only 5 values ​​...

or

B. Use this syntax

 INSERT INTO TABLE_NAME (COLUMN_NAMES_SEPARATED_BY_COMMA) VALUES (VALUES_SEPARATED_BY_COMMA) 

Answer 5

like munyengm, an expression made by you would be: "INSERT INTO incomeCalc (ID, TIPS, CLOCK, GAS, HOURLY_EARNINGS)" + "VALUES (3, 75, 6, 25, 18.50)"

but you are probably going to use the value of the loop and stuff, so I would recommend using a prepared statement instead, this will prevent sql injection.

 ps = "INSERT INTO incomeCalc(ID, TIPS, HOURS, GAS, HOURLY_EARNINGS) VALUES (?, ?, ?, ?, ?)" 

then

  ps.setInt(index++, id); //id=3 ps.setInt(index++, tips); //tips=75 ps.setInt(index++, hours);//hour=6 ps.setInt(index++, gas);//gas=25 ps.setFloat(index++, HOURLY_EARNINGS); 

Answer 6

First READONLY = false;

There are some limitations. You need to put [Name_table$] and \ before and after COLUMN_NAMES . eg:

 st.executeUpdate("INSERT INTO [IncomeCalc$] (\"ID\", \"TIPS\", \"HOURS\", \"GAS\",\"HOURLY_EARNINGS\") VALUES ('2012-10-25 01:00:00','16.3')"); 

Answer 7

This format worked for me, as you can see, there were no additional commas, slashes, or anything else:

 statement.executeUpdate("INSERT INTO incomeCalc (value1, value2, value99) VALUES (3, 75, 6)");