Oracle JDBC执行DESC语句获取表字段类型提示无效SQL的问题求助

Hey there, let's break down why your code is throwing that error and how to fix it.

The DESC (or DESCRIBE) command you're using is only supported in SQL*Plus and Oracle's command-line tools—it's not a standard SQL statement that JDBC can execute directly. That's exactly why you're getting the "invalid SQL statement" message.

Instead, you should query Oracle's built-in data dictionary views to get table column metadata. These views store all the information about your tables, columns, and their types. Here's how to adjust your code:

Modified Working Code #

ResultSet rs = null;
Statement stmt = null;
Map<String,String> hm = new HashMap<String,String>();
ArrayList al = new ArrayList();
Connection conn = DBUtil.getOraDBConnection();

// Replace the DESC command with a query against Oracle's data dictionary
String tableName = "EMS#CHANNELS";
// Use USER_TAB_COLUMNS if the table belongs to the current JDBC user
String query = "SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('" + tableName + "')";

Logger.info("Getting table metadata with query: " + query);
stmt = conn.createStatement();
rs = stmt.executeQuery(query);

// Iterate through the result to collect column names and their types
while (rs.next()) {
    String columnName = rs.getString("COLUMN_NAME");
    String dataType = rs.getString("DATA_TYPE");
    hm.put(columnName, dataType);
    al.add(columnName); // Add columns to your list as needed
}

// Remember to close your resources (rs, stmt, conn) in a finally block or try-with-resources!

Additional Notes #

• If the table isn't owned by the current JDBC user: Use ALL_TAB_COLUMNS instead and specify the schema owner, like this:

  String query = "SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('YOUR_SCHEMA_NAME') AND TABLE_NAME = UPPER('" + tableName + "')";
  

• Avoid SQL Injection: If the table name comes from user input, use a PreparedStatement to prevent injection risks:

  String query = "SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER(?)";
  PreparedStatement pstmt = conn.prepareStatement(query);
  pstmt.setString(1, tableName);
  rs = pstmt.executeQuery();
  

• Get more metadata: You can add other columns from the view like DATA_LENGTH (column size), NULLABLE (whether the column allows nulls), or DATA_PRECISION (for numeric types) if you need extra details.

内容的提问来源于stack exchange，提问作者Mahesh