Syntax for SQL statement using mysql database

Question

public void insertTags(Elements[] elements) {

    Connection con = (Connection) DbConnection.getConnection();

    try {
        String sql = "insert into htmltags(source) values(?),(?),(?)";
        PreparedStatement ps = (PreparedStatement) con.prepareStatement(sql);
        ps.setString(1, elements[0].toString());
        ps.setString(2, elements[1].toString());
        ps.setString(3, elements[2].toString());
        int rs = ps.executeUpdate(sql);
        System.out.println("Data inserted" + rs);

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

is this a valid syntax for Prepared statement.

You can't insert 3 values into one column "source", and the syntax would need to be values(?,?,?). — Gerard H. Pille
– Gerard H. Pille, Commented Jan 23, 2018 at 11:11

Tim Biegeleisen · Accepted Answer · 2018-01-23 11:13:54Z

2

This is your problem:

int rs = ps.executeUpdate(sql);

From the JavaDoc we see that PreparedStatement#executeUpdate() does not take any parameters. The reason is that we already passed the query earlier when preparing the statement. Your code should be this:

int rs = ps.executeUpdate();  // no parameter

Also no need to cast the result of prepareStatement to PrepareStatement

edited Jan 23, 2018 at 11:13

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Jan 23, 2018 at 11:11

Reimeus

160k16 gold badges225 silver badges282 bronze badges

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

2 Comments

Tim Biegeleisen Over a year ago

Of note, many tutorials also seem to make this mistake (e.g. MkYong, at least as of several months ago when I last checked).

Reimeus Over a year ago

in fact its incorrect here. Maybe youre trying to concatenate values. In that case only use one placeholder and concatenate in the java code...

Youcef LAIDANI · Accepted Answer · 2018-01-23 12:33:26Z

0

To insert multiple values, I don't thing using values(?),(?),(?) is the right syntax, instead use a loop, or for better way you can use batch :

String sql = "insert into htmltags(source) values(?)";
try (PreparedStatement ps = con.prepareStatement(sql);) {
    for (Elements element : elements) {
        ps.setString(1, element.toString());
        ps.addBatch();//Add a new batch for each Element
    }

    int[] result = ps.executeBatch();//Submits a batch of commands to the database

} catch (SQLException e) {
    e.printStackTrace();
}

answered Jan 23, 2018 at 12:33

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Collectives™ on Stack Overflow

Syntax for SQL statement using mysql database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related