如何根据今天的日期从表中提取10天后的数据

Question

我有搜索框的表格。我想在这个搜索框中输入今天的日期，并点击提交按钮后，我从数据库中获得10天的记录。我在table（table=issueDevices的名称）中有以下列。Device_ID、Employee_id、Employee_Name、Employee_Ext、Issue_Date

当我像下面这样编写查询时，pst=con.prepareStatement（“select*from issuedevices where Issue_Date='”+str+“'-INTERVAL'10'day”）；

try 
          {
                   String str = tf5.getText();
                   Connection con=DB.getConnection();
                   PreparedStatement st = con.prepareStatement("select * from issuedevices where Issue_Date=?");
                 //  PreparedStatement st = con.prepareStatement("SELECT * FROM issuedevices WHERE Issue_Date > sysdate - INTERVAL '10' DAY;");

                   st.setString(1, str);
                   ResultSet rs=st.executeQuery();

             //       Vector v = new Vector();
               if (rs.next()) 
                    {
                         frame1 = new JFrame("Database Search Result");
                         frame1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                         frame1.setLayout(new BorderLayout());
                        //TableModel tm = new TableModel();
                         DefaultTableModel model = new DefaultTableModel();
                         model.setColumnIdentifiers(columnNames);
                         table = new JTable();
                         table.setModel(model);
                         table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
                         table.setFillsViewportHeight(true);
                           JScrollPane scroll = new JScrollPane(table);
                           scroll.setHorizontalScrollBarPolicy(
                                   JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
                           scroll.setVerticalScrollBarPolicy(
                                   JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
                 //  from = (String) c1.getSelectedItem();
                             String id = "";
                             String Device_ID = "";
                             String Employee_id = "";
                             String Employee_Name = "";
                             String Employee_Ext = "";
                             String Issue_Date= "";

                try {
                      //  pst = con.prepareStatement("select * from issuedevices where Issue_Date='" + str + "'");
                       pst = con.prepareStatement("select * from issuedevices where Issue_Date BETWEEN '" + str + "' - INTERVAL '11' DAY  AND '" + str + "'");
                       // pst = con.prepareStatement("select * from issuedevices where Issue_Date = '" + str + "'- INTERVAL '10' DAY");

                        ResultSet rs1 = pst.executeQuery();

                        int i = 0;
                        if (rs1.next())
                        {
                            id = rs1.getString("id");
                            Device_ID = rs1.getString("Device_ID");
                            Employee_id = rs1.getString("Employee_id");
                            Employee_Name = rs1.getString("Employee_Name");
                            Employee_Ext=rs1.getString("Employee_Ext");
                            Issue_Date=rs1.getString("Issue_Date");

                            model.addRow(new Object[]{id, Device_ID, Employee_id,Employee_Name,Employee_Ext,Issue_Date});
                            i++;
                        }
                        if (i < 1) 
                        {
                            JOptionPane.showMessageDialog(null, "No Record Found", "Error", JOptionPane.ERROR_MESSAGE);
                        }
                        if (i == 1) 
                        {
                            System.out.println(i + " Record Found");
                        }
                        else 
                        {
                            System.out.println(i + " Records Found");
                        }
                } 
                        catch (Exception ex)
                        {
                            JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
                        }
                        frame1.add(scroll);
                        frame1.setVisible(true);
                        frame1.setSize(400, 300);
                    }

                    st.close();
                    rs.close();
          } 
          catch (Exception e) 
          {
              JOptionPane.showMessageDialog(null, "Name not Found");
          }

Answer 1

    pst = con.prepareStatement("select * from issuedevices where Issue_Date BETWEEN ? AND ?;");
    LocalDate today = LocalDate.now(ZoneId.of("Europe/Isle_of_Man"));
    pst.setObject(1, today.minusDays(10));
    pst.setObject(2, today);

不要手动构建查询字符串。使用参数。并且不将日期参数作为字符串传递，而是作为localdate对象传递。从代码中可以看到，localdate有一个减去10天（或任何其他天数）的方法。

链接：Oracle教程：日期时间，解释如何使用java.Time。