英文:
MySQL - Java syntax error. Why this doesnt work?
问题
错误行在“BETWEEN DATE_SUB(now(), interval ” + days + “ day) AND current_date()”附近，但这个语句在MySQL Workbench上可以工作。'
所以，也许错误在于使用between的方式或者日期的处理方式上。
我尝试做的是根据实际日期和之前通过变量days输入的天数来筛选数据，即在“date_sub(now(), interval 这里是天数 days)”中考虑。
是否有正确或更有效的方法？
private void getStatement(int days) {
try {
DefaultTableModel model = new DefaultTableModel();
this.jtblReports.setModel(model);
Connection con = this.connection.getConexion();
PreparedStatement ps = null;
ResultSet rs = null;
String SELECT = "SELECT client_name, client_surname, client_dni,"
+ " game_name, rent_date, return_date, game_price from "
+ "rent_date BETWEEN DATE_SUB(now(), interval " + days + " day) AND current_date()";
ps = con.prepareStatement(SELECT);
rs = ps.executeQuery();
ResultSetMetaData rsMd = rs.getMetaData();
int columnsCant = rsMd.getColumnCount();
model.addColumn("CLIENT NAME");
model.addColumn("CLIENT SURNAME");
model.addColumn("CLIENT DNI");
model.addColumn("GAME");
model.addColumn("RENT DATE");
model.addColumn("RETURN DATE");
model.addColumn("GAME PRICE");
while (rs.next()) {
Object[] rows = new Object[columnsCant];
for (int i = 0; i < columnsCant; i++) {
rows[i] = rs.getObject(i + 1);
}
model.addRow(rows);
}
model.fireTableDataChanged();
} catch (SQLException e) {
e.printStackTrace();
}
}
英文:
The error line is highlited near "BETWEEN DATE_SUB(now(), interval " + days + " day) AND current_date()" but this statement work on the mysql workbench. '
So, maybe the mistake is on the way of using between or the date in that way.
The thing im trying to do is filter the data considering my actual date and the days before that i introduce throught the variable days in "date_sub(now(), interval here the days days)" .
Is there a correct or more efficient way?
private void getStatement(int days) {
try {
DefaultTableModel model = new DefaultTableModel();
this.jtblReports.setModel(model);
Connection con = this.connection.getConexion();
PreparedStatement ps = null;
ResultSet rs = null;
String SELECT = "SELECT client_name, client_surname, client_dni,"
+ " game_name, rent_date, return_date, game_price from "
+ "rent_date BETWEEN DATE_SUB(now(), interval " + days + " day) AND current_date()";
ps = con.prepareStatement(SELECT);
rs = ps.executeQuery();
ResultSetMetaData rsMd = rs.getMetaData();
int columnsCant = rsMd.getColumnCount();
model.addColumn("CLIENT NAME");
model.addColumn("CLIENT SURNAME");
model.addColumn("CLIENT DNI");
model.addColumn("GAME");
model.addColumn("RENT DATE");
model.addColumn("RETURN DATE");
model.addColumn("GAME PRICE");
while (rs.next()) {
Object[] rows = new Object[columnsCant];
for (int i = 0; i < columnsCant; i++) {
rows[i] = rs.getObject(i + 1);
}
model.addRow(rows);
}
model.fireTableDataChanged();
} catch (SQLException e) {
e.printStackTrace();
}
}
答案1
得分: 0
您的SQL查询至少缺少表名和WHERE子句的起始部分，应该像这样：
SELECT ... FROM your_table WHERE rent_date BETWEEN ...
英文:
Your SQL query is missing at least the name of the table and the start of the WHERE clause, it should look more like this:
SELECT ... FROM your_table WHERE rent_date BETWEEN ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论