MySQL表名后接t_解决（springboot项目）mysql表名大写，造成jpaTabledoesn'texist问题...

一般实体类中的属性, 如contentType会被hibernate默认映射为content_type&＃xff0c;如果数据库中的字段是contenttype则会不匹配

这个问题有2种解决方法&＃xff1a;

我的报错是&＃xff1a;

java.sql.SQLSyntaxErrorException: Table &＃39;gaei_ms.gaei_work_task&＃39; doesn&＃39;t exist

方法一&＃xff1a;

转自&＃xff1a;https://confluence.atlassian.com/fishkb/table-xxx-doesn-t-exist-error-with-mysql-server-302810019.html

2012-09-05 10:12:30,728 ERROR [btpool0-472 ] fisheye.app TotalityFilter-logExceptionDetails - Exception "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table &＃39;fecru.AO_B434B9_WEB_HOOK&＃39; doesn&＃39;t exist" (net.java.ao.sql.ActiveObjectSqlException) while processing "/plugins/servlet/webhooks/list" (Referer:"https://fisheye.bln.native-instruments.de/admin/viewServerSettings.do")

net.java.ao.sql.ActiveObjectSqlException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table &＃39;fecru.AO_B434B9_WEB_HOOK&＃39; doesn&＃39;t exist

at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:114)

at com.atlassian.activeobjects.osgi.DelegatingActiveObjects.find(DelegatingActiveObjects.java:71)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

...

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table &＃39;fecru.AO_B434B9_WEB_HOOK&＃39; doesn&＃39;t exist

原因&＃xff1a;

MySQL对lower_case_table_names使用区分大小写的表名比较设置  (可能值为&＃39;0&＃39;)。但是&＃xff0c;FishEye在表名  FE-4276中不一致-数据库表OPEN的大小写不一致。您可以使用以下查询确认设置&＃xff1a;

show variables like &＃39;lower_case_table_names&＃39;;

解决&＃xff1a;

重新启动MySQL和FishEye / Crucible。

方法二&＃xff1a;

我的情况是springboot项目&＃xff0c;配置文件的改法略有不同&＃xff1a;

我的改法是在配置文件中加上这一行&＃xff1a;

spring.jpa.hibernate.naming.physical-strategy&＃61;org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

以下转自&＃xff1a;https://blog.csdn.net/Halleycomett/article/details/78638639

报错信息

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table XXX doesn&＃39;t exist

报错原因调查

mysql里实际表名都是大写

尝试在entity上标记表名大写&＃xff0c;实际测试无效&＃xff0c;仍然找不到表

&＃64;Entity

&＃64;Data

&＃64;Table(name &＃61; "T_BASE_ORDER_PRINT_CLIENT")

public classMerchantPrinter{

&＃64;Id

&＃64;Column(name &＃61; "print_client_id")

private String id;

&＃64;Column(name &＃61; "merchant_id")

private String merchantId;

private String imei;

}

最终的解决方案

在spring jpa配置中增加physical-strategy配置解决问题

spring:

datasource:

driver-class-name: com.mysql.jdbc.Driver

url: jdbc:mysql://XXX:3306/dbname

username: root

password: *******

schema:

jpa:

hibernate:

ddl-auto: none

naming:

physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

show-sql: true

physical naming strategy &＃xff1a;used to convert a “logical name” (either implicit or explicit) name of a table or column into a physical name (e.g. following corporate naming guidelines)

physical naming strategy&＃xff1a;物理命名策略&＃xff0c;用于转换“逻辑名称”(隐式或显式)的表或列成一个物理名称