Java Code Examples for java.sql.ResultSet

The following examples show how to use java.sql.ResultSet. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source Project: primefaces-blueprints   Author: sudheerj   File: AdminDAO.java    License: The Unlicense 7 votes vote down vote up
public List<Employee> getEmployeeList() throws SQLException {
	PreparedStatement ps = con
			.prepareStatement("select firstname,lastname,gender,country,city,company from blueprintsdb.employee");

	// get jobposts data from database
	ResultSet result = ps.executeQuery();

	List<Employee> list = new ArrayList<Employee>();

	while (result.next()) {
		Employee employee = new Employee();

		employee.setFirstname(result.getString("firstname"));
		employee.setLastname(result.getString("lastname"));
		employee.setGender(result.getString("gender"));
		employee.setCountry(result.getString("country"));
		employee.setCity(result.getString("city"));
		employee.setCompany(result.getString("company"));

		list.add(employee);
	}

	return list;

}
 
Example #2
Source Project: spring-basics-course-project   Author: yuriytkach   File: DBLogger.java    License: MIT License 6 votes vote down vote up
public List<Event> getAllEvents() {
    List<Event> list = jdbcTemplate.query("select * from t_event", new RowMapper<Event>() {
        @Override
        public Event mapRow(ResultSet rs, int rowNum) throws SQLException {
            Integer id = rs.getInt("id");
            Date date = rs.getDate("date");
            String msg = rs.getString("msg");
            Event event = new Event(id, new Date(date.getTime()), msg);
            return event;
        }
    });
    return list;
}
 
Example #3
Source Project: HeavenMS   Author: ronancpl   File: MapleClient.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
public int getVotePoints(){
	int points = 0;
	try {
                       Connection con = DatabaseConnection.getConnection();
		PreparedStatement ps = con.prepareStatement("SELECT `votepoints` FROM accounts WHERE id = ?");
		ps.setInt(1, accId);
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {
			points = rs.getInt("votepoints");
		}
		ps.close();
		rs.close();

                       con.close();
	} catch (SQLException e) {
                   e.printStackTrace();
	}
	votePoints = points;
	return votePoints;
}
 
Example #4
Source Project: spliceengine   Author: splicemachine   File: TableLockBasicTest.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * Test update to heap, should get exclusive table lock.
 */
public void testUpdateToHeap () throws SQLException { 
    Statement st = createStatement();
    constructHeap(st);
    
    st.execute("update heap_only set a = 1000 where a = 2");
    ResultSet rs = st.executeQuery(
            " select * from lock_table order by tabname, type "
            + "desc, mode, cnt, lockname");
    JDBC.assertFullResultSet(rs, new String[][]{
            {"SPLICE", "UserTran", "TABLE", "2", "X", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"},
    });
    commit();

    st.close();
    dropTable("heap_only");
}
 
Example #5
Source Project: phoenix   Author: forcedotcom   File: PhoenixDatabaseMetaData.java    License: BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
@Override
public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException {
    // Catalogs are not supported for schemas
    if (catalog != null && catalog.length() > 0) {
        return emptyResultSet;
    }
    StringBuilder buf = new StringBuilder("select /*+" + Hint.NO_INTRA_REGION_PARALLELIZATION + "*/ distinct " +
            "null " + TABLE_CATALOG_NAME + "," + // no catalog for tables
            TABLE_SCHEM_NAME +
            " from " + TYPE_SCHEMA_AND_TABLE + 
            " where " + COLUMN_NAME + " is null");
    if (schemaPattern != null) {
        buf.append(" and " + TABLE_SCHEM_NAME + " like '" + SchemaUtil.normalizeIdentifier(schemaPattern) + "'");
    }
    Statement stmt = connection.createStatement();
    return stmt.executeQuery(buf.toString());
}
 
Example #6
Source Project: MyBox   Author: Mararsh   File: TableGeographyCode.java    License: Apache License 2.0 6 votes vote down vote up
public static GeographyCode readCode(Connection conn, PreparedStatement statement, boolean decodeAncestors) {
        if (conn == null || statement == null) {
            return null;
        }
        try {
            GeographyCode code;
            statement.setMaxRows(1);
            try ( ResultSet results = statement.executeQuery()) {
                if (results.next()) {
                    code = readResults(results);
                } else {
                    return null;
                }
            }
            if (decodeAncestors && code != null) {
                decodeAncestors(conn, code);
            }
            return code;
        } catch (Exception e) {
            failed(e);
//            logger.debug(e.toString());
            return null;
        }
    }
 
Example #7
Source Project: stendhal   Author: arianne   File: StendhalHallOfFameDAO.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * Returns the points in the specified hall of fame.
 *
 * @param transaction
 *            Transaction
 * @param charname
 *            name of the player
 * @param fametype
 *            type of the hall of fame
 * @return points or 0 in case there is no entry
 */
public int getHallOfFamePoints(final DBTransaction transaction, final String charname, final String fametype) {
	int res = 0;
	try {
		final String query = "SELECT points FROM halloffame WHERE charname="
				+ "'[charname]' AND fametype='[fametype]'";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("charname", charname);
		params.put("fametype", fametype);

		final ResultSet result = transaction.query(query, params);
		if (result.next()) {
			res = result.getInt("points");
		}
		result.close();
	} catch (final Exception sqle) {
		logger.warn("Error reading hall of fame", sqle);
	}

	return res;
}
 
Example #8
Source Project: entando-core   Author: entando   File: AbstractEntitySearcherDAO.java    License: GNU Lesser General Public License v3.0 6 votes vote down vote up
@Override
public List<ApsEntityRecord> searchRecords(EntitySearchFilter[] filters) {
    Connection conn = null;
    List<ApsEntityRecord> records = new ArrayList<>();
    PreparedStatement stat = null;
    ResultSet result = null;
    try {
        conn = this.getConnection();
        stat = this.buildStatement(filters, false, true, conn);
        result = stat.executeQuery();
        while (result.next()) {
            ApsEntityRecord record = this.createRecord(result);
            if (!records.contains(record)) {

                records.add(record);
            }
        }
    } catch (Throwable t) {
        _logger.error("Error while loading records list", t);
        throw new RuntimeException("Error while loading records list", t);
    } finally {
        closeDaoResources(result, stat, conn);
    }
    return records;
}
 
Example #9
private void markFailed(Connection connection, Collection<Tuple2<String, String>> failedSnapshotDefinitions)
    throws Exception
{
    ResultSet resultSet = connection.createStatement(
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).executeQuery(SD_SELECT_ALL);
    while (resultSet.next())
    {
        String rscName = resultSet.getString(SD_RES_NAME);
        String snapshotName = resultSet.getString(SD_NAME);
        if (failedSnapshotDefinitions.contains(Tuples.of(rscName, snapshotName)))
        {
            resultSet.updateLong(SD_FLAGS,
                (resultSet.getLong(SD_FLAGS) & ~SD_FLAG_SUCCESSFUL) | SD_FLAG_FAILED_DEPLOYMENT
            );
            resultSet.updateRow();
        }
    }
    resultSet.close();
}
 
Example #10
Source Project: reladomo   Author: goldmansachs   File: TestRelationships.java    License: Apache License 2.0 6 votes vote down vote up
public void testRelationshipWithIntegerInOperation() throws SQLException
{
	Book book = BookFinder.findOne(BookFinder.inventoryId().eq(1));
	SupplierList suppliersList = book.getSuppliersWithSpecificId();
	suppliersList.forceResolve();
	String sql = "select count(distinct SUPPLIER_ID) from SUPPLIER_INVENTORY_ITEM " +
			"where INVENTORY_ID = 1 and SUPPLIER_ID in (?, ?)";
	Connection conn = getConnection();
	int count;
	try
	{
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, 1);
		pstmt.setInt(2, 2);
		ResultSet rs = pstmt.executeQuery();
		rs.next();
		count = rs.getInt(1);
	}
	finally
	{
           if(conn != null) conn.close();
	}
	assertEquals(suppliersList.size(), count);
}
 
Example #11
Source Project: gemfirexd-oss   Author: gemxd   File: PlatformImplBase.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Helper method for retrieving the value for a column from the given result set
 * using the type code of the column.
 * 
 * @param resultSet The result set
 * @param column    The column
 * @param idx       The value's index in the result set (starting from 1) 
 * @return The value
 */
protected Object getObjectFromResultSet(ResultSet resultSet, Column column, int idx) throws SQLException
{
    int    originalJdbcType = column.getTypeCode();
    int    targetJdbcType   = getPlatformInfo().getTargetJdbcType(originalJdbcType);
    int    jdbcType         = originalJdbcType;
    Object value            = null;

    // in general we're trying to retrieve the value using the original type
    // but sometimes we also need the target type:
    if ((originalJdbcType == Types.BLOB) && (targetJdbcType != Types.BLOB))
    {
        // we should not use the Blob interface if the database doesn't map to this type 
        jdbcType = targetJdbcType;
    }
    if ((originalJdbcType == Types.CLOB) && (targetJdbcType != Types.CLOB))
    {
        // we should not use the Clob interface if the database doesn't map to this type 
        jdbcType = targetJdbcType;
    }
    value = extractColumnValue(resultSet, null, idx, jdbcType);
    return resultSet.wasNull() ? null : value;
}
 
Example #12
Source Project: phoenix   Author: apache   File: UserDefinedFunctionsIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testListJars() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Path jarPath = new Path(util.getConfiguration().get(QueryServices.DYNAMIC_JARS_DIR_KEY));
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("list jars");
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar1.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar2.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar3.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar4.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar5.jar").toString(), rs.getString("jar_location"));
    assertTrue(rs.next());
    assertEquals(new Path(jarPath, "myjar6.jar").toString(), rs.getString("jar_location"));
    assertFalse(rs.next());
}
 
Example #13
Source Project: Plan   Author: plan-player-analytics   File: RemoveOldExtensionsTransaction.java    License: GNU Lesser General Public License v3.0 6 votes vote down vote up
private Query<Collection<Integer>> inactiveTableProviderIDsQuery() {
    String sql = SELECT + "pr." + ExtensionTableProviderTable.ID +
            FROM + ExtensionTableProviderTable.TABLE_NAME + " pr" +
            INNER_JOIN + ExtensionPluginTable.TABLE_NAME + " pl on pl." + ExtensionPluginTable.ID + "=pr." + ExtensionTableProviderTable.PLUGIN_ID +
            WHERE + ExtensionPluginTable.LAST_UPDATED + "<?" +
            AND + ExtensionPluginTable.SERVER_UUID + "=?";
    return new QueryStatement<Collection<Integer>>(sql, 100) {
        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setLong(1, deleteOlder);
            statement.setString(2, serverUUID.toString());
        }

        @Override
        public Collection<Integer> processResults(ResultSet set) throws SQLException {
            Collection<Integer> providerIds = new HashSet<>();
            while (set.next()) {
                providerIds.add(set.getInt(ExtensionProviderTable.ID));
            }
            return providerIds;
        }
    };
}
 
Example #14
Source Project: cloudstack   Author: apache   File: Upgrade222to224.java    License: Apache License 2.0 6 votes vote down vote up
private void dropIndexIfExists(Connection conn) {
    try {
        PreparedStatement pstmt = conn.prepareStatement("SHOW INDEX FROM domain WHERE KEY_NAME = 'path'");
        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {
            pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`domain` DROP INDEX `path`");
            pstmt.executeUpdate();
            s_logger.debug("Unique key 'path' is removed successfully");
        }

        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        throw new CloudRuntimeException("Unable to drop 'path' index for 'domain' table due to:", e);
    }
}
 
Example #15
Source Project: entando-components   Author: entando   File: NewsletterDAO.java    License: GNU Lesser General Public License v3.0 6 votes vote down vote up
private NewsletterContentReportVO getContentReport(String contentId, Connection conn) {
	NewsletterContentReportVO contentReport = null;
	PreparedStatement stat = null;
	ResultSet res = null;
	try {
		stat = conn.prepareStatement(LOAD_CONTENT_REPORT);
		stat.setString(1, contentId);
		res = stat.executeQuery();
		if (res.next()) {
			contentReport = this.createContentReportFromRecord(res);
		}
	} catch (Throwable t) {
		this.processDaoException(t, "Error loading content report", "getContentReport");
	} finally {
		closeDaoResources(res, stat);
	}
	return contentReport;
}
 
Example #16
Source Project: phoenix   Author: apache   File: PhoenixMetricsIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testReadMetricsForSelect() throws Exception {
    String tableName = generateUniqueName();
    long numSaltBuckets = 6;
    String ddl = "CREATE TABLE " + tableName + " (K VARCHAR NOT NULL PRIMARY KEY, V VARCHAR)" + " SALT_BUCKETS = "
            + numSaltBuckets;
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute(ddl);

    long numRows = 1000;
    long numExpectedTasks = numSaltBuckets;
    insertRowsInTable(tableName, numRows);

    String query = "SELECT * FROM " + tableName;
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    PhoenixResultSet resultSetBeingTested = rs.unwrap(PhoenixResultSet.class);
    changeInternalStateForTesting(resultSetBeingTested);
    while (resultSetBeingTested.next()) {}
    resultSetBeingTested.close();
    Set<String> expectedTableNames = Sets.newHashSet(tableName);
    assertReadMetricValuesForSelectSql(Lists.newArrayList(numRows), Lists.newArrayList(numExpectedTasks),
        resultSetBeingTested, expectedTableNames);
}
 
Example #17
Source Project: quark   Author: qubole   File: QuarkMetaResultSet.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Creates a frame containing a given number or unlimited number of rows
 * from a result set.
 */
static Meta.Frame frame(ResultSet resultSet, long offset,
                        int fetchMaxRowCount, Calendar calendar) throws SQLException {
  final ResultSetMetaData metaData = resultSet.getMetaData();
  final int columnCount = metaData.getColumnCount();
  final int[] types = new int[columnCount];
  for (int i = 0; i < types.length; i++) {
    types[i] = metaData.getColumnType(i + 1);
  }
  final List<Object> rows = new ArrayList<>();
  // Meta prepare/prepareAndExecute 0 return 0 row and done
  boolean done = fetchMaxRowCount == 0;
  for (int i = 0; fetchMaxRowCount < 0 || i < fetchMaxRowCount; i++) {
    if (!resultSet.next()) {
      done = true;
      resultSet.close();
      break;
    }
    Object[] columns = new Object[columnCount];
    for (int j = 0; j < columnCount; j++) {
      columns[j] = getValue(resultSet, types[j], j, calendar);
    }
    rows.add(columns);
  }
  return new Meta.Frame(offset, done, rows);
}
 
Example #18
Source Project: xipki   Author: xipki   File: CertStore.java    License: Apache License 2.0 6 votes vote down vote up
public boolean isHealthy() {
  final String sql = "SELECT ID FROM CA";

  try {
    PreparedStatement ps = borrowPreparedStatement(sql);

    ResultSet rs = null;
    try {
      rs = ps.executeQuery();
    } finally {
      datasource.releaseResources(ps, rs);
    }
    return true;
  } catch (Exception ex) {
    LOG.error("isHealthy(). {}: {}", ex.getClass().getName(), ex.getMessage());
    LOG.debug("isHealthy()", ex);
    return false;
  }
}
 
Example #19
Source Project: bither-desktop-java   Author: bither   File: BlockProvider.java    License: Apache License 2.0 6 votes vote down vote up
public int getBlockCount() {
    String sql = "select count(*) cnt from blocks ";
    int count = 0;
    try {
        PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
        ResultSet c = statement.executeQuery();
        if (c.next()) {
            int idColumn = c.findColumn("cnt");
            if (idColumn != -1) {
                count = c.getInt(idColumn);
            }
        }
        c.close();
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return count;
}
 
Example #20
Source Project: gemfirexd-oss   Author: gemxd   File: GenericDMLExecutor.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public DBRow.Column getRandomColumnForDML(String query, Object value, int type)
     {
  
  try {
  PreparedStatement stmt = gConn.prepareStatement(query,
      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  PrepareStatementSetter ps = new PrepareStatementSetter(stmt);
  ps.setValues(value, type);
  ResultSet rs = stmt.executeQuery();
  if (rs.next()) {
    rs.last();
    int randomRow = GenericDML.rand.nextInt(rs.getRow());
    rs.absolute(randomRow == 0 ? 1 : randomRow);
    return new DBRow.Column(rs.getMetaData().getColumnName(1), rs
        .getMetaData().getColumnType(1), rs.getObject(1));
  }
  else {
    return new DBRow.Column(null, 0, value);
  }
  
  } catch (  SQLException  se) {
    throw new TestException (" Error while retrieving a row from database " + se.getSQLState() + TestHelper.getStackTrace(se));
  }
}
 
Example #21
Source Project: xipki   Author: xipki   File: DbSchemaInfo.java    License: Apache License 2.0 6 votes vote down vote up
public DbSchemaInfo(DataSourceWrapper datasource) throws DataAccessException {
  Args.notNull(datasource, "datasource");

  final String sql = "SELECT NAME,VALUE2 FROM DBSCHEMA";

  Statement stmt = null;
  ResultSet rs = null;

  try {
    stmt = datasource.createStatement();
    if (stmt == null) {
      throw new DataAccessException("could not create statement");
    }

    rs = stmt.executeQuery(sql);
    while (rs.next()) {
      variables.put(rs.getString("NAME"), rs.getString("VALUE2"));
    }
  } catch (SQLException ex) {
    throw datasource.translate(sql, ex);
  } finally {
    datasource.releaseResources(stmt, rs);
  }
}
 
Example #22
Source Project: gemfirexd-oss   Author: gemxd   File: GeneralProcedure.java    License: Apache License 2.0 5 votes vote down vote up
protected  ResultSet[] callDerbyProcedure( Object[] inOut, boolean[] success) {    
  success[0] = true;
  try {
    return  callProcedure(dConn, inOut);
  } catch (SQLException se) {     
    if (! exceptionHandler.handleDerbyException(dConn, se) ) {      
      success[0] = false;
      return null;
    }
    }
  return null;
}
 
Example #23
Source Project: gemfirexd-oss   Author: gemxd   File: TradeBuyOrdersHdfsDataVerifier.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void map(Key key, Row value, OutputCollector<Text, TradeBuyOrdersRow> output, Reporter reporter) throws IOException {           
  try {                
    if ( ! value.getEventType().equals(Type.AFTER_DELETE)) {
        ResultSet rs = value.getRowAsResultSet();
        int oid=rs.getInt("oid");
        output.collect(new Text(Integer.toString(oid)), new TradeBuyOrdersRow(oid, rs.getInt("cid"), rs.getInt("sid"), rs.getInt("tid"), rs.getInt("qty"),  rs.getString("status"),  rs.getBigDecimal("bid"), rs.getTimestamp("ordertime")) );
    }
  } catch (SQLException se) {
    System.err.println("mapper -  -Error logging result set" + se);
    throw new  IOException(se);
  }
}
 
Example #24
Source Project: aion-germany   Author: AionGermany   File: MySQL5WeddingDAO.java    License: GNU General Public License v3.0 5 votes vote down vote up
@Override
public int loadPartnerId(final Player player) {
	Connection con = null;
	int playerId = player.getObjectId();
	int partnerId = 0;
	try {
		con = DatabaseFactory.getConnection();
		PreparedStatement stmt = con.prepareStatement(SELECT_QUERY);
		stmt.setInt(1, playerId);
		stmt.setInt(2, playerId);
		ResultSet rset = stmt.executeQuery();
		int partner1Id = 0;
		int partner2Id = 0;
		if (rset.next()) {
			partner1Id = rset.getInt("player1");
			partner2Id = rset.getInt("player2");
		}
		partnerId = playerId == partner1Id ? partner2Id : partner1Id;
		rset.close();
		stmt.close();
	}
	catch (Exception e) {
		log.error("Could not get partner for player: " + playerId + " from DB: " + e.getMessage(), e);
	}
	finally {
		DatabaseFactory.close(con);
	}
	return partnerId;
}
 
Example #25
Source Project: Online-Library-System   Author: GroverZhu   File: LibrarianDAO.java    License: GNU General Public License v2.0 5 votes vote down vote up
/**
 * 通过Librarian的名字来修改Librarian的密码
 *
 * @param name
 *            Librarian的用户名
 * @param oldPassword
 *            旧密码
 * @param newPassword
 *            新密码
 * @return 旧密码错误,返回false;修改成功返回true
 * @author zengyaoNPU
 */
public boolean changePasswordByOldPassword_NewPassword(String name, String oldPassword, String newPassword) {
	Connection conn = null;
	Statement st = null;
	ResultSet rs;
	try {
		conn = DatabaseUtil.getInstance().getConnection();
		st = conn.createStatement();
		String sql = "select * from librarian where librarian_name='" + name + "'";
		rs = st.executeQuery(sql);
		if (rs.next()) {
			String oldPw = rs.getString("librarian_password");
			if (oldPassword.equals(oldPw)) {// 检查旧密码是否匹配
				sql = "update librarian set librarian_password='" + newPassword + "' where librarian_name='" + name
						+ "'";
				int row = st.executeUpdate(sql);// 更新数据库,将新密码填入
				if (row == 0) {// 改变0行,说明没有改变,返回false
					System.out.println("--changePasswordByOldPassword_NewPassword--,row==0");
					return false;
				} else {
					return true;// 改变1行,更改密码成功
				}
			} else {
				System.out.println("密码不正确");
				return false;
			}
		} else {// 未找到数据
			return false;// 没有该用户,一般不会出现这种情况
		}

	} catch (Exception e) {
		System.out.println("--LibrarianDAO--,--changePasswordByOldPassword_NewPassword()--suffers exception");
		return false;
	}

}
 
Example #26
Source Project: snowflake-jdbc   Author: snowflakedb   File: BindingDataIT.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Binding null as all types.
 */
@Test
public void testBindNullForAllTypes() throws Throwable
{
  try (Connection connection = getConnection())
  {
    connection.createStatement().execute(
        "create or replace table TEST_BIND_ALL_TYPES(C0 string," +
        "C1 number(20, 3), C2 INTEGER, C3 double, C4 varchar(1000)," +
        "C5 string, C6 date, C7 time, C8 timestamp_ntz, " +
        "C9 timestamp_ltz, C10 timestamp_tz," +
        "C11 BINARY, C12 BOOLEAN)");

    for (SnowflakeType.JavaSQLType t : SnowflakeType.JavaSQLType.ALL_TYPES)
    {
      PreparedStatement preparedStatement = connection.prepareStatement(
          "insert into TEST_BIND_ALL_TYPES values(?, ?,?,?, ?,?,?, ?,?,?, ?,?,?)"
      );
      preparedStatement.setString(1, t.toString());
      for (int i = 2; i <= 13; ++i)
      {
        preparedStatement.setNull(i, t.getType());
      }
      preparedStatement.executeUpdate();
    }

    ResultSet result = connection.createStatement().executeQuery("select * from TEST_BIND_ALL_TYPES");
    while (result.next())
    {
      String testType = result.getString(1);
      for (int i = 2; i <= 13; ++i)
      {
        assertNull(String.format("Java Type: %s is not null", testType), result.getString(i));
      }
    }
  }
}
 
Example #27
Source Project: spliceengine   Author: splicemachine   File: WithStatementIT.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
@Test
public void testSimpleWithStatementWithAggregate() throws Exception {
    ResultSet rs = methodWatcher.executeQuery("with footest as " +
            "(select count(*) as count, col2 from foo group by col2) " +
            "select foo2.col1, count from foo2 " +
            "inner join footest on foo2.col1 = footest.col2"
    );
    Assert.assertTrue("with join algebra incorrect",rs.next());
    Assert.assertEquals("Wrong Count", 1, rs.getInt(1));
    Assert.assertEquals("Wrong Join Column" , 5, rs.getInt(2));
    Assert.assertFalse("with join algebra incorrect",rs.next());
}
 
Example #28
Source Project: weasis-pacs-connector   Author: nroduit   File: DbQueryConfiguration.java    License: Eclipse Public License 2.0 5 votes vote down vote up
private static LocalDateTime getLocalDateTimeFromTimeStamp(ResultSet resultSet, String field) throws SQLException {
    Timestamp timestamp = resultSet.getTimestamp(field);
    if (timestamp != null) {
        return timestamp.toLocalDateTime();
    }
    return null;
}
 
Example #29
Source Project: spliceengine   Author: splicemachine   File: BigDataTest.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * Various tests for JIRA-614: handling of rows which span QRYDTA blocks. What happens
 * when the SplitQRYDTA has to span 3+ blocks.
 * 
 * @throws SQLException
 */
public void testSpanQRYDTABlocks() throws SQLException {
    int[] lens = { 32672, 32672, 32672, 32672, };
    boolean[] useClob = { false, false, false, false, };
    createTable(BIG_TABLE_NAME, lens, useClob);

    String[] sa = { "a", "b", "c", "d", };
    insertOneRow(BIG_TABLE_NAME, sa, lens);

    String[] row = getStringArray(sa, lens);
    String[][] expected = { row, };
    validTable(expected, BIG_TABLE_NAME);

    insertOneRow(BIG_TABLE_NAME, sa, lens);
    insertOneRow(BIG_TABLE_NAME, sa, lens);

    expected = new String[][] { row, row, row, };
    validTable(expected, BIG_TABLE_NAME);

    String sql1 = getSelectSql(BIG_TABLE_NAME);
    Statement st = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = st.executeQuery(sql1);
    assertEquals("Before operation, row No. is 0.", 0, rs.getRow());
    rs.first();
    assertEquals("After calling first(), row No. is 1.", 1, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.next();
    assertEquals("After calling next(), row No. is 2.", 2, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.previous();
    assertEquals("After calling previous(), row No. is 1.", 1, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.last();
    assertEquals("After calling last(), row No. is 3.", 3, rs.getRow());
    validSingleRow(row, useClob, rs);
    rs.close();
    st.close();
}
 
Example #30
Source Project: gemfirexd-oss   Author: gemxd   File: ProcedureTestDUnit.java    License: Apache License 2.0 5 votes vote down vote up
public void testDAPLocalBug() throws Exception {
  startVMs(1, 2);
  
  Connection conn = TestUtil.getConnection();
  Statement st = conn.createStatement();
  st.execute("CREATE TABLE APP.EMP (ID VARCHAR(10) NOT NULL, NAME VARCHAR(25), CONSTRAINT EMP_PK PRIMARY KEY (ID)) PARTITION BY PRIMARY KEY REDUNDANCY 1");
  //st.execute("CREATE TABLE APP.EMP (ID VARCHAR(10) NOT NULL, NAME VARCHAR(25), CONSTRAINT EMP_PK PRIMARY KEY (ID)) PARTITION BY column(NAME) REDUNDANCY 1");
  st.execute("insert into APP.EMP values('1', 'one'), ('2', 'two'), ('3', 'three')");
  st.execute("CREATE PROCEDURE TESTLOCAL(loc INT) "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
      + ProcedureTestDUnit.class.getName() + ".testLocal' "
      + "DYNAMIC RESULT SETS 1");
  CallableStatement cs = conn.prepareCall(
  "CALL TESTLOCAL(?) on table APP.EMP");
  cs.setInt(1, 3);
  cs.execute();
  
  int rsIndex = 0;
  int cnt = 0;
  do {
    ++rsIndex;
    ResultSet rs = cs.getResultSet();
    while (rs.next()) {
      cnt++;

      System.out.println("KN: " + rs.getObject(1) + ", " + rs.getObject(2));
      
    }
  } while (cs.getMoreResults());
  assertEquals(1, rsIndex);
  assertEquals(1, cnt);
  conn.close();
}