建议统一代码中对数据库表名引用的大小写，建议都采用小写

代码中对数据库表名引用时的大小写混合使用，会对Debian平台下MySQL用户带来不便，Debian下MySQL的数据库表名严格区分大小写（如果通过设置my.cnf强制不区分——此时就是事实上的小写)，而代码中混着用大小写会导致找不到数据表错误：

=========================================================================
试举两个找不到数据表错误的实例：
Hibernate: select distinct NAME,ID,PARENTID,NUM,READPOP,WRITEPOP FROM (SELECT a.NAME AS NAME,a.readpop AS READPOP,a.WRITEPOP AS WRITEPOP, a.ID AS ID, a.PARENTID AS PARENTID, (SELECT COUNT(B1.ID) FROM FARM_DOC B1 LEFT JOIN FARM_RF_DOCTYPE B2 ON B1.ID = B2.DOCID LEFT JOIN FARM_DOCTYPE B3 ON B3.ID = B2.TYPEID WHERE B1.STATE='1' and B3.TREECODE LIKE CONCAT(A.TREECODE,'%') AND B1.STATE='1') AS NUM FROM farm_doctype AS a LEFT JOIN farm_doctype AS b ON b.ID = a.PARENTID WHERE 1 = 1 AND (a.TYPE = '1' OR a.TYPE = '3') AND a.PSTATE = '1' and (a.PARENTID='NONE' or b.PARENTID='NONE') ORDER BY a.SORT ASC) AS e WHERE 1=1 and READPOP='0' limit ?
ERROR: Table 'wcp2.FARM_DOC' doesn't exist (org.hibernate.engine.jdbc.spi.SqlExceptionHelper#logExceptions ) 2016年11月23日12点58分
ERROR: java.sql.SQLException: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet(com.farm.doc.server.impl.FarmDocTypeManagerImpl#getTypeInfos ) 2016年11月23日12点58分

Hibernate: select TOP.ID AS ID, TOP.SORT AS SORT, A.DOCDESCRIBE AS TEXT,A.WRITEPOP AS WRITEPOP,A.READPOP AS READPOP,A.TITLE AS TITLE,A.AUTHOR AS AUTHOR,A.PUBTIME AS PUBTIME,A.DOMTYPE AS DOMTYPE,A.SHORTTITLE AS SHORTTITLE,A.TAGKEY AS TAGKEY,A.STATE AS STATE,D.GROUPNAME AS GROUPNAME, DOCRUNINFO.VISITNUM AS VISITNUM, DOCRUNINFO.ANSWERINGNUM AS ANSWERINGNUM, A.IMGID AS IMGID, A.ID AS DOCID,A.CUSER AS AUTHORID FROM FARM_TOP TOP LEFT JOIN FARM_DOC A ON TOP.DOCID = A.ID and a.STATE='1' LEFT JOIN FARM_RF_DOCTYPE B ON B.DOCID =A.ID LEFT JOIN FARM_DOCTYPE C ON C.ID=B.TYPEID LEFT JOIN FARM_DOCGROUP D ON D.ID=A.DOCGROUPID LEFT JOIN FARM_DOCRUNINFO DOCRUNINFO ON A.RUNINFOID = DOCRUNINFO.ID WHERE 1=1 ORDER BY TOP.SORT asc limit ?
ERROR: Table 'wcp2.FARM_TOP' doesn't exist (org.hibernate.engine.jdbc.spi.SqlExceptionHelper#logExceptions ) 2016年11月23日12点58分
ERROR: java.sql.SQLException: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet(com.farm.doc.server.impl.FarmDocRunInfoImpl#getPubTopDoc ) 2016年11月23日12点58分

=========================================================================
对数据库表名引用时的大小写混合使用举例：
int com.farm.doc.dao.impl.FarmDocDao.getAllListNum()

public int getAllListNum(){
Session session= sessionFatory.getCurrentSession();
SQLQuery sqlquery= session.createSQLQuery("select count(*) from farm_doc");
BigInteger num=(BigInteger)sqlquery.list().get(0);
return num.intValue() ;
}

int com.farm.doc.dao.impl.FarmDocgroupDao.getGroupDocNum(String groupId)

public int getGroupDocNum(String groupId) {
Session session = sessionFatory.getCurrentSession();
SQLQuery sqlquery = session.createSQLQuery("select count(*) from FARM_DOC where STATE='1' and DOCGROUPID=?");
sqlquery.setString(0, groupId);
BigInteger num = (BigInteger) sqlquery.list().get(0);
return num.intValue();
}

=========================================================================
建议：还请直接将所有对数据库表名的引用统一成小写，包括未开源的那几个工程里面，如果引用到数据库表名的话！

此外，还有部分引用（比如 as A 这种方式）时混淆了大小写的情况，如
List com.farm.doc.server.impl.FarmDocTypeManagerImpl.getPubTypes()
中混用了 a 和 A： “A.TREECODE” VS “FROM farm_doctype AS a”
DataQuery query = DataQuery.init(new DataQuery(),
"(SELECT a.NAME as NAME, a.ID as ID, a.PARENTID as PARENTID, (SELECT COUNT(B1.ID) FROM farm_doc B1 LEFT JOIN farm_rf_doctype B2 ON B1.ID = B2.DOCID LEFT JOIN farm_doctype B3 ON B3.ID = B2.TYPEID WHERE B1.STATE='1' and B3.TREECODE LIKE CONCAT(A.TREECODE,'%') AND B1.STATE='1') AS NUM FROM farm_doctype AS a WHERE 1 = 1 AND (TYPE = '1' OR TYPE = '3') AND PSTATE = '1' ORDER BY SORT ASC) AS e",
"NAME,ID,PARENTID,NUM");

下面是一些出现这种错误的情况供参考：请注意部分错误出现在 com.farm.core.sql.query.DataQuery.search，这应该在尚未开源的工程代码中。而且也有这种 "A.ID" VS "as a" 此类的错误。

ERROR: Unknown column 'A.TREECODE' in 'where clause'(org.hibernate.engine.jdbc.spi.SqlExceptionHelper#logExceptions ) 2016年11月23日13点39分
ERROR: java.sql.SQLException: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet(com.farm.doc.server.impl.FarmDocTypeManagerImpl#getPubTypes ) 2016年11月23日13点39分

ERROR: Unknown column 'A.TREECODE' in 'where clause'(org.hibernate.engine.jdbc.spi.SqlExceptionHelper#logExceptions ) 2016年11月23日13点39分
ERROR: java.sql.SQLException: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet(com.farm.doc.server.impl.FarmDocTypeManagerImpl#getTypeInfos ) 2016年11月23日13点39分

ERROR: Unknown column 'a.STATE' in 'on clause'(org.hibernate.engine.jdbc.spi.SqlExceptionHelper#logExceptions ) 2016年11月23日13点39分
ERROR: java.sql.SQLException: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet(com.farm.doc.server.impl.FarmDocRunInfoImpl#getPubTopDoc ) 2016年11月23日13点39分

Hibernate: select A.ID AS DOCID,A.DOCDESCRIBE AS TEXT,A.AUTHOR AS AUTHOR, A.TITLE AS TITLE, DOMTYPE, B.HOTNUM AS HOTNUM,B.VISITNUM AS VISITNUM,B.ANSWERINGNUM AS ANSWERINGNUM FROM farm_doc a LEFT JOIN farm_docruninfo b ON a.RUNINFOID = b.ID LEFT JOIN farm_rf_doctype c ON c.DOCID=a.ID LEFT JOIN farm_doctype d ON d.ID=c.TYPEID WHERE 1=1 AND A.READPOP = '1' AND D.READPOP = '0' ORDER BY b.HOTNUM desc limit ?
ERROR: Unknown column 'A.ID' in 'field list'(org.hibernate.engine.jdbc.spi.SqlExceptionHelper#logExceptions ) 2016年11月23日13点39分
java.sql.SQLException: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at com.farm.core.sql.query.DataQuery.search(DataQuery.java:158)
at com.farm.core.sql.query.DataQuery.search(DataQuery.java:180)
at com.farm.doc.server.impl.FarmDocRunInfoImpl.getPubHotDoc(FarmDocRunInfoImpl.java:336)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy51.getPubHotDoc(Unknown Source)
at com.farm.wcp.controller.DocLuceneController.show(DocLuceneController.java:86)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:177)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:446)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:434)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.farm.web.filter.FilterValidate.doFilter(FilterValidate.java:94)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.farm.web.filter.FilterLogInfo.doFilter(FilterLogInfo.java:44)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.farm.web.filter.FilterEncoding.doFilter(FilterEncoding.java:33)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at com.farm.core.sql.query.Searcher.doSearch(Searcher.java:54)
at com.farm.core.sql.query.DataQuery.search(DataQuery.java:153)
... 59 more
Caused by: java.sql.SQLException: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at com.farm.core.sql.query.HibernateQueryHandle.runDataQuery(HibernateQueryHandle.java:110)
at com.farm.core.sql.query.Searcher.doSearch(Searcher.java:52)
... 60 more
Caused by: java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at com.farm.core.sql.query.HibernateQueryHandle.runLimitQuery(HibernateQueryHandle.java:161)
at com.farm.core.sql.query.HibernateQueryHandle.runDataQuery(HibernateQueryHandle.java:86)
... 61 more
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
at org.hibernate.loader.Loader.doQuery(Loader.java:910)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
at org.hibernate.loader.Loader.doList(Loader.java:2540)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
at org.hibernate.loader.Loader.list(Loader.java:2365)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
at com.farm.core.sql.query.HibernateQueryHandle.runLimitQuery(HibernateQueryHandle.java:159)
... 62 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'A.ID' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2313)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
... 76 more
Hibernate: select aloneparam0_.ID as ID1_11_, aloneparam0_.COMMENTS as COMMENTS2_11_, aloneparam0_.CTIME as CTIME3_11_, aloneparam0_.CUSER as CUSER4_11_, aloneparam0_.DOMAIN as DOMAIN5_11_, aloneparam0_.MUSER as MUSER6_11_, aloneparam0_.NAME as NAME7_11_, aloneparam0_.PKEY as PKEY8_11_, aloneparam0_.PVALUE as PVALUE9_11_, aloneparam0_.RULES as RULES10_11_, aloneparam0_.STATE as STATE11_11_, aloneparam0_.USERABLE as USERABL12_11_, aloneparam0_.UTIME as UTIME13_11_, aloneparam0_.VTYPE as VTYPE14_11_ from alone_parameter aloneparam0_ where PKEY=?
DEBUG: web-simple/lucene/search(com.farm.core.page.ViewMode#returnModelAndView ) 2016年11月23日13点39分
Hibernate: select aloneparam0_.ID as ID1_11_, aloneparam0_.COMMENTS as COMMENTS2_11_, aloneparam0_.CTIME as CTIME3_11_, aloneparam0_.CUSER as CUSER4_11_, aloneparam0_.DOMAIN as DOMAIN5_11_, aloneparam0_.MUSER as MUSER6_11_, aloneparam0_.NAME as NAME7_11_, aloneparam0_.PKEY as PKEY8_11_, aloneparam0_.PVALUE as PVALUE9_11_, aloneparam0_.RULES as RULES10_11_, aloneparam0_.STATE as STATE11_11_, aloneparam0_.USERABLE as USERABL12_11_, aloneparam0_.UTIME as UTIME13_11_, aloneparam0_.VTYPE as VTYPE14_11_ from alone_parameter aloneparam0_ where PKEY=?
Hibernate: select aloneparam0_.ID as ID1_11_, aloneparam0_.COMMENTS as COMMENTS2_11_, aloneparam0_.CTIME as CTIME3_11_, aloneparam0_.CUSER as CUSER4_11_, aloneparam0_.DOMAIN as DOMAIN5_11_, aloneparam0_.MUSER as MUSER6_11_, aloneparam0_.NAME as NAME7_11_, aloneparam0_.PKEY as PKEY8_11_, aloneparam0_.PVALUE as PVALUE9_11_, aloneparam0_.RULES as RULES10_11_, aloneparam0_.STATE as STATE11_11_, aloneparam0_.USERABLE as USERABL12_11_, aloneparam0_.UTIME as UTIME13_11_, aloneparam0_.VTYPE as VTYPE14_11_ from alone_parameter aloneparam0_ where PKEY=?
Hibernate: select aloneparam0_.ID as ID1_11_, aloneparam0_.COMMENTS as COMMENTS2_11_, aloneparam0_.CTIME as CTIME3_11_, aloneparam0_.CUSER as CUSER4_11_, aloneparam0_.DOMAIN as DOMAIN5_11_, aloneparam0_.MUSER as MUSER6_11_, aloneparam0_.NAME as NAME7_11_, aloneparam0_.PKEY as PKEY8_11_, aloneparam0_.PVALUE as PVALUE9_11_, aloneparam0_.RULES as RULES10_11_, aloneparam0_.STATE as STATE11_11_, aloneparam0_.USERABLE as USERABL12_11_, aloneparam0_.UTIME as UTIME13_11_, aloneparam0_.VTYPE as VTYPE14_11_ from alone_parameter aloneparam0_ where PKEY=?
Hibernate: select ID,WEBNAME,URL,CTIME,ETIME,CUSERNAME,CUSER,EUSERNAME,EUSER,PCONTENT,PSTATE,SORT,FILEID FROM farm_weburl WHERE 1=1 limit ?

随着对代码的进一步阅读，发现
com.farm.core.sql.result.DataResults
com.farm.core.sql.query.DBRule
等使用了.toUpperCase()，试图来规避在代码中内嵌的SQL语句大小写混乱的问题，但数据库的创建语句中表名又使用的是小写，这样在Debian 平台的MySQL下刚好反过来，找不着表了。如是这样，还不如在创建数据库时直接将表名也和列名一样，均为大写。

mysql在linux下可以设置为大小写不敏感

太原扁舟科技 / WCP知识档案管理系统

内容风险标识

评论 (4)

太原扁舟科技 / WCP知识档案管理系统 .gitee-modal { width: 500px !important; }

内容风险标识