在Java编程中,以下示例使用connect.commit()方法来提交事务执行查询。假定数据库名称是: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, '市场部');
以下示例使用connect.commit()方法来执行提交事务和查询语句。
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); } }
上述代码示例将产生以下结果。
No. of rows before commit statement = 0 No. of rows after commit statement = 2
注:如果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
¥ 我要打赏 纠错/补充 收藏