1. 使用PreparedStatement实现CRUD操作基础知识

1.1. 操作和访问数据库

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

  • 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：

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

1.2. PreparedStatement概念

  • 可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象

  • PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句

  • PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

1.3. Java和SQL数据类型

1.4. ResultSet与ResultSetMetaData

1.4.1. 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开始。

  • ResultSet 接口的常用方法：
    • boolean next()
    • getString()
    • getObject()

1.4.2. ResultSetMetaData

  • 可用于获取关于 ResultSet 对象中列的类型和属性信息的对象

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

  • 得到结果集后需要使用一个描述 ResultSet 的对象， 即 ResultSetMetaData.

    • 调用 ResultSet 的 getMetaData() 方法获取 ResultSetMetaData
    • 调用 ResultSetMetaData 的 getColumnCount() 方法获取 ResultSet 中有多少列
    • 调用 ResultSetMetaData 的getColumnLabel() 方法获取 ResultSet 每一列的列的别名是什么

1.4.3. 资源的释放

  • 释放ResultSet, Statement,Connection。

  • 数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

  • 可以在finally中关闭，保证及时其他代码出现异常，资源也一定能被关闭。
    代码示例：

  • 通用增删改操作

public void update(String sql,Object... args){
		Connection conn = JDBCUtils.getConnection ("jdbc.properties");
		PreparedStatement statement = null;
		try {
			 statement = conn.prepareStatement (sql);
			 for (int i = 0;i < args.length;i++){
			 	statement.setObject (i+1,args[i]);
			 }
			 statement.execute ();
		} catch (SQLException e) {
			e.printStackTrace ();
		}finally {
			JDBCUtils.closeResource (conn,statement);
		}

}
  • 通用查询操作
public static List<ClassInfo> query(String sql, Object... args) throws SQLException, NoSuchFieldException, IllegalAccessException {
		//连接数据库
		Connection conn = JDBCUtils.getConnection ("jdbc.properties");

		PreparedStatement preparedStatement = conn.prepareStatement (sql);
		for (int i = 0;i < args.length;i++){
			preparedStatement.setObject (i + 1,args[i]);
		}

		ResultSet resultSet = preparedStatement.executeQuery();
		//结果集的元数据
		ResultSetMetaData metaData = resultSet.getMetaData ();
		int columnCount = metaData.getColumnCount ();

		List resList = new ArrayList ();

		while(resultSet.next ()){
			ClassInfo temp = new ClassInfo ();
			for (int i = 0;i < columnCount;i++){
				//获取列值
				Object value = resultSet.getObject (i + 1);
				//获取列名
				String columnName = metaData.getColumnName (i + 1);
				//利用反射设置值
				Field valueField = ClassInfo.class.getDeclaredField (columnName);
				valueField.setAccessible (true);
				valueField.set (temp,value);
				//添加到返回集合中
				resList.add (temp);
			}
		}
		return resList;
}

2. 操作BLOB字段

2.1. MySQL BLOB类型

  • MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。

  • 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

  • MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

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

2.2. 插入Blob字段

public static int insertBlob() throws IOException, ClassNotFoundException, SQLException {
		InputStream inputStream = ClassLoader.getSystemResourceAsStream ("jdbc.properties");

		Properties properties = new Properties ();
		assert inputStream != null;
		properties.load (inputStream);

		String url = properties.getProperty ("url");
		Class.forName ("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection (url,properties);

		String sql = "insert into testBlob(name,email,age,photo) values(?,?,?,?)";
		PreparedStatement statement = conn.prepareStatement (sql);

		statement.setObject (1,"孙悟空");
		statement.setObject (2,"sunwukong@qq.com");
		statement.setObject (3,1500);

		FileInputStream in = new FileInputStream ("G:\\Java_WorkSpace\\JDBC\\TestPreparedStatement\\src\\sunwukong.jpg");
		statement.setBlob (4,in);

		return statement.executeUpdate ();
}

2.3. 修改/更新Blob字段

public static int updateBlob() throws Exception {
		InputStream in = ClassLoader.getSystemResourceAsStream ("jdbc.properties");

		Properties properties = new Properties ();
		properties.load (in);

		String url = properties.getProperty ("url");
		Class.forName ("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection (url,properties);
		String sql = "update testblob set photo = ? where name = ? ";
		PreparedStatement statement = conn.prepareStatement (sql);
		InputStream inputStream = new FileInputStream("G:\\Java_WorkSpace\\JDBC\\TestPreparedStatement\\beijing123.jpg");
		statement.setBlob (1,inputStream);
		statement.setObject (2,"金克丝");

		return statement.executeUpdate ();
	}
2.4. 读取Blob字段
public static void readBlob() throws Exception {
		Connection conn = JDBCUtils.getConnection ("jdbc.properties");
		String sql = "select id,name,email,age,photo from testblob where email = ?";
		assert conn != null;
		PreparedStatement statement = conn.prepareStatement (sql);
		statement.setObject (1,"787645957@qq.com");

		ResultSet resultSet = statement.executeQuery ();

		if (resultSet.next ()){

			int id = resultSet.getInt ("id");
			String name = resultSet.getString ("name");
			String email = resultSet.getString ("email");
			int age = resultSet.getInt ("age");
			Student student = new Student (id,name,email,age);
			Blob photo = resultSet.getBlob ("photo");

			InputStream inputStream = photo.getBinaryStream ();
			//保存为liyao.jpg
			OutputStream outputStream = new FileOutputStream ("liyao.jpg");

			byte[] buf = inputStream.readAllBytes ();
			outputStream.write (buf,0,buf.length);

			System.out.println (student);
		}
}

3. 批量插入/批量执行SQL语句

3.1. 批量执行SQL语句

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

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

  • 把**?rewriteBatchedStatements=true写在配置文件的url后面或者&rewriteBatchedStatements=true**写在其他参数后面。

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

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

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

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

3.2. 批量插入

public static void piInsert() throws SQLException {
		long start = System.currentTimeMillis ();
		//JDBCUtils是一个工具类，封装了数据库连接，返回一个连接
		Connection conn = JDBCUtils.getConnection ("jdbc.properties");
		conn.setAutoCommit (false);

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

		for (int i = 0;i < 20000;i++){
			statement.setObject (1,"student" + (i+1));
			//积攒sql语句
			statement.addBatch ();
			if (i % 500 == 0){
				//执行
				statement.executeBatch ();
				//清空
				statement.clearBatch ();
			}
			//执行最后一条
			if (i == 19999){
				statement.executeBatch ();
				statement.clearBatch ();
			}

		}
		//提交数据
		conn.commit ();
		//设置为默认的自动提交
		conn.setAutoCommit (true);
		long end = System.currentTimeMillis ();
		System.out.println ("执行时间: " + (end - start) / 1000);
}

4. 数据库事务

4.1. 注意事项和操作

  • 数据一旦提交，就不可回滚。
  • 数据什么时候意味着提交？
  • 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
  • **关闭数据库连接，数据就会自动的提交。**如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。
  • JDBC程序中为了让多个 SQL 语句作为一个事务执行：
    • 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
    • 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
    • 在出现异常时，调用 rollback(); 方法回滚事务
    • 若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，恢复自动提交状态。

举例：

	public static void updateBalance()  {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection ("jdbc.properties");
			conn.setAutoCommit (false);
			String sql1 = "update `pichuli` set balance = balance - 100 where `name` = 'lux' ";
			String sql2 = "update `pichuli` set balance = balance + 100 where `name` = 'jinx' ";

			PreparedStatement statement1 = conn.prepareStatement (sql1);
			PreparedStatement statement2 = conn.prepareStatement (sql2);

			try {
				statement1.executeUpdate ();
				statement2.executeUpdate ();
			} catch (SQLException e) {
				conn.rollback ();
				e.printStackTrace ();
			}
			try {
				conn.commit ();
				System.out.println ("修改余额成功！");
			} catch (SQLException e) {
				conn.rollback ();

				e.printStackTrace ();
			}
		} catch (SQLException e) {
			e.printStackTrace ();
		} finally {
			try {
				conn.setAutoCommit (true);
			} catch (SQLException e) {
				e.printStackTrace ();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace ();
			}
		}
	}

4.2. 事务的ACID属性

  • 原子性（Atomicity）
    原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  • 一致性（Consistency）
    事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  • 隔离性（Isolation）
    事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性（Durability）
    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。