Java hibernate secondary table

Hibernate : @SecondaryTable doesn’t work

I know, @SecondaryTable issues were published numerous times, so, if there is the same one (I haven’t found it yet), please, give me the link or an advice. I have two tables in my database ( firstTable and secondTable ), two POJO Hibernate classes ( FirstTablePojo and SecondTablePojo ).

+----------+ +-----------+ |firstTable| |secondTable| |----------+ |-----------+ |featureId |------------>|featureId |(secondTable's primary key) |foo | |featureName| |bar | +-----------+ +----------+ 

I want to show fields from both these tables in the jsp from the single list object, I decided to use @SecondaryTable . These two tables are connected by the featureId field (which is a primary key for the secondTable ), I want the featureName from the secondTable to be shown along with fields from the firstTable . The FirstTablePojo is preceded by this annotation:

@SecondaryTable(name="secondTable", pkJoinColumns=@PrimaryKeyJoinColumn(name="featureId", referencedColumnName = "featureId")) 

 @ManyToOne @JoinColumn(name="featureId", table="secondTable") String featureName; 

When with a help of Источник

Аннотация @SecondaryTable

С одной стороны, это противоположность аннотации @Embedded (которая позволяет вынести часть полей сущности в другой класс, но сохранять данные в единой таблице). Тут наоборот — происходит разбивка одной сущности на две таблицы.

С другой стороны, @SecondaryTable — это альтернатива аннотации @OneToOne (особенно с @MapsId). Ниже мы увидим, что таблицы генерируются точь-в-точь такие, как в примере с @OneToOne+@MapsId:

Модель

Итак, есть всего одна сущность, но она будет разбита на две таблицы. Во вторую таблицу мы хотим вынести телефон пользователя:

@Entity @SecondaryTable(name = "UserDetails", pkJoinColumns = @PrimaryKeyJoinColumn(name = "user_id", referencedColumnName = "id")) @Table(name = "users") public class User < @Id @GeneratedValue(strategy = GenerationType.SEQUENCE) private long id; private String name; @Column(name="phone", table="UserDetails") private String phone; //getters/setters/constructors >

Внутри аннотации @SecondaryTable мы задаем имя второй таблицы user-details.

Поле phone аннотируем @Column, а внутри поясняем, что его надо положить в user-details.

@PrimaryKeyJoinColumn задает, что user_id будет первичным ключом в таблице user-details, и одновременно он будет внешним ключом, ссылающимся на поле id главной таблицы users (из которой мы выносим поля).

Вроде все, в итоге получается такая схема.

Таблицы в базе

Скрипт, генерируемый PostgreSQL. Видно, что user_id в таблице user_details одновременно первичный и внешний ключ:

CREATE TABLE public.user_details ( phone character varying(255) COLLATE pg_catalog."default", user_id bigint NOT NULL, CONSTRAINT user_details_pkey PRIMARY KEY (user_id), CONSTRAINT fkicouhgavvmiiohc28mgk0kuj5 FOREIGN KEY (user_id) REFERENCES public.users (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION )

CREATE TABLE public.users ( id bigint NOT NULL, name character varying(255) COLLATE pg_catalog."default", CONSTRAINT users_pkey PRIMARY KEY (id) )

Добавление пользователей и выборка

Если и имя, и поле телефона заполнить, то в базу добавятся две записи — по одной в каждую таблицу.

Если же телефон оставить пустым, то будет добавлена только строка в таблицу users.

@Service public class UserService < @Autowired UserRepository userRepository; @Transactional public void addUser(String name, String phone) < User user = new User(name); if (phone != null) user.setPhone(phone); userRepository.save(user); >>

Добавим пару пользователей, второго — с пустым телефоном:

userService.addUser("Jane1", "111-111"); userService.addUser("Jane2", null);

Результат (для пустого телефона строка в user_details не добавилась, только в users):

Выборка из UserRepository генерирует SQL с left join по двум таблицам:

List users=userRepository.findAll();

SELECT user0_.id AS id1_1_, user0_.name AS name2_1_, user0_1_.phone AS phone1_0_ FROM users user0_ LEFT OUTER JOIN user_details user0_1_ ON user0_.id=user0_1_.user_id

Итоги

Добавить комментарий Отменить ответ

Прошу прощения: на комментарии временно не отвечаю.

Источник

Hibernate Tips: How to map an entity to multiple tables

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

I’m working with a legacy database, and I need to map an entity to 2 database tables. Is there any way to do that with JPA or Hibernate?

Solution:

Yes, you can map an entity to 2 database tables in 2 simple steps:

1. You need to annotate your entity with JPA’s @Table and @SecondaryTable annotations and provide the names of the first and second table as the value of the name parameters.
2. You need to annotate each attribute which you want to map to the secondary table with a @Column annotation and set the name of the secondary table as the value of the table attribute.

Map the Author entity to 2 tables

Let’s take a look at a simple example that maps the author and the author_details table to the Author entity. Here are the 2 tables:

The following code maps these tables to the Author entity.

The @Table annotation defines the primary table to which the entity attributes get mapped by default. In this example, that’s the case for the id, version, firstName, and lastName attributes.

The @SecondaryTable annotation specifies the second database table to which the entity gets mapped. It consists of the columns id, pseudonym, and category. You need to annotate the attributes that map these columns with an additional @Column annotation that provides the name of the secondary table.

@Entity @Table(name = «author») @SecondaryTable(name = «author_details») public class Author

That’s all you need to do to map the 2 database tables to the Author entity. Every time you persist or update an Author entity, Hibernate writes the values of the id, firstName, lastName, and version attributes to the author table, and the values of the id, pseudonym, and category attributes to the author_details table.

And when you read an Author entity, Hibernate gets the attribute values from the same 2 tables.

EntityManager em = emf.createEntityManager(); em.getTransaction().begin(); Author a = new Author(); a.setFirstName("Thorben"); a.setLastName("Janssen"); a.setCategory(Category.NON_FICTION); a.setPseudonym("Thorben Janssen"); em.persist(a); em.getTransaction().commit(); em.close();

As you can see in the log output, Hibernate uses the name and value of the primary key column of the primary table also as the name and value of the primary key column of the secondary table.

09:12:40,154 DEBUG [org.hibernate.SQL] - select nextval ('hibernate_sequence') 09:12:40,204 DEBUG [org.hibernate.SQL] - insert into author (firstName, lastName, version, id) values (?, ?, ?, ?) 09:12:40,218 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - [Thorben] 09:12:40,218 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [VARCHAR] - [Janssen] 09:12:40,219 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [INTEGER] - [0] 09:12:40,222 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [4] as [BIGINT] - [1] 09:12:40,225 DEBUG [org.hibernate.SQL] - insert into author_details (category, pseudonym, id) values (?, ?, ?) 09:12:40,225 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [VARCHAR] - [Thorben Janssen] 09:12:40,226 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [BIGINT] - [1]

Customize the primary key columns

The previous example didn’t specify the name of the primary key column in the secondary table. By default, Hibernate uses an identical mapping to map the primary key attribute to both tables. If you’re working with a legacy database, you might need to adapt this for the secondary table.

You can do that with the pkJoinColumns attribute of the @SecondaryTable annotation. It allows you to customize the mapping with one or more @PrimaryKeyJoinColumn annotations. Its name attribute specifies the name of the primary key column of the secondary table and the referencedColumnName attribute defines the name of the primary key column of the primary table.

@Entity @Table(name = «author») @SecondaryTable(name = «author_details», pkJoinColumns = @PrimaryKeyJoinColumn(name = «authorId», referencedColumnName = «id»)) public class Author

When you now persist a new Author entity, Hibernate uses the authorId column as the primary key column of the author_details table.

09:13:34,254 DEBUG [org.hibernate.SQL] - select nextval ('hibernate_sequence') 09:13:34,315 DEBUG [org.hibernate.SQL] - insert into author (firstName, lastName, version, id) values (?, ?, ?, ?) 09:13:34,321 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - [Thorben] 09:13:34,323 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [VARCHAR] - [Janssen] 09:13:34,324 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [INTEGER] - [0] 09:13:34,327 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [4] as [BIGINT] - [1] 09:13:34,330 DEBUG [org.hibernate.SQL] - insert into author_details (category, pseudonym, authorId) values (?, ?, ?) 09:13:34,331 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [VARCHAR] - [Thorben Janssen] 09:13:34,331 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [BIGINT] - [1]

Learn more:

If you enjoyed this post, you might also be interested in the following posts about entity mappings:

Hibernate Tips Book

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Источник

hibernate two tables per one entity

I have one entity — User . It is described by User.class . Hibernate creates one table per entity, so when I call session.save(user) , my data is always saved to this table. Now I need another table for data of same User type, and I need to save my entity to that table only. Data structure (something like this):

table users_1_table < string id; string username; >table users_2_table

session.save(user1,"users_1_table") session.save(user2,"users_2_table") 

and in result I should have user1 in users_1_table and user2 in users_2_table . Due to system limitation I can not put these two objects in one table. (Even creating extra field is bad idea). Can I do this without subclassing? Using programmaticaly hibernate configuration?

Why do you want to do that without subclassing? Subclassing it the solution for Hibernate. If you want to do that without subclassing, you’re using wrong tools.

5 Answers 5

This is a widely asked question even on SO, and also widely the answers are related to Subclass or actually SuperClass approach (e.g. [1])

Actual answer:

On these posts [2], [3] they suggest to use a xml mapping with EntityName parameter.

So, mapping with xml you don’t need superclass, just give the EntityName parameter to two identical mappings.

Example Mapping:

Then depending on which type of entity you need you call the appropriate session methods as:

Posts 2 & 3 were used as a basis for this snippet. Official source [4]

After match:

One answer on the first question which is actually link to this post [5] there is different approach:

You say bye to the first instance of the object, clone the data to fresh instance and persist that with different name. Thus, no violation on Hibernate logic and everybody content: same data at two tables and no sub-classes used.

Well, the implementation or code or credibility of that approach is so and so, I haven’t tested it neither.

In this post [6] there is another person trying to challenge the super class approach with something simpler, but again, the most credible answer states it is not possible another way around, the official non-xml approach is the said subclass approach.

Источник