View Javadoc
1   /**
2    *    Copyright 2009-2019 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    *       http://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  
18  import java.sql.Connection;
19  import java.sql.PreparedStatement;
20  import java.sql.ResultSet;
21  import java.sql.ResultSetMetaData;
22  import java.sql.SQLException;
23  import java.sql.Statement;
24  import java.util.ArrayList;
25  import java.util.HashMap;
26  import java.util.Iterator;
27  import java.util.List;
28  import java.util.Locale;
29  import java.util.Map;
30  
31  import org.apache.ibatis.io.Resources;
32  import org.apache.ibatis.type.TypeHandler;
33  import org.apache.ibatis.type.TypeHandlerRegistry;
34  
35  /**
36   * @author Clinton Begin
37   */
38  public class SqlRunner {
39  
40    public static final int NO_GENERATED_KEY = Integer.MIN_VALUE + 1001;
41  
42    private final Connection connection;
43    private final TypeHandlerRegistry typeHandlerRegistry;
44    private boolean useGeneratedKeySupport;
45  
46    public SqlRunner(Connection connection) {
47      this.connection = connection;
48      this.typeHandlerRegistry = new TypeHandlerRegistry();
49    }
50  
51    public void setUseGeneratedKeySupport(boolean useGeneratedKeySupport) {
52      this.useGeneratedKeySupport = useGeneratedKeySupport;
53    }
54  
55    /**
56     * Executes a SELECT statement that returns one row.
57     *
58     * @param sql  The SQL
59     * @param args The arguments to be set on the statement.
60     * @return The row expected.
61     * @throws SQLException If less or more than one row is returned
62     */
63    public Map<String, Object> selectOne(String sql, Object... args) throws SQLException {
64      List<Map<String, Object>> results = selectAll(sql, args);
65      if (results.size() != 1) {
66        throw new SQLException("Statement returned " + results.size() + " results where exactly one (1) was expected.");
67      }
68      return results.get(0);
69    }
70  
71    /**
72     * Executes a SELECT statement that returns multiple rows.
73     *
74     * @param sql  The SQL
75     * @param args The arguments to be set on the statement.
76     * @return The list of rows expected.
77     * @throws SQLException If statement preparation or execution fails
78     */
79    public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
80      PreparedStatement ps = connection.prepareStatement(sql);
81      try {
82        setParameters(ps, args);
83        ResultSet rs = ps.executeQuery();
84        return getResults(rs);
85      } finally {
86        try {
87          ps.close();
88        } catch (SQLException e) {
89          //ignore
90        }
91      }
92    }
93  
94    /**
95     * Executes an INSERT statement.
96     *
97     * @param sql  The SQL
98     * @param args The arguments to be set on the statement.
99     * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
100    * @throws SQLException If statement preparation or execution fails
101    */
102   public int insert(String sql, Object... args) throws SQLException {
103     PreparedStatement ps;
104     if (useGeneratedKeySupport) {
105       ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
106     } else {
107       ps = connection.prepareStatement(sql);
108     }
109 
110     try {
111       setParameters(ps, args);
112       ps.executeUpdate();
113       if (useGeneratedKeySupport) {
114         List<Map<String, Object>> keys = getResults(ps.getGeneratedKeys());
115         if (keys.size() == 1) {
116           Map<String, Object> key = keys.get(0);
117           Iterator<Object> i = key.values().iterator();
118           if (i.hasNext()) {
119             Object genkey = i.next();
120             if (genkey != null) {
121               try {
122                 return Integer.parseInt(genkey.toString());
123               } catch (NumberFormatException e) {
124                 //ignore, no numeric key support
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   /**
141    * Executes an UPDATE statement.
142    *
143    * @param sql  The SQL
144    * @param args The arguments to be set on the statement.
145    * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
146    * @throws SQLException If statement preparation or execution fails
147    */
148   public int update(String sql, Object... args) throws SQLException {
149     PreparedStatement ps = connection.prepareStatement(sql);
150     try {
151       setParameters(ps, args);
152       return ps.executeUpdate();
153     } finally {
154       try {
155         ps.close();
156       } catch (SQLException e) {
157         //ignore
158       }
159     }
160   }
161 
162   /**
163    * Executes a DELETE statement.
164    *
165    * @param sql  The SQL
166    * @param args The arguments to be set on the statement.
167    * @return The number of rows impacted or BATCHED_RESULTS if the statements are being batched.
168    * @throws SQLException If statement preparation or execution fails
169    */
170   public int delete(String sql, Object... args) throws SQLException {
171     return update(sql, args);
172   }
173 
174   /**
175    * Executes any string as a JDBC Statement.
176    * Good for DDL
177    *
178    * @param sql The SQL
179    * @throws SQLException If statement preparation or execution fails
180    */
181   public void run(String sql) throws SQLException {
182     Statement stmt = connection.createStatement();
183     try {
184       stmt.execute(sql);
185     } finally {
186       try {
187         stmt.close();
188       } catch (SQLException e) {
189         //ignore
190       }
191     }
192   }
193 
194   /**
195    * @deprecated Since 3.5.4, this method is deprecated. Please close the {@link Connection} outside of this class.
196    */
197   @Deprecated
198   public void closeConnection() {
199     try {
200       connection.close();
201     } catch (SQLException e) {
202       //ignore
203     }
204   }
205 
206   private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
207     for (int i = 0, n = args.length; i < n; i++) {
208       if (args[i] == null) {
209         throw new SQLException("SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
210       } else if (args[i] instanceof Null) {
211         ((Null) args[i]).getTypeHanNull().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
212       } else {
213         TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
214         if (typeHandler == null) {
215           throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
216         } else {
217           typeHandler.setParameter(ps, i + 1, args[i], null);
218         }
219       }
220     }
221   }
222 
223   private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
224     try {
225       List<Map<String, Object>> list = new ArrayList<>();
226       List<String> columns = new ArrayList<>();
227       List<TypeHandler<?>> typeHandlers = new ArrayList<>();
228       ResultSetMetaData rsmd = rs.getMetaData();
229       for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
230         columns.add(rsmd.getColumnLabel(i + 1));
231         try {
232           Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
233           TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
234           if (typeHandler == null) {
235             typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
236           }
237           typeHandlers.add(typeHandler);
238         } catch (Exception e) {
239           typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
240         }
241       }
242       while (rs.next()) {
243         Map<String, Object> row = new HashMap<>();
244         for (int i = 0, n = columns.size(); i < n; i++) {
245           String name = columns.get(i);
246           TypeHandler<?> handler = typeHandlers.get(i);
247           row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
248         }
249         list.add(row);
250       }
251       return list;
252     } finally {
253       if (rs != null) {
254         try {
255           rs.close();
256         } catch (Exception e) {
257           // ignore
258         }
259       }
260     }
261   }
262 
263 }