This is the 13th day of my participation in the August More Text Challenge

Ali JAVA specification: [mandatory] do not use foreign keys and cascades, all foreign key concepts must be resolved at the application layer.

The reason:

When a foreign key is added to the database, the database needs to check whether a foreign key is added to the database. For example, when a foreign key is added to the database, the database needs to check whether a foreign key is added to the database. The database needs to check whether a foreign key is added to the database. Student_id = student; student_id = student; student_id = student; student_id = student; student_id = student;

This will lead to business changes in the database of large and medium-sized projects, and the cost of modifying the database will become high. Foreign keys affect the query and insert speed of the database. Therefore, do not insert foreign keys. The dependency between tables must be resolved by application programs.

How to do not use foreign keys, currently adopted methods:

For example, there are user tables and role tables in the database, as well as the role association tables corresponding to users.

The user table UserBean is as follows:

DROP TABLE "UserBean";
CREATE TABLE "UserBean" (
  "user_id" NUMBER(11) NOT NULL ,
  "username" VARCHAR2(40 BYTE) ,
  "password" VARCHAR2(40 BYTE) 
)
Copy the code

The role table RoleBean is as follows:

DROP TABLE "RoleBean";
CREATE TABLE "RoleBean" (
  "role_id" NUMBER(11) NOT NULL ,
  "role_name" VARCHAR2(100 BYTE) ,
  "description" VARCHAR2(200 BYTE) , 
)
Copy the code

The UserRole association table userroles is as follows:

DROP TABLE "UserRole";
CREATE TABLE "UserRole" (
  "user_id" NUMBER(11) NOT NULL ,
  "role_id" NUMBER(11) NOT NULL 
)
Copy the code

In this case, the database table does not add foreign keys, but in the application layer, when writing SQL statements.

When you need to query the role of a user, in roleMapper,

public interface RoleMapper {
    // Use username to search for user role information
    String findRoleByUsername(@Param("username") String username);
}

Copy the code

In roleService,

@Service
public interface RoleService {
    // Use username to search for user role information
    String findRoleByUsername(@Param("username") String username);
}

Copy the code

In roleServiceImpl

@Service
public class RoleServiceImpl implements RoleService{

    @Autowired
    RoleMapper roleMapper;
    
    @Override
    public String findRoleByUsername(String username) {
        returnroleMapper.findRoleByUsername(username); }}Copy the code

In rolemapper. XML, the findRoleByUsername SQL statement could be written as:


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.tjm.mapper.RoleMapper">
    <select id="findRoleByUsername" resultType="String" parameterType="String">
        select
               "RoleBean"."role"
        from
             "UserBean"
        RIGHT JOIN "UserRole" on "UserBean"."user_id"="UserRole"."user_id"
        LEFT JOIN "RoleBean" on "RoleBean"."role_id"="UserRole"."role_id"
        where "username"=#{username}
    </select>

</mapper>
Copy the code

Right Join and left Join are used to associate the two tables to query the information we need. Insert and delete operate in the same way, which ensures the consistency of data in the database without using foreign keys.