最原始的JDBC逐渐进化

阶段1
原始阶段

import java.sql.*;

public class JDBCDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.编写用户信息和url     显示时区&serverTimezone=GMT%2B8
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&serverTimezone=GMT%2B8&charcterEncoding=utf8&useSSL=true";
        String username = "root";
        String pwd = "123456";
        //3.通过驱动管理获取数据库连接
        Connection connection = DriverManager.getConnection(url,username,pwd);
        //4.执行sql的statement对象
        Statement statement = connection.createStatement();
        String sql = "SELECT * from users";

        //5.创建返回的结果集
        ResultSet res = statement.executeQuery(sql);

        while (res.next()){
            System.out.println("id:"+res.getObject("id"));
            System.out.println("name:"+res.getObject("NAME"));
            System.out.println("pwd:"+res.getObject("PASSWORD"));
            System.out.println("email:"+res.getObject("email"));
            System.out.println("birth:"+res.getObject("birthday"));
            System.out.println("====================================");
        }
        //6.释放连接

        res.close();
        statement.close();
        connection.close();
    }

}

阶段2

将数据库配置放在properties文件中，编写一个JdbcUtils类来管理连接：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=true
username=root
password=123456

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

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


    static {
        try{
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            Class.forName(driver);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConn() throws SQLException {
       return DriverManager.getConnection(url, username, password);
    }

    //释放资源
    public static void release(Connection conn, Statement stmt, ResultSet res){
        if(res!=null){
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

编写测试：插入测试

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

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet res = null;
        try {
            conn = JDBCUtils.getConn();
            stmt = conn.createStatement();
            String sql = "UPDATE users set `name` = \"gsysnd\" where id =1";
            int i = stmt.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,stmt,res);
        }
    }
}

查询测试：

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

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            stmt = conn.createStatement();
            String sql = "select * from users where id = 1";
            rs = stmt.executeQuery(sql);

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,stmt,rs);
        }

    }
}

使用statement的最大的缺点就是容易产生SQL注入！！！如下！！
SQL注入：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLzr {
    public static void main(String[] args) {
        login("'or' 1=1","123456");
    }
    public static void login(String username,String password){

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConn();
            stmt = conn.createStatement();

            //在此处进行字符串拼接进行SQL注入！！！
            String sql = "select * from users where `NAME`='"+username+"' and `PASSWORD`='"+password+"'";
            rs = stmt.executeQuery(sql);

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,stmt,rs);
        }

    }
}

阶段3：
使用statement的进阶版preparestatement来进行预编译，放在SQL注入的产生。
statement和preparestatement的区别看我下面这个博客：
点击查看statement和preparestatement的区别
下面这个代码主要是用preparestatement来进行了增删改查的操作！

//插入操作


import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {


        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConn();

            String sql = "INSERT into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values (?,?,?,?,?)";

            stmt = conn.prepareStatement(sql);


            stmt.setInt(1,5);
            stmt.setString(2,"gsy");
            stmt.setString(3,"123456");
            stmt.setString(4,"654382@qq.com");
            //时间戳的转换
            stmt.setDate(5, new Date(new java.util.Date().getTime()));

            int i = stmt.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,stmt,null);
        }
    }
}


//删除操作

import java.sql.*;

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {
            conn = JDBCUtils.getConn();

            String sql = "DELETE from users where id =?";

            stmt = conn.prepareStatement(sql);


            stmt.setInt(1,5);


            int i = stmt.executeUpdate();
            if (i>0){
                System.out.println("删除成功！！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,stmt,null);
        }
    }
}


//更新操作

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

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;


        try {
            conn = JDBCUtils.getConn();

            String sql = "update users set `name` = ? where `id` = ?;";

            stmt = conn.prepareStatement(sql);

            stmt.setString(1,"gsy12345");
            stmt.setInt(2,5);

            int i = stmt.executeUpdate();
            if (i>0){
                System.out.println("更新成功！！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,stmt,null);
        }
    }
}

//查询操作


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

public class TestSelect {
    public static void main(String[] args) {



        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConn();
            String sql = "select * from users where id=?;";

            stmt = conn.prepareStatement(sql);

            stmt.setInt(1,1);

            rs = stmt.executeQuery();

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JDBCUtils.release(conn,stmt,rs);
        }
    }
}

阶段4 数据库连接池

1.DBCP

public class JDBCUtils_DBCP {

    private  static DataSource dataSource;

    static {
        try{
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            dataSource = BasicDataSourceFactory.createDataSource(properties);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConn() throws SQLException {
        return dataSource.getConnection();
    }

    //释放资源
    public static void release(Connection conn, Statement stmt, ResultSet res){
        if(res!=null){
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

DBCP的properties配置文件

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=truecharacterEncoding=utf8useSSL=true&serverTimezone=GMT%2B8
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

C3P0
…