在Java编程中，如何使用Prepared语句？假定数据库名称是：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, '市场部');

以下示例使用PrepareStatement()方法创建PreparedStatement对象。 它还使用PreparedStatement的setInt()和setString()方法来设置PreparedStatement的参数。

package com.yiibai;

import java.sql.*;

public class CommitStatement {
    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);
        con.setAutoCommit(false);
        Statement stmt = con.createStatement();
        String createTable = "CREATE TABLE `employees` ( `id` int(11) NOT NULL, `age` int(11) NOT NULL,`name` varchar(64) DEFAULT NULL,`dept_id` int(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        String query = "insert into employees(id, name,age,dept_id) values(1,'Maxsu', 27, 1)";
        String query1 = "insert into employees(id, name,age,dept_id) values(5,'苏小牛', 24, 2)";
        String query2 = "select * from employees";
        // 创建表
        stmt.execute(createTable);
        con.commit();

        ResultSet rs = stmt.executeQuery(query2);
        int no_of_rows = 0;

        while (rs.next()) {
            no_of_rows++;
        }
        System.out.println("No. of rows before commit statement = " + no_of_rows);
        con.setAutoCommit(false);
        stmt.execute(query1);
        stmt.execute(query);
        con.commit();
        rs = stmt.executeQuery(query2);
        no_of_rows = 0;

        while (rs.next()) {
            no_of_rows++;
        }
        System.out.println("No. of rows after commit statement = " + no_of_rows);
    }
}

上述代码示例将产生以下结果。

Id Name age
=====================================
100  MaxSu   28
101  WeiWang   25
102  KidaSu   30
103  KobeBryant   28
108  苏小牛   26

注：如果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

纠错/补充 收藏