SqlRunner.java

  1. /*
  2.  *    Copyright 2009-2023 the original author or authors.
  3.  *
  4.  *    Licensed under the Apache License, Version 2.0 (the "License");
  5.  *    you may not use this file except in compliance with the License.
  6.  *    You may obtain a copy of the License at
  7.  *
  8.  *       https://www.apache.org/licenses/LICENSE-2.0
  9.  *
  10.  *    Unless required by applicable law or agreed to in writing, software
  11.  *    distributed under the License is distributed on an "AS IS" BASIS,
  12.  *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13.  *    See the License for the specific language governing permissions and
  14.  *    limitations under the License.
  15.  */
  16. package org.apache.ibatis.jdbc;

  17. import java.sql.Connection;
  18. import java.sql.PreparedStatement;
  19. import java.sql.ResultSet;
  20. import java.sql.ResultSetMetaData;
  21. import java.sql.SQLException;
  22. import java.sql.Statement;
  23. import java.util.ArrayList;
  24. import java.util.HashMap;
  25. import java.util.Iterator;
  26. import java.util.List;
  27. import java.util.Locale;
  28. import java.util.Map;

  29. import org.apache.ibatis.io.Resources;
  30. import org.apache.ibatis.type.TypeHandler;
  31. import org.apache.ibatis.type.TypeHandlerRegistry;

  32. /**
  33.  * @author Clinton Begin
  34.  */
  35. public class SqlRunner {

  36.   public static final int NO_GENERATED_KEY = Integer.MIN_VALUE + 1001;

  37.   private final Connection connection;
  38.   private final TypeHandlerRegistry typeHandlerRegistry;
  39.   private boolean useGeneratedKeySupport;

  40.   public SqlRunner(Connection connection) {
  41.     this.connection = connection;
  42.     this.typeHandlerRegistry = new TypeHandlerRegistry();
  43.   }

  44.   public void setUseGeneratedKeySupport(boolean useGeneratedKeySupport) {
  45.     this.useGeneratedKeySupport = useGeneratedKeySupport;
  46.   }

  47.   /**
  48.    * Executes a SELECT statement that returns one row.
  49.    *
  50.    * @param sql
  51.    *          The SQL
  52.    * @param args
  53.    *          The arguments to be set on the statement.
  54.    *
  55.    * @return The row expected.
  56.    *
  57.    * @throws SQLException
  58.    *           If less or more than one row is returned
  59.    */
  60.   public Map<String, Object> selectOne(String sql, Object... args) throws SQLException {
  61.     List<Map<String, Object>> results = selectAll(sql, args);
  62.     if (results.size() != 1) {
  63.       throw new SQLException("Statement returned " + results.size() + " results where exactly one (1) was expected.");
  64.     }
  65.     return results.get(0);
  66.   }

  67.   /**
  68.    * Executes a SELECT statement that returns multiple rows.
  69.    *
  70.    * @param sql
  71.    *          The SQL
  72.    * @param args
  73.    *          The arguments to be set on the statement.
  74.    *
  75.    * @return The list of rows expected.
  76.    *
  77.    * @throws SQLException
  78.    *           If statement preparation or execution fails
  79.    */
  80.   public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
  81.     try (PreparedStatement ps = connection.prepareStatement(sql)) {
  82.       setParameters(ps, args);
  83.       try (ResultSet rs = ps.executeQuery()) {
  84.         return getResults(rs);
  85.       }
  86.     }
  87.   }

  88.   /**
  89.    * Executes an INSERT statement.
  90.    *
  91.    * @param sql
  92.    *          The SQL
  93.    * @param args
  94.    *          The arguments to be set on the statement.
  95.    *
  96.    * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
  97.    *
  98.    * @throws SQLException
  99.    *           If statement preparation or execution fails
  100.    */
  101.   public int insert(String sql, Object... args) throws SQLException {
  102.     PreparedStatement ps;
  103.     if (useGeneratedKeySupport) {
  104.       ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
  105.     } else {
  106.       ps = connection.prepareStatement(sql);
  107.     }

  108.     try {
  109.       setParameters(ps, args);
  110.       ps.executeUpdate();
  111.       if (useGeneratedKeySupport) {
  112.         try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
  113.           List<Map<String, Object>> keys = getResults(generatedKeys);
  114.           if (keys.size() == 1) {
  115.             Map<String, Object> key = keys.get(0);
  116.             Iterator<Object> i = key.values().iterator();
  117.             if (i.hasNext()) {
  118.               Object genkey = i.next();
  119.               if (genkey != null) {
  120.                 try {
  121.                   return Integer.parseInt(genkey.toString());
  122.                 } catch (NumberFormatException e) {
  123.                   // ignore, no numeric key support
  124.                 }
  125.               }
  126.             }
  127.           }
  128.         }
  129.       }
  130.       return NO_GENERATED_KEY;
  131.     } finally {
  132.       try {
  133.         ps.close();
  134.       } catch (SQLException e) {
  135.         // ignore
  136.       }
  137.     }
  138.   }

  139.   /**
  140.    * Executes an UPDATE statement.
  141.    *
  142.    * @param sql
  143.    *          The SQL
  144.    * @param args
  145.    *          The arguments to be set on the statement.
  146.    *
  147.    * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
  148.    *
  149.    * @throws SQLException
  150.    *           If statement preparation or execution fails
  151.    */
  152.   public int update(String sql, Object... args) throws SQLException {
  153.     try (PreparedStatement ps = connection.prepareStatement(sql)) {
  154.       setParameters(ps, args);
  155.       return ps.executeUpdate();
  156.     }
  157.   }

  158.   /**
  159.    * Executes a DELETE statement.
  160.    *
  161.    * @param sql
  162.    *          The SQL
  163.    * @param args
  164.    *          The arguments to be set on the statement.
  165.    *
  166.    * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
  167.    *
  168.    * @throws SQLException
  169.    *           If statement preparation or execution fails
  170.    */
  171.   public int delete(String sql, Object... args) throws SQLException {
  172.     return update(sql, args);
  173.   }

  174.   /**
  175.    * Executes any string as a JDBC Statement. Good for DDL
  176.    *
  177.    * @param sql
  178.    *          The SQL
  179.    *
  180.    * @throws SQLException
  181.    *           If statement preparation or execution fails
  182.    */
  183.   public void run(String sql) throws SQLException {
  184.     try (Statement stmt = connection.createStatement()) {
  185.       stmt.execute(sql);
  186.     }
  187.   }

  188.   /**
  189.    * @deprecated Since 3.5.4, this method is deprecated. Please close the {@link Connection} outside of this class.
  190.    */
  191.   @Deprecated
  192.   public void closeConnection() {
  193.     try {
  194.       connection.close();
  195.     } catch (SQLException e) {
  196.       // ignore
  197.     }
  198.   }

  199.   private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
  200.     for (int i = 0, n = args.length; i < n; i++) {
  201.       if (args[i] == null) {
  202.         throw new SQLException(
  203.             "SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
  204.       }
  205.       if (args[i] instanceof Null) {
  206.         ((Null) args[i]).getTypeHandler().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
  207.       } else {
  208.         TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
  209.         if (typeHandler == null) {
  210.           throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
  211.         } else {
  212.           typeHandler.setParameter(ps, i + 1, args[i], null);
  213.         }
  214.       }
  215.     }
  216.   }

  217.   private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
  218.     List<Map<String, Object>> list = new ArrayList<>();
  219.     List<String> columns = new ArrayList<>();
  220.     List<TypeHandler<?>> typeHandlers = new ArrayList<>();
  221.     ResultSetMetaData rsmd = rs.getMetaData();
  222.     for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
  223.       columns.add(rsmd.getColumnLabel(i + 1));
  224.       try {
  225.         Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
  226.         TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
  227.         if (typeHandler == null) {
  228.           typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
  229.         }
  230.         typeHandlers.add(typeHandler);
  231.       } catch (Exception e) {
  232.         typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
  233.       }
  234.     }
  235.     while (rs.next()) {
  236.       Map<String, Object> row = new HashMap<>();
  237.       for (int i = 0, n = columns.size(); i < n; i++) {
  238.         String name = columns.get(i);
  239.         TypeHandler<?> handler = typeHandlers.get(i);
  240.         row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
  241.       }
  242.       list.add(row);
  243.     }
  244.     return list;
  245.   }

  246. }