what's wrong with my insert statement? mysql java

Question

what's wrong with my insert method? my table has two columns, name, and artist..and timestamp, that too actually, how do i pass timestamp argument to the insert statement?

ok.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            try {
                /*FileWriter dir = new FileWriter(nameOfSong.getText()
                        + ".txt");
                BufferedWriter buffer = new BufferedWriter(dir);
                buffer.write(nameOfSong.getText());
                buffer.newLine();
                buffer.write(artist.getText());
                buffer.newLine();
                buffer.newLine();
                buffer.write(lyrics.getText());
                buffer.close();
                */

                Statement statement = connection.createStatement();
                statement.executeUpdate("INSERT INTO lyrics1_lyrics1 VALUES(" +
                nameOfSong.getText() + ", " + artist.getText() + "");


            } catch (Exception z) {
                System.err.println("Error: " + z.getMessage());
            } 
            internalFrame.dispose();
        }
    });
)

You might consider changing to using a PreparedStatement to avoid the SQL injection vulnerability. — Andrew
– Andrew, Commented Dec 7, 2011 at 3:52
you need INSERT INTO TALBE_NAME ( [COLUMN1][, COLUMN2][, COLUMN_N] ) VALUES( [VALUE FOR COLUMN 1][, VALUE FOR COLUMN 2][, VALUE FOR COLUMN N] ); also note that strings needs to be surrounded by single quotes 'this is a string' — user497849
– user497849, Commented Dec 7, 2011 at 3:53
@DaveNewton really? suppose you have a table "TestTable" columns "ID", "Name", "Date" and you want to insert a new record with target for "Date" only with SQL: INSERT INTO "TestTable" VALUES( '2012-01-01' ); where will the engine insert the date value, in "ID", "Name" or "Date", it's not a tricky question, but I always thought that you need to specify target columns if number of values != number of columns, and in his case "name, and artist..and timestamp" he has 3 columns. — user497849
– user497849, Commented Dec 7, 2011 at 4:00
PLEASE, PLEASE, PLEASE work on making questions useful for future people: If you can't it likely "isn't a real question". E.g. "what doesn't work"? At least part of this should be in the title! Well, it's obvious to many of us ;-) but take some time to include the error message/symptoms! They are also great terms to SEARCH SO with. — user166390
– user166390, Commented Dec 7, 2011 at 4:05

KV Prajapati · Accepted Answer · 2011-12-07 03:53:50Z

6

Always use PreparedStatement.

 String sql="INSERT INTO lyrics1_lyrics1 VALUES (?,?)";
 PreparedStatement statement = connection.prepareStatement(sql);
 statement.setString(1,nameOfSong.getText());
 statement.setString(2,artist.getText());
 statement.executeUpdate();
 statement.close();
 connection.close();

answered Dec 7, 2011 at 3:53

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

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

Comments

Dave Newton · Accepted Answer · 2011-12-07 03:55:11Z

4

The text values need to be surrounded by single quotes ('').

And SQL-escaped to avoid SQL injection attacks, or the first time you have a song by Little Bobby Tables, all your DB are belong to him.

Better yet, use a PreparedStatement, and let the machine do work for you.

edited Dec 7, 2011 at 3:55

answered Dec 7, 2011 at 3:49

Dave Newton

161k27 gold badges264 silver badges311 bronze badges

Comments

Arun P Johny · Accepted Answer · 2011-12-07 05:31:40Z

3

You can use prepared statement for it

String query = "INSERT INTO lyrics1_lyrics1(name, artist, timestamp) values(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, name); // set input parameter 2
pstmt.setString(2, artist);
pstmt.setString(3, new TimeStamp(new Date().getTime()));

You need to add an import statement for the TimeStap;

import java.sql.Timestamp;

or else use

pstmt.setString(3, new java.sql.TimeStamp(new Date().getTime()));

Example: Prepared Statement Insert.

You can find a lot of example in java2s site.

edited Dec 7, 2011 at 5:31

answered Dec 7, 2011 at 3:54

Arun P Johny

389k68 gold badges532 silver badges532 bronze badges

3 Comments

Jad J Over a year ago

ok learning from a book, didn't about prepared statements, now i do..on the timestamp statement, it has error?

Jad J Over a year ago

statement.setString(3, new TimeStamp(new Date().getTime()));

Dave Newton Over a year ago

@JadJ java.sql.Timestamp, it needs to be imported. Consider keeping a window with the API docs open; things will go faster :)

ysrb · Accepted Answer · 2011-12-07 03:50:43Z

2

Change the line to:

statement.executeUpdate("INSERT INTO lyrics1_lyrics1 VALUES('" +
                    nameOfSong.getText() + "', '" + artist.getText() + "'");

answered Dec 7, 2011 at 3:50

ysrb

6,7202 gold badges32 silver badges30 bronze badges

Comments

Jonathan Spooner · Accepted Answer · 2011-12-07 06:22:42Z

1

This might solve your problem:

statement.executeUpdate("INSERT INTO lyrics1_lyrics1 VALUES('" + nameOfSong.getText() + "', '" + artist.getText() + "')");`

edited Dec 7, 2011 at 6:22

Jonathan Spooner

7,7622 gold badges38 silver badges41 bronze badges

answered Dec 7, 2011 at 5:38

Naveed S

5,3064 gold badges37 silver badges52 bronze badges

Collectives™ on Stack Overflow

what's wrong with my insert statement? mysql java

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related