使用JDBC的步骤,随意创建一个表,创建一个Java项目,编程对该表进行增删改查的操作。
可以只用Eclipse创建如下目录
DB.java
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class DB {
static Connection con = null;
static Statement stmt = null;
static ResultSet rs = null;
public static void connectToAndQueryDatabase(String driverClass, String database, String username, String password,
String querySQL) {
try {
// 加载JDBC驱动类
Class.forName(driverClass);
// 连接数据库
// DriverManager.setLogWriter(new PrintWriter(System.out));
con = DriverManager.getConnection(database, username, password);
// 使用Statement对象执行查询sql语句并获取查询结果存储在ResultSet对象中
stmt = con.createStatement();
rs = stmt.executeQuery(querySQL);
// 根据rs对象获取查询结果的元数据:表的列数
ResultSetMetaData rsmd=rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//输出元数据:列名
for(int i=1;i<=columnCount;i++){
System.out.print(rsmd.getColumnName(i) + "\t");
}
System.out.println();
// 循环输出查询结果
while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
// 输出每列的字段值,列号从1开始编号
System.out.print(rs.getObject(i) + "\t");
}
System.out.println();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭数据库连接
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (con != null && con.isClosed()) {
con.close();
con = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void connectToAndUpdateDatabase(String driverClass, String database, String username, String password,
String updateSQL) {
try {
// 加载JDBC驱动类
Class.forName(driverClass);
// 连接数据库
// DriverManager.setLogWriter(new PrintWriter(System.out));
con = DriverManager.getConnection(database, username, password);
// 使用Statement对象执行更新sql语句,返回值n记录更新了几条记录
stmt = con.createStatement();
int n = stmt.executeUpdate(updateSQL);
System.out.println("已更新了"+n+"条记录");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭数据库连接
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (con != null && con.isClosed()) {
con.close();
con = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
delete.java
package test;
public class delete {
public static void main(String[] args) {
// TODO Auto-generated method stub
String driverClass="com.mysql.cj.jdbc.Driver";
//数据库URL字符串
String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
//数据库用户名
String username="root";
//数据库密码
String password="hongfengting";
String deleteSQL="DELETE FROM `student`.`student1` WHERE (`no` = '005');";
DB.connectToAndUpdateDatabase(driverClass, database, username, password, deleteSQL);
}
}
insert.java
package test;
public class insert {
public static void main(String[] args) {
// TODO Auto-generated method stub
//数据库驱动字符串
String driverClass="com.mysql.cj.jdbc.Driver";
//数据库URL字符串
String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
//数据库用户名
String username="root";
//数据库密码
String password="hongfengting";
//更新语句
String insertSQL="INSERT INTO `student`.`student1` (`no`, `name`, `sex`) VALUES ('005', '小红', '女');";
DB.connectToAndUpdateDatabase(driverClass, database, username, password, insertSQL);
}
}
select.java
package test;
import java.sql.SQLException;
public class select {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//数据库驱动字符串
String driverClass="com.mysql.cj.jdbc.Driver";
//数据库URL字符串
String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
//数据库用户名
String username="root";
//数据库密码
String password="hongfengting";
//查询语句
String querySQL="select * from student1";
DB.connectToAndQueryDatabase(driverClass, database, username, password, querySQL);
}
}
update.java
package test;
public class update {
public static void main(String[] args) {
// TODO Auto-generated method stub
//数据库驱动字符串
String driverClass="com.mysql.cj.jdbc.Driver";
//数据库URL字符串
String database="jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=UTF-8";
//数据库用户名
String username="root";
//数据库密码
String password="hongfengting";
//更新语句
String updateSQL="update student1 set SEX='女' where NO='001'";
DB.connectToAndUpdateDatabase(driverClass, database, username, password, updateSQL);
//DB.connectToAndQueryDatabase(driverClass, database, username, password, updateSQL);
}
}