在Java编程中,如何使用JDBC连接更新(删除,插入或更新)表的数据内容?假定数据库名称是:testdb
,其中有一个表:employee
,这个表中有4
条记录。
创建数据库表的语句 -
use testdb;
create table if not exists employees (
id int not null,
age int not null,
first varchar (255),
last varchar (255)
);
INSERT INTO Employees VALUES (100, 28, 'Max', 'Su');
INSERT INTO Employees VALUES (101, 25, 'Wei', 'Wang');
INSERT INTO Employees VALUES (102, 30, 'Kida', 'Su');
INSERT INTO Employees VALUES (103, 28, 'Kobe', 'Bryant');
以下示例代码中执行SQL的更新,删除和插入命令来编辑或删除数据库表中的行内容。
package com.yiibai;
import java.sql.*;
public class UpdateTableContents {
public static void main(String[] args) {
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);
}
try {
Connection con = DriverManager.getConnection(DB_URL, User, Passwd);
Statement stmt = con.createStatement();
String query1 = "update employees set first = 'NewComer' where id = 102";
String query2 = "delete from employees where id = 100";
String query3 = "insert into employees(id,first,last,age) values(105,'Andy','Mark',26)";
stmt.execute(query1);
stmt.execute(query2);
stmt.execute(query3);
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
System.out.println("id name age");
while (rs.next()) {
int id = rs.getInt("id");
String first = rs.getString("first");
String last = rs.getString("last");
int age = rs.getInt("age");
System.out.println(id + " " + first + " " +last+ " " + age);
}
} catch (SQLException e) {
System.out.println("SQL exception occured" + e);
}
}
}
上述代码示例将产生以下结果。
id name age
101 Wei Wang 25
102 NewComer Su 30
103 Kobe Bryant 28
105 Andy Mark 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