我们在MS SQL中有一个存储过程,该存储过程返回的结果集与任何数据库表都不匹配。而且我们希望将结果映射到纯Java POJO,而不使用@Entity批注。但是如果我们打电话

entityManager.createStoredProcedureQuery("schema.myProcedure",
                        PlainPojo.class);

它会因org.hibernate.MappingException: Unknown entity异常而失败

我知道有@SqlResultSetMapping批注,但需要将其放在Entity类上吗?这不起作用
@Repository
@SqlResultSetMapping(name = "Mapping")
public class MyRepositoryImpl implements MyRepository

只有这个
@Enity
@SqlResultSetMapping(name = "Mapping")
public class MyEntity

但我们不希望为此使用实体类...因此将其放置在另一个实体上? (不完全是)
  • 是否可以将其放置在其他地方?
  • 还是可以在代码中完成?

    另一个解决方案?
  • 是否可以制作类似瞬态实体的东西? ->使用@Entity注释而不创建数据库表等。
  • 最佳答案

    我没有找到关于stackoverflow的复杂解决方案,并且重新发明了轮子,也许这对某些需要在项目中多次调用存储过程的人有所帮助。

    主要思想是编写类似于@StoredProcedure的自定义注释,但独立于@Entity

    @Target(TYPE)
    @Retention(RUNTIME)
    public @interface CustomNamedStoredProcedureQueries {
         /**
         * Array of CustomNamedStoredProcedureQuery annotations.
         */
         CustomNamedStoredProcedureQuery[] value();
    }
    @Target(TYPE)
    @Retention(RUNTIME)
    public @interface CustomNamedStoredProcedureQuery {
        /*
        * The name of the stored procedure for call.
        */
        String name();
        /*
        * The name of the stored procedure in the database.
        */
        String procedureName();
        /*
        * The scheme name of the database.
        */
        String schemeName();
        /*
        * The name of the package that contains stored procedure in the database.
        */
        String packageName();
        /*
        * Information about all parameters of the stored procedure.
        */
        CustomStoredProcedureParameter[] parameters() default {};
        /*
        * The names of one or more result set mappings, as defined in metadata, when you need custom mapping.
        */
        ProcedureRowMapper[] resultSetRowMappers() default {};
        /*
        * The names of one or more result set mappings, as defined in metadata, when you need 1 to 1 mapping.
        */
        ProcedureResultClass[] resultSetMappers() default {};
    }
    @Target({})
    @Retention(RUNTIME)
    public @interface CustomStoredProcedureParameter {
        /** Name of strored procedure parameter.*/
        String name() default "";
        /** JDBC type of the parameter.*/
        int type();
        /** Parameter mode.*/
        ParameterMode mode() default ParameterMode.IN;
    }
    @Target({})
    @Retention(RUNTIME)
    public @interface ProcedureResultClass {
        /* Name of procedure parameter*/
        String name();
        /* Class of object using for mapping */
        Class<?> resultClass();
    }
    @Target({})
    @Retention(RUNTIME)
    public @interface ProcedureRowMapper {
        /* Name of cursor field for use mapper*/
        String name();
        /* Class of object using for mapping, must extend BeanPropertyRowMapper */
        Class<? extends BeanPropertyRowMapper> mapper();
    }
    

    如前所述,当您需要将resultSet 1映射到1时,请使用ProcedureResultClass字段必须具有相同的名称。

    如果需要一些自定义映射,请使用ProcedureRowMapper,可以@Override mapRow()方法并手动进行映射,或者如果只需要以特定方式转换某些字段,则可以@Override initBeanWrapper()

    因此,接下来我们需要一种使其起作用的方法。将其放在应用程序的init方法中,例如main方法的@PostConstruct。
    ProjectUtils {
        private JdbcTemplate jdbcTemplate;
    
        @Autowired
        public ProjectUtils(DataSource dataSource) {
            jdbcTemplate = new JdbcTemplate(DataSource);
        }
    
        private static Map<String, SimpleJdbcCall> storedProcedureContainer = new HashMap<>();
    
    
        public void initStoredProcedure() {
            Reflections reflections = new Reflections("my.package");
            Set<Class<?>> classList = reflections.getTypesAnnotatedWith(CustomNamedStoredProcedureQueries.class);
    
            for (Class<?> clazz : classList) {
                CustomNamedStoredProcedureQueries queriesAnnotation = clazz.getAnnotation(CustomNamedStoredProcedureQueries.class);
    
                for (CustomNamedStoredProcedureQuery queryAnnotation : queriesAnnotation.value()) {
                    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                            .withSchemaName(queryAnnotation.schemeName())
                            .withCatalogName(queryAnnotation.packageName())
                            .withProcedureName(queryAnnotation.procedureName());
                    Set<SqlParameter> parameters = this.getDeclaredParameters(queryAnnotation.parameters());
                    jdbcCall.declareParameters(parameters.toArray(new SqlParameter[0]));
    
                    Map<String, RowMapper<?>> rowMappers = getDeclaredRowMappers(queryAnnotation.resultSetRowMappers(), queryAnnotation.resultSetMappers());
                     rowMappers.forEach((parameterName, rowMapper) -> jdbcCall.addDeclaredRowMapper(parameterName, rowMapper));
    
                    jdbcCall.compile();
    
                    storedProcedureContainer.put(queryAnnotation.name(), jdbcCall);
                }
            }
        }
    
        private Set<SqlParameter> getDeclaredParameters(CustomStoredProcedureParameter[] procedureParameters) {
            Set<SqlParameter> parameters = new HashSet<>();
            for (CustomStoredProcedureParameter procedureParameter : procedureParameters) {
                if (procedureParameter.mode().equals(ParameterMode.IN)) {
                    parameters.add(new SqlParameter(procedureParameter.name(), procedureParameter.type()));
                }
                if (procedureParameter.mode().equals(ParameterMode.OUT)) {
                    parameters.add(new SqlOutParameter(procedureParameter.name(), procedureParameter.type()));
                }
            }
            return parameters;
        }
    
        private Map<String, RowMapper<?>> getDeclaredRowMappers(ProcedureRowMapper[] resultSetMappers, ProcedureResultClass[] simpleResultSetMappers) {
            Map<String, RowMapper<?>> mappers = new HashMap<>();
            if (!ArrayUtils.isEmpty(simpleResultSetMappers)) {
                for (ProcedureResultClass procedureResultClass : simpleResultSetMappers) {
                    mappers.put(procedureResultClass.name(), BeanPropertyRowMapper.newInstance(procedureResultClass.resultClass()));
                }
                return mappers;
            }
            try {
                for (ProcedureRowMapper resultSetMapper : resultSetMappers) {
                    mappers.put(resultSetMapper.name(), resultSetMapper.mapper().newInstance());
                }
            } catch (InstantiationException | IllegalAccessException e) {
                e.printStackTrace();
            }
            return mappers;
        }
    
        public SimpleJdbcCall getProcedure(String name) {
            return storedProcedureContainer.get(name);
        }
    }
    
  • 假设必须声明@CustomNamedStoredProcedureQueries,即使存储过程只有一个,也可以根据需要自行更改。
  • 您可以使用ProcedureResultClass或ProcedureRowMapper,但不能同时使用,也可以将其更改为。

  • 示例(我使用oracledb,但稍作更改即可将其用于其他数据库)
    @CustomNamedStoredProcedureQueries({
            @CustomNamedStoredProcedureQuery(
                    name = "getFoo",
                    schemeName = "FooScheme",
                    packageName = "FooPackage",
                    procedureName = "get_Foo",
                    resultSetMappers = @ProcedureResultClass(name = "foo_cursor", resultClass = Foo.class),
                    parameters = {
                            @CustomStoredProcedureParameter(name = "foo_id", type = OracleTypes.NUMBER, mode = ParameterMode.IN),
                            @CustomStoredProcedureParameter(name = "error_code", type = OracleTypes.NUMBER, mode = ParameterMode.OUT),
                            @CustomStoredProcedureParameter(name = "foo_cursor", type = OracleTypes.CURSOR, mode = ParameterMode.OUT),
                    }
            ),
            @CustomNamedStoredProcedureQuery(
                    name = "deleteFoo",
                    schemeName = "FooScheme",
                    packageName = "FooPackage",
                    procedureName = "delete_Foo",
                    parameters = {
                            @CustomStoredProcedureParameter(name = "foo_id", type = OracleTypes.NUMBER, mode = ParameterMode.IN),
                            @CustomStoredProcedureParameter(name = "error_code", type = OracleTypes.NUMBER, mode = ParameterMode.OUT),
                    }
            )
    })
    public class Foo {
         long id;
         String name;
         int age;
    
         public void setId(long id) {
           this.id= id;
         }
         public long getId() {
           return this.id;
         }
    
         public void setName(String name) {
           this.name = name;
         }
         public String getName() {
           return this.name;
         }
    
         public void setAge(int age) {
           this.age = age;
         }
         public int getAge() {
           return this.age;
         }
    }
    

    命名:
    public Foo getFoo(Long fooId) {
            SqlParameterSource sqlParams = new MapSqlParameterSource()
                    .addValue("foo_id", fooId);
            SimpleJdbcCall procedure = projectUtils.getProcedure("getFoo");
            Map<String, Object> result = procedure.execute(sqlParams);
            return ((List<Foo>) result.get("foo_cursor")).get(0);
        }
    

    提示-可以与自定义类型(存储在bd中的类型)一起使用,例如Array,为此您需要创建自定义类型AbstractSqlTypeValue的扩展,并覆盖 createTypeValue(),然后将其像其他参数一样传递(经过仔细测试仅适用于oracle )。

    必须在方案级别和更高级别上声明CustomType,在程序包级别上声明它不起作用,必须以大写形式调用方案和类型。
        @Override
        protected Object createTypeValue(Connection connection, int sqlType, String typeName) throws SQLException {
            if (connection.isWrapperFor(OracleConnection.class)) {
                OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
                return oracleConnection.createARRAY("FOOSCHEME.NUMBERARRAY", this.array); // array pass when customType class init
            }
            return connection.createArrayOf("FOOSCHEME.NUMBERARRAY", this.array);
        }
    

    08-06 07:14
    查看更多