Java calls stored procedures, you should get a good look at

A stored procedure is a program that is saved in a database and executed on the database side. You can use special syntax to invoke stored procedures in a Java class. At invocation time, the name of the stored procedure and the specified parameters are sent to the DBMS through a JDBC connection, executing the stored procedure and returning the result through 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 free from many popular DBMS, and application servers are mostly expensive. This is not just a matter of license fees. The management required to use the application server, the cost of writing code, and the added complexity of the client program can all be replaced by stored procedures throughout the DBMS.

You can write stored procedures using Java,python,perl or C, but typically use the specific language that your DBMS specifies. Oracle uses PL/SQL,POSTGRESQL to use procedural SQL with PL/PGSQL,DB2. These languages are very similar. Porting stored procedures between them is no more difficult than porting session beans between different implementations of the Sun's EJB specification. Also, stored procedures are designed for embedded SQL, which makes them more user-friendly ways of expressing a database than a language such as Java or C.

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

Using Stored Procedures

Simple old JDBC supports the invocation of stored procedures through the CallableStatement class. This class is actually a subclass of PreparedStatement. Suppose we have a poets database. The database has a stored procedure that sets the age of the poet's death. The following is an old alcoholic Dylan Thomas, soak Dylan Thomas, who does not specify whether the allusion, culture, please criticize. The detailed code for the call:

The string passed to the Preparecall method is the writing specification for the stored procedure call. It specifies the name of the stored procedure and represents the parameters you need to specify.

and JDBC integration is a great convenience for stored procedures: In order to invoke a stored procedure from an application, no stub class or configuration file is required, except for the JDBC driver of your DBMS.

When this code executes, the stored procedure for the database is called. We are not going to get the result because the stored procedure does not return a result. Execution success or failure will be known by exception. Failure can mean a failure to invoke a stored procedure (such as an incorrect type of argument provided), or an application failure (such as throwing an exception indicating that there is no "Dylan Thomas" in the Poets Database)

Combining SQL operations with stored procedures

Mapping Java objects to rows in a SQL table is fairly straightforward, but typically requires a few SQL statements, a select Lookup ID, and an insert that inserts the data for the specified ID. In a database schema that is highly normalized (conforming to higher paradigms), multiple table updates may be required, so more statements are required. Java code expands quickly, and the network overhead for each statement increases rapidly.

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

Very unique? No. I bet you expect to see an update on the Poets table. This also implies how easy it is to use a stored procedure implementation. Set_death_age can almost certainly be a rotten realization. We should add a column to the Poets table to store the age of death. The Java code does not care how the database schema is implemented because it calls only stored procedures. We can change the database schema later to improve performance, but we don't have to modify our code.

Here is the Java code that calls the stored procedure above:

To ensure maintainability, it is recommended to use a static method like this here. This also causes the code that invokes the stored procedure to be in a simple template code. If you use many stored procedures, you will find that you only need to copy and paste to create a new method. Because of the template of the code, you can even automate the production of code that calls the stored procedure through scripting.

#p #
Functions
Stored procedures can have return values, so the CallableStatement class has a method like Getresultset to get the return value. When a stored procedure returns a value, you must use the Registeroutparameter method to tell the JDBC drive what the SQL type of the value is. You must also adjust the stored procedure tune to indicate that the procedure returns a value.

Following is the example above. This time we inquire about the age of Dylan Thomas's death. This time the stored procedure uses PostgreSQL's pl/pgsql:
Hereby affirm that this article is not finished, the wonderful is still behind, please focus on tomorrow at the same time push, want to know in advance I [Q21535722319] together to discuss.

Java calls stored procedures, you should get a good look at