![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
Note that code to catch java.lang.Exception is explicitly included. We will see later what this means. For the moment, just note that the compiler would complain if we do not catch it. 2 errors compiling: ex.java ex.java(13): Exception java.lang.ClassNotFoundException must be caught, or it must be declared in the throws clause of this method. Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); ^ ex.java(15): Exception java.sql.SQLException must be caught, or it must be declared in the throws clause of this method. DriverManager.getConnection(url, ^ What happens? The following lines are printed to the JDBC log stream shown in Listing 5-7 during the execution of this small Java program: Listing 5-7: JDBC log stream. DriverManager.getConnection(jdbc:odbc:mysource) trying driver[className=sun.jdbc.odbc.JdbcOdbcDriver,context=null,jdbc.odbc.JdbcOdbcDriver @1393878] *Driver.connect (jdbc:odbc:mysource) JDBC to ODBC Bridge: Checking security No SecurityManager present, assuming trusted application/applet JDBC to ODBC Bridge 1.0 Current Date/Time: Wed Aug 07 19:42:19 1996 Loading JdbcOdbc library Allocating Environment handle (SQLAllocEnv) hEnv=5308508 Allocating Connection handle (SQLAllocConnect) hDbc=5310680 Connecting (SQLDriverConnect), hDbc=5310680, szConnStrIn=DSN=mysource;UID=javauser;PWD=hotjava RETCODE = 1 getConnection returning driver[className=sun.jdbc.odbc.JdbcOdbcDriver,context=null,sun.jdbc.odbc.JdbcOdbcDri ver@1393878] *Connection.close Disconnecting (SQLDisconnect), hDbc=5310680 Closing connection (SQLFreeConnect), hDbc=5310680 Closing environment (SQLFreeEnv), hEnv=5308508 The JDBC log is quite long, and we actually did nothing but open and close a connection. It is interesting to see all the silently performed actions. Sending SQL StatementsThe connection object will send SQL statements to the database engine. There are different methods to perform this, depending on the kind of operation needed. In this chapter, we focus on sending normal SQL statements. Unlike prepared statements or calls to stored procedures that are discussed later, normal SQL statements are usually constructed, sent, and executed only once. This is, for example, the case within an interactive query tool where the user builds his or her own queries. Normal statements include both statements to query data from the database and statements to update its data. Building Statements The step that directly follows the creation of the connection is the creation of a SQL statement. This does not mean that it is forbidden to build a SQL query string before opening the connection. You are free to do this. The exact meaning is that a JDBC statement is an object associated to a connection and it will be used later to request an execution of a SQL string within this connection environment space in the DBMS. As shown in Figure 5-3, the step order is very simple.
Closing the SQL statement releases all the data associated with the statement. Here is the method to build a statement object. Connection Statement createStatement(); Statement createStatement(); The statement object is obtained by calling this method on the connection instance, as shown in Listing 5-8. Listing 5-8: How to create a statement. ... ... Connection myConnection = DriverManager.getConnection(url, javauser, hotjava); Statement myStatement = myConnection.createStatement(); myConnection.close(); ... ... Sending Statements The SQL statement is sent to the DBMS where it is parsed, optimized, and executed. But we have not yet built the statement text. Indeed, the SQL string passes to the database when the call for execution of the statement is issued, as shown in Figure 5-4.
The driver converts this SQL string into the DBMS native SQL grammar. It is possible to see the converted string without sending it to the database. Usually, you will not use this facility, but sometimes it is necessary to know what the native translation of a query is prior to sending it. Connection String nativeSQL(String sql); The method applies on the connection object because it is DBMS-dependent. Indeed, a connection is associated to one and only one DBMS through its driver. String nativeSQL(String sql); The string passed as an argument is the user SQL statement; nativeSQL() returns the native form of this statement. Listing 5-9 shows the language to obtain the native SQL translation. Listing 5-9: Native SQL translation. ... ... Connection myConnection = DriverManager.getConnection(url, javauser, hotjava); Statement myStatement = myConnection.createStatement(); String myQuery(SELECT * FROM employees ORDER BY salary DESCENDING); System.out.println(This query: + myQuery); System.out.println(is sent to the DBMS as: + myConnection.nativeSQL(myQuery)); myConnection.close(); ... ... All the previous steps, opening a connection and creating a statement, are necessary before executing a query. None of them can be skipped. Figure 5-5, while still somewhat simple, shows the current action sequence.
The method chosen to send a SQL statement to the database depends on the type of statement and type of data it returns. Statement ResultSet executeQuery(String sql); int executeUpdate(String sql); boolean execute(String sql); ResultSet executeQuery(String sql); If the query returns normal rows of data, then the executeQuery() should be used. In this case, the query is typically a static SQL SELECT statement. The SQL text is simply passed as a string argument. It does not have to be translated to the native form with nativeSQL(). This method returns a result set object that is discussed in the next section of this chapter. int executeUpdate(String sql); If the SQL statement returns nothing [returning nothing is different than returning zero (0) rows] or returns an integer value, as is the case with SQL INSERT, UPDATE, or DELETE clauses, executeUpdate should be used. The call returns the integer value or 0 (zero) for statements that return nothing. boolean execute(String sql);
|
![]() |
|