Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 ColType.PSQL_JSON oracle报错
发布于 2752天前 作者 qq_895c9d88 1567 次浏览 复制 上一个帖子 下一个帖子
标签: 无 
@Table("system_user")
@TableIndexes({ @Index(name = "user_name", fields = { "username" }, unique = true) })
@View("v_system_user")
public class User{
   	@Column
	@ColDefine(type = ColType.PSQL_JSON)
	private List<Integer> organizations;

	@Column
	@ColDefine(type = ColType.PSQL_JSON)
	private List<String> factorys;
}

问题1. 新增字段organizations和factorys，在MvcSetup初始化方法时Daos.migration(dao, "包名", true, true);出现以下错误“

2016-10-18 15:44:26,749 [INFO][org.nutz.dao.util.Daos] - add column[organizations] to table[system_user]
2016-10-18 15:44:33,047 [INFO][org.nutz.dao.util.Daos] - add column[factorys] to table[system_user]
2016-10-18 15:44:35,060 [DEBUG][org.nutz.plugins.cache.dao.CachedNutDaoExecutor] - parser SQL sql, skip cache detect!! SQL=ALTER TABLE  ADD organizations JSON;
2016-10-18 15:44:35,060 [DEBUG][org.nutz.dao.impl.sql.run.NutDaoExecutor] - ALTER TABLE  ADD organizations JSON;
2016-10-18 15:44:35,069 [DEBUG][org.nutz.dao.impl.sql.run.NutDaoExecutor] - SQLException
java.sql.SQLSyntaxErrorException: ORA-00903: 表名无效

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2489)
	at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2451)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
	at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:137)
	at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:421)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runStatement(NutDaoExecutor.java:317)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:50)
	at org.nutz.plugins.cache.dao.CachedNutDaoExecutor.exec(CachedNutDaoExecutor.java:89)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:264)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:219)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:197)
	at org.nutz.dao.impl.NutDao.execute(NutDao.java:967)
	at org.nutz.dao.util.Daos.migration(Daos.java:703)
	at org.nutz.dao.util.Daos.migration(Daos.java:744)
	at schedule.go.MvcSetup.init(MvcSetup.java:49)
	at org.nutz.mvc.impl.NutLoading.evalSetup(NutLoading.java:270)
	at org.nutz.mvc.impl.NutLoading.load(NutLoading.java:119)
	at org.nutz.mvc.ActionHandler.<init>(ActionHandler.java:19)
	at org.nutz.mvc.NutFilter.init(NutFilter.java:77)
	at org.apache.catalina.core.ApplicationFilterConfig.initFilter(ApplicationFilterConfig.java:279)
	at org.apache.catalina.core.ApplicationFilterConfig.getFilter(ApplicationFilterConfig.java:260)
	at org.apache.catalina.core.ApplicationFilterConfig.<init>(ApplicationFilterConfig.java:105)
	at org.apache.catalina.core.StandardContext.filterStart(StandardContext.java:4841)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5535)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1574)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1564)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
2016-10-18 15:44:35,072 [ERROR][org.nutz.mvc.impl.NutLoading] - Error happend during start serivce!
org.nutz.dao.DaoException: !Nutz SQL Error: 'ALTER TABLE  ADD organizations JSON;'
PreparedStatement: 
'ALTER TABLE  ADD organizations JSON;'
CaseMessage=ORA-00903: 表名无效

跟踪源代码：
Daos类，public static void migration(Dao dao, final Class<?> klass, final boolean add, final boolean del, Object tableName)方法中
ResultSetMetaData meta = null;
try {
// 获取数据库元信息
stat = conn.createStatement();
rs = stat.executeQuery("select * from " + en.getTableName() + " where 1 != 1");
meta = rs.getMetaData();

          Set<String> columnNames = new HashSet<String>();
          int columnCount = meta.getColumnCount();
          for (int i = 1; i <= columnCount; i++) {
              columnNames.add(meta.getColumnName(i).toLowerCase());
          }
          for (MappingField mf : en.getMappingFields()) {
              if (mf.isReadonly())
                 continue;
              String colName = mf.getColumnName();
              if (columnNames.contains(colName.toLowerCase())) {
                 columnNames.remove(colName.toLowerCase());
                 continue;
              }
              if (add) {
                 log.infof("add column[%s] to table[%s]", mf.getColumnName(), en.getTableName());
                 StringBuilder sb = new StringBuilder("ALTER TABLE ");
                 sb.append(meta.getTableName(1)).append(" ADD ");
                 if (sqlAddNeedColumn)
                   sb.append("COLUMN ");
                 sb.append(colName).append(" ").append(expert.evalFieldType(mf));
                 if (mf.isUnsigned()) {
                   sb.append(" UNSIGNED");
                 }
                 if (mf.isNotNull()) {
                   sb.append(" NOT NULL");
                 }
                 if (mf.getColumnType() == ColType.TIMESTAMP) {
                   if (mf.hasDefaultValue()) {
                    sb.append(" ").append(mf.getDefaultValue(null).replaceAll("@", "@@"));
                   } else {
                    if (mf.isNotNull()) {
                        sb.append(" DEFAULT 0");
                    } else {
                        sb.append(" NULL DEFAULT NULL");
                    }
                   }
                 } else {
                   if (mf.hasDefaultValue())
                    sb.append(" DEFAULT '").append(mf.getDefaultValue(null).replaceAll("@", "@@")).append("'");
                 }
                 if (mf.hasColumnComment() && isCanComment) {
                   sb.append(" COMMENT '").append(mf.getColumnComment()).append("'");
                 }
                 sb.append(';');
                 Sql sql = Sqls.create(sb.toString());
                 sqls.add(sql);
              }
          }
          if (del) {
              for (String colName : columnNames) {
                 log.infof("del column[%s] from table[%s]", colName, en.getTableName());
                 Sql sql = Sqls.create("ALTER table $table DROP column $name");
                 sql.vars().set("table", en.getTableName());
                 sql.vars().set("name", colName);
                 sqls.add(sql);
              }
          }
         } catch (SQLException e) {
          if (log.isDebugEnabled())
              log.debugf("migration Table '%s' fail!", en.getTableName(), e);
         }

其中meta.getTableName(1)获取表名为空，而 en.getTableName()能获取代码中的表名

问题2. 我要添加ColType.PSQL_JSON字段，对应Oracle的字段实际上类型竟然是JSON类型，请问有这个类型吗？
类OracleJdbcExpert、AbstractJdbcExpert，方法evalFieldType，其中解析PSQL_JSON类型是JSON

case PSQL_JSON:
            return "JSON";

问题3. 我尝试升级到1.r.58版本，结果发现还没有跑到数据表处理这块，就在合并sql方法中给跪了
日记：

2016-10-18 15:59:53,189 [DEBUG][org.nutz.dao.impl.FileSqlManager] - load 4 sql >> erp.sql from root=sql/erp/erp.sql
2016-10-18 15:59:53,193 [DEBUG][org.nutz.dao.impl.FileSqlManager] - load >> isp.sql from root=sql/isp/isp.sql
2016-10-18 15:59:53,193 [DEBUG][org.nutz.dao.impl.FileSqlManager] - load 0 sql >> isp.sql from root=sql/isp/isp.sql
2016-10-18 15:59:53,193 [DEBUG][org.nutz.dao.impl.DaoSupport] - SqlManager Sql count=4
2016-10-18 15:59:53,282 [ERROR][com.alibaba.druid.filter.stat.StatFilter] - merge sql error, dbType oracle, sql : 
show index from factory_head
com.alibaba.druid.sql.parser.ParserException: TODO : INDEX index
	at com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser.parseStatementList(OracleStatementParser.java:495)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:121)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:116)
	at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:53)
	at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145)
	at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:629)
	at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:305)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:318)
	at org.nutz.dao.util.Daos$4.invoke(Daos.java:841)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:147)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:114)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:81)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:70)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:266)
	at org.nutz.dao.util.Daos.migration(Daos.java:766)
	at org.nutz.dao.util.Daos.migration(Daos.java:1042)
	at org.nutz.dao.util.Daos.migration(Daos.java:1060)
	at schedule.go.MvcSetup.init(MvcSetup.java:48)
	at org.nutz.mvc.impl.NutLoading.evalSetup(NutLoading.java:277)
	at org.nutz.mvc.impl.NutLoading.load(NutLoading.java:121)
	at org.nutz.mvc.ActionHandler.<init>(ActionHandler.java:19)
	at org.nutz.mvc.NutFilter._init(NutFilter.java:87)
	at org.nutz.mvc.NutFilter.init(NutFilter.java:65)
	at org.apache.catalina.core.ApplicationFilterConfig.initFilter(ApplicationFilterConfig.java:279)
	at org.apache.catalina.core.ApplicationFilterConfig.getFilter(ApplicationFilterConfig.java:260)
	at org.apache.catalina.core.ApplicationFilterConfig.<init>(ApplicationFilterConfig.java:105)
	at org.apache.catalina.core.StandardContext.filterStart(StandardContext.java:4841)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5535)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1574)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1564)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
2016-10-18 15:59:53,291 [DEBUG][org.nutz.dao.util.Daos] - migration Table 'factory_head' fail!
java.sql.SQLSyntaxErrorException: ORA-00900: 无效 SQL 语句

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2714)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:132)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
	at org.nutz.dao.util.Daos$4.invoke(Daos.java:842)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:147)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:114)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:81)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:70)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:266)
	at org.nutz.dao.util.Daos.migration(Daos.java:766)
	at org.nutz.dao.util.Daos.migration(Daos.java:1042)
	at org.nutz.dao.util.Daos.migration(Daos.java:1060)
	at schedule.go.MvcSetup.init(MvcSetup.java:48)
	at org.nutz.mvc.impl.NutLoading.evalSetup(NutLoading.java:277)
	at org.nutz.mvc.impl.NutLoading.load(NutLoading.java:121)
	at org.nutz.mvc.ActionHandler.<init>(ActionHandler.java:19)
	at org.nutz.mvc.NutFilter._init(NutFilter.java:87)
	at org.nutz.mvc.NutFilter.init(NutFilter.java:65)
	at org.apache.catalina.core.ApplicationFilterConfig.initFilter(ApplicationFilterConfig.java:279)
	at org.apache.catalina.core.ApplicationFilterConfig.getFilter(ApplicationFilterConfig.java:260)
	at org.apache.catalina.core.ApplicationFilterConfig.<init>(ApplicationFilterConfig.java:105)
	at org.apache.catalina.core.StandardContext.filterStart(StandardContext.java:4841)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5535)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1574)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1564)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)

原因是Daos类migration方法：

// show index from mytable;
                    String showIndexs = "show index from " + en.getTableName();
                    PreparedStatement ppstat = conn.prepareStatement(showIndexs);
                    ResultSet rest = ppstat.executeQuery();
                    while (rest.next()) {
                        String index = rest.getString(3);
                        _indexs.add(index);
                    }

请问Dao是否支付oracle数据库。

跪求解决！

7 回复

额, 你想在Oracle中操作类似JSON类型的属性? 还是为了兼容一下数据库而已??

问题1和问题2出现的版本是1.b.53
问题3出现的版本是1.r.58

@wendal
java代码中字段属性是list，想直接转换成json保存到数据库

如果只是为了兼容, 修改org.nutz.dao.impl.jdbc.oracle.OracleJdbcExpert.evalFieldType(MappingField)的实现应该能解决

@wendal
恩，如果要额外编写nutz，我想一下其它类型方案

嗯, ColDefine设置一下适配器咯,然后不需要指定type用VARCHAR就好了

ColType.PSQL_JSON 本来就是只给 postgrsql 用的

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