CODEWITHSUNDEEP
javasqljdbc
Lukáš Václavek
Lukáš Václavek

Reputation: 452

SQL type specifier

Hello guys we have problem, not knowing how to add type specifier for date type? We tried to use %% but this doesn't seem to work.

    public class OrderDB implements OrderDBIF {
@Override
public Order create(Date date, int totalAmount, String deliveryStatus, Date deliveryDate, int invoiceId, int customerId) throws SQLException {
    String sql = String.format("INSERT INTO Order (date, totalAmount, deliveryStatus, deliveryDate, invoiceId, customerId) VALUES "
            + "('%%', '%d', '%s', '%%', '%d', '%d')", date, totalAmount, deliveryStatus, deliveryDate, invoiceId, customerId);
    try (Connection conn = DBConnection.getInstance().getDBcon();
         Statement stmt = conn.createStatement()) {
        stmt.executeUpdate(sql);
    } catch(SQLException e) {
        e.printStackTrace();
        throw e;
    }

Upvotes: 1

Views: 582

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

Use a prepared statement:

String sql = "INSERT INTO Order "
    + "(date, totalAmount, deliveryStatus, deliveryDate, invoiceId, customerId) VALUES "
    + "(?,?,?,?,?,?)";
try (Connection conn = DBConnection.getInstance().getDBcon();
    PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setDate(1, date);
    ps.setInt(2, totalAmount);
    ps.setString(3, deliveryStatus);
    ps.setDate(4, deliveryDate);
    ps.setInt(5, invoiceId);
    ps.setInt(6, customerId);
    ps.executeUpdate();
} catch(SQLException e) {
    e.printStackTrace();
    throw e;
}

In addition to taking care of proper escaping for all values in the INSERT, a prepared statement also protects you against SQL injection.

Upvotes: 3

Related Questions