简介
JDBC：（Java Database Connectivity,Java数据库连接）是一组类。通过JDBC，可以在Java程序中使用相容的类和方法来读写记录以及执行其他是数据库访问操作。被称为驱动程序的类是连接到数据源的桥梁——对于每种流行的数据库（Oracle、Mysql等），都有相应的驱动程序。
对于每种常见的数据库使用任务，JDBC库中都有相应的类：
- 连接到数据库
- 使用SQL创建语句
- 在数据库中执行SQL查询
- 查看结果
这些JDBC类都位于java.sql包中
通过示例学习基本操作
本示例用的是Mysql数据库
先来规定一个环境
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| static class Student {
private String Id;
private String Name;
private String Sex;
private String Age;
Student(String Name, String Sex, String Age) {
this.Id = null;
this.Name = Name;
this.Sex = Sex;
this.Age = Age;
}
public String getId() {
return Id;
}
public void setId(String Id) {
this.Id = Id;
}
public String getName() {
return Name;
}
public void setName(String Name) {
this.Name = Name;
}
public String getSex() {
return Sex;
}
public void setSex(String Sex) {
this.Sex = Sex;
}
public String getAge() {
return Age;
}
public void setage(String Age) {
this.Age = Age;
}
}
|
连接数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| private static Connection getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/samp_db";
String username = "root";
String password = "";
Connection conn = null;
try {
Class.forName(driver);
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
|
开始操作数据库
insert(增)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| private static int insert(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setString(3, student.getAge());
i = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
|
update(改)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| private static int update(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
|
select(查)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| private static Integer getAll() {
Connection conn = getConn();
String sql = "select * from students";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();
System.out.println("============================");
while (rs.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(rs.getString(i) + "\t");
if ((i == 2) && (rs.getString(i).length() < 8)) {
System.out.print("\t");
}
}
System.out.println("");
}
System.out.println("============================");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
|
delete(删)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| private static int delete(String name) {
Connection conn = getConn();
int i = 0;
String sql = "delete from students where Name='" + name + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
|
调用运行
1
2
3
4
5
6
7
8
| public static void main(String args[]) {
JDBCOperation.getAll();
JDBCOperation.insert(new Student("Achilles", "Male", "14"));
JDBCOperation.getAll();
JDBCOperation.update(new Student("Bean", "", "7"));
JDBCOperation.delete("Achilles");
JDBCOperation.getAll();
}
|
分析
通过以上代码可以看出，JDBC的操作有通用的流程：
- 创建Connection对象、SQL查询命令字符串
- 对Connection对象传入SQL查询命令，获得PreparedStatement对象
- 对PreparedStatement对象执行executeUpdate()或executeQurey()获得结果
- 先后关闭PreparedStatement对象和Connection对象
- 可见，使用JDBC时，最常打交道的是Connection、PreparedStatement这两个类，以及select中的ResultSet类。
本文主要内容转载于：通过JDBC进行简单的增删改查（以MySQL为例）