Example usage for org.springframework.jdbc.object MappingSqlQuery MappingSqlQuery

Introduction

In this page you can find the example usage for org.springframework.jdbc.object MappingSqlQuery MappingSqlQuery.

Prototype

public MappingSqlQuery(DataSource ds, String sql) 

Source Link

Document

Convenient constructor with DataSource and SQL string.

Usage

From source file:org.easyrec.store.dao.plugin.impl.LogEntryDAOMysqlImpl.java

protected LogEntryDAOMysqlImpl(DataSource dataSource, SqlScriptService sqlScriptService,
        PluginRegistry pluginRegistry) {
    super(sqlScriptService);
    setDataSource(dataSource);/* ww  w  . ja  va 2s.  com*/

    startEntry = new SqlUpdate(dataSource,
            "INSERT INTO plugin_log(tenantId, pluginId, pluginVersion, startDate, assocTypeId, "
                    + "configuration) VALUES (?, ?, ?, ?, ?, ?)",
            new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR,
                    Types.BLOB });
    startEntry.compile();

    endEntry = new SqlUpdate(dataSource,
            "INSERT INTO plugin_log(tenantId, pluginId, pluginVersion, startDate, assocTypeId, configuration, "
                    + "endDate, statistics) VALUES (?, ?, ?, ?, ?, ?, ?, ?) "
                    + "ON DUPLICATE KEY UPDATE endDate = ?, statistics = ?",
            new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.BLOB,
                    Types.TIMESTAMP, Types.BLOB, Types.TIMESTAMP, Types.BLOB });
    endEntry.compile();

    endAllEntries = new SqlUpdate(dataSource,
            "UPDATE plugin_log SET endDate = ?, statistics = ? WHERE endDate IS NULL",
            new int[] { Types.TIMESTAMP, Types.BLOB });
    endAllEntries.compile();

    getRunningTenants = new MappingSqlQuery<Integer>(dataSource,
            "SELECT tenantId FROM plugin_log WHERE endDate IS NULL") {
        @Override
        protected Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getInt("tenantId");
        }
    };
    getRunningTenants.compile();

    getLogEntries = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log ORDER BY endDate DESC, id DESC LIMIT ?, ?");
    getLogEntries.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntries.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntries.compile();

    getLogEntriesForTenant = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log WHERE tenantId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?");
    getLogEntriesForTenant.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    getLogEntriesForTenant.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntriesForTenant.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntriesForTenant.compile();

    getLogEntriesWithAssocType = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log WHERE assocTypeId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?");
    getLogEntriesWithAssocType.declareParameter(new SqlParameter("assocTypeId", Types.INTEGER));
    getLogEntriesWithAssocType.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntriesWithAssocType.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntriesWithAssocType.compile();

    getLogEntriesForTenantWithAssocType = new GetLogEntriesStatement(dataSource, pluginRegistry,
            "SELECT * FROM plugin_log WHERE tenantId = ? AND assocTypeId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?");
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("assocTypeId", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("offset", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("limit", Types.INTEGER));
    getLogEntriesForTenantWithAssocType.compile();

    getNumberOfLogEntries = new SqlFunction<Integer>(dataSource,
            "SELECT count(*) AS entry_count FROM plugin_log");
    getNumberOfLogEntries.compile();

    getNumberOfLogEntriesForTenant = new SqlFunction<Integer>(dataSource,
            "SELECT count(*) AS entry_count FROM plugin_log WHERE tenantId = ?");
    getNumberOfLogEntriesForTenant.setResultType(Integer.class);
    getNumberOfLogEntriesForTenant.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    getNumberOfLogEntriesForTenant.compile();

    deleteLogEntries = new SqlUpdate(dataSource, "TRUNCATE plugin_log");
    deleteLogEntries.compile();

    getComputationDurationForDate = new SqlFunction<Integer>(dataSource,
            "SELECT sum(timestampdiff(second, startDate, endDate)) AS sum_seconds FROM plugin_log WHERE endDate BETWEEN ? AND ?");
    getComputationDurationForDate.setResultType(Integer.class);
    getComputationDurationForDate.declareParameter(new SqlParameter("start", Types.DATE));
    getComputationDurationForDate.declareParameter(new SqlParameter("end", Types.DATE));
    getComputationDurationForDate.compile();

    deleteLogEntryStatement = new SqlUpdate(dataSource,
            "DELETE FROM plugin_log WHERE tenantId = ? AND pluginId = ? AND pluginVersion = ? AND startDate = ? AND assocTypeId = ?");
    deleteLogEntryStatement.declareParameter(new SqlParameter("tenantId", Types.INTEGER));
    deleteLogEntryStatement.declareParameter(new SqlParameter("pluginId", Types.VARCHAR));
    deleteLogEntryStatement.declareParameter(new SqlParameter("pluginVersion", Types.VARCHAR));
    deleteLogEntryStatement.declareParameter(new SqlParameter("startDate", Types.TIMESTAMP));
    deleteLogEntryStatement.declareParameter(new SqlParameter("assocTypeId", Types.VARCHAR));
    deleteLogEntryStatement.compile();
}

---