Java Code Examples for java.sql.PreparedStatement

The following examples show how to use java.sql.PreparedStatement. 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 want to check out the right sidebar which shows the related API usage.
Example 1
Source Project: ignite   Source File: H2CompareBigQueryTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Insert {@link CustOrder} at h2 database.
 *
 * @param o CustOrder.
 */
private void insertInDb(CustOrder o) throws SQLException {
    try (PreparedStatement st = conn.prepareStatement(
        "insert into \"custord\".CustOrder (_key, _val, orderId, rootOrderId, date, alias, archSeq, origOrderId) " +
            "values(?, ?, ?, ?, ?, ?, ?, ?)")) {
        int i = 0;

        st.setObject(++i, o.orderId);
        st.setObject(++i, o);
        st.setObject(++i, o.orderId);
        st.setObject(++i, o.rootOrderId);
        st.setObject(++i, o.date);
        st.setObject(++i, o.alias);
        st.setObject(++i, o.archSeq);
        st.setObject(++i, o.origOrderId);

        st.executeUpdate();
    }
}
 
Example 2
Source Project: phoenix   Source File: VariableLengthPKIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testNonfirstColumnGroupBy() throws Exception {
    String pTSDBTableName = generateUniqueName();
    String query = "SELECT HOST FROM "+pTSDBTableName+" WHERE INST='abc' GROUP BY HOST";
    String url = getUrl();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        initPTSDBTableValues(null, pTSDBTableName);
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("abc-def-ghi", rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 3
Source Project: gemfirexd-oss   Source File: CustPortfSoSubqueryStmt.java    License: Apache License 2.0 6 votes vote down vote up
private ResultSet getUniqQuery5(Connection conn, int whichQuery,  
    int tid, boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + uniqSelect[whichQuery]);
    stmt = conn.prepareStatement(uniqSelect[whichQuery]);      
    Log.getLogWriter().info("data used in query -- tid: "+ tid);
    stmt.setInt(1, tid);
    stmt.setInt(2, tid);
    rs = stmt.executeQuery();
  } catch (SQLException se) {
  	SQLHelper.printSQLException(se);
  	if (!isTicket42422Fixed && se.getSQLState().equalsIgnoreCase("0A000")) {
  	  Log.getLogWriter().info("get unsupported exception for union query, continue testing");
  	  success[0] = true;
  	  return null; //only suitable for cases when derby DB is available.
  	}
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 
Example 4
Source Project: gemfirexd-oss   Source File: TradePortfolioDMLStmt.java    License: Apache License 2.0 6 votes vote down vote up
protected boolean deleteFromDerbyTable(Connection dConn, int whichDelete, 
    int[]cid, int []sid, List<SQLException> exList){
  PreparedStatement stmt = getStmt(dConn, delete[whichDelete]); 
  if (stmt == null) return false;
  int tid = getMyTid();
  int count = -1;
  
  try {
    for (int i=0; i<cid.length; i++) {
    verifyRowCount.put(tid+"_delete"+i, 0);
      count = deleteFromTable(stmt, cid[i], sid[i], tid, whichDelete);
      verifyRowCount.put(tid+"_delete"+i, new Integer(count));
      
    } 
  } catch (SQLException se) {
    if (!SQLHelper.checkDerbyException(dConn, se))
      return false;
    else SQLHelper.handleDerbySQLException(se, exList); //handle the exception
  }
  return true;
}
 
Example 5
Source Project: ClaimChunk   Source File: MySQLDataHandler.java    License: MIT License 6 votes vote down vote up
@Override
@Nullable
public String getPlayerUsername(UUID player) {
    String sql = String.format("SELECT `%s` FROM `%s` WHERE `%s`=?",
            PLAYERS_IGN, PLAYERS_TABLE_NAME, PLAYERS_UUID);
    try (PreparedStatement statement = prep(claimChunk, connection, sql)) {
        statement.setString(1, player.toString());
        try (ResultSet result = statement.executeQuery()) {
            if (result.next()) return result.getString(1);
        }
    } catch (Exception e) {
        Utils.err("Failed to retrieve player username: %s", e.getMessage());
        e.printStackTrace();
    }
    return null;
}
 
Example 6
Source Project: spring4-understanding   Source File: BatchUpdateUtils.java    License: Apache License 2.0 6 votes vote down vote up
protected static void setStatementParameters(Object[] values, PreparedStatement ps, int[] columnTypes) throws SQLException {
	int colIndex = 0;
	for (Object value : values) {
		colIndex++;
		if (value instanceof SqlParameterValue) {
			SqlParameterValue paramValue = (SqlParameterValue) value;
			StatementCreatorUtils.setParameterValue(ps, colIndex, paramValue, paramValue.getValue());
		}
		else {
			int colType;
			if (columnTypes == null || columnTypes.length < colIndex) {
				colType = SqlTypeValue.TYPE_UNKNOWN;
			}
			else {
				colType = columnTypes[colIndex - 1];
			}
			StatementCreatorUtils.setParameterValue(ps, colIndex, colType, value);
		}
	}
}
 
Example 7
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
	PreparedStatement statement;
	if (getJdbcSpecific().getAutoKeyType() == AutoGeneratedKeysType.SINGLE) {
		if (columnIndexes != null) {
			statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		}else{
			logger.warn("Columns are null");
			logger.info("Returning generated keys switched off");
			statement = connection.prepareStatement(sql);
		}
	}else{
		statement = connection.prepareStatement(sql, columnIndexes);
	}
	optimizeStatement(statement);
	return statement;
}
 
Example 8
@Override
public final Serializable performInsert(
		String insertSQL,
		SharedSessionContractImplementor session,
		Binder binder) {
	try {
		// prepare and execute the insert
		PreparedStatement insert = prepare( insertSQL, session );
		try {
			binder.bindValues( insert );
			return executeAndExtract( insert, session );
		}
		finally {
			releaseStatement( insert, session );
		}
	}
	catch (SQLException sqle) {
		throw session.getJdbcServices().getSqlExceptionHelper().convert(
				sqle,
				"could not insert: " + MessageHelper.infoString( persister ),
				insertSQL
		);
	}
}
 
Example 9
Source Project: JspMyAdmin2   Source File: SideBarLogic.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * 
 * @return
 * @throws Exception
 */
public List<String> getDatabaseList() throws Exception {
	List<String> databaseList = null;

	ApiConnection apiConnection = null;
	PreparedStatement statement = null;
	ResultSet resultSet = null;
	try {
		apiConnection = getConnection();
		statement = apiConnection.getStmtSelect("SHOW DATABASES");
		resultSet = statement.executeQuery();
		databaseList = new ArrayList<String>();
		while (resultSet.next()) {
			databaseList.add(resultSet.getString(1));
		}
		Collections.sort(databaseList);
	} finally {
		close(resultSet);
		close(statement);
		close(apiConnection);
	}
	return databaseList;
}
 
Example 10
Source Project: spanner-jdbc   Source File: ExtendedModeIT.java    License: MIT License 6 votes vote down vote up
@Before
public void createAndFillTestTable() throws SQLException {
  if (tableExists("test"))
    return;
  getConnection().createStatement().execute(
      "create table test (id int64 not null, name string(100) not null) primary key (id)");
  getConnection().setAutoCommit(false);
  PreparedStatement ps =
      getConnection().prepareStatement("insert into test (id, name) values (?,?)");
  for (int i = 0; i < NUMBER_OF_ROWS; i++) {
    ps.setLong(1, i);
    ps.setString(2, String.valueOf(i));
    ps.addBatch();
  }
  ps.executeBatch();
  getConnection().commit();
}
 
Example 11
Source Project: gemfirexd-oss   Source File: TradePortfolioDMLTxStmt.java    License: Apache License 2.0 6 votes vote down vote up
protected boolean updateToDerbyTableTidListTx(Connection conn, int cid, int sid,  
		BigDecimal price, int qty, int whichUpdate, int tid) throws SQLException {
   PreparedStatement stmt = conn.prepareStatement(updateByTidList[whichUpdate]);
   int count = -1;
   Log.getLogWriter().info("update portfolio table in derby, myTid is " + getMyTid());
   Log.getLogWriter().info("update statement is " + updateByTidList[whichUpdate]);
   try {
   	count = updateToTableTidListTx(stmt, cid, sid, price, qty, whichUpdate, tid);
   } catch (SQLException se) {
     if (!SQLHelper.checkDerbyException(conn, se)) { //handles the deadlock of aborting
       Log.getLogWriter().info("detected the lock issue, will try it again");
       return false;
     } else throw se;
   }
   Log.getLogWriter().info("derby updated " + count + " rows");
   return true;
}
 
Example 12
@Override
public List<String> oldAccountsNotActivated(Date date) {
	List<String> usernames = new ArrayList<String>();
	Timestamp timeBound = new Timestamp(date.getTime());
	Connection conn = null;
	PreparedStatement stat = null;
	ResultSet res = null;
	String username = null;
	try {
		conn = this.getConnection();
		stat = conn.prepareStatement(USERNAMES_FROM_OLD_ACCOUNT_REQUESTS);
		stat.setTimestamp(1, timeBound);
		res = stat.executeQuery();
		while (res.next()) {
			username = res.getString("username");
			usernames.add(username);
		}
	} catch (Throwable t) {
		_logger.error("error extracting oldAccountsNotActivated",  t);
		throw new RuntimeException("error extracting oldAccountsNotActivated", t);
	} finally {
		closeDaoResources(res, stat, conn);
	}
	return usernames;
}
 
Example 13
Source Project: phoenix   Source File: QueryTest.java    License: BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
@Test
public void testCoerceLongToDecimal2() throws Exception {
    String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_integer <= x_decimal";
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW9, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 14
Source Project: gemfirexd-oss   Source File: TradeSecuritiesDMLStmt.java    License: Apache License 2.0 6 votes vote down vote up
protected void insertToSecuritiesFulldataset (Connection conn, int sec_id, String symbol, BigDecimal price, String exchange, int tid){
  //manually update fulldataset table for above entry.
   try{
    
     Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence deleting  the  row  from TRADE.SECURITIES_FULLDATASET with data SEC_ID:" +  sec_id );
     conn.createStatement().execute("DELETE FROM TRADE.SECURITIES_FULLDATASET  WHERE  sec_id = "  + sec_id );      

    PreparedStatement preparedInsertStmt = conn.prepareStatement("insert into trade.SECURITIES_fulldataset values (?,?,?,?,?)");          
    
    preparedInsertStmt.setInt(1, sec_id);
    preparedInsertStmt.setString(2, symbol);
    preparedInsertStmt.setBigDecimal(3, price);
    preparedInsertStmt.setString(4, exchange);       
    preparedInsertStmt.setInt(5, tid); 
   
    Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence inserting  the  row  into  TRADE.SECURITIES_FULLDATASET with data SEC_ID:" +  sec_id +  ",SYMBOL" + symbol  + ",EXCHANGE:" +  exchange + ",PRICE:" + price + ".TID:" + tid );
    preparedInsertStmt.executeUpdate();
   } catch (SQLException se) {
     Log.getLogWriter().info("Error while updating TRADE.SECURITIES_FULLDATASET table. It may cause Data inconsistency " + se.getMessage() ); 
   }
}
 
Example 15
Source Project: phoenix   Source File: PercentileIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testPercentileDiscAsc() throws Exception {
	long ts = nextTimestamp();
	String tenantId = getOrganizationId();
	initATableValues(tenantId, null, getDefaultSplits(tenantId), null, ts);

	String query = "SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY A_INTEGER ASC) FROM aTable";

	Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
	props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
			Long.toString(ts + 2)); // Execute at
									// timestamp 2
	Connection conn = DriverManager.getConnection(getUrl(), props);
	try {
		PreparedStatement statement = conn.prepareStatement(query);
		ResultSet rs = statement.executeQuery();
		assertTrue(rs.next());
		int percentile_disc = rs.getInt(1);
		assertEquals(9, percentile_disc);
		assertFalse(rs.next());
	} finally {
		conn.close();
	}
}
 
Example 16
Source Project: getting-started-java   Source File: CloudSqlDao.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public void updateBook(Book book) throws SQLException {
  final String updateBookString = "UPDATE books5 SET author = ?, createdBy = ?, createdById = ?, "
      + "description = ?, publishedDate = ?, title = ?, imageUrl = ? WHERE id = ?";
  try (Connection conn = dataSource.getConnection();
       PreparedStatement updateBookStmt = conn.prepareStatement(updateBookString)) {
    updateBookStmt.setString(1, book.getAuthor());
    updateBookStmt.setString(2, book.getCreatedBy());
    updateBookStmt.setString(3, book.getCreatedById());
    updateBookStmt.setString(4, book.getDescription());
    updateBookStmt.setString(5, book.getPublishedDate());
    updateBookStmt.setString(6, book.getTitle());
    updateBookStmt.setString(7, book.getImageUrl());
    updateBookStmt.setLong(8, book.getId());
    updateBookStmt.executeUpdate();
  }
}
 
Example 17
@Override
public void add(final TransactionLog transactionLog) {
    String sql = "INSERT INTO `transaction_log` (`id`, `transaction_type`, `data_source`, `sql`, `parameters`, `creation_time`) VALUES (?, ?, ?, ?, ?, ?);";
    try (
        Connection conn = dataSource.getConnection();
        PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
        preparedStatement.setString(1, transactionLog.getId());
        preparedStatement.setString(2, SoftTransactionType.BestEffortsDelivery.name());
        preparedStatement.setString(3, transactionLog.getDataSource());
        preparedStatement.setString(4, transactionLog.getSql());
        preparedStatement.setString(5, new Gson().toJson(transactionLog.getParameters()));
        preparedStatement.setLong(6, transactionLog.getCreationTime());
        preparedStatement.executeUpdate();
    } catch (final SQLException ex) {
        throw new TransactionLogStorageException(ex);
    }
}
 
Example 18
Source Project: cosmic   Source File: DataCenterVnetDaoImpl.java    License: Apache License 2.0 6 votes vote down vote up
@Override
@DB
//In the List<string> argument each string is a vlan. not a vlanRange.
public void add(final long dcId, final long physicalNetworkId, final List<String> vnets) {
    final String insertVnet = "INSERT INTO `cloud`.`op_dc_vnet_alloc` (vnet, data_center_id, physical_network_id) VALUES ( ?, ?, ?)";

    final TransactionLegacy txn = TransactionLegacy.currentTxn();
    try {
        txn.start();
        final PreparedStatement stmt = txn.prepareAutoCloseStatement(insertVnet);
        for (int i = 0; i <= vnets.size() - 1; i++) {
            stmt.setString(1, vnets.get(i));
            stmt.setLong(2, dcId);
            stmt.setLong(3, physicalNetworkId);
            stmt.addBatch();
        }
        stmt.executeBatch();
        txn.commit();
    } catch (final SQLException e) {
        throw new CloudRuntimeException(e.getMessage());
    }
}
 
Example 19
Source Project: HibernateDemos   Source File: BasicJdbcDemo.java    License: The Unlicense 6 votes vote down vote up
private static void insertSkill(Skill skill) throws SQLException {
	Connection conn = null;
	PreparedStatement stmt = null;
	
	try {
		conn = connection();
		
		stmt = conn.prepareStatement( "INSERT INTO skills VALUES(?, ?)" );
		stmt.setInt( 1, skill.getId() );
		stmt.setString( 2, skill.getName() );
		stmt.executeUpdate();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		if (stmt != null) {
			stmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}
}
 
Example 20
Source Project: micro-integrator   Source File: RDBMSDataHandler.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public List<ODataEntry> readTable(String tableName) throws ODataServiceFault {
    ResultSet resultSet = null;
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = initializeConnection();
        String query = "select * from " + tableName;
        statement = connection.prepareStatement(query);
        resultSet = statement.executeQuery();
        return createDataEntryCollectionFromRS(tableName, resultSet);
    } catch (SQLException e) {
        throw new ODataServiceFault(e, "Error occurred while reading entities from " + tableName + " table. :" +
                                       e.getMessage());
    } finally {
        releaseResources(resultSet, statement);
        releaseConnection(connection);
    }
}
 
Example 21
Source Project: tomee   Source File: DatabaseConfigSource.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public String getValue(final String propertyName) {
    try {
        final Connection connection = dataSource.getConnection();
        final PreparedStatement query =
                connection.prepareStatement("SELECT VALUE FROM CONFIGURATIONS WHERE NAME = ?");
        query.setString(1, propertyName);
        final ResultSet value = query.executeQuery();

        if (value.next()) {
            return value.getString(1);
        }

        DbUtils.closeQuietly(value);
        DbUtils.closeQuietly(query);
        DbUtils.closeQuietly(connection);
    } catch (final SQLException e) {
        e.printStackTrace();
    }

    return null;
}
 
Example 22
Source Project: sharding-jdbc-1.5.1   Source File: Main.java    License: Apache License 2.0 6 votes vote down vote up
private static void printHintSimpleSelect(final DataSource dataSource) throws SQLException {
    String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id";
    try (
            HintManager hintManager = HintManager.getInstance();
            Connection conn = dataSource.getConnection();
            PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
        hintManager.addDatabaseShardingValue("t_order", "user_id", 10);
        hintManager.addTableShardingValue("t_order", "order_id", 1001);
        try (ResultSet rs = preparedStatement.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getInt(1));
                System.out.println(rs.getInt(2));
                System.out.println(rs.getInt(3));
            }
        }
    }
}
 
Example 23
Source Project: phoenix   Source File: ProductMetricsIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testFilterOnTrailingKeyColumn2() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT organization_id, \"DATE\", feature FROM "+tablename+" WHERE substr(organization_id,1,3)=? AND \"DATE\" > to_date(?)";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        initTableValues(tablename, tenantId, getSplits(tenantId));
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId.substring(0,3));
        statement.setString(2, DS4);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(tenantId, rs.getString(1));
        assertEquals(D5.getTime(), rs.getDate(2).getTime());
        assertEquals(F3, rs.getString(3));
        assertTrue(rs.next());
        assertEquals(tenantId, rs.getString(1));
        assertEquals(D6, rs.getDate(2));
        assertEquals(F1, rs.getString(3));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 24
Source Project: gemfirexd-oss   Source File: TradeNetworthDMLStmtJson.java    License: Apache License 2.0 5 votes vote down vote up
protected void deleteFromGFETable(Connection gConn, int whichDelete, int cid){
  PreparedStatement stmt = getStmt(gConn, delete[whichDelete]); 
  if (SQLTest.testSecurity && stmt == null) {
  	if (SQLSecurityTest.prepareStmtException.get() != null) {
  	  SQLSecurityTest.prepareStmtException.set(null);
  	  return;
  	} else ; //need to find out why stmt is not obtained
  } //work around #43244
  if (setCriticalHeap && stmt == null) {
    return; //prepare stmt may fail due to XCL54 now
  }
  if (stmt == null && alterTableDropColumn) {
    Log.getLogWriter().info("prepare stmt failed due to missing column");
    return; //prepare stmt may fail due to alter table now
  } 
  
  int tid = getMyTid();
  
  try {
    deleteFromTable(stmt, cid, tid, whichDelete);
  } catch (SQLException se) {
    if ((se.getSQLState().equals("42500") || se.getSQLState().equals("42502"))
        && testSecurity) {
      Log.getLogWriter().info("Got the expected exception for authorization," +
         " continuing tests");
    } else if (alterTableDropColumn && se.getSQLState().equals("42X14")) {
      Log.getLogWriter().info("Got expected column not found exception in delete, continuing test");
    } else 
      SQLHelper.handleSQLException(se); //handle the exception
  }
}
 
Example 25
Source Project: CodeDefenders   Source File: DB.java    License: GNU Lesser General Public License v3.0 5 votes vote down vote up
public static int executeUpdateGetKeys(PreparedStatement stmt, Connection conn) {
    try {
        if (stmt.executeUpdate() > 0) {
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
    } catch (SQLException e) {
        logger.error("SQLException while executing Update and getting generated Keys for statement\n\t" + stmt, e);
    } finally {
        DB.cleanup(conn, stmt);
    }
    return -1;
}
 
Example 26
Source Project: phoenix   Source File: HashJoinIT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testJoinWithDifferentDateJoinKeyTypes() throws Exception {
    Connection conn = getConnection();
    String query = "SELECT \"order_id\", c.name, o.\"DATE\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o INNER JOIN "
        + getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME) + " c ON o.\"customer_id\" = c.\"customer_id\" AND o.\"DATE\" = c.\"DATE\"";
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "000000000000001");
        assertEquals(rs.getString(2), "C4");
        assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "000000000000002");
        assertEquals(rs.getString(2), "C3");
        assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "000000000000003");
        assertEquals(rs.getString(2), "C2");
        assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-25 16:45:07").getTime()));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "000000000000005");
        assertEquals(rs.getString(2), "C5");
        assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-27 09:37:50").getTime()));

        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 27
/**
 * @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#updateAssetsCreatedInLastFiscalPeriod(java.lang.Integer,
 *      java.lang.Integer)
 */
@Override
public void updateAssetsCreatedInLastFiscalPeriod(final Integer fiscalMonth, final Integer fiscalYear) {
    // If we are in the last month of the fiscal year
    if (fiscalMonth == 12) {
        if ( LOG.isInfoEnabled() ) {
            LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Starting updateAssetsCreatedInLastFiscalPeriod()");
        }
        // Getting last date of fiscal year
        final UniversityDate lastFiscalYearDate = universityDateDao.getLastFiscalYearDate(fiscalYear);
        if (lastFiscalYearDate == null) {
            throw new IllegalStateException(kualiConfigurationService.getPropertyValueAsString(KFSKeyConstants.ERROR_UNIV_DATE_NOT_FOUND));
        }

        Collection<String> movableEquipmentObjectSubTypes = parameterService.getParameterValuesAsString(Asset.class, CamsConstants.Parameters.MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES);

        // Only update assets with a object sub type code equals to any MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES.
        if (!movableEquipmentObjectSubTypes.isEmpty()) {
            getJdbcTemplate().update("UPDATE CM_CPTLAST_T SET CPTL_AST_IN_SRVC_DT=?, CPTL_AST_DEPR_DT=?, FDOC_POST_PRD_CD=? , FDOC_POST_YR=? WHERE CPTLAST_CRT_DT > ? AND FIN_OBJ_SUB_TYP_CD IN (" + buildINValues(movableEquipmentObjectSubTypes) + ")", new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setDate(1, lastFiscalYearDate.getUniversityDate());
                    ps.setDate(2, lastFiscalYearDate.getUniversityDate());
                    ps.setString(3, fiscalMonth.toString());
                    ps.setInt(4, fiscalYear);
                    ps.setDate(5, lastFiscalYearDate.getUniversityDate());
                }
            });
        }
        if ( LOG.isInfoEnabled() ) {
            LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Finished updateAssetsCreatedInLastFiscalPeriod()");
        }
    }
}
 
Example 28
Source Project: ScoreboardStats   Source File: Database.java    License: MIT License 5 votes vote down vote up
private void update(Collection<PlayerStats> stats) {
    if (stats.isEmpty()) {
        return;
    }

    //Save the stats to the database
    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement("UPDATE player_stats "
                 + "SET kills=?, deaths=?, killstreak=?, mobkills=?, last_online=CURRENT_TIMESTAMP, playername=? "
                 + "WHERE id=?")) {
        conn.setAutoCommit(false);
        for (PlayerStats stat : stats) {
            stmt.setInt(1, stat.getKills());
            stmt.setInt(2, stat.getDeaths());
            stmt.setInt(3, stat.getKillstreak());
            stmt.setInt(4, stat.getMobkills());

            stmt.setString(5, stat.getPlayername());

            stmt.setInt(6, stat.getId());
            stmt.addBatch();
        }

        stmt.executeBatch();
        conn.commit();
    } catch (Exception ex) {
        logger.error("Error updating profiles", ex);
    }
}
 
Example 29
public void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((PreparedStatement) this.wrappedStmt).setNull(parameterIndex, sqlType, typeName);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}
 
Example 30
Source Project: carbon-identity   Source File: DeviceStoreDAO.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Retrieves Device Registration metadata data from store.
 *
 * @param username The username of the Device Registration.
 * @return Collection of Device Registration.
 * @throws FIDOAuthenticatorServerException when SQL statement can not be executed.
 */
public ArrayList<String> getDeviceMetadata(String username, String tenantDomain, String userStoreDomain)
        throws FIDOAuthenticatorServerException {

    ResultSet resultSet = null;
    ArrayList<String> devicesMetadata = new ArrayList<String>();
    if (log.isDebugEnabled()) {
        log.debug("getDeviceRegistration inputs {username: " + username + ", tenantDomain: " + tenantDomain +
                  ", userStoreDomain : " + userStoreDomain +"}");
    }
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement preparedStatement = null;

    try {
        preparedStatement = connection.prepareStatement(FIDOAuthenticatorConstants.SQLQueries.GET_DEVICE_REGISTRATION_QUERY);
        preparedStatement.setInt(1, IdentityTenantUtil.getTenantId(tenantDomain));
        preparedStatement.setString(2, userStoreDomain);
        preparedStatement.setString(3, username);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String deviceRemark = resultSet.getTimestamp(FIDOAuthenticatorConstants.U2F_DEVICE_METADATA).toString();
            devicesMetadata.add(deviceRemark);
        }
    } catch (SQLException e) {
        throw new FIDOAuthenticatorServerException(
                "Error executing get device registration SQL : " +
                FIDOAuthenticatorConstants.SQLQueries.GET_DEVICE_REGISTRATION_QUERY, e
        );
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, preparedStatement);
    }

    return devicesMetadata;
}