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

List of usage examples for org.springframework.jdbc.object SqlFunction SqlFunction

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.object.*
  5. SqlFunction
  6. SqlFunction

Introduction

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

Prototype

public SqlFunction(DataSource ds, String sql)

Source Link

Document

Create a new SqlFunction object with SQL, but without parameters.

Usage

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

protected LogEntryDAOMysqlImpl(DataSource dataSource, SqlScriptService sqlScriptService,
        PluginRegistry pluginRegistry) {
    super(sqlScriptService);
    setDataSource(dataSource);/*from ww  w  . java 2  s .co m*/

    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();
}

From source file:org.springframework.jdbc.object.SqlFunctionTests.java

public void testTooManyRows() throws SQLException {
    ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
    mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
    mockResultSetMetaData.getColumnCount();
    ctrlResultSetMetaData.setReturnValue(1, 2);

    mockResultSet.getMetaData();// w  w w.j  a  va2s .  c o  m
    ctrlResultSet.setReturnValue(mockResultSetMetaData, 2);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getObject(1);
    ctrlResultSet.setReturnValue(new Integer(14), 1);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getObject(1);
    ctrlResultSet.setReturnValue(new Integer(15), 1);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(FUNCTION);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    SqlFunction function = new SqlFunction(mockDataSource, FUNCTION);
    function.compile();

    try {
        int count = function.run();
        fail("Shouldn't continue when too many rows returned");
    } catch (IncorrectResultSizeDataAccessException idaauex) {
        // OK 
    }
}

From source file:org.springframework.jdbc.object.SqlFunctionTests.java

public void testFunctionInt() throws SQLException {
    ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
    mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
    mockResultSetMetaData.getColumnCount();
    ctrlResultSetMetaData.setReturnValue(1);

    mockResultSet.getMetaData();/*w  ww .j a va  2  s  .c o  m*/
    ctrlResultSet.setReturnValue(mockResultSetMetaData, 1);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getObject(1);
    ctrlResultSet.setReturnValue(new Integer(14));
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(FUNCTION_INT);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    SqlFunction function = new SqlFunction(mockDataSource, FUNCTION_INT);
    function.setTypes(new int[] { Types.INTEGER });
    function.compile();

    int count = function.run(1);
    assertTrue("Function returned value 14", count == 14);
}

From source file:org.springframework.jdbc.object.SqlFunctionTests.java

public void testFunctionWithStringResult() throws SQLException {
    ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
    mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
    mockResultSetMetaData.getColumnCount();
    ctrlResultSetMetaData.setReturnValue(1);

    mockResultSet.getMetaData();/*from   ww  w  . j av a  2  s .c o  m*/
    ctrlResultSet.setReturnValue(mockResultSetMetaData, 1);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getObject(1);
    ctrlResultSet.setReturnValue("14");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setString(2, "rod");
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(FUNCTION_MIXED);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    SqlFunction function = new SqlFunction(mockDataSource, FUNCTION_MIXED);
    function.setTypes(new int[] { Types.INTEGER, Types.VARCHAR });
    function.compile();

    String result = (String) function.runGeneric(new Object[] { new Integer(1), "rod" });
    assertTrue("Function returned value 14", "14".equals(result));
}

From source file:org.springframework.jdbc.object.SqlFunctionTests.java

public void testFunctionWithStringConvertedResult() throws SQLException {
    ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class);
    mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock();
    mockResultSetMetaData.getColumnCount();
    ctrlResultSetMetaData.setReturnValue(1);

    mockResultSet.getMetaData();//  w  w w .  java 2 s .  c  o m
    ctrlResultSet.setReturnValue(mockResultSetMetaData, 1);
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getString(1);
    ctrlResultSet.setReturnValue("14");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setString(2, "rod");
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(FUNCTION_MIXED);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    SqlFunction function = new SqlFunction(mockDataSource, FUNCTION_MIXED);
    function.setTypes(new int[] { Types.INTEGER, Types.VARCHAR });
    function.setResultType(String.class);
    function.compile();

    String result = (String) function.runGeneric(new Object[] { new Integer(1), "rod" });
    assertTrue("Function returned value 14", "14".equals(result));
}