MySQL database query from Java fails

Question

I have this table in MySQL:

CREATE TABLE CanteenMan.Foods (
 id TINYINT PRIMARY KEY AUTO_INCREMENT,
 type ENUM("soup","main","desert") DEFAULT "soup",
 name VARCHAR(30) NOT NULL,
 price FLOAT NULL DEFAULT NULL
)ENGINE = INNODB;

So if i want to insert new food, I should do this:

INSERT INTO Foods(id,type,name,price)
VALUES(NULL,"soup","somename",3.3);

And here is my problem now...I am using JDBC to add new foods in the table:

public void addFood(Meal f) throws ClassNotFoundException, SQLException {

     Class.forName (dbid.driver);
     Connection c = (Connection) DriverManager.getConnection(dbid.url,dbid.user,dbid.pass);
     PreparedStatement ps = (PreparedStatement) c.prepareStatement("INSERT INTO foods(id,type,name,price"
            + "VALUES(NULL,?,?,?)");
     ps.setString(1,f.getType()); // here throws SQLException
     ps.setString(2, f.getName());
     ps.setFloat(3,f.getPrice());

     int rowsInserted = ps.executeUpdate();

     System.out.println("Rows inserted:"+rowsInserted);
 }

But this throws SQL Exception because MySQL wants here

+ "VALUES(NULL,?,?,?)");

to surround the values with "".Is it any way to make this query to be accepted from the MySQL server ?

We need to see your Meal class, especially what the return value of getType() is. — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented Nov 23, 2013 at 8:32
@chrylis, I think the OP is wrong about the line where the error is — Paul Draper
– Paul Draper, Commented Nov 23, 2013 at 8:33

Kevin Bowersox · Accepted Answer · 2013-11-23 08:33:56Z

3

The sql statement is missing a closing right parenthesis for the list of columns.

PreparedStatement ps = 
(PreparedStatement) c.prepareStatement("INSERT INTO foods(id,type,name,price) "
                + "VALUES(NULL,?,?,?)");

Notice that after the price column is listed, I have inserted ).

edited Nov 23, 2013 at 8:33

answered Nov 23, 2013 at 8:32

Kevin Bowersox

94.6k19 gold badges164 silver badges198 bronze badges

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

Comments

M Khalid Junaid · Accepted Answer · 2013-11-23 08:41:42Z

0

You Can write query like this:

public void addFood(Meal f) throws ClassNotFoundException, SQLException {

 Class.forName (dbid.driver);
 Connection c = (Connection) DriverManager.getConnection(dbid.url,dbid.user,dbid.pass);
 PreparedStatement ps = (PreparedStatement) c.prepareStatement("INSERT INTO foods(id,type,name,price)
        VALUES(NULL,?,?,?)");
 ps.setString(1,f.getType()); // here throws SQLException
 ps.setString(2, f.getName());
 ps.setFloat(3,f.getPrice());

 int rowsInserted = ps.executeUpdate();

 System.out.println("Rows inserted:"+rowsInserted);
}

edited Nov 23, 2013 at 8:41

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

answered Nov 23, 2013 at 8:41

user2996174

1,5215 gold badges15 silver badges20 bronze badges

Comments

Krish R · Accepted Answer · 2013-11-23 08:41:42Z

0

foods table id is uniqueid and primary key , so it should be like,

PreparedStatement ps = (PreparedStatement) c.prepareStatement("INSERT INTO foods(type,name,price) " + "VALUES(?,?,?)");

answered Nov 23, 2013 at 8:41

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

Collectives™ on Stack Overflow

MySQL database query from Java fails

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related