SqlRunner.java
- /*
- * Copyright 2009-2023 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
- *
- * https://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.apache.ibatis.jdbc;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.ResultSetMetaData;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.Iterator;
- import java.util.List;
- import java.util.Locale;
- import java.util.Map;
- import org.apache.ibatis.io.Resources;
- import org.apache.ibatis.type.TypeHandler;
- import org.apache.ibatis.type.TypeHandlerRegistry;
- /**
- * @author Clinton Begin
- */
- public class SqlRunner {
- public static final int NO_GENERATED_KEY = Integer.MIN_VALUE + 1001;
- private final Connection connection;
- private final TypeHandlerRegistry typeHandlerRegistry;
- private boolean useGeneratedKeySupport;
- public SqlRunner(Connection connection) {
- this.connection = connection;
- this.typeHandlerRegistry = new TypeHandlerRegistry();
- }
- public void setUseGeneratedKeySupport(boolean useGeneratedKeySupport) {
- this.useGeneratedKeySupport = useGeneratedKeySupport;
- }
- /**
- * Executes a SELECT statement that returns one row.
- *
- * @param sql
- * The SQL
- * @param args
- * The arguments to be set on the statement.
- *
- * @return The row expected.
- *
- * @throws SQLException
- * If less or more than one row is returned
- */
- public Map<String, Object> selectOne(String sql, Object... args) throws SQLException {
- List<Map<String, Object>> results = selectAll(sql, args);
- if (results.size() != 1) {
- throw new SQLException("Statement returned " + results.size() + " results where exactly one (1) was expected.");
- }
- return results.get(0);
- }
- /**
- * Executes a SELECT statement that returns multiple rows.
- *
- * @param sql
- * The SQL
- * @param args
- * The arguments to be set on the statement.
- *
- * @return The list of rows expected.
- *
- * @throws SQLException
- * If statement preparation or execution fails
- */
- public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
- try (PreparedStatement ps = connection.prepareStatement(sql)) {
- setParameters(ps, args);
- try (ResultSet rs = ps.executeQuery()) {
- return getResults(rs);
- }
- }
- }
- /**
- * Executes an INSERT statement.
- *
- * @param sql
- * The SQL
- * @param args
- * The arguments to be set on the statement.
- *
- * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
- *
- * @throws SQLException
- * If statement preparation or execution fails
- */
- public int insert(String sql, Object... args) throws SQLException {
- PreparedStatement ps;
- if (useGeneratedKeySupport) {
- ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
- } else {
- ps = connection.prepareStatement(sql);
- }
- try {
- setParameters(ps, args);
- ps.executeUpdate();
- if (useGeneratedKeySupport) {
- try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
- List<Map<String, Object>> keys = getResults(generatedKeys);
- if (keys.size() == 1) {
- Map<String, Object> key = keys.get(0);
- Iterator<Object> i = key.values().iterator();
- if (i.hasNext()) {
- Object genkey = i.next();
- if (genkey != null) {
- try {
- return Integer.parseInt(genkey.toString());
- } catch (NumberFormatException e) {
- // ignore, no numeric key support
- }
- }
- }
- }
- }
- }
- return NO_GENERATED_KEY;
- } finally {
- try {
- ps.close();
- } catch (SQLException e) {
- // ignore
- }
- }
- }
- /**
- * Executes an UPDATE statement.
- *
- * @param sql
- * The SQL
- * @param args
- * The arguments to be set on the statement.
- *
- * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
- *
- * @throws SQLException
- * If statement preparation or execution fails
- */
- public int update(String sql, Object... args) throws SQLException {
- try (PreparedStatement ps = connection.prepareStatement(sql)) {
- setParameters(ps, args);
- return ps.executeUpdate();
- }
- }
- /**
- * Executes a DELETE statement.
- *
- * @param sql
- * The SQL
- * @param args
- * The arguments to be set on the statement.
- *
- * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
- *
- * @throws SQLException
- * If statement preparation or execution fails
- */
- public int delete(String sql, Object... args) throws SQLException {
- return update(sql, args);
- }
- /**
- * Executes any string as a JDBC Statement. Good for DDL
- *
- * @param sql
- * The SQL
- *
- * @throws SQLException
- * If statement preparation or execution fails
- */
- public void run(String sql) throws SQLException {
- try (Statement stmt = connection.createStatement()) {
- stmt.execute(sql);
- }
- }
- /**
- * @deprecated Since 3.5.4, this method is deprecated. Please close the {@link Connection} outside of this class.
- */
- @Deprecated
- public void closeConnection() {
- try {
- connection.close();
- } catch (SQLException e) {
- // ignore
- }
- }
- private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
- for (int i = 0, n = args.length; i < n; i++) {
- if (args[i] == null) {
- throw new SQLException(
- "SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
- }
- if (args[i] instanceof Null) {
- ((Null) args[i]).getTypeHandler().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
- } else {
- TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
- if (typeHandler == null) {
- throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
- } else {
- typeHandler.setParameter(ps, i + 1, args[i], null);
- }
- }
- }
- }
- private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
- List<Map<String, Object>> list = new ArrayList<>();
- List<String> columns = new ArrayList<>();
- List<TypeHandler<?>> typeHandlers = new ArrayList<>();
- ResultSetMetaData rsmd = rs.getMetaData();
- for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
- columns.add(rsmd.getColumnLabel(i + 1));
- try {
- Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
- TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
- if (typeHandler == null) {
- typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
- }
- typeHandlers.add(typeHandler);
- } catch (Exception e) {
- typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
- }
- }
- while (rs.next()) {
- Map<String, Object> row = new HashMap<>();
- for (int i = 0, n = columns.size(); i < n; i++) {
- String name = columns.get(i);
- TypeHandler<?> handler = typeHandlers.get(i);
- row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
- }
- list.add(row);
- }
- return list;
- }
- }