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.Driverurl=jdbc:mysql://localhost:3306?characterEncoding=utf8username=rootpassword=123456

Main.java

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