JDBC详解

JDBC详解

概念

• JDBC（Java Database connectivity）数据库连接。JDBC 就是由 java提供的一套访问数据库的统一api. 使用这套api, 我们在切换数据库时十分方便. 不会较大改变代码.学习成本也降低了.

>

• Java程序对数据库进行的操作是通过每种数据库的驱动程序实现的（如上图所示）；但是由于数据库厂商的不同，每种数据库的驱动程序编写方式并不一样，所以程序员通过Java程序操作不同数据库的时候，需要学习每种数据库的驱动程序开发规范，这样大大增加了程序的开发负担，因此SUN公司为了简化统一数据库的操作，定义了一个接口（规范），让每个数据库厂商都是实现这个接口，这个接口就是JDBC。这样Java程序员进行不同关系型数据库的链接或切换操作的时候就不用学习对应数据库驱动的定义的开发规范，只需要学习JDBC定义的这一套规范就行了。

• 我们可以类别生活中Android手机的USB接口和数据线，以前每个手机厂商都自己设计了一种USB接口及数据线，你使用不同手机时只能使用该手机对应的数据线，假如手机的数据线坏了你只能去购买对应厂商的数据线，或者你买了多部安卓手机就需要多条数据线，这样很麻烦，不人性化，所以后来定义了一套规范每种Android手机厂商的USB接口都要根据这个接口规范来制造自己的USB，所以现在我们只需要一条数据线就可以对不同的安卓手机进行充电等操作，方便了用户。

JDBC程序的开发流程

1. 导包 导入厂商提供的数据库驱动.例如：mysql-connector-java-5.0.8-bin.jar（mysql数据库的驱动包）
2. 注册驱动
3. 连接数据库
4. 操作数据库(执行sql)

5. 关闭资源

   /** * 向数据库中查询数据 * 1. next方法,向下移动并判断是否有内容 * 2. getXXX方法,根据列索引或列名获得列的内容 */@Testpublic void request(){    Connection con = null;    Statement st = null;    ResultSet rs = null;    //1、导入驱动库    //2、注册驱动器    try {        Class.forName("com.mysql.jdbc.Driver");    } catch (ClassNotFoundException e) {        System.out.println("没有找到相应的类");        e.printStackTrace();    }    //3、连接数据库    try {        con = DriverManager.getConnection("jdbc:mysql://localhost:3305/dbdemo","root","1211124");        //4、操作数据库        st = con.createStatement();        //sql语句        String sql = "select * FROM userinfo";        //执行查询语句 注意是executeQuery        st.executeQuery(sql);        //获取查询结果        rs = st.getResultSet();        //或者直接写成这样        //rs = st.executeQuery(sql);        //打印查询结果        while (rs.next()) {            //int userid = rs.getInt(1);//获得第一列的值            int userid = rs.getInt("userid");//获得userid列的值            String username = rs.getString("username");            int money = rs.getInt("money");            System.out.println(userid+"   "+username+"   "+money);        }        /*//倒着遍历        // 1> 光标移动到最后一行之后        rs.afterLast();        // 2> 遍历=>        while (rs.previous()) {// 向上移动光标,并判断是否有数据            int userid = rs.getInt("userid");            String username = rs.getString("username");            int money = rs.getInt("money");            System.out.println(userid+"   "+username+"   "+money);        }*/    } catch (SQLException e) {        e.printStackTrace();    }finally{        //5、关闭资源        if(st!=null){            try {                st.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if(con!=null){            try {                con.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

   附（链接的是dbdemo数据库）上面用到的表userinfo结构如下：

   CREATE TABLE `userinfo` (  `userid` int(32) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  `username` varchar(16) DEFAULT NULL COMMENT '用户姓名',  `password` varchar(16) DEFAULT NULL COMMENT '用户密码',  `mail` varchar(32) DEFAULT NULL COMMENT '用户邮箱',  `money` int(32) DEFAULT NULL COMMENT '用户存款',  PRIMARY KEY (`userid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

开发流程细节详解

导包

• 因为我这个样例里面用的是mysql数据，所以我需要导入mysql数据库的jar包（mysql-connector-java-5.0.8-bin.jar），不同数据库对应的jar包不一样。

• 导入步骤

  在项目下面新建Folder命名为lib，把你下载的驱动包放到粘贴到里面。右键添加导依赖库；如下图所示。

  

注册驱动

• 方法一

  DriverManager.registerDriver(new com.mysql.jdbc.Driver());

  不推荐使用这种方法，因为这样相当于创建了两个驱动类，为什么这么说，我们来看一下com.mysql.jdbc.Driver()的源码。注意你查看源码的时候如果你没有添加源码文件，是看不到的。
  

  
  因为这个类中一个静态代码块，里面new Driver()当类加载的时候创建了驱动类对象，而当我们通过DriverManager.registerDriver(new com.mysql.jdbc.Driver());对象时，又重新创建了一个这样相当与我们创建了两个驱动类，耗费内存资源，所以不推荐使用。

• 方法二

  Class.forName("com.mysql.jdbc.Driver");

  我们通过反射的方式类加载类，从而注册驱动对象。因为驱动类的包名是以字符串的形式填写,那么当我们把该名称放到配置文件中,每次从配置文件中读取.那么切换驱动类就非常方便. 也就意味着切换数据库方便.

链接数据库（获得Connection）

DriverManager.getConnection("jdbc:mysql://127.0.0.1:3305/dbdemo", "root", "root");

• 代码解释

  • url格式

    //jdbc:mysql://127.0.0.1:3305/dbdemo?useUnicode=true&characterEncoding=utf8url完整 格式:  大协议:子协议://IP地址:端口号/库名?参数键=参数值//jdbc:mysql://127.0.0.1:3305/dbdemourl简单 格式:  大协议:子协议://IP地址:端口号/库名

  • 用户名

    root

    填写你自己数据库的用户名（我这里的数据库用户名为root）。

  • 密码

    root

    同样填写你自己数据库的密码（我这里的数据库密码也为root）。

• 编码参数解释

  例如：mysql数据库用的是gbk编码，而项目数据库用的是utf-8编码。添加useUnicode=true&characterEncoding=UTF-8

  添加的作用：指定字符的编码、解码格式

  • 存数据时

    数据库在存放项目数据的时候会先用UTF-8格式将数据解码成字节码，然后再将解码后的字节码重新使用GBK编码存放到数据库中。

  • 取数据时

    在从数据库中取数据的时候，数据库会先将数据库中的数据按GBK格式解码成字节码，然后再将解码后的字节码重新按UTF-8格式编码数据，最后再将数据返回给客户端。

Connection对象的细节问题

• 功能:
  
  • 1.代表数据库的链接
  • 2.可以根据该对象创建运送sql语句的Statement对象

• 方法:

  Statement createStatement() 创建statement对象
  CallableStatement prepareCall(String sql)调用数据库的存储过程
  PreparedStatement prepareStatement(String sql) 创建 PreparedStatement 对象

Statement对象

• 解释：该对象可以理解为一个 向数据库运送sql语句的 “小车”;

• 方法:

  • void addBatch(String sql) 向车上添加语句. (用于批量执行sql语句);insert update delete

  • int[] executeBatch() 将车上的语句 运送给数据库执行. 返回值存放每个语句执行后影响的行数. 因为是多个语句,所以用数组装.

  • void clearBatch() 清除车上的语句.

  以上3个方法是批量执行sql相关的(下面会有程序演示)

  • boolean execute(String sql) 执行一个sql语句. 如果该语句返回结果集 返回值为true(select). 如果该语句不返回结果集 返回false(insert update delete);

  • ResultSet executeQuery(String sql) 执行一个有结果集的查询. 会将结果集包装到resultset对象中.(select)

  • int executeUpdate(String sql) 执行一个没有结果集的语句. 会将语句影响的行数返回.(insert update delete)

• 建议:

  • 执行查询语句时使用: executeQuery方法
  • 执行增删改等语句时使用: executeUpdate方法

ResultSet对象

• 功能: 当执行的语句是查询语句时, resultSet对象用于封装查询结果.

• 方法:

  • boolean next() 该方法让结果集中的指针(游标)往下移动一行.并且判断改行是否有数据。 有返回true，没有返回false

  • String getString(int cloumnCount) 从当前指向的行中获得String 类型的数据. 根据列所在的索引位置取.

  • String getString(String columnName) 从当前指向的行中获得String 类型的数据. 根据列名取.

  • getXXX系列方法 有很多种, 没对针对的都是数据库中的不同类型.

  • 数据库中的类型根getXXX方法如何对应?
    

ResultSet滚动及修改

• 结果集滚动;

  • 滚动指的就是指针的位置不仅可以向下,还可以任意控制.

  • 涉及的方法如下:

    • boolean absolute(int row) 将指针移动到指定位置. 参数就是位置. 第一行的位置是1. 如果填写负数表示倒数.例如-1=>最后一行. 如果移动超出范围将会返回false.

    • void afterLast() 将光标移动到此 ResultSet 对象的末尾，正好位于最后一行之后。 (该行没有数据)

    • void beforeFirst() 将光标移动到此 ResultSet 对象的开头，正好位于第一行之前。(result的初始位置)
    • boolean first() 将光标移动到第一行
    • boolean last() 将光标移动到最后一行
    • boolean next() 光标向下移动一行.
    • boolean previous() next反方向移动.向上移动一行.

• 使用resultSet修改记录.

  • 默认情况下resultSet 是不能反向修改数据库中的记录的. 需要在创建Statement对象时, 通过指定参数 创建一个可以产生 可以修改数据的resultSet对象的Statement
    Statement createStatement(int resultSetType, int resultSetConcurrency)

    • 参数1 resultSetType - 结果集类型

      ResultSet.TYPE_FORWARD_ONLY 不支持结果集滚动,只能向前.

      ResultSet.TYPE_SCROLL_INSENSITIVE 支持滚动, 迟钝,不敏感的结果集.(默认的)

      ResultSet.TYPE_SCROLL_SENSITIVE 支持滚动, 敏感的结果集.但是效率底下

    • 参数2 resultSetConcurrency - 结果是否支持修改类型

      ResultSet.CONCUR_READ_ONLY 不支持修改

      ResultSet.CONCUR_UPDATABLE 支持修改

• 建议:

  • 不要使用resultSet 做修改的操作. 真的要做修改 我们要手写update语句来做. 修改的时候要知道对应的列名

释放资源

• 注意

  • 从小到大释放. resultSet < Statement < Connection
  • 3个都需要释放.

  • 释放时调用close方法即可. 如果其中一个对象的关闭 出现了异常. 也要保证其他的对象关闭方法被调用.

    resultSet.close();Statement.close();Connection.close();

    以上代码是无法保证一定都能执行的所以一般放到finally里面.

    try{    resultSet.close();}catch(Exception e){}finally{    try{        Statement.close();    }catch(Exception e){    }    finally{        try{            Connection.close();        }catch(Exception e){        }    }}

自定义JDBC工具类

• 我们在开发过程中经常对数据库进行操作，例如增删改查（CRUD）,每次进行操作都要对数据库注册驱动，创建链接，关闭资源流操作。非常繁琐而且每次都注册驱动浪费资源；所以我们可以把这几共同的操作写成一个工具类，使只注册一次驱动，通过配置文件类创建链接，通过传参类关闭链接和资源。
  

• 工具类代码

  import java.io.FileInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JDBCUtils {    private static String driver;    private static String url;    private static String user;    private static String password;    static {// 静态代码块只执行一次        try {            // 0读取配置文件            Properties prop = new Properties();            InputStream is = new FileInputStream("src/db.properties");            prop.load(is);            is.close();            driver = prop.getProperty("driver");            url = prop.getProperty("url");            user = prop.getProperty("user");            password = prop.getProperty("password");            // 1 注册驱动            Class.forName(driver);        } catch (Exception e) {            e.printStackTrace();        }    }    // 1 获得连接    public static Connection getConnection() {        Connection conn = null;        try {            // 2 获得连接            conn = DriverManager.getConnection(url, user, password);        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException("创建连接失败!");        }        return conn;    }    // 2 释放资源    // 1> 参数可能为空    // 2> 调用close方法要抛出异常,确保即使出现异常也能继续关闭    // 3>关闭顺序,需要从小到大    public static void close(Connection conn, Statement st, ResultSet rs) {        try {            if (rs != null) {                rs.close();            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                if (st != null) {                    st.close();                }            } catch (SQLException e) {                e.printStackTrace();            } finally {                try {                    if (conn != null) {                        conn.close();                    }                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }}

SQL注入攻击

• 早年登录逻辑,就是把用户在表单中输入的用户名和密码 带入如下sql语句. 如果查询出结果,那么 认为登录成功.

  SELECT * FROM USER WHERE NAME='' AND PASSWORD='xxx';

• sql注入: 请尝试以下 用户名和密码.

• 将用户名和密码带入sql语句, 如下:

  SELECT * FROM USER WHERE NAME='xxx' OR 1=1 -- and password='xxx';

  注意：NAME=’xxx’ OR 1=1 恒为true ‘–’在数据库中代表注释

• 发现sql语句失去了判断效果,条件部分成为了恒等式.导致网站可以被非法登录, 以上问题就是sql注入的问题.

  @Test//演示使用Statement对象,sql注入问题public void loginTest() throws Exception{    String name ="xxx' OR 1=1 -- ";    String password ="1234";    //1 获得连接    Connection conn= JDBCUtils.getConnection();    //2 获得Statement    Statement st = conn.createStatement();    //3 拼装sql语句    String sql = "SELECT * FROM userinfo  WHERE username='"+name+"' AND   PASSWORD='"+password+"';";    //4 执行sql并拿到结果    ResultSet rs = st.executeQuery(sql);    //5 根据结果判断是否登录成功    if(rs.next()){        System.out.println("登录成功!");    }else{        System.out.println("登录失败!");    }    //6关闭资源    JDBCUtils.close(conn, st, rs);}

• 原因

  将用户名密码带入sql语句,发现sql语句变成了如下形式:

  SELECT * FROM userinfo WHERE username='abcd'OR 1=1;--  AND PASSWORD='1234';

  该sql语句就是一个 恒等条件.所以 一定会查询出记录. 造成匿名登陆.有安全隐患

• 解决

  • 解决办法:在运送sql时,我们使用的是Statement对象. 如果换成prepareStatement对象,那么就不会出现该问题.

  • sql语句不要再直接拼写.而要采用预编译的方式来做.

• 完成如上两步.即可解决问题.

  • 为什么使用PrepareStatement对象能解决问题?

    • sql的执行需要编译. 注入问题之所以出现,是因为用户填写 sql语句 参与了编译. 使用PrepareStatement对象在执行sql语句时,会分为两步.

    • 第一步将sql语句 “运送” 到mysql上编译. 再回到java端 拿到参数 运送到mysql端.

    • 用户填写的 sql语句,就不会参与编译. 只会当做参数来看. 避免了sql注入问题;

    • PrepareStatement 在执行 母句相同, 参数不同的 批量执行时. 因为只会编译一次.节省了大量编译时间.效率会高.

• 使用PrepareStatement对象 与 Statement对象的区别

  1. Statement 可以先行创建, 然后将sql语句写入.
     PrepareStatement 在创建时一定要传入sql语句, 因为它要先运送到数据库执行预编译

     api:
     PreparedStatement pst = conn.prepareStatement(sql);

  2. PrepareStatement 在执行之前 先要设置 语句中的参数.

     api:     pst.setString(1, name);  -- set方法的调用要看 参数的类型.    char/varchar    setString    int             setInt    double          setDouble    datatime/timestamp  setDate

  3. Statement对象在真正执行时 传入sql语句PrepareStatement 在执行之前已经 设置好了sql语句 以及对应参数. 执行方法不需要参数

     api:    ResultSet rs = pst.executeQuery();

Statement对象批量执行SQL语句

• 前面说Statement对象的时候已经说了批量执行SQL语句的方法，这里就直接上代码。

  @Test// 1 使用Statement对象批量执行sqlpublic void CreatTest() throws Exception {    // 1 获得连接    Connection conn = JDBCUtils.getConnection();    // 2 获得Statement    Statement st = conn.createStatement();    // 3 添加多条sql语句到st中 不支持批量查询    st.addBatch("create table t_stu ( id int primary key auto_increment , name varchar(20) )");    st.addBatch("insert into t_stu values(null,'tom')");    st.addBatch("insert into t_stu values(null,'jerry')");    st.addBatch("insert into t_stu values(null,'jack')");    st.addBatch("insert into t_stu values(null,'rose')");    // 4 执行sql    // 所返回的数组包含 N 个元素，这就意味着在调用 executeBatch() 时批处理中的前 N 个命令被成功执行    int[] results = st.executeBatch();    System.out.println(Arrays.toString(results));    // 5关闭资源    JDBCUtils.close(conn, st, null);}@Test// 2 使用PrepareStatement对象批量执行sqlpublic void insertTest() throws Exception {    // 1 获得连接    Connection conn = JDBCUtils.getConnection();    // 2 书写sql语句    String sql = "insert into t_stu values(null,?)";    // 3 创建PrepareStatement    PreparedStatement ps = conn.prepareStatement(sql);    // 4 循环.添加参数    for (int i = 0; i < 100; i++) {        ps.setString(1, "用户" + i);        ps.addBatch();    }    // 5 批量执行    int[] results = ps.executeBatch();    System.out.println(Arrays.toString(results));    // 5关闭资源    JDBCUtils.close(conn, ps, null);}

• 附

  上面用到的表t_stu结构如下：

  CREATE TABLE `t_stu` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

事务

• 事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)，是恢复和并发控制的基本单位。

• 例如：A给B转账500元。第一步，A数据库的账户上余额减少500元；
  第二步，B数据库的账户上余额增加500元；A给B转账500元这件事就是一个事务，上述两个步骤不可分割，试想下，如果A减少500元后，系统异常，B没有增加500元，怎么办？

• 事务机制的建立就是为了在上述类似的情况发生下能恢复异常发生前数据库的状态。恢复到异常发生前数据库的状态称为回滚。

• 事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

  • 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
  • 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
  • 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  • 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
• 事务编写流程
  
  • 首先，设置事务的提交方式为非自动提交conn.setAutoCommit(false);
  • 接下来，将需要添加事务的代码放入try，catch块中，为了在事务发生异常时能捕获并处理
  • 然后，在try块内添加事务的提交操作，表示操作无异常，提交事务，体现原子性conn.commit();
  • 在catch块内添加回滚事务，表示操作出现异常，撤销事务conn.rollback();
  • 最后，设置事务提交方式为自动提交conn.setAutoCommit(true);,因为默认的是自动提交，不设置的话之后的所有操作都要手动提交。

• 代码如下

  import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class TransactionDemo {    public static void main(String[] args) {        Connection conn = JDBCUtils.getConnection();        //normalTest(conn);        transaction(conn);    }    /**     * 事务     * @param conn 链接对象     */    private static void transaction(Connection conn) {        PreparedStatement stmt = null;        try {            conn.setAutoCommit(false);            String sql1 = "UPDATE userinfo SET money=money+10000 WHERE userid = '3'";            String sql2 = "UPDATE userinfo SET money=money-10000 WHERE userid = '4'";            stmt = conn.prepareStatement(sql1);            stmt.executeUpdate();            int a = 1 / 0;            stmt = conn.prepareStatement(sql2);            stmt.executeUpdate();            conn.commit();            System.out.println("事务提交成功。");        } catch (Exception e) {            // System.out.println(e);            System.out.println("事务提交失败！开始回滚。");            try {                conn.rollback();                System.out.println("事务回滚成功。");            } catch (SQLException e1) {                System.out.println("事务回滚失败。");            }        } finally {            try {                conn.setAutoCommit(true);            } catch (SQLException e1) {                e1.printStackTrace();            }            if (stmt != null) {                try {                    stmt.close();                } catch (SQLException e) {                    System.out.println("资源关闭失败");                    e.printStackTrace();                }            }            if(conn != null){                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }    /**     * 正常提交没有使用事务     * @param conn     */    private static void normalTest(Connection conn) {        PreparedStatement stmt = null;        String sql1 = "UPDATE userinfo SET money=money+10000 WHERE userid = '3'";        String sql2 = "UPDATE userinfo SET money=money-10000 WHERE userid = '4'";        try {            stmt = conn.prepareStatement(sql1);            stmt.executeUpdate();            int a = 1 / 0;            stmt = conn.prepareStatement(sql2);            stmt.executeUpdate();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

驱动包及驱动源码下载地址

• 链接: https://pan.baidu.com/s/1qYbYM7a 密码: r6dn