java - 如何在 Oracle SQL 中的 Select 语句中重命名表列？

Question

上面显示的是结果。我得到了正确的数据，只需要更改列名称。问题是我正在尝试重命名表中的列。我尝试了以下错误 -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));
}

Answer 1

除了 @EvgeniyDorofeev 正确指出的缺失空格之外，在您的第一个查询中，您尝试使用 reserved word 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 之前应用，因此您将在任何一天获得 10 个不确定的行，然后对这些行进行排序。如果您确实想查看最近的 10 行，那么您需要在子查询中进行排序，然后对其应用 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 注入(inject) - 尤其是在这种情况下值似乎是由用户提供的。