JDBC详解

1. 目录

   JDBC

   数据库驱动的概念

   JDBC的概念

   JDBC包

   六个步骤实现JDBC

   JDBC API详解

   数据库URL

   Connection

   Statement

   ResultSet

   释放资源

   JDBC实现CRUD

   SQL注入攻击

   Sql注入攻击的原理

   PreparedStatement

   PreparedStatement防止sql注入

   批处理

   批处理业务场景

   执行批处理SQL语句

   采用Statement.addBatch(sql)方式实现批处理：

   连接池

   连接池概述

   手写连接池

   C3P0

   C3P0概述

   使用C3P0：

   ---

2. JDBC

   1. 数据库驱动

      1. 数据库驱动的概念

数据库厂商提供的用来操作数据库的jar包就叫做数据库的驱动

1. 1. JDBC

      1. JDBC的概念

JDBC（Java DataBase Connectivity）就是Java数据库连接，说白了就是用Java语言来操作数据库。

由于不同的数据库厂商提供的数据库驱动各不相同,在使用不同数据库时需要学习对应数据库驱动的api，对于开发人员来说学习成本十分的高。

于是sun提供了JDBC的规范,本质上一大堆的接口,要求不同的数据库厂商提供的驱动都实现这套接口,这样以来开发人员只需要学会JDBC这套接口,所有的数据库驱动作为这套接口的实现，就都会使用了。

1. 1. 1. JDBC包

JDBC主要是由 java.sql 和javax.sql包组成的,并且这两个包已经被集成到J2SE的规范中了,这意味着,只要一个普通的java程序就可以使用JDBC。

要注意的是,在开发数据库程序时,除了如上的两个包,还需要手动的导入具体的数据库驱动。

如图-1所示：

 

图-1

1. 1. 六个步骤实现JDBC

      1. 代码实现

Connection conn = null;

Statement stat = null;

ResultSet rs = null;

try {

//1.注册数据库驱动

DriverManager.registerDriver(new Driver());

    //2.获取数据库的连接

conn=DriverManager.getConnection("jdbc:mysql:///day15?user=root&password=root");

//3.获取传输器对象

stat = conn.createStatement();

//4.传输sql语句到数据库中执行,获取结果集对象

rs = stat.executeQuery("select * from user");

//5.遍历结果集获取需要的数据

while(rs.next()){

         String name = rs.getString("name");

         Date date = rs.getDate("birthday");

         System.out.println(name+":"+date.toLocaleString());

         }

} catch (Exception e) {

         e.printStackTrace();

}finally{

         //6.关闭资源

         if(rs != null){

                  try {

                           rs.close();

                  } catch (SQLException e) {

                           e.printStackTrace();

                  } finally{

                           rs = null;

                  }

         }

         if(stat != null){

                  try {

                           stat.close();

                  } catch (SQLException e) {

                           e.printStackTrace();

                  } finally{

                           stat = null;

                  }

         }

         if(conn != null){

                  try {

                           conn.close();

                  } catch (SQLException e) {

                           e.printStackTrace();

                  } finally{

                           conn = null;

                  }

         }

}

1. 1. JDBC API详解

      1. 注册数据库驱动：

使用DriverManager.registerDriver(new Driver());注册数据库有两个缺点，首先，通过观察mysql的中Driver接口的实现类发现在静态代码块中注册驱动的逻辑，所以这种方式会造成驱动被注册两次。另外，这种方式导致了程序和具体的数据库驱动绑死在了一起，程序的灵活性比较低。

所以推荐使用：Class.forName(“com.mysql.jdbc.Driver”);的方式注册数据库驱动。

获取数据库连接

Connection conn = DriverManager.getConnection(url,name,psw);

1. 1. 1. 数据库URL

URL用于标识数据库的位置，程序员通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：

   jdbc:mysql://localhost:3306/test ?参数名=参数值

常用数据库URL地址的写法：

Oracle写法：jdbc:oracle:thin:@localhost:1521:sid

SqlServer写法:jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=sid

MySql:jdbc:mysql://localhost:3306/sid

Mysql的url地址的简写形式： jdbc:mysql:///sid

1. 1. 1. Connection

Jdbc程序中的Connection，它用于代表数据库的链接，Connection是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过connection对象完成的，这个对象的常用方法：

createStatement()：创建向数据库发送sql的statement对象。

prepareStatement(sql):创建向数据库发送预编译sql的PreparedSatement对象。

prepareCall(sql)：创建执行存储过程的callableStatement对象。

setAutoCommit(boolean autoCommit)：设置事务是否自动提交。

commit()：在链接上提交事务。

rollback()：在此链接上回滚事务。

1. 1. 1. Statement

Jdbc程序中的Statement对象用于向数据库发送SQL语句， Statement对象常用方法：

executeQuery(String sql) ：用于向数据库发送查询语句。

executeUpdate(String sql)：用于向数据库发送insert、update或delete语句

execute(String sql)：用于向数据库发送任意sql语句

addBatch(String sql)：把多条sql语句放到一个批处理中。

executeBatch()：向数据库发送一批sql语句执行。

1. 1. 1. ResultSet

Jdbc程序中的ResultSet用于代表Sql语句的执行结果。Resultset封装执行结果时，采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前，调用ResultSet.next() 方法，可以使游标指向具体的数据行，进行调用方法获取该行的数据。

ResultSet既然用于封装执行结果的，所以该对象提供的都是用于获取数据的get方法：

获取任意类型的数据

getObject(int index)

getObject(string columnName)

获取指定类型的数据，例如：

getString(int index)

getString(String columnName)

getInt(columnIndex)

getInt(columnLabel)

getDouble(columnIndex)

getDouble(columnLabel)

...

操作游标的方法,例如:

next()：移动到下一行

Previous()：移动到前一行

absolute(int row)：移动到指定行

beforeFirst()：移动resultSet的最前面。

afterLast() ：移动到resultSet的最后面。

...

1. 1. 1. 释放资源

Jdbc程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通常是ResultSet, Statement和Connection对象。

特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时、正确的关闭，极易导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

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

1. 1. JDBC实现CRUD

      1. JDBC实现CRUD

package cn.tedu;

import org.junit.Test;

import java.sql.*;

public class Test1 {


    //修改
    @Test
    public void test03(){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

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

            conn = DriverManager.getConnection("jdbc:mysql:///mydb2", "root", "root");

            ps = conn.prepareStatement("update user2 set name='dd' where id = 4");

            int i = ps.executeUpdate();

            if (i>0){
                System.out.println("影响"+i);
            }else {
                System.out.println("添加失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    rs=null;
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    conn=null;
                }
            }
        }


    }

    //增加
    @Test
    public void test02(){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql:///mydb2","root","root");

            ps = conn.prepareStatement("insert into user2 values (4,'zl','444')");

            int i = ps.executeUpdate();
            if (i>0) System.out.println("影响"+i+"行");
            else System.out.println("添加失败");



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

    //查询
    @Test
    public void test01(){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

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

            conn = DriverManager.getConnection("jdbc:mysql:///mydb2", "root", "root");

            ps = conn.prepareStatement("select * from user2");

            rs = ps.executeQuery();

            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String pwd = rs.getString("pwd");
                System.out.println(id+" "+name+" "+pwd);
            }



        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    rs=null;
                }
            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    ps=null;
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    conn=null;
                }
            }
        }


    }

    //删除
    @Test
    public void test04(){

        Connection conn = null;
        PreparedStatement ps = null;

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

            conn = DriverManager.getConnection("jdbc:mysql:///mydb2", "root", "root");

            ps = conn.prepareStatement("delete from user2 where id = 4");

            int i = ps.executeUpdate();

            if (i>0){
                System.out.println("影响"+i);
            }else {
                System.out.println("删除失败");
            }


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


    }

}

1. 1. SQL注入攻击

      1. Sql注入攻击演示

在登录界面我们输入，如图-6,图-7所示:

 

图-6

 

图-7

发现无需输入密码，直接登录，发现无需密码登录了进去。

这就是发生了SQL注入问题。

1. 1. 1. Sql注入攻击的原理

由于jdbc程序在执行的过程中sql语句在拼装时使用了由页面传入参数，如果用户,恶意传入一些sql中的特殊关键字，会导致sql语句意义发生变化，这种攻击方式就叫做sql注入。

如何防止SQL注入攻击呢？

这时候就需要用到PreparedStatement对象。

1. 1. PreparedStatement

      1. PreparedStatement对象

PreparedStatement是Statement的孩子，不同的是，PreparedStatement使用预编译机制，在创建PreparedStatement对象时就需要将sql语句传入，传入的过程中参数要用?替代，这个过程回导致传入的sql被进行预编译，然后再调用PreparedStatement的setXXX将参数设置上去，由于sql语句已经经过了预编译，再传入特殊值也不会起作用了。

PreparedStatement使用了预编译机制，sql语句在执行的过程中效率比Statement要高。

PreparedStatement使用了 “？”通配符省去了字符串的拼接，使代码更加优雅。

1. 1. 1. PreparedStatement防止sql注入

想要防止Sql注入攻击，我们可以使用PreparedStatement防止sql注入

package cn.tedu.ps;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Scanner;

import cn.tedu.test.JDBCUtils;

public class Test1 {

public static void main(String[] args) {

         Scanner scan = new Scanner(System.in);

         System.out.println("请登陆");

         System.out.println("请输入用户名：");

         String username = scan.nextLine();

         System.out.println("请输入密码：");

         String password = scan.nextLine();

        

         login(username,password);

        

}

private static void login(String username, String password) {

         Connection conn = null;

         PreparedStatement ps = null;

         ResultSet rs = null;

         try {

                  conn = JDBCUtils.getConnection();

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

                  ps = conn.prepareStatement(sql);

                  ps.setString(1,username );

                  ps.setString(2, password);

                 

                  rs = ps.executeQuery();

                 

                  if(rs.next()){

                           System.out.println("恭喜，登陆成功！");

                  }else{

                           System.out.println("用户名或密码错误！");

                  }

         } catch (Exception e) {

                  e.printStackTrace();

                  throw new RuntimeException();

         }finally{

                  JDBCUtils.close(conn, ps, rs);

         }

}

}

经测试可以防止sql注入共计问题。

1. 批处理

   1. 批处理业务场景

      1. 批处理业务场景

当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。

1. 1. 1. Statement方式实现批处理

Statement.addBatch(sql)

执行批处理SQL语句

executeBatch()方法：执行批处理命令

clearBatch()方法：清除批处理命令

示例代码：

Connection conn = null;

Statement st = null;

ResultSet rs = null;

try {

conn = JdbcUtil.getConnection();

String sql1 = "insert into person(name,password,email,birthday)          values('kkk','123','abc@sina.com','1978-08-08')";

String sql2 = "update user set password='123456' where id=3";

st = conn.createStatement();

st.addBatch(sql1);  //把SQL语句加入到批命令中

st.addBatch(sql2);  //把SQL语句加入到批命令中

st.executeBatch();

} finally{

    JdbcUtil.free(conn, st, rs);

}

采用Statement.addBatch(sql)方式实现批处理：

优点：可以向数据库发送多条不同的SQL语句。

缺点：

SQL语句没有预编译。

当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：

insert into user(name,password) values('aa','111');

insert into user(name,password) values('bb','222');

insert into user(name,password) values('cc','333');

insert into user(name,password) values('dd','444');

实现批处理的第二种方式：

PreparedStatement.addBatch()

示例代码：

conn = JdbcUtil.getConnection();

String sql = "insert into person(name,password,email,birthday)

values(?,?,?,?)";

ps = conn.prepareStatement(sql);

for(int i=0;i<50000;i++){

    ps.setString(1, "aaa" + i);

ps.setString(2, "123" + i);

ps.setString(3, "aaa" + i + "@sina.com");

ps.setDate(4,new Date(1980, 10, 10));

ps.addBatch();

if(i%1000==0){

ps.executeBatch();

ps.clearBatch();

}

}

ps.executeBatch();

采用PreparedStatement.addBatch()实现批处理：

优点：发送的是预编译后的SQL语句，执行效率高。

缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

1. 连接池

   1. 连接池概述

      1. 连接池概述

用户每次请求都需要向数据库获得链接，而数据库创建连接通常需要消耗相对较大的资源，创建时间也较长。假设网站一天10万访问量，数据库服务器就需要创建10万次连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、宕机。如图-9所示：

 

图-9

频繁的开关连接相当的耗费资源,所以我们可以设置一个连接池,在程序启动时就初始化一批连接,在程序中共享,需要连接时从池中获取,用完再还回池中,通过池共享连接,减少开关连接的次数,提高程序的效率。如图-10所示:

 

图-10

1. 1. 手写连接池

      1. 手写连接池

Sun公司为连接池提供 javax.sql.DataSource接口,要求连接池去实现,所以连接池也叫数据源。

我们可以自己实现这个接口来实现一个连接池。

package com.tarena;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.LinkedList;

import java.util.List;

import javax.sql.DataSource;

public class MyPool implements DataSource {

private static List<Connection> pool = new LinkedList<Connection>();

static{

         try{

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

                  for(int i=0;i<5;i++){

                           Connection conn = DriverManager.getConnection("jdbc:mysql:///day11","root","root");

                           pool.add(conn);

                  }

         }catch (Exception e) {

                  e.printStackTrace();

                  throw new RuntimeException(e);

         }

}

public Connection getConnection() throws SQLException {

         if(pool.size()==0){

                  for(int i=0;i<3;i++){

                           Connection conn = DriverManager.getConnection("jdbc:mysql:///day11","root","root");

                           pool.add(conn);

                  }

         }

         System.out.println("获取了一个连接,池里还剩余"+pool.size()+"个连接");

         return pool.remove(0);

}

private void retConn(Connection conn){

         try {

                  if(conn!=null && !conn.isClosed()){

                           pool.add(conn);

                           System.out.println("还回了一个连接,池里还剩余"+pool.size()+"个连接");

                  }

         } catch (SQLException e) {

                  e.printStackTrace();

         }

}

public Connection getConnection(String username, String password)

                  throws SQLException {

         // TODO Auto-generated method stub

         return null;

}

public PrintWriter getLogWriter() throws SQLException {

         // TODO Auto-generated method stub

         return null;

}

public int getLoginTimeout() throws SQLException {

         // TODO Auto-generated method stub

         return 0;

}

public void setLogWriter(PrintWriter out) throws SQLException {

         // TODO Auto-generated method stub

}

public void setLoginTimeout(int seconds) throws SQLException {

         // TODO Auto-generated method stub

}

public boolean isWrapperFor(Class<?> iface) throws SQLException {

         // TODO Auto-generated method stub

         return false;

}

public <T> T unwrap(Class<T> iface) throws SQLException {

         // TODO Auto-generated method stub

         return null;

}

}

如上代码写的连接池，还需要在使用完连接后记得不能关闭连接，而是要调用retConn方法将连接还回池中。

我们想能不能想办法改造conn的close方法，使close方法不会真的关闭连接而是将连接还回池中。

想要改造不喜欢的方法有 继承 装饰 动态代理三种方式实现。

1. 1. C3P0

      1. C3P0概述

我们手写的连接池是比较简陋的，是为了讲解连接池的原理。其实在真实开发中可以使用开源的数据库连接池。其中C3P0是比较常用的一种。

1. 1. 1. 使用C3P0：

(1)导入jar包

(2)写配置文件,放置到类加载目录下c3p0-config.xml

<?xml version="1.0"?>

<c3p0-config>

<default-config>

         <property name="driverClass">com.mysql.jdbc.Driver</property>

         <property name="jdbcUrl">jdbc:mysql:///mydb1</property>

         <property name="user">root</property>

         <property name="password">root</property>

</default-config>

</c3p0-config>

(3)程序中获取连接池对象

ComboPooledDataSource pool = new ComboPooledDataSource();