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.Driver
url=jdbc:mysql://localhost:3306?characterEncoding=utf8
username=root
password=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");
    }
}
           

運作結果: