List of usage examples for java.sql PreparedStatement execute
boolean execute() throws SQLException;
PreparedStatement object, which may be any kind of SQL statement. From source file:lineage2.gameserver.model.pledge.SubUnit.java
/** * Method setName./*from w w w . ja v a 2 s.com*/ * @param name String * @param updateDB boolean */ public void setName(String name, boolean updateDB) { _name = name; if (updateDB) { Connection con = null; PreparedStatement statement = null; try { con = DatabaseFactory.getInstance().getConnection(); statement = con.prepareStatement("UPDATE clan_subpledges SET name=? WHERE clan_id=? and type=?"); statement.setString(1, _name); statement.setInt(2, _clan.getClanId()); statement.setInt(3, _type); statement.execute(); } catch (Exception e) { _log.error("Exception: " + e, e); } finally { DbUtils.closeQuietly(con, statement); } } }
From source file:com.chenxin.authority.common.logback.DBAppender.java
@SuppressWarnings("rawtypes") protected void insertProperties(Map<String, String> mergedMap, Connection connection, long eventId) throws SQLException { Set propertiesKeys = mergedMap.keySet(); // TODO:add chenxin ?logging_event_property if (propertiesKeys.size() < -1) { PreparedStatement insertPropertiesStatement = connection.prepareStatement(insertPropertiesSQL); for (Iterator i = propertiesKeys.iterator(); i.hasNext();) { String key = (String) i.next(); String value = mergedMap.get(key); insertPropertiesStatement.setLong(1, eventId); insertPropertiesStatement.setString(2, key); insertPropertiesStatement.setString(3, value); if (cnxSupportsBatchUpdates) { insertPropertiesStatement.addBatch(); } else { insertPropertiesStatement.execute(); }//w ww. ja v a 2s.c om } if (cnxSupportsBatchUpdates) { insertPropertiesStatement.executeBatch(); } insertPropertiesStatement.close(); insertPropertiesStatement = null; } }
From source file:com.threecrickets.prudence.cache.SqlCache.java
public void prune() { // Note that this will not discard locks try {// w ww. java 2 s . co m Connection connection = connect(); if (connection == null) return; try { String sql = "DELETE FROM " + cacheTableName + " WHERE expiration_date<?"; PreparedStatement statement = connection.prepareStatement(sql); try { statement.setTimestamp(1, new Timestamp(System.currentTimeMillis())); if (!statement.execute()) logger.fine("Pruned " + statement.getUpdateCount()); } finally { statement.close(); } } finally { connection.close(); } } catch (SQLException x) { logger.log(Level.WARNING, "Could not prune", x); } }
From source file:com.wso2telco.workflow.dao.WorkflowStatsDbService.java
public void insertAppApprovalAuditRecord(ApplicationApprovalAuditRecord record) throws SQLException, BusinessException { Connection conn = null;//from w w w . ja v a2 s. c om PreparedStatement ps = null; try { conn = dbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); StringBuilder query = new StringBuilder(); query.append("INSERT INTO app_approval_audit (APP_NAME, APP_CREATOR, APP_STATUS, "); query.append("APP_APPROVAL_TYPE, COMPLETED_BY_ROLE, COMPLETED_BY_USER) "); query.append("VALUES (?, ?, ?, ?, ?, ?)"); ps = conn.prepareStatement(query.toString()); ps.setString(1, record.getAppName()); ps.setString(2, record.getAppCreator()); ps.setString(3, record.getAppStatus()); ps.setString(4, record.getAppApprovalType()); ps.setString(5, record.getCompletedByRole()); ps.setString(6, record.getCompletedByUser()); ps.execute(); } catch (SQLException e) { throw new SQLException(); } catch (Exception e) { throw new BusinessException(GenaralError.UNDEFINED); } finally { dbUtils.closeAllConnections(ps, conn, null); } }
From source file:com.transcend.rds.worker.CreateDBInstanceReadReplicaActionWorker.java
/** * createDBInstanceReadReplica ******************************************* * Creates a read replica for a named master database instance All of the * characteristics of the read replica default to the characteristics of the * the master instance with the exception of InstanceClass, * AvailabilityZone,Port and AutoMinorVersionUpgrade that can be provided by * the user. Request: SourceDBInstanceIdentifier (R) DBInstanceIdentifier * for the read replica (R) DBInstanceClass AvailabilityZone Port * AutoMinorVersionUpgrade Response: Full details of new DBInstance created * Exceptions: DBIInstanceAlreadyExists DBInstanceNotFound * DBParameterGroupNotFound DBSecurityGroupNotFound InstanceQuotaExceeded * InsufficientDBInstanceCapacity InvalidDBInstanceState * StorageQuotaExceeded Processing 1. Confirm that source DBInstance exists * 2. Determined that requested DBInstance replica doesn't already exist for * that user 3. Confirm quotas have not been exceeded (instance, * availabilityZone, storage) 4. Validate and insert the DBInstance and * associated records 5. Call the instance manager to provision the read * replica 6. Return response giving details of newly created replica * instance including end point.// ww w. ja v a 2 s .c o m */ @Override protected CreateDBInstanceReadReplicaActionResultMessage doWork0( CreateDBInstanceReadReplicaActionRequestMessage req, ServiceRequestContext context) throws Exception { final Session sess = HibernateUtil.newSession(); DBInstance dbInst = null; try { sess.beginTransaction(); final AccountBean ac = context.getAccountBean(); final long userId = ac.getId(); final boolean autoUpgrade = req.getAutoMinorVersionUpgrade(); String avZone = req.getAvailabilityZone(); String DBInstanceClass = req.getDbInstanceClass(); final String DBInstanceId = req.getDbInstanceIdentifier(); int port = req.getPort(); final String sourceDBInstanceId = req.getSourceDBInstanceIdentifier(); if (sourceDBInstanceId == null || "".equals(sourceDBInstanceId)) { throw QueryFaults.MissingParameter( "SourceDBInstanceIdentifier must be supplied for CreateDBInstanceReadReplica request."); } if (DBInstanceId == null || "".equals(DBInstanceId)) { throw QueryFaults.MissingParameter( "DBInstanceIdentifier must be supplied for CreateDBInstanceReadReplica request."); } final RdsDbinstance source = InstanceEntity.selectDBInstance(sess, sourceDBInstanceId, userId); if (source == null || "".equals(source)) { throw RDSQueryFaults.DBInstanceNotFound(sourceDBInstanceId + " does not exist."); } if (!source.getDbinstanceStatus().equals(RDSUtilities.STATUS_AVAILABLE)) { throw RDSQueryFaults.InvalidDBInstanceState(); } if (port == -1) { logger.debug("request did not include port; port value is set with " + source.getPort() + " from the source DBInstance."); port = source.getPort(); } if (DBInstanceClass == null || "".equals(DBInstanceClass)) { logger.debug("request did not include DBInstanceClass; DBInstanceClass value is set with " + source.getDbinstanceClass() + " from the source DBInstance."); DBInstanceClass = source.getDbinstanceClass(); } if (avZone == null || "".equals(avZone)) { logger.debug( "AvailabilityZone is not included in the request; it is set to the default zone of the account: " + ac.getDefZone()); avZone = ac.getDefZone(); } logger.debug("Preparing the request for CreateDBInstance"); final CreateDBInstanceActionRequestMessage.Builder createDBInstanceReq = CreateDBInstanceActionRequestMessage .newBuilder(); createDBInstanceReq.setAllocatedStorage(source.getAllocatedStorage()); createDBInstanceReq.setAutoMinorVersionUpgrade(autoUpgrade); createDBInstanceReq.setAvailabilityZone(avZone); createDBInstanceReq.setBackupRetentionPeriod(source.getBackupRetentionPeriod()); createDBInstanceReq.setDbInstanceClass(DBInstanceClass); createDBInstanceReq.setDbInstanceIdentifier(DBInstanceId); createDBInstanceReq.setDbParameterGroupName(source.getDbParameterGroup()); final List<RdsDbsecurityGroup> dbSecGrps = source.getSecurityGroups(); final LinkedList<String> dbSecGrpNames = new LinkedList<String>(); for (final RdsDbsecurityGroup secGrp : dbSecGrps) { dbSecGrpNames.add(secGrp.getDbsecurityGroupName()); } createDBInstanceReq.addAllDbSecurityGroups(dbSecGrpNames); createDBInstanceReq.setEngine(source.getEngine()); createDBInstanceReq.setEngineVersion(source.getEngineVersion()); createDBInstanceReq.setLicenseModel(source.getLicenseModel()); createDBInstanceReq.setMasterUsername(source.getMasterUsername()); createDBInstanceReq.setMasterUserPassword(source.getMasterUserPassword()); createDBInstanceReq.setMultiAZ(false); createDBInstanceReq.setPort(port); createDBInstanceReq.setPreferredBackupWindow(source.getPreferredBackupWindow()); createDBInstanceReq.setPreferredMaintenanceWindow(source.getPreferredMaintenanceWindow()); logger.debug("Request: " + createDBInstanceReq.toString()); logger.debug("Calling CreateDBInstance..."); final CreateDBInstanceActionWorker createAction = new CreateDBInstanceActionWorker(); dbInst = createAction.createDBInstance(createDBInstanceReq.buildPartial(), context, true); logger.debug("Adding another authorization to the underlying ec2 security group"); final String internal = "rds-" + ac.getId() + "-" + source.getDbinstanceId() + "-" + source.getPort(); final List<RdsDbsecurityGroup> secGrps = SecurityGroupEntity.selectAllSecurityGroups(sess, internal, ac.getId(), null, 0); if (secGrps.size() != 1) { throw RDSQueryFaults.InternalFailure(); } final String rds_host = Appctx.getBean("internalServiceIp"); final String RdsServerCidrip = rds_host + "/32"; final RdsDbsecurityGroup masterSecGrp = secGrps.get(0); final List<RdsIPRangeBean> ips = masterSecGrp.getIPRange(sess); boolean authorized = false; for (final RdsIPRangeBean ip : ips) { if (ip.getCidrip().equals(RdsServerCidrip)) { authorized = true; logger.debug("Authorization already exists for " + RdsServerCidrip); } } final int port0 = source.getPort(); if (!authorized) { logger.debug("Authorizing ingress for " + RdsServerCidrip + " to access the source DBInstance."); final CallStruct callEc2SecGrp = new CallStruct(); callEc2SecGrp.setAc(AccountUtil.toAccount(ac)); callEc2SecGrp.setCtx(new TemplateContext(null)); callEc2SecGrp.setName(internal); callEc2SecGrp.setStackId("rds." + ac.getId() + "." + sourceDBInstanceId); final Map<String, Object> props = new HashMap<String, Object>(); props.put(Constants.AVAILABILITYZONE, ac.getDefZone()); props.put(Constants.GROUPNAME, internal); props.put(Constants.CIDRIP, RdsServerCidrip); props.put(Constants.SOURCESECURITYGROUPNAME, null); // SourceSecurityGroupOwnerId is not required props.put(Constants.SOURCESECURITYGROUPOWNERID, null); // hardcoded values below props.put(Constants.FROMPORT, port0); props.put(Constants.TOPORT, port0); props.put(Constants.IPPROTOCOL, Constants.DEFAULT_RDS_PROTOCOL); callEc2SecGrp.setProperties(props); callEc2SecGrp.setType(SecurityGroupIngress.TYPE); final SecurityGroupIngress provider0 = new SecurityGroupIngress(); try { provider0.create(callEc2SecGrp); } catch (final AmazonServiceException e) { logger.debug(e.getMessage()); } catch (final AmazonClientException e) { logger.debug(e.getMessage()); } final RdsIPRangeBean newAuth = new RdsIPRangeBean(masterSecGrp.getId(), RdsServerCidrip); ips.add(newAuth); sess.save(newAuth); } // modify the source DBInstance's status and commit the transaction source.setDbinstanceStatus(RDSUtilities.STATUS_MODIFYING); source.getReplicas().add(DBInstanceId); sess.save(source); sess.getTransaction().commit(); final Connection master = getConnection("root", source.getMasterUserPassword(), source.getEngine().toLowerCase(), source.getAddress(), source.getPort()); logger.debug("Checking to see if the source DBInstance has RDS replication user already..."); final String checkPermission = "SELECT User from mysql.user"; final Statement check = master.createStatement(); final ResultSet existingGrant = check.executeQuery(checkPermission); boolean exist = false; while (existingGrant.next()) { final String user = existingGrant.getString("User"); logger.debug("User: " + user); if (user.equals(RDS_Constants.RDS_REPLICATION_USER)) { exist = true; } } // create a new user and grant replication privilege if (!exist) { logger.debug("Replicaion user for RDS does not exist; creating a replication user..."); final String grantPermission = "GRANT REPLICATION SLAVE ON *.* TO \'" + RDS_Constants.RDS_REPLICATION_USER + "\'@\'%\' IDENTIFIED BY \'" + RDS_Constants.RDS_REPLICATION_PASSWORD + "\'"; final PreparedStatement grant = master.prepareStatement(grantPermission); grant.execute(); } logger.debug("Flushing tables with read lock on the source DBInstance..."); final String flushTables = "FLUSH TABLES WITH READ LOCK"; final Statement flushTablesAndLock = master.createStatement(); flushTablesAndLock.execute(flushTables); logger.debug("Getting the master status"); final String getMasterStatus = "SHOW MASTER STATUS"; final Statement queryMasterStatus = master.createStatement(); final ResultSet masterStatus = queryMasterStatus.executeQuery(getMasterStatus); String masterFile = null; int position = -1; while (masterStatus.next()) { masterFile = masterStatus.getString("File"); position = masterStatus.getInt("Position"); // ignore Binlog_Do_DB and Binlog_Ignore_DB for now } logger.debug("Master file is " + masterFile + " and the position is set at " + position); if (masterFile == null || position == -1) { RDSQueryFaults.InternalFailure("Master status could not be retrieved from the source DBInstance."); } logger.debug("Unlocking the tables..."); final String unlockTables = "UNLOCK TABLES"; final Statement unlock = master.createStatement(); unlock.execute(unlockTables); logger.debug("Successfully unlocked the tables."); logger.debug("Close the connection to the source DBInstance."); master.close(); logger.debug("Updating the databag to run the replication_server.rb recipe"); final String task = "mysqldump"; final String target = "?"; final String databagName = "rds-" + ac.getId() + "-" + source.getDbinstanceId(); final String replication_item = "{\"Task\":\"" + task + "\", " + "\"TargetHostname\":\"" + target + "\"}"; ChefUtil.createDatabagItem(databagName, "Replication"); ChefUtil.putDatabagItem(databagName, "Replication", replication_item); logger.debug( "Starting a new thread to wait for read replica to spin up while returning the response message."); final String DBInstanceId0 = DBInstanceId; final String sourceDBInstanceId0 = sourceDBInstanceId; final String avZone0 = avZone; final String masterFile0 = masterFile; final int position0 = position; final int port1 = port; final Executable r = new ExecutorHelper.Executable() { @Override public void run() { HibernateUtil.withNewSession(new HibernateUtil.Operation<Object>() { @Override public Object ex(final Session s, final Object... as) throws Exception { replicationHelper(s, ac, DBInstanceId0, sourceDBInstanceId0, avZone0, port0, port1, masterFile0, position0); return null; } }); } }; ExecutorHelper.execute(r); } catch (final ErrorResponse rde) { sess.getTransaction().rollback(); throw rde; } catch (final Exception e) { e.printStackTrace(); sess.getTransaction().rollback(); final String msg = "CreateDBInstanceReadReplica: Class: " + e.getClass() + "Msg:" + e.getMessage(); logger.error(msg); throw RDSQueryFaults.InternalFailure(); } finally { sess.close(); } CreateDBInstanceReadReplicaActionResultMessage.Builder resp = CreateDBInstanceReadReplicaActionResultMessage .newBuilder(); resp.setDbInstance(dbInst); return resp.buildPartial(); }
From source file:com.chiralbehaviors.CoRE.kernel.Bootstrap.java
public void insert(WellKnownStatusCode wko) throws SQLException { PreparedStatement s = connection.prepareStatement(String.format( "INSERT into %s (id, name, description, propagate_children, updated_by) VALUES (?, ?, ?, ?, ?)", wko.tableName()));/*from w w w . j a v a 2s .c o m*/ try { s.setString(1, wko.id()); s.setString(2, wko.wkoName()); s.setString(3, wko.description()); s.setInt(4, wko == WellKnownStatusCode.UNSET ? Ruleform.TRUE : Ruleform.FALSE); s.setString(5, WellKnownAgency.CORE.id()); s.execute(); } catch (SQLException e) { throw new SQLException(String.format("Unable to insert %s", wko), e); } }
From source file:net.solarnetwork.node.dao.jdbc.AbstractJdbcDao.java
/** * Persist a domain object, without using auto-generated keys. * /* ww w. j av a 2 s . com*/ * @param obj * the domain object to persist * @param sqlInsert * the SQL insert statement to use * @return the primary key created for the domain object */ protected Long storeDomainObjectWithoutAutogeneratedKeys(final T obj, final String sqlInsert) { Object result = getJdbcTemplate().execute(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sqlInsert); setStoreStatementValues(obj, ps); return ps; } }, new PreparedStatementCallback<Object>() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { ps.execute(); int count = ps.getUpdateCount(); if (count == 1 && ps.getMoreResults()) { ResultSet rs = ps.getResultSet(); if (rs.next()) { return rs.getObject(1); } } return null; } }); if (result instanceof Long) { return (Long) result; } else if (result instanceof Number) { return Long.valueOf(((Number) result).longValue()); } if (log.isWarnEnabled()) { log.warn("Unexpected (non-number) primary key returned: " + result); } return null; }
From source file:com.mirth.connect.server.userutil.DatabaseConnection.java
/** * Executes a prepared INSERT/UPDATE statement on the database and returns the row count. * //w w w . j a va2 s . c om * @param expression * The prepared statement to be executed. * @param parameters * The parameters for the prepared statement. * @return A count of the number of updated rows. * @throws SQLException */ public int executeUpdate(String expression, List<Object> parameters) throws SQLException { PreparedStatement statement = null; try { statement = connection.prepareStatement(expression); logger.debug("executing prepared statement:\n" + expression); ListIterator<Object> iterator = parameters.listIterator(); while (iterator.hasNext()) { int index = iterator.nextIndex() + 1; Object value = iterator.next(); logger.debug("adding parameter: index=" + index + ", value=" + value); statement.setObject(index, value); } if (statement.execute()) { return -1; } else { return statement.getUpdateCount(); } } catch (SQLException e) { throw e; } finally { DbUtils.closeQuietly(statement); } }
From source file:com.commander4j.db.JDBPrinters.java
public boolean create() { logger.debug("create [" + getPrinterID() + " - " + getGroupID() + "]"); boolean result = false; try {//ww w . ja va 2s . c o m PreparedStatement stmtupdate; stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement( Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBPrinters.create")); stmtupdate.setString(1, getPrinterID()); stmtupdate.setString(2, getGroupID()); stmtupdate.execute(); stmtupdate.clearParameters(); Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit(); stmtupdate.close(); update(); result = true; } catch (SQLException e) { setErrorMessage(e.getMessage()); } return result; }
From source file:ke.co.tawi.babblesms.server.persistence.notification.NotificationDAO.java
/** * // w ww . ja v a 2s.c o m * @param notification * @return whether the action was successful or not */ @Override public boolean putNotification(Notification notification) { boolean success = true; Connection conn = null; PreparedStatement pstmt = null; try { conn = dbCredentials.getConnection(); pstmt = conn.prepareStatement( "INSERT INTO Notification (Uuid, origin, ShortDesc, LongDesc) " + "VALUES (?,?,?,?);"); pstmt.setString(1, notification.getUuid()); pstmt.setString(2, notification.getOrigin()); pstmt.setString(3, notification.getShortDesc()); pstmt.setString(4, notification.getLongDesc()); pstmt.execute(); } catch (SQLException e) { logger.error("SQL Exception when trying to put: " + notification); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { } } if (conn != null) { try { conn.close(); } catch (SQLException e) { } } } return success; }