CODEWITHSUNDEEP
javamysqlsqljdbc
David Tunnell
David Tunnell

Reputation: 7542

Insert data into mySQL table with java

I have a predefined table in a mySQL database: enter image description here

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);

 }

Upvotes: 6

Views: 83997

Answers (7)

John Woo
John Woo

Reputation: 263893

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,...)

Upvotes: 9

Dan
Dan

Reputation: 11

This format worked for me like you see it, no extra commas, slashes or anything else was necessary:

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

Upvotes: 1

Guga
Guga

Reputation: 11

First READONLY = false;

There are some restrictions.You need to put [Name_table$] and \ before and after the COLUMN_NAMES. like this:

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

Upvotes: 1

munyengm
munyengm

Reputation: 15479

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.

Upvotes: 3

Chris
Chris

Reputation: 1177

like munyengm the statement in you rcase would be : "INSERT INTO incomeCalc(ID, TIPS, HOURS, GAS, HOURLY_EARNINGS) " + "VALUES (3, 75, 6, 25, 18.50)"

but you probably going to loop value and stuff so I would recommend using a prepared statement instead, this 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);

Upvotes: 1

Bharat Sinha
Bharat Sinha

Reputation: 14373

A. Either pass the values for all the columns of the table in the order which they are defined in MYSQL DB. Here we can see 7 columns in your table and you are providing only 5 values...

or

B. Make use of this syntax

INSERT INTO TABLE_NAME (COLUMN_NAMES_SEPARATED_BY_COMMA) VALUES (VALUES_SEPARATED_BY_COMMA)

Upvotes: 1

Lo Juego
Lo Juego

Reputation: 1325

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)"

Upvotes: 1

Related Questions