Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 ValueAdaptor的问题
发布于 2035天前 作者 jirongjun 2012 次浏览 复制 上一个帖子 下一个帖子
标签: 无

entity中有个PGSQL的geometry类型，我写了个类来包装，也搞了个ValueAdaptor来转换SQL语句

    @Column
    @ColDefine(adaptor = PolygonValueAdaptor.class)
    private Polygon paths;

@Override
    public void set(PreparedStatement stat, Object obj, int index) throws SQLException {
        /**
         * SQL: ST_PolygonFromText('POLYGON((lng1 lat1, lng2 lat2, ...))')
         */
        if (Lang.isEmpty(obj))
            stat.setNull(index, Types.NULL);
        
        if (obj instanceof Polygon) {
        
            Polygon data = (Polygon) obj;
            //要求头尾一致，所以要补第一个在最后
            List<LngLat> paths = data.getPaths();
            paths.add(paths.get(0));
            String sql = String.format("ST_PolygonFromText('POLYGON((%s))')",
                String.join(",", paths.stream().map(item -> {
                    return String.format("%s %s", item.getLng(), item.getLat());
                }).collect(Collectors.toList()))
            );
            stat.setString(index, sql);
        } else {
            throw new SQLException("type not match");
        }
    }

但是最终生成的INSERT SQL，这个paths字段是按json输出的，这是为什么？
INSERT INTO ATT_LOCATION(CUSTOMER_ID,name,GPS_LAT,GPS_LNG,paths,IS_ENABLE) VALUES(NULL,'附院本部',32.00763582629068,120.86553105798339,'{"paths":[{"lat":32.008950904477175,"lng":120.86344957351685}, {"lat":32.008659778232705,"lng":120.86314916610718}, {"lat":32.00824128263634,"lng":120.86323499679565}, {"lat":32.00815030507996,"lng":120.86359977722168}, {"lat":32.00647630192637,"lng":120.86405038833618}, {"lat":32.00685841403447,"lng":120.86681842803955}, {"lat":32.00529356387788,"lng":120.86737632751465}, {"lat":32.005675680915985,"lng":120.86870670318604}, {"lat":32.008532410209995,"lng":120.86778402328491}, {"lat":32.00809571850279,"lng":120.86604595184326}, {"lat":32.00933300626979,"lng":120.86570262908936}, {"lat":32.00935120155353,"lng":120.86525201797485}, {"lat":32.01084320252756,"lng":120.86482286453247}, {"lat":32.010388326755354,"lng":120.86310625076294}]}',NULL)

11 回复

insert语句是通过PrepareStatment执行的,带问号的SQL, 然后设置参数矩阵

日志里面的打印的是"example",不是真正运行的SQL

2019-04-29 11:11:41,745 [DEBUG] org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) - INSERT INTO ATT_LOCATION(CUSTOMER_ID,name,GPS_LAT,GPS_LNG,paths,IS_ENABLE) VALUES(?,?,?,?,?,?)
| 1 | 2 | 3 | 4 | 5 | 6 |
|------|------|-------------------|--------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------|
| NULL | 附院本部 | 32.00763582629068 | 120.86553105798339 | {"paths":[{"lat":32.008950904477175,"lng":120.86344957351685}, {"lat":32.008659778232705,"lng":120.86314916610718}, {"lat":32.00824128263634,"lng":120.86323499679565}, {"lat":32.00815030507996,"lng":120.86359977722168}, {"lat":32.00647630192637,"lng":120.86405038833618}, {"lat":32.00685841403447,"lng":120.86681842803955}, {"lat":32.00529356387788,"lng":120.86737632751465}, {"lat":32.005675680915985,"lng":120.86870670318604}, {"lat":32.008532410209995,"lng":120.86778402328491}, {"lat":32.00809571850279,"lng":120.86604595184326}, {"lat":32.00933300626979,"lng":120.86570262908936}, {"lat":32.00935120155353,"lng":120.86525201797485}, {"lat":32.01084320252756,"lng":120.86482286453247}, {"lat":32.010388326755354,"lng":120.86310625076294}]} | NULL |
For example:> "INSERT INTO ATT_LOCATION(CUSTOMER_ID,name,GPS_LAT,GPS_LNG,paths,IS_ENABLE) VALUES(NULL,'附院本部',32.00763582629068,120.86553105798339,'{"paths":[{"lat":32.008950904477175,"lng":120.86344957351685}, {"lat":32.008659778232705,"lng":120.86314916610718}, {"lat":32.00824128263634,"lng":120.86323499679565}, {"lat":32.00815030507996,"lng":120.86359977722168}, {"lat":32.00647630192637,"lng":120.86405038833618}, {"lat":32.00685841403447,"lng":120.86681842803955}, {"lat":32.00529356387788,"lng":120.86737632751465}, {"lat":32.005675680915985,"lng":120.86870670318604}, {"lat":32.008532410209995,"lng":120.86778402328491}, {"lat":32.00809571850279,"lng":120.86604595184326}, {"lat":32.00933300626979,"lng":120.86570262908936}, {"lat":32.00935120155353,"lng":120.86525201797485}, {"lat":32.01084320252756,"lng":120.86482286453247}, {"lat":32.010388326755354,"lng":120.86310625076294}]}',NULL) "
2019-04-29 11:11:42,556 [DEBUG] org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:97) - SQLException
org.postgresql.util.PSQLException: ERROR: parse error - invalid geometry
建议："ST" <-- parse error at position 2 within geometry
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:157)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:620)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
at org.nutz.dao.impl.sql.run.NutDaoExecutor._runPreparedStatement(NutDaoExecutor.java:308)

这样的SQL在数据库客户端是可以执行的


insert into att_location(name, paths) values('aaa',ST_PolygonFromText('POLYGON((120.89282512664795 31.982292388751286, 120.89308261871338  31.980435903533536, 120.89651584625244 31.980654315508694, 120.8959150314331 31.982911208810265, 120.89282512664795 31.982292388751286))'));

是不是stat.set的时候有什么问题？对preparedStatement了解的不多，google了一下，也没找到什么有用的信息。
valueadaptor的set作用，不就是在占位上用我set过去的东西么，就像sql里的var一样。

是不是ValueAdaptor不能做到某个字段是类似Sql里的setVar的效果？

你把对象转成字符串了?? setObject进去就行了, 不要转字符串

是不是必须用ST_GeomFromText函数的?

是的，必须走个函数把数据拼成的字符串转成这个数据类型。
也就是说这是SQL文的一部分，所以用占位符可能并不合适。

如果dao里能提供一个生成SQL时对字段进行SQL文包装的功能就好了，结合EL，把view和field和entity的数据传进去。这个比valueadaptor要开放得多。

如果目前的ValueAdaptor解决不了我这个情况，我是不是可以先insert，避开这个字段，然后再做个Chain的Update，这样我可以自己拼SQL文了。
有那么一内内的麻烦。

嗯, 看来ValueAdaptor是搞不定

添加回复
请先登陆
回到顶部