JDBC总结

最新推荐文章于 2024-06-14 08:18:26 发布

原创最新推荐文章于 2024-06-14 08:18:26 发布 · 307 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #sql #mysql #java

本文介绍了JDBC的基础知识，包括数据持久化、JDBC接口和驱动的概念。详细阐述了获取数据库连接的步骤，以及PreparedStatement相对于Statement的优势，如防止SQL注入和提高性能。还讨论了数据库事务的重要性和事务隔离级别。最后提到了数据库连接池的作用和Druid连接池的使用，并简单介绍了ApacheDBUtils如何简化CRUD操作。

一、JDBC概述

1、数据持久化
    把数据保存到可掉电式存储设备中以供之后使用，jdbc中通过各种关系型数据库实现
2、JDBC概念
    JDBC为一套接口，使用接口可实现对具体数据库的操作
3、驱动
    针对接口提供具体实现类的集合
4、JDBC体系
    面向应用的api
	         供程序开发人员使用
    面向数据库的api
	         供开发商开发数据库驱动使用

二、JDBC程序编写步骤

1、获取数据库的连接

（1）添加相应jar
（2）加载与注册JDBC驱动
   Driver接口实现类
      java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口。由驱动程序管理器类 
  (java.sql.DriverManager)去调用Driver实现。
     - Oracle的驱动：oracle.jdbc.driver.OracleDriver
     - mySql的驱动： com.mysql.jdbc.Driver
（3）设置配置文件

配置文件：

user=root//用户名
password=******//密码
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.jdbc.Driver//加载与注册JDBC驱动

JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。
JDBC URL的标准由三部分组成，各部分间用冒号分隔。

协议：JDBC URL中的协议总是jdbc
子协议：子协议用于标识一个数据库驱动程序
子名称：一种标识数据库的方法，包含主机名(对应服务端的ip地址)，端口号，数据库名

连接代码：

public static Connection getConnection() throws Exception {
		// 1.读取配置文件中的4个基本信息
		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);
		return conn;
	}

2、数据库的增删改查操作

（1）操作和访问数据库

数据库的调用的三种不同方式：

Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
CallableStatement：用于执行 SQL 存储过程（2）

（2）使用Statement操作数据表的弊端

但是使用Statement操作数据表存在弊端：
- 问题一：存在拼串操作，繁琐
- 问题二：存在SQL注入问题

（3）PreparedStatement的使用

①PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句。
②可以通过调用 Connection 对象的 preparedStatement(String sql)方法获取 PreparedStatement 对象。
③PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数。

使用PreparedStatement实现通用的增、删、改的方法：

public void update(String sql,Object ...args){//sql中占位符的个数与可变形参的长度相同！
		Connection conn = null;
		PreparedStatement ps = 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.执行
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);	
		}	
	}

（4）PreparedStatement vs Statement

①采用占位符解决Statement的拼串问题。
②解决了Statementsql注入问题。
③可操作blob类型变量。
④PreparedStatement 能最大可能提高性能：
	DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
⑤批量插入效率高。

（5）向数据表中插入Blob类型的字段

PreparedStatement可以操作Blob类型的变量。
写入操作的方法：setBlob(InputStream is);
读取操作的方法：
Blob blob = getBlob(int index);
InputStream is = blob.getBinaryStream();

向数据表中插入Blob类型的字段

@Test
	public void testInsert() 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, "yuan@qq.com");
		ps.setObject(3,"1992-09-08");
		FileInputStream is = new FileInputStream(new File("girl.jpg"));
		ps.setBlob(4, is);
		
		ps.execute();
		
		JDBCUtils.closeResource(conn, ps);
		
	}

查询数据表中Blob类型的字段

@Test
	public void testQuery(){
		Connection conn = null;
		PreparedStatement ps = null;
		InputStream is = null;
		FileOutputStream fos = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "select id,name,email,birth,photo from customers where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, 21);
			rs = ps.executeQuery();
			if(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
				Customer cust = new Customer(id, name, email, birth);
				System.out.println(cust);
				
				//将Blob类型的字段下载下来，以文件的方式保存在本地
				Blob photo = rs.getBlob("photo");
				is = photo.getBinaryStream();
				fos = new FileOutputStream("zhangyuhao.jpg");
				byte[] buffer = new byte[1024];
				int len;
				while((len = is.read(buffer)) != -1){
					fos.write(buffer, 0, len);
				}
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
			try {
				if(is != null)
					is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				if(fos != null)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}	
			JDBCUtils.closeResource(conn, ps, rs);
		}
	}

（6）使用PreparedStatement实现批量操作：

@Test
	public void testInsert3() {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			
			long start = System.currentTimeMillis();
			
			conn = JDBCUtils.getConnection();
			
			//设置不允许自动提交数据
			conn.setAutoCommit(false);
			
			String sql = "insert into goods(name)values(?)";
			ps = conn.prepareStatement(sql);
			for(int i = 1;i <= 1000000;i++){
				ps.setObject(1, "name_" + i);
				
				//1."攒"sql
				ps.addBatch();
				
				if(i % 500 == 0){
					//2.执行batch
					ps.executeBatch();
					
					//3.清空batch
					ps.clearBatch();
				}
				
			}
			
			//提交数据
			conn.commit();
			
			long end = System.currentTimeMillis();
			
			System.out.println("花费的时间为：" + (end - start));      //20000:83065 -- 565
		} catch (Exception e) {								//1000000:16086 -- 5114
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, ps);
			
		}
		
	}

3、数据库的事务

关于事务的学习我们在上一篇博客就已经介绍一些了，接下来就不过多的赘述了。

（1）哪些操作会导致数据的自动提交？
 ①DDL操作一旦执行，都会自动提交。
     set autocommit = false 对DDL操作失效
 ②DML默认情况下，一旦执行，就会自动提交。
      可以通过set autocommit = false的方式取消DML操作的自动提交。
 ③默认在关闭连接时，会自动的提交数据

(2)在MySql中设置隔离级别

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
查看当前的隔离级别:
```
SELECT @@tx_isolation;
```

设置当前 mySQL 连接的隔离级别:

set  transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

补充操作：

创建mysql数据库用户：
```
create user tom identified by 'abc123';
```

授予权限

#授予通过网络方式登录的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';

(3)考虑到事务以后，实现的通用的增删改操作：

public int update(Connection conn,String sql, Object... args) {// sql中占位符的个数与可变形参的长度相同！
		PreparedStatement ps = null;
		try {
			// 1.预编译sql语句，返回PreparedStatement的实例
			ps = conn.prepareStatement(sql);
			// 2.填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);// 小心参数声明错误！！
			}
			// 3.执行
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 4.资源的关闭
			JDBCUtils.closeResource(null, ps);

		}
		return 0;

	}

(4)考虑到事务以后，实现的通用的查询：

public <T> T getInstance(Connection conn,Class<T> clazz,String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

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

			if (rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				for (int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);

					// 获取每个列的列名
					// String columnName = rsmd.getColumnName(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					// 给t对象指定的columnName属性，赋值为columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);

		}

		return null;
	}

三、数据库连接池

（1）传统开发中的数据库连接问题
①每次连接，使用完都得断开，不然会产生数据泄漏。
②不能控制被创建的连接对象数。
③数据库资源不能得到很好的重复利用。

（2）数据库连接池技术

①基本思想： 就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
②数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
③这些数据库连接的数量是由最小数据库连接数来设定，连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数。

（3）数据库连接池的优点

1. 资源重用

由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

2. 更快的系统反应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间

3. 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

4. 统一的连接管理，避免数据库连接泄漏

在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

（4）多种开源的数据库连接池

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

DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池。
DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。

*Druid（德鲁伊）数据库连接，是目前最好的连接池之一，重点介绍一下。

（5）测试连接的代码

private static DataSource source1;
	static{
		try {
			Properties pros = new Properties();
			
			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
			
			pros.load(is);
			
			source1 = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection3() throws SQLException{
		
		Connection conn = source1.getConnection();
		return conn;
	}

配置文件定义在src下：druid.properties：

url=jdbc:mysql:///test
username=root
password=abc123
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

四、Apache-DBUtils实现CRUD操作

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

1、主要API 的使用

①DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。
②QueryRunner：该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

使用dbutils.jar包中的DbUtils工具类实现连接等资源的关闭：

public static void closeResource1(Connection conn,Statement ps,ResultSet rs){
		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(rs);
	}

使用QueryRunner测试增、删、改的操作:

@Test
	public void testInsert() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "insert into customers(name,email,birth)values(?,?,?)";
			int insertCount = runner.update(conn, sql, "蔡徐坤","caixukun@126.com","1997-09-08");
			System.out.println("添加了" + insertCount + "条记录");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
			JDBCUtils.closeResource(conn, null);
		}
		
	}

2、ResultSetHandler接口及实现类：

接口的主要实现类：

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：查询单个值对象

使用QueryRunner测试查询的操作：

BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录。

@Test
	public void testQuery1(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			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, 23);
			System.out.println(customer);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
			
		}
		
	}

BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。

@Test
	public void testQuery2() {
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select id,name,email,birth from customers where id < ?";
			
			BeanListHandler<Customer>  handler = new BeanListHandler<>(Customer.class);

			List<Customer> list = runner.query(conn, sql, handler, 23);
			list.forEach(System.out::println);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
			JDBCUtils.closeResource(conn, null);
		}
		
	}

MapHander:是ResultSetHandler接口的实现类，对应表中的一条记录。

@Test
	public void testQuery3(){
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils.getConnection3();
			String sql = "select id,name,email,birth from customers where id = ?";
			MapHandler handler = new MapHandler();
			Map<String, Object> map = runner.query(conn, sql, handler, 23);
			System.out.println(map);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, null);
			
		}
		
	}