上面显示的是结果。我得到正确的数据,只需要更改列名。问题是我正在尝试重命名表中的列。我已经尝试过以下错误-java.sql.SQLSyntaxErrorException:ORA-00923:找不到期望的FROM关键字:
"SELECT to_char(logdate,'dd-MON-yy') AS DATE, adj_login_time AS LOGIN, adj_logout_time AS LOGOUT, adj_lunch_in AS LUNCHIN, adj_lunch_out AS LUNCHOUT, round(logduration,2) AS LOGDURATION " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10" +
"ORDER BY logdate DESC");
AND
"SELECT to_char(logdate,'dd-MON-yy') AS 'Log Date', adj_login_time AS 'Log In Time', adj_logout_time AS 'Log Out Time', adj_lunch_in AS 'Lunch In', adj_lunch_out AS 'Lunch Out', round(logduration,2) AS 'Log Duration' " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10" +
"ORDER BY logdate DESC");
我正在使用NetBeans IDE并在JAVA中进行编码。我尝试了上述其他几种方法,但都没有碰到运气。谢谢您的帮助!
private DefaultTableModel weeklyLogTableModel(ResultSet weeklyLogSet) throws SQLException
{
ResultSetMetaData metaData = weeklyLogSet.getMetaData();
Vector<String> columnNames = new Vector<String>();
int columnCount = metaData.getColumnCount();
for (int column = 1; column <= columnCount; column++)
{
columnNames.add(metaData.getColumnName(column));
}
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
while (weeklyLogSet.next())
{
Vector<Object> vector = new Vector<Object>();
for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++)
{
vector.add(weeklyLogSet.getObject(columnIndex));
}
data.add(vector);
}
return new DefaultTableModel(data, columnNames);
}
private void userDashboard(int userInputIdNumber)
{
jPanel1.setVisible(false);
jPanel2.setVisible(true);
jPanel3.setVisible(false);
jPanel4.setVisible(false);
try
{
//Getting Information FROM EMPLOYEE_TIME_LOG for Weekly Log
Statement weeklyLogstmt = dbConn.createStatement();
ResultSet weeklyLogSet = weeklyLogstmt.executeQuery (
"SELECT to_char(logdate,'dd-MON-yy') AS Date, adj_login_time, adj_logout_time, adj_lunch_in, adj_lunch_out, round(logduration,2) " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10" +
"ORDER BY logdate DESC");
jTable3.setModel((TableModel)weeklyLogTableModel(weeklyLogSet));
}
除了@EvgeniyDorofeev正确指出的缺失空间外,在您的第一个查询中,您尝试将保留字 DATE
用作列别名,这使解析器感到困惑。使用其他名称:
"SELECT to_char(logdate,'dd-MON-yy') AS ACTIVITY_DATE, adj_login_time AS LOGIN, adj_logout_time AS LOGOUT, adj_lunch_in AS LUNCHIN, adj_lunch_out AS LUNCHOUT, round(logduration,2) AS LOGDURATION " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10 " +
"ORDER BY logdate DESC");
或者,如果您确实希望它具有该名称,则可以使用带引号的标识符(字符串中带有转义的双引号),尽管我确实建议不要这样做,因为它也会引起调用代码的混乱:
"SELECT to_char(logdate,'dd-MON-yy') AS \"DATE\", adj_login_time AS LOGIN, adj_logout_time AS LOGOUT, adj_lunch_in AS LUNCHIN, adj_lunch_out AS LUNCHOUT, round(logduration,2) AS LOGDURATION " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10 " +
"ORDER BY logdate DESC");
在第二个版本中,您使用单引号试图将标识符引起来,但是这些标识符仅被视为字符串文字而不是标识符,并且在语法上此时无效。您必须对引用的标识符使用双引号:
"SELECT to_char(logdate,'dd-MON-yy') AS \"Log Date\", adj_login_time AS \"Log In Time\", adj_logout_time AS \"Log Out Time\", adj_lunch_in AS \"Lunch In\", adj_lunch_out AS \"Lunch Out\", round(logduration,2) AS \"Log Duration\" " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10 " +
"ORDER BY logdate DESC");
您还在ROWNUM
错误的位置进行检查;它将在之前应用ORDER BY
,因此从任何一天开始您都会获得十个不确定的行,然后将这些行进行排序。如果您实际上想查看最近的十行,则需要在子查询中进行排序,然后将ROWNUM
过滤器应用于该行:
"SELECT * FROM (" +
"SELECT to_char(logdate,'dd-MON-yy') AS \"Log Date\", " +
"adj_login_time AS \"Log In Time\", " +
"adj_logout_time AS \"Log Out Time\", " +
"adj_lunch_in AS \"Lunch In\", " +
"adj_lunch_out AS \"Lunch Out\", " +
"round(logduration,2) AS \"Log Duration\" " +
"FROM employee_time_log " +
"WHERE employee_id_number = " +userInputIdNumber + " " +
"ORDER BY logdate DESC" +
") WHERE ROWNUM <= 10";
您还应该考虑使用参数化查询,而不是userInputIdNumber
在字符串中嵌入,这部分是因为每个ID都需要对唯一查询进行硬解析,部分是避免SQL注入-尤其是当该值由用户提供时,看起来成为。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句