以OOP(即面向对象编程)编写 JDBC 程序
1.JDBC工具类
将一些常用的 JDBC 方法封装成一个 JDBC 工具类, 方便管理及调用.
下面程序中的配置文件 jdbc.properties 必须在当前项目的 src 目录下.
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTools {
public static Connection getConnection() throws Exception {
InputStream is = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
pro.load(is);
String url = pro.getProperty("jdbc");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
Class.forName(pro.getProperty("driver"));
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
public static void update(String sql) {
Connection conn = null;
Statement state = null;
try {
conn = JDBCTools.getConnection();
state = conn.createStatement();
state.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCTools.release(null,state, conn);
}
}
public static void release(ResultSet rs,Statement state,Connection conn) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
|
2.操作Student数据表中的数据
利用 Java 的对象操作数据表中的记录, 这里的 Java 对象之后会有个专业名词: JavaBean
处理表的记录时, 不再一个字段一个字段的处理, 而是把它们封装成一个对象, 统一处理.
可以称为 O-R映射 即 对象与关系(也可以说数据表)之间的映射, 对象的属性与数据表的字段一一对应.
A.DB中的Student表
B.Java中的Student对象
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
46
47
|
import java.sql.Date;
public class Student {
private int id;
private String name;
private String email;
private Date date;
public Student() {
super();
}
public Student(int id, String name, String email, Date date) {
super();
this.id = id;
this.name = name;
this.email = email;
this.date = date;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", email=" + email + ", date=" + date + "]";
}
}
|
C.插入一条Student记录
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
|
import java.sql.Date;
import java.util.Scanner;
import org.junit.Test;
public class TestJDBCOOP {
@Test
public void testAddStudent() {
Student stu = getStudentFromConsole();
addStudent(stu);
}
private Student getStudentFromConsole() {
Student stu = new Student();
Scanner scanner = new Scanner(System.in);
System.out.print("请输入学生ID:");
stu.setId(scanner.nextInt());
System.out.print("请输入学生Name:");
stu.setName(scanner.next());
System.out.print("请输入学生Email:");
stu.setEmail(scanner.next());
System.out.print("请输入学生Birth:");
stu.setDate(Date.valueOf(scanner.next()));
System.out.println(stu.getDate());
scanner.close();
return stu;
}
public void addStudent(Student stu) {
String sql = "INSERT INTO Student VALUES("
+ stu.getId() +",'"
+ stu.getName() + "','"
+ stu.getEmail() + "','"
+ stu.getDate() + "')";
System.out.println(sql);
JDBCTools.update(sql);
}
}
|
D.查询数据表
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
import java.io.FilterInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import org.junit.Test;
public class TestJDBCOOP {
@Test
public void testSearchStudent() {
int type = getSearchType();
Student stu = searchStudent(type);
showMsg(stu);
}
private void showMsg(Student stu) {
if(stu == null) {
System.out.println("查无此人!");
}else {
System.out.println(stu);
}
}
private int getSearchType() {
System.out.print("请输入查询类型:1.id查询 2.姓名查询");
Scanner scanner = new Scanner(new FilterInputStream(System.in){
@Override
public void close() throws IOException {
}
});
int type = scanner.nextInt();
scanner.close();
if(type != 1 && type != 2) {
System.out.println("输入的查询类型有误!");
throw new RuntimeException();
}
return type;
}
public Student searchStudent(int type) {
String sql = null;
Scanner scanner = new Scanner(System.in);
if(type == 1) {
System.out.print("请输入要查询的id:");
int id = scanner.nextInt();
sql = "SELECT * FROM Student WHERE id = " + id;
}else {
System.out.print("请输入要查询的Name:");
String name = scanner.next();
sql = "SELECT * FROM Student WHERE name = '" + name +"'";
}
scanner.close();
return getStudent(sql);
}
private Student getStudent(String sql) {
Connection conn = null;
Statement state = null;
ResultSet rs = null;
Student stu = null;
try {
conn = JDBCTools.getConnection();
state = conn.createStatement();
rs = state.executeQuery(sql);
if(rs.next()) {
stu = new Student(
rs.getInt(1),
rs.getString(2).trim(),
rs.getString(3).trim(),
rs.getDate(4));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(rs, state, conn);
}
return stu;
}
}
|
注意: 一个方法A中使用了Scanner, 且在里面把它关闭了, 然后又在方法B里再用Scanner时Scanner in = new Scanner(System.in); 会报 java.util.nosuchelementexception 异常.
Java提供的IO包中, 一般都会处理资源级联关闭, 即关闭最外层包装的流, 内层被包被的流也会被关闭.
在Scanner类中使用了一个InputStreamReader()输入流, 对System.in进行再次包装. 当调用Scanner的close()方法时, 会执行InputStreamReader的关闭操作, 即调用用该包装类的close(). 该操作又会调用其内部包装的Sysetm.in的close操作.
若想解决关闭Scanner而不关闭System.in, 只需要对传入Scanner构造方法的System.in做下包装就可以了, 新包装的流要注意: 1.不产生新的流资源 2.是一个InputStream的实现类 3.close方法为空实现
你也可以利用上述方法, 解决其他类似的问题.
如下:
1
2
3
4
5
6
| Scanner scanner = new Scanner(new FilterInputStream(System.in){
@Override
public void close() throws IOException {
}
});
|
编写 JDBC 程序)
编写 JDBC 程序)
编写 JDBC 程序)
