实现第一个JDBC程序
1.搭建数据库环境
在MySQL中创建一个名称为jdbc的数据库，然后在该数据库中创建一个users表，创建
数据库和表的SQL语句如下所示。\

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE jdbc;
USE jdbc;

CREATE TABLE users(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40),
  password VARCHAR(40),
  email VARCHAR(60),
  birthday DATE
  )CHARACTER SET utf8 COLLATE utf8_general_ci;

数据库和表创建成功后，再向users表中插入3条数据

1
2
3
4
5
6
INSERT INTO users (NAME, PASSWORD, email, birthday)
VALUES('zs', '123456','zs@sina.com','1980-12-04');
INSERT INTO users (NAME, PASSWORD, email,birthday)
VALUES('lisi','123456','lisi0sina.com','1981-12-04');
INSERT INTO users (NAME, PASSWORD, email,birthday)
VALUES('wangwu','123456','wangwu@sina.com','1979-12-04');

2.创建项目环境，导入数据库驱动
在Eclipse中新建一个名称为chapter09的Web项目，将MySQL的数据库驱动文件mysql-connector-java-5.1.13-bin.jar复制到项目的lib目录中，
并发布到类路径下（MysQL驱动文件可以在其官网地址：http://dev.mysql.com/downloads/connector/j/页面中下载，在浏览器中输入该地址后即可进入下载页面，
单击页面Generally Available（GA）Releases窗口中的Looking for previous GA versions 超链接后，在显示出的下拉框中下载所需的驱动版本即可)。

3.编写JDBC程序
在项目chapter09的src目录下，新建一个名称为pidan.jdbc.example的包，
在该包中创建类Example01，该类用于读取数据库中的users表，并将结果输出到控制台

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
package baixiao.jdbc.example;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
 
public class Example01 {
	public static void main(String[] args) throws SQLException{
		Statement stmt=null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/jdbc";
			String username = "root";
			String password = "123456";
			conn = DriverManager.getConnection(url,username,password); 
			stmt = conn.createStatement();
			String sql = "select * from users";
			rs= stmt.executeQuery(sql);
			System.out.println("id\t |name |password\t |email\t |birthday");
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String psw = rs.getString("password");
				String email = rs.getString("email");
				Date birthday =rs.getDate("birthday");
				System.out.println(id + "\t |" + name + "\t |"+ psw + "\t |" + email +"\t |" +birthday);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			if(rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			if(stmt!=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt=null;
			}
			if(conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn=null;
			}
		}
	}
}

首先注册了MySQL数据库驱动，通过DriverManager获取一个Connection对象,
然后使用Connection对象创建一个Statement对象，Statement对象通过executeQuery()方法执行SQL语句，
并返回结果集ResultSet。接下来，通过遍历ResultSet得到最终的查询结果，最后关闭连接，回收了数据库资源。

在chapter09项目的pidan.jdbc.example包中创建一个名称为Example02的类，
在该类中使用PreparedStatement对象对数据库进行插入数据的操作

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
package baixiao.jdbc.example;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class Example02 {
	public static void main(String[] args) throws SQLException{
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/jdbc";
			String username = "root";
			String password = "123456";
			conn = DriverManager.getConnection(url,username,password); 
			String sql ="INSERT INTO users (NAME, PASSWORD, email,birthday)"+"VALUES(?,?,?,?)";
			preStmt =conn.prepareStatement(sql);
			preStmt.setString(1, "zl");
			preStmt.setString(2, "123456");
			preStmt.setString(3, "zl@sina.com");
			preStmt.setString(4, "1789-12-23");
			preStmt.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			if(preStmt!=null) {
				try {
					preStmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				preStmt = null;
			}
			if(conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn=null;
			}
		}
	}
}

首先通过Connection对象的prepareStatement()方法生成PreparedStatement对象，
然后调用PreparedStatement对象的setXxx()方法，给SQL语句中的参数赋值，
最后通过调用executeUpdate()方法执行SQL语句。运行成功后，会在users表中插入一条数据。

ResultSet对象
在chapter09项目的baixiao.jdbc.example包中创建一个名称为Example03的类,
该类中使用ResultSet对象取出指定数据的信息

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
package baixiao.jdbc.example;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Example03 {
	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/jdbc";
			String username = "root";
			String password = "123456";
			conn = DriverManager.getConnection(url,username,password);
			String sql = "select * from users";
			stmt = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stmt.executeQuery(sql);
			System.out.print("第2条数据的name值为：");
			rs.absolute(2);
			System.out.println(rs.getNString("name"));
			System.out.print("第1条数据的name值为：");
			rs.beforeFirst();  //将指针定位到结果集中第一行数据之前
			rs.next();	//指针向后滚动
			System.out.println(rs.getString("name"));
			System.out.print("第4条数据的name值为：");
			rs.afterLast(); //将指针定位到结果集中最后一条数据之后
			rs.previous();	//指针向前滚动
			System.out.println(rs.getString("name"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(stmt!=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			if(conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn=null;
			}
		}
	}
}

首先获取Connection对象连接数据库，然后通过Connection对象创建Statement对象并设置所需的两个常量，
接下来执行SQL语句，将获取的数据信息存放在ResultSet中 ，最后通过ResultSet对象的absolute()方法取出ResultSet中指定数据的信息并输出。

【任务】使用JDBC完成数据的增删改查
1.创建JavaBean
在chapter09项目的src目录下,创建包baixiao.jdbc.example.domain,并在该包下创建一个用于保存用户数据的User类，User类的具体实现方式如下

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
package baixiao.jdbc.example.domain;
 
import java.util.Date;
 
public class User {
	private int id;
	private String username;
	private String password;
	private String email;
	private Date birthday;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
}

2.创建工具类
由于每次操作数据库时，都需要加载数据库驱动、建立数据库连接以及关闭数据库连接，为了避免代码的重复书写，下面建立一个专门用于数据库相关操作的工具类。
在src下新建一个包baixiao.jdbc.example.utils,在包中创建一个封装了上述操作的工具类JDBCUtils，JDBCUtils的具体实现方式如下

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
package pidan.jdbc.example.utils;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCUtils {
	public static Connection getConnection() throws SQLException,ClassNotFoundException{
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/jdbc";
			String username = "root";
			String password = "123456";
			Connection conn = DriverManager.getConnection(url,username,password);
			return conn;
	}
	public static void release(Statement stmt, Connection conn) {
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn=null;
		}
	}
	public static void release(ResultSet rs,Statement stmt,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		release(stmt, conn);
	}
}

3.创建DAO
在src下新建一个名称为baixiao.jdbc.example.dao的包，在包中创建一个名称为UsersDao的类，该类中封装了对表users的添加、查询、删除和更新等操作，具体实现方式如下

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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
package baixiao.jdbc.example.dao;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
 
import pidan.jdbc.example.domain.User;
import pidan.jdbc.example.utils.JDBCUtils;
 
public class UsersDao {
	//添加用户的操作
	public boolean insert(User user) {
		Connection conn = null;
		Statement stmt=null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			stmt = conn.createStatement();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String birthday = sdf.format(user.getBirthday());
			String sql = "INSERT INTO users(id,name,password,email,birthday)"+
						"VALUES("
						+user.getId()
						+",'"
						+user.getUsername()
						+"','"
						+user.getPassword()
						+"','"
						+user.getEmail()
						+"','"
						+birthday + "')";
			int num = stmt.executeUpdate(sql);
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs,stmt,conn);
		}
		return false;
	}
	public ArrayList<User> findAll(){
		Connection conn = null;
		Statement stmt=null;
		ResultSet rs = null;
		ArrayList<User> list = new ArrayList<User>();
		try {
			conn = JDBCUtils.getConnection();
			stmt = conn.createStatement();
			String sql = "SELECT * FROM users";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				list.add(user);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs, stmt, conn);
		}
		return null;
	}
	public User find(int id) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			stmt=conn.createStatement();
			String sql = "SELECT * FROM users WHERE id=" + id;
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs,stmt, conn);
		}
		return null;
	}
	public boolean delete(int id) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			stmt= conn.createStatement();
			String sql = "DELETE FROM users WHERE id=" + id;
			int num =stmt.executeUpdate(sql);
			if(num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs, stmt,conn);
		}
		return false;
	}
	public boolean update(User user) {
		Connection conn =null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn= JDBCUtils.getConnection();
			stmt = conn.createStatement();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String birthday =sdf.format(user.getBirthday());
			String sql = "UPDATE users set name='"+user.getUsername()
						+"',password='"+user.getPassword() + "',email='"
						+user.getEmail() + "',birthday='" + birthday
						+"'WHERE id=" + user.getId();
			int num = stmt.executeUpdate(sql);
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.release(rs, stmt, conn);
		}
		return false;
	}
}

4.创建测试类
（1）在baixiao.jdbc.example包中编写测试类JdbclnsertTest,实现向 users表中添加数据的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package baixiao.jdbc.example;
 
import java.util.Date;
 
import pidan.jdbc.example.dao.UsersDao;
import pidan.jdbc.example.domain.User;
 
public class JdbcInsertTest {
	public static void main(String[] args) {
		//向users表插入一个用户信息
		UsersDao ud = new UsersDao();
		User user=new User();
		user.setId(5);
		user.setUsername("hl");
		user.setPassword("123");
		user.setEmail("hl@sina.com");
		user.setBirthday(new Date());
		boolean b= ud.insert(user);
		System.out.println(b);
	}
}

在baixiao.jdbc.example包中编写测试类FindAllUsersTest,该类用于实现读取users表中所有的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package baixiao.jdbc.example;
 
import java.util.ArrayList;
 
import pidan.jdbc.example.dao.UsersDao;
import pidan.jdbc.example.domain.User;
 
public class FinddAllUsersTest {
	public static void main(String[] args) {
		UsersDao usersDao = new UsersDao();
		ArrayList<User> list = usersDao.findAll();
		for (int i=0; i< list.size(); i++) {
			System.out.println("第" + (i+1) + "条数据的username的值为："
					+list.get(i).getUsername());
		}
	}
}

在baixiao.jdbc.example包中编写测试类FindUserByldTest,在该类中实现读取 users表中指定的数据

1
2
3
4
5
6
7
8
9
10
11
12
package baixiao.jdbc.example;
 
import pidan.jdbc.example.dao.UsersDao;
import pidan.jdbc.example.domain.User;
 
public class FindUserByldTest {
	public static void main(String[] args) {
		UsersDao usersDao = new UsersDao();
		User user = usersDao.find(1);
		System.out.println("id为1的User对象的name值为："+user.getUsername());
	}
}

在baixiao.jdbc.example包中编写测试类UpdateUserTest,在该类中实现修改users表中数据的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package baixiao.jdbc.example;
 
import java.util.Date;
 
import pidan.jdbc.example.dao.UsersDao;
import pidan.jdbc.example.domain.User;
 
public class UpdateUserTest {
	public static void main(String[] args) {
		UsersDao usersDao = new UsersDao();
		User user = new User();
		user.setId(4);
		user.setUsername("zhaoxiaoliu");
		user.setPassword("456");
		user.setEmail("zhaoxiaoliu@sina.com");
		user.setBirthday(new Date());
		boolean b=usersDao.update(user);
		System.out.println(b);
	}
}****

在baixiao.jdbc.example包中编写测试类DeleteUserTest,该类实现了删除users 表中数据的操作

1
2
3
4
5
6
7
8
9
10
11
package baixiao.jdbc.example;
 
import pidan.jdbc.example.dao.UsersDao;
 
public class DeleteUserTest {
	public static void main(String[] args) {
		UsersDao usersDao = new UsersDao();
		boolean b = usersDao.delete(4);
		System.out.println(b);
	}
}