JDBC入门

JDBC: Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标^[1]。JDBC是面向关系型数据库的。

图片.png

JDBC的API

• 创建Java工程后，需要将数据库驱动包导入

package com.alan.jdbc;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.sql.*;

public class JDBCDemo1 {

    @Test
    /**
     * JDBC入门程序
     */
    public void demo1() {

        try {
            //1、加载驱动
            DriverManager.registerDriver(new Driver());
            //2、获得连接 ，url分别是数据库类型、数据库IP、数据库端口号、数据库名
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC", "root", "root");
            //3、创建SQL语句对象，并执行SQL
            String sql = "select * from user;";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            //4、通过ResultSet对象获取查询到的数据
            while (resultSet.next()){
                int uid = resultSet.getInt("uid");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String name = resultSet.getString("name");

                //打印输出
                System.out.println(+uid+"   "+username+"   "+password+"   "+name);
            }
            //5、释放资源
            resultSet.close();
            statement.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

DriverManager驱动管理类的使用

• 主要有两个作用
  1、注册驱动
  实际中加载Driver,其中的静态代码块就会被执行，完成驱动加载。
  Class.forName("com.mysql.cj.jdbc.Driver");
  2、获得连接
• url中JDBC:数据库协议、mysql:数据库子协议、数据库IP、数据库端口号、数据库名
  jdbc:mysql://localhost:3306/jdbctest
• 连接本机的时候可以简写
  jdbc:mysql:///jdbctest

Connection对象的使用

• 创建执行SQL语句的对象
  1、Statement createStatement() 执行SQL语句，有SQL注入的漏洞存在
  2、PreparedStatement preparedStatement(String sql) 预编译SQL语句 解决SQL注入的漏洞
  3、CallableStatement prepareCall(String sql) 执行SQL中存储过程
• 进行事务的管理
  setAutoCommit(false) 事务不自动提交了，true 自动提交
  commit() 提交事务
  rollback() 事务回滚

Statement对象

• 执行sql
  1、 boolean execute(String sql) 执行sql 执行sql中的select语句返回true 否则false
  2、 ResultSet executeQuery(String sql) 执行sql中的select语句
  3、int executeUpdate(String sql) 执行sql中的insert/update/delete语句 返回影响的行数
• 执行批处理操作
  1、 addBatch(String sql) 添加批处理
  2、executeBatch() 执行批处理
  3、clearBatch) 清空批处理

ResultSet对象

返回结果集中的数据 next(); getObject(String name);

JDBC资源释放

Connection 是连接数据库的对象，每个数据库的连接数都有上限,使用Connection对象的原则，尽量晚创建，尽量早释放。

package com.alan.jdbc;

import com.mysql.cj.jdbc.Driver;
import org.junit.Test;

import java.sql.*;

public class JDBCDemo1 {

    @Test
    /**
     * JDBC入门程序
     */
    public void demo1() {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1、加载驱动
            //应该使用这种方式，加载Driver,其中的静态代码块就会被执行，完成驱动加载。
            Class.forName("com.mysql.cj.jdbc.Driver");
            //DriverManager.registerDriver(new Driver()); //这种方式会导致驱动注册两次
            //2、获得连接 ，url中JDBC:数据库协议、mysql:数据库子协议、数据库IP、数据库端口号、数据库名
            //连接本机的时候可以简写
            //Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest?useSSL=false&serverTimezone=UTC", "root", "root");
            connection = DriverManager.getConnection("jdbc:mysql:///jdbctest?useSSL=false&serverTimezone=UTC", "root", "root");
            //3、创建SQL语句对象，并执行SQL
            String sql = "select * from user;";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            //4、通过ResultSet对象获取查询到的数据
            while (resultSet.next()){
                int uid = resultSet.getInt("uid");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String name = resultSet.getString("name");

                //打印输出
                System.out.println(+uid+"   "+username+"   "+password+"   "+name);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            //5、释放资源
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                resultSet = null;
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                statement = null;
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace() ;
                }
                //设置为null,垃圾回收机制会更早的回收对象。
                connection = null;
            }
        }

    }
}

JDBC增删改查操作

package com.alan.jdbc;

import org.junit.Test;

import java.sql.*;

public class JDBCDemo2 {

    @Test
    /**
     * insert操作
     */
    public void demo1(){

        Connection connection = null;
        Statement statement = null;

        try {
            //注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获得连接
            connection = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "root");
            //获取执行SQL语句对象
            statement = connection.createStatement();
            //编写sql
            String sql = "insert into user values(null,'eee','123','张三')";
            //执行sql
            for (int i = 0; i < 100; i++) {
                statement.addBatch(sql);
            }
            int[] i =  statement.executeBatch();
            //int i = statement.executeUpdate(sql);
            if(i != null){
                System.out.println("执行成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                connection = null;
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                statement = null;
            }
        }

    }


    @Test
    /**
     * update操作
     */
    public void demo2(){
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql:///jdbctest","root","root");
            statement = connection.createStatement();
            String sql = "update user set name = '六六六' where name = '张三' ";
            int i = statement.executeUpdate(sql);
            if(i >0){
                System.out.println("执行成功");
            }else {
                System.out.println("执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                connection = null;
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                statement = null;
            }
        }
    }


    @Test
    /**
     * 删除操作
     */
    public  void  demo3(){

        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql:///jdbctest","root","root");
            statement = connection.createStatement();
            String sql = "delete from user where name = '六六六'";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功，共删除"+ i + "条");
            }else {
                System.out.println("删除失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                connection = null;
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                statement = null;
            }
        }

    }

    @Test
    public void demo4(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql:///jdbctest","root","root");
            statement = connection.createStatement();
            String sql = "select * from user limit 100";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getInt("uid")+"   "+resultSet.getString("username")+"   "+resultSet.getString("password")+ "   "+resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                connection = null;
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                statement = null;
            }
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //强制设置为null，垃圾回收尽早释放资源
                resultSet = null;
            }
        }
    }
}

JDBC工具类抽取

• JDBCUtil

package com.alan.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {

    //定义url、用户名、密码常量、Driver路径
    private static final String url ;
    private static final String dbName ;
    private static final String dbPassword ;
    private static final String driverPath ;

    //静态代码块中加载配置文件信息
    static {
        //加载文件属性并解析
        Properties properties = new Properties();
        //通常不采用FileInputStream因为发布到web项目有可能会有路径的改变
        //这里采用类的加载器方式
        InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");

        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        url =properties.getProperty("url");
        dbName = properties.getProperty("dbName");
        dbPassword = properties.getProperty("dbPassword");
        driverPath = properties.getProperty("driverPath");
    }

    /**
     * 注册驱动的方法
     * @throws ClassNotFoundException
     */
    public static void  loadDriver() throws ClassNotFoundException {
        Class.forName(driverPath);
    }

    /**
     * 获得连接的方法
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        loadDriver();
        Connection connection = DriverManager.getConnection(url, dbName, dbPassword);
        return connection;
    }

    /**
     * 资源释放
     * @param connection
     * @param statement
     */
    public static void release(Connection connection, Statement statement) {

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

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

    }

    /**
     * 资源释放
     * @param connection
     * @param statement
     */
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {

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

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

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

    }
}

• jdbc.properties

driverPath = com.mysql.cj.jdbc.Driver
url = jdbc:mysql:///jdbctest
dbName = root
dbPassword = root

JDBC的sql注入漏洞演示

package com.alan.SQLInject;

import com.alan.util.JDBCUtil;
import org.junit.Test;

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

/**
 * 演示JDBC SQL注入漏洞
 */
public class JDBCDemo4 {



    public static boolean login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try {
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
            resultSet = statement.executeQuery(sql);
            if(resultSet.next()){
                flag =  true;
            }else {
                flag =  false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtil.release(connection,statement,resultSet);
        }

        return  flag;
    }


    @Test
    /**
     * 测试漏洞的方法
     */
    public void demo1(){

        //用户名sql注入方式一 输入了SQL语句关键字 or
       // boolean flag = JDBCDemo4.login("bbb' or ' 1=1","22dnifs2");
        //用户名sql注入方式二 输入了SQL语句关键字 --
        boolean flag = JDBCDemo4.login("bbb' -- ","22dnifs2");
//        boolean flag = JDBCDemo4.login("bbb","222");
        if(flag){
            System.out.println("登陆成功");
        }else{
            System.out.println("登陆失败");
        }
    }
}

• 注入漏洞解决

  
  图片.png

package com.alan.SQLInject;

import com.alan.util.JDBCUtil;
import org.junit.Test;

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

/**
 * 演示JDBC SQL注入漏洞
 */
public class JDBCDemo4 {



    public static boolean login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try {
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
            resultSet = statement.executeQuery(sql);
            if(resultSet.next()){
                flag =  true;
            }else {
                flag =  false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtil.release(connection,statement,resultSet);
        }

        return  flag;
    }

    /**
     * 避免sql注入漏洞的登陆方法
     * @return
     */
    public  static  boolean login2(String username,String password){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try {
            //创建连接
            connection = JDBCUtil.getConnection();
            //写sql语句，变量处用问号代替
            String sql = "select * from user where username = ? and password = ?";
            //将sql语句传给PreparedStatement对象
            preparedStatement = connection.prepareStatement(sql);
            //通过PreparedStatement对象给sql语句中的变量赋值
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            //执行sql,结果返回给结果集对象
            resultSet = preparedStatement.executeQuery();
            //判断是否登陆成功
            if(resultSet.next()){
                flag = true;
            }else{
                flag = false;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,preparedStatement,resultSet);
        }

        return  flag;
    }

    @Test
    /**
     * 测试漏洞的方法
     */
    public void demo1(){

        //用户名sql注入方式一 输入了SQL语句关键字 or
       // boolean flag = JDBCDemo4.login("bbb' or ' 1=1","22dnifs2");
        //用户名sql注入方式二 输入了SQL语句关键字 --
        boolean flag = JDBCDemo4.login2("bbb","222");
//        boolean flag = JDBCDemo4.login("bbb","222");
        if(flag){
            System.out.println("登陆成功");
        }else{
            System.out.println("登陆失败");
        }
    }
}

PreparedStatement对象使用

package com.alan.jdbc;

import com.alan.util.JDBCUtil;
import org.junit.Test;

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

/**
 * PreparedStatement对象使用
 */
public class JDBCDemo5 {

    @Test
    /**
     * insert使用
     */
    public void demo1(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            //编写sql语句
            String sql = "insert into user values(null,?,?,?)";
            //传给PreparedStatement进行sql预编译
            preparedStatement = connection.prepareStatement(sql);
            //变量赋值
            preparedStatement.setString(1,"qqq");
            preparedStatement.setString(2,"vvv");
            preparedStatement.setString(3,"小花");
            //执行sql
            int i = preparedStatement.executeUpdate();
            if(i > 0){
                System.out.println("insert执行成功");
            }else {
                System.out.println("insert执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,preparedStatement);
        }

    }


    @Test
    /**
     * update使用
     */
    public void  demo2(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "update user set username = ? ,password = ?, name = ? where uid = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"zxc");
            preparedStatement.setString(2,"nmn");
            preparedStatement.setString(3,"哈哈哈");
            preparedStatement.setInt(4,190);
            int i = preparedStatement.executeUpdate();
            if(i >0){
                System.out.println("update执行成功，共影响"+i+"行");
            }else{
                System.out.println("update执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,preparedStatement);
        }
    }

    @Test
    /**
     * delete使用
     */
    public void demo3(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "delete from user where uid = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,188);
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("delete执行成功");
            }else {
                System.out.println("delete执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,preparedStatement);
        }
    }

    @Test
    /**
     * select使用
     */
    public void demo4(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "select * from user";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getInt("uid")+"    "+resultSet.getString("username")+"    "+resultSet.getString("password")+"     "+resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,preparedStatement,resultSet);
        }
    }
}

数据库连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

图片.png
图片.png

C3P0连接池使用

• 导入jar包
• java文件

package com.alan.datasource;

import com.alan.util.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

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

/**
 * C3P0连接池测试类
 */
public class DataSourceDemo1 {

    @Test
    /**
     * 手动设置连接池
     */
    public void demo1(){
        //创建连接池
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        //获得连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //设置连接池参数：驱动、url、用户名、密码、最大连接数、初始化连接数
            comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            comboPooledDataSource.setJdbcUrl("jdbc:mysql:///jdbctest");
            comboPooledDataSource.setUser("root");
            comboPooledDataSource.setPassword("root");
            comboPooledDataSource.setMaxPoolSize(30);
            comboPooledDataSource.setInitialPoolSize(4);
            //建立连接
            connection = comboPooledDataSource.getConnection();
            String sql = "select * from user where uid = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,190);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getInt("uid")+"    "+resultSet.getString("username")+"    "+resultSet.getString("password")+"   "+resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtil.release(connection,preparedStatement,resultSet);
        }

    }

    @Test
    /**
     * 使用c3p0-config.xml配置
     */
    public void demo2(){
        //创建连接池
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        //获得连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            //xml配置后不用任何操作
            //建立连接
            connection = comboPooledDataSource.getConnection();
            String sql = "select * from user where uid = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,190);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getInt("uid")+"    "+resultSet.getString("username")+"    "+resultSet.getString("password")+"   "+resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JDBCUtil.release(connection,preparedStatement,resultSet);
        }

    }

}

• c3p0-config.xml 配置文件

<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>


    <default-config>
        <property name = "driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name = "jdbcUrl">jdbc:mysql:///jdbctest</property>
        <property name = "user">root</property>
        <property name = "password">root</property>
        <property name = "maxPoolSize">20</property>
        <property name = "initialPoolSize">5</property>
    </default-config>


</c3p0-config>