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