Java如何在資料庫上執行多個SQL命令？

在Java程式設計中，如何同時在資料庫上執行多個SQL命令？假定資料庫名稱是：testdb，其中有兩張表:employee和dept，employee表中有4條記錄,dept表中有2條記錄。

建立資料庫表的語句 -

use testdb;
-- 員工表
drop table if exists employees;
create table if not exists employees (
  id int not null primary key,
  age int not null,
  name varchar(64),
  dept_id int(10)
);
INSERT INTO employees VALUES (100, 28, 'MaxSu', 1);
INSERT INTO employees VALUES (101, 25, 'WeiWang', 2);
INSERT INTO employees VALUES (102, 30, 'KidaSu', 2);
INSERT INTO employees VALUES (103, 28, 'KobeBryant', 1);
----
-- 部門表
drop table if exists dept;
create table if not exists dept (
  id int not null primary key,
  name varchar (64)
);
INSERT INTO dept VALUES (1, '技術部');
INSERT INTO dept VALUES (2, '市場部');

以下範例中演示如何使用addBatch()和executeBatch()方法同時執行多個SQL命令。

package com.yiibai;

import java.sql.*;

public class ExecuteMultipleSQLStatements {
    public static void main(String[] args) throws Exception {
        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        String DB_URL = "jdbc:mysql://localhost/testdb?useSSL=false";
        String User = "root";
        String Passwd = "123456";
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("Class not found " + e);
        }
        Connection con = DriverManager.getConnection(DB_URL, User, Passwd);

        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        String insertEmp1 = "insert into employees(id,name,age,dept_id) values(110,'Jay',19, 1)";
        String insertEmp2 = "insert into employees(id,name,age,dept_id) values(111,'Weiwei',22,2)";
        String insertEmp3 = "insert into employees(id,name,age,dept_id) values(112,'Hkou',25,3)";
        con.setAutoCommit(false);

        stmt.addBatch(insertEmp1);
        stmt.addBatch(insertEmp2);
        stmt.addBatch(insertEmp3);

        ResultSet rs = stmt.executeQuery("select * from employees");
        rs.last();
        System.out.println("rows before batch execution= " + rs.getRow());
        stmt.executeBatch();
        con.commit();

        System.out.println("Batch executed");
        rs = stmt.executeQuery("select * from employees");
        rs.last();
        System.out.println("rows after batch execution = " + rs.getRow());
    }
}

上述程式碼範例將產生以下結果。

rows before batch execution= 4
Batch executed
rows after batch execution = 7

註：如果JDBC驅動程式安裝不正確，將獲得ClassNotfound異常。

Class not found java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
JDBC Class found
SQL exception occuredjava.sql.SQLException: No suitable driver found for jdbc:mysql://localhost/testdb