testpreparedstatement.java
来自「非常棒的java数据库」· Java 代码 · 共 887 行 · 第 1/3 页
JAVA
887 行
check(ts.toString(), ts2.toString());
}
private void testPreparedSubquery(Connection conn) throws Exception {
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE TEST(ID IDENTITY, FLAG BIT)");
s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(0, FALSE)");
s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(1, FALSE)");
PreparedStatement u = conn.prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
PreparedStatement p = conn.prepareStatement("UPDATE TEST SET FLAG=true WHERE ID=(SELECT ?)");
p.clearParameters();
p.setLong(1, 0);
check(p.executeUpdate(), 1);
p.clearParameters();
p.setLong(1, 1);
check(p.executeUpdate(), 1);
ResultSet rs = u.executeQuery();
check(rs.next());
check(rs.getInt(1), 0);
check(rs.getBoolean(2));
check(rs.next());
check(rs.getInt(1), 1);
check(rs.getBoolean(2));
p = conn.prepareStatement("SELECT * FROM TEST WHERE EXISTS(SELECT * FROM TEST WHERE ID=?)");
p.setInt(1, -1);
rs = p.executeQuery();
checkFalse(rs.next());
p.setInt(1, 1);
rs = p.executeQuery();
check(rs.next());
s.executeUpdate("DROP TABLE IF EXISTS TEST");
}
private void testParameterMetaData(Connection conn) throws Exception {
PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL");
ParameterMetaData pm = prep.getParameterMetaData();
check(pm.getParameterClassName(1), "java.lang.String");
check(pm.getParameterTypeName(1), "VARCHAR");
check(pm.getParameterCount(), 3);
check(pm.getParameterMode(1), ParameterMetaData.parameterModeIn);
check(pm.getParameterType(1), Types.VARCHAR);
check(pm.getPrecision(1), 0);
check(pm.getScale(1), 0);
check(pm.isNullable(1), ResultSetMetaData.columnNullableUnknown);
check(pm.isSigned(1), true);
try {
pm.getPrecision(0);
error();
} catch (SQLException e) {
checkNotGeneralException(e);
}
try {
pm.getPrecision(4);
error();
} catch (SQLException e) {
checkNotGeneralException(e);
}
prep.close();
try {
pm.getPrecision(1);
error();
} catch (SQLException e) {
checkNotGeneralException(e);
}
}
private void testLikeIndex(Connection conn) throws Exception {
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
stat.execute("INSERT INTO TEST VALUES(2, 'World')");
stat.execute("create index idxname on test(name);");
PreparedStatement prep, prepExe;
prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
check(prep.getParameterMetaData().getParameterCount(), 1);
prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
prep.setString(1, "%orld");
prepExe.setString(1, "%orld");
ResultSet rs = prep.executeQuery();
rs.next();
String plan = rs.getString(1);
check(plan.indexOf("TABLE_SCAN") >= 0);
rs = prepExe.executeQuery();
rs.next();
check(rs.getString(2), "World");
checkFalse(rs.next());
prep.setString(1, "H%");
prepExe.setString(1, "H%");
rs = prep.executeQuery();
rs.next();
String plan1 = rs.getString(1);
check(plan1.indexOf("IDXNAME") >= 0);
rs = prepExe.executeQuery();
rs.next();
check(rs.getString(2), "Hello");
checkFalse(rs.next());
stat.execute("DROP TABLE IF EXISTS TEST");
}
private void testCasewhen(Connection conn) throws Exception {
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(ID INT)");
stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
PreparedStatement prep;
ResultSet rs;
prep = conn.prepareStatement("EXPLAIN SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
prep.setInt(1, 1);
rs = prep.executeQuery();
rs.next();
String plan = rs.getString(1);
trace(plan);
rs.close();
prep = conn
.prepareStatement("EXPLAIN SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID");
prep.setInt(1, 1);
rs = prep.executeQuery();
rs.next();
plan = rs.getString(1);
trace(plan);
prep = conn.prepareStatement("SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
prep.setInt(1, 1);
rs = prep.executeQuery();
check(rs.next());
check(rs.getInt(1), 1);
checkFalse(rs.next());
prep = conn
.prepareStatement("SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID");
prep.setInt(1, 1);
rs = prep.executeQuery();
check(rs.next());
check(rs.getInt(1), 1);
checkFalse(rs.next());
prep = conn.prepareStatement("SELECT * FROM TEST WHERE ? IS NULL");
prep.setString(1, "Hello");
rs = prep.executeQuery();
checkFalse(rs.next());
try {
prep = conn.prepareStatement("select ? from dual union select ? from dual");
error();
} catch (SQLException e) {
checkNotGeneralException(e);
}
prep = conn.prepareStatement("select cast(? as varchar) from dual union select ? from dual");
check(prep.getParameterMetaData().getParameterCount(), 2);
prep.setString(1, "a");
prep.setString(2, "a");
rs = prep.executeQuery();
rs.next();
check(rs.getString(1), "a");
check(rs.getString(1), "a");
checkFalse(rs.next());
stat.execute("DROP TABLE TEST");
}
private void testSubquery(Connection conn) throws Exception {
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST(ID INT)");
stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
PreparedStatement prep = conn.prepareStatement("select x.id, ? from "
+ "(select * from test where id in(?, ?)) x " + "where x.id*2 <> ?");
check(prep.getParameterMetaData().getParameterCount(), 4);
prep.setInt(1, 0);
prep.setInt(2, 1);
prep.setInt(3, 2);
prep.setInt(4, 4);
ResultSet rs = prep.executeQuery();
rs.next();
check(rs.getInt(1), 1);
check(rs.getInt(2), 0);
checkFalse(rs.next());
stat.execute("DROP TABLE TEST");
}
private void testDataTypes(Connection conn) throws Exception {
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
Statement stat = conn.createStatement();
PreparedStatement prep;
ResultSet rs;
trace("Create tables");
stat.execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)");
stat.execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
stat.execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))");
stat.execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))");
stat.execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)");
prep = conn.prepareStatement("INSERT INTO T_INT VALUES(?,?)", ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
prep.setInt(1, 1);
prep.setInt(2, 0);
prep.executeUpdate();
prep.setInt(1, 2);
prep.setInt(2, -1);
prep.executeUpdate();
prep.setInt(1, 3);
prep.setInt(2, 3);
prep.executeUpdate();
prep.setInt(1, 4);
prep.setNull(2, Types.INTEGER);
prep.executeUpdate();
prep.setInt(1, 5);
prep.setBigDecimal(2, new java.math.BigDecimal("0"));
prep.executeUpdate();
prep.setInt(1, 6);
prep.setString(2, "-1");
prep.executeUpdate();
prep.setInt(1, 7);
prep.setObject(2, new Integer(3));
prep.executeUpdate();
prep.setObject(1, "8");
// should throw an exception
prep.setObject(2, null);
// some databases don't allow calling setObject with null (no data type)
prep.executeUpdate();
prep.setInt(1, 9);
prep.setObject(2, new Integer(-4), Types.VARCHAR);
prep.executeUpdate();
prep.setInt(1, 10);
prep.setObject(2, "5", Types.INTEGER);
prep.executeUpdate();
prep.setInt(1, 11);
prep.setObject(2, null, Types.INTEGER);
prep.executeUpdate();
prep.setInt(1, 12);
prep.setBoolean(2, true);
prep.executeUpdate();
prep.setInt(1, 13);
prep.setBoolean(2, false);
prep.executeUpdate();
prep.setInt(1, 14);
prep.setByte(2, (byte) -20);
prep.executeUpdate();
prep.setInt(1, 15);
prep.setByte(2, (byte) 100);
prep.executeUpdate();
prep.setInt(1, 16);
prep.setShort(2, (short) 30000);
prep.executeUpdate();
prep.setInt(1, 17);
prep.setShort(2, (short) (-30000));
prep.executeUpdate();
prep.setInt(1, 18);
prep.setLong(2, Integer.MAX_VALUE);
prep.executeUpdate();
prep.setInt(1, 19);
prep.setLong(2, Integer.MIN_VALUE);
prep.executeUpdate();
check(stat.execute("SELECT * FROM T_INT ORDER BY ID"));
rs = stat.getResultSet();
testResultSetOrdered(rs, new String[][] { { "1", "0" }, { "2", "-1" }, { "3", "3" }, { "4", null },
{ "5", "0" }, { "6", "-1" }, { "7", "3" }, { "8", null }, { "9", "-4" }, { "10", "5" }, { "11", null },
{ "12", "1" }, { "13", "0" }, { "14", "-20" }, { "15", "100" }, { "16", "30000" }, { "17", "-30000" },
{ "18", "" + Integer.MAX_VALUE }, { "19", "" + Integer.MIN_VALUE }, });
prep = conn.prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)");
prep.setInt(1, 1);
prep.setLong(2, Long.MAX_VALUE);
prep.executeUpdate();
prep.setInt(1, 2);
prep.setLong(2, Long.MIN_VALUE);
prep.executeUpdate();
prep.setInt(1, 3);
prep.setFloat(2, 10);
prep.executeUpdate();
prep.setInt(1, 4);
prep.setFloat(2, -20);
prep.executeUpdate();
prep.setInt(1, 5);
prep.setFloat(2, 30);
prep.executeUpdate();
prep.setInt(1, 6);
prep.setFloat(2, -40);
prep.executeUpdate();
rs = stat.executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID");
checkBigDecimal(rs, new String[] { "" + Long.MAX_VALUE, "" + Long.MIN_VALUE, "10", "-20", "30", "-40" });
// getMoreResults
stat.execute("CREATE TABLE TEST(ID INT)");
stat.execute("INSERT INTO TEST VALUES(1)");
prep = conn.prepareStatement("SELECT * FROM TEST");
// just to check if it doesn't throw an exception - it may be null
prep.getMetaData();
check(prep.execute());
rs = prep.getResultSet();
checkFalse(prep.getMoreResults());
try {
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?