public boolean updateByParams(String sql, List params) throws SQLException { // 影响行数 int result = -1; pstmt = conn.prepareStatement(sql); int index = 1; // 填充sql语句中的占位符 if (null != params && !params.isEmpty()) { for (int i = 0; i < params.size(); i ++) { pstmt.setObject(index ++, params.get(i)); } } result = pstmt.executeUpdate(); return result > 0 ? true : false; }
public List<Map> selectByParams(String sql, List params) throws SQLException { List<Map> list = new ArrayList<> (); int index = 1; pstmt = conn.prepareStatement(sql); if (null != params && !params.isEmpty()) { for (int i = 0; i < params.size(); i ++) { pstmt.setObject(index++, params.get(i)); } } rs = pstmt.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int colsLen = metaData.getColumnCount(); while (rs.next()) { Map map = new HashMap(colsLen); for (int i = 0; i < colsLen; i ++) { String columnName = metaData.getColumnName(i + 1); Object columnValue = rs.getObject(columnName); if (null == columnValue) { columnValue = ""; } map.put(columnName, columnValue); } list.add(map); } return list; }
@Test public void testQueryForObjectWithMapAndInteger() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); Map<String, Object> params = new HashMap<>(); params.put("id", 3); Object o = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id", params, Integer.class); assertTrue("Correct result type", o instanceof Integer); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?"); verify(preparedStatement).setObject(1, 3); }
private void doTestQueryForListWithArgs(String sql) throws Exception { given(this.resultSet.next()).willReturn(true, true, false); given(this.resultSet.getObject(1)).willReturn(11, 12); List<Map<String, Object>> li = this.template.queryForList(sql, new Object[] {3}); assertEquals("All rows returned", 2, li.size()); assertEquals("First row is Integer", 11, ((Integer) li.get(0).get("age")).intValue()); assertEquals("Second row is Integer", 12, ((Integer) li.get(1).get("age")).intValue()); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
ResultSet rs = null; try { ps = statementProxy.getConnection().prepareStatement(selectSQLAppender.toString()); ps.setObject(i, pkValues.get(i - 1)); rs = ps.executeQuery(); afterImage = TableRecords.buildRecords(getTableMeta(), rs);
private static Long executeInsert(PreparedStatement preparedStatement, Object... values) throws SQLException { for (int i = 0; i < values.length; i++) { preparedStatement.setObject(i + 1, values[i]); } preparedStatement.executeUpdate(); ResultSet rs = preparedStatement.getGeneratedKeys(); if (rs.next()) { return rs.getLong(1); } else return null; }
@Test public void destroyActionThatRemovesNoRowCompletesUnexceptionally() throws Exception { when(statement.executeUpdate()).thenReturn(0); Object destroyKey = "destroyKey"; handler.write(region, Operation.DESTROY, destroyKey, value); verify(statement).setObject(1, destroyKey); verify(statement, times(1)).setObject(anyInt(), any()); verify(statement).close(); }
/** * Executes a prepared query that was generated using generatePreparedQuery. * * @param prep the prepared statement * @param min the lower values * @param max the upper values * @return the result set */ public ResultSet getResult(PreparedStatement prep, int[] min, int[] max) throws SQLException { long[][] ranges = getMortonRanges(min, max); int len = ranges.length; Long[] from = new Long[len]; Long[] to = new Long[len]; for (int i = 0; i < len; i++) { from[i] = ranges[i][0]; to[i] = ranges[i][1]; } prep.setObject(1, from); prep.setObject(2, to); len = min.length; for (int i = 0, idx = 3; i < len; i++) { prep.setInt(idx++, min[i]); prep.setInt(idx++, max[i]); } return prep.executeQuery(); }
@Test public void writeWithNullField() throws Exception { String fieldName = "fieldName"; Object fieldValue = null; int dataType = 0; when(regionMapping.getColumnNameForField(eq(fieldName), any())).thenReturn(fieldName); when(value.getFieldNames()).thenReturn(Arrays.asList(fieldName)); when(value.getField(fieldName)).thenReturn(fieldValue); when(statement.executeUpdate()).thenReturn(1); Object createKey = "createKey"; handler.write(region, Operation.CREATE, createKey, value); verify(statement).setNull(1, dataType); verify(statement).setObject(2, createKey); verify(statement).close(); }
@Override public void executeQuery(String query, @NotNull Object... args) { try (PreparedStatement ps = connection.prepareStatement(query)) { for (int i = 0; i < args.length; i++) ps.setObject(i + 1, args[i]); ResultSet resultSet = ps.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); List<String> headings = new ArrayList<>(columnCount); for (int i = 1; i <= columnCount; i++) headings.add(metaData.getColumnName(i)); List<List<Object>> rows = new ArrayList<>(); while (resultSet.next()) { List<Object> row = new ArrayList<>(columnCount); for (int i = 1; i <= columnCount; i++) { row.add(resultSet.getObject(i)); } rows.add(row); } result.queryResult(headings, rows, query, args); } catch (Throwable t) { result.queryThrown(t, query, args); } } }
@Test public void testQueryForLongWithParamBeanWithCollection() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getLong(1)).willReturn(87L); BeanPropertySqlParameterSource params = new BeanPropertySqlParameterSource(new ParameterCollectionBean(3, 5)); long l = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID IN (:ids)", params, Long.class).longValue(); assertEquals("Return of a long", 87, l); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID IN (?, ?)"); verify(preparedStatement).setObject(1, 3); verify(preparedStatement).setObject(2, 5); }
private int executeUpdate(final String sql, final Object... params) { try (Connection connection = dataSource.getConnection(); PreparedStatement ps = connection.prepareStatement(sql)) { if (params != null) { for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } } return ps.executeUpdate(); } catch (SQLException e) { LOGGER.error("executeUpdate-> " + e.getMessage()); } return 0; }
@Test public void testQueryForListWithArgsAndSingleRowAndColumn() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getObject(1)).willReturn(11); List<Map<String, Object>> li = this.template.queryForList(sql, new Object[] {3}); assertEquals("All rows returned", 1, li.size()); assertEquals("First row is Integer", 11, ((Integer) li.get(0).get("age")).intValue()); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
rs = st.executeQuery(selectSQL); } else { ps = statementProxy.getConnection().prepareStatement(selectSQL); for (int i = 0; i< paramAppender.size(); i++) { ps.setObject(i + 1, paramAppender.get(i)); rs = ps.executeQuery();
@Test public void destroyActionSucceeds() throws Exception { when(statement.executeUpdate()).thenReturn(1); Object destroyKey = "destroyKey"; handler.write(region, Operation.DESTROY, destroyKey, value); verify(statement).setObject(1, destroyKey); verify(statement, times(1)).setObject(anyInt(), any()); verify(statement).close(); }
/** * Execute a SQL prepared query. * * @param preparedQueryString the prepared query string * @param parameters the list of values for parameters in the query; may not be null * @param resultConsumer the consumer of the query results * @return this object for chaining methods together * @throws SQLException if there is an error connecting to the database or executing the statements * @see #execute(Operations) */ public JdbcConnection prepareQuery(String preparedQueryString, List<?> parameters, ParameterResultSetConsumer resultConsumer) throws SQLException { final PreparedStatement statement = createPreparedStatement(preparedQueryString); int index = 1; for (final Object parameter: parameters) { statement.setObject(index++, parameter); } try (ResultSet resultSet = statement.executeQuery()) { if (resultConsumer != null) { resultConsumer.accept(parameters, resultSet); } } return this; }
@Test public void writeWithNullFieldWithDataTypeFromMetaData() throws Exception { String fieldName = "fieldName"; Object fieldValue = null; int dataType = 79; when(tableMetaDataView.getColumnDataType(fieldName)).thenReturn(dataType); when(regionMapping.getColumnNameForField(eq(fieldName), any())).thenReturn(fieldName); when(value.getFieldNames()).thenReturn(Arrays.asList(fieldName)); when(value.getField(fieldName)).thenReturn(fieldValue); when(statement.executeUpdate()).thenReturn(1); Object createKey = "createKey"; handler.write(region, Operation.CREATE, createKey, value); verify(statement).setNull(1, dataType); verify(statement).setObject(2, createKey); verify(statement).close(); }
private List<Map<String, Object>> executeQuery(final String sql, final Object... params) { List<Map<String, Object>> list = null; try (Connection connection = dataSource.getConnection(); PreparedStatement ps = connection.prepareStatement(sql)) { if (params != null) { for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } } try (ResultSet rs = ps.executeQuery()) { ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); list = new ArrayList<>(); while (rs.next()) { Map<String, Object> rowData = new HashMap<>(16); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); } } } catch (SQLException e) { LOGGER.error("executeQuery-> " + e.getMessage()); } return list; } }