private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception {
final String sql0 = "select cast(? as varchar(20)) as c\n" + "from (values (1, 'a'))";
final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))";
final Date date = Date.valueOf("2015-04-08");
final long time = date.getTime();
PreparedStatement ps;
ParameterMetaData parameterMetaData;
ResultSet resultSet;
ps = connection.prepareStatement(sql0);
parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setDate(1, date);
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("2015-04-08"));
ps.setTimestamp(1, new Timestamp(time));
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0"));
ps.setTime(1, new Time(time));
resultSet = ps.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getString(1), is("00:00:00"));
ps.close();
ps = connection.prepareStatement(sql1);
parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setDate(1, date);
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
ps.setTimestamp(1, new Timestamp(time));
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
ps.setObject(1, new java.util.Date(time));
resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
resultSet.close();
ps.close();
connection.close();
}
protected void showStatementState(String when, Statement s) throws SQLException {
super.showStatementState(when, s);
System.out.println(" getResultSetHoldability() " + rsHoldability(s.getResultSetHoldability()));
if (s instanceof PreparedStatement) {
PreparedStatement ps = (PreparedStatement) s;
ParameterMetaData psmd = ps.getParameterMetaData();
System.out.println(" Parameter Count " + psmd.getParameterCount());
for (int i = 1; i <= psmd.getParameterCount(); i++) {
System.out.println(" " + i + " type " + psmd.getParameterType(i));
}
}
}
private void checkStatementExecuteQuery(Connection connection, boolean prepare)
throws SQLException {
final String sql =
"select * from (\n" + " values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
final Statement statement;
final ResultSet resultSet;
final ParameterMetaData parameterMetaData;
if (prepare) {
final PreparedStatement ps = connection.prepareStatement(sql);
statement = ps;
parameterMetaData = ps.getParameterMetaData();
resultSet = ps.executeQuery();
} else {
statement = connection.createStatement();
parameterMetaData = null;
resultSet = statement.executeQuery(sql);
}
if (parameterMetaData != null) {
assertThat(parameterMetaData.getParameterCount(), equalTo(0));
}
final ResultSetMetaData metaData = resultSet.getMetaData();
assertEquals(2, metaData.getColumnCount());
assertEquals("C1", metaData.getColumnName(1));
assertEquals("C2", metaData.getColumnName(2));
assertTrue(resultSet.next());
assertTrue(resultSet.next());
assertTrue(resultSet.next());
assertFalse(resultSet.next());
resultSet.close();
statement.close();
connection.close();
}
private void testParameterMetaData(Connection conn) throws SQLException {
PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL");
ParameterMetaData pm = prep.getParameterMetaData();
assertEquals("java.lang.String", pm.getParameterClassName(1));
assertEquals("VARCHAR", pm.getParameterTypeName(1));
assertEquals(3, pm.getParameterCount());
assertEquals(ParameterMetaData.parameterModeIn, pm.getParameterMode(1));
assertEquals(Types.VARCHAR, pm.getParameterType(1));
assertEquals(0, pm.getPrecision(1));
assertEquals(0, pm.getScale(1));
assertEquals(ResultSetMetaData.columnNullableUnknown, pm.isNullable(1));
assertEquals(pm.isSigned(1), true);
assertThrows(ErrorCode.INVALID_VALUE_2, pm).getPrecision(0);
assertThrows(ErrorCode.INVALID_VALUE_2, pm).getPrecision(4);
prep.close();
assertThrows(ErrorCode.OBJECT_CLOSED, pm).getPrecision(1);
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE TEST3(ID INT, NAME VARCHAR(255), DATA DECIMAL(10,2))");
PreparedStatement prep1 = conn.prepareStatement("UPDATE TEST3 SET ID=?, NAME=?, DATA=?");
PreparedStatement prep2 = conn.prepareStatement("INSERT INTO TEST3 VALUES(?, ?, ?)");
checkParameter(prep1, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
checkParameter(prep1, 2, "java.lang.String", 12, "VARCHAR", 255, 0);
checkParameter(prep1, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
checkParameter(prep2, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
checkParameter(prep2, 2, "java.lang.String", 12, "VARCHAR", 255, 0);
checkParameter(prep2, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
PreparedStatement prep3 =
conn.prepareStatement("SELECT * FROM TEST3 WHERE ID=? AND NAME LIKE ? AND ?>DATA");
checkParameter(prep3, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
checkParameter(prep3, 2, "java.lang.String", 12, "VARCHAR", 0, 0);
checkParameter(prep3, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
stat.execute("DROP TABLE TEST3");
}
@Test
public void testParameterMetaData() throws Exception {
assertEquals(parameters.length, paramMetaData.getParameterCount());
for (int i = 1; i <= parameters.length; i++) {
assertEquals(ParameterMetaData.parameterNoNulls, paramMetaData.isNullable(i));
assertTrue(paramMetaData.isSigned(i));
assertEquals(0, paramMetaData.getPrecision(i));
assertEquals(0, paramMetaData.getScale(i));
assertEquals(0, paramMetaData.getParameterType(i));
assertNull(paramMetaData.getParameterTypeName(i));
assertNull(paramMetaData.getParameterClassName(i));
assertEquals(ParameterMetaData.parameterModeIn, paramMetaData.getParameterMode(i));
}
}
@Test
public void testPrepareBindExecuteFetchVarbinary() throws Exception {
final Connection connection = ljs();
final String sql = "select x'de' || ? as c from (values (1, 'a'))";
final PreparedStatement ps = connection.prepareStatement(sql);
final ParameterMetaData parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setBytes(1, new byte[] {65, 0, 66});
final ResultSet resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getBytes(1), equalTo(new byte[] {(byte) 0xDE, 65, 0, 66}));
resultSet.close();
ps.close();
connection.close();
}
private void checkPrepareBindExecuteFetch(Connection connection) throws SQLException {
final String sql = "select cast(? as integer) * 3 as c, 'x' as x\n" + "from (values (1, 'a'))";
final PreparedStatement ps = connection.prepareStatement(sql);
final ResultSetMetaData metaData = ps.getMetaData();
assertEquals(2, metaData.getColumnCount());
assertEquals("C", metaData.getColumnName(1));
assertEquals("X", metaData.getColumnName(2));
try {
final ResultSet resultSet = ps.executeQuery();
fail("expected error, got " + resultSet);
} catch (SQLException e) {
assertThat(e.getMessage(), equalTo("exception while executing query: unbound parameter"));
}
final ParameterMetaData parameterMetaData = ps.getParameterMetaData();
assertThat(parameterMetaData.getParameterCount(), equalTo(1));
ps.setInt(1, 10);
final ResultSet resultSet = ps.executeQuery();
assertTrue(resultSet.next());
assertThat(resultSet.getInt(1), equalTo(30));
assertFalse(resultSet.next());
resultSet.close();
ps.setInt(1, 20);
final ResultSet resultSet2 = ps.executeQuery();
assertFalse(resultSet2.isClosed());
assertTrue(resultSet2.next());
assertThat(resultSet2.getInt(1), equalTo(60));
assertThat(resultSet2.wasNull(), is(false));
assertFalse(resultSet2.next());
resultSet2.close();
ps.setObject(1, null);
final ResultSet resultSet3 = ps.executeQuery();
assertTrue(resultSet3.next());
assertThat(resultSet3.getInt(1), equalTo(0));
assertThat(resultSet3.wasNull(), is(true));
assertFalse(resultSet3.next());
resultSet3.close();
ps.close();
connection.close();
}
@Override
public MetaData[] getMetaData() throws DataLinkException {
try {
ParameterMetaData meta = stmt.getParameterMetaData();
int size = meta.getParameterCount();
MetaData[] metadata = new MetaData[size];
for (int i = 0; i < size; i++) {
metadata[i] =
new MetaData(
i < params.length ? params[i] : null,
SQLDataLinkFactory.getKind(meta.getParameterType(i + 1)));
}
return metadata;
} catch (SQLException ex) {
throw new DataLinkException(ex);
}
}
private void sendParameterDescription(Prepared p) throws IOException {
try {
PreparedStatement prep = p.prep;
ParameterMetaData meta = prep.getParameterMetaData();
int count = meta.getParameterCount();
startMessage('t');
writeShort(count);
for (int i = 0; i < count; i++) {
int type;
if (p.paramType != null && p.paramType[i] != 0) {
type = p.paramType[i];
} else {
type = PgServer.PG_TYPE_VARCHAR;
}
server.checkType(type);
writeInt(type);
}
sendMessage();
} catch (Exception e) {
sendErrorResponse(e);
}
}
/**
* 填充PreparedStatement
*
* @param pstmt
* @param parameterElements
* @return 返回下次填充的起始位置
* @throws SQLException
* @throws ServiceInvocationException
*/
public static int fulfillPreparedStatement(
PreparedStatement pstmt, List<Element> parameterElements, int startIndex)
throws SQLException, ServiceInvocationException {
ParameterMetaData parameterMetaData = pstmt.getParameterMetaData();
int preparedParamCount = parameterMetaData.getParameterCount();
int paramCount = parameterElements.size();
int currentParamIndex = 0;
if (preparedParamCount > 0) {
for (int i = 0; i < paramCount; i++) {
currentParamIndex = i + startIndex;
int type = parameterMetaData.getParameterType(currentParamIndex);
Element element = parameterElements.get(i);
String strValue = null;
NodeList nodeList = element.getChildNodes();
for (int k = 0; k < nodeList.getLength(); k++) {
Node node = nodeList.item(k);
if (node.getNodeType() == Node.TEXT_NODE) {
strValue = ((Text) node).getNodeValue();
}
}
switch (type) {
case java.sql.Types.CHAR:
case java.sql.Types.VARCHAR:
case java.sql.Types.LONGVARCHAR:
// case java.sql.Types.NCHAR:
// case java.sql.Types.NVARCHAR:
// case java.sql.Types.LONGNVARCHAR:
pstmt.setString(currentParamIndex, strValue);
break;
case java.sql.Types.NUMERIC:
case java.sql.Types.DECIMAL:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
if (strValue.indexOf(".") == -1) {
Long theLong = Long.valueOf(strValue);
pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(theLong));
} else {
Double d = Double.valueOf(strValue);
pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(d));
}
}
break;
case java.sql.Types.BOOLEAN:
case java.sql.Types.BIT:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setBoolean(currentParamIndex, Boolean.valueOf(strValue));
}
break;
case java.sql.Types.TINYINT:
case java.sql.Types.SMALLINT:
case java.sql.Types.INTEGER:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setInt(currentParamIndex, Integer.valueOf(strValue));
}
break;
case java.sql.Types.BIGINT:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setLong(currentParamIndex, Long.valueOf(strValue));
}
break;
case java.sql.Types.REAL:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setFloat(currentParamIndex, Float.valueOf(strValue));
}
break;
case java.sql.Types.FLOAT:
case java.sql.Types.DOUBLE:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setDouble(currentParamIndex, Double.valueOf(strValue));
}
break;
case java.sql.Types.BINARY:
case java.sql.Types.VARBINARY:
case java.sql.Types.LONGVARBINARY:
case java.sql.Types.BLOB:
case java.sql.Types.CLOB:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
byte[] content = null;
try {
content = Base64.decodeBase64(strValue.getBytes("UTF-8"));
} catch (Exception e) {
e.printStackTrace();
}
if (content == null) {
pstmt.setNull(currentParamIndex, type);
} else {
pstmt.setBytes(currentParamIndex, content);
}
}
break;
case java.sql.Types.DATE:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
java.sql.Date sqlDate = java.sql.Date.valueOf(strValue);
pstmt.setDate(currentParamIndex, sqlDate);
}
break;
case java.sql.Types.TIME:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
java.sql.Time t = java.sql.Time.valueOf(strValue);
pstmt.setTime(currentParamIndex, t);
}
break;
case java.sql.Types.TIMESTAMP:
if (StringUtils.isEmpty(strValue)) {
pstmt.setNull(currentParamIndex, type);
} else {
String dt = strValue;
if (dt.indexOf("T") >= 0) { // 如果是“YYYY-MM-DDThh:mm:ss”这种格式，则将T替换成空格
dt = dt.replace("T", " ");
}
java.sql.Timestamp ts = Timestamp.valueOf(dt);
pstmt.setTimestamp(currentParamIndex, ts);
}
break;
}
}
}
return currentParamIndex + 1;
}
public int getParameterCount() throws SQLException {
return realMetaData.getParameterCount();
}