Jdbc basics (7)

Stored Procedure

A stored procedure is a logical unit composed of a group of SQL statements used to execute specific tasks. Stored procedures are used to encapsulate a group of operations or queries executed on the database server. For example, the operations on the employee database (recruitment, dismissal, promotion, search) can be written into a stored procedure and then called and executed by the application. Stored procedures can be compiled and executed using different parameters and results. These parameters and results can be any combination of input, output, and input/output parameters.

 

Most dmbs support stored procedures, but their syntax and functions vary. Therefore, here is a simple example of what the stored procedure is and how to call it in JDBC, but this example does not run.

Create a stored procedure using SQL statements

This section describes a simple stored procedure without parameters. The functions of most stored procedures are much more complex than in this example. However, some basic points of stored procedures are described here. As described above, the syntax for different DBMS-defined stored procedures is different. For example, some DBMS uses begin... end or other keywords to indicate the start and end of the stored procedure definition. In some DBMS, the following SQL statement can create a stored procedure:

 

create procedure SHOW_SUPPLIERSasselect SUPPLIERS.SUP_NAME, COFFEES.COF_NAMEfrom SUPPLIERS, COFFEESwhere SUPPLIERS.SUP_ID = COFFEES.SUP_IDorder by SUP_NAME

The following code places the SQL statement in a string and assigns it to the variable createprocedure for future use:

String createProcedure = "create procedure SHOW_SUPPLIERS " + "as " + "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME";

The following code snippet uses the connection object con to create a statement object, which is used to send the SQL statement used to create a stored procedure to the database:

Statement stmt = con.createStatement();stmt.executeUpdate(createProcedure);

The stored procedure show_suppliers will be compiled and stored in the database as an callable database object, just like calling other methods.

Call stored procedures from JDBC

JDBC allows calling stored procedures in programs written in Java. The first step is to create a callablestatement object. Just like the statement and preparedstatement objects, an open connection object can be used to complete the creation. The callablestatement object contains a call to a stored procedure, but does not contain the stored procedure itself. The first line of code below uses the connection con to create a call to the stored procedure show_suppliers. The part in braces is the escape syntax of the stored procedure. When the driver encounters "{call show_suppliers}", it converts the escape syntax to the local SQL statement used by the database to call the Stored Procedure named show_suppliers.

 CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");ResultSet rs = cs.executeQuery();

The RS of the result set after execution is as follows:

SUP_NAMECOF_NAME---------------------------------------Acme, Inc.ColombianAcme, Inc.Colombian_DecafSuperior CoffeeFrench_RoastSuperior CoffeeFrench_Roast_DecafThe High GroundEspresso

Note: The method used to execute CS is executequery. Because the stored procedure called by CS contains a query, a result set is generated after execution. If the Stored Procedure contains an update or a DLL statement, use the executeupdate method. However, sometimes a stored procedure contains multiple SQL statements. Therefore, it produces not only a result set, but also an update count or a combination of result sets and update counts. In this way, there are multiple result sets, and the execute method should be used to execute callablestatement.

The callablestatement class is a subclass of preparedstatement. Therefore, the callablestatement object can have the same input parameters as the preparedstatement object. In addition, the callablestatement object can contain output parameters or input/output parameters. Inout parameters and execute methods are rarely used. For more information, see "using Java for JDBC database access ".

 

Create a complete JDBC Application

So far, you have only seen some code segments. Later, you will see some runnable and complete sample applications in this section.

 

The first sample code creates the coffees table. The second sample code inserts some data into the table and outputs the query results. The third application creates the suppliers table, and the fourth application inserts some data into the suppliers table. After running the code, you can try to associate the coffees table with the suppliers table for query (as shown in the fifth code example ). The sixth code example is an application that demonstrates transaction processing. It also shows how to use the for loop to set the placeholder parameter in the preparedstatement object.

Because they are complete applications, the Code contains some elements of the Java programming language that are not seen in the previous code segment. These elements are briefly explained here.

Put code into Class Definition

In the Java programming language, all the code to be executed must be placed in the class definition. Enter a file for the class definition, name the file the class name, and add the extension. Java after the file name. Therefore, if the class name is mysqlstatement, its class definition should be put in the file named mysqlstatement. java.

Import classes to make them visible

The first is to guide the packages or classes to be used in the new class. The classes in our example use the java. SQL package (JDBC API). You can use this package by placing the following code before the class definition:

import java.sql.*;

The asterisk (*) indicates all classes in the Java. SQL package to be imported. The import class makes it visible. This indicates that you do not have to write a fully qualified name when using methods or fields in this class. If "Import java. SQL. *;" is not included in the Code, "Java. SQL." And the class name must be written before each JDBC field or method. You can selectively import individual classes instead of the entire package. Java does not require the import of classes or packages, but this makes code writing much easier.

The code line of the import class appears at the top of all code examples, because the imported class must be visible to the defined class. The actual class is defined under the code line of the import class.

Use the main method

Class must contain a static public main method. This method is followed by the code line of the declared class, and it calls other methods in the class. The static keyword indicates that the operation of this method is at the class level rather than on individual instances of the class. The public keyword indicates that any class member can access this method. Since we don't just want to define the classes used by other classes, but the classes to be run, the sample programs in this chapter all include a main method.

Use try and catch Blocks

All examples include try and catch blocks. This is a mechanism for Java programming languages to handle exceptions. Java requires a mechanism to handle an exception thrown by a method. Generally, catch blocks can capture exceptions and specify a solution (you can choose not to handle them ). Two try blocks and two catch blocks are used in the sample code. The first try contains the class. forname method in the Java. lang package. This method throws a classnotfoundexception exception, so the subsequent Catch Block will handle that exception. The second try block contains the JDBC Method, which throws the sqlexceptions exception. Therefore, there is a catch block at the end of the program to handle all the other exceptions that may be thrown, because they are all sqlexception objects.

Retrieval exception

JDBC allows you to view warnings and exceptions generated by DBMS and Java compilers. You can use catch blocks to output exceptions. For example, the following two catch blocks in the sample code output explain the exception message:

try {// Code that could generate an exception goes here.// If an exception is generated, the catch block below// will print out information about it.} catch(SQLException ex) {System.err.println("SQLException: " + ex.getMessage());}try {Class.forName("myDriverClassName");} catch(java.lang.ClassNotFoundException e) {System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage());}

If createcoffees. Java is run twice, the following error message is returned:

SQLException: There is already an object named 'COFFEES'in the database.Severity 16, State 1, Line 1

This example shows how to output the Message parts of the sqlexception object. In most cases, this is enough to indicate an error.

However, this object actually has three parts. To fully understand the cause of the error, You can output all of them. The following code snippet shows the catch blocks completed in two ways. The first step is to output all three parts of the sqlexception object: the message (a string that describes the error) and the SQL status (the error string is indicated according to the X/Open sqlstate rule) and vendor error code (number indicating the driver supplier error code ). After capturing the sqlexception object ex, you can use the getmessage, getsqlstate, and geterrorcode methods to access its three components.

The second way to complete the following code snippet is to get all thrown exceptions. If there is a second exception, it will be connected to ex. You can call ex. getnextexception to check whether the next exception exists. If yes, the while loop continues and outputs the next exception message, sqlstate, and supplier error code. This process continues until there is no exception.

try {// Code that could generate an exception goes here.// If an exception is generated, the catch block below// will print out information about it.} catch(SQLException ex) {System.out.println("/n--- SQLException caught ---/n");while (ex != null) {System.out.println("Message:   "                                   + ex.getMessage ());System.out.println("SQLState:  "                                   + ex.getSQLState ());System.out.println("ErrorCode: "                                   + ex.getErrorCode ());ex = ex.getNextException();System.out.println("");}}

If the above Catch Block is substituted into createcoffees. Java, the following output is obtained after the coffees table is created:

--- SQLException caught ---Message:  There is already an object named 'COFFEES' in the database.Severity 16, State 1, Line 1SQLState: 42501ErrorCode:   2714

Sqlstate is a code defined in X/open and ANSI-92 that identifies exceptions. The following are two examples of sqlstate code numbers and their meanings:

08001 -- No suitable driverHY011 -- Operation invalid at this time 

The vendor error codes for each driver are different, so to get a list of error codes and their meanings, you need to check the driver documentation.

Retrieve warning

The sqlwarning object is a subclass of sqlexception and is used to handle database access warnings. Like exceptions, warnings do not terminate the execution of programs; they only remind users that unexpected events have occurred. For example, a warning tells you that the privilege you are trying to revoke has not been revoked. Or a warning tells you that an error occurred while requesting to disconnect the connection.

A warning is reported for the connection object, statement object (including preparedstatement and callablestatement object), or resultset object. These classes all have the getwarnings method. This method must be called to view the first warning of the call object report. If getwarnings returns a warning, you can call the sqlwarning method getnextwarning on it to obtain other warnings. Automatically executing a statement will clear the warning of the previous statement, so the warning will not be superimposed. However, this also indicates that the warning for extracting a statement report must be performed before the next statement is executed.

The following code snippet demonstrates how to obtain the complete information of the stmt statement object and the warning reported on the rs of the resultset object:

Statement stmt = con.createStatement();ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES");while (rs.next()) {String coffeeName = rs.getString("COF_NAME");System.out.println("Coffees available at the Coffee Break:  ");System.out.println("    " + coffeeName);SQLWarning warning = stmt.getWarnings();if (warning != null) {System.out.println("/n---Warning---/n");while (warning != null) {System.out.println("Message: "                                           + warning.getMessage());System.out.println("SQLState: "                                           + warning.getSQLState());System.out.print("Vendor error code: ");System.out.println(warning.getErrorCode());System.out.println("");warning = warning.getNextWarning();}}SQLWarning warn = rs.getWarnings();if (warn != null) {System.out.println("/n---Warning---/n");while (warn != null) {System.out.println("Message: "                                           + warn.getMessage());System.out.println("SQLState: "                                           + warn.getSQLState());System.out.print("Vendor error code: ");System.out.println(warn.getErrorCode());System.out.println("");warn = warn.getNextWarning();}}}

In fact, warnings are uncommon. The most common warning in the report is the runcation warning-a subclass of sqlwarning. The sqlstate of all datatruncation objects is 01004, indicating a problem occurred when reading and writing data. The datatruncation method can be used to find out which columns or parameters have been truncated, whether reading/writing has been truncated, the number of bytes should be transmitted, and the actual number of bytes.