R. Jbeily R. Jbeily - 1 month ago 6
Java Question

Insert a Date into oracle database

I need to insert date into my database, i have a table that contains a row date of type Date but i need to insert the date without using preparedStatement but it wont work. here is my code:

try{

dbConnection = DriverManager.getConnection(DR_URL, DB_USER,DB_PASSWORD);
stmt = dbConnection.createStatement();

for(int i=1; i<3; i++){
String invoiceNumber = JOptionPane.showInputDialog("Invoice Number:");
String customerName = JOptionPane.showInputDialog("Customer Name:");
Date invoiceDate = new Date(System.currentTimeMillis());
java.sql.Date invDate = new java.sql.Date (invoiceDate.getTime());

stmt.executeUpdate("INSERT INTO INVOICEMAIN VALUES ('" + invoiceNumber + "','" + customerName + "','" + setDate(invDate) + "')");
}

stmt.close();
dbConnection.close();
}

Answer

The correct way to do this:

  • Don't keep database connection live while waiting for user input. Gather the input first, then connect to database.
    Reason: If user is slow, connection may time out.

  • Use try-with-resources to clean up the JDBC resources.
    Reason: Guaranteed cleanup, better error handling, cleaner code.

  • Use PreparedStatement. Never use string concatenation with user-supplied text to build a SQL statement, because that leaves your code vulnerable to crashes, but more importantly, vulnerable to SQL Injection attacks, allowing hackers to steal your data and delete your tables.

Since you need to collect multiple sets of values, create a class for retaining those.

public class Invoice {
    private final String invoiceNumber;
    private final String customerName;
    private final Date invoiceDate;
    public Invoice(String invoiceNumber, String customerName, Date invoiceDate) {
        this.invoiceNumber = invoiceNumber;
        this.customerName = customerName;
        this.invoiceDate = invoiceDate;
    }
    public String getInvoiceNumber() {
        return this.invoiceNumber;
    }
    public String getCustomerName() {
        return this.customerName;
    }
    public Date getInvoiceDate() {
        return this.invoiceDate;
    }
}
// Prompt user for two invoices
List<Invoice> invoices = new ArrayList<>();
for (int i = 1; i < 3; i++) {
    String invoiceNumber = JOptionPane.showInputDialog("Invoice Number:");
    String customerName = JOptionPane.showInputDialog("Customer Name:");
    invoices.add(new Invoice(invoiceNumber, customerName, new Date()));
}

// Insert invoices
try (Connection dbConnection = DriverManager.getConnection(DR_URL, DB_USER, DB_PASSWORD)) {
    String sql = "INSERT INTO INVOICEMAIN VALUES (?,?,?)";
    try (PreparedStatement stmt = dbConnection.prepareStatement(sql)) {
        for (Invoice invoice : invoices) {
            stmt.setString(1, invoice.getInvoiceNumber());
            stmt.setString(2, invoice.getCustomerName());
            stmt.setDate  (3, new java.sql.Date(invoice.getInvoiceDate().getTime()));
            stmt.addBatch();
        }
        stmt.executeBatch();
    }
}
Comments