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();
  }
Exemplo n.º 2
0
 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();
  }
Exemplo n.º 8
0
  @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);
    }
  }
Exemplo n.º 9
0
 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;
  }
Exemplo n.º 11
0
 public int getParameterCount() throws SQLException {
   return realMetaData.getParameterCount();
 }