java - 使用PreparedStatement将java.sql.Date更新到数据库中

Question

在过去的几个小时里，我一直在摸不着头脑，想知道出了什么问题。我想在我的 Access 数据库上编辑日期/时间格式，但它一直让我困惑为什么我无法更新它。这是我的代码，我会解释。 。 .

这是主类，其中有一个按钮显示更新，单击后将进入另一个名为 UpdateBooking_Run() 的类

public MainInterface() {
    JButton updateBookings = new JButton("UPDATE");
    updateBookings.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            UpdateBooking_Run ub = new UpdateBooking_Run();
            ub.setVisible(true);
        }
    });
 }

这是 UpdateBooking_Run()，没有文本框，只有此类中的按钮...正如您从字符串中看到的，我将日期转换为 java.sql.Date

public class UpdateBooking_Run extends JFrame {
   public static java.sql.Date sqldate;
   public UpdateBooking_Run() {
      JButton btnAdd = new JButton("UPDATE");
       btnAdd.addActionListener(new ActionListener() {
          public void actionPerformed(ActionEvent arg0) {
            try {
                Integer.parseInt(yearBookings.getText().trim());
            }
            catch (NumberFormatException na) {
                JOptionPane.showMessageDialog(null, "Please input year", "Error", JOptionPane.ERROR_MESSAGE);

            }

            DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
            String mu = monthBookings.getSelectedItem().toString();
            String da = dayBookings.getSelectedItem().toString();
            String ye = yearBookings.getText().trim();

            String dat =  ye + "/" + mu + "/" + da ;

            java.util.Date date = null;

            try {
                date = df.parse(dat);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            sqldate = new java.sql.Date(date.getTime());

            try {
                UpdateConn_Run uc = new UpdateConn_Run();
                uc.updateDate();
            }
            catch (Exception en) {
                en.printStackTrace();
            }
          }
       });
     }
  }

正如您在上面的代码中看到的，我将 sqldate 设置为静态主要是因为它可以与我的其他类上的更新字符串一起使用，但我似乎无法更新日期。 。 .

然后这就是连接...或者UpdateConn_Run()

public class UpdateConn_Run {
   Connection con;
   Statement st;
   ResultSet rs;
   StringBuffer results;
   String url = "jdbc:ucanaccess://C://DATABASE//DATA.accdb";   
   PreparedStatement ps;

      public void updateBooking() {
        try {
           int i = 1;
           UpdateBooking_Run ubr = new UpdateBooking_Run();
           MainInterface mi = new MainInterface();

        Date da = UpdateBooking_Run.sqldate;

        con = DriverManager.getConnection(url);
        //theres actually an uneditable textbox in MainInterface that shows the ID of the selected row
        int id = Integer.parseInt(mi.idstringBookings);
        String sql = "UPDATE bookings SET date ='"+da+"' WHERE ID ='"+id+"'";
        ps = con.prepareStatement(sql);
        ps.executeUpdate();

        ps.close();
        con.close();

        if (i == 1) {
            JOptionPane.showMessageDialog(null, "Database has been updated please click Refresh");

        }

    }

    catch (Exception e) {
            e.printStackTrace();
    }
}

我似乎也遇到了错误...

net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid datetime format
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:261)
at interfaceSystem.UpdateConn_Run.updateBooking(UpdateConn_Run.java:109)
at interfaceSystem.UpdateBooking_Run$2.actionPerformed(UpdateBooking_Run.java:284)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

但它仍然让我困惑。 。 .

• 它说无效的日期时间格式，所以我继续一次又一次地更改格式。即使尝试了 MS Access 中的格式，问题仍然存在。
• 尝试不将日期设置为静态。我只是得到“null”。
• 有人可以解释为什么在PreparedStatements中使用static来更新数据库吗？直到今天我仍然感到困惑......(我类的每个人似乎都在使用它，尽管我不知道为什么)

我对我的英语感到抱歉。我不是母语人士，所以如果您对语法有疑问，我将不胜感激......

Answer 1

由于您使用的是 PreparedStatement，因此您应该使用占位符，并且您的代码应如下所示:

query = "UPDATE bookings SET date = ? WHERE id = ?";

preparedStatement = con.prepareStatement(query);
preparedStatement.setDate(1, da);
preparedStatement.setInt(2, id);
preparedStatement.executeUpdate();

因为您将 Date 对象包含到 String 中，所以将使用其 toString() 方法和您的语句对其进行解析看起来像:

"UPDATE bookings SET date = 2000-01-12 WHERE id = 22"