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.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:708)
    at java.awt.EventQueue$4.run(EventQueue.java:706)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.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

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. — Henry
– Henry, Commented Mar 8, 2014 at 10:43
Character literals need to be encloses in single quotes. Please go through a SQL (beginners) tutorial. — user330315
– user330315, Commented Mar 8, 2014 at 10:47

Salah · Accepted Answer · 2014-03-08 10:50:55Z

2

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

edited Mar 8, 2014 at 10:50

answered Mar 8, 2014 at 10:43

Salah

8,7173 gold badges29 silver badges45 bronze badges

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

Comments

score 2 · Accepted Answer · 2014-03-08 11:12:23Z

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

edited Mar 8, 2014 at 11:12

answered Mar 8, 2014 at 10:45

user180100

1 Comment

SpringLearner Over a year ago

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

Engin Kayraklioglu · Accepted Answer · 2014-03-08 10:43:39Z

1

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() + "')"

answered Mar 8, 2014 at 10:43

Engin Kayraklioglu

5953 silver badges11 bronze badges

Comments

SpringLearner · Accepted Answer · 2014-03-08 11:08:04Z

1

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

edited Mar 8, 2014 at 11:08

answered Mar 8, 2014 at 10:51

SpringLearner

13.9k20 gold badges82 silver badges117 bronze badges

Comments

jmail · Accepted Answer · 2014-03-08 10:51:36Z

0

you should change and type to get the solution:

Statement stmt = anslutning.createStatement();

stmt.executeUpdate("INSERT INTO 

       person (nr, fnamn, snamn, address)VALUES (?,?,?,?)");

answered Mar 8, 2014 at 10:51

jmail

6,1503 gold badges23 silver badges35 bronze badges

Collectives™ on Stack Overflow

Java SQL-statement problems

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related