java - 连接两个表时的休眠异常
问题描述
数据库表:
student (
stud_id int primary key,
stud_name varchar(10)
);
product (
product_id int primary key,
product_name varchar(10),
product_price int
);
user (
stud_id int,
product_id int,
foreign key(product_id) references product(product_id),
foreign key(stud_id) references student(stud_id)
);
我正在运行的 SQL 查询:
select s.stud_name, result.tot_exp
from student s
inner join (
select u.stud_id sid, sum(product_price) tot_exp
from product p
inner join user u on u.product_id = p.product_id
group by u.stud_id order by tot_exp desc
) result on s.stud_id = result.sid
order by result.tot_exp desc;
实体类:
class Product {
@Id
int product_id;
String product_name;
int product_price;
@ManyToOne() //not accepting cascade
@JoinTable(name="user",
joinColumns= { @JoinColumn(name="stud_id") },
inverseJoinColumns= { @JoinColumn(name = "product_id") }
)
Student s;
// ...
}
class Student {
@Id
int stud_id;
String stud_name;
@OneToMany//(cascade = CascadeType.ALL)
// @JoinTable(name="user",
// joinColumns= { @JoinColumn(name="stud_id") },
// inverseJoinColumns= { @JoinColumn(name = "product_id") }
// )
// //Initially join-table was here
List<Product> products;
// ...
}
现在我的问题是,如果我@JoinTable
在Product
课堂上提到,那么我会遇到异常,但令人惊讶的是,在执行选择查询时得到了正确的结果,但在向 Student 类插入新值时却出错了。
如果我提到@JoinTable
的是Student
类,我没有收到任何错误并按预期获得结果。如果我在表中插入新学生也没有问题。
任何人都可以阐明实际发生的事情吗?@JoinTable
在Product
课堂上声明是错误的吗?请帮忙。
例外:
Hibernate: alter table Student_Product drop index UK_f28mdtx7chxxfked98qbllv2j
Hibernate: alter table Student_Product add constraint UK_f28mdtx7chxxfked98qbllv2j unique (products_product_id)
Hibernate: alter table Student_Product add constraint FKad7avlyg2mjh9nhnh3u64yacd foreign key (products_product_id) references Product (product_id)
Hibernate: alter table Student_Product add constraint FKkkdpc75v3wtdvoq2087lt5rjn foreign key (Student_stud_id) references Student (stud_id)
Apr 28, 2021 11:07:45 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "Apr 28, 2021 11:07:43 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate ORM core version 5.4.27.Final
Apr 28, 2021 11:07:44 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
Apr 28, 2021 11:07:44 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Apr 28, 2021 11:07:44 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/fst]
Apr 28, 2021 11:07:44 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {password=****, user=root}
Apr 28, 2021 11:07:44 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Apr 28, 2021 11:07:44 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
Apr 28, 2021 11:07:44 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
Apr 28, 2021 11:07:45 PM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@daf22f0] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Hibernate: create table Student_Product (Student_stud_id integer not null, products_product_id integer not null) type=MyISAM
Apr 28, 2021 11:07:45 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "create table Student_Product (Student_stud_id integer not null, products_product_id integer not null) type=MyISAM" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table Student_Product (Student_stud_id integer not null, products_product_id integer not null) type=MyISAM" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277)
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
at hibernate.retry.hibernate.App.main(App.java:30)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=MyISAM' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
... 13 more
Hibernate: alter table Student_Product drop index UK_f28mdtx7chxxfked98qbllv2j
Hibernate: alter table Student_Product add constraint UK_f28mdtx7chxxfked98qbllv2j unique (products_product_id)
Hibernate: alter table Student_Product add constraint FKad7avlyg2mjh9nhnh3u64yacd foreign key (products_product_id) references Product (product_id)
Hibernate: alter table Student_Product add constraint FKkkdpc75v3wtdvoq2087lt5rjn foreign key (Student_stud_id) references Student (stud_id)
Apr 28, 2021 11:07:45 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "alter table Student_Product add constraint FKad7avlyg2mjh9nhnh3u64yacd foreign key (products_product_id) references Product (product_id)" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Student_Product add constraint FKad7avlyg2mjh9nhnh3u64yacd foreign key (products_product_id) references Product (product_id)" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:433)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:249)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
at hibernate.retry.hibernate.App.main(App.java:30)
Caused by: java.sql.SQLSyntaxErrorException: Table 'fst.student_product' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
... 12 more
Apr 28, 2021 11:07:45 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "alter table Student_Product add constraint FKkkdpc75v3wtdvoq2087lt5rjn foreign key (Student_stud_id) references Student (stud_id)" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Student_Product add constraint FKkkdpc75v3wtdvoq2087lt5rjn foreign key (Student_stud_id) references Student (stud_id)" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:433)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:249)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
at hibernate.retry.hibernate.App.main(App.java:30)
Caused by: java.sql.SQLSyntaxErrorException: Table 'fst.student_product' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
... 12 more
Hibernate: alter table user add constraint FKhfswe0v9qoihn3j6e8lxgdkj4 foreign key (stud_id) references Product (product_id)
Apr 28, 2021 11:07:45 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "alter table user add constraint FKhfswe0v9qoihn3j6e8lxgdkj4 foreign key (stud_id) references Product (product_id)" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table user add constraint FKhfswe0v9qoihn3j6e8lxgdkj4 foreign key (stud_id) references Product (product_id)" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:433)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:249)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
at hibernate.retry.hibernate.App.main(App.java:30)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`fst`.`#sql-1aa8_b7`, CONSTRAINT `FKhfswe0v9qoihn3j6e8lxgdkj4` FOREIGN KEY (`stud_id`) REFERENCES `product` (`product_id`))
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
... 12 more
Hibernate: select s.stud_name,result.tot_exp from student s inner join
(select u.stud_id sid, SUM(product_price) tot_exp from product p inner join user u on u.product_id=p.product_id group by u.stud_id order by tot_exp desc) result
on s.stud_id=result.sid order by result.tot_exp desc
Name :Austin Spending:14000
Name :Jason Spending:9000
Name :Kendall Spending:3500
Name :Ramsey Spending:3000
Name :Jeremy Spending:1500
休眠配置文件:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.password">123</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/fst</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.default_schema">fst</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<property name="hibernate.current_session_context_class">thread</property>
<mapping class="hibernate.retry.hibernate.Student"/>
<mapping class="hibernate.retry.hibernate.Product"/>
</session-factory>
</hibernate-configuration>
解决方案
@JoinTable注释只能在关联的拥有方使用,另一个被称为反向(或mappedBy
)方。
您的案例的正确映射应如下所示:
@Entity
class Product {
// ...
@ManyToOne
@JoinTable(name="user",
joinColumns= @JoinColumn(name="stud_id"),
inverseJoinColumns = @JoinColumn(name = "product_id")
)
Student s;
// ...
}
@Entity
class Student {
@OneToMany(mappedBy = "s")
List<Product> products;
}
PS 通常连接表用于建模@ManyToMany关联。双向@OneToMany关联的常见情况是在拥有方只需要一个外键。
推荐阅读
- python - 如何在没有覆盖重定向或属性的情况下在 linux LXDE 上使用 tkinter 删除标题栏?
- gradle - Gatling 报告场景中第一个请求组的响应时间更长
- sql-server - 创建 2 对唯一 id 数据行
- javascript - 在 jquery 中调整视口大小时禁用和启用滚动窗口
- java - 如何正确地只允许业务部分分类帐中的数量为 5
- java - Libgdx 断言失败 [表达式:pointCount > 0]
- javascript - 如何从另一个页面获取值并将其输出到选择列表?JAVASCRIPT
- mysql - 更换电脑后我的数据库变成了“只读”
- c++ - 在 C++ 中重载 -> 运算符以返回自定义对象
- javascript - 如何使用 Leaflet Marker onclick 路由到 Ionic 4 中的另一个页面