Java笔记（30）JDBC

JAVA笔记之JDBC

1.JDBC简介

JDBC是Java语言中用于规范与数据库连接操作的一组接口，它的全称是（Java Database Connectivity）通过这套规范，我们可以在Java中对不同数据库厂商的数据库进行访问与操作。

2.JDBC使用

1.加载驱动获取数据库连接对象

要使用jdbc，要先导入驱动，即数据库厂商实现的jar包，以mysql为例，将其jar包导入到项目中，就可以正常使用JDBC进行对数据库的操作了；
导入jar包后，我们要获取数据库连接对象，也就是Connection，它用于代表数据库的链接，Collection是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过connection对象完成的，创建方法为：

	//1.加载驱动类
	Class.forName("com.mysql.jdbc.Driver");
	//2.标识url，用于告诉连接数据库的地址
	String url = "jdbc:mysql://localhost:3306/";
	//3.数据库用户名
	String user = "root";
	//4.密码
	String pass = "123123";
	//5.获取连接对象
	Connection connection = DriverManager.getConnection(url, user, pass);

获取到数据库连接对象后，我们就可以进行一系列对数据库的增删查改操作了，上面的代码还可以进行优化，例如使用读取配置文件的方式来读取数据库的四大参数，方便得到不同数据库的连接对象时的配置操作；

示例：

	public static Connection getConnection() {
		//得到配置文件的输入流，这里的获取输入流方式随着你的文件位置和项目类型进行改变
		InputStream iStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
		//获取Properties对象，该对象可以方便的对键值对形式的文件进行读取封装
		Properties prop = new Properties();
		try {
			//加载配置文件
			prop.load(iStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
		//得到数据库四大参数
		String user = prop.getProperty("user");
		String pass = prop.getProperty("pass");
		String url = prop.getProperty("url");
		String driver = prop.getProperty("driver");

		try {
			//加载驱动
			Class.forName(driver);
			//获取连接对象
			Connection connection = DriverManager.getConnection(url, user, pass);
			return connection;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

2.执行SQL语句之Statement

JDBC程序中的Statement对象用于向数据库发送SQL语句，创建方法为：

	//获取SQL语句发送器对象，con是Connection连接对象
	Statement stm = con.createStatement();

Statement对象常用方法：

方法	含义
executeQuery(String sql)	用于向数据库发送查询语句，返回一个结果集对象
executeUpdate(String sql)	用于向数据库发送insert、update或delete语句
execute(String sql)	用于向数据库发送任意sql语句
addBatch(String sql)	把多条sql语句放到一个批处理中
executeBatch()	向数据库发送一批sql语句执行

	Statement stm = null;
	//获取用于向数据库发送sql语句的statement
	stm = conn.createStatement();
	//向数据库发sql
	String sql = "select id,name,password,email,birthday from users";
	//得到一个结果集对象，后面可以通过结果集对象的方法来获取查询到的信息
	stm.executeQuery(sql);

3.执行SQL语句之PreperedStatement

PreperedStatement是Statement类的子类，它的实例对象可以通过调用：

	PreperedStatement stm = null;
	//sql语句，其中?是占位符，表示参数
	String sql = "select * from users where name=? and password=?";
 
	//获取用于向数据库发送sql语句的Preperedstatement
	stm = con.preparedStatement(sql);//在此次传入sql，进行预编译
	//给第一个占位符设置值，注意索引是从1开始
	stm.setString(1, username);
	stm.setString(2, password);
	//4.向数据库发sql
	stm.executeQuery();//在这里不需要传入sql

比较：
相对于Statement对象而言，PreperedStatement可以避免SQL注入的问题。Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement 可对SQL进行预编译，从而提高数据库的执行效率。
并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

此外，如果需要批量处理大量sql语句或者操作大数据类型（Blob）时，建议使用PreparedStatement，此外，在对很多条sql语句处理时，可以先关闭Connection对象的自动提交方法，等到所有sql语句都添加到批处理中（缓存）时，再手动提交可以提升效率。

例子：

		Connection con = null;
		PreparedStatement pstm = null;

		try {
			// 获取连接对象，这里使用了自己封装的JDBC工具类来获取
			con = JDBCUtil.getConnection();
			//设置连接不自动提交
			con.setAutoCommit(false);
			
			String sql = "insert into goods(name) values(?)";
			// 获取PreparedStatement对象
			pstm = con.prepareStatement(sql);
			for (int i = 0; i < 1000000; i++) {
				pstm.setObject(1, "name_" + i);
				//添加到批处理中
				pstm.addBatch();
				
				if(i % 500 == 0) {
					//如果已经添加了500条批处理语句，就执行
					pstm.executeBatch();
					
					//清空批处理空间，即清理缓存
					pstm.clearBatch();
				}
			}
			//提交事务
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}

4.获取结果集ResultSet

当我们使用JDBC的一些操作时，会产生一个结果集，例如在查询操作时，就会返回一个ResultSet对象，Resultset封装执行结果时，采用的类似于表格的方式，ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前，调用ResultSet.next() 方法，可以使游标指向具体的数据行，进行调用方法获取该行的数据。
1、获取行
ResultSet提供了对结果集进行滚动的方法：

• next()：移动到下一行
• Previous()：移动到前一行
• absolute(int row)：移动到指定行
• beforeFirst()：移动resultSet的最前面。
• afterLast() ：移动到resultSet的最后面。

2、获取值
ResultSet既然用于封装执行结果的，所以该对象提供的都是用于获取数据的get方法：

获取任意类型的数据
getObject(int index)
getObject(string columnName)

获取指定类型的数据，例如：
getString(int index)
getString(String columnName)

JDBC案例：
这里用一个代码案例来巩固之前学习的JDBC操作：

1.首先，数据库中建立如上表。
2.将该表封装成一个实体类；

//封装该表为一个类
public class Customer {
	private int id;
	private String name;
	private String email;
	private Date birth;

	public Customer() {
		super();
	}

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

	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 + "]";
	}

}

3.写一个通用的对表来增删查改的类

/**
 * 这个抽象类封装了对表的常用操作
 * 
 * @author watermelon
 *
 * @param <T> 泛型参数，操作哪个表的实体类就传哪个类，例如这里操作Customer就传Customer
 */
public abstract class BaseDao<T> {

	private Class<T> clazz = null;

	{
		// 获取当前对象的父类的泛型类型，造该类的子类对象时的那个子类对象就是这里的this
		Type genericSuperclass = this.getClass().getGenericSuperclass();
		ParameterizedType type = (ParameterizedType) genericSuperclass;

		Type[] typeArguments = type.getActualTypeArguments();
		// 将获取到的要操作的实际对象赋值给clazz
		clazz = (Class<T>) typeArguments[0];
	}

	/**
	 * 对表的通用增删改操作
	 * 
	 * @param conn 连接对象
	 * @param sql  更新语句
	 * @param args sql语句中参数的值
	 * @return 返回影响的记录数
	 */
	public int update(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		int result = 0;

		try {
			// 预编译sql语句
			ps = conn.prepareStatement(sql);
			// 给参数赋值
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			JDBCUtil.close(ps);
		}
		return result;
	}

	/**
	 * 通用查询操作,只返回结果集的第一行数据，若无数据返回null
	 * 
	 * @param conn
	 * @param sql
	 * @param args
	 * @return
	 */
	public T getInstance(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		T t = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			// 执行sql语句返回ResultSet
			rs = ps.executeQuery();
			// 获取结果集的元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			// 获取结果集的列数
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				//得到要操作的实例对象，例如操作Customer类那这里就是Customer的实例对象
				t = clazz.newInstance();
				// 处理结果集第一行中每一列的数据并通过反射赋值给操作的表对象
				for (int i = 0; i < columnCount; i++) {
					Object object = rs.getObject(i + 1);
					// 获取每一列的列名，这里这个方法返回的是sql语句的别名，没有别名就返回原本列名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					//通过反射给实例对象的属性赋值
					Field field = clazz.getDeclaredField(columnLabel);
					// 取消访问检查
					field.setAccessible(true);
					field.set(t, object);
				}
			}
		} catch (SQLException | InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(null, ps, rs);
		}
		return t;
	}
	
	//返回数据库表中所有的记录
	public List<T> getAll(Connection conn, String sql) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> list = null;
		try {
			ps = conn.prepareStatement(sql);
			// 执行sql语句返回ResultSet
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();

			list = new ArrayList<T>();

			while (rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集第一行中每一列的数据并通过反射赋值给操作的表对象
				for (int i = 0; i < columnCount; i++) {
					Object object = rs.getObject(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);

					Field field = clazz.getDeclaredField(columnLabel);
					// 取消访问检查
					field.setAccessible(true);
					field.set(t, object);
				}
				list.add(t);
			}
		} catch (SQLException | InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(null, ps, rs);
		}
		return list;
	};

	// 返回表中记录数
	public Long getValue(Connection conn, String sql) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		long l = 0;

		try {
			ps = conn.prepareStatement(sql);
			// 执行sql语句返回ResultSet
			rs = ps.executeQuery();

			if (rs.next()) {
				Object object = rs.getObject(1);
				l = (long) object;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(null, ps, rs);
		}
		return l;
	};
}

4.定义针对customer表操作的接口，用于规范

/**
 * 该接口用于规范针对Customer表的常用操作
 * 
 * @author Administrator
 *
 */
public interface CustomerDao {
	/**
	 * 将Customer对象添加到数据库中
	 * 
	 * @param conn
	 * @param cust
	 */
	public abstract void insert(Connection conn, Customer cust);

	/**
	 * 根据指定id删除一条记录
	 * 
	 * @param conn
	 * @param id
	 */
	public abstract void deleteById(Connection conn, int id);

	/**
	 * 根据内存中的cust对象修改表中指定的记录
	 * 
	 * @param conn
	 * @param cust
	 */
	public abstract void update(Connection conn, Customer cust);

	/**
	 * 根据指定id查询其对应的Customer对象
	 * 
	 * @param conn
	 * @param id
	 * @return
	 */
	public abstract Customer getCustomerById(Connection conn, int id);
	
	/**
	 * 查询表中的所有记录构成的集合
	 * @param conn
	 * @return
	 */
	public abstract List<Customer> getAll(Connection conn);
	
	/**
	 * 查询表中的记录数
	 * @param conn
	 * @return
	 */
	public abstract Long getCount(Connection conn);
}

5.实现CustomerDao接口并继承BaseDao类

public class CustomerDaoImpl extends BaseDao<Customer> implements CustomerDao {

	@Override
	public void insert(Connection conn, Customer cust) {
		String sql = "insert into customer(name,email,birth) values(?,?,?)";
		update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth());
	}

	@Override
	public void deleteById(Connection conn, int id) {
		String sql = "delete from customer where id = ?";
		update(conn, sql, id);
	}

	@Override
	public void update(Connection conn, Customer cust) {
		String sql = "update customer set name = ?,email = ?,birth = ? where id = ?";
		update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId());
	}

	@Override
	public Customer getCustomerById(Connection conn, int id) {
		String sql = "select id,name,email,birth from customer where id = ?";
		Customer instance = getInstance(conn, sql, id);
		return instance;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customer";
		List<Customer> all = getAll(conn, sql);
		return all;
	}

	@Override
	public Long getCount(Connection conn) {
		String sql = "select count(*) from customer";
		return getValue(conn, sql);
	}

}

之后进行测试即可，这里经过测试代码没有问题；主要是体会对JDBC的操作和ORM的封装思想；其中BaseDao也可以先设计为一个接口然后提供具体实现类比较好，可以优化的地方还有很多。

5.JDBC事务操作

事务： 一组逻辑操作单元，使一种数据状态转换到另一种数据状态。
事务操作是指将所有事务都作为一个工作单元来执行，即使出现了事故，都不能改变这种执行方式。当一个事务中执行多个操作时，要么所有事务都被提交，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所有的修改，整个事务回滚到最初的状态；

为保证数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元；当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有起始点以后的操作应该全部回退到开始状态。

在JDBC操作中，数据一旦提交，就不可回滚；

哪些操作会导致数据的自动提交：

• DDL操作
• DML操作，默认情况下一旦执行就会自动提交，但可以通过设置set autocommit来取消自动提交；
• 关闭连接

隔离级别：
类似多线程的安全问题，在多个连接访问操作数据库时也可能会出现一些并发问题：

• 脏读：对于两个事物T1，T2，T1读取了已经被T2更新但没有提交的数据。这时如果T2回滚，那么T1读取到的数据就是临时且无效的；
• 不可重复读：对于两个事物T1，T2，如果T1读取了一个字段，T2又更新了该字段，之后T1再次读取该字段则两次的值就不相同了；
• 幻读：对于两个事物T1，T2，T1从表中读取了一个字段，然后T2在该表中插入了一些新的行。之后如果T1再次读取同一个表，就会多出几行；

为了解决这些数据库并发问题，数据库提供了四种事务隔离级别，隔离级别越高，数据一致性就越好，但并发性能就越弱；
Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）
Read committed：可避免脏读情况发生（读已提交）。
Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		Savepoint sp = null;
		
		try{
			con = JdbcUtils.getConnection();
			//避免脏读
			con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
			con.setAutoCommit(false);   //设置不自动提交
			
			String sql1 = "update account set money=money-100 where name='aaa'";
			st = con.prepareStatement(sql1);
			st.executeUpdate();
			
			sp = con.setSavepoint();//在这里设置事务回滚点
			
			String sql2 = "update account set money=money+100 where name='bbb'";
			st = con.prepareStatement(sql2);
			st.executeUpdate();
			
			int x = 1/0;
			
			String sql3 = "update account set money=money+100 where name='ccc'"; 
			st = con.prepareStatement(sql3);
			st.executeUpdate();
			
			con.commit();
			
		}catch (Exception e) {
			try {
				con.rollback(sp);//回滚到该事务点，即该点之前的会正常执行（sql1）
				con.commit();  //回滚了要记得提交,如果没有提交sql1将会自动回滚
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			//释放资源
			JDBCUtil.close(con, st, rs);
		}
		
	}

6.JDBC数据库连接池

数据库连接池的必要性：
在使用开发基于数据库的web程序时，传统的模式基本是按以下的步骤：

1. 在主程序中（如servlet，beans）建立数据库连接；
2. 进行sql操作；
3. 断开数据库连接

这种模式开发存在的问题：

• 普通的JDBC数据库连接使用DriverManager获取，每次向数据库建立连接时都要将Connection加载到内存中去，再验证用户名和密码(需要花费0.05s~1s的时间)。需要数据库连接时，就向数据库要一个，执行完后再断开连接，这样的方式将会消耗大量的资源和时间，数据库的连接资源并没有得到很好的重复利用，若是上百甚至上千人同时在线，频繁的进行数据库连接操作将占用更多的系统资源，严重的甚至会造成服务器的崩溃。
• 对于每一次的数据库连接操作，使用完后都得断开。否则，如果程序出现异常未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库；
• 这种开发模式不能控制被创建的连接对象数，系统资源会被毫无顾忌的分配出去，如连接过多，也可能导致连接过多，内存泄漏，最后服务器崩溃。

为了解决这种传统开发模式中数据库连接的弊端，可以采用数据库连接池技术，它的原理和功能与线程池类似。

• 数据库连接池的基本思想： 就是为数据库连接建立一个缓冲池，预先在缓冲池中放入一定数量的连接，当需要使用时只需要从中取出一个，使用完毕再放回缓冲池中即可。
• 数据库连接池负责分配和管理、释放数据库连接，它允许程序重复使用一个现有的数据库连接，而不是去重新建立；
• 数据库连接池在初始化时将创建一定量的数据库连接到连接池中，这些连接的数量由最小数据库连接数来设定，无论数据库连接是否被使用，连接池都将一直保持有这么多的连接数量。连接池的最大数据库连接量限定了这个连接池最多能有多少个连接数，超过这个连接数的的请求都将被加入到等待队列中去。
• 统一的连接管理，避免数据库连接泄漏。

Java的数据库连接池使用javax.sql.DataSource来表示，其中DataSource只是一个接口，可以自己实现，也有数据库自己的实现，也可以采用服务器或别的组织封装好的一些实现，常用的有C3P0、Druid、Tomcat实现的数据库连接池等。

这里我使用了Druid的数据库连接池驱动，这是由阿里开发的一个数据库连接池实现，比较常用性能也较好，具体驱动可以自己去下载和使用。这里只贴出其创建连接池和得到连接的代码，其余方法和日志监控请自己查阅文档使用。

	//首先定义一个静态的数据库源，即连接池对象。使得多个对象得到的是同一个数据库连接池
	private static DataSource source;
	//静态代码块保证类加载时就读取配置文件中的信息得到连接池对象，且只执行一次
	static {
		Properties pros = new Properties();
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

		try {
			pros.load(is);
			//使用com.alibaba.druid.pool包提供的工厂类创建数据库连接池，注意别导错包
			source = DruidDataSourceFactory.createDataSource(pros);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {
		return source.getConnection();
	}

这个代码在Myeclipse的项目中如果使用了自带的javaee6.0包会发生版本冲突异常NoSuchMethodError，具体原因待以后探索，这里我采取了升级为javaee7.0解决问题，通过异常信息猜测可能是myeclipse自带的javaee6.0中的日志类与Druid中使用的日志类发生了冲突。

7.使用apache的dbutils写jdbc

由于我们在日常开发中大量的jdbc操作都是重复的，因此一些组织也对这些非常常用且重复的操作进行了一个封装，使得我们可以非常方便的来进行增删改查等日常操作，简化我们的开发。这里我使用apache发布的dbutils工具。
导入其对应的jar包后，我们就可以进行使用了。

	//update测试
	@Test
	public void test1() {
		Connection conn = null;
		try {
			//获取dbutils工具的核心类
			QueryRunner qr = new QueryRunner();
			//获取连接
			conn = JDBCUtil.getConnection();
			String sql = "insert into customer(name,email,birth) values(?,?,?)";
			//执行SQL语句
			int update = qr.update(conn, sql, "蔡大傻", "13813822@qq.com", new Date(13215412127L));
			//输出影响的记录数
			System.out.println(update);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn);
		}
	}
	//query测试
	@Test
	public void test2() {
		Connection conn = null;
		try {
			QueryRunner qr = new QueryRunner();
			conn = JDBCUtil.getConnection();
			//查询语句
			String sql = "select id,name,email,birth from customer where id = ?";
			//ResultHandle的子类，由dbutils的jar包内提供，可以将查询到的结果封装成指定的bean对象
			BeanHandler<Customer> rsh = new BeanHandler<Customer>(Customer.class);
			Customer cust = qr.query(conn,sql, rsh, 2);
			//输出结果
			System.out.println(cust);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn);
		}
	}
	@Test
	public void test3() {
		Connection conn = null;
		try {
			QueryRunner qr = new QueryRunner();
			conn = JDBCUtil.getConnection();
			//查询全部结果
			String sql = "select id,name,email,birth from customer";
			BeanListHandler<Customer> rsh = new BeanListHandler<Customer>(Customer.class);
			List<Customer> list = qr.query(conn, sql, rsh);
			
			list.forEach(System.out::println);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn);
		}
	}