JDBC（一）-阿里云开发者社区

一. JDBC的概述

1>.什么是JDBC

概念：Java DataBase Connectivity Java 数据库连接， Java语言操作数据库

JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口（也就是数据库驱动），提供数据库驱动jar包。我们可以使用这套接（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

2>.快速入门JDBC

public class JdbcDemo1 {
    public static void main(String[] args)throws Exception {
        //1.导入驱动jar包
        //2.配置四大参数
        String Driver="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/jdbctest";
        String uername="root";
        String password="root";
        //3.注册驱动
        Class.forName(Driver);
        //4.创建连接Connection
        Connection conn=DriverManager.getConnection(url,uername,password);
        //5.定义sql语句
        String sql="insert into user values(1,'小智',23,'男')";
        //6.通过Connection对象创建statement
        Statement stmt = conn.createStatement();
        //7.通Statment对象操作sql
        int result=stmt.executeUpdate(sql);
        if(result>0){
            System.out.println("成功插入一条数据");
        }
        //8.关闭连接[倒关]
        stmt.close();
        conn.close();
    }
}

3>. DriverManager（类）

功能：①. 注册驱动 ②. 获取数据库连接

注解驱动

告诉程序该使用哪一个数据库驱动jar

理解：

在com.mysql.jdbc.Driver中为什么使用了Class.forName(“com.mysql.jdbc.Driver”）就能执行static中的代码？这是因为反射是一种自动引用，这样会初始化该类，所以static会执行

注意：class.forName（）可以不写是因为在META-INF/services目录下有一个名为java.mysql.Driver文件

获取数据库连接

方法：static Connection getConnection(String url, String user, String password)

我们在连接mysql数据库的时候一般都会在url后面添加useUnicode=true&characterEncoding=UTF-8 ，但是问什么要添加呢？

1. 存数据时：
数据库在存放项目数据的时候会先用UTF-8格式将数据解码成字节码，
然后再将解码后的字节码重新使用GBK编码存放到数据库中。
2.取数据时：
在从数据库中取数据的时候，数据库会先将数据库中的数据按GBK格式
解码成字节码，然后再将解码后的字节码重新按UTF-8格式编码数据，最
后再将数据返回给客户端。
注意：在xml配置文件中配置数据库URL时，要使用&的转义字符也就是&amp;
例如：
<property name="url"
value="jdbc:mysql://localhost:3306/email?useUnicode=true&amp;characterEncoding=UTF-8"/>

4>. Conncetion接口（接口）

①. 获取执行sql 的对象 ②. 管理事务：

获取执行sql 的对象

Statement createStatement( )

PreparedStatement prepareStatement(String sql)

管理事务

①. 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务

②. 提交事务：commit( )

③. 回滚事务：rollback( )

5>. Statement（接口）

执行sql的对象

①. boolean execute(String sql) ：可以执行任意的sql 了解

②. int executeUpdate(String sql)：执行（insert、update、delete）语句、DDL(create，alter、drop)语句

返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功返回值>0的则执行成功，反之，则失败。

③. ResultSet executeQuery(String sql)：执行DQL（select)语句

  Statement stmt = null;
       Connection conn = null;
       try {
           //1. 注册驱动
           Class.forName("com.mysql.jdbc.Driver");
           //2. 定义sql
           String sql = "insert into account values(null,'王五',3000)";
           //3.获取Connection对象
           conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
           //4.获取执行sql的对象 Statement
           stmt = conn.createStatement();
           //5.执行sql
           int count = stmt.executeUpdate(sql);//影响的行数
           //6.处理结果
           System.out.println(count);
           if(count > 0){
               System.out.println("添加成功！");
           }else{
               System.out.println("添加失败！");
           }
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       } catch (SQLException e) {
           e.printStackTrace();
       }finally {
           //stmt.close();
           //7. 释放资源
           //避免空指针异常
           if(stmt != null){
               try {
                   stmt.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if(conn != null){
               try {
                   conn.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }

6>. ResultSet（接口）

ResultSet：结果集，封装了使用JDBC进行查询的结果

①. Boolean next( )：游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true

②. ResultSet 返回实际上就是一张数据表，有一个指针指向数据表的第一行的前面。可以调用 next( ）方法检测下一行是否有效，若有效该方法返回true，且指针下移，相当于Iterator方法hasNext( )和next( )方法的结合体。

③. 当指针定位到一行时，可以通过调用getXxx(index)或getXxx(columnName)获取每一列的值例如: getInt(1)、getString("name")

int：代表列的编号,从1开始如： getInt(1)

String：代表列名称。如： getDouble(“balance”)

④. ResultSet 当然也需要关闭

//遍历结果集
* 注意：
  * 使用步骤：
    1. 游标向下移动一行
    2. 判断是否有数据
    3. 获取数据
     //循环判断游标是否是最后一行末尾。
           while(rs.next()){
               //获取数据
               //6.2 获取数据
               int id = rs.getInt(1);
               String name = rs.getString("name");
               double balance = rs.getDouble(3);
               System.out.println(id + "---" + name + "---" + balance);
           }

    @Test
  public void testResultSet(){
    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;
    try{
      //1.获取Connection
      conn=JDBCTools.getConnection();
      //2.获取Statement
      stmt=conn.createStatement();
      //3.准备Sql
      String sql="select * from customer";
      //String sql="select * from customer where id=1";
      //4.执行查询，得到ResultSet
      rs=stmt.executeQuery(sql);
      //5.处理resultSet  1条
    /*  if(rs.next()){
        int id=rs.getInt(1);
        String name=rs.getString("name");
        String email=rs.getString(3);
        Date date=rs.getDate(4);
        System.out.println(id+" "+name+" "+email+" "+date);
      }*/
      //多条
      while(rs.next()){
        int id=rs.getInt(1);
        String name=rs.getString("name");
        String email=rs.getString(3);
        Date date=rs.getDate(4);
        System.out.println(id+" "+name+" "+email+" "+date);
      }
    }catch (Exception e) {
      // TODO: handle exception
    }finally{
      //6.关闭数据库连接
      JDBCTools.releaseSoucreT(rs, stmt, conn);
    } 
  }

二. Jdbc工具类

7>. 工具类的使用 [ 掌握 ]

在src下获取配置文件

ClassLoader classLoader = JDBCUtils.class.getClassLoader();

URL res=classLoader.getResource(“jdbc.properties”);

//这里的path是获取的绝对路径

String path=res.getPath();

注意：关闭的顺序是先得到的后关闭，后得到的先关闭

抽取JDBC工具类 ： JDBCUtils
  * 目的：简化书写
  * 分析：
    1. 注册驱动也抽取
    2. 抽取一个方法获取连接对象
      * 需求：不想传递参数（麻烦），还得保证工具类的通用性。
      * 解决：配置文件
        jdbc.properties
          url=
          user=
          password=
    3. 抽取一个方法释放资源

public class JDBCUtils {
    private static String url;
    private static String username;
    private static String password;
    private static String driver;
    /*
    文件的读取,只需要读取一次即可拿到这些值。使用静态代码块
    * */
    static{
        try { //读取配置文件
            //1.创建properties 集合类
            Properties prop=new Properties();
            //获取src路径下的文件的方式-->ClassLoader
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res=classLoader.getResource("jdbc.properties");
            //---file:/E:/soft/idea/Jvm/out/production/Jdbc_Test/jdbc.properties
            System.out.println("---"+res);
            //这里的path是获取的绝对路径
            String path=res.getPath();
            //6666/E:/soft/idea/Jvm/out/production/Jdbc_Test/jdbc.properties
            System.out.println("6666"+path);
            //2.加载文件
            prop.load(new FileReader(path));
            //3.获取数据,赋值
            driver=prop.getProperty("driver");
            url=prop.getProperty("url");
            username=prop.getProperty("username");
            password=prop.getProperty("password");
            //4.注册驱动
            Class.forName(driver);
        } 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(Connection conn, Statement stmt){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /*
    释放资源:查询的时候
    * */
    public static void close(Connection conn, Statement stmt, ResultSet rs){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

8>. 登录小案列

/*
* 需求：
    1. 通过键盘录入用户名和密码
    2. 判断用户是否登录成功
        * select * from user where name = "" and password = "";
        * 如果这个sql有查询结果，则成功，反之，则失败
* */
public class TestJdbc {
    public static void main(String[] args)throws Exception{
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入一个用户名");
        String username = sc.nextLine();
        System.out.println("请输入一个密码");
        String password = sc.nextLine();
        boolean login = login(username, password);
        if(login==true){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
    }
    private static boolean login(String username, String password) {
     if(username==null||password==null){
         System.out.println("你输入的账号或密码为空,请重新输入");
     }
     //获取连接
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        try {
             conn = JDBCUtils.getConnection();
             stmt = conn.createStatement();
             String sql="select * from user where name='"+username+"' and password='"+password+"'";
             rs=stmt.executeQuery(sql);
             return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,stmt,rs);
        }
      return false;
    }
}

9>. PreparedStatament

功能

（1）防SQL攻击；

（2）提高效率！

1. SQL注入

①. SQL注入问题：在拼接sql时，有一些sql的特殊关键字参与字符串的拼接，会造成安全性问题

出现问题：

（1）输入用户随便，输入密码： a’ or ‘a’='a

(2) sql：select * from user where username=‘asdafe’ and password=‘a’ or ‘a’=‘a’

②. 解决SQL注入问题：使用PreparedStatement对象来解决

③. 预编译的SQL：参数使用？作为占位符

2. PreparedStatement的说明及使用

①. PreparedStatement:是Statement的子接口，可以传入带占位符的SQL语句，并且提供了补充占位符变量的方法

②. 使用PreparedStatement

过程[ 掌握 ]

（1）创建PreparedStatement：

String sql=“insert into examstudent values(?,?,?,?,?)”;

PreparedStatement ps=conn.preparedStatement(sql);

（2）调动PreparedStatement的setXxx(int index,object val):设置占位符：index从1开始

（3）执行SQL语句：executeQuery（）或executeUpdate（）注意：执行时不再需要传入SQL语句

   @Test
  public void fun1() throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = JDBCTools.getConnection();
      //1.创建PreparedStatement：
      String sql = "insert into customer(name,email,birth) values(?,?,?)";
      ps = conn.prepareStatement(sql);
      //2.调动PreparedStatement的setXxx(int index,object val)
      ps.setString(1, "maimai");
      ps.setString(2, "888@qq.com");
      ps.setDate(3, new Date(new java.util.Date().getTime()));
      //3.执行SQL语句
      ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCTools.releaseSoucreT(null, ps, conn);
    }
  }

预编译的原理

①. 每个pstmt都与一个sql模板绑定在一起，先把sql模板给数据库，数据库先进行校验，再进行编译。执行时只是把参数传递过去而已！

②. 若二次执行时，就不用再次校验语法，也不用再次编译！直接执行！

批处理

10>. JDBC控制事务

①. 事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。

②. 操作：①. 开启事务 ②. 提交事务 ③. 回滚事务

③. 使用Connection对象来管理事务

（1）开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务，在执行sql之前开启事务

（2）提交事务：commit() ：当所有sql都执行完提交事务

（3）回滚事务：rollback()：在catch中回滚事务

/*
事务的操作
 * */
public class JDBCaccount {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement pstm1=null;
        PreparedStatement pstm2=null;
        try {
            conn = JDBCUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            //2.定义sql
            //2.1. 张三  -500
            String sql="update account set money =money-? where id=?";
            //2.2. 李四 +500
            String sql2="update account set money =money+? where id=?";
            //3.获取执行sql对象
            pstm1 = conn.prepareStatement(sql);
            pstm2=conn.prepareStatement(sql2);
            //4.设置参数
            pstm1.setDouble(1,500);
            pstm1.setInt(2,1);
            pstm2.setDouble(1,500);
            pstm2.setInt(2,2);
            //5.执行sql
            pstm1.executeUpdate();
            //手动制造异常
            //int i=3/0;
            pstm2.executeUpdate();
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            if(conn!=null){
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,pstm1);
            JDBCUtils.close(conn,pstm2);
        }
    }
}

11>. 作业小练习[ 重点掌握 ]

create table if not exists selldb(
    id int PRIMARY KEY auto_increment,
    name varchar(225),
    sellNumbers int,
    sellmoney int,
    money int
);
insert  into selldb values
(1,"郭凤芝",3,900000,8000),(2,"李清风",1,250000,5000),
(3,"杨晓初",0,0,4000),(4,"霍币",5,1000000,6000),
(5,"宋明",6,1200000,5000),(6,"杨洋",2,502000,7000);

利用JDBC按照销售额的降序进行排列将销售额前三名的工资分别上涨1000,800,50 0（利用JDBC），并打印上涨前后的内容

//JdbcTools
public class JdbcTools {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private JdbcTools(){}
    static{
        try {
            Properties prop=new Properties();
            ClassLoader classLoader = JdbcTools.class.getClassLoader();
            InputStream is=classLoader.getResourceAsStream("db.properties");
            prop.load(is);
            driver=prop.getProperty("driver");
            url=prop.getProperty("url");
            username=prop.getProperty("username");
            password=prop.getProperty("password");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //创建连接
    public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    //增删改: 两个
    public void close(Statement stmt, Connection conn){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public void close(ResultSet rs,Statement stmt, Connection conn){
        close(stmt,conn);
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

//db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbctest
username=root
password=root

    @Test
    public void fun4()throws Exception{
        //4.利用JDBC按照销售额的降序进行排列将销售额前三名的工资分别上涨1000,800,500（利用JDBC），并打印上涨前后的内容
        Connection conn = JdbcTools.getConnection();
        String sql="select sellmoney,id from selldb  order by sellmoney desc limit 0,3";
        PreparedStatement pstm=conn.prepareStatement(sql);
        ResultSet rs=pstm.executeQuery();
        /*
        分析: 分别上涨1000,800,500
         1.定义一个数组,该数组存放的就是要上涨的工资
         2.定义一个变量,作为数组的所引 index=0;每次循环都让索引++一次
         3.每次循环都拼写一个更新的sql,需要动态的拼接
         4.每次循环都执行一次更新的sql
        * */
        //1.定义一个数组,该数组存放的就是要上涨的工资
        int[]arr={1000,800,500};
        System.out.println("前三名的工资是:");
        int index=0;
        String sql2;
        while (rs.next()){
            int id=rs.getInt("id");
            int sellmoney=rs.getInt("sellmoney");
            System.out.println(id+" "+sellmoney);
            //3.每次循环都拼写一个更新的sql,需要动态的拼接
            //sql="update selldb set sellmoney=sellmoney+"+arr[index]+" where id= "+id+"";
            sql2="update selldb set sellmoney=sellmoney+"+arr[index]+" where id= "+id;
            PreparedStatement pstm2=conn.prepareStatement(sql2);
            //4.每次循环都执行一次更新的sql
            pstm2.executeUpdate();
            index++;
        }
        System.out.println("上涨后的工资是:");
        //修改后要把数据重新查询出来显示在页面上
        ResultSet rs2=pstm.executeQuery();
        while(rs2.next()){
            int id=rs2.getInt("id");
            int sellmoney=rs2.getInt("sellmoney");
            System.out.println(id+" "+sellmoney);
        }
        //System.out.print(sellone+" "+selltwo+" "+sellthere);
    }

12>. 时间类型

关于java.util (父类) 和 java.sql（子类）的转换

数据库类型与java中类型的对应关系：

DATE java.sql.Date

TIME java.sql.Time

TIMESTAMP java.sql.Timestamp

①. 领域对象（domain）中的所有属性不能出现java.sql包下的东西！即不能使用java.sql.Date；

②. ResultSet#getDate()返回的是java.sql.Date( )

③. PreparedStatement#setDate(int, Date)，其中第二个参数也是java.sql.Date

时间类型的转换：

java.util.Date 变成 java.sql.Date、Time、Timestamp

把util的Date转换成毫秒值

使用毫秒值创建sql的Date、Time、Timestamp

java.sql.Date、Time、Timestamp 变成 java.util.Date

这一步不需要处理了：因为java.sql.Date是java.util.Date [ 子类直接转成父类 ]

练习

①. Date：表示日期，只有年月日，没有时分秒。会丢失时间；

②. Time：表示时间，只有时分秒，没有年月日。会丢失日期；

③. timestamp：表示时间戳，有年月日时分秒，以及毫秒

Timestamp：表示时间戳，有年月日时分秒，以及毫秒。

当需要把java.util.Date转换成数据库的三种时间类型时，这就不能直接赋值了，这需要使用数据库三种时间类型的构造器。java.sql包下的Date、Time、TimeStamp三个类的构造器都需要一个long类型的参数，表示毫秒值。创建这三个类型的对象，只需要有毫秒值即可。我们知道java.util.Date有getTime()方法可以获取毫秒值，那么这个转换也就不是什么问题了。

# 我们来创建一个dt表：
CREATE TABLE dt(
  d DATE,
  t TIME,
  ts TIMESTAMP
)

//下面是从dt表中查询数据的代码：
@Test
  public void fun2() throws SQLException {
    Connection con = JdbcUtils.getConnection();
    String sql = "select * from dt";
    PreparedStatement pstmt = con.prepareStatement(sql);
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    java.util.Date d1 = rs.getDate(1);
    java.util.Date d2 = rs.getTime(2);
    java.util.Date d3 = rs.getTimestamp(3);
    System.out.println(d1);
    System.out.println(d2);
    System.out.println(d3);
  }

三.数据库连接池

1>. 数据库连接池

其实就是一个容器(集合)，存放数据库连接的容器。当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器

2>. 实现

①. 标准接口：DataSource javax.sql 包下的

方法：

获取连接：getConnection( )

归还连接：Connection.close( )。如果连接对象Connection是从连接池中获取的，那么调用Connection.close( )方法，则不会再关闭连接了。而是归还连接

②. 一般我们不去实现它，有数据库厂商来实现 dbcp

实现的方式

c3p0：数据库连接池技术

Druid：数据库连接池实现技术，由阿里巴巴提供的

JDBC（一）