Java executes multiple SQL statements

The technical points for executing multiple SQL at a time are as follows:

• DatabaseMetaData interface is to describe the overall comprehensive information about the database, because DatabaseMetaData is an interface, so there is no construction method, so you cannot use new to create the DatabaseMetaData object, However, it can be created by connection's GetMetaData () method. For example: DatabaseMetaData md=con.getmetadata ().

• The Supportsbatchupdates method of the DatabaseMetaData class is used to determine whether this database supports bulk updates. The return value type is Boolean, which returns true if this database supports bulk updates, otherwise false.

• The statement addbatch (String sql) method adds a given SQL command to the current list of commands for this Statement object, which can be called multiple times.

• The function of the statement ExecuteBatch () method is to submit a batch of commands to the database for execution, and if all commands succeed, an array of update counts is returned.

1.java Procedures for handling transactions

In the case of a database operation, if you execute multiple updated SQL statements (such as UPDATE or INSERT statements), if an exception occurs after the first article or the computer loses power, then the following SQL statement does not execute, this time set ourselves to commit the SQL statement, do not let JDBC auto-commit, in the format:

Conn.setautocommit (false); Stmt.addbatch ("INSERT into people values (078, ' Ding ', ' duo ')"); Stmt.addbatch ("INSERT into people values (' Nokia ', ' ddd ')"); Stmt.executebatch (); Execute multiple SQL statements; Conn.commit (); Transaction commit//Recovery autocommit mode Conn.setautocommit (true), ... if (con! = null) {con.rollback (); Con.setautocommit (true);}//If an exception is found, Then take a rollback

　　

If more than one statement repeats, only the arguments are the same.

Special case: If it is just the same parameter, the following is the same

PreparedStatement ps=conn.preparestatement ("INSERT into temp values (?)"); Ps.setint (1);p s.addbatch ();p s.setint (1),;p s.addbatch ();p s.executebatch ();

Example:

Package net.xsoftlab.dict; Import Java.sql.connection;import Java.sql.databasemetadata;import Java.sql.drivermanager;import Java.sql.resultset;import Java.sql.sqlexception;import java.sql.Statement;            public class Batch {/** determines whether the database supports batch */public static Boolean Supportbatch (Connection con) {try {            Get the meta data of the database DatabaseMetaData MD = Con.getmetadata ();        return Md.supportsbatchupdates ();        } catch (SQLException e) {e.printstacktrace ();    } return false;  }/** executes a batch of SQL statements */public static int[] Gobatch (Connection con, string[] sqls) throws Exception {if (Sqls = =        NULL) {return null;        } Statement sm = null;            try {sm = con.createstatement ();            for (int i = 0; i < sqls.length; i++) {Sm.addbatch (sqls[i]);//Add all SQL statements to statement}        Execute multiple SQL statements at once return Sm.executebatch (); } catch (SQLException e) {E.printstacktrace ();        } finally {sm.close ();    } return null;        The public static void main (string[] args) throws Exception {System.out.println ("The data when the batch was not executed is:");        Query ();        string[] Sqls = new String[3];        Sqls[0] = "UPDATE staff SET depart= ' personnel ' where name= ' Mali '"; SQLS[1] = "INSERT into the staff (name, age, Sex,address, Depart, Worklen,wage) VALUES (' Mali ', +, ' w ', ' China ', ' technology        ', ' 2 ', ' 2300 ');         SQLS[2] = "DELETE from the staff where name= ' marry '";        Connection con = null;            try {con = getconnection ();//Get database connection Boolean supportbatch = Supportbatch (con);//Determine if batch processing is supported SYSTEM.OUT.PRINTLN ("Batch processing supported?")            "+ Supportbatch);                if (Supportbatch) {int[] results = Gobatch (con, SQLS);//execute a batch of SQL statements//analyze the results of the execution                   for (int i = 0; i < sqls.length; i++) {if (Results[i] >= 0) {     SYSTEM.OUT.PRINTLN ("statement:" + sqls[i] + "executed successfully, affecting" + results[i] + "row data"); } else if (results[i] = = Statement.success_no_info) {System.out.println ("statement:" + sqls[i] +                    "Execution succeeded, the number of rows affected is unknown"); } else if (results[i] = = statement.execute_failed) {System.out.println ("statement:" + sqls[i] + "execution failed")                    ;        }}}} catch (ClassNotFoundException E1) {throw E1;        } catch (SQLException E2) {throw e2;        } finally {con.close ();//Close Database connection} System.out.println ("Data after batch execution is:");    Query ();        } public static Connection getconnection () {//database connection Connection con = null;                    try {class.forname ("com.mysql.jdbc.Driver");//load MySQL data-driven con = drivermanager.getconnection (     "Jdbc:mysql://localhost:3306/myuser", "root", "123456");//Create a data connection   } catch (Exception e) {System.out.println ("database connection failed");    } return con;        } public static void query () throws Exception {//query all data Connection con = getconnection ();        Statement st = Con.createstatement ();        ResultSet rs = st.executequery ("SELECT * from staff");            while (Rs.next ()) {String name = rs.getstring ("name");            int age = Rs.getint ("Age");            String sex = rs.getstring ("Sex");            String address = rs.getstring ("Address");            String depart = rs.getstring ("Depart");            String Worklen = rs.getstring ("Worklen");            String wage = rs.getstring ("wage"); SYSTEM.OUT.PRINTLN (name + "+ Age +" "+ Sex +" "+ Address +" "+ Depart +" "+ Worklen +" + "+        wage); }    } }

Program Interpretation:

1. The Support_batch () method determines whether the database supports batch processing of SQL statements. DatabaseMetaData the metadata object of the database by connection GetMetaData method, and then call DatabaseMetaData Supportsbatchupdates method to determine whether the database supports batch processing.

2. The Startbatch () method executes a set of SQL statements. The statement object that executes the SQL statement is created first, the pending SQL statement is added to the execution buffer by the Addbatch method of the statement class, and the ExecuteBatch method executes all of the SQL statements in the execution buffer, returning an array of integers. If the value of the array element is greater than or equal to 0, the statement executes successfully, indicating the number of rows of the record that executed the SQL statement modification, or if the value of the array element equals the Statement.success_no_info constant, indicating that the statement also succeeds, but does not know how many records have been modified specifically If the value of the array element equals the statement.execute_failed constant, the statement execution fails.

3. The Getconnection () method encapsulates the way the database is connected. If you need to use a database in your program, call this method directly.

4. The role of the query () method is to query the database, pass in the statement object that executes the query statement, and the SQL statement to execute, and return a ResultSet object by executing the SQL statement statement the ExecuteQuery method. Then call the next () method of resultset, remove the data based on the field name, and print it on the console.

2. Write a stored procedure

Java executes multiple SQL statements