Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to use stored procedures in Java database programs". In daily operation, I believe that many people have doubts about how to use stored procedures in Java database programs. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use stored procedures in Java database programs". Next, please follow the editor to study!

Stored procedures are programs that are saved in the database and executed on the database side. You can use special syntax to call stored procedures in the Java class. When called, the name of the stored procedure and the specified parameters are sent to DBMS over a JDBC connection, the stored procedure is executed, and the result is returned over the connection, if any.

Using stored procedures has the same benefits as using an application server based on EJB or CORBA. The difference is that stored procedures can be used for free from many popular DBMS, while application servers are mostly very expensive. It's not just a matter of license fees. The administrative cost of using the application server, the cost of writing code, and the increased complexity of the client program can be replaced entirely by stored procedures in DBMS.

You can write stored procedures in Java,Python,Perl or C #, but usually in a specific language specified by your DBMS. Oracle uses PL/SQL,PostgreSQL, uses pl/pgsql,DB2, uses Procedural SQL. These languages are very similar. Porting stored procedures between them is no more difficult than migrating Session Bean between different implementations of the EJB specification of Sun. Moreover, stored procedures are designed to embed SQL, which makes them express database mechanisms in a more friendly way than languages such as Java or C.

Because the stored procedure runs on DBMS itself, this can help reduce the wait time in the application. Instead of executing four or five SQL statements in Java code, you only need to execute one stored procedure on the server side. A reduction in the number of data round trips on the network can dramatically optimize performance.

Using stored procedures

The simple old JDBC supports calls to stored procedures through the CallableStatement class. This class is actually a subclass of PreparedStatement. Suppose we have a poets database. There is a stored procedure in the database that sets the age of the poet's death. The following is the old alcoholic Dylan Thomas (old soak Dylan Thomas), do not specify whether or not related to allusions, culture, please criticize and correct. The detailed code to make the call:

Try {int age = 39; String poetName = "dylan thomas"; CallableStatement proc = connection.prepareCall ("{call set_death_age (,)}"); proc.setString (1, poetName); proc.setInt (2, age); cs.execute ();} catch (SQLException e) {/ /. }

The string passed to the prepareCall method is the writing specification for stored procedure calls. It specifies the name of the stored procedure,? Represents the parameters you need to specify.

Integration with JDBC is a great convenience of stored procedures: in order to call stored procedures from an application, no stub classes or configuration files are required, nothing but your DBMS JDBC driver.

When this code is executed, the stored procedure of the database is called. We did not get the result because the stored procedure did not return the result. The success or failure of the execution will be known through the exception. Failure may mean a failure to call a stored procedure (for example, an incorrect type of parameter provided) or an application failure (such as throwing an exception indicating that "Dylan Thomas" does not exist in the poets database)

Combine SQL operations with stored procedures

Mapping Java objects to rows in the SQL table is fairly simple, but you usually need to execute several SQL statements; it could be a SELECT lookup ID, and then an INSERT inserts the data of the specified ID. In a highly normalized database schema, updates to multiple tables may be required, so more statements are required. The Java code expands quickly, and the network overhead of each statement increases rapidly.

Moving these SQL statements into a stored procedure greatly simplifies the code, involving only one network call. All associated SQL operations can occur within the database. Also, stored procedure languages, such as PL/SQL, allow the use of SQL syntax, which is more natural than Java code. Here are our early stored procedures, written in Oracle's PL/ SQL language:

Create procedure set_death_age (poet VARCHAR2, poet_age NUMBER) poet_id NUMBER; beginSELECT id INTO poet_id FROM poets WHERE name = poet; INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age); end set_death_age

Unique? No, no. I bet you're looking forward to seeing a UPDATE on the poets table. This also implies how easy it is to implement using stored procedures. Set_death_age is almost certainly a bad implementation. We should add a column to the poets table to store the age of death. The Java code doesn't care about how the database schema is implemented, because it only calls stored procedures. We can change the database schema later to improve performance, but we don't have to modify our code.

The following is the Java code that calls the above stored procedure:

Public static void setDeathAge (Poet dyingBard, int age) throws SQLException {Connection con = null; CallableStatement proc = null; try {con = connectionPool.getConnection (); proc = con.prepareCall ("{call set_death_age (,)}"); proc.setString (1, dyingBard.getName ()); proc.setInt (2, age); proc.execute ();} finally {try {proc.close () } catch (SQLException e) {} con.close ();}

To ensure maintainability, it is recommended to use a static method like this one. This also makes the code that calls the stored procedure centralized in a simple template code. If you use many stored procedures, you will find that you only need to copy and paste to create new methods. Because of the templating of the code, it is even possible to automatically produce code that calls stored procedures through scripts.

Functions

Stored procedures can have return values, so the CallableStatement class has methods like getResultSet to get the return value. When a stored procedure returns a value, you must use the registerOutParameter method to tell the JDBC driver what the SQL type of the value is. You must also adjust the stored procedure call to instruct the procedure to return a value.

The following is followed by the example above. This time we inquired about the age of Dylan Thomas when he died. This time the stored procedure uses PostgreSQL's pl/pgsql:

Create function snuffed_it_when (VARCHAR) returns integer 'declarepoet_id NUMBER; poet_age NUMBER; begin-- first get the id associated with the poet.SELECT id INTO poet_id FROM poets WHERE name = $1;-- get and return the age.SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id; return age; end;' language 'pl/pgsql'

Also, notice that the pl/pgsql parameter name is referenced by the $n syntax of the Unix and DOS scripts. At the same time, pay attention to embedded comments, which is another advantage over Java code. It's OK to write such comments in Java, but it looks messy and disconnected from the SQL statement and must be embedded in the Java String.

At this point, the study on "how to use stored procedures in Java database programs" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.