JDBC(概述和对象)

---

JDBC

1.概述

程序会通过数据库驱动(如MySQL驱动、Oracle驱动)和数据库进行交互。而SUN公司为了简化开发人员对数据库的统一操作，为关系数据库定义了一套标准的访问接口：JDBC（Java Database Connectivity）。

2. 第一个JDBC程序

IDEA导入JDBC驱动的jar包

1. 导入jar包
如下图，新建lib文件夹，复制jar包到文件夹下，一定要点Add as library


2. 测试代码

public class JdbcDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");// 固定写法
        
        //2. 用户信息和url
        //useUnicode=true-支持中文编码、characterEncoding=utf8-设定中文字符集、useSSL=true-设置为安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "密码";
        
        //3. 连接数据库 connection是数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        
        //4. 执行sql的对象 statement是执行sql的对象
        Statement statement = connection.createStatement();
        
        //5. 用statement对象执行sql语句
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集，链表形式
        while(resultSet.next()) {
            System.out.println("id=" + resultSet.getObject("id")); // 不知道数据类型可直接用getObject
            System.out.println("name=" + resultSet.getString("name"));
            System.out.println("password=" + resultSet.getObject("password"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("=============================");
        }
        
        //6. 释放连接 注意和创建对象的顺序相反
        resultSet.close();
        statement.close();
        connection.close();// 十分耗资源，用完要关掉
    }
}

3.JDBC对象

3.1 对象概述

• URL

  String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
  // jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2...
  // mysql端口号3306
  // oracle--1521 url = "jdbc:oracle:thin:@localhost:1521:sid"
  

• Connection 数据库对象

  Connection connection = DriverManager.getConnection(url,username,password);
  connection.commit(); // 提交
  connection.rollback(); // 回滚
  connection.setAutoCommit();
  ...
  

• Statement 执行sql的对象

  String sql = " sql语句";
  Statement statement = connection.createStatement();
  statement.excute(sql); //执行任何一条sql
  ResultSet resultSet = statement.excuteQuery(sql); // 执行一条查询的sql语句 返回结果集
  statement.update(); //执行一条更新、插入、删除的sql语句 返回受影响的行数
  

• ResultSet 存储查询的结果集，链表结构 https://www.cnblogs.com/lyuweigh/p/eb16d79e30a2e4a9f606770ff591fc7b.html

  // 参数为字段名
  resultSet.getObject();// 不知道列类型时使用
  resultSet.getString();
  resultSet.getInt();
  resultSet.getFloat();
  resultSet.getDate();
  
  // 遍历
  while(resultSet.next()) {...}
  

3.2 Statement对象总结

• CRUD操作—create 增

  Statement st = conn.createStatement();
  String sql = "insert into `表名`([`字段1`,`字段2`,`字段3`]) values('值1','值2','值3'),(下一行值)";
  int num = st.executeUpdate(sql);
  if(num>0) {  System.out.println("插入成功");}
  

• CRUD操作—delete 删

  Statement st = conn.createStatement();
  String sql = "delete from `表名` [WHERE语句]";
  int num = st.executeUpdate(sql);
  if(num>0) {  System.out.println("删除成功");}
  

• CRUD操作—update 改

  Statement st = conn.createStatement();
  String sql = "update `表名` set `字段`=val,`字段1`=value... [where _字段=_val]";
  int num = st.executeUpdate(sql);
  if(num>0) {  System.out.println("更改成功");}
  

• CRUD操作—read 查

  Statement st = conn.createStatement();
  String sql = "select ... from users where ...";
  ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集，链表形式
  while(resultSet.next()) { }
  

3.3 提取工具类

可以看到，上述流程只有执行sql语句的代码会改变，因此可以把其余代码提取为一个工具类，减少代码量。

文件结构

工具类

package com.kk.jdbc2.util;

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

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    // 读取配置文件
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//反射
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 1.驱动只用加载一次,因此放在static块里
            Class.forName("driver");
            } catch (IOException | ClassNotFoundException e) {
                e.printStackTrace();
        }
    }
    // 连接数据库
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    // 释放连接
    public static void release(ResultSet rs, Statement st, Connection conn) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试代码

public class Test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "INSERT INTO `users`(`id`,`name`,`password`) VALUES(6,'kk','123')";
            int num = st.executeUpdate(sql);
            if(num > 0) System.out.println("插入成功");
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
}

3.3 PreparedStatement对象

可以防止SQL注入，且效率更高

public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement preSt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO `users`(`id`,`name`,`password`) VALUES(?,?,?)"; // ？为占位符
            preSt = conn.prepareStatement(sql); // 预编译SQL，先写sql但先不执行
            //手动给参数赋值
            preSt.setInt(1,7);
            preSt.setString(2,"kk");
            preSt.setString(3,"1234");
            //时间参数
            //preSt.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行sql
            int num = preSt.executeUpdate();
            if(num > 0) System.out.println("插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(rs,preSt,conn);
        }
    }

3.4 SQL注入问题

sql注入基础原理

public class SqlInject {
    public static void main(String[] args) {
        //login("kk","123");
        login("''or'1=1","''or'1=1"); // 注入

    }

    public static void login(String username, String password) {
        Connection conn = null;
        //Statement st = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

//            st = conn.createStatement();
//            String sql = "select * from users where `name`='"+username+"' and `password`='"+password+"'";
//            rs = st.executeQuery(sql);

//          PreparedStatement防止注入的本质：把传递进来的参数当作字符,且参数存在转义字符(如'')的话直接省略。
            String sql = "select * from users where `name`=? and `password`=?";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs = st.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("=====================");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(rs,st,conn);
        }
    }
    }