Insert data into mySQL table with java

Question

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

 }

If your not getting an error, change the SqlException to Exception and see what error you get — Orn Kristjansson
– Orn Kristjansson, Commented Aug 10, 2012 at 15:02
why use straight sql to do your insert and not a ORM like hibernate or Ibatis instead ? — Chris
– Chris, Commented Aug 10, 2012 at 15:05

user3542880 · Accepted Answer · 2014-05-02 13:16:24Z

9

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

edited May 2, 2014 at 13:16

user3542880

1951 gold badge1 silver badge8 bronze badges

answered Aug 10, 2012 at 15:04

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

munyengm · Accepted Answer · 2012-08-10 15:24:09Z

3

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.

edited Aug 10, 2012 at 15:24

answered Aug 10, 2012 at 15:03

munyengm

15.6k4 gold badges26 silver badges34 bronze badges

2 Comments

Mark Rotteveel Over a year ago

Columnnames in an INSERT statement are optional, even though it is not wise to not specify them, see dev.mysql.com/doc/refman/5.5/en/insert.html

AlenBer Over a year ago

Column names are NOT optional if you have a insert statement with less columns than real column number in the table.

Lo Juego · Accepted Answer · 2012-08-10 15:06:14Z

1

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

answered Aug 10, 2012 at 15:06

Lo Juego

1,32511 silver badges12 bronze badges

Comments

Bharat Sinha · Accepted Answer · 2012-08-10 15:06:59Z

1

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)

answered Aug 10, 2012 at 15:06

Bharat Sinha

14.4k7 gold badges45 silver badges64 bronze badges

Comments

Chris · Accepted Answer · 2012-08-10 15:15:52Z

1

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

answered Aug 10, 2012 at 15:15

Chris

1,1872 gold badges23 silver badges49 bronze badges

Comments

kapa · Accepted Answer · 2012-10-25 12:52:28Z

1

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

edited Oct 25, 2012 at 12:52

kapa

78.9k21 gold badges166 silver badges178 bronze badges

answered Oct 25, 2012 at 12:34

Guga

111 bronze badge

Comments

Daniel · Accepted Answer · 2013-02-19 19:39:09Z

1

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

edited Feb 19, 2013 at 19:39

Daniel

11.1k22 gold badges90 silver badges120 bronze badges

answered Feb 19, 2013 at 19:18

Dan

111 bronze badge

Collectives™ on Stack Overflow

Insert data into mySQL table with java

7 Answers 7

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related