Java JDBC基础（二）

1.不可滚动，不可更新结果集测试

package com.yli.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import com.yli.dal.util.RowMapper;

/**
 * [ResultSet不可滚动不可更新结果集测试]
 */
public class StatementTest {

	public static void main(String[] args) {

		Test1(); // 测试[一个Connection+一个Statement+一个SQL]

		// Test2(); // 测试[一个Connection+一个Statement+多个SQL]

		// Test3(); // 测试[一个Connection+一个Statement+多个SQL]

		// Test4(); // 测试ResultSet被关闭出错的情况

		// Test5(); // 测试[一个Connection+多个Statement+多个SQL]

		// Test6(); // 预编译PreparedStatement测试
	}

	/**
	 * 一个Connection创建一个Statement <br>
	 * 一个Statement执行一个SQL
	 */
	public static void Test1() {
		try {
			Connection conn;
			conn = ConnectionUtil.getConnection();
			Statement statement = conn.createStatement();
			String sql = "select * from ES_T_SHOP_AFFICHE";
			ResultSet rs = statement.executeQuery(sql);
			List<Map<String, Object>> list = RowMapper.getForList(rs);
			ConnectionUtil.close(conn);
			System.out.println(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 一个Connection创建一个Statement <br>
	 * 一个Statement执行多个SQL[查询在最后执行并返回ResultSet,得到ResultSet后立即使用]
	 */
	public static void Test2() {
		try {
			Connection conn;
			conn = ConnectionUtil.getConnection();
			Statement statement = conn.createStatement();

			// Statement[update]
			String sql = "update ES_T_SHOP_AFFICHE set AFFICHETITLE='hello' where AFFICHEID=100001";
			statement.execute(sql);

			// Statement[delete]
			sql = "delete ES_T_SHOP_AFFICHE where AFFICHEID=100002";
			statement.execute(sql);

			// Statement[select]
			sql = "select * from ES_T_SHOP_AFFICHE";
			ResultSet rs = statement.executeQuery(sql);
			// 立即使用ResultSet
			List<Map<String, Object>> list = RowMapper.getForList(rs);
			System.out.println(list);

			ConnectionUtil.close(conn);

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

	/**
	 * 一个Connection创建一个Statement <br>
	 * 一个Statement执行多个SQL[查询在最开始执行并返回ResultSet,得到ResultSet后立即使用]
	 */
	public static void Test3() {
		try {
			Connection conn;
			conn = ConnectionUtil.getConnection();
			Statement statement = conn.createStatement();

			// Statement[select]
			String sql = "select * from ES_T_SHOP_AFFICHE";
			ResultSet rs = statement.executeQuery(sql);
			// 立即使用ResultSet
			List<Map<String, Object>> list = RowMapper.getForList(rs);
			System.out.println(list);

			// Statement[update]
			sql = "update ES_T_SHOP_AFFICHE set AFFICHETITLE='admin' where AFFICHEID=100001";
			statement.execute(sql);

			// Statement[delete]
			sql = "delete ES_T_SHOP_AFFICHE where AFFICHEID=100002";
			statement.execute(sql);

			ConnectionUtil.close(conn);

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

	/**
	 * 一个Connection创建一个Statement <br>
	 * 一个Statement执行多个SQL[查询在最开始执行并返回ResultSet,得到ResultSet后没有立即使用] <br>
	 * Statement经过多次执行,再使用先前的ResultSet,会出错
	 */
	public static void Test4() {
		try {
			Connection conn;
			conn = ConnectionUtil.getConnection();
			Statement statement = conn.createStatement();

			// Statement[select]
			String sql = "select * from ES_T_SHOP_AFFICHE";
			ResultSet rs = statement.executeQuery(sql);

			// Statement[update]
			sql = "update ES_T_SHOP_AFFICHE set AFFICHETITLE='hello' where AFFICHEID=100001";
			statement.execute(sql);

			// Statement[delete]
			sql = "delete ES_T_SHOP_AFFICHE where AFFICHEID=100002";
			statement.execute(sql);

			// Statement经过多次SQL执行之后,再使用先前查询出来的ResultSet
			// 系统出错原因：因为经历过其他执行,到时ResultSet已经被关闭
			List<Map<String, Object>> list = RowMapper.getForList(rs);
			System.out.println(list);

			ConnectionUtil.close(conn);

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

	/**
	 * 一个Connection创建多个个Statement <br>
	 * 一个Statement执行多个SQL
	 */
	public static void Test5() {
		try {
			Connection conn;
			conn = ConnectionUtil.getConnection();
			Statement statement1 = conn.createStatement();

			// Statement[select]
			String sql = "select * from ES_T_SHOP_AFFICHE";
			ResultSet rs = statement1.executeQuery(sql);
			List<Map<String, Object>> list = RowMapper.getForList(rs);
			System.out.println(list);

			// Statement[update]
			sql = "update ES_T_SHOP_AFFICHE set AFFICHETITLE='haha' where AFFICHEID=100001";
			statement1.execute(sql);

			// Statement[delete]
			sql = "delete ES_T_SHOP_AFFICHE where AFFICHEID=100002";
			statement1.execute(sql);

			// 创建第二个Statement
			Statement statement2 = conn.createStatement();
			sql = "select * from ES_T_SHOP_AFFICHE";
			rs = statement2.executeQuery(sql);
			list = RowMapper.getForList(rs);
			System.out.println(list);

			ConnectionUtil.close(conn);

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

	/**
	 * PreparedStatement预编译测试<br>
	 * 至于其他效果和Statement一样
	 */
	public static void Test6() {
		try {
			Connection conn;
			conn = ConnectionUtil.getConnection();

			// Statement[select]
			String sql = "select * from ES_T_SHOP_AFFICHE where AFFICHEID=? or AFFICHETITLE=?";
			PreparedStatement preStat = conn.prepareStatement(sql);

			preStat.setInt(1, 100001);
			preStat.setString(2, "333");

			ResultSet rs = preStat.executeQuery();
			List<Map<String, Object>> list = RowMapper.getForList(rs);
			System.out.println(list);

			ConnectionUtil.close(conn);

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

 

 2.测试结果

（1）一个连接Connection可创建多个Statement

（2）一个Statement可连续执行多个SQL命令

（3）针对查询，一个Statement只能返回一个结果集，并且不能在执行其他SQL之后再使用之前获得的结果集