1JDBC

JDBC

1 JDBC概述

简介

在Java中，数据库存取技术可分为如下几类：a JDBC直接访问数据库；b JDO(Java Data Object)技术；c 第三方O/R工具，如Hibernate,Mybatis等
JDBC是java访问数据库的基石，其他等只是更好的封装了JDBC
JDBC是一个独立于特定数据库管理系统，通用的SQL数据库存取和操作的公共接口（一组API）,定义了用来访问数据库的标准java类库（java.sql.javax.sql）使用这些类库可以以一种标准的方法，方便地访问数据库资源
JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题

2获取数据库连接三要素

建立连接(Connection)

可以调用DriverManager类的getConnection()方法建立数据库的连接
User,password可以用“属性名=属性值”方式告诉数据库
JDBC URL用于标识一个被注册的驱动程序，驱动程序管理器通过这个URL选择正确的驱动程序，从而建立数据库的连接

Driver接口（要素一）

java.sql.Driver接口是所有JDBC驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现
在程序中不需要直接去访问实现了Driver接口的类，而是由驱动程序管理器类（java.sql.DriverManager）去调用这些Driver实现
Oracle的驱动：oracle.jdbc.driver.OracleDriver
mySql的驱动：com.mysql.cj.jdbc.Driver

加载与注册JDBC驱动

方式一：加载JDBC驱动需要调用Class类的静态方法forName(),向其传递要加载的JDBC驱动的类名 Class.forName("com.mysql.cj.jdbc.Driver")
方式二：DriverManager类是驱动程序管理类，负责管理驱动程序 DriverManager.registerDriver("com.mysql.cj.jdbc.Driver);通常不用显示调用DriverManager类的registerDriver()方法来注册驱动程序类的实例，因为Driver接口的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用DriverManager.registerDriver()方法来注册自身的一个实例

JDBC URL（要素二）

JDBC URL的标准由三部分组成，各部分用冒号分隔
jdbc:子协议：子名称
协议：JDBC URL中的协议总是jdbc
子协议：子协议用于标识一个数据库驱动程序
子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名

几种常用数据库的JDBC URL

a. MySQL的连接URL编写方式:
jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值
jdbc:mysql://localhost:3306/atguigu
jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8（如果JDBC程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集
jdbc:mysql://localhost:3306/atguigu?user=root&password=123456
b. Oracle 9i的连接URL编写方式：
jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称
jdbc:oracle:thin:@localhost:1521:atguig
c. SQLServer的连接URL编写方式：
jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称
jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

用户名和密码(要素三)

user,password可以用“属性名=属性值”方式告诉数据库

数据库连接方式举例（五种，最后一种掌握！）

连接方式一

  //方式一
    @Test
    public void test1() throws SQLException {
        //1提供java.sql.Driver接口实现类的对象
        Driver driver=new com.mysql.cj.jdbc.Driver();

        //2提供url 指明要连接的数据库
        String url="jdbc:mysql://localhost:3306/test";
        
        //3提供Properties的对象，指明用户名和密码
        Properties info=new Properties();
        info.setProperty("user","root");
        info.setProperty("password","root");

        //4调用driver的connect() 获取连接
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
        //com.mysql.cj.jdbc.ConnectionImpl@732c2a62
    }
//上述代码中显式出现了第三方数据库的API

连接方式二

 //方式二
    //在如下程序中不出现第三方api
    @Test
    public void test2() throws Exception {

        //1获取driver实现类对象，用反射
        String className = "com.mysql.cj.jdbc.Driver";
        Class clazz = Class.forName(className);
        Driver driver = (Driver)clazz.newInstance();

        //2提供url 指明要连接的数据库
        String url="jdbc:mysql://localhost:3306/test";
        
        //3将用户名和密码封装在properties
        Properties info=new Properties();
        info.setProperty("user","root");
        info.setProperty("password","root");

        //4获取连接
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
        //com.mysql.cj.jdbc.ConnectionImpl@272ed83b
    }
//相较于方式一，这里使用反射实例化Driver，不在代码中体现第三方数据库的API。体现了面向接口编程思想。

连接方式三

 //方式三
    //使用drivermanager替换driver
    @Test
    public void test3() throws Exception{
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";
        String driverName = "com.mysql.cj.jdbc.Driver";
        
        //2获取driver实现类对象，用反射
        Class clazz = Class.forName(driverName);
        Driver driver = (Driver)clazz.newInstance();

        //3注册驱动
        DriverManager.registerDriver(driver);

        //4获取连接
 Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
        //com.mysql.cj.jdbc.ConnectionImpl@431cd9b2
    }
//使用DriverManager实现数据库的连接。体会获取连接必要的4个基本要素。

连接方式四

 //方式四
    //在三基础上优化 可以省略加载驱动，不用显示的注册驱动了
    @Test
    public void test4() throws Exception{

        //1获取另外三个信息
        String url="jdbc:mysql://localhost:3306/test";
        String user="root";
        String password="root";
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2获取driver实现类对象，用反射
        //mysql自己帮我们做了加载driver
        Class.forName(driverName);
        //这步mysql也可以省 但建议不要省
  
        //Driver driver = (Driver) clazz.newInstance();
        //3.注册驱动
        //DriverManager.registerDriver(driver);
        /*
        可以注释掉上述代码的原因，是因为在mysql的Driver类中声明有：
        static {
            try {
                DriverManager.registerDriver(new Driver());
            } catch (SQLException var1) {
                throw new RuntimeException("Can't register driver!");
            }
        }
        */
        
        //3获取连接
 Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
//说明：不必显式的注册驱动了。因为在DriverManager的源码中已经存在静态代码块，实现了驱动的注册。

连接方式五（最终版）

//配置文件声明在工程的src目录下：【jdbc.properties】
//使用配置文件的好处：1实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要深入代码; 2如果修改了配置信息，省去重新编译的过程
user=root
password=root
url=jdbc:mysql://localhost:3306/test?
driverClass=com.mysql.cj.jdbc.Driver

//方式五
    //最终版 将数据量需要的基本信息放在配置文件中
    //实现了数据与代理的分离，实现了解耦
    //如果需要修改配置文件信息，可以避免程序重新打包
    @Test
    public void test5() throws Exception{
        //1读取配置文件中的基本信息
		InputStream is = J1Connection.class.getClassLoader().getResourceAsStream("jdbc.properties");//这步可以看反射中第四点回忆
        Properties pros=new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2加载驱动
        Class.forName(driverClass);

        //3获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

3 使用PreparedStatement实现CRUD操作

3.1 操作和访问数据库

数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
CallableStatement：用于执行 SQL 存储过程

3.2 使用Statement操作数据表的弊端

通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句，并且返回执行结果。
但是使用Statement操作数据表存在弊端：问题1：存在拼串操作，繁琐；问题2：存在SQL注入问题
SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1') ，从而利用系统的 SQL 引擎完成恶意行为的做法。
对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。

3.3 PreparedStatement的使用

简介

可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
PreparedStatement接口是Statement的子接口，它表示一条预编译过的SQL语句
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值
优点：1 代码的可读性和可维护性 2能最大可能提高性能 3可以防止SQL注入

Java与SQL对应数据类型转换表

Java类型	SQL类型
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY,VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIMR
java.sql.Timestamp	TIMESTAMP

3.4使用PreparedStatement实现增，删，改操作

//Customer类
/*
ORM编程思想（Object relational mapping）
一个数据表对应一个java类
一个记录对应一个对象
一个字段对应一个属性
 */
public class Customer {
    private int id;
    private String name;
    private  String email;
    private Date birth;

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public Customer() {
    }

    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 String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

public class J2PreparedStatement {

    //向customers表中添加一条记录
    @Test
    public void test1() throws Exception{
        //之后改成try-catch
        //1读取配置文件中的基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros=new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2加载驱动
        Class.forName(driverClass);

        //3获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

        //4预编译sql语句，返回PreparedStatement实例
        String sql="insert into customers(name,email,birth) values(?,?,?)";//?占位符
        PreparedStatement ps = conn.prepareStatement(sql);
        
        //5填充占位符
        ps.setString(1,"哪吒");
        ps.setString(2,"nezha@gmail.com");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("1000-01-01");
        ps.setDate(3,new java.sql.Date(date.getTime()));

        //6执行操作
        ps.execute();

        //7资源关闭
        ps.close();
        conn.close();
    }

//连接和关闭资源操作可以封装在jdbcutils中，形成静态方法

//连接
public static Connection getConnection() throws Exception{
        //1读取配置文件中的基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros=new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2加载驱动
        Class.forName(driverClass);

        //3获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

//关闭资源
    public static void closeResource(Connection conn, PreparedStatement ps) throws SQLException {
        ps.close();
        conn.close();
    }

//修改关闭操作（后面查询要用到）
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {
        ps.close();
        conn.close();
        rs.close();
    }

    public static void closeResource( PreparedStatement ps) throws SQLException {
        ps.close();
    }

//修改customer一条记录
    @Test
    public void test2() throws Exception{
        //1获取数据库连接
        Connection conn = jdbcutils.getConnection();

        //2预编译sql语句，返回preparedstatement实例
        String sql="update customers set name= ? where id=? ";
        PreparedStatement ps = conn.prepareStatement(sql);

        //3填充站位符
        ps.setObject(1,"莫扎特");
        ps.setObject(2,18);
        
        //4执行
        ps.execute();

        //5资源关闭
        jdbcutils.closeResource(conn,ps);
    }

//通用的增删改操作
    //sql的占位符应该和可变形参的个数一样
    public void update(String sql,Object...args) throws Exception{
        //1获取数据库连接
        Connection conn = jdbcutils.getConnection();

        //2预编译sql语句，返回preparedstatement实例

        PreparedStatement ps = conn.prepareStatement(sql);

        //3填充站位符
        for (int i=0;i<args.length;i++){
            //注意从1开始
            ps.setObject(i+1,args[i]);
        }
        
        //4执行
        ps.execute();

        //5资源关闭
        jdbcutils.closeResource(conn,ps);
    }

//测试
    @Test
    public void test4() throws Exception {
        String sql="delete from customers where id=?";
        update(sql,3);
    }

3.5使用PreparedStatement实现查询操作

ResultSet

查询需要调用PreparedStatement 的 executeQuery() 方法，查询结果是一个ResultSet 对象
ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现
ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面
ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效，该方法返回 true，且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体
当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值。例如: getInt(1), getString("name")
注意：Java与数据库交互涉及到的相关Java API的索引都从1开始

ResultSetMetaData

可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
ResultSetMetaData meta = rs.getMetaData();
getColumnName(int column)：获取指定列的名称
getColumnLabel(int column)：获取指定列的别名
getColumnCount()：返回当前 ResultSet 对象中的列数
getColumnTypeName(int column)：检索指定列的数据库特定的类型名称
getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位
isNullable(int column)：指示指定列中的值是否可以为 nul
isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的

资源释放

释放ResultSet, Statement,Connection
数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
可以在finally中关闭，保证及时其他代码出现异常，资源也一定能被关闭

 //查询操作
 @Test
    public void test1() throws Exception{
        //1获取数据库连接
        Connection conn = jdbcutils.getConnection();

        //2预编译sql语句，返回preparedstatement实例
        String sql="select id,name,email,birth from customers where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,1);
       
        //3执行并返回结果集
        ResultSet resultSet = ps.executeQuery();

        //4处理结果集
        //next判断结果集的下一条是否有数据，
        //如果有数据 返回true并指针下移 否则指针不移
        if (resultSet.next()){
            
            //获取当前数据的各个字段值
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.getDate(4);

            //方式一 直接输出
            System.out.println("id = "+ id+"name = "+ name +"email = "+ email +"birth = "+ birth);

            //方式二 封装在数组中
            Object[] data=new Object[]{id,name,email,birth};

            //方式三 将数据封装为一个对象（推荐）
            Customer customer=new Customer(id,name,email,birth);
            System.out.println(customer);
        }
        
        //5关闭资源
    	jdbcutils.closeResource(conn,ps,resultSet);
    }

//针对于customers一次查询的共性操作
    public Customer queryFroCustomers(String sql,Object...args) throws Exception{
        
        //1获取数据库连接
        Connection conn = jdbcutils.getConnection();

        //2预编译sql语句，返回preparedstatement实例
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }

        //3执行并返回结果集
        ResultSet rs = ps.executeQuery();

        //3+获取结果集的元数据 ResultSetMetaData
        ResultSetMetaData rsmd = rs.getMetaData();
        
        //通过ResultSetMetaData获取列数
        int column=rsmd.getColumnCount();

        if (rs.next()){
            Customer cust=new Customer();
            
            //处理结果集一行中的每一列
           for (int i=0;i<column;i++){
               Object columnValue = rs.getObject(i + 1);//注意这里加1
               
               //获取每个列的列名(不推荐，下面一个案例有原因)
               String columnName = rsmd.getColumnName(i + 1);

               //给cust对象指定的columnName属性赋值为value
               //反射 最难
               Field field = Customer.class.getDeclaredField(columnName);
               field.setAccessible(true);
               field.set(cust,columnValue);
           }
            return cust;
        }
        
        //5关闭资源
        jdbcutils.closeResource(conn,ps,rs);
        return null;
    }

//测试
@Test
    public void test3() throws Exception{
        String sql="select id,name,birth,email from customers where id=?";
        Customer customer=queryFroCustomers(sql,13);
        System.out.println(customer);
    }

//补充一案例:Order类 其中类的属性名和数据库里面的名字不一样
public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public Order() {
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}

	//针对order表的通用查询操作
    public Order orderForQuery(String sql,Object...args) throws Exception{
        //1获取数据库连接
        Connection conn = jdbcutils.getConnection();

        //2预编译sql语句，返回preparedstatement实例
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }

        //3执行并返回结果集
        ResultSet rs = ps.executeQuery();

        //3+获取结果集的元数据 ResultSetMetaData
        ResultSetMetaData rsmd = rs.getMetaData();
        
        //通过ResultSetMetaData获取列数
        int columnCount=rsmd.getColumnCount();

        if (rs.next()){
            Order order=new Order();
            
            //处理结果集一行中的每一列
            for (int i=0;i<columnCount;i++){
                //获取每个列的列值
                Object columnValue = rs.getObject(i + 1);
                //获取每个列的列名(不推荐使用)
                //因为名字不一样 所以获取列的别名getcolumnlable
                //别名可以在sql语句中指定
                String columnLabel = rsmd.getColumnLabel(i + 1);

                //给对象指定的columnName属性赋值为value
                //反射 最难
                Field field = Order.class.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(order,columnValue);
            }
            return order;
        }
        //5关闭资源
        jdbcutils.closeResource(conn,ps,rs);
        return null;
    }

    @Test
    public void test4() throws Exception{
        //注意表名和关键字一样要加``
        String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id=?";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);
    }

3.6练习：：创立数据库表 examstudent，表结构如下：并向examstudent中添加一条记录

public class J5Test {

    //问题1向examstudent中添加一条记录
    public int update(String sql,Object...args) throws Exception{

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = jdbcutils.getConnection();
            ps = conn.prepareStatement(sql);
            
            for (int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

        //execute如果执行查询操作有结果返回true;其他操作返回false
        //executeUpdate返回操作成功数目
            return  ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jdbcutils.closeResource(conn,ps);
        }
        return 0;
    }

    @Test
    public void test1() throws Exception{
        System.out.println("开始");
        Scanner scanner=new Scanner(System.in);
        System.out.print("四级/六级: ");
        int type = scanner.nextInt();
        System.out.print("身份证号：");
        String IDCard = scanner.next();
        System.out.print("准考证号：");
        String examCard = scanner.next();
        System.out.print("学生姓名");
        String studentName = scanner.next();
        System.out.print("所在城市：");
        String location = scanner.next();
        System.out.print("考试成绩: ");
        int grade = scanner.nextInt();

        String sql="insert into examstudent(type,IDCard,examCard,studentName,location,grade) values(?,?,?,?,?,?)";
        int insetCount = update(sql, type, IDCard, examCard, studentName, location, grade);
        if (insetCount>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
    }
}

4操作BLOB类型字段

4.1简介

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据
插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的
MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

类型	大小（字节）
TinyBlob	最大 255
Blob	最大 65K
MediumBlob	最大 16M
LongBlob	最大 4G

实际使用中根据需要存入的数据大小定义不同的BLOB类型
需要注意的是：如果存储的文件过大，数据库的性能会下降
如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务

4.2向数据表中插入blob字段

	//	向数据表中插入blob字段
	@Test
    public void test1() throws Exception{
        Connection conn = jdbcutils.getConnection();

        String sql="insert into customers(name,email,birth,photo) values(?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,"嗷嗷");
        ps.setObject(2,"fan@qq.com");
        ps.setObject(3,"1999-06-28");
        FileInputStream is=new FileInputStream(new File("1.jpg"));
        ps.setBlob(4,is);

        ps.execute();

        jdbcutils.closeResource(conn,ps);
    }

4.3查询数据表中的blob字段

  //查询数据表中的blob字段
    @Test
    public void test2() throws Exception{

        //创建流
        InputStream is =null;
        FileOutputStream fos=null;

        Connection conn = jdbcutils.getConnection();

        String sql="select id,name,email,birth,photo from customers where  id=?";
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,20);
        ResultSet rs = ps.executeQuery();

        if (rs.next()){

            int id = rs.getInt(1);//和getInt("id")一样
            String name = rs.getString(2);
            String email = rs.getString(3);
            Date birth = rs.getDate(4);

            Customer cust = new Customer(id, name, email, birth);
            System.out.println(cust);

            Blob photo = rs.getBlob(5);
            //将blob下载下来以文件方式保存在本地
            is = photo.getBinaryStream();
            fos=new FileOutputStream("2.jpg");
            byte[] buffer=new byte[1024];
            int len;
            while ((len=is.read(buffer))!=-1){
                fos.write(buffer,0,len);
            }

        }

        jdbcutils.closeResource(conn,ps,rs);
        is.close();
        fos.close();
    }

5 批量操作

简介

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
update,delete本身就具有批量操作的效果,此时的批量操作主要指的是批量插入

JDBC批量处理语句包括下面三个方法

addBatch(String)：添加需要批量处理的SQL语句或是参数
executeBatch()：执行批量处理的语句
clearBatch()：清空缓存的数据

通常我们会遇到两种批量执行SQL语句的情况

多条SQL语句的批量处理
一个SQL语句的批量传参

实现层次一：使用Statement(不推荐)

实现层次二：使用PreparedStatement

  //批量插入方式二使用preparedstatement
    @Test
    public void test2() throws Exception{

        long start = System.currentTimeMillis();
        Connection conn = jdbcutils.getConnection();

        String sql="insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i=1;i<=1000;i++){
            ps.setObject(1,"name"+i);
            ps.execute();
        }
        jdbcutils.closeResource(conn,ps);

        long end = System.currentTimeMillis();
        System.out.println(end-start);//148448
    }

实现层次三：addBatch(),executeBatch(),cleatBatch()

  //批量插入方式三优化
  //1addBatch(),executeBatch(),cleatBatch()

    @Test
    public void test3() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = jdbcutils.getConnection();

        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 1; i <= 1000; i++) {
            ps.setObject(1, "name" + i);
            //1攒sql
            ps.addBatch();
            if (i % 200 == 0) {
                //2执行
                ps.executeBatch();
                //3清空
                ps.clearBatch();
            }
        }

        jdbcutils.closeResource(conn, ps);
        long end = System.currentTimeMillis();
        System.out.println(end - start);//141223
    }

实现层次四：设置不自动提交

	//批量插入方式四优化
    //设置不自动提交
    @Test
    public void test4() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = jdbcutils.getConnection();

        //设置不允许自动提交数据
        conn.setAutoCommit(false);

        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 1; i <= 1000; i++) {
            ps.setObject(1, "name" + i);
            //1攒sql
            ps.addBatch();
            if (i % 50 == 0) {
                //2执行
                ps.executeBatch();
                //3情空
                ps.clearBatch();
            }
        }
        //提交数据
        conn.commit();
        jdbcutils.closeResource(conn, ps);
        long end = System.currentTimeMillis();
        System.out.println(end - start);//2568
    }

6 数据库连接池技术

6.1传统数据库连接缺点

数据库连接资源并没有得到很好的重复利用
对于每一次数据库连接，使用完后都得断开
这中开发不能控制被创建的连接对象数

6.2简介

为解决传统开发中的数据库连接问题，可以采用数据库连接池技术
数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

6.3数据库连接池技术的优点

资源重用
更快的系统反应速度
新的资源分配手段
统一的连接管理，避免数据库连接泄露

6.4多种开源的数据库连接池

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
BoneCP 是一个开源组织提供的数据库连接池，速度快
Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快
DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池，DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
注意：
数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

6.5C3P0数据库连接池

连接方式一

     //使用C3P0数据库连接池的方式，获取数据库的连接：不推荐

    @Test
    public void test1() throws Exception{
        //获取c3p0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); 
        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?" );
        cpds.setUser("root");
        cpds.setPassword("root");

        //设置相关参数对护具看连接池进行管理
        //设置初始数据库连接池中的连接数
        cpds.setInitialPoolSize(10);

        Connection conn=cpds.getConnection();
        System.out.println(conn);
        DataSources.destroy(cpds);
    }

连接方式二

//使用C3P0数据库连接池的配置文件方式，获取数据库的连接：推荐
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2() throws SQLException{
	Connection conn = cpds.getConnection();
	return conn;
}

//src下的配置文件为：【c3p0-config.xml】
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<named-config name="helloc3p0">
		<!-- 获取连接的4个基本信息 -->
		<property name="user">root</property>
		<property name="password">abc123</property>
		<property name="jdbcUrl">jdbc:mysql:///test</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		
		<!-- 涉及到数据库连接池的管理的相关属性的设置 -->
		<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
		<property name="acquireIncrement">5</property>
		<!-- 初始化数据库连接池时连接的数量 -->
		<property name="initialPoolSize">5</property>
		<!-- 数据库连接池中的最小的数据库连接数 -->
		<property name="minPoolSize">5</property>
		<!-- 数据库连接池中的最大的数据库连接数 -->
		<property name="maxPoolSize">10</property>
		<!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
		<property name="maxStatements">20</property>
		<!-- 每个连接同时可以使用的 Statement 对象的个数 -->
		<property name="maxStatementsPerConnection">5</property>

	</named-config>
</c3p0-config>

6.6Druid（德鲁伊）数据库连接池

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

    //方式三druid（主要掌握） 直接使用配置文件
    //省略第一种方法

    @Test
    public void test3() throws Exception{

        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        //如果是main方法可以是
        //Properties pro = new Properties();		 pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
        
        pros.load(is);

        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        System.out.println(conn);
    }

//druid.properties
url=jdbc:mysql://localhost:3306/test?
username=root
password=root
driverClassName=com.mysql.cj.jdbc.Driver
    
//下面是可选的
initialSize=10
maxActive=20
maxWait=1000
filters=wall

//在jdbcutils封装成静态方法 ！！！常用
 public static Connection getConnection2() throws Exception{

        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);

        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        return conn;
    }

详细配置参数

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url		连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/使用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，是组合关系，并非替换关系

7 Apache-DBUtils实现CRUD操作

7.1简介

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能
API介绍
org.apache.commons.dbutils.QueryRunner
org.apache.commons.dbutils.ResultSetHandler
工具类：org.apache.commons.dbutils.DbUtils

7.2主要API的使用-DbUtils

DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下
public static void close(…) throws java.sql.SQLException：　DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSe
public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception
public static void commitAndClose(Connection conn)throws SQLException：用来提交连接的事务，然后关闭连接
public static void commitAndCloseQuietly(Connection conn)：用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常
public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断
public static void rollbackAndClose(Connection conn)throws SQLException
public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException

7.3主要API的使用-QueryRunner

该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量

QueryRunner类提供了两个构造器：
- 默认的构造器
- 需要一个 javax.sql.DataSource 来作参数的构造器
QueryRunner类的主要方法：
- 更新
  - public int update(Connection conn, String sql, Object... params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
- 插入
  - public T insert(Connection conn,String sql,ResultSetHandler rsh, Object... params) throws SQLException：只支持INSERT语句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 返回值: An object generated by the handler.即自动生成的键值
- 批处理
  - public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT, UPDATE, or DELETE语句
  - public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException：只支持INSERT语句
- 查询
  - public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。

7.3主要API的使用-ResultSetHandler接口及实现类

该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式
ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)
接口的主要实现类：
ArrayHandler：把结果集中的第一行数据转成对象数组
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中
BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里
ColumnListHandler：将结果集中某一列的数据存放到List中
KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
ScalarHandler：查询单个值对象

7.4测试

//插入操作
    @Test
    public void test1() throws Exception {
        QueryRunner runner = new QueryRunner();

        Connection conn = jdbcutils.getConnection2();

        String sql="insert into customers(name,email,birth) values(?,?,?)";

        int insertCount = runner.update(conn, sql, "蔡徐坤", "caixu@qq.com", "1999-06-22");
        System.out.println("添加了"+insertCount+"记录");

        conn.close();
    }

 //查询操作
 //BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
    @Test
    public void test2() throws Exception{
        QueryRunner runner = new QueryRunner();
        Connection conn = jdbcutils.getConnection2();

        String sql="select id,name,email,birth from customers where id=?";

        BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);

        Customer customer = runner.query(conn, sql, handler, 18);

        System.out.println(customer);

        conn.close();
    }

   //查询操作2 返回多条记录
   //BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
 
    @Test
    public void test3() throws Exception{
        QueryRunner runner = new QueryRunner();
        Connection conn = jdbcutils.getConnection2();

        String sql="select id,name,email,birth from customers";

        BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);

        List<Customer> list = runner.query(conn, sql, handler);

        list.forEach(System.out::println);
        
        //另外一种输出方式
        //        for(Customer c:list){
		//            System.out.println(c);
		//        }
        
        conn.close();
    }

 //查询操作3
 //MapHandler:是ResultSetHandler接口的实现类，对应表中一条记录 作为键和值
    @Test
    public void test4() throws Exception{
        QueryRunner runner = new QueryRunner();
        Connection conn = jdbcutils.getConnection2();

        String sql="select id,name,email,birth from customers where id=?";

        MapHandler handler=new MapHandler();

        Map<String, Object> map = runner.query(conn, sql, handler, 18);

        System.out.println(map);

        conn.close();
    }

//查询操作4--特殊查询（类似于最大的，最小的，平均的，总和，个数相关的数据）
//ScalarHandler：查询单个值对象
    @Test
    public void test5() throws Exception{
        QueryRunner runner = new QueryRunner();
        Connection conn = jdbcutils.getConnection2();

        String sql="select count(*) from customers";

        ScalarHandler handler = new ScalarHandler();

        Long count =(Long) runner.query(conn, sql, handler);

        System.out.println(count);

        conn.close();
    }

//自定义resultsethandler实现类
//重写ResultSetHandler接口提供的单独方法：Object handle (java.sql.ResultSet .rs)
    @Test
    public void test6() throws Exception{
        QueryRunner runner = new QueryRunner();
        Connection conn = jdbcutils.getConnection2();

        String sql="select id,name,email,birth from customers where id=?";

        ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
            @Override
            public Customer handle(ResultSet resultSet) throws SQLException {

                System.out.println("自定义");
                return null;
            }
        };


        Customer customer = runner.query(conn, sql, handler, 13);

        System.out.println(customer);

        conn.close();
    }

   //关闭
    //使用dbutils.jar中提供的dbutils工具类实现资源关闭
    public static void closeResource1(Connection conn, Statement ps,ResultSet rs){
    //方式一
        //        try {
//            DbUtils.close(conn);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        try {
//            DbUtils.close(ps);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        try {
//            DbUtils.close(rs);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }

        //方式二
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }

posted @ 2022-05-16 15:05 fao99 阅读(41) 评论(0) 收藏举报

刷新页面返回顶部

fao99

1JDBC

JDBC

公告