java - 将日期插入oracle数据库

Question

我需要将日期插入我的数据库，我有一个包含日期类型的行日期的表，但我需要在不使用preparedStatement的情况下插入日期，但它不起作用。这是我的代码:

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 1

正确的做法是:

• 在等待用户输入时不要保持数据库连接处于 Activity 状态。首先收集输入，然后连接到数据库。
  原因:如果用户速度较慢，连接可能会超时。

• 使用try-with-resources清理 JDBC 资源。
  原因:有保证的清理、更好的错误处理、更干净的代码。

• 使用PreparedStatement 。切勿使用字符串连接与用户提供的文本来构建 SQL 语句，因为这会使您的代码容易崩溃，但更重要的是，容易受到 SQL Injection 的影响。攻击，允许黑客窃取您的数据并删除您的表。

由于您需要收集多组值，因此创建一个类来保留这些值。

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