java - 将 Select 语句限制为最后 10 天

Question

我正在尝试实现一个搜索界面，该界面允许用户搜索最近 10 天内为数据库添加的内容，然后将结果显示在 jtable 中。我收到 sql 语句的语法错误，但我不确定它出了什么问题，因为它对我来说似乎是正确的。我知道该错误表明语法错误，但我不确定具体错误是什么或如何修复它。

这是我的代码:

  package project_files;



import javax.swing.JFrame;
import javax.swing.JToolBar;
import javax.swing.table.DefaultTableModel;

import project_files.registration_test;
import project_files.root_login;
import project_files.gui_interface;
import project_files.video_interface;

import javax.swing.JList;
import javax.swing.JOptionPane;
import javax.swing.JLabel;
import javax.swing.JTextField;
import javax.swing.JTextPane;
import javax.swing.JPasswordField;
import javax.swing.JButton;
import javax.swing.JSeparator;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.awt.EventQueue;
import java.awt.event.ActionEvent;
import javax.swing.JTable;

public class searchby {

    private JFrame frame;
    private JTable table;

    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    searchby window = new searchby();
                    window.frame.setVisible(true);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
    }

    /**
     * Create the application.
     */
    public searchby() {
        initialize();
    }

    public void video_interface() {
        initialize();
    }

    /**
     * Initialize the contents of the frame.
     */
    private void initialize() {
        frame = new JFrame();
        frame.setBounds(200, 200, 523, 300);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.getContentPane().setLayout(null);

        JLabel lbSearch = new JLabel("Search by");
        lbSearch.setBounds(209, 12, 100, 15);
        frame.getContentPane().add(lbSearch);

        JButton btnSearch = new JButton("Search by");
        btnSearch.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                   DefaultTableModel model = new DefaultTableModel(new String[]{"video name", "video description", "video_city", "video_subject", "video_tags"}, 0);
                try {

                    Class.forName("com.mysql.cj.jdbc.Driver");
                    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/userdatabase", "root", "pass1234");
                            Statement stmt= con.createStatement();
                            String sql = "SELECT *, (NOW() - INTERVAL 10 DAY) AS diff" + 
                                    "FROM video " + 
                                    "WHERE date >= (NOW() - INTERVAL 10 DAY))";
                            ResultSet rs=stmt.executeQuery(sql);
                            if(rs.next())
                            {
                                 String d = rs.getString("video_name");
                                 String e = rs.getString("video_description");
                                 String f = rs.getString("video_city");
                                 String g = rs.getString("video_subject");
                                 String h = rs.getString("video_tags");
                                 model.addRow(new Object[]{d, e, f, g, h});
                                    table.setModel(model);
                            {   JOptionPane.showMessageDialog(null, "videos selected Successfully...");
                            }
                            }

                            con.close();
                } catch(Exception e) {System.out.print (e);}






            }});
        btnSearch.setBounds(23, 203, 130, 25);
        frame.getContentPane().add(btnSearch);



        JButton btnNewExit = new JButton("Exit");
        btnNewExit.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {

                JFrame frmLogin_sys = new JFrame("Exit");
                if (JOptionPane.showConfirmDialog(frmLogin_sys, "Confirm if you want to exit", "Login System",
                JOptionPane.YES_NO_OPTION)== JOptionPane.YES_NO_OPTION) {
                    System.exit(0);
                }
            }
        });
        btnNewExit.setBounds(373, 203, 114, 25);
        frame.getContentPane().add(btnNewExit);

        JSeparator separator = new JSeparator();
        separator.setBounds(12, 175, 499, 2);
        frame.getContentPane().add(separator);

        JSeparator separator_1 = new JSeparator();
        separator_1.setBounds(12, 36, 499, 2);
        frame.getContentPane().add(separator_1);

        table = new JTable();
        table.setBounds(103, 61, 285, 100);
        frame.getContentPane().add(table);




    }
}

以及我的错误:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'videoWHERE date >= (NOW() - INTERVAL 10 DAY)' at line 1

Answer 1

您只是忘记了“AS diff”后面的空格:)