java JDBC 自定义连接池

当执行sql语句比较频繁 。每一次执行 连接一次数据库 关闭连接  比较浪费资源。
连接池是来重复使用Connection 提交效率.

文件结构:

文件JDBCTool.java

package jdbc;
import jdbc_u1.MyDataSourse;

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

public class JDBCTool {
    private static Connection conn=null;
    private static PreparedStatement pstmt=null;
    private static ResultSet rs=null;
    private static MyDataSourse dataSourse=new MyDataSourse();
    public static void  testinsert(String uname,String upassword)
    {
        try {
            //conn=JDBCUtils.getConnection();
            conn=dataSourse.getConnection();
            String sql = "insert into db.users values(null,?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt.setString(2,upassword);
            int row=pstmt.executeUpdate();
            if(row>0)
            {
                System.out.println("注册成功");
            }
            else  System.out.println("注册失败");
        } catch (Exception e) {
            System.out.println("注册失败");
        }
        finally {
            //JDBCUtils.release(conn,pstmt,rs);
            dataSourse.BackConnection(conn);
        }
    }

    public static void testSelect(String uname,String upassword)
    {
        try {
            //conn=JDBCUtils.getConnection();
            conn=dataSourse.getConnection();
            String sql="select * from db.users where uname=? and upassword=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt.setString(2,upassword);
            rs=pstmt.executeQuery();
            if(rs.next())
            {
                System.out.println("验证成功");
            }
            else System.out.println("验证失败");
        } catch (Exception e) {
            System.out.println("验证失败");
        }
        finally {
            //JDBCUtils.release(conn,pstmt,rs);
            dataSourse.BackConnection(conn);
        }
    }

    public static void testUpdate(String oldname,String oldpassword,String newname,String newpassword)
    {
        try {
           // conn=JDBCUtils.getConnection();
            conn=dataSourse.getConnection();
            String sql = "update db.users set uname=?,upassword=? where uname=? and upassword=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,newname);
            pstmt.setString(2,newpassword);
            pstmt.setString(3,oldname);
            pstmt.setString(4,oldpassword);
            int row=pstmt.executeUpdate();
            if(row>0)
            {
                System.out.println("修改成功");
            }
            else
                System.out.println("修改失败");
        } catch (Exception e) {
            System.out.println("修改失败");
        }
        finally {
            //JDBCUtils.release(conn,pstmt,rs);
            dataSourse.BackConnection(conn);
        }
    }

    public static void delete(String uname,String upassword)
    {
        try {
            //conn=JDBCUtils.getConnection();
            conn=dataSourse.getConnection();
            String sql = "delete from db.users where uname=? and upassword=?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt.setString(2,upassword);
            int row=pstmt.executeUpdate();
            if(row>0)
            {
                System.out.println("注销成功");
            }
            else System.out.println("注销失败");
        } catch (Exception e) {
            System.out.println("注销失败");
        }
        finally {
            //JDBCUtils.release(conn,pstmt,rs);
            dataSourse.BackConnection(conn);
        }
    }

}

JDBCUtils.java

package jdbc;

import java.sql.*;
import java.util.ResourceBundle;

public class JDBCUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static{
        ResourceBundle bundle= ResourceBundle.getBundle("db");
        driver=bundle.getString("driver");
        url=bundle.getString("url");
        username=bundle.getString("username");
        password=bundle.getString("password");
    }

    public static Connection getConnection()
    {
        Connection conn=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn= DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs)
    {
        if(conn!=null)
        {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null)
        {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null)
        {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

MyDataSourse.java

package jdbc_u1;

import jdbc.JDBCUtils;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

public class MyDataSourse implements DataSource{
    //创建存储Connection的一个容器
    private static LinkedList<Connection> pool = new LinkedList<Connection>();

    //创建5个连接
    static {
        for(int i=0;i<5;i++)
            pool.add(JDBCUtils.getConnection());
    }

    //从池子里获取一个连接对象
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn=null;
        if(pool.size()==0)
        {
            for(int i=0;i<5;i++)
                pool.add(JDBCUtils.getConnection());
        }
        conn=pool.removeFirst();
        return conn;
    }
    //归还
    public void BackConnection(Connection conn)
    {
        pool.add(conn);
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @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;
    }


    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }
}

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306?characterEncoding=utf8
username=root
password=123456

Main.java

import jdbc.JDBCTool;

public class Main {
    public static void main(String[] args){
        JDBCTool.testSelect("abc","uabc");
        JDBCTool.testSelect("woc","absd");
    }
}

阅读更多