1 import java.io.IOException;
2 import java.io.InputStream;
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 import java.util.Properties;
9
10 import org.apache.commons.dbcp.BasicDataSource;
11
12 public class DBUtils {
13 private static BasicDataSource dateSource;
14 static {
15 //创建属性对象
16 Properties prop = new Properties();
17 //得到文件的输入流
18 InputStream ips = DBUtils2.class.getClassLoader().getResourceAsStream("jdbc.properties");
19 //把文件加载到属性对象中
20 try {
21 prop.load(ips);
22 //读取数据
23 String driver = prop.getProperty("driver");
24 String url = prop.getProperty("url");
25 String username = prop.getProperty("username");
26 String password = prop.getProperty("password");
27 //创建数据源对象
28 dateSource = new BasicDataSource();
29 //设置数据库链接信息
30 dateSource.setDriverClassName(driver);
31 dateSource.setUrl(url);
32 dateSource.setUsername(username);
33 dateSource.setPassword(password);
34 //设置连接池参数
35 dateSource.setInitialSize(3);//初始连接数量
36 dateSource.setMaxActive(5);//最大连接数量
37
38 } catch (IOException e) {
39 e.printStackTrace();
40 }
41
42 }
43 //1、获取链接
44 public static Connection getConn() throws Exception {
45
46
47 //获取连接池中的连接
48 Connection conn = dateSource.getConnection();
49 return conn;
50 }
51 //2、关闭资源
52 public static void close(ResultSet rs,Statement stat,Connection conn) {
53 try {
54 if (rs!=null) {
55 rs.close();
56 }
57 } catch (SQLException e) {
58 e.printStackTrace();
59 }
60 try {
61 if (stat!=null) {
62 stat.close();
63 }
64 } catch (SQLException e) {
65 e.printStackTrace();
66 }
67 //关闭连接
68 try {
69 if (conn!=null) {
70 conn.close();
71 }
72 } catch (SQLException e) {
73 e.printStackTrace();
74 }
75 }
76 }
DAO
1 /**
2 * 依据用户名查询对应的用户信息。 如果找不到，返回null。
3 *
4 * @throws SQLException
5 */
6 public User find(String uname) throws SQLException {
7 User user = null;
8
9 Connection conn = null;
10 PreparedStatement ps = null;
11 ResultSet rs = null;
12
13 try {
14 conn = DBUtils.getconn();
15 String sql = "SELECT * FROM t_user " + "WHERE username=?";
16 ps = conn.prepareStatement(sql);
17 ps.setString(1, uname);
18 rs = ps.executeQuery();
19
20 if (rs.next()) {
21 int id = rs.getInt("id");
22 String pwd = rs.getString("password");
23 String email = rs.getString("email");
24
25 user = new User();
26 user.setId(id);
27 user.setUname(uname);
28 user.setPwd(pwd);
29 user.setEmail(email);
30
31 }
32
33 } catch (SQLException e) {
34 e.printStackTrace();
35 throw e;
36 } finally {
37 DBUtils.close(rs, ps, conn);
38 }
39
40 return user;
41 }
42 /**
43 * 删除指定信息
44 * @param id
45 * @throws SQLException
46 */
47 public void delete(int id) throws SQLException {
48 Connection conn = null;
49 PreparedStatement ps = null;
50
51 try {
52 conn = DBUtils.getconn();
53 String sql = "DELETE FROM t_user " + "WHERE id = ?";
54 ps = conn.prepareStatement(sql);
55 ps.setInt(1, id);
56 ps.executeUpdate();
57
58 } catch (SQLException e) {
59 e.printStackTrace();
60 throw e;
61 } finally {
62 DBUtils.close(null, ps, conn);
63 }
64 }
65
66 /**
67 * 将用户信息插入到t_user表。
68 *
69 * @throws SQLException
70 *
71 */
72 public void save(User user) throws SQLException {
73 Connection conn = null;
74 PreparedStatement ps = null;
75
76 try {
77 conn = DBUtils.getconn();
78 String sql = "INSERT INTO t_user " + "VALUES(null,?,?,?)";
79 ps = conn.prepareStatement(sql);
80 ps.setString(1, user.getUname());
81 ps.setString(2, user.getPwd());
82 ps.setString(3, user.getEmail());
83 ps.executeUpdate();
84
85 } catch (SQLException e) {
86 e.printStackTrace();
87 throw e;
88 } finally {
89 DBUtils.close(null, ps, conn);
90 }
91 }
92
93 /**
94 * 从t_user表中查询出所有用户的信息。 注： 一条记录对应一个User对象(即将记录中的数据 存放到User对象里面)。
95 *
96 * @throws SQLException
97 */
98 public List<User> findAll() throws SQLException {
99
100 List<User> users = new ArrayList<User>();
101
102 Connection conn = null;
103 PreparedStatement ps = null;
104 ResultSet rs = null;
105
106 try {
107 conn = DBUtils.getconn();
108 String sql = "SELECT * FROM t_user";
109 ps = conn.prepareStatement(sql);
110 rs = ps.executeQuery();
111
112 while (rs.next()) {
113
114 int id = rs.getInt("id");
115 String uname = rs.getString("username");
116 String pwd = rs.getString("password");
117 String email = rs.getString("email");
118
119 User user = new User();
120 user.setId(id);
121 user.setUname(uname);
122 user.setPwd(pwd);
123 user.setEmail(email);
124
125 users.add(user);
126
127 }
128
129 } catch (SQLException e) {
130 e.printStackTrace();
131 throw e;
132 } finally {
133 DBUtils.close(rs, ps, conn);
134 }
135
136 return users;
137
138 }
Java中访问数据库的步骤
1 //注册驱动
2 Class.forName("com.mysql.jdbc.Driver");
3 //建立连接
4 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "");
5 System.out.println("创建完毕");
6 //创建Statement
7 Statement stat = conn.createStatement();
8 String sql = "delete from jdbc01 where id=1";
9 //执行sql语句(若SQL语句为查询语句需要处理结果集)
10 stat.executeUpdate(sql);
11 System.out.println("删除完毕");
12 //关闭连接
13 stat.close();
14 conn.close();
数据库的基本连接
1 public static void main(String[] args) throws Exception {
2 //创建数据源对象
3 BasicDataSource dateSource = new BasicDataSource();
4 //设置数据库连接信息
5 dateSource.setDriverClassName("com.mysql.jdbc.Driver");
6 dateSource.setUrl("jdbc:mysql://localhost:3306/db3");
7 dateSource.setUsername("root");
8 dateSource.setPassword("root");
9 //设置连接池参数
10 dateSource.setInitialSize(3);//初始连接数量
11 dateSource.setMaxActive(5);//最大连接数量
12 //获取连接池中的连接
13 Connection conn = dateSource.getConnection();
14 System.out.println(conn);
15 }