MySql-JDBC

JDBC是什么

每一个公司的数据库都需要与其对应的驱动才能进行操作，sun公司为了简化开发人员对对数据库的统一操作，提供了一个java操作数据库的规范，俗称JDBC，各个公司只需实现JDBC这个接口，而开发人员只需掌握JDBC接口的操作。

1. 加载驱动
2. 用户名，密码和连接数据库的url
3. 连接数据库，获得数据库对象
4. 获取执行sql的对象
5. 执行sql对象执行sql语句
6. 释放连接

package temp;
import java.sql.*;

//我的第一个JDBC程序
public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");  //固定写法

        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/learning?useunicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123.com";

        //3.连接成功，数据库对象 connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        //4.执行sql的对象  statement
        Statement statement = connection.createStatement();

        //5.执行sql的对象去执行  sql语句
        String sql = "select * from student";
        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("id = "+resultSet.getObject("id"));
            System.out.println("name = "+resultSet.getObject("name"));
            System.out.println("sex = "+resultSet.getObject("sex"));
            System.out.println("grade = "+resultSet.getObject("grade"));
            System.out.println("================================================");
        }

        //6.释放连接
        connection.close();
        statement.close();
        resultSet.close();
    }
}

在实际使用中，有很多的重复代码，所以一般写一个集成类，包含获取数据库对象和资源释放，这样就可以只关心sql语句

sql注入本质上就是命令的拼接，导致输入的字符串被当作sql来执行，如下：

package temp;

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

public class SQL注入 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();

            //模拟用户登录
            String user = "1";
            String password = "123456";

            String sql = "select * from user where id = " + user + " and password = " + password;
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("password"));
            }
        }catch (Exception e){

        }finally {
            JDBCUtils.release(connection,statement,resultSet);
        }

        System.out.println("=================================");
        try{
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();

            //SQL注入，拿到所有的账户和密码，本质就是命令的拼接
            String user = "1 or 1=1";
            String password = "1 or 1=1";

            String sql = "select * from user where id = " + user + " and password = " + password;
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("password"));
            }
        }catch (Exception e){

        }finally {
            JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

使用PreparedStatement类可以避免SQL注入，用法如下：

package temp;

import utils.JDBCUtils;

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

public class TestInsertPlus {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;


        try {
            connection = JDBCUtils.getConnection();

            String sql = "insert into student values(?,?,?,?)";     //使用？占位符来代替参数
            preparedStatement = connection.prepareStatement(sql);   //预编译sql，先写sql，然后不执行

            preparedStatement.setObject(1,13);
            preparedStatement.setObject(2,"艹");
            preparedStatement.setObject(3,"男");
            preparedStatement.setObject(4,5);

            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.release(connection,preparedStatement,null);
        }

    }
}

package temp;

import 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 connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);    //关闭自动提交，自动会开启事务

            String sql1 = "update account set money=money-300 where name='A';";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            int x = 1/0;    //报错测试

            String sql2 = "update account set money=money+300 where name='B';";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //业务完毕，提交事务
            connection.commit();
            System.out.println("成功");

        } catch (Exception e) {
            //如果失败则默认回滚，不需要手动写
            try {
                connection.rollback();      //如果失败则回滚
            } catch (SQLException t) {
                t.printStackTrace();
            }


        }finally {
            JDBCUtils.release(connection,preparedStatement,null);
        }
    }
}

别人封装好的连接池，可以拿来直接使用，效率更高。