JDBC总结

1.什么是JDBC

JDBC的全称是Java数据库连接(Java Database connect)，它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作。 不同数据库厂商提供的数据库驱动不同。因此，应用程序使用JDBC访问特定的数据库时，需要与特定的数据库驱动进行连接。
JDBC封装了与各种数据库服务器通信的细节，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。

环境准备

1.下载驱动数据库
MySQL数据库驱动下载地址：https://dev.mysql.com/downloads/connector/j/

选择：Platform Independent
TAR包是Linux操作系统下的，ZIP包是Windows操作系统下的，这里我们选择ZIP包。

2.导入驱动 Jar 包（这里我创建的是一个web项目）
解压下载好的MySQL数据库驱动，得到：mysql-connector-java-8.0.23.jar驱动文件

在WEB-INF下创建一个lib目录，并将mysql-connector-java-8.0.23.jar驱动文件复制到lib目录下

选中mysql-connector-java-8.0.23.jar驱动文件，点击鼠标右键找到Add as library，点击确定

看到它可以展开说明成功。

JDBC详细步骤

1. 加载并注册驱动
2. 获取数据库连接
3. 获取执行 SQL 语句的对象(Statement，PreparedStatement)
4. 并执行sql语句
5. 关闭资源

1.加载并注册驱动

调用Class类的静态方法forName()，参数为JDBC驱动的全类名。

public class JdbcTest {
    
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

2.获取数据库连接

方式一：使用DriverManager.getConnection(String url,String user, String password)获取数据库连接

public class JdbcTest {

    public static void main(String[] args) {
    //加载并注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
       //获取数据库连接
        String url="jdbc:mysql://localhost:3306/mydate?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true";
        String name="root";
        String password="968426";
        Connection conn =null;
        try {
             conn = DriverManager.getConnection(url, name, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
          System.out.println(conn);
    }
}

username：数据库登录名
password：数据库密码

参数 （有多个参数用&隔开）
useUnicode=true：true表示使用unicode编码
characterEncoding=UTF-8：使用UTF-8字符集
zeroDateTimeBehavior=convertToNull：java在连接mysql数据库时，在操作值为0的timestamp类型时不能正确的处理。zeroDateTimeBehavior=convertToNull属性的方式予以规避。
useSSL=true：JDBC版本与MySQL版本不兼容，MySQL的版本更高一些，在连接语句后加上“useSSL=‘true’” ，就可以连接到数据库了。
serverTimezone=GMT%2B8 设置时区为北京时间东八区

方式二： DriverManager.getConnection(String url,Properties info)其中Properties info通常至少应该包括 “user” 和 “password” 属性

public class JdbcTest {

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        String url="jdbc:mysql://localhost:3306/mydate?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true";
        //把用户名和密码放在 properties 对象中
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","968426");
        //获取连接
        Connection conn =null;
        try {
            conn = DriverManager.getConnection(url, properties);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(conn);
    }
}

补充：通常我们会把一些常用属性写入配置文件中，然后再从配置文件中获取属性。因为修改配置文件永远比修改代码来的简单和快捷
配置文件jdbc.properties

username=root
password=968426
url=jdbc:mysql://localhost:3306/mydate?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true
driverClassName=com.mysql.cj.jdbc.Driver

public class JdbcTest {

    public static void main(String[] args) {
        Connection conn =null;
        try {
            Properties pro = new Properties();
            // 使用ClassLoader类加载器,加载配置文件
            URL res = JdbcUtils.class.getClassLoader().getResource("jdbc.properties");
            // 获取路径
            String path = res.getPath();
            // 读取文件
            pro.load(new FileReader(path));
            // 给静态变量赋值
            String url = pro.getProperty("url");
            String username = pro.getProperty("username");
            String password = pro.getProperty("password");
            String driverClassName = pro.getProperty("driverClassName");
            Class.forName(driverClassName);
            conn = DriverManager.getConnection(url, username, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(conn);
    }
}

3.获取执行 SQL 语句的对象（Statement、PreparedStatement）
4.并执行sql语句

关闭资源

需要关闭的资源有

1. ResultSet对象
2. Statement、PreparedStatement对象
3. connection对象

为确保关闭资源能运行，关闭资源代码一定要放在finally语句中。

实操

创建数据库

CREATE TABLE `book` (
  `bookID` int NOT NULL AUTO_INCREMENT,
  `bookName` varchar(100) NOT NULL,
  `bookCounts` int NOT NULL,
  `detail` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`bookID`),
  KEY `bookID` (`bookID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中的数据

实体类

public class Book {
    private int bookID;
    private String bookName;
    private int bookCounts;
    private String detail;
    有参，无参构造器
    get set toString方法
    }

Statement实操

Statement对象常用方法：

方法	说明
executeQuery(String sql)	执行查询sql，返回ResultSet对象。
executeUpdate(String sql)	执行增删改sql，返回受影响的行数
execute(String sql)	执行任意sql语句，返回布尔值

ResultSet：ResultSet对象以逻辑表格的形式封装了执行数据库操作的结果集。
获取数据（XXX表示Object和基本数据类型）

方法	说明
next()	游标向下移动 1 行,返回 boolean 类型，如果还有下一条记录，返回 true，否则返回 false
getXXX(int index)	通过索引
getXXX(string columnName)	通过列名，若sql语句中使用了别名，则使用别名

查询book表中的数据

public class JdbcTest {

    public static void main(String[] args) {
        Connection conn =null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            //加载并注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取数据库连接
            String url="jdbc:mysql://localhost:3306/mydate?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true";
            String name="root";
            String password="968426";
            conn = DriverManager.getConnection(url, name, password);
            //获取Statement对象
            statement = conn.createStatement();
            String sql="select * from book";
            //执行sql，查询book表
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                Book book = new Book();
                book.setBookID(resultSet.getInt("bookID"));
                book.setBookCounts(resultSet.getInt("bookCounts"));
                book.setBookName(resultSet.getString("bookName"));
                book.setDetail(resultSet.getString("detail"));
                System.out.println(book);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

注意Statement有sql注入问题

String sql = "select * from user where name='" + name + "' and password='" + password+ "'";

分析

用户输入的name=userName，password=a’ or ‘1’='1。拼接后的sql是 select * from user where name=‘userName ’ and password=’ a’ or ‘1’=‘1’;
我们可以发现拼接后的sql多了一个where条件or ‘1’=‘1’ 而它为真。所以就算我们用户表里没有这个用户也一样可以登录成功

所以 Statement 对象用于执行不带参数的简单 SQL 语句

PreparedStatement实操

PreparedStatement对象用于执行带或不带 IN 参数的预编译 SQL 语句

1. 因为有预先编译的功能，提高 SQL 的执行效率。
2. 可以有效的防止 SQL 注入的问题，安全性更高。

分析：‘如何防止sql注入

String sql = "select * from user where name=? and password=? ";

同样用户输入的name=userName，password=a’ or ‘1’='1。拼接后的sql是 select * from user where name='userName ’ and password='a\'or\‘1\’=\‘1’;
我们可以看到拼接后的SQL文是把整个参数用引号包起来，并把参数中的引号作为转义字符， 从而避免了参数也作为条件的一部分。
源码分析如何防止sql注入传送门：PreparedStatement是如何防止SQL注入的？

PreparedStatement 是 Statement 接口的子接口，继承于父接口中所有的方法。

PreparedStatement对象的SQL语句中的参数用问号(?)来表示，调用PreparedStatement对象的setXXX(int parameterIndex, XX xx)方法来设置这些参数. 第一个参数SQL语句中的参数的索引(从 1 开始)，第二个是SQL语句中的参数的值。

往book表中添加数据

public class JdbcTest {

    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet =null;
        try {
            //加载并注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取数据库连接
            String url="jdbc:mysql://localhost:3306/mydate?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true";
            String name="root";
            String password="968426";
            conn = DriverManager.getConnection(url, name, password);
            String sql="insert into book(bookName,bookCounts,detail) values(?,?,?)";
           preparedStatement = conn.prepareStatement(sql);
            Book book = new Book(0,"mybatis",7,"框架");
            preparedStatement.setString(1,book.getBookName());
            preparedStatement.setInt(2,book.getBookCounts());
            preparedStatement.setString(3,book.getDetail());
            preparedStatement.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

jdbc工具类

获取连接和关闭资源每一个增删改查操作都要执行。建议把这几个功能做成一个工具类，可以在不同的地方重用。

public class JdbcUtils {
    private static String url;
    private static String username;
    private static String password;
    private static String driverClassName;

    static {
        try {
            Properties pro = new Properties();
            // 使用ClassLoader类加载器,加载配置文件
            URL res = JdbcUtils.class.getClassLoader().getResource("jdbc.properties");
            // 获取字符串路径
            String path = res.getPath();
            // 读取文件
            pro.load(new FileReader(path));
            // 给静态变量赋值
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            driverClassName = pro.getProperty("driverClassName");
            // 注册驱动
            Class.forName(driverClassName);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    //关闭资源
    public static void close(ResultSet rs, PreparedStatement preparedStatement, Connection conn) {

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

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

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

BookDaoI接口

public interface BookDao {
    //增
    int addBook(Book book);
    //删
    int delBook(int id);
    //改
     int updateBook(Book book);
    //查
   List<Book> queryBook(String bookName);
}

BookDaoImpl 类

public class BookDaoImpl implements BookDao {

    @Override
    public int addBook(Book book) {
        Connection con =null;
        PreparedStatement preparedStatement=null;
        try {
            con = JdbcUtils.getConnection();
            String sql="insert into book(bookName,bookCounts,detail) values(?,?,?)";
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1,book.getBookName());
            preparedStatement.setInt(2,book.getBookCounts());
            preparedStatement.setString(3,book.getDetail());
            preparedStatement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null,preparedStatement,con);
        }

    }

    @Override
    public int delBook(int id) {
        return 0;
    }

    @Override
    public int updateBook(Book book) {
        return 0;
    }

    @Override
    public List<Book> queryBook(String bookName) {
        return 0;
    }
}

测试

public class JdbcTest {

    public static void main(String[] args) {
        BookDao bookDao = new BookDaoImpl();
        bookDao.addBook(new Book(0,"sql索引优化",10,"无"));
    }
}

参考：学JDBC，这一篇就够了