JDBC概述及数据表操作

JDBC概述

首先引入数据持久化的概念——

• 数据持久化：将数据保存到可掉电式存储设备中以供之后使用，也就是内存中的数据保存到硬盘上，过程一般由各种关系数据库完成，也可以存在磁盘文件、XML文件中。
  

• 那么在java中，数据的存储技术可分为三类
  （1）JDBC直接访问数据库；（2）JDO技术；（3）第三方O/R工具。

   通过下图，可以看出JDBC是独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口，即一组API。
  

该接口有两个层次——（1）Java API，面向应用，抽象接口，供应用程序开发人员使用；（2）Java Driver API，面向数据库，供开发商开发数据库驱动程序使用。
也是面向接口编程思想(不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动)的一种体现。

JDBC程序编写步骤如下图所示

获取数据库连接

三个要素

1. Driver接口实现类
   上述提到的供开发商“面向接口，提供具体实现”中的接口就是java.sql.Driver，它是所有JDBC驱动程序需要实现的接口，并且在程序中不需要直接去访问实现了Driver接口的类，而是由驱动程序管理器类DriverManager去调用实现。

• Oracle的驱动——oracle.jdbc.driver.OracleDriver；MySQL的驱动——com.mysql.jdbc.Driver;

2. URL
   作用： 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。
   其组成有三部分——jdbc:子协议:子名称
   （1）协议——JDBC URL中的协议总是jdbc；
   （2）子协议——用于标识一个数据库驱动程序；
   （3）子名称——一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名。

具体如下图所示

MySQL的连接URL编写方式如下
jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值
jdbc:mysql://localhost:3306/test
jdbc:mysql://localhost:3306/test?user=root&password=123456

3. 用户名和密码
   user,password可以用“属性名=属性值”方式告诉数据库
   可以调用 DriverManager 类的 getConnection() 方法建立到数据库的连接

获取数据库连接的五个方式(迭代)

1.方式一

@Test
    public void testConnection1() {
        try {
            //1.提供java.sql.Driver接口实现类的对象
            Driver driver = null;
            driver = new com.mysql.jdbc.Driver();

            //2.提供url，指明具体操作的数据
            String url = "jdbc:mysql://localhost:3306/test";

            //3.提供Properties的对象，指明用户名和密码
            Properties info = new Properties();
            info.setProperty("user", "root");
            info.setProperty("password", "abc123");

            //4.调用driver的connect()，获取连接
            Connection conn = driver.connect(url, info);
            System.out.println(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

上述代码中显式出现了第三方数据库的API

2.方式二

@Test
    public void testConnection2() {
        try {
            //1.实例化Driver
            String className = "com.mysql.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", "abc123");

            //4.调用driver的connect()，获取连接
            Connection conn = driver.connect(url, info);
            System.out.println(conn);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

相较于方式一，这里使用反射实例化Driver，不在代码中体现第三方数据库的API。体现了面向接口编程思想

3.方式三

@Test
    public void testConnection3() {
        try {
            //1.数据库连接的4个基本要素：
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "abc123";
            String driverName = "com.mysql.jdbc.Driver";

            //2.实例化Driver
            Class clazz = Class.forName(driverName);
            Driver driver = (Driver) clazz.newInstance();
            //3.注册驱动
            DriverManager.registerDriver(driver);
            //4.获取连接
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

使用DriverManager实现数据库的连接。体会获取连接必要的4个基本要素。

4.方式四

@Test
    public void testConnection4() {
        try {
            //1.数据库连接的4个基本要素：
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "abc123";
            String driverName = "com.mysql.jdbc.Driver";

            //2.加载驱动 （①实例化Driver ②注册驱动）
            Class.forName(driverName);


            //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 conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

不必显式的注册驱动了。因为在DriverManager的源码中已经存在静态代码块，实现了驱动的注册。

5.方式五

@Test
    public  void testConnection5() throws Exception {
    	//1.加载配置文件
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2.读取配置信息
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

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

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

    }

使用配置文件的方式保存配置信息，在代码中加载配置文件
最终的连接方式的优点是
①实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要深入代码；
②如果修改了配置信息，省去重新编译的过程。

Statement和PreparedStatement操作数据表

使用PreparedStatement实现CRUD操作

• 数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接

数据库连接之后，需要执行SQL语句，根据不同的数据库调用方式主要分为三个接口

（1）Statement——用于执行静态 SQL 语句并返回它所生成结果的对象；
（2）PreparedStatement——SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句；是Statement的子接口
（3）CallableStatement——用于执行 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 就可以了。

PreparedStatement vs Statement
（1）代码的可读性和可维护性

（2）PreparedStatement 能最大可能提高性能
DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。语法检查，语义检查，翻译成二进制命令，缓存

（3）PreparedStatement 可以防止 SQL 注入

（4）Statement没法操作Blob类型变量

（5）Statement实现批量插入时，效率较低

在java和SQL中有对应数据类型转换表

对数据表的查询，需要返回一个对象。具体说明就是_ORM编程思想_：
（1）一个数据表对应一个java类；
（2）表中的一条记录对应java类的一个对象；
（3）表中的一个字段对应java类的一个属性；

注意：sql是需要结合列名和表的属性名来写。注意起别名。

// 通用的针对于不同表的查询:返回一个对象 (version 1.0)
	public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 1.获取数据库连接
			conn = JDBCUtils.getConnection();

			// 2.预编译sql语句，得到PreparedStatement对象
			ps = conn.prepareStatement(sql);

			// 3.填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			// 4.执行executeQuery(),得到结果集：ResultSet
			rs = ps.executeQuery();

			// 5.得到结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();

			// 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {// 遍历每一个列

				// 获取列值
				Object columnVal = rs.getObject(i + 1);
				// 获取列的别名:列的别名，使用类的属性名充当
				String columnLabel = rsmd.getColumnLabel(i + 1);
				// 6.2使用反射，给对象的相应属性赋值
				Field field = clazz.getDeclaredField(columnLabel);
				field.setAccessible(true);
				field.set(t, columnVal);

				}

				return t;

			}
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			// 7.关闭资源
			JDBCUtils.closeResource(conn, ps, rs);
		}

		return null;

	}

填充占位符图示说明

ResultSetMetaData不同于ResultSet，它主要是获取关于ResultSet对象中列的类型和属性信息的对象。

通过反射，创建指定类的对象，获取指定的属性并赋值

操作BLOB类型字段

BLOB是一个二进制大型对象，可存储大量数据的容器，能容纳不同大小的数据；插入BLOB类型数据必须使用PS，因为BLOB类型数据无法使用字符串拼接。

BLOB分为四种类型，如下图所示

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

写入操作的方法：setBlob(InuputStream is)

读取操作的方法：Blob blob = getBlob(int index); InputStream is = blob.getBinaryStream( );

批量插入

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。

包含的方法如下
（1）addBatch(String)——添加需要批量处理的SQL语句或是参数；
（2）executeBatch()——执行批量处理语句；
（3）clearBatch()——清空缓存的数据；

Mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。如下
？rewriteBatchedStatements=true 写在配置文件的url后面

数据库事务

一行或多行sql语句，要么它们全部执行，要么都不执行，我们把这一组逻辑操作单元称为事务；

当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务**回滚(rollback)**到最初状态。

事务的ACID属性
（1）原子性（Atomicity）——事务不可分割，要么都执行，要么都不执行；
（2）一致性（Consistency）
（3）隔离性（Isolation）
（4）持久性（Durability）

并发问题
（1）脏读
对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的
（2）不可重复读
对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了
（3）幻读
对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行

四种隔离级别

在MySql中设置隔离级别
每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。

操作
（1）查看当前的隔离级别
SELECT @@tx_isolation;
（2）设置当前 mySQL 连接的隔离级别
set transaction isolation level read committed;
（3）设置数据库系统的全局的隔离级别
set global transaction isolation level read committed;
（4）创建mysql数据库用户
create user tom identified by ‘abc123’;
（5）授予权限
#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
grant all privileges on . to tom@‘%’ identified by ‘abc123’;
#给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by ‘abc123’;

数据库连接池

为什么JDBC数据库连接池非常有必要呢？
因为使用开发基于数据库的web程序时，传统的模式是先在主程序中建立数据库连接，进行sql操作，之后断开数据库连接。

所以在这样的模式开发下会存在三个问题：
（1）数据库连接资源可重复利用性较差。若几百人或者几千人在线，频繁进行数据库操作会占用很多数据库资源和时间，严重会造成服务器崩溃；
（2）对于每一次数据库连接，使用完后都得断开。否则，若程序出现异常而未能关闭，将会导致数据库系统中得内存泄漏，最终将导致重启数据库。
（3）不能控制被创建的连接对象数。系统资源会毫无顾忌地分配出去，连接过多，会导致内存泄漏，服务器崩溃。

为解决上述问题，可采用数据库连接池技术。接下来介绍数据库连接池——

基本思想
为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕后再放回去。

作用
负责分配、管理和释放数据库连接；允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

工作原理

由上图，技术优点有以下四点：
（1）资源重用；因为数据库连接可以重用，避免了频繁创建，减少连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

（2）更快的系统反应速度；数据库连接已经初始化，可直接利用现有可用连接，避免数据库连接初始化和释放过程的时间开销，减少系统的响应时间。

（3）新的资源分配手段；对于多应用共享一个数据库系统，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源。

（4）统一的连接管理，避免数据库连接泄漏。在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免数据库连接操作中可能出现的资源泄漏。

DataSource接口实现
JDBC的数据库连接池是使用javax.sql.DataSource来表示，DataSource只是一个接口，该接口通常由服务器提供实现；DataSource通常被称为数据源，它包含连接池和连接池管理两部分，习惯上经常把DataSource称为连接池。

它的优点是可用来取代DriverManager来获取Connection，获取速度快，同时可大幅提高数据库访问速度。

同时也要注意数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。还有当数据库访问结束后，程序还是像以前一样关闭数据库连接（ conn.close(); ），但该操作后并没有关闭数据库的物理连接，仅仅释放了数据库连接，归还给数据库连接池。

多种开源的数据库连接池

• C3P0
  一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
• DBCP
  Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持
• Druid
  阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快

以上三种数据库连接池推荐使用配置文件的方式，获取数据库连接。

• Proxool
  sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
• BoneCP
  一个开源组织提供的数据库连接池，速度快

Apache-DBUtils实现CRUD操作

Apache-DBUtils

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

主要API的使用

• DbUtils
  介绍——提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的

• 主要方法
  public static void close(…) throws java.sql.SQLException
  DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet

    	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
    	
    	rollbackAndCloseQuietly(Connection)
    	
    	public static boolean loadDriver(java.lang.String driverClassName)
    		这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException
  

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

提供了两个构造器——默认的构造器；需要一个 javax.sql.DataSource 来作参数的构造器

• 主要方法
  （1）更新
  public int update(Connection conn, String sql, Object… params) throws SQLException
  用来执行一个更新（插入、更新或删除）操作

2）插入
public T insert(Connection conn,String sql,ResultSetHandler rsh, Object… params) throws SQLException

// 测试添加
@Test
public void testInsert() throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "insert into customers(name,email,birth)values(?,?,?)";
	int count = runner.update(conn, sql, "何成飞", "he@qq.com", "1992-09-08");
	System.out.println("添加了" + count + "条记录");
	JDBCUtils.closeResource(conn, null);
}
				// 测试删除
@Test
public void testDelete() throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "delete from customers where id < ?";
	int count = runner.update(conn, sql,3);
	System.out.println("删除了" + count + "条记录");
	JDBCUtils.closeResource(conn, null);
}

• ResultSetHandler接口及实现类
  ResultSetHandler 接口——用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式
  提供了一个单独的方法
  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
  查询单个值对象
  特殊值例如记录条数，生日最大等

资源的关闭
DBUtils.close( );
报异常
DBUtils.closeQuietly( );
不报异常

DAO及相关实现类

介绍
Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO
作用
为了实现功能的模块化，更有利于代码的维护和升级。
层次结构