Java JDBC with mySql - passing id from table

Question

Here is the code:

public static Connection getConnection() throws Exception {

    String name1 = "Danny";
    String city1 = "Wien";

    try {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/supermarket??verifyServerCertificate=false&useSSL=true";
        String username = "myuser";
        String password = "mypass";
        Class.forName(driver);

        Connection conn = DriverManager.getConnection(url, username, password);

        String sql = "insert into marketinfo "
                + " (name, country)" + " values (" + name1 + ", " + city1 + ")";

        Statement insertie = conn.prepareStatement(sql);

        insertie.executeUpdate(sql);

    } catch (Exception e) {
        System.out.println(e);
    }

    return null;
}

My error is "Unknown column 'Danny' in 'field list'" .

In Sql database my table contains id, name and city. I want to pass the id field because that id is incremented automatically.

score 1 · Answer 1 · answered Nov 06 '17 at 19:38

1

There's alot going on in that code, and as others have suggested you should break it up. But actually performing the query can be done like this:

public class YourClass {
    private static final String SQL = "INSERT INTO marketinfo (name, country) VALUES (?,?)";

    public void addMarketInfo(String name, String city) {
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(SQL)) {

            stmt.setString(1, name);
            stmt.setString(2, city);
            stmt.executeUpdate();

        } catch (SQLException e) {
            // This is fine for debugging, but you probably want to log this or throw an exception
            // Depends on how the rest of your application is set up
            e.printStackTrace();
        }
    }
}

All your code creating the connection should most likely get moved to another class, and then called by the getConnection() method as in my example.

If you're using JDBC, PreparedStatements are used ALOT. It's worth looking more more examples on how they are used. Among other benefits, they're really helpful for avoiding string concatenation bugs like your original question.

answered Nov 06 '17 at 19:38

GridDragon

2,857
2
33
41

You aren't closing your Connection or PreparedStatement in method scope - bad idea. This might be a good place for try with from JDK 8. https://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc – duffymo Nov 06 '17 at 20:06
How am I not closing them? They're in a try-with-resources block. Sure I haven't put the `stmt.set()` calls into a separate method like the answer you linked to. But I'm not dealing with a ResultSet object so it doesn't seem necessary. Is there another facet of this I missed? – GridDragon Nov 06 '17 at 20:17
1

I see Connection there. I'm not aware that it accrues to PreparedStatement. Could be my failure, not yours. That's why I didn't downvote. – duffymo Nov 06 '17 at 20:36
1

No worries. I haven't found a good way to format try-with-resources so they are more readable. The one I used is `try(conn; stmt) {}` so both the connection and the statement are auto-closed. The line break may have made that hard to see though. – GridDragon Nov 06 '17 at 20:51

score 0 · Answer 2 · answered Nov 06 '17 at 19:24

This is the wrong way to do it. You should learn about PreparedStatement and how to bind values to parameters.

But it's worse than that.

Your method is getConnection, but it's also performing the query. Methods should do one thing well.

You don't close any of your resources. Another bad idea.

You print a stack trace to the console. Better to log it.

You hard wire your connection parameters instead of passing them in.

There's no connection pooling here.

score 0 · Answer 3 · answered Nov 06 '17 at 19:24

0

seems you missed inner quotes around var name1 and city1

 String sql = "insert into marketinfo "
        + " (name, country)" + " values ('"+ name1 +"', '"+ city1 +"')";

but most important you should use parametrized query instead of string concat .

answered Nov 06 '17 at 19:24

ScaisEdge

131,976
10
91
107

score 0 · Answer 4 · answered Nov 06 '17 at 19:25

To fix this you need to quote your variables in the SQL:

String sql = "insert into marketinfo "
            + " (name, country)" + " values ('"+ name1 +"', '"+ city1 +"')";

However, this is awful code, and you should not do it like this.

See here for why not: https://www.acunetix.com/websitesecurity/sql-injection/

As a hit, your sql should look like this:

String sql = "insert into marketinfo "
                + " (name, country)" + " values (:name, :city)";

Then, you use a prepared statement to set the values. Code like this is why websites get all their private information stolen.

score 0 · Answer 5 · answered Nov 06 '17 at 19:31

String or varchar type should be between two quotes 'some string', but this still not secure so to avoid Syntax errors (Like you have now) or SQL Injection it's better to use PreparedStatement :

String sql = "insert into marketinfo (name, country) values (?, ?)";

try(PreparedStatement insertie = con.prepareStatement(sql);){
    insertie.setString(1, name1);
    insertie.setString(2, city1);
    insertie.executeUpdate();
    //...
}

Java JDBC with mySql - passing id from table

5 Answers5