JDBC Java Database connection 5) CallableStatement interface

callablestatement Execute stored procedure (also precompiled language)

First, execute the following code with the input parameters in sql:

1       DELIMITER $2               CREATE PROCEDURE Pro_findbyid (inINT)3       BEGIN4               SELECT*fromWHERE=  sid; 5       END $

So, the stored procedure Code for this statement is

Call Pro_findbyid (? );

　　Use CallableStatement to perform:

1     /**2 storage language with input parameters3 run first in SQL4 DELIMITER $5 CREATE PROCEDURE Pro_findbyid (in Sid INT)6 BEGIN7 SELECT * from the person WHERE id = sid;8 END $9      */Ten     Private Static voidTestin () { One          AConnection conn =NULL; -CallableStatement STSM =NULL; -ResultSet rs =NULL; the     Try{     -         //1: Create connection -conn =jdbcutil.getconnection (); -          +         //2: Preparing the SQL language (precompiled language with stored procedures) -String sql = "Call Pro_findbyid (?);"; +  A         //3: Execute precompiled language atSTSM =conn.preparecall (SQL); -          -         //4: Setting Parameters -Stsm.setint (1, 4); -          -         //5: Send parameters, Execute SQL (NOTE: callablestatement Execute SQL only ExecuteQuery () method) in         //returns the ResultSet object -rs =stsm.executequery (); to          +         //6: Traverse, print out results -      while(Rs.next ()) { the         intid = rs.getint ("id"); *String name = rs.getstring ("name"); $String sex = rs.getstring ("Sex");Panax NotoginsengSystem.out.println (id+ "," +name+ "," +sex); -          } the          } +     Catch(Exception e) { A e.printstacktrace (); the         Throw NewRuntimeException (e); +     } -     //7: Close the connection $     finally{ $ Jdbcutil.close (conn, STSM, RS); -     } -      the}

Execute code with input parameters and output parameters in sql:

DELIMITER $ CREATE PROCEDURE pro_findout (inINTVARCHAR)BEGIN    SELECTinto fromWHERE=  sid; END $

The stored procedure code for this code is:

1 Call Pro_findout (4,@NAME); 2 // The precompiled language is 3 // Call Pro_findout (?,?);

Use CallableStatement to perform:

1 /**2 storage language with input and output parameters3 DELIMITER $4 CREATE PROCEDURE pro_findout (in Sid INT, out sname VARCHAR )5 BEGIN6 SELECT NAME into sname from the person WHERE id = sid;7 END $8      */9     Private Static voidinandout () {Ten          OneConnection conn =NULL; ACallableStatement STSM =NULL; -          -         Try{ the              -             //1: Create connection -conn =jdbcutil.getconnection (); -              +             //2: Create SQL Storage language (precompiled) -String sql = "Call Pro_findout (?,?)"; +              A             //3: Execute SQL Precompiled Language atSTSM =conn.preparecall (SQL); -              -             //4: Setting Parameters -             /** - * The first parameter is an input parameter - * The second parameter is the JDBC type of the output parameter in the stored procedure (this is varchar ()) in * in SQL. Find the corresponding type in types -              */ to             //4.1: Setting of the first parameter +Stsm.setint (1, 4); -             //4.2: Setting of the second parameter theStsm.registeroutparameter (2, Java.sql.Types.VARCHAR); *              $             //5: Send parameters, Execute SQL: This return is not a ResultSet objectPanax Notoginseng stsm.executequery (); -              the             /** + * 6: Get the value of the output parameter A * Based on the index value, that is, the location of the output parameters in the precompiled SQL language the              */ +String result = stsm.getstring (2); - System.out.println (Result);  $         } $         Catch(Exception e) { - e.printstacktrace (); -             Throw NewRuntimeException (e); the         } -             //7: Close the connectionWuyi         finally{ the Jdbcutil.close (conn, STSM); -         } Wu          -  About}

JDBC Java Database connection 5) CallableStatement interface