查询

@param <T> 结果集需要处理的对象类型
@param sql 查询语句
@param rsh 结果集处理对象
@param params 参数
@return 结果对象
@throws SQLException SQL执行异常
961280
public <T> T query(String sql, RsHandler<T> rsh, Object... params) throws SQLException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return SqlExecutor.query(conn, sql, rsh, params);
		} catch (SQLException e) {
			throw e;
		} finally {
			this.closeConnection(conn);
		}
	}
935399
private void updateResultSetCurrentLine(ResultSet rs, QueryParameters params) throws SQLException {
        Object value = null;
        Object prevValue = null;

        int columnIndex = -1;
        boolean readOnly = false;
        QueryParameters prevParams = get(this.currentIndex);
        ResultSetMetaData rsmd = rs.getMetaData();

        QueryParameters processedParams = typeHandler.processInput(stmt, params);

        for (String parameterName : processedParams.keySet()) {
            value = processedParams.getValue(parameterName);
            prevValue = prevParams.getValue(parameterName);

            // no need to update value if it is the same
            if (value == prevValue || (value != null && prevValue != null && value.equals(prevValue) == true)) {
                continue;
            }

            columnIndex = rs.findColumn(parameterName);
            readOnly = rsmd.isReadOnly(columnIndex) || rsmd.isAutoIncrement(columnIndex);

            if (readOnly == false) {
                rs.updateObject(parameterName, value);
            }
        }
    }
567096
public static String substitute(String sql, Object... params) {
        try {
            if (params == null || params.length == 0)
                return sql;
            String subst = sql;
            int start = 0;
            int q;
            for (int i = 0; start < subst.length() && (q = subst.indexOf('?', start)) >= 0; i++) {
                Object param = params[i];
                String p = String.valueOf(param);
                if (param != null && !(param instanceof Integer) && !(param instanceof Long))
                    p = "'" + p + "'";
                subst = subst.substring(0, q) + p + subst.substring(q + 1);
                start = q + p.length();
            }
            return subst;
        }
        catch (Throwable t) {
            logger.severeException(t.getMessage(), t);
            return sql;
        }
    }
318214
@SuppressWarnings("unchecked")
    public <N> List<N> findOneColumn(String sql, List<?> params, final int mapperColumnIndex,
                                     final Class<N> resultClass) {

        if (sql == null) {
            recordLog(" param sql is null!");
        }

        if (Number.class.isAssignableFrom(resultClass)) {
            if (resultClass.equals(Long.class)) {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        if (n == null) {
                            return null;
                        }
                        return (N) Long.valueOf(n.longValue());
                    }
                });
            } else if (resultClass.equals(Integer.class)) {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        if (n == null) {
                            return null;
                        }
                        return (N) Integer.valueOf(n.intValue());
                    }
                });
            } else if (resultClass.equals(Float.class)) {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        if (n == null) {
                            return null;
                        }
                        return (N) Float.valueOf(n.floatValue());
                    }
                });
            } else if (resultClass.equals(Double.class)) {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        if (n == null) {
                            return null;
                        }
                        return (N) Double.valueOf(n.doubleValue());
                    }
                });
            } else if (resultClass.equals(Short.class)) {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        if (n == null) {
                            return null;
                        }
                        return (N) Short.valueOf(n.shortValue());
                    }
                });
            } else if (resultClass.equals(Byte.class)) {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        if (n == null) {
                            return null;
                        }
                        return (N) Byte.valueOf(n.byteValue());
                    }
                });
            } else {
                return findBySQL(sql, params, new RowMapper<N>() {
                    public N mapRow(ResultSet rs, int count) throws SQLException {
                        Number n = (Number) (rs.getObject(mapperColumnIndex));
                        return (N) n;
                    }
                });
            }
        } else if (resultClass.equals(String.class)) {
            return findBySQL(sql, params, new RowMapper<N>() {
                public N mapRow(ResultSet rs, int count) throws SQLException {
                    return (N) (rs.getString(mapperColumnIndex));
                }
            });
        } else if (resultClass.equals(Date.class)) {
            return findBySQL(sql, params, new RowMapper<N>() {
                public N mapRow(ResultSet rs, int count) throws SQLException {
                    return (N) (rs.getDate(mapperColumnIndex));
                }
            });
        } else {
            throw new RuntimeException("not supported type");
        }
    }
368113
public static int[] executeBatch(Connection conn, String... sqls) throws SQLException {
		return executeBatch(conn, new ArrayIter<String>(sqls));
	}
888162
public static <T> T query(PreparedStatement ps, RsHandler<T> rsh, Object... params) throws SQLException {
		StatementUtil.fillParams(ps, params);
		return executeQuery(ps, rsh);
	}
686278
@Override
	public E createRecord(final ResultSet rs) throws SQLException {
		try {
			if (columnLabels == null) {
				ResultSetMetaData rsmd = rs.getMetaData();
				columnCount = rsmd.getColumnCount();

				// columnLabelsは1始まりの配列で値を格納
				columnLabels = new String[columnCount + 1];
				for (int i = 1; i <= columnCount; i++) {
					columnLabels[i] = rsmd.getColumnLabel(i);
				}
			}

			E rec = constructor.newInstance();
			for (MappingColumn column : columns) {
				bindValue(rec, rs, column);
			}
			return rec;
		} catch (InstantiationException | IllegalAccessException | InvocationTargetException e) {
			LOG.error("Error!!", e);
			throw new UroborosqlRuntimeException(e);
		} catch (SQLException | RuntimeException | Error e) {
			LOG.error("Error!!", e);
			throw e;
		}
	}
1185445
private static String stmtAssignSqlParams(PreparedStatement stmt, List<SqlParam> sqlParams) throws SQLException {
		String cursorParam = null;
		if (sqlParams == null)
			return null;

		CallableStatement cstmt = null;
		if (stmt instanceof CallableStatement) {
			cstmt = (CallableStatement) stmt;
		}

		int pos = 0;
		for (SqlParam param : sqlParams) {
			pos++;
			boolean isPositionalParam = (param.code == null); // named vs positional param
			if (param.type == null)
				throw new SQLDataException("Please provide parameter type for param.code='" + param.code + "' pos=" + pos);
			if (isPositionalParam == false && cstmt == null)
				throw new SQLDataException("To use named params, need to use CallableStatement (param.code='" + param.code + "' pos=" + pos + ")");

			switch (param.type) {
				case CURSOR:
					if (cstmt == null)
						throw new SQLDataException("Param type CURSOR is not allowed for preparedStatement");
					cursorParam = param.code;
					if (param.code == null)
						cstmt.registerOutParameter(pos, JetJdbcParamType.CURSOR.oracleId);
					else
						cstmt.registerOutParameter(param.code, JetJdbcParamType.CURSOR.oracleId);
					break;
				case STRING:
					if (isPositionalParam)
						stmt.setString(pos, param.value == null ? null : param.value.toString());
					else
						cstmt.setString(param.code, param.value == null ? null : param.value.toString());
					break;
				case INTEGER: case LONG:
					if (param.value == null) {
						if (isPositionalParam)
							stmt.setNull(pos, Types.BIGINT);
						else
							cstmt.setNull(param.code, Types.BIGINT);
					} else {
						long val = 0;
						if (param.value.getClass().isAssignableFrom(Integer.class)) {
							val = (Integer) param.value;
						} else {
							val = (Long) param.value;
						}
						if (isPositionalParam)
							stmt.setLong(pos, val);
						else
							cstmt.setLong(param.code, val);
					}
					break;
				case TIMESTAMP:
					Date date1 = (Date) param.value;
					if (isPositionalParam)
						stmt.setTimestamp(pos, param.value == null ? null : new Timestamp(date1.getTime()));
					else
						cstmt.setTimestamp(param.code, param.value == null ? null : new Timestamp(date1.getTime()));
					break;
				case DATE:
					Date date2 = (Date) param.value;
					if (isPositionalParam)
						stmt.setDate(pos, param.value == null ? null : new java.sql.Date(date2.getTime()));
					else
						cstmt.setDate(param.code, param.value == null ? null : new java.sql.Date(date2.getTime()));
					break;
				case BLOB:
					if (param.value == null) {
						if (isPositionalParam)
							stmt.setBlob(pos, new ByteArrayInputStream(new byte[0]));
						else
							cstmt.setBlob(param.code, new ByteArrayInputStream(new byte[0]));
					} else {
						byte[] bytes = (byte[]) param.value;
						if (isPositionalParam)
							stmt.setBlob(pos, new ByteArrayInputStream(bytes), (int) bytes.length);
						else
							cstmt.setBlob(param.code, new ByteArrayInputStream(bytes), (int) bytes.length);
					}
					break;
				default:
					throw new IllegalStateException("Data type '" + param.type.toString() + "' is not supported yet");
			}
		}
		return cursorParam;
	}
1313712
public <T> T find(Connection conn, Query query, RsHandler<T> rsh) throws SQLException {
		checkConn(conn);
		Assert.notNull(query, "[query] is null !");
		
		PreparedStatement ps = null;
		try {
			ps = dialect.psForFind(conn, query);
			return SqlExecutor.query(ps, rsh);
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(ps);
		}
	}
559538
public static <T> T query(Connection conn, String sql, RsHandler<T> rsh, Map<String, Object> paramMap) throws SQLException {
		final NamedSql namedSql = new NamedSql(sql, paramMap);
		return query(conn, namedSql.getSql(), rsh, namedSql.getParams());
	}
6170
public static ResultSet callQuery(Connection conn, String sql, Object... params) throws SQLException {
		CallableStatement proc = null;
		try {
			proc = StatementUtil.prepareCall(conn, sql, params);
			return proc.executeQuery();
		} finally {
			DbUtil.close(proc);
		}
	}
491129
public static PreparedStatement fillParams(PreparedStatement ps, Object... params) throws SQLException {
		if (ArrayUtil.isEmpty(params)) {
			return ps;// 无参数
		}
		Object param;
		for (int i = 0; i < params.length; i++) {
			int paramIndex = i + 1;
			param = params[i];
			if (null != param) {
				if (param instanceof java.util.Date) {
					// 日期特殊处理
					if (param instanceof java.sql.Date) {
						ps.setDate(paramIndex, (java.sql.Date) param);
					} else if (param instanceof java.sql.Time) {
						ps.setTime(paramIndex, (java.sql.Time) param);
					} else {
						ps.setTimestamp(paramIndex, SqlUtil.toSqlTimestamp((java.util.Date) param));
					}
				} else if (param instanceof Number) {
					// 针对大数字类型的特殊处理
					if (param instanceof BigInteger) {
						// BigInteger转为Long
						ps.setLong(paramIndex, ((BigInteger) param).longValue());
					} else if (param instanceof BigDecimal) {
						// BigDecimal的转换交给JDBC驱动处理
						ps.setBigDecimal(paramIndex, (BigDecimal) param);
					} else {
						// 普通数字类型按照默认传入
						ps.setObject(paramIndex, param);
					}
				} else {
					ps.setObject(paramIndex, param);
				}
			} else {
				final ParameterMetaData pmd = ps.getParameterMetaData();
				int sqlType = Types.VARCHAR;
				try {
					sqlType = pmd.getParameterType(paramIndex);
				} catch (SQLException e) {
					// ignore
					// log.warn("Null param of index [{}] type get failed, by: {}", paramIndex, e.getMessage());
				}
				ps.setNull(paramIndex, sqlType);
			}
		}
		return ps;
	}
880235
public <T> T find(Query query, RsHandler<T> rsh) throws SQLException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return runner.find(conn, query, rsh);
		} catch (SQLException e) {
			throw e;
		} finally {
			this.closeConnection(conn);
		}
	}
1106856
public static String execQueryAllRows(@NotNull final SQLInputs sqlInputs) throws Exception {
        ConnectionService connectionService = new ConnectionService();
        try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {
            connection.setReadOnly(true);

            Statement statement = connection.createStatement(sqlInputs.getResultSetType(), sqlInputs.getResultSetConcurrency());

            statement.setQueryTimeout(sqlInputs.getTimeout());
            final ResultSet resultSet = statement.executeQuery(sqlInputs.getSqlCommand());

            final String resultSetToDelimitedColsAndRows = Format.resultSetToDelimitedColsAndRows(resultSet, sqlInputs.isNetcool(), sqlInputs.getColDelimiter(), sqlInputs.getRowDelimiter());
            if (resultSet != null) {
                resultSet.close();
            }
            return resultSetToDelimitedColsAndRows;
        }
    }
1100848
public static SqlParamsPairs handleIn(String sql, Object[] params){
		
		//split with question mark placeholder
		String[] sqlPieces = sql.split("\\?");
		
		//question mark placeholder list
		String[] questionPlaceholders;
		if(sql.endsWith("?")){
			questionPlaceholders = new String[sqlPieces.length];
		}else{
			questionPlaceholders = new String[sqlPieces.length-1];
		}
		for(int i=0;i<questionPlaceholders.length;i++){
			questionPlaceholders[i]="?";
		}
		
		List<Object> plist = new ArrayList<Object>();
		for(int i=0; i<params.length; i++){
			Object p = params[i];
			if(p.getClass().equals(ArrayList.class)){
				
				//change ? => (?,?,?...)
				//change list to objects
				StringBuilder sb = new StringBuilder();
				sb.append("(");
				ArrayList inParams = (ArrayList)p;
				for(int j=0;j<inParams.size();j++){
					if(j!=0){
						sb.append(",");
					}
					sb.append("?");
					//split list to objects
					plist.add(inParams.get(j));
				}
				sb.append(")");
				questionPlaceholders[i] = sb.toString();
				
			}else{
				plist.add(p);
			}
		}
		
		//join sql
		StringBuilder sqlsb = new StringBuilder();
		for(int i=0;i<sqlPieces.length;i++){
			
			sqlsb.append(sqlPieces[i]);
			if(i<questionPlaceholders.length){
				sqlsb.append(questionPlaceholders[i]);
			}
		}
		
		
		SqlParamsPairs spPairs = new SqlParamsPairs(sqlsb.toString(),plist.toArray());
		
		return spPairs;
	}
172274
public int count(String sql, Object[] params) {
				
		int rowCount = 0;
		try{
			Map<String, Object> resultMap = null;
			if (params == null || params.length == 0) {
				resultMap = getProxy().queryForMap(sql);
			} else {
				resultMap = getProxy().queryForMap(sql, params);
			}
			Iterator<Map.Entry<String, Object>> it = resultMap.entrySet().iterator();
			if(it.hasNext()){
				Map.Entry<String, Object> entry = it.next();
				rowCount = ((Long)entry.getValue()).intValue();
			}
		}catch(EmptyResultDataAccessException e){
			
		}
		
		
		return rowCount;
	}
977223
public static PreparedStatement setStrings(PreparedStatement stmt, String... params)
			throws SQLException {
		return setStrings(1, stmt, params);
	}