Java SQL-statement problems

Question

I have a table in a database, list, and the columns are NR, FNAME, SNAME and ADDRESS. I also have a person class with all these attributes. Now I want to add a Person to the database.

Statement stmt = anslutning.createStatement();
 stmt.executeUpdate("INSERT INTO person (nr, fnamn, snamn, address) VALUES (" + p.getNr() + "," + p.getFname()+ "," + p.getSname()+","+ p.getAddress() + ")");

If the p.getFname is "Hank" then I get this error message:

mar 08, 2014 11:30:44 FM dblab.PersonAccessor addPerson
Allvarlig: null
java.sql.SQLException: Column not found: HANK
 at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
 at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
 at org.hsqldb.jdbc.jdbcStatement.executeUpdate(Unknown Source)
 at dblab.PersonAccessor.läggTillPerson(PersonAccessor.java:82)
 at dblab.LäggTill.actionPerformed(LäggTill.java:139)
 at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
 at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
 at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
 at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
 at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
 at java.awt.Component.processMouseEvent(Component.java:6505)
 at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
 at java.awt.Component.processEvent(Component.java:6270)
 at java.awt.Container.processEvent(Container.java:2229)
 at java.awt.Component.dispatchEventImpl(Component.java:4861)
 at java.awt.Container.dispatchEventImpl(Container.java:2287)
 at java.awt.Component.dispatchEvent(Component.java:4687)
 at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
 at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
 at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
 at java.awt.Container.dispatchEventImpl(Container.java:2273)
 at java.awt.Window.dispatchEventImpl(Window.java:2719)
 at java.awt.Component.dispatchEvent(Component.java:4687)
 at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
 at java.awt.EventQueue.access200ドル(EventQueue.java:103)
 at java.awt.EventQueue3ドル.run(EventQueue.java:694)
 at java.awt.EventQueue3ドル.run(EventQueue.java:692)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.security.ProtectionDomain1ドル.doIntersectionPrivilege(ProtectionDomain.java:76)
 at java.security.ProtectionDomain1ドル.doIntersectionPrivilege(ProtectionDomain.java:87)
 at java.awt.EventQueue4ドル.run(EventQueue.java:708)
 at java.awt.EventQueue4ドル.run(EventQueue.java:706)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.security.ProtectionDomain1ドル.doIntersectionPrivilege(ProtectionDomain.java:76)
 at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
 at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
 at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
 at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
 at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
 at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
 at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

Hank

• Henry: I don't see what is causing this error. But you should never construct an SQL statement like this, it opens an SQL injection vulnerability. Use prepared statements instead.
• user330315: Character literals need to be encloses in single quotes. Please go through a SQL (beginners) tutorial.

Answer 1

I think you need to insert the String value with single quotes, like this:

Statement stmt = anslutning.createStatement();
stmt.executeUpdate("INSERT INTO person (nr, fnamn, snamn, address) VALUES (" + p.getNr() + ",'" + p.getFname()+ "','" + p.getSname()+"', '"+ p.getAddress() + "')");

And i suggest not to use Statement, use PreparedStatement like this:

PreparedStatement stmt = anslutning.prepareStatement("INSERT INTO person (nr, fnamn, snamn, address) VALUES (?, ?, ?, ?)");
stmt.setString(1, p.getNr());
stmt.setString(2, p.getFname());
stmt.setString(3, p.getSname());
stmt.setString(4, p.getAddress());

Ream More About PreparedStatement

Answer 2

Never, ever do that:

stmt.executeUpdate("INSERT INTO person (nr, fnamn, snamn, address) 
 VALUES (" + p.getNr() + "," + p.getFname()+ "," + p.getSname()+","+ p.getAddress() + ")");

The error you see is because p.getFname() is not quoted, so MySQL assumes it's a column name.

Use a prepared statement instead:

PreparedStatement stm = anslutning.prepareStatement("INSERT INTO person (nr, fnamn, snamn, address) VALUES (?, ?, ?, ?)");
stm.setInt(1, p.getNr());
stm.setString(2, p.getFname());
stm.setString(3, p.getSname());
stm.setString(4, p.getAddress());
stm.executeUpdate();

• SpringLearner: PreparedStatement has setInt() docs.oracle.com/javase/7/docs/api/java/sql/…, int)

Answer 3

You have to include quotes in you query. Something like this:

"INSERT INTO person (nr, fnamn, snamn, address) VALUES (" + p.getNr() + ",'" + p.getFname()+ "','" + p.getSname()+"','"+ p.getAddress() + "')"

Answer 4

Always try to go for PreparedStatement and it removes the confusion of adding quotes(') for varchars

I assume that nr is int and rest all (fnamn, snamn, address) are varchars

So By using preparedstatement

PreparedStatement stmt = anslutning.prepareStatement("insert into person(nr, fnamn, snamn, address) values(?,?,?,?)");
 stmt.setInt(1,p.getNr());
stmt.setString(2,p.getFname());
stmt.setString(3,p.getSname());
stmt.setString(4,p.getAddress());
stmt.executeUpdate();

Answer 5

you should change and type to get the solution:

Statement stmt = anslutning.createStatement();
stmt.executeUpdate("INSERT INTO 
 person (nr, fnamn, snamn, address)VALUES (?,?,?,?)");