This is the 16th day of my participation in the August Text Challenge.More challenges in August

As we all know, when designing database tables, there are one-to-one, one-to-many, many-to-one and many-to-read relationships between tables, and Spring Data JPA is a fully automatic ORM framework. There are also ways to manage the relationships between entities in JPA.

JPA uses @onetoone, @onetomany, @ManytoOne, and @Manytomany annotations to describe and manage associative associations between entities.

@OneToOneOne-to-one mapping

The @oneToOne annotation is used to describe the one-to-one mapping between managed entities, such as users and accounts, classes and homeroom teachers, and so on.

  • cascade: Cascaded operations

Cascade defines the actions that an entity can take on its associated objects.

PERSIST: indicates a new operation. When a source entity object is added, its associated entity object is addedMERGE: Corresponds to the update operation, that is, when the source entity object is updated, its associated entity object is updatedREMOVE: Corresponds to the deletion operation, that is, when the source entity object is deleted, its associated entity object is also deletedREFRESH: When updating an entity, the system obtains the latest entity information before updating itDETACHWhen the entity is updated, the data in its corresponding table is updated intelligently. The table that references the entity is not updated

  • fetch: Pulls data

Fetch defines when associated data is queried at query time.

LAZY: Queries associated entities only when they are used,EAGER: Queries associated entities while querying source entities

@JoinColumnAssociated column:

Generally, relational mapping annotations are used in conjunction with the @JoinColumn annotation. The @JoinColumn annotation defines the field by which entities are associated, which is equivalent to the ON of an associated query in an SQL statement. If you don’t have the @JoinColumn annotation, by default the primary key of the two entities is associated

name: The source entity corresponds to the field associated with the table. The default source entity corresponds to the primary key of the tablereferencedColumnName: Indicates the field associated with the table that references the entity. The default value is the primary key corresponding to the table that references the entityunique: Constraint associated fields are uniquenullable: Indicates whether the associated field can be emptyinsertabl: Generated when addinginsertWhether the statement wraps the field. Conflicts with the corresponding field in the source entityupdatable: generated during updateuptateWhether the statement wraps the field. Conflicts with the corresponding field in the source entitycolumnDefinition: generates columnsDDLStatementSQLfragmenttable: Specifies the name of the table where the associated field resides. The default is the representation of the source entityforeignKeyGenerated by:DDLStatement whether the associated column is subject to a foreign key constraint. Foreign key constraints are added by default

The sample

  1. Build table
CREATE TABLE `user` (
  `id` int(64) NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `name` varchar(255) DEFAULT NULL COMMENT 'Username',
  `sex` varchar(2) DEFAULT NULL COMMENT 'gender',
  `birth_day` datetime DEFAULT NULL COMMENT Date of birth,
  `account_id` int(64) DEFAULT NULL COMMENT 'account id'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='users';

CREATE TABLE `account` (
  `id` int(64) NOT NULL AUTO_INCREMENT COMMENT 'account id',
  `username` varchar(255) DEFAULT NULL COMMENT 'Username',
  `password` varchar(255) DEFAULT NULL COMMENT 'password'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='account';

Copy the code
  1. Build entity
@Data
@Entity
@Table(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    private String sex;

    private Date birthDay;

// private Integer accountId;

    / * * *@JoinColumnThe field accountId cannot exist unless the cascade add and update is turned off, i.e@JoinColumn(name = "accountId", insertable = false, updatable = false)
     */
    @JoinColumn(name = "accountId")
    @OneToOne(cascade = CascadeType.ALL)
    private Account account;
    
}


@Data
@Entity
@Table(name = "account")
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String username;

    private String password;
}

Copy the code
  1. writeRepository
public interface UserRepo extends JpaRepository<User.Integer> {}Copy the code

test

  • new

The accountId field associated with the User entity is commented out because the insertable property of @JoinColumn defaults to true. If you don’t want to comment out the accountId field, It can be written as follows

  • The query

  • Delay querying associated objects

In the @oneToOne annotation, set fetch = fetchtype. LAZY to delay the query of the associated entity’s data. The diagram below:

Write two two-line print statements here, and you can see that only the User entity is queried when no Account is retrieved. The data corresponding to the Account entity is queried only when the Account is obtained

Note: Put @Transactional(readOnly = true) ona method or an exception will be thrown

  • update

Note: The accountId field associated with the User entity is commented out, so the accountId field is not set. AcoountId will program NULL if the accountId field is not commented out and no value is set

  • delete

@OneToManyand@ManyToOne More than a pair ofandFor one moreassociated

The most common examples of one-to-many and many-to-one relationships are orders and order items, where an order can have multiple order details and multiple order item details for an order. JPA uses @onetomany and @manytoOne to manage one-to-many and many-to-one associations

@OneToManyand@ManyToOneProperties with@OneToOne

The sample

  1. Build table

Create the item and item_detail tables, where item_detail holds the ID of the item

CREATE TABLE `item` (
  `id` int(64) NOT NULL AUTO_INCREMENT COMMENT 'order id',
  `item_name` varchar(255) DEFAULT NULL COMMENT 'Order name'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='order';


CREATE TABLE `item_detail` (
  `id` int(64) NOT NULL AUTO_INCREMENT COMMENT 'Order Detail ID',
  `item_detail_name` varchar(255) DEFAULT NULL COMMENT 'Order Details Name',
  `item_id` int(64) DEFAULT NULL COMMENT 'order id'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Item ID';

Copy the code

Note: The item_ID field in the ITEM_detail table does not have a non-null constraint. This is because if a non-null constraint is set, cascading new will fail

  1. Build entity

Create the entities corresponding to the item and ITEM_detail tables. The item entity uses the List structure to hold multiple ItemDetail entities.

You can use List, Set structures to hold multiple reference objects

@Data
@Entity
@Table(name = "item")
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String itemName;

    @JoinColumn(name = "itemId")
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private List<ItemDetail> detailList;

}


@Data
@Entity
@Table(name = "item_detail")
public class ItemDetail {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String itemDetailName;

    private Integer itemId;
}

Copy the code
  1. writeRepository
public interface ItemRepo extends JpaRepository<Item.Integer> {}Copy the code

test

  • new

If you want to get the last entity, you can look again based on the returned ID

  • The query

@ManyToManyMany-to-many relationships

When a many-to-many relationship is being built, it is common to use a third-party table to represent the relationship. A common example of a many-to-many relationship is users and roles, where a user has multiple roles and a role can be owned by multiple users.

@ManyToManyProperties with@OneToOne

@JoinTableAssociated intermediate tables

For many-to-many relationships, an intermediate table is required to describe the relationship. Similarly, @JoinTable is required to associate intermediate entities to describe the relationship between entities

  • name: Specifies the name of the intermediate table
  • catalog: Catalog of the intermediate table
  • schema: Schema of the intermediate table
  • joinColumns: The name of the field associated with the primary table in the intermediate table
  • inverseJoinColumns: Associated table Name of the associated field in an intermediate table
  • foreignKey: Foreign key name of the primary table in the middle table
  • inverseForeignKey: Associated table Foreign key name associated with an intermediate table
  • uniqueConstraints: The unique constraint to place on the table. These are used only when the table generation is valid
  • indexes: Index of the table. These are used only when the table generation is valid

The sample

In this example, users and roles are used in the user table

  1. Build table
CREATE TABLE `role` (
  `id` int(64) NOT NULL AUTO_INCREMENT COMMENT 'character id',
  `role_name` varchar(255) DEFAULT NULL COMMENT 'Role name'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=The 'role';

CREATE TABLE `user_role` (
  `id` int(64) NOT NULL AUTO_INCREMENT COMMENT 'User role association ID',
  `user_id` int(64) DEFAULT NULL COMMENT 'user id',
  `role_id` int(64) DEFAULT NULL COMMENT 'character id'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Role association Table';
Copy the code
  1. Build entity

Add a List

field to the User entity, and add @manytomany and @JoinTable annotations to that field to describe the many-to-many relationship

@Data
@Entity
@Table(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    private String sex;

    private Date birthDay;

// private Integer accountId;

    / * * *@JoinColumnThe field accountId cannot exist unless the cascading add and update is turned off, as in@JoinColumn(name = "accountId", insertable = false, updatable = false)
     */
    @JoinColumn(name = "accountId")
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Account account;

    @JoinTable(name ="user_role", joinColumns = @JoinColumn(name="user_id"), inverseJoinColumns = @JoinColumn(name="role_id") )
    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private List<Role> roleList;

}


@Data
@Entity
@Table(name = "role")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String roleName;
    
}
Copy the code
  1. writeRepository

Repository also uses UserRepo, which was built earlier

test

  • new

  • The query