数据库JDBC知识概括

GeorgeLin
217 阅读16分钟

@TOC

JDBC简介

JDBC概述：

  • JDBC是sun公司提供的一套独立于数据库的通用SQL数据库存取h额操作的公共接口和规范，用来实现与数据库连接的。因此各大数据库厂商，需要针对这套接口，提供不同的实现类，即不同的的数据库驱动。

JDBC程序编写步骤： 在这里插入图片描述 获取数据库连接：

  • 获取数据库连接，我们需要四个基本信息： ①数据库连接地址 Url ②连接用户名 User ③连接密码 Password ④数据库驱动 DriverName
  • 一般我们把这四个基本配置信息写在properties文件中。而不是硬编码写在代码中。这样： ①减少代码与配置信息的耦合度 ②当需要重新打包时直接修改配置文件即可，而不用重新编译打包

使用PreparedStatement实现CRUD操作

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

  • 存在拼串操作，繁琐
  • 存在SQL注入问题

PreparedStatement介绍：

  • 可以通过调用 Connection 对象的 preparedStatement(String sql)方法获取 PreparedStatement 对象
  • PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
  • PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

PreparedStatement vs Statement对比：

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

JDBC详解

资源释放：

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

java 中#{}与${}的区别：

  • #{}: 表示一个占位符号,实现向PreparedStatement占位符中设置值(#{}表示一个占位符?),自动进行Java类型到JDBC类型的转换(因此#{}可以有效防止SQL注入).#{}可以接收简单类型或PO属性值,如果parameterType传输的是单个简单类型值,#{}花括号中可以是value或其它名称.
  • $ {}: 表示替换SQL字符串,通过${}可将parameterType内容拼接在SQL中而不进行JDBC类型转换,${}可以接收简单类型或PO属性值,如果parameterType传输的是单个简单类型值,${}花括号中只能是value.

为什么#{}能预防SQL注入：

  • 其实是因为SQL语句在程序运行前已经进行了预编译，在程序运行时第一次操作数据库之前，SQL语句已经被数据库分析，编译和优化，对应的执行计划也会缓存下来并允许数据库已参数化的形式进行查询，当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1'也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令，如此，就起到了防止SQL注入的作用了！
  • 注意：虽然${}不能防止SQL注入,但有时${}会非常方便(如order by排序,需要将列名通过参数传入SQL,则用ORDER BY${column},使用#{}则无法实现此功能。

JDBC API小结：

  • 两种思想： ①面向接口编程的思想 ②ORM思想(object relational mapping) <1>一个数据表对应一个java类 <2>表中的一条记录对应java类的一个对象 <3>表中的一个字段对应java类的一个属性 1、sql是需要结合列名和表的属性名来写。注意起别名。
  • 两种技术： ①JDBC结果集的元数据：ResultSetMetaData <1>获取列数：getColumnCount() <2>获取列的别名：getColumnLabel() ②通过反射，创建指定类的对象，获取指定的属性并赋值

操作BLOB类型字段：

  • MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
  • 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
  • MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的) 在这里插入图片描述
  • 实际使用中根据需要存入的数据大小定义不同的BLOB类型。
  • 需要注意的是：如果存储的文件过大，数据库的性能会下降。
  • 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务

批处理

简述：

  • mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。

  • ?rewriteBatchedStatements=true 写在配置文件的url后面

  • 当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率！ MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。

  • MySQLJDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。

  • 只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL，另外这个选项对INSERT/UPDATE/DELETE都有效。

  • 添加rewriteBatchedStatements=true这个参数后的执行速度比较： 同个表插入一万条数据时间近似值： JDBCBATCH 1.1秒左右 > Mybatis BATCH 2.2秒左右 > 拼接SQL 4.5秒左右

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

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

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

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

事务和批处理的区别：

  • 事务： ①事务底层是在数据库方存储SQL，没有提交事务的数据放在数据库的临时表空间。 ②最后一次提交是把临时表空间的数据提交到数据库服务器执行 ③消耗的是数据库服务器内存
  • 批处理： ①批处理底层是在客户端存储SQL ②最后一次执行批处理是把客户端存储的数据发送到数据库服务器执行。 ③消耗的是客户端的内存。

DAO及相关实现类

DAO简介：

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

数据库连接池

数据库连接池由来：

  • 在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：　　 ①在主程序（如servlet、beans）中建立数据库连接 ②进行sql操作 ③断开数据库连接
  • 这种模式开发，存在的问题： ①普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。 ②这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。 ③若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。 ④对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？） ⑤这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

数据库连接池技术：

  • 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。

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

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

  • 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

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

  • 资源重用：由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
  • 更快的系统反应速度：数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间
  • 新的资源分配手段：对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源
  • 统一的连接管理，避免数据库连接泄漏：在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

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

  • 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()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

数据库连接池-常用参数配置及含义：

  • maxActive ： 连接池支持的最大连接数，这里取值为20，表示同时最多有20个数据库连接。一般把maxActive设置成可能的并发量就行了设0为没有限制。
  • maxIdle ：连接池中最多可空闲maxIdle个连接 ，这里取值为20，表示即使没有数据库连接时依然可以保持20空闲的连接，而不被清除，随时处于待命状态。设 0 为没有限制。当超过此值时会收回连接。(idle表示闲置的)
  • minIdle ：连接池中最小空闲连接数，当连接数少于此值时，连接池会创建连接来补充到该值的数量。(idle表示闲置的)
  • initialSize ：初始化连接数目
  • maxWait ：连接池中连接用完时,新的请求等待时间,毫秒，这里取值-1，表示无限等待，直到超时为止，也可取值9000，表示9秒后超时。超过时间会出错误信息
  • removeAbandoned ： 是否清除已经超过“removeAbandonedTimout”设置的无效连接。如果值为“true”则超过“removeAbandonedTimout”设置的无效连接将会被清除。设置此属性可以从那些没有合适关闭连接的程序中恢复数据库的连接。
  • removeAbandonedTimeout ： 活动连接的最大空闲时间,单位为秒 超过此时间的连接会被释放到连接池中,针对未被close的活动连接
  • minEvictableIdleTimeMillis ：连接池中连接可空闲的时间,单位为毫秒 针对连接池中的连接对象
  • timeBetweenEvictionRunsMillis / minEvictableIdleTimeMillis ： 每timeBetweenEvictionRunsMillis毫秒秒检查一次连接池中空闲的连接,把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止.

JDBC事务

MySQL中的事务：

  • 在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。 ①开启事务：start transaction ②结束事务：commit或rollback
  • 在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所作出的影响会持久到数据库中，或者rollback，表示回滚到事务的起点，之前做的所有操作都被撤销了。

JDBC事务：

  • 在JDBC中处理事务，都是通过Connection完成的。
  • 同一事务中所有的操作，都在使用同一个Connection对象。
  • Connection的三个方法与事务有关： ①setAutoCommit（boolean）：设置是否为自动提交事务，如果true（默认值为true）表示自动提交，也就是每条执行的SQL语句都是一个单独的事务，如果设置为false，那么相当于开启了事务了；con.setAutoCommit(false) 表示开启事务。 ②commit（）：提交结束事务。 ③rollback（）：回滚结束事务。
  • JDBC处理事务的代码格式：
try{
     con.setAutoCommit(false);//开启事务
     ......
     con.commit();//try的最后提交事务      
} catch（） {
    con.rollback();//回滚事务
}

事务隔离级别：

  • 事务的并发读问题： ①脏读：读取到另外一个事务未提交数据（不允许出来的事）； ②不可重复读：两次读取不一致； ③幻读（虚读）：读到另一事务已提交数据。
  • 并发事务问题：因为并发事务导致的问题大致有5类，其中两类是更新问题三类是读问题。 ①脏读（dirty read）：读到另一个事务的未提交新数据，即读取到了脏数据； ②不可重复读（unrepeatable）：对同一记录的两次读取不一致，因为另一事务对该记录做了修改； ③幻读（虚读）（phantom read）：对同一张表的两次查询不一致，因为另一事务插入了一条记录。
  • 四大隔离级别：4个等级的事务隔离级别，在相同的数据环境下，使用相同的输入，执行相同的工作，根据不同的隔离级别，可以导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力是不同的。 ①SERIALIZABLE(串行化)：不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问的；性能最差。 ②REPEATABLE READ(可重复读)（MySQL）：防止脏读和不可重复读，不能处理幻读，性能比SERIALIZABLE好。 ③READ COMMITTED(读已提交数据)（Oracle）：防止脏读，不能处理不可重复读和幻读；性能比REPEATABLE READ好 ④READ UNCOMMITTED(读未提交数据)：可能出现任何事物并发问题，什么都不处理。性能最好

Apache-DBUtils实现CRUD操作

Apache-DBUtils：

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

数据源监控

Druid整合以及开启监控：

  • druid中内置了很多种Filter，这里使用StatFilter，是用于统计监控信息。开启配置的有两种方式：
#默认：配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,log4j


# 自定义：添加StatFilter配置
filter:
  stat:
    enabled: true
    db-type: mysql
    log-slow-sql: true
    slow-sql-millis: 1000
  • 编写@Configuration配置交给spring管理：
@Configuration
public class DruidConfig {
    @Value("${spring.druid.slowSqlMillis}")
    private Long slowSqlMillis;
    
    //会扫描application.properties文件的以spring.druid开头的数据注入
    @ConfigurationProperties(prefix = "spring.druid")    
    @Bean(initMethod = "init",destroyMethod = "close") //跟着spring一块启动，一块关闭
    public DruidDataSource dataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setProxyFilters(Lists.newArrayList(statFilter()));//Lists是guava包
        return dataSource;
    }

    //alibaba的监听器，打印慢sql
    @Bean
    public Filter statFilter(){
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(slowSqlMillis);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;
    }
    
	//配置监控访问页面
    @Bean
    public ServletRegistrationBean servletRegistrationBean(){
        return new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
    }
}
  • 通过DruidStatManagerFacade.getInstance().getDataSourceStatDataList()方法查询数据库链接池监控信息：代码中可以通过DruidStatManagerFacade.getInstance().getDataSourceStatDataList()方法查询数据库链接池监控信息，大家可以通过服务接口、定时任务写入数据库、定时任务写入日志抽取到kafka等方式来存储这个监控信息，进行实时监控，来看下监控返回的数据。

JDBC总结

总结：

@Test
public void testUpdateWithTx() {
	
Connection conn = null;
try {
	//1.获取连接的操作（
	//① 手写的连接：JDBCUtils.getConnection();
	//② 使用数据库连接池：C3P0;DBCP;Druid
	//2.对数据表进行一系列CRUD操作
	//① 使用PreparedStatement实现通用的增删改、查询操作（version 1.0 \ version 2.0)
//version2.0的增删改public void update(Connection conn,String sql,Object ... args){}
//version2.0的查询 public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object ... args){}
	//② 使用dbutils提供的jar包中提供的QueryRunner类
		
	//提交数据
	conn.commit();
	
	
} catch (Exception e) {
	e.printStackTrace();
		
		
	try {
		//回滚数据
		conn.rollback();
	} catch (SQLException e1) {
		e1.printStackTrace();
	}
		
}finally{
	//3.关闭连接等操作
	//① JDBCUtils.closeResource();
	//② 使用dbutils提供的jar包中提供的DbUtils类提供了关闭的相关操作
	}
}
avatar
文章
阅读
粉丝