java JDBC 使用DBCP连接池

文件结构:

1.数据库创建 db.users

create database db character set utf8;use db;create table users(uid int(32) primary key auto_increment,uname varchar(32) not null,upassword varchar(32) not null);alter table users convert to character set utf8;

2.(可选)插入一些用户

INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('abc', 'uabc');INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('神奇的我', 'abc123');INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('隔壁老王', 'hahaha');INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('瑟瑟发抖', 'sesefadou');INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('abcdefg', 'zxcvbnm');INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('神奇的他', 'aaaa123');INSERT INTO `db`.`users` (`uname`, `upassword`) VALUES ('神奇的她', 'bbbbb123');

3.配置db.properties配置文件

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306?characterEncoding=utf8username=rootpassword=123456

4.DBCPUtils.java

package DBCP;import org.apache.commons.dbcp.BasicDataSourceFactory;import javax.sql.DataSource;import java.io.InputStream;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;public class DBCPUtils {    private static DataSource dataSource=null;    static {        try {        //1.加载properties文件        InputStream is=DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties");        //2.加载输入流        Properties properties=new Properties();        properties.load(is);        //3.创建数据源        dataSource= BasicDataSourceFactory.createDataSource(properties);        }catch (Exception e) {            e.printStackTrace();        }    }    public static DataSource getDataSource()    {        return dataSource;    }    public static Connection getConnection()    {        try {            return dataSource.getConnection();        } catch (SQLException e) {            throw  new RuntimeException(e);        }    }}

5.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();            }        }    }}

6.JDBCTool.java

package jdbc;import DBCP.DBCPUtils;import com.mchange.v2.c3p0.ComboPooledDataSource;import jdbc_u1.C3P0Utils;import jdbc_u1.MyDataSourse;import javax.sql.DataSource;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;    public static void  testinsert(String uname,String upassword)    {        try {            conn= DBCPUtils.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);        }    }    public static void testSelect(String uname,String upassword)    {        try {            conn= DBCPUtils.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);        }    }    public static void testUpdate(String oldname,String oldpassword,String newname,String newpassword)    {        try {            conn= DBCPUtils.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);        }    }    public static void delete(String uname,String upassword)    {        try {            conn= DBCPUtils.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);        }    }}

7.Main.java

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

运行结果: