JDBC Learning Note (12): Using JDBC to call stored procedures and batch processing

stored procedures: A set of SQL statements that are compiled to complete a particular function, stored in a database, and executed by specifying the name of the stored procedure and giving parameters. Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, the stored procedure runs faster than a single SQL statement block. At the same time, because it only needs to provide the stored procedure name and the necessary parameter information in the call, it can reduce the network traffic and the simple network burden to some extent.

To create a stored procedure:

DELIMITER $$DROP PROCEDURE IF EXISTS 'JDBC'.'AddUser' $$CREATE PROCEDURE 'JDBC'.'AddUser'(inchPNameVARCHAR( $),inchBirthday DATE,INT  Money FLOAT, out PIDINT)BEGIN    INSERT  into USER(Name,birthday, Money)VALUES(Pname,birthday, Money); SELECTLAST_INSERT_ID () intopid;END$ $DELIMITER;

Insert a record by calling the stored procedure:

1  PackageCom.xxyh.jdbc;2 Importjava.sql.CallableStatement;3 Importjava.sql.Connection;4 ImportJava.sql.ResultSet;5 Importjava.sql.SQLException;6 Importjava.sql.Types;7  Public classPstest {8     9      Public Static voidMain (string[] args)throwsSQLException {Ten          OneConnection conn =NULL; ACallableStatement cs =NULL; -ResultSet rs =NULL; -         Try { theconn =jdbcutils.getconnection (); -String sql = "{call AddUser (?,?,?,?)}"; -CS =conn.preparecall (SQL); -              +Cs.registeroutparameter (4, Types.integer);  -Cs.setstring (1, "PS1"); +Cs.setdate (2,Newjava.sql.Date (System.currenttimemillis ())); ACs.setfloat (3, 100f); at              - cs.executeupdate (); -              -             intid = cs.getint (4); -SYSTEM.OUT.PRINTLN ("id =" +ID); -}finally { in Jdbcutils.close (RS, CS, conn); -         } to     } +}

Because the last_insert_id () method in the stored procedure is related to the MySQL database, the program will have errors when changing the source of the data (such as from Oracle, MS SQL Server database), in order to improve the commonality of the Code, Use a different API to achieve primary key acquisition:

1  PackageCom.xxyh.jdbc;2 Importjava.sql.Connection;3 Importjava.sql.PreparedStatement;4 ImportJava.sql.ResultSet;5 Importjava.sql.SQLException;6 Importjava.sql.Statement;7  Public classOtherapi {8      Public Static voidMain (string[] args)throwsSQLException {9Connection conn =NULL;TenPreparedStatement PS =NULL; OneResultSet rs =NULL; A          -         Try { -conn =jdbcutils.getconnection (); theString sql = "INSERT into user (Name,birthday,money) VALUES (' ps3 ', ' 2015-01-02 ', 400)"; -              -             //return PRIMARY Key -PS =conn.preparestatement (Sql,statement.return_generated_keys); + ps.executeupdate (); -              +             //return a ResultSet object containing the auto-generated key (s) Ars =Ps.getgeneratedkeys (); at             intID = 0; -             if(Rs.next ()) -id = rs.getint (1); -SYSTEM.OUT.PRINTLN ("id =" +ID); -}finally { - Jdbcutils.close (RS, PS, conn); in         } -     } to}

Batch Processing

When sending a batch of SQL statements to the database, you should avoid sending execution to the database, and use the JDBC batch mechanism to improve efficiency.

1  PackageCom.xxyh.jdbc;2 Importjava.sql.Connection;3 Importjava.sql.Date;4 Importjava.sql.PreparedStatement;5 ImportJava.sql.ResultSet;6 Importjava.sql.SQLException;7 Importjava.sql.Statement;8  Public classBatchtest {9      Public Static voidMain (string[] args)throwsSQLException {Ten          One Createbatch (); A     } -      -     Static voidCreatebatch ()throwsSQLException { theConnection conn =NULL; -PreparedStatement PS =NULL; -ResultSet rs =NULL; -         Try { +conn =jdbcutils.getconnection (); -String sql = "INSERT into user (Name,birthday,money) VALUES (?,?,?)"; +PS =conn.preparestatement (SQL, Statement.return_generated_keys); A              for(inti = 0; I < 5; i++) { atPs.setstring (1, "batch" +i); -Ps.setdate (2,NewDate (System.currenttimemillis ())); -Ps.setfloat (3, 100f+i); -                  - Ps.addbatch (); -             } in Ps.executebatch (); -}finally { to Jdbcutils.close (RS, PS, conn); +         } -     } the}

JDBC Learning Note (12): Using JDBC to call stored procedures and batch processing