[大数据]Day07 狂神说Java-MySQL＞P40-P45

P40 Statement对象详解

executeUpdate和executeQuery

提取工具类

package com.hu.lesson02.utils;

import java.io.IOException;
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");
            // 1.驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.hu.lesson02;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement();
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

package com.hu.lesson02;

import com.hu.lesson02.utils.JdbcUtils;

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 st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement();
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(4,'kuangshen','123456','24736743@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

package com.hu.lesson02;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement();
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

package com.hu.lesson02;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement();
            String sql = "UPDATE users SET `NAME`='kuangshen',`email`='24736743@qq.com' WHERE id=1;";
            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

P41 SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露，SQL会被拼接

package com.hu.lesson02;

import com.hu.lesson02.utils.JdbcUtils;

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

public class SQLInjection {
    public static void main(String[] args) {
        login(" 'or' 1=1","123456");
    }
    // 登录业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement();
            String sql = "select * from users where `NAME` ='"+username+"' AND `password`='"+password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

P42 PreparedStatement对象

可以防止SQL注入并且效率更高

package com.hu.lesson03;

import com.hu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用?占位符代替参数
            String sql = "delete from users where id = ?";
            st = conn.prepareStatement(sql); //预编译SQL，先写SQL，但是不执行
            // 手动赋值
            st.setInt(1,4);
            // 执行
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

package com.hu.lesson03;

import com.hu.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用?占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql); //预编译SQL，先写SQL，但是不执行
            // 手动赋值
            st.setInt(1,4);
            st.setString(2,"rye");
            st.setString(3,"123456");
            st.setString(4,"520@qq.com");
            // 注意点，这里是sql.Date 数据库
            // util.Date是java的
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            // 执行
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

package com.hu.lesson03;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用?占位符代替参数
            String sql = "select * from users where id = ?";
            st = conn.prepareStatement(sql); //预编译SQL，先写SQL，但是不执行
            // 手动赋值
            st.setInt(1,1);
            // 执行
            rs = st.executeQuery();
            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.hu.lesson03;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            // 区别
            // 使用?占位符代替参数
            String sql = "update users set `NAME`=? where id =?";
            st = conn.prepareStatement(sql); //预编译SQL，先写SQL，但是不执行
            // 手动赋值
            st.setString(1,"狂神");
            st.setInt(2,1);
            // 执行
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

package com.hu.lesson03;

import com.hu.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) {
        login(" 'or' 1=1","123456");
    }
    // 登录业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            String sql = "select * from users where `NAME` =? AND `password`=?";
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当作字符
            // 假设其中存在转义字符则会被直接转义
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

P43 使用IDEA连接数据库

如果在IDEA中连接不上数据库，则在IDEA中修改数据库对应版本SQL

P44 JDBC操作事务

要么全部完成，要么都不完成
ACID原则：原子性、一致性、隔离性和持久性。
隔离性的问题：脏读、不可重复读和幻读。

步骤：开启事务》一组业务执行完毕，提交事务》可以在Catch显式定义回滚语句，但默认失败就会回滚

package com.hu.lesson04;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // 关闭数据库的自动提交,自动会开启事务
            conn.setAutoCommit(false);
            String sql1 = "update account set money = money-100 where name = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            String sql2 = "update account set money = money+100 where name = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();
            // 业务完毕，提交事务
            conn.commit();
            System.out.println("成功");
        } catch (SQLException e) {
            try {
                conn.rollback(); // 如果失败则回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.hu.lesson04;

import com.hu.lesson02.utils.JdbcUtils;

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

public class TestTransaction2 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // 关闭数据库的自动提交,自动会开启事务
            conn.setAutoCommit(false);
            String sql1 = "update account set money = money-100 where name = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            int x = 1/0; // error
            String sql2 = "update account set money = money+100 where name = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();
            // 业务完毕，提交事务
            conn.commit();
            System.out.println("成功");
        } catch (SQLException e) {
            try {
                conn.rollback(); // 如果失败则回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

P45 DBCP-C3P0连接池

数据库连接 – 执行完毕 – 释放
连接 – 释放十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

根据常用连接数设置最小连接数

最大连接数：业务最高承载上限
排队等待，等待超时

编写连接池，实现一个接口DataSource

DBCP\C3P0/Druid
使用以上数据库连接池后，在项目开发中就无需编写数据库连接代码

无论使用什么数据源，接口DataSource是不会变的，方法耶不会变