Search in sources :

Example 26 with FixedQuerySender

use of nl.nn.adapterframework.jdbc.FixedQuerySender in project iaf by ibissource.

the class LoadDatabaseSchedulesJob method execute.

@Override
public void execute(IbisManager ibisManager) {
    Map<JobKey, IbisJobDetail> databaseJobDetails = new HashMap<JobKey, IbisJobDetail>();
    Scheduler scheduler = null;
    SchedulerHelper sh = getApplicationContext().getBean(SchedulerHelper.class);
    try {
        scheduler = sh.getScheduler();
        // Fill the databaseJobDetails Map with all IbisJobDetails that have been stored in the database
        Set<JobKey> jobKeys = scheduler.getJobKeys(GroupMatcher.anyJobGroup());
        for (JobKey jobKey : jobKeys) {
            IbisJobDetail detail = (IbisJobDetail) scheduler.getJobDetail(jobKey);
            if (detail.getJobType() == JobType.DATABASE) {
                databaseJobDetails.put(detail.getKey(), detail);
            }
        }
    } catch (SchedulerException e) {
        getMessageKeeper().add("unable to retrieve jobkeys from scheduler", e);
    }
    // Get all IbisSchedules that have been stored in the database
    FixedQuerySender qs = SpringUtils.createBean(getApplicationContext(), FixedQuerySender.class);
    qs.setDatasourceName(JndiDataSourceFactory.GLOBAL_DEFAULT_DATASOURCE_NAME);
    qs.setQuery("SELECT COUNT(*) FROM IBISSCHEDULES");
    try {
        qs.configure();
        qs.open();
        try (Connection conn = qs.getConnection()) {
            try (PreparedStatement stmt = conn.prepareStatement("SELECT JOBNAME,JOBGROUP,ADAPTER,RECEIVER,CRON,EXECUTIONINTERVAL,MESSAGE,LOCKER,LOCK_KEY FROM IBISSCHEDULES")) {
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        String jobName = rs.getString("JOBNAME");
                        String jobGroup = rs.getString("JOBGROUP");
                        String adapterName = rs.getString("ADAPTER");
                        String javaListener = rs.getString("RECEIVER");
                        String cronExpression = rs.getString("CRON");
                        int interval = rs.getInt("EXECUTIONINTERVAL");
                        String message = rs.getString("MESSAGE");
                        boolean hasLocker = rs.getBoolean("LOCKER");
                        String lockKey = rs.getString("LOCK_KEY");
                        JobKey key = JobKey.jobKey(jobName, jobGroup);
                        Adapter adapter = ibisManager.getRegisteredAdapter(adapterName);
                        if (adapter == null) {
                            getMessageKeeper().add("unable to add schedule [" + key + "], adapter [" + adapterName + "] not found");
                            continue;
                        }
                        // Create a new JobDefinition so we can compare it with existing jobs
                        DatabaseJob jobdef = SpringUtils.createBean(adapter.getApplicationContext(), DatabaseJob.class);
                        jobdef.setCronExpression(cronExpression);
                        jobdef.setName(jobName);
                        jobdef.setInterval(interval);
                        jobdef.setJobGroup(jobGroup);
                        jobdef.setAdapterName(adapterName);
                        jobdef.setJavaListener(javaListener);
                        jobdef.setMessage(message);
                        if (hasLocker) {
                            Locker locker = SpringUtils.createBean(getApplicationContext(), Locker.class);
                            locker.setName(lockKey);
                            locker.setObjectId(lockKey);
                            locker.setDatasourceName(JndiDataSourceFactory.GLOBAL_DEFAULT_DATASOURCE_NAME);
                            jobdef.setLocker(locker);
                        }
                        try {
                            jobdef.configure();
                        } catch (ConfigurationException e) {
                            getMessageKeeper().add("unable to configure DatabaseJobDef [" + jobdef + "] with key [" + key + "]", e);
                        }
                        // If the job is found, find out if it is different from the existing one and update if necessarily
                        if (databaseJobDetails.containsKey(key)) {
                            IbisJobDetail oldJobDetails = databaseJobDetails.get(key);
                            if (!oldJobDetails.compareWith(jobdef)) {
                                log.debug("updating DatabaseSchedule [" + key + "]");
                                try {
                                    sh.scheduleJob(jobdef);
                                } catch (SchedulerException e) {
                                    getMessageKeeper().add("unable to update schedule [" + key + "]", e);
                                }
                            }
                            // Remove the key that has been found from the databaseJobDetails Map
                            databaseJobDetails.remove(key);
                        } else {
                            // The job was not found in the databaseJobDetails Map, which indicates it's new and has to be added
                            log.debug("add DatabaseSchedule [" + key + "]");
                            try {
                                sh.scheduleJob(jobdef);
                            } catch (SchedulerException e) {
                                getMessageKeeper().add("unable to add schedule [" + key + "]", e);
                            }
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        // Only catch database related exceptions!
        getMessageKeeper().add("unable to retrieve schedules from database", e);
    } finally {
        qs.close();
    }
    // Loop through all remaining databaseJobDetails, which were not present in the database. Since they have been removed, unschedule them!
    for (JobKey key : databaseJobDetails.keySet()) {
        log.debug("delete DatabaseSchedule [" + key + "]");
        try {
            scheduler.deleteJob(key);
        } catch (SchedulerException e) {
            getMessageKeeper().add("unable to remove schedule [" + key + "]", e);
        }
    }
}
Also used : SchedulerException(org.quartz.SchedulerException) Locker(nl.nn.adapterframework.util.Locker) HashMap(java.util.HashMap) Scheduler(org.quartz.Scheduler) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) Adapter(nl.nn.adapterframework.core.Adapter) IbisJobDetail(nl.nn.adapterframework.scheduler.IbisJobDetail) ConfigurationException(nl.nn.adapterframework.configuration.ConfigurationException) SchedulerException(org.quartz.SchedulerException) JobKey(org.quartz.JobKey) SchedulerHelper(nl.nn.adapterframework.scheduler.SchedulerHelper) ConfigurationException(nl.nn.adapterframework.configuration.ConfigurationException) ResultSet(java.sql.ResultSet) FixedQuerySender(nl.nn.adapterframework.jdbc.FixedQuerySender)

Example 27 with FixedQuerySender

use of nl.nn.adapterframework.jdbc.FixedQuerySender in project iaf by ibissource.

the class CleanupDatabaseJob method beforeExecuteJob.

@Override
public boolean beforeExecuteJob(IbisManager ibisManager) {
    Set<String> datasourceNames = getAllLockerDatasourceNames(ibisManager);
    for (String datasourceName : datasourceNames) {
        FixedQuerySender qs = null;
        try {
            qs = SpringUtils.createBean(getApplicationContext(), FixedQuerySender.class);
            qs.setDatasourceName(datasourceName);
            qs.setName("cleanupDatabase-IBISLOCK");
            qs.setQueryType("other");
            qs.setTimeout(getQueryTimeout());
            qs.setScalar(true);
            String query = "DELETE FROM IBISLOCK WHERE EXPIRYDATE < ?";
            qs.setQuery(query);
            Parameter param = new Parameter("now", DateUtils.format(new Date()));
            param.setType(ParameterType.TIMESTAMP);
            qs.addParameter(param);
            qs.configure();
            qs.open();
            Message result = qs.sendMessage(Message.nullMessage(), null);
            String resultString = result.asString();
            int numberOfRowsAffected = Integer.parseInt(resultString);
            if (numberOfRowsAffected > 0) {
                getMessageKeeper().add("deleted [" + numberOfRowsAffected + "] row(s) from [IBISLOCK] table. It implies that there have been process(es) that finished unexpectedly or failed to complete. Please investigate the log files!", MessageKeeperLevel.WARN);
            }
        } catch (Exception e) {
            String msg = "error while cleaning IBISLOCK table (as part of scheduled job execution): " + e.getMessage();
            getMessageKeeper().add(msg, MessageKeeperLevel.ERROR);
            log.error(getLogPrefix() + msg, e);
        } finally {
            if (qs != null) {
                qs.close();
            }
        }
    }
    return true;
}
Also used : Message(nl.nn.adapterframework.stream.Message) Parameter(nl.nn.adapterframework.parameters.Parameter) FixedQuerySender(nl.nn.adapterframework.jdbc.FixedQuerySender) Date(java.util.Date)

Example 28 with FixedQuerySender

use of nl.nn.adapterframework.jdbc.FixedQuerySender in project iaf by ibissource.

the class TestTool method executeFixedQuerySenderRead.

private static int executeFixedQuerySenderRead(String step, String stepDisplayName, Properties properties, Map<String, Map<String, Object>> queues, Map<String, Object> writers, String queueName, String fileName, String fileContent) {
    int result = RESULT_ERROR;
    Map querySendersInfo = (Map) queues.get(queueName);
    Integer waitBeforeRead = (Integer) querySendersInfo.get("readQueryWaitBeforeRead");
    if (waitBeforeRead != null) {
        try {
            Thread.sleep(waitBeforeRead.intValue());
        } catch (InterruptedException e) {
        }
    }
    boolean newRecordFound = true;
    FixedQuerySender prePostFixedQuerySender = (FixedQuerySender) querySendersInfo.get("prePostQueryFixedQuerySender");
    if (prePostFixedQuerySender != null) {
        try {
            String preResult = (String) querySendersInfo.get("prePostQueryResult");
            debugPipelineMessage(stepDisplayName, "Pre result '" + queueName + "':", preResult, writers);
            PipeLineSession session = new PipeLineSession();
            session.put(PipeLineSession.businessCorrelationIdKey, TestTool.TESTTOOL_CORRELATIONID);
            String postResult = prePostFixedQuerySender.sendMessage(TESTTOOL_DUMMY_MESSAGE, session).asString();
            debugPipelineMessage(stepDisplayName, "Post result '" + queueName + "':", postResult, writers);
            if (preResult.equals(postResult)) {
                newRecordFound = false;
            }
            /* Fill the preResult with postResult, so closeQueues is able to determine if there
				 * are remaining messages left.
				 */
            querySendersInfo.put("prePostQueryResult", postResult);
        } catch (TimeoutException e) {
            errorMessage("Time out on execute query for '" + queueName + "': " + e.getMessage(), e, writers);
        } catch (IOException | SenderException e) {
            errorMessage("Could not execute query for '" + queueName + "': " + e.getMessage(), e, writers);
        }
    }
    String message = null;
    if (newRecordFound) {
        FixedQuerySender readQueryFixedQuerySender = (FixedQuerySender) querySendersInfo.get("readQueryQueryFixedQuerySender");
        try {
            PipeLineSession session = new PipeLineSession();
            session.put(PipeLineSession.businessCorrelationIdKey, TestTool.TESTTOOL_CORRELATIONID);
            message = readQueryFixedQuerySender.sendMessage(TESTTOOL_DUMMY_MESSAGE, session).asString();
        } catch (TimeoutException e) {
            errorMessage("Time out on execute query for '" + queueName + "': " + e.getMessage(), e, writers);
        } catch (IOException | SenderException e) {
            errorMessage("Could not execute query for '" + queueName + "': " + e.getMessage(), e, writers);
        }
    }
    if (message == null) {
        if ("".equals(fileName)) {
            result = RESULT_OK;
        } else {
            errorMessage("Could not read jdbc message (null returned) or no new message found (pre result equals post result)", writers);
        }
    } else {
        if ("".equals(fileName)) {
            debugPipelineMessage(stepDisplayName, "Unexpected message read from '" + queueName + "':", message, writers);
        } else {
            result = compareResult(step, stepDisplayName, fileName, fileContent, message, properties, writers, queueName);
        }
    }
    return result;
}
Also used : PipeLineSession(nl.nn.adapterframework.core.PipeLineSession) IOException(java.io.IOException) SenderException(nl.nn.adapterframework.core.SenderException) Map(java.util.Map) HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap) FixedQuerySender(nl.nn.adapterframework.jdbc.FixedQuerySender) TimeoutException(nl.nn.adapterframework.core.TimeoutException)

Example 29 with FixedQuerySender

use of nl.nn.adapterframework.jdbc.FixedQuerySender in project iaf by ibissource.

the class ShowScheduler method deleteSchedules.

@DELETE
@RolesAllowed({ "IbisDataAdmin", "IbisAdmin", "IbisTester" })
@Path("/schedules/{groupName}/job/{jobName}")
@Relation("schedules")
@Produces(MediaType.APPLICATION_JSON)
public Response deleteSchedules(@PathParam("jobName") String jobName, @PathParam("groupName") String groupName) throws ApiException {
    Scheduler scheduler = getScheduler();
    try {
        if (log.isInfoEnabled())
            log.info("delete job jobName [" + jobName + "] groupName [" + groupName + "] " + commandIssuedBy());
        JobKey jobKey = JobKey.jobKey(jobName, groupName);
        if (jobKey == null) {
            throw new ApiException("JobKey not found, unable to remove schedule");
        }
        IbisJobDetail jobDetail = (IbisJobDetail) scheduler.getJobDetail(jobKey);
        if (jobDetail.getJobType() == JobType.DATABASE) {
            boolean success = false;
            // remove from database
            FixedQuerySender qs = (FixedQuerySender) getIbisContext().createBeanAutowireByName(FixedQuerySender.class);
            qs.setDatasourceName(JndiDataSourceFactory.GLOBAL_DEFAULT_DATASOURCE_NAME);
            qs.setQuery("SELECT COUNT(*) FROM IBISSCHEDULES");
            try {
                qs.configure();
            } catch (ConfigurationException e) {
                throw new ApiException("Error creating FixedQuerySender bean to remove job from database", e);
            }
            try {
                qs.open();
                try (Connection conn = qs.getConnection()) {
                    String query = "DELETE FROM IBISSCHEDULES WHERE JOBNAME=? AND JOBGROUP=?";
                    try (PreparedStatement stmt = conn.prepareStatement(query)) {
                        stmt.setString(1, jobKey.getName());
                        stmt.setString(2, jobKey.getGroup());
                        success = stmt.executeUpdate() > 0;
                    }
                }
            } catch (SenderException | SQLException | JdbcException e) {
                throw new ApiException("error removing job from database", e);
            } finally {
                qs.close();
            }
            if (!success) {
                throw new ApiException("failed to remove job from database");
            }
        }
        // remove from memory
        scheduler.deleteJob(jobKey);
    } catch (SchedulerException e) {
        throw new ApiException("Failed to delete job", e);
    }
    return Response.status(Response.Status.OK).build();
}
Also used : SchedulerException(org.quartz.SchedulerException) SQLException(java.sql.SQLException) Scheduler(org.quartz.Scheduler) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) JdbcException(nl.nn.adapterframework.jdbc.JdbcException) IbisJobDetail(nl.nn.adapterframework.scheduler.IbisJobDetail) JobKey(org.quartz.JobKey) ConfigurationException(nl.nn.adapterframework.configuration.ConfigurationException) SenderException(nl.nn.adapterframework.core.SenderException) FixedQuerySender(nl.nn.adapterframework.jdbc.FixedQuerySender) Path(javax.ws.rs.Path) DELETE(javax.ws.rs.DELETE) RolesAllowed(javax.annotation.security.RolesAllowed) Produces(javax.ws.rs.Produces)

Example 30 with FixedQuerySender

use of nl.nn.adapterframework.jdbc.FixedQuerySender in project iaf by ibissource.

the class ShowScheduler method createSchedule.

private Response createSchedule(String groupName, String jobName, MultipartBody inputDataMap, boolean overwrite) {
    if (inputDataMap == null) {
        throw new ApiException("Missing post parameters");
    }
    String name = jobName;
    if (// If name is not explicitly set, try to deduct it from inputmap
    name == null)
        name = resolveStringFromMap(inputDataMap, "name");
    String cronExpression = resolveTypeFromMap(inputDataMap, "cron", String.class, "");
    int interval = resolveTypeFromMap(inputDataMap, "interval", Integer.class, -1);
    // Either one of the two has to be set
    if (interval == -1 && StringUtils.isEmpty(cronExpression)) {
        throw new ApiException("Either 'cron' or 'interval' has to be set");
    }
    String adapterName = resolveStringFromMap(inputDataMap, "adapter");
    // Make sure the adapter exists!
    DefaultIbisManager manager = (DefaultIbisManager) getIbisManager();
    IAdapter adapter = manager.getRegisteredAdapter(adapterName);
    if (adapter == null) {
        throw new ApiException("Adapter [" + adapterName + "] not found");
    }
    // Make sure the receiver exists!
    String receiverName = resolveStringFromMap(inputDataMap, "receiver");
    Receiver<?> receiver = adapter.getReceiverByName(receiverName);
    if (receiver == null) {
        throw new ApiException("Receiver [" + receiverName + "] not found");
    }
    String listenerName = null;
    IListener<?> listener = receiver.getListener();
    if (listener != null) {
        listenerName = listener.getName();
    }
    if (StringUtils.isEmpty(listenerName)) {
        throw new ApiException("unable to determine listener for receiver [" + receiverName + "]");
    }
    Configuration applicationContext = adapter.getConfiguration();
    String jobGroup = groupName;
    if (StringUtils.isEmpty(jobGroup)) {
        jobGroup = adapter.getConfiguration().getName();
    }
    boolean persistent = resolveTypeFromMap(inputDataMap, "persistent", boolean.class, false);
    boolean hasLocker = resolveTypeFromMap(inputDataMap, "locker", boolean.class, false);
    String lockKey = resolveTypeFromMap(inputDataMap, "lockkey", String.class, "lock4[" + name + "]");
    String message = resolveStringFromMap(inputDataMap, "message");
    String description = resolveStringFromMap(inputDataMap, "description");
    SchedulerHelper sh = getSchedulerHelper();
    // First try to create the schedule and run it on the local ibis before storing it in the database
    DatabaseJob jobdef = SpringUtils.createBean(applicationContext, DatabaseJob.class);
    jobdef.setCronExpression(cronExpression);
    jobdef.setName(name);
    jobdef.setAdapterName(adapterName);
    jobdef.setJavaListener(listenerName);
    jobdef.setJobGroup(jobGroup);
    jobdef.setMessage(message);
    jobdef.setDescription(description);
    jobdef.setInterval(interval);
    if (hasLocker) {
        Locker locker = SpringUtils.createBean(applicationContext, Locker.class);
        locker.setName(lockKey);
        locker.setObjectId(lockKey);
        locker.setDatasourceName(JndiDataSourceFactory.GLOBAL_DEFAULT_DATASOURCE_NAME);
        jobdef.setLocker(locker);
    }
    try {
        jobdef.configure();
        sh.scheduleJob(jobdef);
    } catch (Exception e) {
        throw new ApiException("Failed to add schedule", e);
    }
    // Save the job in the database
    if (persistent && AppConstants.getInstance().getBoolean("loadDatabaseSchedules.active", false)) {
        boolean success = false;
        FixedQuerySender qs = SpringUtils.createBean(applicationContext, FixedQuerySender.class);
        qs.setDatasourceName(JndiDataSourceFactory.GLOBAL_DEFAULT_DATASOURCE_NAME);
        qs.setQuery("SELECT COUNT(*) FROM IBISSCHEDULES");
        try {
            qs.configure();
        } catch (ConfigurationException e) {
            throw new ApiException("Error creating FixedQuerySender bean to store job in database", e);
        }
        try {
            qs.open();
            try (Connection conn = qs.getConnection()) {
                if (overwrite) {
                    String deleteQuery = "DELETE FROM IBISSCHEDULES WHERE JOBNAME=? AND JOBGROUP=?";
                    try (PreparedStatement deleteStatement = conn.prepareStatement(deleteQuery)) {
                        deleteStatement.setString(1, name);
                        deleteStatement.setString(2, jobGroup);
                        deleteStatement.executeUpdate();
                    }
                }
                String insertQuery = "INSERT INTO IBISSCHEDULES (JOBNAME, JOBGROUP, ADAPTER, RECEIVER, CRON, EXECUTIONINTERVAL, MESSAGE, DESCRIPTION, LOCKER, LOCK_KEY, CREATED_ON, BY_USER) " + "VALUES (?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?)";
                try (PreparedStatement stmt = conn.prepareStatement(insertQuery)) {
                    stmt.setString(1, name);
                    stmt.setString(2, jobGroup);
                    stmt.setString(3, adapterName);
                    stmt.setString(4, listenerName);
                    stmt.setString(5, cronExpression);
                    stmt.setInt(6, interval);
                    stmt.setClob(7, new StringReader(message));
                    stmt.setString(8, description);
                    stmt.setBoolean(9, hasLocker);
                    stmt.setString(10, lockKey);
                    stmt.setString(11, getUserPrincipalName());
                    success = stmt.executeUpdate() > 0;
                }
            }
        } catch (SenderException | SQLException | JdbcException e) {
            throw new ApiException("error saving job in database", e);
        } finally {
            qs.close();
        }
        if (!success)
            throw new ApiException("An error occurred while storing the job in the database");
    }
    return Response.status(Response.Status.CREATED).build();
}
Also used : Locker(nl.nn.adapterframework.util.Locker) Configuration(nl.nn.adapterframework.configuration.Configuration) SQLException(java.sql.SQLException) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) JdbcException(nl.nn.adapterframework.jdbc.JdbcException) SenderException(nl.nn.adapterframework.core.SenderException) JdbcException(nl.nn.adapterframework.jdbc.JdbcException) SQLException(java.sql.SQLException) SchedulerException(org.quartz.SchedulerException) ConfigurationException(nl.nn.adapterframework.configuration.ConfigurationException) SchedulerHelper(nl.nn.adapterframework.scheduler.SchedulerHelper) ConfigurationException(nl.nn.adapterframework.configuration.ConfigurationException) DatabaseJob(nl.nn.adapterframework.scheduler.job.DatabaseJob) StringReader(java.io.StringReader) SenderException(nl.nn.adapterframework.core.SenderException) IAdapter(nl.nn.adapterframework.core.IAdapter) DefaultIbisManager(nl.nn.adapterframework.unmanaged.DefaultIbisManager) FixedQuerySender(nl.nn.adapterframework.jdbc.FixedQuerySender)

Aggregations

FixedQuerySender (nl.nn.adapterframework.jdbc.FixedQuerySender)32 Connection (java.sql.Connection)17 PreparedStatement (java.sql.PreparedStatement)16 ResultSet (java.sql.ResultSet)14 SenderException (nl.nn.adapterframework.core.SenderException)13 ConfigurationException (nl.nn.adapterframework.configuration.ConfigurationException)11 Parameter (nl.nn.adapterframework.parameters.Parameter)10 IOException (java.io.IOException)9 HashMap (java.util.HashMap)9 SQLException (java.sql.SQLException)8 LinkedHashMap (java.util.LinkedHashMap)8 ParameterResolutionContext (nl.nn.adapterframework.parameters.ParameterResolutionContext)8 Map (java.util.Map)7 PipeRunException (nl.nn.adapterframework.core.PipeRunException)7 JdbcException (nl.nn.adapterframework.jdbc.JdbcException)7 ArrayList (java.util.ArrayList)6 TransformerConfigurationException (javax.xml.transform.TransformerConfigurationException)5 ListenerException (nl.nn.adapterframework.core.ListenerException)4 IbisWebServiceSender (nl.nn.adapterframework.http.IbisWebServiceSender)4 WebServiceListener (nl.nn.adapterframework.http.WebServiceListener)4