jdbc配置及使用测试
源码:https://github.com/xiaostudy/jdbc_test1
这是没有使用连接池的
目录
创建的sql语句create.sql
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 = '用户表';
maven配置pom.xml
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>
Base实体类User.java
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 }
mysql连接配置db.properties
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
重点,jdbc配置JdbcUtil.java
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 }
操作数据库UserDao.java
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 }
测试Test_jdbc.java
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 }
以下是使用自定义的连接池
目录
自定义的连接池MyDataSource.java
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 }
使用连接池的dao,UserDao_MyDataSource.java
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 }