CODEWITHSUNDEEP
javasqloracle-database
R. Jbeily
R. Jbeily

Reputation: 7

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();
    }

Upvotes: 0

Views: 4828

Answers (2)

Sentinel
Sentinel

Reputation: 6449

If you're just inserting the current date, you can use Oracle's SYSDATE function:

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

Using the SYSDATE function will also prevent date related issues dependent on where the code executes (client machine vs a middle tier, or DB tier server).

However, I agree with @Andreas you should avoid string concatenation of user entered values when building your SQL statements. That is unless of course you like playing fast and loose with little Bobby Tables.

Upvotes: 0

Andreas
Andreas

Reputation: 159086

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();
    }
}

Upvotes: 2

Related Questions