JDBC介绍及使用

定义

JDBC(Java Database Connectivity)，它是代表一组独立于数据库管理系统的API，声明在java.sql与javax.sql包中，是SUN(现在Oracle)提供的一组接口规范。由各个数据库厂商来提供实现类，这些实现类的集合构成了数据库驱动jar。

使用步骤

1.注册驱动

三部曲：
（1）将DBMS数据库管理软件的驱动jar拷贝到项目的libs目录中
例如：mysql-connector-java-5.1.36-bin.jar
（2）把驱动jar添加到项目的build path中
（3）将驱动类加载到内存中
Class.forName(“com.mysql.jdbc.Driver”);

2.获取Connection连接对象

Connection conn = DriverManager.getConnection(url,username,password);
mysql的url：jdbc:mysql://localhost:3306/数据库名?参数名=参数值

3.执行sql并处理结果

（1）编写sql
（2）创建Statement或PreparedStatement对象
（3）执行sql
增删改：调用executeUpate方法
查询：调用executeQuery方法
（4）处理结果
增删改：返回的是整数值
查询：返回ResultSet结果，需要使用next()和getXxx()结合进行遍历

4.释放连接

相关API

1、DriverManager：驱动管理类
2、Connection：代表数据库连接
3、Statement和PreparedStatement：用来执行sql
​ 执行增、删、改：int executeUpate()
​ 执行查询：ResultSet executeQuery()
4、如何遍历ResultSet ？
​ （1）boolean next()：判断是否还有下一行
​ （2）getString(字段名或序号),getInt(字段名或序号),getObject(字段名或序号)

package com.hhy.jdbc;

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        query();
        update();
    }

    private static void update() throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建驱动类对象
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.创建驱动类对象
        //mysql的url
        String url = "jdbc:mysql://localhost:3306/test";
        //账户名
        String user = "root";
        //密码
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);

        //3.执行sal
        //向stu表中插入一行数据
        String insert = "insert into stu values('1','161')";
        Statement st = conn.createStatement();
        System.out.println(st.executeUpdate(insert) > 0 ? "成功" : "失败");

        //4.关闭资源
        st.close();
        conn.close();
    }

    private static void query() throws SQLException, ClassNotFoundException {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.创建驱动类对象
        //mysql的url
        String url = "jdbc:mysql://localhost:3306/test";
        //账户名
        String user = "root";
        //密码
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);

        //执行sql
        //查询stu表数据
        String sql = "SELECT * FROM STU";
        Statement st = conn.createStatement();

        //ResultSet可以看做InputStream
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            //获取第一列值
            Object did = rs.getObject(1);
            //获取第二列值
            Object dphone_number = rs.getObject(2);
            System.out.println(did + "\t" + dphone_number + "\t");
        }

        //4.关闭资源
        rs.close();
        st.close();
        conn.close();
    }
}

使用PreparedStatement处理增删改查（CRUD）

PreparedStatement来解决Statement的问题

statement的问题：
（1）sql拼接

		String sql = "insert into t_employee(ename,tel,gender,salary) values('" + ename + "','" + tel + "','" + gender + "'," + salary +")";
		Statement st = conn.createStatement();
		int len = st.executeUpdate(sql);

（2）sql注入

		String sql = "SELECT * FROM t_employee where ename='" + ename + "'";
		//如果我此时从键盘输入ename值的时候，输入：张三' or '1'= '1
		//结果会把所有数据都查询出来
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);

PreparedStatement解决问题：
（1）避免sql拼接

		String sql = "insert into t_employee(ename,tel,gender,salary) values(?,?,?,?)";
		PreparedStatement pst = conn.prepareStatement(sql);//这里要传带？的sql，然后mysql端就会对这个sql进行预编译
		//设置？的具体值
		/*pst.setString(1, ename);
		pst.setString(2, tel);
		pst.setString(3, gender);
		pst.setDouble(4, salary);*/
		pst.setObject(1, ename);
		pst.setObject(2, tel);
		pst.setObject(3, gender);
		pst.setObject(4, salary);	
		int len = pst.executeUpdate();//此处不能传sql
		System.out.println(len);

（2）不会有sql注入

		String sql = "SELECT * FROM t_employee where ename=?";
		//即使输入'张三' or '1'= '1'也没问题
		PreparedStatement pst = conn.prepareStatement(sql);
		//中间加入设置？的值
		pst.setObject(1, ename);
		ResultSet rs = pst.executeQuery();

批处理

批处理：
批量处理sql
例如：
（1）订单明细表的多条记录的添加
（2）批量添加模拟数据

不用批处理，和用批处理有什么不同？
批处理的效率很多
如何进行批处理操作？
（1）在url中要加一个参数
rewriteBatchedStatements=true
那么我们的url就变成了 jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
这里的?，表示?后面是客户端给服务器端传的参数，多个参数直接使用&分割
（2）调用方法不同
pst.addBatch();
int[] all = pst.executeBatch();

注意：如果批量添加时，insert使用values,不要使用value

package com.hhy.demo01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBC_BATCH {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        long startTime = System.currentTimeMillis();
        add();
        long endTime = System.currentTimeMillis();
        System.out.println(endTime - startTime);

    }
    private static void add() throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接
        String url = "jdbc:mysql://localhost:3306/jdbctest?rewriteBatchedStatements=true";
        String user = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url,user,password);
        //执行
        String sql = "insert into student values (null,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 1000; i++) {
            ps.setString(1,"模拟姓名" + i);
            ps.setInt(2,(int)(Math.random() * 10 + 20));
            ps.addBatch();
        }
        ps.executeBatch();
        
        //关闭资源
        ps.close();
        conn.close();
    }
}

事务

mysql默认每一个连接是自动提交事务的。
那么当我们在JDBC这段，如果有多条语句想要组成一个事务一起执行
(1)在执行之前，设置手动提交事务
Connection的对象.setAutoCommit(false)
成功：
Connection的对象.commit();
失败：
Connection的对象.rollback();

补充说明：
在关闭Connection的对象之前，把连接对象设置回自动提交
Connection的对象.setAutoCommit(true)

因为我们现在的连接是建立新的连接，那么如果没有还原为自动提交，没有影响。
但是我们后面实际开发中，每次获取的连接，不一定是新的连接，而是从连接池中获取的旧的连接，而且你关闭也不是真关闭，
而是还给连接池，供别人接着用。以防别人拿到后，以为是自动提交的，而没有commit，最终数据没有成功。

package com.hhy.demo01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBC_Transaction {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接
        String url = "jdbc:mysql://localhost:3306/jdbctest";
        String user = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url,user,password);
        //设置手动提交事务
        conn.setAutoCommit(false);
        //执行
        PreparedStatement ps = null;
        
        try {
            String sql1 = "update balance set balance = 500 where id = 1";
            String sql2 = "update balance set balance = 1500 where id = 2";
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
//            System.out.println(1 / 0);
            ps = conn.prepareStatement(sql2);
            ps.executeUpdate();
            conn.commit();
        } catch(Exception e){
            System.out.println("执行失败");
            conn.rollback();
        }finally {
            //将提交改为自动
            conn.setAutoCommit(true);
            ps.close();
            conn.close();
        }
    }
}

数据库连接池

1、什么是数据库连池
连接对象的缓冲区。负责申请，分配管理，释放连接的操作。
2、为什么要使用数据库连接池
不使用数据库连接池，每次都通过DriverManager获取新连接，用完直接抛弃断开，连接的利用率太低，太浪费。
对于数据库服务器来说，压力太大了。我们数据库服务器和Java程序对连接数也无法控制，很容易导致数据库服务器崩溃。
我们就希望能管理连接。
我们可以建立一个连接池，这个池中可以容纳一定数量的连接对象，一开始，我们可以先替用户先创建好一些连接对象，
等用户要拿连接对象时，就直接从池中拿，不用新建了，这样也可以节省时间。然后用户用完后，放回去，别人可以接着用。
可以提高连接的使用率。当池中的现有的连接都用完了，那么连接池可以向服务器申请新的连接放到池中。
直到池中的连接达到“最大连接数”，就不能在申请新的连接了，如果没有拿到连接的用户只能等待。
3、市面上有很多现成的数据库连接池技术：

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口（通常被称为数据源），该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池，速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持
  • C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以
  • Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
  • BoneCP 是一个开源组织提供的数据库连接池，速度快
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池
    4、阿里的德鲁伊连接池技术
    （1）加入jar包
    例如：druid-1.1.10.jar
    （2）代码步骤
    第一步：建立一个数据库连接池
    第二步：设置连接池的参数
    第三步：获取连接

package com.hhy.demo01;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

import java.sql.SQLException;

public class JDBC_Druid {
    public static void main(String[] args) throws SQLException {
        //注册驱动
        DruidDataSource dds = new DruidDataSource();
        dds.setDriverClassName("com.mysql.jdbc.Driver");
        //设置一些参数
        dds.setUrl("jdbc:mysql://localhost:3306/jdbctest");
        dds.setUsername("root");
        dds.setPassword("root");
        //设置最大的连接数量
        dds.setMaxActive(10);
        //设置等待时间
        dds.setMaxWait(5000);

        try {
            //测试一个连接的上限
            for (int i = 0 ; i < 30 ; i++) {
                //通过dds获取连接对象
                DruidPooledConnection conn = dds.getConnection();
                System.out.println("第" + (i + 1) + conn);
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println("您的网络不通常");
        }
    }
}

参数介绍

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
jdbcUrl		连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。
driverClassName		根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	8	最大连接池数量
maxIdle	8	已经不再使用，配置了也没效果
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun		不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter		根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

封装JDBCTools

配置文件：src/jdbc.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbctest
username=root
password=root
maxActive=10
maxWait=5000

JDBCTools工具类：

package com.hhy.jdbcutils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
1.获取连接对象
2.关闭所有能能关闭的对象
 */
public class JDBCUtils {
    //2.定义数据库连接池对象 (多态)
    private static DataSource ds;
    //1.私有化构造器
    private JDBCUtils(){};
    //静态代码块
    static{
        Properties properties = new Properties();
        try {
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            System.out.println("初始化失败");
            e.printStackTrace();
        }
    }
    //3.提供一个公共获取连接对象的方法
    public static Connection getconnection(){
        Connection conn = null;
        try {
            conn = ds.getConnection();
        } catch (SQLException e) {
            System.out.println("获取连接失败");
        }
        return conn;
    }

    //4.关闭的方法
    public static void close(Connection conn){
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("连接对象关闭失败");
        }
    }

    //4.关闭statement和conn
    public static void close(Statement state,Connection conn){
        try {
            state.close();
        } catch (SQLException e) {
            System.out.println("state对象关闭失败");
        } finally {
            close(conn);
        }
    }

    //4.关闭resultset statement conn
    public static void close(ResultSet resulSet,Statement state, Connection conn){
        try {
            resulSet.close();
        } catch (SQLException e) {
            System.out.println("state对象关闭失败");
        } finally {
            close(state,conn);
        }
    }

}

封装BasicDAOImpl

学生类的设计

package com.hhy.jdbcbeandemoend;

public class Student {
    private int id;
    private String name;
    private int age;

    public Student() {
    }

    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Student(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }

}

完成学生类增删改查接口的设计

package com.hhy.jdbcbeandemoend;

import java.util.List;

public interface StudentDao {
    //提供添加学生对象的方法
    void addStudent(Student s);
    //提供删除学生对象的方法
    void deleteStudent(int sid);
    //提供修改学生对象的方法
    void updateStudent(Student s);
    //提供查询所有学生的方法
    List<Student> getAllStudent();
    //提供通过指定ID查询学生的方法
    Student getStudent(int sid);

}

完成学生类增删改查接口实现类的设计

package com.hhy.jdbcbeandemoend;

import com.atguigu.jdbcutils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImp implements StudentDao {
    //添加学生对象
    @Override
    public void addStudent(Student s) {
        String sql = "insert into student values (null,?,?)";
        update(sql,s.getName(),s.getAge());
    }

    //删除学生对象
    @Override
    public void deleteStudent(int sid) {
        String sql = "delete from student where id = ?";
        update(sql,sid);
    }

    //修改学生对象
    @Override
    public void updateStudent(Student s) {
        String sql = "update student set name = ?, age = ? where id = ? ";
        update(sql,s.getName(),s.getAge(),s.getId());
    }

    //查询所有学生对象
    @Override
    public List<Student> getAllStudent() {
        ArrayList<Student> list = new ArrayList<>();
        Connection conn = JDBCUtils.getconnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from student";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Student(rs.getInt("id"),rs.getString("name"),rs.getInt("age")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,ps,conn);
        }
        return list;
    }

    //查询单个学生对象
    @Override
    public Student getStudent(int sid) {
        ArrayList<Student> list = new ArrayList<>();
        Connection conn = JDBCUtils.getconnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            String sql = "select * from student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sid);
            rs = ps.executeQuery();
            while (rs.next()) {
                student = new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,ps,conn);
        }
        return student;
    }

    //update
    private void update(String sql,Object... arr){
        Connection conn = JDBCUtils.getconnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < arr.length; i++) {
                ps.setObject(i + 1, arr[i]);
            }
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(ps,conn);
        }
    }
}

测试代码

package com.atguigu.jdbcbeandemoend;

public class JDBCTest {
    public static void main(String[] args) {
        StudentDaoImp sdi = new StudentDaoImp();
        Student s1 = new Student("好宇", 19);
        //添加
//        sdi.addStudent(s1);
        //删除
//        sdi.deleteStudent(1);
        Student s2 = new Student(3, "哼哼哼", 18);
        //修改
//        sdi.updateStudent(s2);
        //遍历所有
//        List<Student> list = sdi.getAllStudent();
//        for (Student student : list) {
//            System.out.println(student);
//        }
        //按id遍历一个
        Student student = sdi.getStudent(5);
        System.out.println(student);
    }
}