欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Java JDBC基础(二)

程序员文章站 2022-04-06 09:45:35
...

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之后再使用之前获得的结果集