Java tutorial
/* * Copyright 2002-2008 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.springframework.jdbc.object; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import java.util.HashMap; import java.util.Map; import org.easymock.MockControl; import org.apache.commons.logging.LogFactory; import org.springframework.core.JdkVersion; import org.springframework.jdbc.AbstractJdbcTests; import org.springframework.jdbc.JdbcUpdateAffectedIncorrectNumberOfRowsException; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; /** * @author Trevor Cook * @author Thomas Risberg * @author Juergen Hoeller */ public class SqlUpdateTests extends AbstractJdbcTests { private static final String UPDATE = "update seat_status set booking_id = null"; private static final String UPDATE_INT = "update seat_status set booking_id = null where performance_id = ?"; private static final String UPDATE_INT_INT = "update seat_status set booking_id = null where performance_id = ? and price_band_id = ?"; private static final String UPDATE_NAMED_PARAMETERS = "update seat_status set booking_id = null where performance_id = :perfId and price_band_id = :priceId"; private static final String UPDATE_STRING = "update seat_status set booking_id = null where name = ?"; private static final String UPDATE_OBJECTS = "update seat_status set booking_id = null where performance_id = ? and price_band_id = ? and name = ? and confirmed = ?"; private static final String INSERT_GENERATE_KEYS = "insert into show (name) values(?)"; private final boolean debugEnabled = LogFactory.getLog(JdbcTemplate.class).isDebugEnabled(); private MockControl ctrlPreparedStatement; private PreparedStatement mockPreparedStatement; private MockControl ctrlResultSet; private ResultSet mockResultSet; private MockControl ctrlResultSetMetaData; private ResultSetMetaData mockResultSetMetaData; protected void setUp() throws Exception { super.setUp(); ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class); mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock(); } protected void tearDown() throws Exception { super.tearDown(); if (shouldVerify()) { ctrlPreparedStatement.verify(); } } protected void replay() { super.replay(); ctrlPreparedStatement.replay(); } public void testUpdate() throws SQLException { mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); Updater pc = new Updater(); int rowsAffected = pc.run(); assertEquals(1, rowsAffected); } public void testUpdateInt() throws SQLException { mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE_INT); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); IntUpdater pc = new IntUpdater(); int rowsAffected = pc.run(1); assertEquals(1, rowsAffected); } public void testUpdateIntInt() throws SQLException { mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC); mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE_INT_INT); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); IntIntUpdater pc = new IntIntUpdater(); int rowsAffected = pc.run(1, 1); assertEquals(1, rowsAffected); } public void testNamedParameterUpdateWithUnnamedDeclarations() throws SQLException { doTestNamedParameterUpdate(false); } public void testNamedParameterUpdateWithNamedDeclarations() throws SQLException { doTestNamedParameterUpdate(true); } private void doTestNamedParameterUpdate(final boolean namedDeclarations) throws SQLException { mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC); mockPreparedStatement.setObject(2, new Integer(1), Types.DECIMAL); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE_INT_INT); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); class NamedParameterUpdater extends SqlUpdate { public NamedParameterUpdater() { setSql(UPDATE_NAMED_PARAMETERS); setDataSource(mockDataSource); if (namedDeclarations) { declareParameter(new SqlParameter("priceId", Types.DECIMAL)); declareParameter(new SqlParameter("perfId", Types.NUMERIC)); } else { declareParameter(new SqlParameter(Types.NUMERIC)); declareParameter(new SqlParameter(Types.DECIMAL)); } compile(); } public int run(int performanceId, int type) { Map params = new HashMap(); params.put("perfId", new Integer(performanceId)); params.put("priceId", new Integer(type)); return updateByNamedParam(params); } } NamedParameterUpdater pc = new NamedParameterUpdater(); int rowsAffected = pc.run(1, 1); assertEquals(1, rowsAffected); } public void testUpdateString() throws SQLException { mockPreparedStatement.setString(1, "rod"); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE_STRING); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); StringUpdater pc = new StringUpdater(); int rowsAffected = pc.run("rod"); assertEquals(1, rowsAffected); } public void testUpdateMixed() throws SQLException { mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC); mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC, 2); mockPreparedStatement.setString(3, "rod"); mockPreparedStatement.setObject(4, Boolean.TRUE, Types.BOOLEAN); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE_OBJECTS); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); MixedUpdater pc = new MixedUpdater(); int rowsAffected = pc.run(1, 1, "rod", true); assertEquals(1, rowsAffected); } public void testUpdateAndGeneratedKeys() throws SQLException { ctrlResultSetMetaData = MockControl.createControl(ResultSetMetaData.class); mockResultSetMetaData = (ResultSetMetaData) ctrlResultSetMetaData.getMock(); mockResultSetMetaData.getColumnCount(); ctrlResultSetMetaData.setReturnValue(1); mockResultSetMetaData.getColumnLabel(1); ctrlResultSetMetaData.setReturnValue("1", 2); ctrlResultSet = MockControl.createControl(ResultSet.class); mockResultSet = (ResultSet) ctrlResultSet.getMock(); mockResultSet.getMetaData(); ctrlResultSet.setReturnValue(mockResultSetMetaData); mockResultSet.next(); ctrlResultSet.setReturnValue(true); mockResultSet.getObject(1); ctrlResultSet.setReturnValue(new Integer(11)); mockResultSet.next(); ctrlResultSet.setReturnValue(false); mockResultSet.close(); ctrlResultSet.setVoidCallable(); mockPreparedStatement.setString(1, "rod"); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); mockPreparedStatement.getGeneratedKeys(); ctrlPreparedStatement.setReturnValue(mockResultSet); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(INSERT_GENERATE_KEYS, PreparedStatement.RETURN_GENERATED_KEYS); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); ctrlResultSet.replay(); ctrlResultSetMetaData.replay(); GeneratedKeysUpdater pc = new GeneratedKeysUpdater(); KeyHolder generatedKeyHolder = new GeneratedKeyHolder(); int rowsAffected = pc.run("rod", generatedKeyHolder); assertEquals(1, rowsAffected); assertEquals(1, generatedKeyHolder.getKeyList().size()); assertEquals(11, generatedKeyHolder.getKey().intValue()); } public void testUpdateConstructor() throws SQLException { mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC); mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC); mockPreparedStatement.setString(3, "rod"); mockPreparedStatement.setObject(4, Boolean.TRUE, Types.BOOLEAN); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(1); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE_OBJECTS); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); ConstructorUpdater pc = new ConstructorUpdater(); int rowsAffected = pc.run(1, 1, "rod", true); assertEquals(1, rowsAffected); } public void testUnderMaxRows() throws SQLException { mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(3); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); MaxRowsUpdater pc = new MaxRowsUpdater(); int rowsAffected = pc.run(); assertEquals(3, rowsAffected); } public void testMaxRows() throws SQLException { mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(5); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); MaxRowsUpdater pc = new MaxRowsUpdater(); int rowsAffected = pc.run(); assertEquals(5, rowsAffected); } public void testOverMaxRows() throws SQLException { mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(8); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); MaxRowsUpdater pc = new MaxRowsUpdater(); try { int rowsAffected = pc.run(); fail("Shouldn't continue when too many rows affected"); } catch (JdbcUpdateAffectedIncorrectNumberOfRowsException juaicrex) { // OK } } public void testRequiredRows() throws SQLException { mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(3); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); RequiredRowsUpdater pc = new RequiredRowsUpdater(); int rowsAffected = pc.run(); assertEquals(3, rowsAffected); } public void testNotRequiredRows() throws SQLException { mockPreparedStatement.executeUpdate(); ctrlPreparedStatement.setReturnValue(2); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(UPDATE); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); RequiredRowsUpdater pc = new RequiredRowsUpdater(); try { int rowsAffected = pc.run(); fail("Shouldn't continue when too many rows affected"); } catch (JdbcUpdateAffectedIncorrectNumberOfRowsException juaicrex) { // OK } } private class Updater extends SqlUpdate { public Updater() { setSql(UPDATE); setDataSource(mockDataSource); compile(); } public int run() { return update(); } } private class IntUpdater extends SqlUpdate { public IntUpdater() { setSql(UPDATE_INT); setDataSource(mockDataSource); declareParameter(new SqlParameter(Types.NUMERIC)); compile(); } public int run(int performanceId) { return update(performanceId); } } private class IntIntUpdater extends SqlUpdate { public IntIntUpdater() { setSql(UPDATE_INT_INT); setDataSource(mockDataSource); declareParameter(new SqlParameter(Types.NUMERIC)); declareParameter(new SqlParameter(Types.NUMERIC)); compile(); } public int run(int performanceId, int type) { return update(performanceId, type); } } private class StringUpdater extends SqlUpdate { public StringUpdater() { setSql(UPDATE_STRING); setDataSource(mockDataSource); declareParameter(new SqlParameter(Types.VARCHAR)); compile(); } public int run(String name) { return update(name); } } private class MixedUpdater extends SqlUpdate { public MixedUpdater() { setSql(UPDATE_OBJECTS); setDataSource(mockDataSource); declareParameter(new SqlParameter(Types.NUMERIC)); declareParameter(new SqlParameter(Types.NUMERIC, 2)); declareParameter(new SqlParameter(Types.VARCHAR)); declareParameter(new SqlParameter(Types.BOOLEAN)); compile(); } public int run(int performanceId, int type, String name, boolean confirmed) { Object[] params = new Object[] { new Integer(performanceId), new Integer(type), name, new Boolean(confirmed) }; return update(params); } } private class GeneratedKeysUpdater extends SqlUpdate { public GeneratedKeysUpdater() { setSql(INSERT_GENERATE_KEYS); setDataSource(mockDataSource); declareParameter(new SqlParameter(Types.VARCHAR)); setReturnGeneratedKeys(true); compile(); } public int run(String name, KeyHolder generatedKeyHolder) { Object[] params = new Object[] { name }; return update(params, generatedKeyHolder); } } private class ConstructorUpdater extends SqlUpdate { public ConstructorUpdater() { super(mockDataSource, UPDATE_OBJECTS, new int[] { Types.NUMERIC, Types.NUMERIC, Types.VARCHAR, Types.BOOLEAN }); compile(); } public int run(int performanceId, int type, String name, boolean confirmed) { Object[] params = new Object[] { new Integer(performanceId), new Integer(type), name, new Boolean(confirmed) }; return update(params); } } private class MaxRowsUpdater extends SqlUpdate { public MaxRowsUpdater() { setSql(UPDATE); setDataSource(mockDataSource); setMaxRowsAffected(5); compile(); } public int run() { return update(); } } private class RequiredRowsUpdater extends SqlUpdate { public RequiredRowsUpdater() { setSql(UPDATE); setDataSource(mockDataSource); setRequiredRowsAffected(3); compile(); } public int run() { return update(); } } }