jdbc配置及使用测试

1 DROP TABLE IF EXISTS t_user;
2 
3 CREATE TABLE t_user(
4 id INT(11) PRIMARY KEY AUTO_INCREMENT,
5 user_name VARCHAR(20) NOT NULL DEFAULT "",
6 password VARCHAR(20) NOT NULL DEFAULT ""
7 )ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用户表';

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0"
 3          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5     <modelVersion>4.0.0</modelVersion>
 6 
 7     <groupId>com.xiaostudy</groupId>
 8     <artifactId>jdbc_test1</artifactId>
 9     <version>1.0.0</version>
10 
11     <properties>
12         <jdbc.version>5.1.46</jdbc.version>
13     </properties>
14 
15     <dependencies>
16         <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
17         <dependency>
18             <groupId>mysql</groupId>
19             <artifactId>mysql-connector-java</artifactId>
20             <version>${jdbc.version}</version>
21         </dependency>
22     </dependencies>
23 </project>

 1 package com.xiaostudy.Base;
 2 
 3 /**
 4  * Created with IntelliJ IDEA.
 5  * User: Administrator
 6  * Date: 2019/5/4
 7  * Time: 12:10
 8  * Description: No Description
 9  */
10 public class User {
11 
12     private Integer id;
13     private String userName;
14     private String password;
15 
16     public Integer getId() {
17         return id;
18     }
19 
20     public void setId(Integer id) {
21         this.id = id;
22     }
23 
24     public String getUserName() {
25         return userName;
26     }
27 
28     public void setUserName(String userName) {
29         this.userName = userName;
30     }
31 
32     public String getPassword() {
33         return password;
34     }
35 
36     public void setPassword(String password) {
37         this.password = password;
38     }
39 
40     @Override
41     public String toString() {
42         return "User{" +
43                 "id=" + id +
44                 ", userName='" + userName + '\'' +
45                 ", password='" + password + '\'' +
46                 '}';
47     }
48 }

1 driver = com.mysql.jdbc.Driver
2 url = jdbc:mysql://localhost:3306/lw_test?useUnicode=true&characterEncoding=UTF-8
3 userName = root
4 password = root

 1 package com.xiaostudy.util;
 2 
 3 import java.io.FileInputStream;
 4 import java.io.IOException;
 5 import java.sql.*;
 6 import java.util.Properties;
 7 
 8 
 9 /**
10  * Created with IntelliJ IDEA.
11  * User: xiaostudy
12  * Date: 2019/5/4
13  * Time: 11:58
14  * Description: No Description
15  */
16 public class JdbcUtil {
17 
18     private static Connection connection;
19 
20     public static Connection getConnection() {
21         if(null != connection) {
22             return connection;
23         }
24 
25         Properties pt = new Properties();
26         try {
27             FileInputStream fis = new FileInputStream("src\\main\\resources\\db.properties");
28             pt.load(fis);
29         } catch (IOException e) {
30             e.printStackTrace();
31         }
32         String driver = pt.getProperty("driver");
33         String url = pt.getProperty("url");
34         String userName = pt.getProperty("userName");
35         String password = pt.getProperty("password");
36 
37         if(null == driver || null == url || null == userName || null == password) {
38             return null;
39         }
40 
41         Connection conn = null;
42         try {
43             Class.forName(driver); //classLoader,加载对应驱动
44             conn = DriverManager.getConnection(url, userName, password);
45         } catch (ClassNotFoundException e) {
46             e.printStackTrace();
47         } catch (SQLException e) {
48             e.printStackTrace();
49         }
50         return conn;
51     }
52 
53     public static void close(PreparedStatement pstmt, Connection conn,ResultSet rs) {
54         try {
55             if(null != rs) {
56                 rs.close();
57                 rs = null;
58             }
59             if(null != pstmt) {
60                 pstmt.close();
61                 pstmt = null;
62             }
63             if(null != conn) {
64                 conn.close();
65                 conn = null;
66             }
67         } catch (SQLException e) {
68             e.printStackTrace();
69         }
70     }
71 }

  1 package com.xiaostudy.dao;
  2 
  3 import com.xiaostudy.Base.User;
  4 import com.xiaostudy.util.JdbcUtil;
  5 import com.xiaostudy.util.MyDataSource;
  6 
  7 import java.sql.Connection;
  8 import java.sql.PreparedStatement;
  9 import java.sql.ResultSet;
 10 import java.sql.SQLException;
 11 import java.util.ArrayList;
 12 import java.util.List;
 13 
 14 /**
 15  * Created with IntelliJ IDEA.
 16  * User: xiaostudy
 17  * Date: 2019/5/4
 18  * Time: 16:47
 19  * Description: No Description
 20  */
 21 public class UserDao {
 22 
 23     public int insert(User user) {
 24         if(null == user) {
 25             return 0;
 26         }
 27 
 28         Connection conn = JdbcUtil.getConnection();
 29         if(null == conn) {
 30             return 0;
 31         }
 32 
 33         int i = 0;
 34         String sql = "insert into t_user (user_name,password) values(?,?)";
 35         PreparedStatement pstmt = null;
 36         try {
 37             pstmt = (PreparedStatement) conn.prepareStatement(sql);
 38             pstmt.setString(1, user.getUserName());
 39             pstmt.setString(2, user.getPassword());
 40             i = pstmt.executeUpdate();
 41         } catch (SQLException e) {
 42             e.printStackTrace();
 43         } finally {
 44             JdbcUtil.close(pstmt, conn, null);
 45         }
 46         return i;
 47     }
 48 
 49     public int update(User user) {
 50         if(null == user || null == user.getId() || user.getId() <= 0) {
 51             return 0;
 52         }
 53 
 54         Connection conn = JdbcUtil.getConnection();
 55         if(null == conn) {
 56             return 0;
 57         }
 58 
 59         int i = 0;
 60         String sql = "update t_user set user_name='" + user.getUserName() + "', password='" + user.getPassword() + "' where id='" + user.getId() + "' ";
 61         PreparedStatement pstmt = null;
 62         try {
 63             pstmt = (PreparedStatement) conn.prepareStatement(sql);
 64             i = pstmt.executeUpdate();
 65         } catch (SQLException e) {
 66             e.printStackTrace();
 67         } finally {
 68             JdbcUtil.close(pstmt, conn, null);
 69         }
 70         return i;
 71     }
 72 
 73     public List<User> getAll() {
 74         List<User> list = new ArrayList<User>();
 75         Connection conn = JdbcUtil.getConnection();
 76         if(null == conn) {
 77             return list;
 78         }
 79 
 80         String sql = "select id, user_name, password from t_user";
 81         PreparedStatement pstmt = null;
 82         ResultSet rs = null;
 83         try {
 84             pstmt = (PreparedStatement)conn.prepareStatement(sql);
 85             rs = pstmt.executeQuery();
 86             while (rs.next()) {
 87                 User user = new User();
 88                 user.setId(rs.getInt(1));
 89                 user.setUserName(rs.getString("user_name"));
 90                 user.setPassword(rs.getString("password"));
 91                 list.add(user);
 92             }
 93         } catch (SQLException e) {
 94             e.printStackTrace();
 95         } finally {
 96             JdbcUtil.close(pstmt, conn, rs);
 97         }
 98         return list;
 99     }
100 
101     public User getUserByUserName(String userName) {
102         if(null == userName || userName.trim().length() <= 0) {
103             return null;
104         }
105 
106         Connection conn = JdbcUtil.getConnection();
107         if(null == conn) {
108             return null;
109         }
110 
111         User user = new User();
112         String sql = "select id, user_name, password from t_user where user_name='" + userName + "'";
113         PreparedStatement pstmt = null;
114         ResultSet rs = null;
115         try {
116             pstmt = (PreparedStatement)conn.prepareStatement(sql);
117             rs = pstmt.executeQuery();
118             while (rs.next()) {
119                 user.setId(rs.getInt(1));
120                 user.setUserName(rs.getString("user_name"));
121                 user.setPassword(rs.getString("password"));
122             }
123         } catch (SQLException e) {
124             e.printStackTrace();
125         } finally {
126             JdbcUtil.close(pstmt, conn, rs);
127         }
128         return user;
129     }
130 
131     public int delete(String userName) {
132         if(null == userName || userName.trim().length() <= 0) {
133             return 0;
134         }
135 
136         Connection conn = JdbcUtil.getConnection();
137         if(null == conn) {
138             return 0;
139         }
140 
141         int i = 0;
142         String sql = "delete from t_user where user_name='" + userName + "'";
143         PreparedStatement pstmt = null;
144         try {
145             pstmt = (PreparedStatement) conn.prepareStatement(sql);
146             i = pstmt.executeUpdate();
147         } catch (SQLException e) {
148             e.printStackTrace();
149         } finally {
150             JdbcUtil.close(pstmt, conn, null);
151         }
152         return i;
153     }
154 }

 1 package com.xiaostudy.test;
 2 
 3 import com.xiaostudy.Base.User;
 4 import com.xiaostudy.dao.UserDao;
 5 import com.xiaostudy.util.JdbcUtil;
 6 
 7 import java.util.List;
 8 
 9 /**
10  * Created with IntelliJ IDEA.
11  * User: Administrator
12  * Date: 2019/5/4
13  * Time: 15:05
14  * Description: No Description
15  */
16 public class Test_jdbc {
17 
18     public static void main(String[] agrs) {
19         UserDao userDao = new UserDao();
20 
21         //查询全部用户
22         List<User> list = userDao.getAll();
23         System.out.println(list);
24         //查询某个用户名下的用户
25 //        User user = userDao.getUserByUserName("张三");
26 //        System.out.println(user);
27 
28         // 新建用户
29 //        User user = new User();
30 //        user.setUserName("张三");
31 //        user.setPassword("123");
32 //        int insert = userDao.insert(user);
33 //        System.out.println(insert);
34 
35         //新建用户
36 //        User user = new User();
37 //        user.setUserName("李四");
38 //        user.setPassword("123444");
39 //        int insert = userDao.insert(user);
40 //        System.out.println(insert);
41 
42         //删除用户
43 //        int i = userDao.delete("李四");
44 //        System.out.println(i);
45 
46         //修改用户
47 //        User user = userDao.getUserByUserName("李四");
48 //        user.setPassword("1995");
49 //        int i = userDao.update(user);
50 //        System.out.println(i);
51 
52 //        list = userDao.getAll();
53 //        System.out.println(list);
54     }
55 }

 1 package com.xiaostudy.util;
 2 
 3 import java.io.PrintWriter;
 4 import java.sql.*;
 5 import java.util.LinkedList;
 6 import java.util.logging.Logger;
 7 
 8 import javax.sql.DataSource;
 9 
10 public class MyDataSource implements DataSource {
11     private static LinkedList<Connection> pool=new LinkedList<Connection>();
12     static{
13         for (int i = 0; i < 5; i++) {
14             Connection conn=JdbcUtil.getConnection();
15             pool.add(conn);
16         }
17     }
18     
19     @Override
20     public Connection getConnection() throws SQLException {
21         Connection conn=null;
22         if (0 == pool.size()) {
23             for (int i = 0; i < 5; i++) {
24                 conn=JdbcUtil.getConnection();
25                 pool.add(conn);
26             }
27         }
28         conn=pool.remove(0);
29         return conn;
30     }
31 
32     public static void close(Connection conn){
33         pool.add(conn);
34     }
35 
36     public static void close(PreparedStatement pstmt, Connection conn, ResultSet rs) {
37         if(null != conn) {
38             pool.add(conn);
39         }
40         JdbcUtil.close(pstmt, null, rs);
41     }
42 
43     @Override
44     public PrintWriter getLogWriter() throws SQLException {
45         return null;
46     }
47 
48     @Override
49     public int getLoginTimeout() throws SQLException {
50         // TODO Auto-generated method stub
51         return 0;
52     }
53 
54     @Override
55     public Logger getParentLogger() throws SQLFeatureNotSupportedException {
56         // TODO Auto-generated method stub
57         return null;
58     }
59 
60     @Override
61     public void setLogWriter(PrintWriter arg0) throws SQLException {
62         // TODO Auto-generated method stub
63 
64     }
65 
66     @Override
67     public void setLoginTimeout(int arg0) throws SQLException {
68         // TODO Auto-generated method stub
69 
70     }
71 
72     @Override
73     public boolean isWrapperFor(Class<?> arg0) throws SQLException {
74         // TODO Auto-generated method stub
75         return false;
76     }
77 
78     @Override
79     public <T> T unwrap(Class<T> arg0) throws SQLException {
80         // TODO Auto-generated method stub
81         return null;
82     }
83 
84 
85 
86     @Override
87     public Connection getConnection(String arg0, String arg1)
88             throws SQLException {
89 
90         return null;
91     }
92 
93 }

  1 package com.xiaostudy.dao;
  2 
  3 import com.xiaostudy.Base.User;
  4 import com.xiaostudy.util.MyDataSource;
  5 
  6 import java.sql.Connection;
  7 import java.sql.PreparedStatement;
  8 import java.sql.ResultSet;
  9 import java.sql.SQLException;
 10 import java.util.ArrayList;
 11 import java.util.List;
 12 
 13 /**
 14  * Created with IntelliJ IDEA.
 15  * User: xiaostudy
 16  * Date: 2019/5/4
 17  * Time: 16:47
 18  * Description: No Description
 19  */
 20 public class UserDao_MyDataSource {
 21 
 22     private static MyDataSource myDataSource;
 23 
 24     static {
 25         myDataSource = new MyDataSource();
 26     }
 27 
 28     public int insert(User user) {
 29         if(null == user) {
 30             return 0;
 31         }
 32 
 33         Connection conn = null;
 34         try {
 35             conn = myDataSource.getConnection();
 36         } catch (SQLException e) {
 37             e.printStackTrace();
 38         }
 39         if(null == conn) {
 40             return 0;
 41         }
 42 
 43         int i = 0;
 44         String sql = "insert into t_user (user_name,password) values(?,?)";
 45         PreparedStatement pstmt = null;
 46         try {
 47             pstmt = (PreparedStatement) conn.prepareStatement(sql);
 48             pstmt.setString(1, user.getUserName());
 49             pstmt.setString(2, user.getPassword());
 50             i = pstmt.executeUpdate();
 51         } catch (SQLException e) {
 52             e.printStackTrace();
 53         } finally {
 54             MyDataSource.close(pstmt, conn, null);
 55         }
 56         return i;
 57     }
 58 
 59     public int update(User user) {
 60         if(null == user || null == user.getId() || user.getId() <= 0) {
 61             return 0;
 62         }
 63 
 64         Connection conn = null;
 65         try {
 66             conn = myDataSource.getConnection();
 67         } catch (SQLException e) {
 68             e.printStackTrace();
 69         }
 70         if(null == conn) {
 71             return 0;
 72         }
 73 
 74         int i = 0;
 75         String sql = "update t_user set user_name='" + user.getUserName() + "', password='" + user.getPassword() + "' where id='" + user.getId() + "' ";
 76         PreparedStatement pstmt = null;
 77         try {
 78             pstmt = (PreparedStatement) conn.prepareStatement(sql);
 79             i = pstmt.executeUpdate();
 80         } catch (SQLException e) {
 81             e.printStackTrace();
 82         } finally {
 83             MyDataSource.close(pstmt, conn, null);
 84         }
 85         return i;
 86     }
 87 
 88     public List<User> getAll() {
 89         List<User> list = new ArrayList<User>();
 90         Connection conn = null;
 91         try {
 92             conn = myDataSource.getConnection();
 93         } catch (SQLException e) {
 94             e.printStackTrace();
 95         }
 96         if(null == conn) {
 97             return list;
 98         }
 99 
100         String sql = "select id, user_name, password from t_user";
101         PreparedStatement pstmt = null;
102         ResultSet rs = null;
103         try {
104             pstmt = (PreparedStatement)conn.prepareStatement(sql);
105             rs = pstmt.executeQuery();
106             while (rs.next()) {
107                 User user = new User();
108                 user.setId(rs.getInt(1));
109                 user.setUserName(rs.getString("user_name"));
110                 user.setPassword(rs.getString("password"));
111                 list.add(user);
112             }
113         } catch (SQLException e) {
114             e.printStackTrace();
115         } finally {
116             MyDataSource.close(pstmt, conn, rs);
117         }
118         return list;
119     }
120 
121     public User getUserByUserName(String userName) {
122         if(null == userName || userName.trim().length() <= 0) {
123             return null;
124         }
125 
126         Connection conn = null;
127         try {
128             conn = myDataSource.getConnection();
129         } catch (SQLException e) {
130             e.printStackTrace();
131         }
132         if(null == conn) {
133             return null;
134         }
135 
136         User user = new User();
137         String sql = "select id, user_name, password from t_user where user_name='" + userName + "'";
138         PreparedStatement pstmt = null;
139         ResultSet rs = null;
140         try {
141             pstmt = (PreparedStatement)conn.prepareStatement(sql);
142             rs = pstmt.executeQuery();
143             while (rs.next()) {
144                 user.setId(rs.getInt(1));
145                 user.setUserName(rs.getString("user_name"));
146                 user.setPassword(rs.getString("password"));
147             }
148         } catch (SQLException e) {
149             e.printStackTrace();
150         } finally {
151             MyDataSource.close(pstmt, conn, rs);
152         }
153         return user;
154     }
155 
156     public int delete(String userName) {
157         if(null == userName || userName.trim().length() <= 0) {
158             return 0;
159         }
160 
161         Connection conn = null;
162         try {
163             conn = myDataSource.getConnection();
164         } catch (SQLException e) {
165             e.printStackTrace();
166         }
167         if(null == conn) {
168             return 0;
169         }
170 
171         int i = 0;
172         String sql = "delete from t_user where user_name='" + userName + "'";
173         PreparedStatement pstmt = null;
174         try {
175             pstmt = (PreparedStatement) conn.prepareStatement(sql);
176             i = pstmt.executeUpdate();
177         } catch (SQLException e) {
178             e.printStackTrace();
179         } finally {
180             MyDataSource.close(pstmt, conn, null);
181         }
182         return i;
183     }
184 }

UserDao userDao = new UserDao();
换成
UserDao_MyDataSource userDao = new UserDao_MyDataSource();

 1 package com.xiaostudy.test;
 2 
 3 import com.xiaostudy.Base.User;
 4 import com.xiaostudy.dao.UserDao_MyDataSource;
 5 
 6 import java.util.List;
 7 
 8 /**
 9  * Created with IntelliJ IDEA.
10  * User: Administrator
11  * Date: 2019/5/4
12  * Time: 15:05
13  * Description: No Description
14  */
15 public class Test_jdbc2 {
16 
17     public static void main(String[] agrs) {
18         UserDao_MyDataSource userDao = new UserDao_MyDataSource();
19 
20         //查询全部用户
21         List<User> list = userDao.getAll();
22         System.out.println(list);
23         //查询某个用户名下的用户
24 //        User user = userDao.getUserByUserName("张三");
25 //        System.out.println(user);
26 
27         // 新建用户
28 //        User user = new User();
29 //        user.setUserName("张三");
30 //        user.setPassword("123");
31 //        int insert = userDao.insert(user);
32 //        System.out.println(insert);
33 
34         //新建用户
35 //        User user = new User();
36 //        user.setUserName("李四");
37 //        user.setPassword("123444");
38 //        int insert = userDao.insert(user);
39 //        System.out.println(insert);
40 
41         //删除用户
42 //        int i = userDao.delete("李四");
43 //        System.out.println(i);
44 
45         //修改用户
46 //        User user = userDao.getUserByUserName("李四");
47 //        user.setPassword("1995");
48 //        int i = userDao.update(user);
49 //        System.out.println(i);
50 
51 //        list = userDao.getAll();
52 //        System.out.println(list);
53     }
54 }