Example usage for org.springframework.jdbc.core JdbcTemplate update

List of usage examples for org.springframework.jdbc.core JdbcTemplate update

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. update

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate update.

Prototype

@Override
    public int update(PreparedStatementCreator psc) throws DataAccessException

Source Link

Usage

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

private int changeStatusInMailingList(int companyID, List<Integer> updatedRecipients, JdbcTemplate jdbc,
        int mailinglistId, int newStatus, String remark, String currentTimestamp) {
    if (updatedRecipients.size() == 0) {
        return 0;
    }/*from  ww w .ja  va  2 s . c  o  m*/
    String recipientsStr = StringUtils.join(updatedRecipients, ',');
    String sql = "UPDATE customer_" + companyID + "_binding_tbl SET user_status=" + newStatus
            + ", exit_mailing_id=0, user_remark='" + remark + "', " + AgnUtils.changeDateName() + "="
            + currentTimestamp + " WHERE mailinglist_id=" + mailinglistId + " AND customer_id IN ("
            + recipientsStr + ") AND user_status=" + BindingEntry.USER_STATUS_ACTIVE;
    return jdbc.update(sql);
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Holds value of property applicationContext.
 *//* w  ww  .j  ava2  s .co m*/

public void deleteAllNoBindings(int companyID, String toBeDeletedTable) {
    JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
    String delete = "delete from customer_" + companyID + "_tbl " + "where customer_id not in ("
            + "select customer_id from customer_" + companyID + "_binding_tbl" + ") "
            + "and customer_id in (select * from " + toBeDeletedTable + ")";

    tmpl.update(delete);
    tmpl.execute("drop table " + toBeDeletedTable);
}

From source file:org.firewaterframework.test.TestRouteMapper.java

@BeforeClass
public static void init() {
    appContext = new ClassPathXmlApplicationContext("rest-base.xml");
    BasicDataSource ds = (BasicDataSource) appContext.getBean("dataSource");
    JdbcTemplate template = new JdbcTemplate(ds);
    String sql = "create table user(id int auto_increment primary key, first_name varchar(255), last_name varchar(255),"
            + "city varchar(255), state char(2), email varchar(255), password varchar(255), zip int)";
    template.execute(sql);/*w  w  w  . jav a2  s  .  co  m*/

    sql = "create table user_role( user_id int, role_id int )";
    template.execute(sql);

    sql = "create table role( id int, name varchar(64) )";
    template.execute(sql);

    sql = "create table pet(id int auto_increment primary key, name varchar(255), species_id int, owner_id int)";
    template.execute(sql);

    sql = "create table species(id int auto_increment primary key, name varchar(255))";
    template.execute(sql);

    sql = "insert into user( id, first_name, last_name, city, state, email, password, zip ) "
            + "values( 0, 'joe', 'who', 'new york', 'NY', 'joe@who.com', 'yahoo', 10012 ),"
            + "( 1, 'willie', 'who', 'new york', 'NY', 'willie@who.com', 'yahoo', 10012 ),"
            + "( 2, 'joe', 'wonka', 'new york', 'NY', 'joe@wonka.com', 'yahoo', 10033 ),"
            + "( 3, 'jane', 'who', 'San Francisco', 'CA', 'jane@who.com', 'ziper', 28218 ),"
            + "( 4, 'jim', 'morrison', 'new york', 'NY', 'whoajee@who.com', 'nutz', 10012 ),"
            + "( 5, 'eddie', 'van halen', 'los angeles', 'CA', 'zorker@who.com', 'yahoo', 90210 )";
    template.update(sql);

    sql = "insert into user_role( user_id, role_id ) "
            + "values( 0, 0 ), ( 1, 0 ), ( 2, 1 ), ( 3, 1 ), ( 4, 0 ), ( 5, 0 )";
    template.update(sql);

    sql = "insert into role( id, name ) values " + "(0, 'User'),(1, 'Admin')";
    template.update(sql);

    sql = "insert into pet( id, name, species_id, owner_id ) " + "values( 0, 'trixie', 1, 1 ),"
            + "( 1, 'wixie', 3, 1 )," + "( 2, 'jimmy', 1, 3 )," + "( 3, 'flopsy', 2, 4 ),"
            + "( 4, 'mixie', 2, 1 )";
    template.update(sql);

    sql = "insert into species( id, name ) " + "values( 0, 'cat' )," + "( 1, 'dog' )," + "( 2, 'birdy' ),"
            + "( 3, 'fish' )";
    template.update(sql);
}

From source file:org.springframework.integration.jdbc.mysql.MySqlJdbcMessageStoreTests.java

@After
public void afterTest() {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    new TransactionTemplate(this.transactionManager).execute(new TransactionCallback<Void>() {
        public Void doInTransaction(TransactionStatus status) {
            final int deletedGroupToMessageRows = jdbcTemplate.update("delete from INT_GROUP_TO_MESSAGE");
            final int deletedMessages = jdbcTemplate.update("delete from INT_MESSAGE");
            final int deletedMessageGroups = jdbcTemplate.update("delete from INT_MESSAGE_GROUP");

            LOG.info(String.format(
                    "Cleaning Database - Deleted Messages: %s, "
                            + "Deleted GroupToMessage Rows: %s, Deleted Message Groups: %s",
                    deletedMessages, deletedGroupToMessageRows, deletedMessageGroups));
            return null;
        }/* w  w  w .j  a va 2s . c  o m*/
    });
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testUpdateCount() throws Exception {
    final String sql = "UPDATE INVOICE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    int idParam = 11111;

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.setInt(1, idParam);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeUpdate();
    ctrlPreparedStatement.setReturnValue(1);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }//  w  w  w . j  av a  2  s .  c om
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

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

    ctrlPreparedStatement.replay();
    replay();

    Dispatcher d = new Dispatcher(idParam, sql);
    JdbcTemplate template = new JdbcTemplate(mockDataSource);

    int rowsAffected = template.update(d);
    assertTrue("1 update affected 1 row", rowsAffected == 1);

    /*
    d = new Dispatcher(idParam);
    rowsAffected = template.update(d);
    assertTrue("bogus update affected 0 rows", rowsAffected == 0);
    */

    ctrlPreparedStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBogusUpdate() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int idParam = 6666;

    // It's because Integers aren't canonical
    SQLException sex = new SQLException("bad update");

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.setInt(1, idParam);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeUpdate();
    ctrlPreparedStatement.setThrowable(sex);
    mockPreparedStatement.close();//  w ww  . jav a  2 s. com
    ctrlPreparedStatement.setVoidCallable();

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

    ctrlPreparedStatement.replay();
    replay();

    Dispatcher d = new Dispatcher(idParam, sql);
    JdbcTemplate template = new JdbcTemplate(mockDataSource);

    try {
        template.update(d);
        fail("Bogus update should throw exception");
    } catch (UncategorizedDataAccessException ex) {
        // pass
        assertTrue("Correct exception", ex instanceof UncategorizedSQLException);
        assertTrue("Root cause is correct", ex.getCause() == sex);
        //assertTrue("no update occurred", !je.getDataWasUpdated());
    }

    ctrlPreparedStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

/**
 * Test update with static SQL.//from  w  w  w  .  j  a va2 s. c  o  m
 */
public void testSqlUpdate() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
    int rowsAffected = 33;

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.executeUpdate(sql);
    ctrlStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);
    ctrlStatement.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    int actualRowsAffected = template.update(sql);
    assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);
    ctrlStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testSqlUpdateEncountersSqlException() throws Exception {
    SQLException sex = new SQLException("bad update");
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.executeUpdate(sql);/*from   www .  j a  va 2  s.  co m*/
    ctrlStatement.setThrowable(sex);
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlStatement.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    try {
        template.update(sql);
    } catch (DataAccessException ex) {
        assertTrue("root cause is correct", ex.getCause() == sex);
    }

    ctrlStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testSqlUpdateWithThreadConnection() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
    int rowsAffected = 33;

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.executeUpdate(sql);/* ww  w .jav  a2s . c  om*/
    ctrlStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlStatement.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);

    int actualRowsAffected = template.update(sql);
    assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);

    ctrlStatement.verify();
}

From source file:org.springframework.test.jdbc.JdbcTestUtils.java

/**
 * Delete all rows from the specified tables.
 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
 * @param tableNames the names of the tables to delete from
 * @return the total number of rows deleted from all specified tables
 *//*from  w  ww  .  j a  va2  s .c om*/
public static int deleteFromTables(JdbcTemplate jdbcTemplate, String... tableNames) {
    int totalRowCount = 0;
    for (String tableName : tableNames) {
        int rowCount = jdbcTemplate.update("DELETE FROM " + tableName);
        totalRowCount += rowCount;
        if (logger.isInfoEnabled()) {
            logger.info("Deleted " + rowCount + " rows from table " + tableName);
        }
    }
    return totalRowCount;
}