java - When a web application built using Springboot is running, can the database table be automatically generated and used?

Question

1. Let’s talk about the premise first:
Spring application built using spring boot;
Hibernate is used, and spring-boot-starter-data-jpa is added to the pom;
The database is MySql;
The development tool is IDEA.

I have never done web development before, and I don’t know much about databases, so if what I want to say below is unrealistic or the implementation method is not good, I hope you will give me some advice. Thank you! !

2. The needs you want to meet:
For example,
I need to enter the information of several students from multiple schools in this database.
The amount of data may be very large, and I don't want to put so much student information in the same table.
My idea is that
each school has a "XX school student information table" (tbl_School_XX, such as "tbl_School_01"), and then enter the information of the students in that school into that table.
Use your own school index table (tbl_School_Index). This table has an index field (such as index) to store the table name of each school (such as "tbl_School_01").
·When you want to enter student information from a new school, when adding a school, the application will automatically generate a new "XX School Student Information Table", and the application will automatically give the table a name (such as "tbl_School_01"), use This new table is used to store information about students in the new school. At the same time, this table name will also be added to the index field (index) of the school index table (tbl_School_Index).
·When you want to read student information, or when you want to enter new student information from an existing school, the data in the index field (index) will be found from the school index table (tbl_School_Index) (such as "tbl_School_01") , and then read/write table tbl_School_01.

As shown

3. The difficulties I am currently encountering are:
(1) I know that relational databases do not allow tables within tables, so I came up with such a method, but I don’t know if it is feasible or appropriate.
(2) When I create a new entity class (@Entity), after configuring the attributes and get/set methods, running the application will generate a corresponding table in the database, but I don’t need to generate that table. It is necessary to dynamically generate a table corresponding to that entity class when adding a school.

4. Other ideas

I have thought about putting the information of all students from several schools in one table, and then adding a "school" field to that table to mark which school the student is from, but I am afraid that when the amount of data becomes large, Sometimes, various problems may arise when using this table.

5. What I said may be a bit verbose. Thank you for reading it carefully. I hope you can give me some advice. Thank you!

Answer 1

When you want to enter student information from a new school, when adding a school, the application will automatically generate a new "XX School Student Information Table", and the application will automatically name the table (such as "tbl_School_01"), use this new table To save the information of students in the new school. At the same time, this table name will also be added to the index field (index) of the school index table (tbl_School_Index).

First of all, based on your current description (the current description does not involve some operations that require cross-table operations), this method is feasible. When adding students, if the school does not exist, you need to insert the school index and create a new related table. Insert student information. This can be achieved with code (this is without restarting the application). Without restarting the application, I don’t know if it can be achieved using configuration. If anyone knows, please let me know. Thank you.

(2) When I create a new entity class (@Entity), after configuring the attributes and get/set methods, running the application will generate a corresponding table in the database, but I don’t need to generate that table; When a school is added, a table corresponding to that entity class is dynamically generated.

What you are talking about here is that the application can be restarted. If the application can be restarted, data can be inserted through configuration and tables can be automatically created. You need to configure hibernate and put the corresponding SQL script under resources.

Configuration of hibernate in application.properties

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root

# 1
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

# 2
spring.jpa.show-sql=true

sql script storage