获得自定义删除SQL
@param clazz
@param postSql
@return
165357
public static <T> String getCustomDeleteSQL(Class<T> clazz, String postSql) {
		StringBuilder sql = new StringBuilder();
		
		Table table = DOInfoReader.getTable(clazz);
		
		sql.append("DELETE FROM ");
		sql.append(getTableName(table));
		
		sql.append(autoSetSoftDeleted(postSql, clazz));
		
		return sql.toString();
	}
527184
@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T> list(String sql, Object[] params, Class<T> clazz) {
		
		//call jdbcTemplate to query for result
		List<T> list = null;
		if (params == null || params.length == 0) {
			list = getProxy().query(sql, new BeanPropertyRowMapper(clazz));
		} else {
			list = getProxy().query(sql, params, new BeanPropertyRowMapper(clazz));
		}
		
		//return list
		return list;
	}
1109405
public static <T> String autoSetSoftDeleted(String whereSql, Class<?> clazz, String extraWhere) {
		if(whereSql == null) {
			whereSql = "";
		}
		extraWhere = extraWhere == null ? "" : extraWhere.trim();
		if(!extraWhere.isEmpty()) {
			extraWhere = "(" + extraWhere + ")";
		}
		
		String deletedExpression = "";
		
		// 处理join方式clazz
		JoinTable joinTable = DOInfoReader.getJoinTable(clazz);
		if(joinTable != null) {
			Field leftTableField = DOInfoReader.getJoinLeftTable(clazz);
			Field rightTableField = DOInfoReader.getJoinRightTable(clazz);
			
			JoinLeftTable joinLeftTable = leftTableField.getAnnotation(JoinLeftTable.class);
			JoinRightTable joinRightTable = rightTableField.getAnnotation(JoinRightTable.class);
			
			Field softDeleteT1 = DOInfoReader.getSoftDeleteColumn(leftTableField.getType());
			Field softDeleteT2 = DOInfoReader.getSoftDeleteColumn(rightTableField.getType());
			
			if(softDeleteT1 == null && softDeleteT2 == null) {
				try {
					return " " + insertWhereAndExpression(whereSql, extraWhere);
				} catch (JSQLParserException e) {
					LOGGER.error("Bad sql syntax,whereSql:{},deletedExpression:{}",
							whereSql, deletedExpression, e);
					throw new BadSQLSyntaxException(e);
				}
			}
			
			StringBuilder deletedExpressionSb = new StringBuilder();
			if(softDeleteT1 != null) {
				Column softDeleteColumn = softDeleteT1.getAnnotation(Column.class);
				String columnName = getColumnName(softDeleteColumn);
				if(joinTable.joinType() == JoinTypeEnum.RIGHT_JOIN) {
					deletedExpressionSb.append("(").append(joinLeftTable.alias()).append(".").append(
						columnName + "=" + softDeleteColumn.softDelete()[0])
					   .append(" or ").append(joinLeftTable.alias()).append(".")
					   .append(columnName).append(" is null)");
				} else {
					deletedExpressionSb.append(joinLeftTable.alias()).append(".").append(
							columnName + "=" + softDeleteColumn.softDelete()[0]);
				}
			}
			
			if(softDeleteT2 != null) {
				if(softDeleteT1 != null) {
					deletedExpressionSb.append(" AND ");
				}
				Column softDeleteColumn = softDeleteT2.getAnnotation(Column.class);
				String columnName = getColumnName(softDeleteColumn);
				if(joinTable.joinType() == JoinTypeEnum.LEFT_JOIN) {
					deletedExpressionSb.append("(").append(joinRightTable.alias()).append(".").append(
							columnName + "=" + softDeleteColumn.softDelete()[0])
					    .append(" or ").append(joinRightTable.alias()).append(".")
					    .append(columnName).append(" is null)");
				} else {
					deletedExpressionSb.append(joinRightTable.alias()).append(".").append(
							columnName + "=" + softDeleteColumn.softDelete()[0]);
				}
			}
			
			deletedExpression = deletedExpressionSb.toString();		
		} else {
			Field softDelete = DOInfoReader.getSoftDeleteColumn(clazz);
			if(softDelete == null) {
				try {
					return " " + insertWhereAndExpression(whereSql, extraWhere);
				} catch (JSQLParserException e) {
					LOGGER.error("Bad sql syntax,whereSql:{},deletedExpression:{}",
							whereSql, deletedExpression, e);
					throw new BadSQLSyntaxException(e);
				}
			}
			
			Column softDeleteColumn = softDelete.getAnnotation(Column.class);
			deletedExpression = getColumnName(softDeleteColumn) + "=" 
			                        + softDeleteColumn.softDelete()[0];
		}
		
		try {
			if(!extraWhere.isEmpty()) {
				deletedExpression = "(" + deletedExpression + " and " + extraWhere + ")";
			}
			return " " + SQLUtils.insertWhereAndExpression(whereSql, deletedExpression);
		} catch (JSQLParserException e) {
			LOGGER.error("Bad sql syntax,whereSql:{},deletedExpression:{}",
					whereSql, deletedExpression, e);
			throw new BadSQLSyntaxException(e);
		}
	}
1216096
@Nullable
    public <T> T get(ResultSet rs, @Nullable Path<?> path, int i, Class<T> clazz) throws SQLException {
        return getType(path, clazz).getValue(rs, i);
    }
497909
public function query($sql, $params = null): Database
    {
        $sth          = null;
        $this->values = [];
        $this->lastid = null;
        if ($sql === '') {
            throw new CoreException(
                __CLASS__
                . '::query(): Query was undefined, please make sure you pass one.'
            );
        }

        if ($params === null) {
            try {
                $sth = $this->connection->query($sql);
            } catch (PDOException $e) {
                throw new CoreException(
                    __CLASS__.'::query()->PDO::query() : ' . $e->getMessage()
                );
            }
        } else {
            try {
                $sth = $this->connection->prepare($sql);
            } catch (PDOException $e) {
                throw new CoreException(
                    __CLASS__.'::query()->PDO::prepare() : ' . $e->getMessage()
                );
            }
            try {
                $sth->execute($params);
            } catch (PDOException $e) {
                throw new CoreException(
                    __CLASS__.'::query()->PDO::execute() : ' . $e->getMessage()
                );
            }
        }

        if ($sth instanceof PDOStatement && 0 !== strpos($sql, 'select')) {
            try {
                $this->values = $sth->fetchAll($this->configs['fetch_mode']);
            } catch (PDOException $e) {
                throw new CoreException(
                    __CLASS__.'::query()->PDOStatement::fetchAll() : '
                    . $e->getMessage()
                );
            }
        }

        if (false !== strpos($sql, 'insert')) {
            try {
                $this->lastid = $this->connection->lastInsertId();
            } catch (PDOException $e) {
                throw new CoreException(
                    __CLASS__.'::query()->PDO::lastInsertId() : ' . $e->getMessage()
                );
            }
        }

        return $this;
    }
5336
public static String getKeysWhereSQL(Class<?> clazz) 
			throws NoKeyColumnAnnotationException {
		List<Field> keyFields = DOInfoReader.getKeyColumns(clazz);
		String where = joinWhere(keyFields, "AND");
		return autoSetSoftDeleted("WHERE " + where, clazz);
	}
315499
private static SqlInfo buildFinalSql(SqlInfo sqlInfo, Object paramObj) {
        // 得到生成的SQL，如果有MVEL的模板表达式，则执行计算出该表达式来生成最终的SQL
        String sql = sqlInfo.getJoin().toString();
        sql = ParseHelper.parseTemplate(sql, paramObj);
        return sqlInfo.setSql(StringHelper.replaceBlank(sql));
    }
212659
public ResultSetAndStatement executeQuery(Query query, ClassDescriptor cld) throws PersistenceBrokerException
    {
        if (logger.isDebugEnabled())
        {
            logger.debug("executeQuery: " + query);
        }
        /*
		 * MBAIRD: we should create a scrollable resultset if the start at
		 * index or end at index is set
		 */
        boolean scrollable = ((query.getStartAtIndex() > Query.NO_START_AT_INDEX) || (query.getEndAtIndex() > Query.NO_END_AT_INDEX));
        /*
		 * OR if the prefetching of relationships is being used.
		 */
        if (query != null && query.getPrefetchedRelationships() != null && !query.getPrefetchedRelationships().isEmpty())
        {
            scrollable = true;
        }
        final StatementManagerIF sm = broker.serviceStatementManager();
        final SelectStatement sql = broker.serviceSqlGenerator().getPreparedSelectStatement(query, cld);
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            final int queryFetchSize = query.getFetchSize();
            final boolean isStoredProcedure = isStoredProcedure(sql.getStatement());
            stmt = sm.getPreparedStatement(cld, sql.getStatement() ,
                    scrollable, queryFetchSize, isStoredProcedure);
            if (isStoredProcedure)
            {
                // Query implemented as a stored procedure, which must return a result set.
                // Query sytax is: { ?= call PROCEDURE_NAME(?,...,?)}
                getPlatform().registerOutResultSet((CallableStatement) stmt, 1);
                sm.bindStatement(stmt, query, cld, 2);

                if (logger.isDebugEnabled())
                    logger.debug("executeQuery: " + stmt);

                stmt.execute();
                rs = (ResultSet) ((CallableStatement) stmt).getObject(1);
            }
            else
            {
                sm.bindStatement(stmt, query, cld, 1);

                if (logger.isDebugEnabled())
                    logger.debug("executeQuery: " + stmt);

                rs = stmt.executeQuery();
            }

            return new ResultSetAndStatement(sm, stmt, rs, sql);
        }
        catch (PersistenceBrokerException e)
        {
            // release resources on exception
            sm.closeResources(stmt, rs);
            logger.error("PersistenceBrokerException during the execution of the query: " + e.getMessage(), e);
            throw e;
        }
        catch (SQLException e)
        {
            // release resources on exception
            sm.closeResources(stmt, rs);
            throw ExceptionHelper.generateException(e, sql.getStatement(), null, logger, null);
        }
    }
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");
        }
    }
399867
@Override
    public Object[] findIdsByColumn(String schemaName, String tableName, String pKeyName, String columnName,
            Object columnValue, Class entityClazz)
    {

        TermQueryBuilder filter = QueryBuilders.termQuery(columnName, columnValue);

        SearchResponse response = txClient.prepareSearch(schemaName.toLowerCase()).setTypes(tableName)
                .addField(pKeyName).setPostFilter(filter).execute().actionGet();

        SearchHits hits = response.getHits();

        Long length = hits.getTotalHits();
        int absoluteLength = length.intValue();
        Object[] ids = new Object[absoluteLength];

        int counter = 0;
        for (SearchHit hit : hits.getHits())
        {
            Map<String, SearchHitField> fields = hit.getFields();
            ids[counter++] = fields.get(pKeyName).getValue();
        }

        return ids;
    }
790105
@Local
    @Override
    public <V> V directQuery(String sql, Function<ResultSet, V> handler) {
        final Connection conn = readPool.poll();
        try {
            if (logger.isLoggable(Level.FINEST)) logger.finest("direct query sql=" + sql);
            conn.setReadOnly(true);
            final Statement statement = conn.createStatement();
            //final PreparedStatement statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            final ResultSet set = statement.executeQuery(sql);// ps.executeQuery();
            V rs = handler.apply(set);
            set.close();
            statement.close();
            return rs;
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            if (conn != null) readPool.offerConnection(conn);
        }
    }
161472
public static <E extends SqlTypeAdapter<?, ?>> E getAdapter(Class<E> clazz) {
		@SuppressWarnings("unchecked")
		E adapter = (E) cache.get(clazz);

		if (adapter == null) {
			try {
				lock.lock();
				adapter = clazz.newInstance();
				cache.put(clazz, adapter);
			} catch(Throwable e) {
				throw(new KriptonRuntimeException(e));
			} finally {
				lock.unlock();
			}
		}

		return adapter;
	}
571642
public List<Object> querySingleRowResults(String sql, String[] args) {
		return querySingleRowResults(sql, args, null);
	}
322769
@When("^I query the database with '(.+?)'$")
    public void selectData(String query) throws Exception {
        Statement myStatement = null;
        //postgres table
        List<String> sqlTable = new ArrayList<String>();
        List<String> sqlTableAux = new ArrayList<String>();
        Connection myConnection = this.commonspec.getConnection();
        java.sql.ResultSet rs = null;
        try {
            myStatement = myConnection.createStatement();
            rs = myStatement.executeQuery(query);
            //column names
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int count = resultSetMetaData.getColumnCount();
            for (int i = 1; i <= count; i++) {
                sqlTable.add(resultSetMetaData.getColumnName(i).toString());
            }
            //takes column names and column count
            while (rs.next()) {
                for (int i = 1; i <= count; i++) {
                    //aux list without column names
                    sqlTableAux.add(rs.getObject(i).toString());
                }
            }
            sqlTable.addAll(sqlTableAux);

            //sends raws to environment variable
            for (int i = 0; i < sqlTable.size(); i++) {
                ThreadProperty.set("queryresponse" + i, sqlTable.get(i));
            }
            ThreadProperty.remove("queryresponse" + sqlTable.size());
            rs.close();
            myStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
            Assertions.assertThat(rs).as("There are no response from SELECT").isNotNull();
        }
    }
697855
private StringBuilder appendPagingSortSQL(final StringBuilder sql, final Post.Sort sort,final Paging paging) {

      if(paging.interval != null) {
         sql.append(" AND post_date");
         sql.append(paging.startIsOpen ? " >" : " >=");
         sql.append("?");

         sql.append(" AND post_date");
         sql.append(paging.endIsOpen ? " <" : " <=");
         sql.append("?");
      }

      switch(sort) {
         case ASC:
            sql.append(" ORDER BY post_date ASC");
            break;
         case DESC:
            sql.append(" ORDER BY post_date DESC");
            break;
         case ASC_MOD:
            sql.append(" ORDER BY post_modified ASC");
            break;
         case DESC_MOD:
            sql.append(" ORDER BY post_modified DESC");
            break;
         case ID_ASC:
            sql.append(" ORDER BY ID ASC");
            break;
         case ID_DESC:
            sql.append(" ORDER BY ID DESC");
            break;
         default:
            sql.append(" ORDER BY post_date DESC");
            break;
      }
      sql.append(" LIMIT ?,?");
      return sql;
   }
155894
public List<List<Object>> queryResults(String sql, String[] args) {
		return db.queryResults(sql, args);
	}
353502
public static <T> List<T> queryForList(String sql, Class<T> clz, Object... args) {
		List<T> result = new ArrayList<T>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getconnnection();
			ps = con.prepareStatement(sql);
			if (args != null) {
				for (int i = 0; i < args.length; i++) {
					ps.setObject((i + 1), args[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				T obj = clz.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					String columnName = rsmd.getColumnName(i);
					String methodName = "set" + columnName.substring(0, 1).toUpperCase()
							+ columnName.substring(1, columnName.length());
					Method method[] = clz.getMethods();
					for (Method meth : method) {
						if (methodName.equals(meth.getName())) {
							meth.invoke(obj, rs.getObject(i));
						}
					}
				}
				result.add(obj);
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
		return result;
	}