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

Question

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

这是 Main 类，其中有一个按钮显示更新，单击时将导致另一个名为 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中的格式，问题仍然存在。
• 尝试不将日期输入为静态。我只是得到"空"。
• 有人可以解释为什么静态用于更新预准备语句中的数据库吗？直到今天仍然让我感到困惑...(我班上的每个人似乎都在使用它，尽管我不知道为什么)

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

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"

Answer 2

我不做Java，但你的最终SQL必须看起来像这样：

"UPDATE bookings SET date = #2017/03/21# WHERE ID = '1'";

或者，如果 ID 为数字：

"UPDATE bookings SET date = #2017/03/21# WHERE ID = 1";

因此，像这样：

//datestring = da formatted as "2017/03/20"
String sql = "UPDATE bookings SET date =#"+datestring+"# WHERE ID ='"+id+"'";