我正在为一家餐厅开发一个餐桌预订系统。如果我预订的日期、表号和时间已经保存在数据库中,我想显示一条错误消息。
if((TableCombo.getSelectedItem().toString().isEmpty()) || CusText.getText().isEmpty()){
JOptionPane.showMessageDialog(null,"Please fill all the required data.");
}
else{
//Inserting to the DB
tableNum = TableCombo.getSelectedItem().toString();
date = ((JTextField)DateChoose.getDateEditor().getUiComponent()).getText();
from = FromCombo.getSelectedItem().toString();
try{
{
Statement stat = conn.createStatement();
String select = "SELECT Table_No, Date, Time_From, COUNT(*) FROM bookings WHERE Table_No = '"+tableNum+"' AND Date = '"+date+"' AND Time_From = '"+from+"' GROUP BY Date, Table_No, Time_From HAVING COUNT(*) > 1";
//System.out.println(select);
ResultSet rs = stat.executeQuery(select);
System.out.println(rs.next());
if(rs.next() == true)
{
JOptionPane.showMessageDialog(null,"Already Booked!", "Error", JOptionPane.ERROR_MESSAGE);
}
else{
PreparedStatement pst = conn.prepareStatement("INSERT INTO bookings (Table_No, Customer_Id, Date, Time_From, Time_To) values (?,?,?,?,?)");
pst.setString(1, TableCombo.getSelectedItem().toString());
pst.setString(2, CusText.getText());
pst.setString(3, date);
pst.setString(4, FromCombo.getSelectedItem().toString());
pst.setString(5, ToCombo.getSelectedItem().toString());
pst.executeUpdate();
//Validation
JOptionPane.showMessageDialog(null, "Successfully Added!");
}
}
////Keeping the Text Fields empty
TableCombo.setSelectedIndex(0);
CusText.setText("");
ToCombo.setSelectedIndex(0);
FromCombo.setSelectedIndex(0);
}
catch (SQLException e){
System.out.println(e);
}
请尽快给我一个答案。。。
1条答案
按热度按时间tp5buhyn1#
可以使表列唯一。就你的情况来说
date
,table_no
,和time_from
. 可以使用以下sql语法:ALTER TABLE bookings ADD UNIQUE INDEX(date,table_no,time_from);
但既然你的问题是SELECT
,那么我建议您将查询改为:如果你的
rs.next
是TRUE
,则sql记录中有重复项。