Java JDBC基础（二）
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);// 立即使用ResultSetList<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);// 立即使用ResultSetList<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);// 创建第二个StatementStatement 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之后再使用之前获得的结果集
?
?
?
?
?
?
?
?
?
?
?
?
?
