Day35
连接池
概念:连接池相当于存放连接对象**(Connection)的容器,需要连接的使用就在池中取出来使用就为什么需要使用连接池?
前言:连接对象是Java**程序和数据库的通道,创建连接对象和销毁连接对象都是需要消耗时间和内存成本的,所以不要自行创建或销毁连接
1.调整和优化创建连接和销毁连接2.使得项目中的连接目数可控
3.提高连接对象的复用率
自定义连接池
package com.qf.Pool01; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.LinkedList; /** * 数据库连接池 */ public class FastConnectionPool { private String driverName; private String url; private String username; private String password; private int maxActive; LinkedList<Connection> list; public void setDriverName(String driverName) { this.driverName = driverName; } public void setUrl(String url) { this.url = url; } public void setUsername(String username) { this.username = username; } public void setPassword(String password) { this.password = password; } public void setMaxActive(int maxActive) { this.maxActive = maxActive; } public String getDriverName() { return driverName; } public String getUrl() { return url; } public String getUsername() { return username; } public String getPassword() { return password; } public int getMaxActive() { return maxActive; } public FastConnectionPool(String driverName, String url, String username, String password, int maxActive) { this.driverName = driverName; this.url = url; this.username = username; this.password = password; this.maxActive = maxActive; } public void init() throws SQLException { list =new LinkedList<>(); try { Class.forName(driverName); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } for(int i=0;i<maxActive;i++){ Connection connection = DriverManager.getConnection(url,username,password); list.add(connection); } } public Connection getConnection() throws SQLException { if(list==null){ init(); } Connection connection=list.removeFirst(); return connection; } public FastConnectionPool() { } public void recovery(Connection connection){ list.add(connection); } }
使用
public static void main(String[] args) { //创建数据库连接池 FastConnectionPool pool = new FastConnectionPool(); //设置参数 pool.setDriverName("com.mysql.cj.jdbc.Driver"); pool.setUrl("jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true"); pool.setUsername("root"); pool.setPassword("123456"); pool.setMaxActive(20); Connection connection=null; PreparedStatement statement=null; ResultSet resultSet=null; try { connection=pool.getConnection(); String sql="SELECT * FROM student"; statement=connection.prepareStatement(sql); resultSet = statement.executeQuery(); while(resultSet.next()){ int id=resultSet.getInt("id"); String name=resultSet.getString("name"); String sex=resultSet.getString("sex"); double salary=resultSet.getDouble("salary"); int age=resultSet.getInt("age"); String course=resultSet.getString("course"); System.out.println(id+"--"+name+"--"+sex+"--"+salary+"--"+age+"--"+course); } } catch (SQLException e) { throw new RuntimeException(e); }finally { if(resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(statement!=null){ try { statement.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(connection!=null){ pool.recovery(connection); } } }
规范自定义连接池
市面上有很多的数据库连接池,这些连接池都遵循了JDBC的规范-- DataSource接口
因此,为了规范,需要实现接口DataSource,但是此时关闭资源的方法close()并不能实现让连接回收,所以需要利用装饰器模式用另外的一个类实现connection接口重写close()等方法(由于设计职责单一性,不建议直接让连接池类再实现connection接口),以此达到规范性。
装饰器:
package com.qf.pool02; import java.sql.*; import java.util.LinkedList; import java.util.Map; import java.util.Properties; import java.util.concurrent.Executor; public class MyConnectionWrapper implements Connection { private Connection connection; private LinkedList<Connection> list; public MyConnectionWrapper() { } public MyConnectionWrapper(Connection connection, LinkedList<Connection> list) { this.connection = connection; this.list = list; } @Override public Statement createStatement() throws SQLException { return connection.createStatement(); } @Override public PreparedStatement prepareStatement(String sql) throws SQLException { return connection.prepareStatement(sql); } @Override public CallableStatement prepareCall(String sql) throws SQLException { return connection.prepareCall(sql); } @Override public String nativeSQL(String sql) throws SQLException { return connection.nativeSQL(sql); } @Override public void setAutoCommit(boolean autoCommit) throws SQLException { connection.setAutoCommit(autoCommit); } @Override public boolean getAutoCommit() throws SQLException { return connection.getAutoCommit(); } @Override public void commit() throws SQLException { connection.commit(); } @Override public void rollback() throws SQLException { connection.rollback(); } @Override public void close() throws SQLException { System.out.println("将连接包装类对象回收到连接池中"); list.add(this); } @Override public boolean isClosed() throws SQLException { return connection.isClosed(); } @Override public DatabaseMetaData getMetaData() throws SQLException { return connection.getMetaData(); } @Override public void setReadOnly(boolean readOnly) throws SQLException { connection.setReadOnly(readOnly); } @Override public boolean isReadOnly() throws SQLException { return connection.isReadOnly(); } @Override public void setCatalog(String catalog) throws SQLException { } @Override public String getCatalog() throws SQLException { return null; } @Override public void setTransactionIsolation(int level) throws SQLException { } @Override public int getTransactionIsolation() throws SQLException { return 0; } @Override public SQLWarning getWarnings() throws SQLException { return null; } @Override public void clearWarnings() throws SQLException { } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { return null; } @Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return null; } @Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return null; } @Override public Map<String, Class<?>> getTypeMap() throws SQLException { return null; } @Override public void setTypeMap(Map<String, Class<?>> map) throws SQLException { } @Override public void setHoldability(int holdability) throws SQLException { } @Override public int getHoldability() throws SQLException { return 0; } @Override public Savepoint setSavepoint() throws SQLException { return null; } @Override public Savepoint setSavepoint(String name) throws SQLException { return null; } @Override public void rollback(Savepoint savepoint) throws SQLException { } @Override public void releaseSavepoint(Savepoint savepoint) throws SQLException { } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return null; } @Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return null; } @Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return null; } @Override public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { return null; } @Override public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { return null; } @Override public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { return null; } @Override public Clob createClob() throws SQLException { return null; } @Override public Blob createBlob() throws SQLException { return null; } @Override public NClob createNClob() throws SQLException { return null; } @Override public SQLXML createSQLXML() throws SQLException { return null; } @Override public boolean isValid(int timeout) throws SQLException { return false; } @Override public void setClientInfo(String name, String value) throws SQLClientInfoException { } @Override public void setClientInfo(Properties properties) throws SQLClientInfoException { } @Override public String getClientInfo(String name) throws SQLException { return null; } @Override public Properties getClientInfo() throws SQLException { return null; } @Override public Array createArrayOf(String typeName, Object[] elements) throws SQLException { return null; } @Override public Struct createStruct(String typeName, Object[] attributes) throws SQLException { return null; } @Override public void setSchema(String schema) throws SQLException { } @Override public String getSchema() throws SQLException { return null; } @Override public void abort(Executor executor) throws SQLException { } @Override public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { } @Override public int getNetworkTimeout() throws SQLException { return 0; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } }
连接池:
package com.qf.pool02; import javax.sql.DataSource; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import java.util.LinkedList; import java.util.logging.Logger; public class FastConnectionPool implements DataSource { private String driverName; private String url; private String username; private String password; private int maxActive; private LinkedList<Connection> list; public FastConnectionPool(String driverName, String url, String username, String password, int maxActive) { this.driverName = driverName; this.url = url; this.username = username; this.password = password; this.maxActive = maxActive; } public FastConnectionPool() { } public void init() throws SQLException { list=new LinkedList<>(); try { Class.forName(driverName); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } for(int i=0;i<maxActive;i++){ Connection connection= DriverManager.getConnection(url,username,password); MyConnectionWrapper myConnectionWrapper=new MyConnectionWrapper(connection,list); list.add(myConnectionWrapper); } } public String getDriverName() { return driverName; } public void setDriverName(String driverName) { this.driverName = driverName; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } 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 int getMaxActive() { return maxActive; } public void setMaxActive(int maxActive) { this.maxActive = maxActive; } @Override public Connection getConnection() throws SQLException { if(list==null){ init(); } Connection connection=list.removeFirst(); return connection; } @Override public Connection getConnection(String username, String password) throws SQLException { return null; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } @Override public PrintWriter getLogWriter() throws SQLException { return null; } @Override public void setLogWriter(PrintWriter out) throws SQLException { } @Override public void setLoginTimeout(int seconds) throws SQLException { } @Override public int getLoginTimeout() throws SQLException { return 0; } @Override public Logger getParentLogger() throws SQLFeatureNotSupportedException { return null; } }
使用:
public static void main(String[] args) { //创建数据库连接池 FastConnectionPool pool=new FastConnectionPool(); //设置参数 pool.setDriverName("com.mysql.cj.jdbc.Driver"); pool.setUrl("jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true"); pool.setUsername("root"); pool.setPassword("123456"); pool.setMaxActive(30); Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; try { connection = pool.getConnection(); String sql = "select * from student"; statement = connection.prepareStatement(sql); resultSet = statement.executeQuery(); while(resultSet.next()){ int id = resultSet.getInt("id"); String name = resultSet.getString("name"); String sex = resultSet.getString("sex"); int age = resultSet.getInt("age"); float salary = resultSet.getFloat("salary"); String course = resultSet.getString("course"); System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course); } } catch (SQLException e) { throw new RuntimeException(e); } finally { if(resultSet != null){ try { resultSet.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(statement != null){ try { statement.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(connection != null){ try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } }
Druid连接池(德鲁伊连接池)
Druid 是目前比较流行的高性能的数据库连接池,由阿里巴巴开发
package com.qf.pool03; import com.alibaba.druid.pool.DruidDataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Test01 { /** * 知识点:Druid连接池 */ public static void main(String[] args) { //创建Druid连接池 DruidDataSource pool = new DruidDataSource(); //设置参数 pool.setDriverClassName("com.mysql.cj.jdbc.Driver"); pool.setUrl("jdbc:mysql://localhost:3306/2042javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true"); pool.setUsername("root"); pool.setPassword("123456"); pool.setMaxActive(50); Connection connection=null; PreparedStatement statement=null; ResultSet resultSet=null; try { connection= pool.getConnection(); String sql="SELECT * FROM student"; statement=connection.prepareStatement(sql); resultSet=statement.executeQuery(); while(resultSet.next()){ int id = resultSet.getInt("id"); String name = resultSet.getString("name"); String sex = resultSet.getString("sex"); int age = resultSet.getInt("age"); float salary = resultSet.getFloat("salary"); String course = resultSet.getString("course"); System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course); } } catch (SQLException e) { throw new RuntimeException(e); }finally { if(resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(statement!=null){ try { statement.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } }
Druid+DBUtil
使用自定义工具类DBUtil+Druid
package com.qf.utils; import com.alibaba.druid.pool.DruidDataSource; import java.io.IOException; import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.List; import java.util.Properties; public class DBUtil { //druid数据库连接池 private static DruidDataSource pool; static{ Properties properties = new Properties(); try { properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties")); } catch (IOException e) { throw new RuntimeException(e); } String driverName = properties.getProperty("driverName"); String url = properties.getProperty("url"); String username = properties.getProperty("username"); String password = properties.getProperty("password"); int maxActive = Integer.parseInt(properties.getProperty("maxActive")); pool = new DruidDataSource(); pool.setDriverClassName(driverName); pool.setUrl(url); pool.setUsername(username); pool.setPassword(password); pool.setMaxActive(maxActive); local = new ThreadLocal<>(); } private static ThreadLocal<Connection> local; /** * 开启事务 */ public static void startTransaction() throws SQLException { Connection connection = getConnection(); connection.setAutoCommit(false); } /** * 提交事务 */ public static void commit() throws SQLException { Connection connection = local.get(); if(connection != null){ connection.commit(); connection = null; } } /** * 回滚事务 */ public static void rollback() throws SQLException { Connection connection = local.get(); if(connection != null){ connection.rollback(); connection = null; } } /** * 获取连接对象 */ public static Connection getConnection() throws SQLException { Connection connection = local.get(); if(connection == null){ connection = pool.getConnection(); //设置事务的隔离界别 connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); local.set(connection); } return connection; } /** * 关闭资源 */ public static void close(Connection connection, Statement statement, ResultSet resultSet) { if(resultSet != null){ try { resultSet.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(statement != null){ try { statement.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(connection != null){ try { if(connection.getAutoCommit()) {//自动提交事务的情况下 connection.close(); local.set(null); } } catch (SQLException e) { throw new RuntimeException(e); } } } /** * 更新数据(添加、删除、修改) */ public static int commonUpdate(String sql,Object... params) throws SQLException { Connection connection = null; PreparedStatement statement = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); paramsHandler(statement,params); int num = statement.executeUpdate(); return num; } finally { close(connection,statement,null); } } /** * 主键回填 */ public static int commonInsert(String sql,Object... params) throws SQLException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; try { connection = getConnection(); statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS); paramsHandler(statement,params); statement.executeUpdate(); resultSet = statement.getGeneratedKeys(); int primaryKey = 0; if(resultSet.next()){ primaryKey = resultSet.getInt(1); } return primaryKey; } finally { close(connection,statement,resultSet); } } public static <T> List<T> commonQuery(Class<T> clazz,String sql,Object... params) throws SQLException, InstantiationException, IllegalAccessException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; try { connection = getConnection(); statement = connection.prepareStatement(sql); paramsHandler(statement,params); resultSet = statement.executeQuery(); //获取表信息对象 ResultSetMetaData metaData = resultSet.getMetaData(); //获取字段个数 int fieldCount = metaData.getColumnCount(); List<T> list = new ArrayList<>(); while(resultSet.next()){ //创建对象 T t = clazz.newInstance(); for (int i = 1; i <= fieldCount ; i++) { //获取字段名 String fieldName = metaData.getColumnName(i); //获取该数据行上对应字段的数据 Object val = resultSet.getObject(fieldName); //利用反射设置对象里对应的属性 setField(t,fieldName,val); } //将对象添加到List集合中 list.add(t); } return list; } finally { close(connection,statement,resultSet); } } /** * 处理sql命令中的参数 */ private static void paramsHandler(PreparedStatement statement,Object... params) throws SQLException { for (int i = 0; i < params.length; i++) { statement.setObject(i+1,params[i]); } } /** * 获取当前类及其父类的属性对象 * @param clazz class对象 * @param name 属性名 * @return 属性对象 */ private static Field getField(Class<?> clazz,String name){ for(Class<?> c = clazz;c != null;c = c.getSuperclass()){ try { Field field = c.getDeclaredField(name); return field; } catch (NoSuchFieldException e) { } catch (SecurityException e) { } } return null; } /** * 设置对象中的属性 * @param obj 对象 * @param name 属性名 * @param value 属性值 */ private static void setField(Object obj,String name,Object value){ Field field = getField(obj.getClass(), name); if(field != null){ field.setAccessible(true); try { field.set(obj, value); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } } } }
使用:
package com.qf.pool04; import com.qf.utils.DBUtil; import java.sql.SQLException; import java.util.List; public class Test01 { /** * 知识点:Druid连接池 + DBUtil */ public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException { String sql = "select * from student"; List<Student> stus = DBUtil.commonQuery(Student.class, sql); for (Student stu : stus) { System.out.println(stu); } } }
Web-Tomcat、Servlet
选中项目右键,添加框架,选择web应用,点击ok。
在web里创建html。
服务器的业务(java代码)放进src里。
JDBC、监听器、过滤器放入src里。
配Tomcat服务器(在运行哪里),点击本地,点Fix,ok。
WEB-INF是受保护的文件夹,浏览器不能直接访问。
在web.xml配置文件里进行配置,默认直接找index,没有找配置里写的文件。( 欢迎文件列表模式 , 欢迎文件(Welcome File)是一个 Web 应用程序中的默认页面,当用户访问 Web 应用的根 URL 或目录 URL 时,服务器自动显示这个页面。 )
<welcome-file-list> <welcome-file>aaa.html</welcome-file> </welcome-file-list>
servlet的作用:1.接受请求里的数据。2.处理业务。3.操作JDBC。4.利用响应返回数据。
场景:注册
写一个register.html的文件(表单),注意其中的服务器地址需要传入web.xml配置文件。
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <form action="register" method="post"> <input type="text" name="username"/>用户名 <br/> <input type="password" name="password"/>密码 <br/> <input type="radio" name="sex" value="man" checked="checked"/>男 <input type="radio" name="sex" value="woman"/>女 <br/> <input type="checkbox" name="hobbies" value="football" checked="checked"/>足球 <input type="checkbox" name="hobbies" value="basketball"/>篮球 <input type="checkbox" name="hobbies" value="shopping"/>购物 <br/> <input type="submit" value="提交"/> </form> </body> </html>
创建css文件夹,写css语句对html进行修饰。
img文件夹里放图片,js文件夹写js语句。
p{ color: Blue; font-size: 50px; }
window.onload = function (){ var btn = document.getElementById("btn"); btn.onclick=function (){ alert("警告信息"); } }
写一个Servlet的实现类,由于接口方法过于繁杂,SUN公司提供了两个默认实现类,分别是GenericServlet和HttpServlet,通过继承二者其一,避免了过于庞大的工作量。
在编写类前,先在WEB-INF文件夹中导入servlet-api.jar放在lib文件夹里,并在File的结构模块中进行配置。
在本场景中,子类重写service(HttpServletRequest req,HttpServletResponse resp)方法。通过req的get方法获取请求中的数据,如果是多选则获取数组。通过resp.getWrite()获取打印流,.println()方法进行打印。
中文出现乱码解决方案:设置编码格式。req.setCharacterEncoding(“UTF-8”),resp.setContentType(“text/html;charset=UTF-8”);
package com.qf.servlet; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; public class Servlet01 extends HttpServlet { /** * 客户端发送给服务器中当前的Servlet请求是调用的方法 * @param req 请求对象(方向:客户端->服务器) * @param resp 响应对象(方向:服务器->客户端) */ @Override protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { //设置编码格式 req.setCharacterEncoding("UTF-8"); resp.setContentType("text/html;charSet=UTF-8"); System.out.println("收到客户端的请求"); //获取请求数据 String username = req.getParameter("username"); String password = req.getParameter("password"); String sex = req.getParameter("sex"); String[] hobbies = req.getParameterValues("hobbies"); System.out.println(username+"--"+password+"--"+sex+"--"+hobbies.toString()); //利用响应返回数据 resp.getWriter().println("<P>注册成功</P>"); } }
配置servlet方法:
方法一:在web.xml配置文件中通过中<Servlet -name> 和<Servket -class>来注明位置,本质是反射。通过<Servlet -mapping>中<Servlet -name>和<url -pattern>通过服务器地址找到文件名。
浏览器通过http协议,找到端口号,找到Tomcat服务器,服务器找到项目,项目找到web.xml配置文件,配置文件里通过url-pattern找到类名,通过类名找到类对象全对象,再找到类,然后根据请求调用类里面的重写的Servlet接口的service()方法。方法通过请求(request)和响应(response)进行数据的接收和返回。
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" version="4.0"> <welcome-file-list> <welcome-file>welcome.html</welcome-file> </welcome-file-list> <servlet> <servlet-name>Servlet01</servlet-name> <servlet-class>com.qf.servlet.Servlet01</servlet-class> </servlet> <servlet-mapping> <servlet-name>Servlet01</servlet-name> <url-pattern>/register</url-pattern> </servlet-mapping> </web-app>
注意:这里必须要有欢迎文件。