java - 如何在JSP中显示oracle(10g)查询结果？

Question

我正在尝试制作一个简单的 JSP 片段来对 Oracle 数据库执行查询并显示结果。查询可以是选择、删除、更改、更新等任何内容。我需要帮助编写通用代码来显示查询结果和错误。

这是我到目前为止编写的代码:

<%@ page language="java" import="java.sql.*" %>
<%@page import="oracle.jdbc.driver.*" %>
<%@page import="oracle.sql.*;" %>

<%

Connection conn = null; 
Statement stmt = null; 
ResultSet rset = null; 

try {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.22:1521:orcl",
                                       "test", "testpass");
    stmt = conn.createStatement();

    String que = request.getParameter("q");
    rset = stmt.executeQuery (que);

} catch (SQLException e) { 
    out.println("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
} finally {
    if (rset!= null) rset.close(); 
    if (stmt!= null) stmt.close();
    if (conn!= null) conn.close();
}

%>

我的问题是:

1. 当我执行“select * from emp”等查询时，如何自动显示所有列和行。
2. 如何检查删除/更新查询是否正确运行。
3. 如何使用 JSP 查看和更改 Oracle 触发器。

Answer 1

1.使用ResuletSetMetaData

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/XE","scott","tiger");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
if( rs.next()){
    // print column names
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount(); 
    for(int i=1; i<= columnCount; i++)
        System.out.print(rsmd.getColumnName(i) + "\t");
    System.out.println();
    // print rows
    do {
        for( int i=1; i<=columnCount; i++)
            System.out.print( rs.getString(i) + "\t");
        System.out.println();
    } while(rs.next());
}
conn.close();

2.Statement的executeUpdate()方法以int形式返回受影响的行。

3(a)。创建触发器

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/XE","scott","tiger");
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE OR REPLACE TRIGGER yourtrigger BEFORE DELETE ON emp FOR EACH ROW BEGIN dbms_output.put_line('row deleted..'); END;");        
conn.close();

3 (b)。查看触发器

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/XE","scott","tiger");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select text from user_source where name = 'YOURTRIGGER'");
if( rs.next())
    System.out.println(rs.getString(1));
conn.close();