分割线

PreparedStatement

SQL 注入问题

SQL 注入问题及解决

  • 通过修改 SQL 指令字符串,使 SQL 服务器执行不正规或者恶意的 SQL 命令

  • 比如客户端可以通过修改 SQL 字符串,可以使服务器传回原本不能显示的数据,或者修改非指定的数据.


区别用法

PreparedStatement 可以防止 SQL 注入,而且效率更高.

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

import twenty_one.jdbc.demo2.utils.JDBCUtils;

public class TestPreparedStatement {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;

public static void main(String[] args) throws SQLException {
// 连接
connection = JDBCUtils.getConnection();

// ! 插入
// 问号'?'为占位符
String insert = "INSERT INTO jdbcstudy.users(NAME, PASSWORD, email, birthday) VALUES (?,?,?,?);";
//预编译sql
preparedStatement = connection.prepareStatement(insert);
//手动赋值
preparedStatement.setString(1, "weidows");
preparedStatement.setInt(2, 123456);
preparedStatement.setString(3, "[email protected]");
preparedStatement.setDate(4, new Date(new java.util.Date().getTime()));
// 执行
if (preparedStatement.executeUpdate() > 0) {
System.out.println("成功.");
}

// ! 查询
String query = "SELECT * From jdbcstudy.users where name = ?;";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, "weidows");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println("id = " + resultSet.getObject("id"));
System.out.println("name = " + resultSet.getObject("NAME"));
System.out.println("password = " + resultSet.getObject("PASSWORD"));
System.out.println("email = " + resultSet.getObject("email"));
System.out.println("birthday = " + resultSet.getObject("birthday"));
System.out.println("===============================================================");
}

// 断连
JDBCUtils.releaseConnection(connection, preparedStatement, resultSet);
}
}

分割线

IDEA+SQL

  • 图形化的,摸索摸索就差不多会了.

  • 在学这个 SQL 课程时,个人开始用的是 sqlyog,后来卸载了,直接用的 idea 内嵌的.

分割线

JDBC+事务

  • 创建表+插入数据

    create TABLE jdbcstudy.account
    (
    id int primary key auto_increment,
    name varchar(255),
    money DECIMAL
    );

    insert into jdbcstudy.account(name, money)
    VALUES ('A', 1000),
    ('B', 1000),
    ('C', 1000);

  • 事务

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

    import twenty_one.jdbc.demo2.utils.JDBCUtils;

    public class TestTransaction {
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;

    public static void main(String[] args) throws SQLException {
    try {
    connection = JDBCUtils.getConnection();
    //关闭数据库的自动提交功能,同时开启事务
    connection.setAutoCommit(false);

    String sql = "update account set money = money-500 where id = 1";
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.executeUpdate();

    String sql2 = "update account set money = money-500 where id = 2";
    preparedStatement = connection.prepareStatement(sql2);
    preparedStatement.executeUpdate();

    //业务完毕,提交事务
    connection.commit();
    System.out.println("事务操作成功");
    } catch (Exception e) {
    //如果失败,默认会回滚,也可以在这里自定义
    connection.rollback();
    e.printStackTrace();
    } finally {
    JDBCUtils.releaseConnection(connection, preparedStatement, resultSet);
    }
    }
    }

分割线

连接池

  • 概念上与线程池相同,因为频繁创建和断开连接耗费大量资源,利用池化技术来减轻资源损耗.

  • 数据库连接池接口 DataSource ,开源数据源实现类:

    • DBCP

    • C3P0

    • Druid: 阿里巴巴

    无论使用什么数据源,本质是不变的,DateSource 接口不会变,方法就不会变


  • 用法区别就是 utils 类中,加载驱动,连接 connection 和读取 properties 配置文件不需要我们自己写了

  • db.properties

    # DBCP的设置文件,属性名不能错
    driverClassName=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 & useSSL = true
    username=root
    password=123456
  • utils.DbcpUtils

    import java.io.FileInputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;

    import javax.sql.DataSource;

    import org.apache.commons.dbcp.BasicDataSourceFactory;

    public class DbcpUtils {
    private static Properties properties = new Properties();
    private static DataSource dataSource = null;

    static {
    // 加载连接信息
    try {
    properties.load(new FileInputStream("Java/src/main/java/twenty_one/jdbc/demo3/db.properties"));
    dataSource = BasicDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
    e.printStackTrace();
    }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
    return dataSource.getConnection();
    }

    // 释放资源
    public static void releaseConnection(Connection connection, Statement statement, ResultSet resultSet)
    throws SQLException {
    if (resultSet != null) {
    resultSet.close();
    }
    if (statement != null) {
    statement.close();
    }
    if (connection != null) {
    connection.close();
    }
    }
    }
  • TestDataSource

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

    import twenty_one.jdbc.demo3.utils.DbcpUtils;

    public class TestDataSource {
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;

    public static void main(String[] args) throws SQLException {
    // 连接
    connection = DbcpUtils.getConnection();

    // ! 插入
    // 问号'?'为占位符
    String insert = "INSERT INTO jdbcstudy.users(NAME, PASSWORD, email, birthday) VALUES (?,?,?,?);";
    //预编译sql
    preparedStatement = connection.prepareStatement(insert);
    //手动赋值
    preparedStatement.setString(1, "weidows");
    preparedStatement.setInt(2, 123456);
    preparedStatement.setString(3, "[email protected]");
    preparedStatement.setDate(4, new Date(new java.util.Date().getTime()));
    // 执行
    if (preparedStatement.executeUpdate() > 0) {
    System.out.println("成功.");
    }

    // ! 查询
    String query = "SELECT * From jdbcstudy.users where name = ?;";
    preparedStatement = connection.prepareStatement(query);
    preparedStatement.setString(1, "weidows");
    resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
    System.out.println("id = " + resultSet.getObject("id"));
    System.out.println("name = " + resultSet.getObject("NAME"));
    System.out.println("password = " + resultSet.getObject("PASSWORD"));
    System.out.println("email = " + resultSet.getObject("email"));
    System.out.println("birthday = " + resultSet.getObject("birthday"));
    System.out.println("===============================================================");
    }

    // 断连
    DbcpUtils.releaseConnection(connection, preparedStatement, resultSet);
    }
    }

分割线

各名词区别

简单分析jdbc、datasource数据源、数据库驱动,连接池,jdbcTemplate,mybatis、JPA、Hibernate的区别