20160409 javaweb 資料庫連線池

破玉發表於2016-04-09
JavaWeb資料庫

1.自己編寫資料庫連線池：

package com.dzq.pool;

import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

public class MyPool implements DataSource {
    private static List<Connection> pool=new LinkedList<Connection>();
    
    static{
        try{
        Class.forName("com.mysql.jdbc.Driver");
        for(int i=0;i<5;i++){
            Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/database01?Unicode=true&characterEncoding=utf-8","root","");
            pool.add(conn);
        }
        
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    
    @Override
    public Connection getConnection() throws SQLException {
        if(pool.size()==0){
            for(int i=0;i<3;i++){
                Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/database01?Unicode=true&characterEncoding=utf-8","root","");
                pool.add(conn);
            }
        }  
            
        final Connection conn=pool.remove(0);
            
            //利用動態代理改造close方法
    Connection  proxy= (Connection) Proxy.newProxyInstance(conn.getClass().getClassLoader(), new Class[]{Connection.class}, new InvocationHandler() {
                
                @Override
                public Object invoke(Object proxy, Method method, Object[] args)
                        throws Throwable {
                    if("close".equals(method.getName())){
                        //對於想改造的close方法，我們自己寫
                        retConn(conn);
                        return null;
                    }else{
                        //對於不想改造的方法，用被代理者身上相同的方法
                        return method.invoke(conn, args);
                    }
                }
            });
            System.out.println("獲取了一個連線，池裡還剩"+pool.size()+"個連線");
            return proxy;
            
    }

    private void retConn(Connection conn){
        try {
            if(conn!=null&&conn.isClosed()){
                pool.add(conn);
                System.out.println("還回了一個連線池裡還剩"+pool.size()+"個連線");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public Connection getConnection(String arg0, String arg1)
            throws SQLException {
        
        return null;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public int getLoginTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter arg0) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void setLoginTimeout(int arg0) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    
    
    

}

 

 

 

2.開源資料來源：
1.DBCP：
方式1：
BasicDataSource source = new BasicDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql:///day11");
source.setUsername("root");
source.setPassword("root");

方式2：
Properties prop = new Properties();
prop.load(new FileReader("dbcp.properties"));
BasicDataSourceFactory factory = new BasicDataSourceFactory();
DataSource source = factory.createDataSource(prop);

配置檔案中：
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///day11
username=root
password=root

#<!-- 初始化連線 -->
initialSize=10

#最大連線數量
maxActive=50

#<!-- 最大空閒連線 -->
maxIdle=20

#<!-- 最小空閒連線 -->
minIdle=5

#<!-- 超時等待時間以毫秒為單位 6000毫秒/1000等於60秒 -->
maxWait=60000

程式碼如下：

package com.dzq.jdbc;

import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPDemo {
    public static void main(String[] args) {
        
          Connection conn=null;
          PreparedStatement ps=null;
          ResultSet rs=null;
          try {
          Properties prop=new Properties();
        
          prop.load(new FileReader("dbcp.properties"));
          /*BasicDataSource source=new BasicDataSource();
          source.setDriverClassName("com.mysql.jdbc.Driver");
          source.setUrl("jdbc:mysql://localhost:3306/database01?Unicode=true&characterEncoding=utf-8");
          source.setUsername("root");
          source.setPassword("");*/
          BasicDataSourceFactory factory =new BasicDataSourceFactory();
          DataSource source=factory.createDataSource(prop);
        
            conn=source.getConnection();
            String sql="select * from account";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()) {
                String name=rs.getString("name");
                System.out.println(name);
                
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    rs=null;
                }
            }
            
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    ps=null;
                }
            }
            
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    conn=null;
                }
            }
        }
      }
}

 2.c3p0

C3P0資料來源：
方式1：
ComboPooledDataSource source = new ComboPooledDataSource();
source.setDriverClass("com.mysql.jdbc.Driver");
source.setJdbcUrl("jdbc:mysql:///day11");
source.setUser("root");
source.setPassword("root");

方式2：
ComboPooledDataSource source = new ComboPooledDataSource();
在類載入目錄下名稱為c3p0-config.xml的配置檔案中配置:
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///day11</property>
<property name="user">root</property>
<property name="password">root</property>
</default-config>
</c3p0-config>

c3p0配置檔案：

<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/database01?Unicode=true&amp;characterEncoding=utf-8</property>
    <property name="user">root</property>
    <property name="password"></property>
  </default-config>
  
   <named-config name="aaa"> 
       <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/database01?Unicode=true&amp;characterEncoding=utf-8</property>
    <property name="user">root</property>
    <property name="password"></property>
   </named-config>    
</c3p0-config>

 

 示例程式碼：

package com.dzq.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo {
     public static void main(String[] args) {
            
          Connection conn=null;
          PreparedStatement ps=null;
          ResultSet rs=null;
          try {
              ComboPooledDataSource source=new ComboPooledDataSource();
        /* ComboPooledDataSource source=new ComboPooledDataSource();
         source.setDriverClass("com.mysql.jdbc.Driver");
         source.setJdbcUrl("jdbc:mysql://localhost:3306/database01?Unicode=true&characterEncoding=utf-8");
         source.setUser("root");
         source.setPassword("");*/
              
              
         
            conn=source.getConnection();
            String sql="select * from account";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()) {
                String name=rs.getString("name");
                System.out.println(name);
                
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    rs=null;
                }
            }
            
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    ps=null;
                }
            }
            
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    conn=null;
                }
            }
        }
      }
}

3.tomcat 資料來源

tomcat內建的資料來源(DBCP):
~1.如何為tomcat配置資料來源
~tomcat/conf/context.xml檔案中配置<Context>配置在這個位置的資訊將會被所有的web應用所共享
~tomcat/conf/[engin]/[Host]/context.xml檔案中可以配置<Context>標籤,這裡配置的資訊將會被這臺虛擬主機中的所有web應用所共享

~tomcat/conf/server.xml檔案中的<Host>標籤中配置<Context>標籤,這是web應用的第一種配置方式,在這個標籤中配置的資訊將只對當前web應用起作用
~tomcat/conf/[engin]/[Host]/自己建立一個.xml檔案,在這個檔案中使用<Context>標籤配置一個web應用,這是web應用第二種配置方式,在這個<Context>標籤中配置的資訊將只會對當前web應用起作用
~web應用還有第三種配置方式:將web應用直接放置到虛擬主機管理的目錄.此時可以在web應用的META-INF資料夾下建立一個context.xml檔案,在其中可以寫<Context>標籤進行配置,這種配置資訊將只會對當前web應用起作用

<Resource name="mySource"
auth="Container"
type="javax.sql.DataSource"
username="root"
password="root"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql:///day11"
maxActive="8"
maxIdle="4"/>

 

~2.如何在程式中獲取這個資料來源
想要訪問jndi就必須在Servlet中才能執行下列程式碼:

Context initCtx = new InitialContext();
Context jndi = (Context) initCtx.lookup("java:comp/env");
DataSource source = jndi.lookUp("mySource");

 

 配置檔案context.xml

<?xml version="1.0" encoding="utf-8" ?>
<Context>
    <Resource name="mySource"
            auth="Container"
            type="javax.sql.DataSource"
            username="root"
            password=""
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/database01?Unicode=true&amp;characterEncoding=utf-8"
            maxActive="8"
            maxIdle="4"/>
</Context>

示例程式碼：

package com.dzq.init;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;


@WebServlet("/DataSourceInitServlet")
public class DataSourceInitServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
   
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
    @Override
    public void init() throws ServletException {
        try{
            Context initCtx = new InitialContext();
            Context jndi = (Context) initCtx.lookup("java:comp/env");
            DataSource source = (DataSource) jndi.lookup("mySource");
            
            Connection conn = source.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from account");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String name = rs.getString("name");
                System.out.println(name);
            }
            rs.close();
            ps.close();
            conn.close();
            
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
}

 

相關文章