连接池用具类
1 public class MyDbUtils {
2 /*静态常量*/
3
4 /**驱动包**/
5 private final static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
6
7 /**连接路径url**/
8 private final static String URL = "jdbc:mysql://127.0.0.1:3306/java0723?characterEncoding=UTF-8";
9
10 /**用户名**/
11 private final static String USERNAME = "root";
12
13 /**密码**/
14 private final static String PASSWORD = "123456";
15
16 /**创建连接池**/
17 public static BasicDataSource dataSource = new BasicDataSource();
18
19 //静态代码块
20 static {
21 //对连接池对象 进行基本的配置
22 /*这是要连接的数据库的驱动*/
23 dataSource.setDriverClassName(DRIVER_CLASS_NAME);
24 /*指定要连接的数据库地址*/
25 dataSource.setUrl(URL);
26 /*指定要连接数据的用户名*/
27 dataSource.setUsername(USERNAME);
28 /*指定要连接数据的密码*/
29 dataSource.setPassword(PASSWORD);
30 /*设置最大连接数*/
31 dataSource.setMaxActive(10);
32 }
33
34 /*获取连接池对象*/
35 public static DataSource getDataSource(){
36 return dataSource;
37 }
38
39 /*获取单个连接对象*/
40 public static Connection getConn(){
41 Connection conn = null;
42 try {
43 conn = dataSource.getConnection();
44 } catch (SQLException e) {
45 e.printStackTrace();
46 }
47 return conn;
48 }
49 }
Dao层
1 public class UserDao {
2
3 /*新增数据*/
4 public void add(Users user) throws SQLException {
5 Connection conn = JDBCUtils.getConn();
6 String sql = "insert into user (uname,pwd) values (?,?)";
7 QueryRunner qr = new QueryRunner();
8 int count = qr.update(conn, sql, user.getUname(), user.getPwd());
9 System.out.println(count);
10 conn.close();
11 }
12
13 /*更改数据*/
14 public void update(Users user) throws SQLException {
15 Connection conn = JDBCUtils.getConn();
16 String sql = "update user set uname=?,pwd=? where uid=?";
17 QueryRunner qr = new QueryRunner();
18 int count = qr.update(conn, sql, user.getUname(), user.getPwd(), user.getUid());
19 System.out.println(count);
20 conn.close();
21 }
22
23 /*删除数据*/
24 public void del(Users user) throws SQLException {
25 Connection conn = JDBCUtils.getConn();
26 String sql = "delete from user where uid=?";
27 QueryRunner qr = new QueryRunner();
28 int count = qr.update(conn, sql, user.getUid());
29 System.out.println(count);
30 conn.close();
31 }
32
33 /*获取结果集的第一条数据*/
34 public Object[] get1() throws SQLException {
35 Connection conn = JDBCUtils.getConn();
36 String sql = "select * from user";
37 QueryRunner qr = new QueryRunner();
38 Object[] query = qr.query(conn, sql, new ArrayHandler());
39 conn.close();
40 return query;
41 }
42
43 /*把查询的所有数据返回一个object类型的list集合*/
44 public List<Object[]> get2() throws SQLException {
45 Connection conn = JDBCUtils.getConn();
46 String sql = "select * from user";
47 QueryRunner qr = new QueryRunner();
48 List<Object[]> query = qr.query(conn, sql, new ArrayListHandler());
49 conn.close();
50 return query;
51 }
52
53 /*获取结果集的第一条数据并封装一个实例类*/
54 public Users get3() throws SQLException {
55 Connection conn = JDBCUtils.getConn();
56 String sql = "select * from user";
57 QueryRunner qr = new QueryRunner();
58 Users query = qr.query(conn, sql, new BeanHandler<Users>(Users.class));
59 conn.close();
60 return query;
61 }
62
63 /*获取结果集的并封装一个实例类,并加到list集合中*/
64 public List<Users> get4() throws SQLException {
65 Connection conn = JDBCUtils.getConn();
66 String sql = "select * from user";
67 QueryRunner qr = new QueryRunner();
68 List<Users> query = qr.query(conn, sql, new BeanListHandler<Users>(Users.class));
69 conn.close();
70 return query;
71 }
72
73 /*获取指定类的结果集,并封装到一个list集合中*/
74 public List<String> get5() throws SQLException {
75 Connection conn = JDBCUtils.getConn();
76 /*第一种*/
77 /*String sql = "select uname from user";*/
78 /*第二种*/
79 String sql = "select * from user";
80 QueryRunner qr = new QueryRunner();
81 /*第一种*/
82 /*List<String> query = qr.query(conn, sql, new ColumnListHandler<String>());*/
83 /*第二种*/
84 List<String> query = qr.query(conn, sql, new ColumnListHandler<String>("uname"));
85 conn.close();
86 return query;
87 }
88
89 /*查询一个参数，并返回一个object类型*/
90 public Object get6() throws SQLException {
91 Connection conn = JDBCUtils.getConn();
92 String sql = "select uname FROM `user` where uid = 6";
93 QueryRunner qr = new QueryRunner();
94 Object query = qr.query(conn, sql, new ScalarHandler<>(1));
95 conn.close();
96 return query;
97 }
98
99 /*查取结果集的一个条数据,封装为map集合中*/
100 public Map<String, Object> get7() throws SQLException {
101 Connection conn = JDBCUtils.getConn();
102 String sql = "select * FROM `user`";
103 QueryRunner qr = new QueryRunner();
104 Map<String, Object> query = qr.query(conn, sql, new MapHandler());
105 conn.close();
106 return query;
107 }
108
109 /*查取结果集所有数据,封装为map集合中,并加载到list集合中*/
110 public List<Map<String, Object>> get8() throws SQLException {
111 Connection conn = JDBCUtils.getConn();
112 String sql = "select * FROM `user`";
113 QueryRunner qr = new QueryRunner();
114 List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler());
115 conn.close();
116 return query;
117 }
118
119 /*连接池*/
120 public List<Map<String, Object>> get9() throws SQLException {
121 String sql = "select * FROM `user`";
122 /*创建 QueryRunner 对象是 传入一个 连接池对象*/
123 QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
124 List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
125 return query;
126 }
127 }