java - JdateChooser 和 mysq.date 不会显示在表中

Question

这是我的代码，没有执行任何操作:)。它应该生成一个包含已分配约会和空闲约会的表。我的数据库名称是 test，表名称是programari，日期的列名称是data，小时的列名称是ora，用户的列名称是pacienti。

private void refreshTable(){

 String[] orar={"8:00","8:30","9:00","9:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00"};

        DefaultTableModel model = new DefaultTableModel();

        model.setRowCount(0);
        try {

               for(int i =0;i<orar.length;i++){
                   pst=conn.prepareStatement("select * from programari");
                   rs=pst.executeQuery();
                   if(rs.next()){
                   if(rs.getDate("data").toString().equals(datap.getDate().toString())){
                       if(rs.getString("ora").equals(orar[i])){
                           model.addRow(new Object[]{orar[i],rs.getString("pacienti")});
                       }else{
                           model.addRow(new Object[]{orar[i],null});
                       }
                   }
                   }else{
                       model.addRow(new Object[]{orar[i],null});
                   }                                
    }                 
        } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
        }         
    }

Answer 1

几点:

1. 在循环外准备语句。
2. 使用例如乔达时间:http://www.joda.org/joda-time/
3. 考虑使用准备好的语句参数。
4. 考虑使用 DATETIME 类型的列 data_y_ora(或类似列)而不是 data 和 ora。
5. 使用循环来初始化小时数。

以下是使用 JodaTime、数据库表中 DATETIME 类型的列 data_y_ora 和准备好的语句参数的示例:

private void refreshTable() {
    // this is the "base" date
    final DateTime data = new DateTime(datap.getDate());
    // here a List is used instead of an array
    final List<DateTime> orar = new ArrayList<DateTime>();
    // set up the dates/times you want to query
    for (int hour = 8; hour <= 22; hour++) {
        orar.add(data.withTime(hour, 0, 0, 0);
        orar.add(data.withTime(hour, 30, 0, 0);
    }
    final DefaultTableModel model = new DefaultTableModel();
    // create a database query with a parameter
    final PreparedStatement pst = conn.prepareStatement("select * from programari where data_y_ora = ?");

    model.setRowCount(0);
    try {
        // iterate over the dates/times
        for (DateTime ora : orar) {
            // set the query parameter
            pst.setDate(1, ora.toDate());
            // execute the query
            rs = pst.executeQuery();
            if (rs.next()) {
                // if a record has been found
                model.addRow(new Object[] { ora, rs.getString("pacienti") });
            } else {
                // no record has been found
                model.addRow(new Object[] { ora, null });
            }
        }
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

请注意，这些行现在有一个 DateTime 实例而不是 String 作为第一个元素。