实现Java的JDBC编程

写在前面

• 当下的应用系统绝大多数离不开数据库，Java 程序访问数据库的基本方式是通过JDBC来实现。
• JDBC 是一种用于 SQL 语句的java API，它由 java.sql.,javaX.sql. 包中的类和接口组成。

1.JDBC 工作原理

• Java 语言访问数据库操作完全面向抽象接口编程。
• 开发数据库应用不用限定在特定数据库厂商的API。
• 程序的可移植性大大增强。
  

2. JDBC 开发步骤

2.1 配置数据库驱动

• 构建成功的 Maven 项目包含 src 文件夹、target 文件夹、pom.xml 文件三部分组成，要想在项目中使用 JDBC 编程，必须在 pom.xml 文件中配置以下依赖，不然会出现无法连接数据库的问题。
• 以MySQL为例：

 <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>

https://search.maven.org/
可供大家查找想要的依赖jar 包的属性。

2.2 加载数据库驱动

• 加载数据库驱动，作为JDBC 开发步骤的第一步，主要是通过反射机制来实现的，通过类名反射得到，会出现 ClassNotFoundException 异常处理。

//1.加载数据库驱动（MySQL）备注：5.1之后可以不明确加载驱动
Class.forName( "com.mysql.jdbc.Driver" );

2.3 建立数据库连接

• 连接数据库采用的是 DriverManager 的静态方法 getConnection（url）来建立连接，返回Connection 对象，另一种是通过DataSource（数据源）对象获取。
• url 必须按照相应的规范书写，jdbc:database://host:port/databaseName?p1=v1&p2=v2 ,对于MySQL 数据库而言，url 可表示为 jdbc:mysql://localhost:3306/databaseName?user=root&password=123456 。

//2.连接数据库
            //jdbc:database://host:port/databaseName?p1=v1&p2=v2
            //jdbc:mysql://localhost:3306/student_management
            Connection connection =
                    DriverManager.getConnection( "jdbc:mysql://localhost:3306/studentdatabase?user=root&password=123456&useSSL=false" );

2.4 创建操作命令

• <1> Statement 用于执行不带参数的简单SQL语句

//3.创建 Statement 命令
 Statement statement = connection.createStatement();

这种情况下创建的操作命令执行带参数的SQL语句会发生SQL注入。

• <2> PreparedStatement 用于执行带或者不带参数的SQL语句
  此种情况下创建的操作命令有如下优点：
  性能比Statement高、SQL预编译、阻止常见的SQL注入攻击。
  需要注意的是PreparedStatement 的占位符：？ 下标从1开始，并且不能使用多值。

//通过调用方法传入参数执行SQL语句
 public static void queryMemoGroupByName(String groupName) {
 	String sql = "select * from 表名 where 字段 in (?)";
    PreparedStatement preparedStatement = connection.prepareStatement( sql );
    preparedStatement.setString( 1, groupName );
 }

2.5 执行SQL语句

• Statement 和 PreparedStatement
  对于 R 操作，statement 调用 executeQuery() 方法返回ResultSet 结果集；对于 CUD 操作statement 调用 executeUpdate() 方法返回 int 型，成功返回1，失败返回0。

//4.准备SQl语句执行SQL语句
//R 查询
  ResultSet resultSet = statement.executeQuery( "select *  from 表名" );
  ResultSet resultSet = preparedStatement.executeQuery();
//CUD 更新 删除 增加操作
  int insertValue =statement.executeUpdate("insert into 表名 values (  )" );
  int insertValue =preparedStatement.executeUpdate("insert into 表名 values (  )" );

2.6 处理返回结果集

• ResultSet对象它被称为结果集，它代表符合SQL语句条件的所有行，并且它通过一套getXXX方法提供了对这些行中数据的访问。
• ResultSet里的数据一行一行排列，每行有多个字段，并且有一个记录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前的数据行。我们如果想要取得某一条记录，就要使用ResultSet的next()方法 ,如果我们想要得到ResultSet里的所有记录，就应该使用while循环。

  while (resultSet.next()) {//如果返回true表示有下一行记录，则否无记录
	  int id = resultSet.getInt("id");
      String name = resultSet.getString("name");
      LocalDateTime createdTime = resultSet.getTimestamp("created_time").toLocalDateTime();
      LocalDateTime modifyTime = resultSet.getTimestamp("modify_time").toLocalDateTime();
      System.out.println(String.format("编号：%d, 名称：%s, 创建时间：%s, 修改时间：%s",
                                id, name,createdTime.toString(), modifyTime.toString()
                        ));
            }

2.7 关闭操作

• 访问数据库完毕后必须关闭结果集、关闭命令、关闭连接三步操作，当然也可以使用try 自动关闭机制，因为 ResultSet Statement Connection 都已经实现了AutoCloseable接口。

//关闭结果集 关闭命令 关闭连接
  resultSet.close();
  statement.close();
  connection.close();
//AutoCloseable接口 自动关闭
 try(
	 Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/databaseName?user=root&password=123456&useSSL=false" );
             //3.创建命令
             Statement statement = connection.createStatement();
             //4.准备SQl语句
             ResultSet resultSet = statement.executeQuery( "select *  from 表名" )
        ) 

3. 典型代码

• <1>Statement 引起SQL注入问题

import java.sql.*;

/**
 * @Auther: SolarL
 * @Date: 2018/11/24
 * @Description: com.sunlong.jdbc
 * @version: 1.0
 */
public class TestJDBC3 {

    public static void queryMemoGroupByName(String groupName) {
        try {
            Class.forName( "com.mysql.jdbc.Driver" );
            Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/studentdatabase?user=root&password=123456" );
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery( "select * from student_management where 学号 = '" + groupName + "'" );
               while (resultSet.next()) {
                String num = resultSet.getString( "学号" );
                String name = resultSet.getString( "姓名" );
                String sex = resultSet.getString( "性别" );
                String school = resultSet.getString( "学院" );
                String class1 = resultSet.getString( "班级" );
                String class2 = resultSet.getString( "籍贯" );

                System.out.println( String.format(
                        "学号：%s,姓名：%s，性别：%s，学院：%s，班级：%s，籍贯：%s",
                        num, name, sex, school, class1, class2
                ) );
            }

            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        // queryMemoGroupByName( "201612345678" );
        //SQL注入
        //select * from student_management where 学号 = '+ groupName+';
        //select * from student_management where 学号= 'PHP组' or 1=1 or 1='';
        queryMemoGroupByName( "学生组' or 1=1 or 1='" );
    }
}

对于以上SQL注入问题的解决办法，只需要将创建操作命令的时候使用 PreparedStatement 创建即可。

• <2> JDBC 开发模板

import java.sql.*;

/**
 * @Auther: SolarL
 * @Date: 2018/11/25
 * @Description: com.sunlong.jdbc
 * @version: 1.0
 */
public abstract class JdbcTemplate {
    private String url;
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    Integer effect = -1;

    //jdbc:mysql://localhost:3306/database?user=root&password=018162
    public JdbcTemplate(String host, Integer port, String databaseName, String user, String password) {
        this.url = String.format( "jdbc:mysql://%s:%d/%s?user=%s&password=%s", host, port, databaseName, user, password );

    }

    public final void call() {
        //1.加载驱动
        loadDriver();
        //2.创建连接
        createConnect();
        //3.创建命令
        createStatement();
        //4.准备SQL
        createSql();
        //5.执行SQL
        execute();
        //6.处理结果
        //第一类：CUD int 第二： R result
        handlerResult();
        //7.关闭结果集 关闭命令 关闭连接
        closeAll();
    }

    private void closeAll() {
        if (this.resultSet != null) {
            try {
                this.resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

        if (this.connection != null) {
            try {
                this.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        this.effect = -1;

    }

    private void handlerResult() {
        if (this.executeType()) {
            try {
                this.handlerR( resultSet );
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } else {
            this.handleCUD( effect );
        }
    }

    protected abstract void handleCUD(Integer effect);

    protected abstract void handlerR(ResultSet resultSet) throws SQLException;

    private void execute() {
        String sql = this.createSql();
        if (sql != null) {
            if (this.executeType()) {
                try {
                    resultSet = statement.executeQuery( sql );
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                try {
                    effect = statement.executeUpdate( sql );
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    //如果返回true 查询， 如果返回false 插入，删除，更新
    public abstract boolean executeType();
    //用户来覆写
    public abstract String createSql();

    private void createStatement() {
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void createConnect() {
        try {
            connection = DriverManager.getConnection( this.url );
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void loadDriver() {
        try {
            Class.forName( "com.mysql.jdbc.Driver" );
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}



import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;

/**
 * @Auther: SolarL
 * @Date: 2018/11/26
 * @Description: com.sunlong.jdbc
 * @version: 1.0
 */
public class TestJdbcTemplate {
    public static void main(String[] args) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate( "localhost", 3306, "memo", "root", "123456" ) {
            @Override
            protected void handleCUD(Integer effect) {
                //do something
            }

            @Override
            protected void handlerR(ResultSet resultSet) throws SQLException {
                if (resultSet != null) {
                    while (resultSet.next()) {
                        int id = resultSet.getInt( "id" );
                        String name = resultSet.getString( "name" );
                        LocalDateTime createdTime = resultSet.getTimestamp( "created_time" ).toLocalDateTime();
                        LocalDateTime modifyTime = resultSet.getTimestamp( "modify_time" ).toLocalDateTime();
                        System.out.println(
                                String.format(
                                        "编号：%d, 名称：%s, 创建时间：%s, 修改时间：%s",
                                        id, name,
                                        createdTime.toString(),
                                        modifyTime.toString()
                                ) );
                    }
                }
            }

            @Override
            public boolean executeType() {
                return true;
            }

            @Override
            public String createSql() {
                return "select id,name,created_time,modify_time from memo_group ";
            }
        };

        jdbcTemplate.call();
    }
}

• <3> JDBC 事务管理
• 数据库中，如不进行手动设置，事务默认自动提交，当我们在进行更新操作正常的情况下，进行错误插入，如果事务自动提交，则会发生事务的不一致性，当我们手动设置事务提交，待操作完全完成后提交事务，出现错误回滚的原始操作，就可确保事务的一致性。

import java.sql.*;

/**
 * @Auther: SolarL
 * @Date: 2018/11/25
 * @Description: com.sunlong.jdbc
 * @version: 1.0
 */
public class TransactionJdbc {
    public static void main(String[] args) {
        //演示事务
        //1.更新操作 - 正常
        //2.插入操作 - 错误
        transaction();
    }

    public static void transaction() {
        Connection connection = null;
        try {
            //1、加载数据库驱动
            Class.forName( "com.mysql.jdbc.Driver" );
            //2.建立连接，连接数据库
            //关于数据库连接的URL格式JDBC规范里面也有定义
            // jdbc:database://host:port/databaseName?p1=v1&p2=v2
            //jdbc:mysql://localhost:3306/memo?user=root&password=123456
            connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/studentdatabase?user=root&password=018162" );
            connection.setAutoCommit( false );
            //3.创建命令
            String updateSQL = " update student_management set 班级 = ? where 学号 = ?";
            PreparedStatement preparedStatement = connection.prepareStatement( updateSQL );
            preparedStatement.setString( 1, "信息163" );
            preparedStatement.setString( 2, "201611010321" );

            //4.准备SQL并执行
            int effectUpdate = preparedStatement.executeUpdate();
            System.out.println( "更新操作" + (effectUpdate == 1) );

            String insertSQL = "insert into student_management(id,name) values (?,?)";
            preparedStatement = connection.prepareStatement( insertSQL );
            int effectInsert = preparedStatement.executeUpdate();
            System.out.println( "插入操作:" + (effectInsert == 1) );
            
            if (effectUpdate == 1 && effectInsert == 1) {
                connection.commit();
            } else {
                connection.rollback();
            }

            //7.关闭命令
            preparedStatement.close();
            //8.关闭连接
            connection.close();

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }
}

结束语

小哥哥，小姐姐们，长得这么好看的你，觉得文章还不错就关注我吧，顺便给我点个赞鼓励一下我吧，哈哈哈