JDBC介绍

JDBC使用

步骤1：引入MySQL驱动
  通过maven引入依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.41</version>
        </dependency>

步骤2：连接数据库

• 1、引入mysql-connector-java依赖包
• 2、引入MySQL驱动
• 3、通过DriverManager连接数据库获取Connection对象
• 4、通过Connection获取Statement对象进行SQL操作
• 5、如果是查询操作处理结果集：ResultSet
• 6、关闭资源

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            //获取连接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/tulun?useSSL=false", "root", "123456");
            //获取statement结果
            Statement statement = connection.createStatement();
            //查询数据
            String sql = "select * from student";
            ResultSet resultSet = statement.executeQuery(sql);
            //对结果进行处理
            while (resultSet.next()) {
                String id = resultSet.getString("SID");
                String name = resultSet.getString("Sname");
                String age = resultSet.getString("Sage");
                String sex = resultSet.getString("Ssex");
                System.out.println(id + name + age + sex);
            }
            //关闭资源
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

通过配置文件连接数据库

  首先在资源文件下创建一个jdbc.properties文件

user=root
password=123456
url=jdbc:mysql://localhost:3306/tulun?useSSL=false
classDriver=com.mysql.jdbc.Driver

  通过代码连接数据库

InputStream inputStream = FinalJdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //读取配置
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String classDriver = properties.getProperty("classDriver");
        //进行连接操作
        try {
            Class.forName(classDriver);
            Connection connection = DriverManager.getConnection(url, user, password);
            Statement statement = connection.createStatement();
            String sql = "select * from student";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String id = resultSet.getString("SID");
                String name = resultSet.getString("Sname");
                String age = resultSet.getString("Sage");
                String sex = resultSet.getString("Ssex");
                System.out.println(id + name + age + sex);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (inputStream!=null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

SQL注入问题

  所谓SQL注入是指利用非法的SQL拼接来达到入侵数据库的目的，通过下面的代码来为大家做一个演示：

try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/tulun?useSSL=false", "root", "123456");
            Statement statement = connection.createStatement();
            //数据库中有对应的数据，所以是可以正确执行的一个语句
//            String sql = "select * from sqlbug where id = " + 01 + " and password = '" + "zhangsan" + "'";
            //数据库中没有对应的数据，执行时无法正确得到结果
//            String sql = "select * from sqlbug where id = " + 01 + " and password = '" + "zhangsan01" + "'";
            //通过非法拼接字符串达到入侵数据库的目的，此时数据库中没有对应的数据，但是可以查询到想要的数据
            String sql = "select * from sqlbug where id = "  + "01 or 1=1"+ " and password = '" + "zhangsan01" + "'";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                String id = resultSet.getString("id");
                String password = resultSet.getString("password");
                System.out.println("id " + id + "  " + "password " + password);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

  因为statement是通过拼接字符串的形式来执行SQL语句，通过在用户 id 后面添加 or 1=1 此时就会将整个语句的语义改变为 where id = 01 or 1 = 1，此时不论我们传入的用户ID和密码为什么都会使得我们绕过对于用户密码的判断，从而达到非法入侵数据库的目的。

SQL注入问题解决

  在解决SQL注入问题时，我们可以采用 PrepareStatement 来解决SQL注入的问题，因为造成SQL注入的问题是非法的拼接字符串，所以 Preparestatement 采用了占位符的形式来将 SQL 和参数分别传递到MySQL服务端。

 Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    @Test
    public void prepareStatementTest() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/tulun?useSSL=false", "root", "123456");
            String sql ="select * from sqlbug where id = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,1);
            preparedStatement.setString(2, "zhangsan");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String password = resultSet.getString("password");
                System.out.println("id " + id + "   " + "password " + password);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

数据库事物

  在我们学习MySQL的时候已经了解过了事物的基本使用，在JDBC中同样能够完成对于事物的基本操作，例如：

connection.commit();//手动提交事物
connection.setAutoCommit(false);//设置事物的提交方式，false代表手动提交事物。true代表自动提交事物
connection.getAutoCommit();//获取当前的事物提交方式
Savepoint savepoint1 = connection.setSavepoint("savepoint1");//设置保存点
connection.rollback(savepoint1);//回滚

数据库连接池

  数据库连接是一种关键的、有限的、昂贵的资源，所以在使用过程中不断的去创建数据库连接会造成性能的浪费，数据库连接池则是在初始化时创建一定数量的数据库连接存放到连接池中，当我们需要使用数据库连接的时候直接可以存数据库连接池中获取到数据库连接，当我们不需要数据库连接的时候，又会将数据库连接放回到数据库连接池

c3p0连接池

1、引入c3p0依赖

<dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

2、创建配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--配置连接池mysql-->
    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/tulun?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 初始化连接数 -->
        <property name="initialPoolSize">10</property>
        <!--最大空闲时间,多少秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
        <property name="maxIdleTime">30</property>
        <!--连接池中保留的最大连接数。Default: 15 -->
        <property name="maxPoolSize">100</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">10</property>
    </named-config>

</c3p0-config>

3、代码使用数据库连接池

public  void main() {
        ComboPooledDataSource source = new ComboPooledDataSource("mysql");
        try {
            Connection connection = source.getConnection();
            String sql = "select * from sqlbug where id = ? and password = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,1 );
            preparedStatement.setString(2, "zhangsan");
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String password = resultSet.getString("password");
                System.out.println(id + password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }