Use named parameters processing CallableStatement [Reserved]

2010-12-12  来源:本站原创  分类:Database  人气:62 

Description: JDBC statements in processing

In JDBC applications, JDBC statement object for sending SQL statements to the database server. A statement, a connection object associated with the application and the communication between the database server to handle the object by the statement.

There are three types of JDBC statement object:

  1. General Statement (General statement)
  2. Prepared statements (Prepared statement)
  3. Callable statements (Callable statement)

Statement objects associated with a connection, so you want to create a statement object should first establish a database connection.

Creating connections

The code example in Listing 1 demonstrates how to create a connection:

Listing 1. Loading Informix driver and create a connection code example

Connection con = null;
try {
Class.forName("com.informix.jdbc.IfxDriver");
 String url = "jdbc:informix-sqli://hostname:port_number/dbname:
informixserver=servername; userid=userid;password=pwd;";
con = DriverManager.getConnection(url);
}

Now examine one by one the three types of statement objects.

Back to top

General statement

Method can be used to create this connection createStatement statement. This statement is designed to not pass any value as a parameter of the SQL statement.

Listing 2. Shows sample code to create statement

Statement stmt = con.createStatement();
      cmd = "create database testDB;";
      rc = stmt.executeUpdate(cmd);
stmt.close();
Back to top

Prepared statements

Prepared statements are a subclass of the class statement. Prepared statements and the statement class main difference is that the former can be compiled and optimized only once, and then by setting different parameter values ​​used multiple times. So, if you want to repeatedly execute a statement, the prepared statements is a better choice. As has been pre-compiled, so reducing the execution time. Therefore, the pre-statement is that it not only contains a SQL statement, but also a pre-compiled SQL statement. Another difference is, SQL statements created after the statement was provided to the preset.

Listing 3. Preset statement to explain the sample code

PreparedStatement pstmt = con.prepareStatement("UPDATE tab1 "+
                    "set col1 = ? where key = 1");
pstmt.setShort(1, (short)2);
int rowcount = pstmt.executeUpdate();

Here, the same statement can be used for different preset values ​​of col1. Parameters once set, its value will remain unchanged until it is reset or clearParameters called. This feature allows prepared statements can be used to batch INSERT / UPDATE.

Batch update

By setting multiple values, need to bulk update feature improves the performance of the statement is executed many times. This can submit multiple update operations to a data source and a one-time deal. You can also use a statement object batch updates. But the statement submitted to different objects batch SQL statements, and prepared statements submitted by a set of parameters.

Listing 4 shows how to use prepared statements for bulk insert:

Listing 4 shows an example of a batch update code

PreparedStatement pst = conn.prepareStatement("insert into tab1 values (?)");
for loop....
{
 pst.setInt (1, i);
 pst.addBatch();
}
pst.executeBatch();

addBatch method statement to a cache, and then use executeBatch() method dump to the database. So it saves a statement compilation / optimization, because it only compiled once (for prepared statements), but also save the server round-trip between, because it sends a one-time bulk insert.

Back to top

Callable statement

This is the third method called SQL statement, it provides a Java ™ program from the server to call the stored procedure approach. Callable statement also need to prepare for, and then using the set methods to set their parameters. You can set parameter values ​​in two ways:

  1. Order position
  2. Named parameters

Ordinal position of the traditional parameters set up, it's based on parameters in CallableStatements position to set the parameters. However, the named parameters provides greater flexibility, which allows the order by name instead of position to set the parameters. Routine is called, it must specify the name or the order form CallableStatement parameters. For example, if a parameter using the parameter name, then all the other parameters must also use the parameter name.

In the call with many parameters, and some parameters have default values ​​of the stored procedure, named parameters are particularly useful. If the process is unique, you can omit parameters that have default values ​​and parameters can be entered in any order. Named parameters to make applications more robust, so even if the stored procedure parameters in the order changed, do not have to modify the application.

E3c348e049298fdb7d9828c6825eb032 JDBC driver provides a way to confirm whether the driver and RDMS support named parameters in CallableStatement. If support for named parameters, the system returns true. For example:

Listing 5. SupportsNamedParameters () use

Connection myConn = . . .   // connection to the RDBMS for Database
      DatabaseMetaData dbmd = myConn.getMetaData();
      if (dbmd.supportsNamedParameters() == true)
      {
          System.out.println("NAMED PARAMETERS FOR CALLABLE"
                            + "STATEMENTS IS SUPPORTED");
      }

Get stored procedure parameter name

You can use the getprocedureColumns DatabaseMetaData get stored procedure parameter names, the definition of the method shown in Listing 6:

Listing 6. GetProcedureColumn () method to use

Connection myConn = . . .   // connection to the RDBMS for Database
 . .
      DatabaseMetaData dbmd = myConn.getMetaData();
      ResultSet rs = dbmd.getProcedureColumns(
       "myDB", schemaPattern, procedureNamePattern, columnNamePattern);
      rs.next() {
          String parameterName = rs.getString(4);
 - - - or - - -
 String parameterName = rs.getString("COLUMN_NAME");
 - - -
          System.out.println("Column Name: " + parameterName);

And getProcedureColumns() method parameters to match the names of all columns will be displayed.

Listing 7 shows CallableStatements the use of named parameters.

Creating Stored Procedures

List 7. Callable use an OUT parameter

create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
                 listprice    float,
                 minprice     float,
             out prod_id      float);
. . .
  let prod_id="value for prod_id";
end procedure;

Listing 8 to create the Java code first five parameters of a CallableStatement, the five parameters in the stored procedure parameter. JDBC calls brackets question mark character (?) On the parameters of reference. Set all the parameters or register. Using the format cstmt.setString ("arg", name); named parameters, where arg is the corresponding stored procedure parameter name. There is no need in accordance with the order of the parameters of a stored procedure named parameters.

Listing 8. Callable use of named parameters

String sqlCall = "{call CreateProductDef(?,?,?,?,?)}";
      CallableStatement cstmt = conn.prepareCall(sqlCall);

      cstmt.setString("productname", name);     // Set Product Name.
      cstmt.setString("productdesc", desc);     // Set Product Description.
      cstmt.setFloat("listprice", listprice);   // Set Product ListPrice.
      cstmt.setFloat("minprice", minprice);     // Set Product MinPrice.

      // Register out parameter which should return the product is created.

      cstmt.registerOutParameter("prod_id", Types.FLOAT);

      // Execute the call.
      cstmt.execute();

      // Get the value of the id from the OUT parameter: prod_id
      float id = cstmt.getFloat("prod_id");

If the number of parameters in CallableStatement than the number of stored procedure parameters, then the rest of the parameters must have default values. Do not need to have the default parameter settings, because the server will automatically use the default value. For example, if a stored procedure has 10 parameters, of which there is no default value of four parameters, six parameters have default values, then the CallableStatement must be at least four question marks. You can also use five, six, or up to 10 question marks. In the following only the stored procedure parameters listprice and minprice have default values:

Listing 9. Created with default values ​​of parameters, including the process of

create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
                 listprice    float default 100.00,
                 minprice     float default  90.00,
             out prod_id      float);
. . .
  let prod_id = value for prod_id;
end procedure;

Java code in Listing 10 uses less than the number of parameters of stored procedure parameters (stored procedure has five parameters, the code uses only four parameters) call stored procedures. As listprice have a default value, so you can omit it in CallableStatement.

Listing 10. The use of default parameters

String sqlCall = "{call CreateProductDef(?,?,?,?)}";
                                              // 4 params for 5 args
      CallableStatement cstmt = conn.prepareCall(sqlCall);

      cstmt.setString("productname", name);   // Set Product Name.
      cstmt.setString("productdesc", desc);   // Set Product Description.

      cstmt.setFloat("minprice", minprice);   // Set Product MinPrice.

      // Register out parameter which should return the product id created.

      cstmt.registerOutParameter("prod_id", Types.FLOAT);

      // Execute the call.
      cstmt.execute();

      // Get the value of the id from the OUT parameter: prod_id
      float id = cstmt.getFloat("prod_id");

If the callable statement contains OUT or INOUT parameters, then the need to use the CallableStatement's registerOutParameter register these parameters. Listing 11 uses out parameters prod_id create a OUT parameters of stored procedures. Similarly, you can use the keyword INOUT create INOUT parameters.

Listing 11. INOUT and OUT parameters using

create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
             inout    listprice    float default 100.00,
                 minprice     float default  90.00,
             out prod_id      float);

List of 12 ways to use CallableStatements registerOutparameter the out registered CallableStatement parameters.

Listing 12 Using CallableStatement OUT Parameters registration

cstmt.registerOutParameter("prod_id", Types.FLOAT);

List 13 will use the named parameters combine features of all statements:

Listing 13. Demonstration program named parameters feature

package Callable;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class out1 {
    static Connection conn;
    public static void main(String[] args) {
        getConnect();
        System.out.println("Connection Established");
        createProc();
        runthis();
        System.out.println("\n=============Finished=============");
        System.exit(0);
    }
    private static void getConnect() {
        try
        {
            Class.forName("com.informix.jdbc.IfxDriver");
            String url = "jdbc:informix-sqli://host name or ip :porn number/database
                 name:informixserver=dbservername;";
            System.out.println("URL: "+url);
            conn = DriverManager.getConnection(url);
            }
        catch( Exception e )
        {
            e.printStackTrace();
            System.exit(1);
        }
    }
    private static void createProc() {
        String str=null;
        Statement stmt = null;
        try
        {
            stmt = conn.createStatement();
        }
        catch (SQLException e2)
        {
            e2.printStackTrace();
        }
        str="drop function c_out_proc";
        try
        {
            stmt.executeUpdate (str);
        }
        catch (SQLException e1)
        { }
        str = "create function  c_out_proc ( i int, OUT d varchar(20) ) \n" +
        "returning float; \n" +
        "define f float; \n" +
        "let d= \"Hello OUT\"; \n" +
        "let f=i*2; \n" +
        "return f; \n" +
        "end function; \n";
        try
        {
            stmt.executeUpdate (str);
            System.out.println("Function created \n");
        }
        catch (SQLException e)
        {
            System.out.println("Error on creating function: " + e.toString());
            System.exit(1);
        }
    }
    private static void runthis()
    {
        CallableStatement cstmt = null;
        String command = "{? = call c_out_proc(?, ?)}  ";
        try
        {
            cstmt = conn.prepareCall (command);
            cstmt.setInt(1, 2);
            cstmt.registerOutParameter(2, Types.VARCHAR);
            ResultSet rs = cstmt.executeQuery();
            if (rs == null)
            {
                System.out.println("rs is null *** this is BAD.");
                System.exit(0);
            }
            else
            {
                rs.next();
                System.out.println(rs.getFloat(1));
                System.out.println(cstmt.getString(2));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}
相关文章
  • Use named parameters processing CallableStatement [Reserved] 2010-12-12

    Description: JDBC statements in processing In JDBC applications, JDBC statement object for sending SQL statements to the database server. A statement, a connection object associated with the application and the communication between the database serv

  • Use named parameters processing CallableStatement [reproduced] 2010-12-12

    Introduction: JDBC statement processing in the In JDBC applications, JDBC statement object is used to send SQL statements to the database server. A statement object is associated with a connection, the application and the communication between the da

  • Use spring of jdbcTemplate ----- use named parameters 2010-09-11

    Usage in JDBC, SQL parameter is a placeholder? Indicates that the , and subject to restriction on the location, location parameters of the problem is, once the parameters or position change, must change Parameter binding, in the Spring JDBC, the SQL

  • response.setHeader parameters. usage description [Reserved] 2010-06-15

    Detailed source information: http://www.jb51.net/article/16437.htm response.setHeader is used to set back to the first page meta information, use response.setHeader (name, contect); meta HTML document is used to simulate HTTP protocol response header

  • Turn: solaris system commands Profile 2010-10-22

    Turn: http://www.tianall.com/BBS/Topic.asp?ID=376 quota-v look at their free disk space (unit: KB) and the number of files ⊙ date the current date, time, queries ⊙ who use the same machine now and you who have the time and place ⊙ w and login on the

  • JDBC to operate a database of three objects: Statement: PreparedStatement: CallableStatement 2010-03-20

    1, create a Statement object Established a connection to a particular database, after, you can use this connection to send SQL statements. Statement objects created with the Connection method createStatement, as shown in the following code snippet: C

  • [Transfer] Java calling SQL stored procedure input and output parameters 2010-11-09

    1 Use a stored procedure without parameters Using the JDBC driver calls the stored procedure without parameters, you must use the call SQL escape sequence. Call with no arguments The syntax of the escape sequence is as follows: {Call procedure-name}

  • JDBC Statement PreparedStatement CallableStatement 2014-12-13

    在 JDBC 应用程序中,JDBC 语句对象用于将 SQL 语句发送到数据库服务器.一个语句对象与一个连接相关联,应用程序与数据库服务器之间的通信由语句对象来处理. JDBC 中有三种类型的语句对象: 1. 常规语句(General statement) 2. 预置语句(Prepared statement) 3. 可调用语句(Callable statement) 语句对象与一个连接相关联,所以要创建一个语句对象,首先应该建立一个数据库连接.连接的获取见上一篇博客. 关于这三种语句,个人觉得最

  • Spring JDBC Introduction 2010-08-05

    11.1. Introduction Spring JDBC abstraction framework the value will be reflected in the following areas: (Note: Use the Spring JDBC abstraction framework, the application developer only needs to complete some of the coding in italics.) Specify databa

  • MyBatis User's Guide 2010-11-21

    MyBatis 3 User's Guide to help us to better document ... If you find omissions in this document, a description or missing MyBatis characteristics, then the best way to find out the omissions and then put it down. We receive public documents in the wi

  • Spring's JDBCTemplate 2010-11-22

    Hql query methods such as performance or flexibility can not meet the requirements, you must use SQL when you have three choices: First, the use of Hibernate in the sql query function, the query result objects into the object Entity. Second, the use

  • IBM语音技术API说明,比较锻炼英文能力 2012-03-09

    SMAPI (Speech Manager Application Programming Interface) Reference IBM ViaVoice. SDK for Windowsa Printed in the USA Note Before using this information and the product it supports, be sure to read the general information under Appendix D "Notices&quo

  • Spring of database operations using JDBC Template 2009-07-18

    Spring provides a template class for three options: ■ JdbcTemplate-The most basic of Spring's JDBC templates, this class provides simple access to a database through JDBC and simple indexed-parameter queries. (in accordance with the index parameter)

  • The 11 major advantage of using the hibernate 2010-03-29

    Hibernate in resolving performance issues, very good. With its caching mechanism, using third-party caching and database connection pool, you better resolve performance issues. But these were not enough, hibernate give developers enough freedom to al

  • Getting Started with Tomcat configuration files 2010-03-29

    Tomcat basic configuration tomcat read the configuration file is the first mention briefly how to read the tomcat configuration file. tomcat on startup, first of all to find the system variable CATALINA_BASE, if not, then look for CATALINA_HOME. Then

  • Spring in Action Study Notes - Chapter IV conquest database (change) 2008-09-28

    Spring2.0 official version (http://www.springframework.org/download) Spring2.0 Chinese documents (http://spring.jactiongroup.net/viewtopic.php?t=2279&sid=0906ae6a913537b249e501db5b54c181). In the previous study, we have learned the core Spring framew

  • Struts2 in OGNL, valueStack, stackContext learning 2010-04-01

    [/ Color] to learn Struts2, had not understand how the form of value is passed to Action, the Internet look for some information, a basic understanding of it! The following is basically reproduced from a few people BOLG come, remember the longer term

  • apache cxf-line reading (http://cxf.apache.org/docs/jax-rs.html) 2010-04-11

    Apache CXF Apache CXF Documentation> Index> RESTful Services> JAX-RS Download | Documentation Overview How-Tos Frontends DataBindings Transports Configuration Debugging and Logging Tools RESTful Services WSDL Bindings Service Routing Dynamic Lang

  • Faramita draft language design 0.1 - truly mobile code (mobile code) 2010-04-19

    Faramita draft language design 0.1 Advertising language (not really): The real mobile code (mobile code), a real distributed computing Abbreviation explanation: RPC: Remote Procedure Call Remote Call DSL: Domain Specific Language Domain Specific Lang

  • Chapter JavaScript function 2010-05-03

    JS use the method requires attention to several areas: There any way to return value, if not explicitly return a value, its return value is undefined . Method definition, the parameter list can write the variable names on OK, for example: diff (iNum1