In the development of some localization projects, there is a requirement that the developed projects should be able to build the database required by the system and its corresponding database tables when they are deployed and started for the first time.
To solve such needs, in fact, many open-source frameworks can automatically generate database tables, such as mybatis plus and spring JPA. But have you ever thought about whether this open-source framework can meet the needs of building a more complex table structure by yourself? If not, how can it be realized?
I wrote an article earlier Activiti workflow learning notes (III) -- Analysis of the underlying principle of automatically generating 28 database tables , which analyzes the underlying principle of workflow Activiti automatically building 28 database tables. In my opinion, one of the reasons to learn the underlying principles of open source framework is to learn something that can be used by me. Therefore, after analyzing and understanding the underlying principle of workflow automatically building 28 database tables, I decided to also write a demo of self creating databases and tables based on the Springboot framework. I refer to workflow Activiti6 The underlying table building implementation logic of version 0 is based on the Springboot framework to realize that the project can automatically build a variety of complex databases and tables in the form of multi table association when it is started for the first time.
The overall implementation idea is not complicated. It is probably like this: first, design a complete set of database sql scripts for creating multi table associations, put them in the resource, and automatically execute the sql scripts during the spring boot startup process.
First of all, first design a set of feasible multi table association database scripts at one time. Here, I mainly refer to the implementation case of using the table provided by Activiti, because it has many table associations designed internally, so there is no additional design.
The sql script statement is the usual create table creation statement, similar to the following:
create table ACT_PROCDEF_INFO (
ID_ varchar(64) not null,
PROC_DEF_ID_ varchar(64) not null,
REV_ integer,
INFO_JSON_ID_ varchar(64),
primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;Add external primary key and index——
create index ACT_IDX_INFO_PROCDEF on ACT_PROCDEF_INFO(PROC_DEF_ID_);
alter table ACT_PROCDEF_INFO
add constraint ACT_FK_INFO_JSON_BA
foreign key (INFO_JSON_ID_)
references ACT_GE_BYTEARRAY (ID_);
alter table ACT_PROCDEF_INFO
add constraint ACT_FK_INFO_PROCDEF
foreign key (PROC_DEF_ID_)
references ACT_RE_PROCDEF (ID_);
alter table ACT_PROCDEF_INFO
add constraint ACT_UNIQ_INFO_PROCDEF
unique (PROC_DEF_ID_);The whole is to design a set of sql statements that meet the needs of the scenario and save them in the sql script files are finally stored in the resource directory, similar to the following:
The next step is to implement the interface of CommandLineRunner, rewrite the bean callback method of its run(), and develop the function of automatic database and table creation logic in the run method.
At present, I have uploaded the developed demo to my github. Children's shoes of interest can be downloaded by themselves. At present, they can be directly downloaded and run in the local environment, which can be used for targeted reference according to their actual needs.
First, when solving such requirements, the first thing to be solved is how to implement the method of creating tables only once after Springboot is started.
Here you need to use a CommandLineRunner interface, which is a class that comes with Springboot and implements the interface. Its rewritten run method will be automatically executed after Springboot is started. The source code of the interface is as follows:
@FunctionalInterface
public interface CommandLineRunner {
/**
*Callback to run the bean
*/
void run(String... args) throws Exception;
}To expand, in Springboot, you can define multiple implementation CommandLineRunner interface classes, and sort these implementation classes. You only need to add @ Order, and the overridden run methods can be executed in Order. Code case verification:
@Component
@Order(value=1)
public class WatchStartCommandSqlRunnerImpl implements CommandLineRunner {
@Override
public void run(String... args) throws Exception {
System.out.println("first Command implement");
}
@Component
@Order(value = 2)
public class WatchStartCommandSqlRunnerImpl2 implements CommandLineRunner {
@Override
public void run(String... args) throws Exception {
System.out.println("the second Command implement");
}
}The information printed on the console is as follows:
1 first Command execution 2 second Command execution
According to the above verification, we can rewrite the bean callback method of run() by implementing the interface of CommandLineRunner, which is used to implement the table creation method only once after Springboot is started. To realize the function of starting the project to create a table, you may also need to judge whether there is a corresponding database. If not, you should first create a new database. At the same time, you should consider that there is no corresponding database. Therefore, when we connect to MySQL for the first time through jdbc, we should connect to an existing database. After each MySQL installation is successful, there will be a MySQL library. When establishing a jdbc connection for the first time, you can connect to it first.
The code is as follows:
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&characterEncoding=UTF-8&ueSSL=false&serverTimezone=GMT%2B8";
Connection conn= DriverManager.getConnection(url,"root","root");After establishing a connection with MySql software, first create a Statement object, which is an object in jdbc that can be used to execute static SQL statements and return the results generated by it. Here, it can be used to find and create libraries.
//Create Statement object
Statement statment=conn.createStatement();
/**
Use the query method executeQuery of statement ("show databases like \" FTE \ "")
Check whether MySql has fte this database
**/
ResultSet resultSet=statment.executeQuery("show databases like \"fte\"");
//If resultset If next() is true, it proves that it already exists;
//If false, if it proves that the library does not exist, execute statement Executeupdate ("create database FTE") creates a database
if(resultSet.next()){
log.info("The database already exists");
}else {
log.info("Database does not exist, create it first fte database");
if(statment.executeUpdate("create database fte")==1){
log.info("New database created successfully");
}
}After the database fte is automatically created, you can create tables in the fte database.
I encapsulate the related methods of table creation into the SqlSessionFactory class. The related table creation methods also need to use the jdbc Connection to connect to the database. Therefore, the connected Connection reference variable needs to be passed as a parameter to the initial constructor of SqlSessionFactory:
public void createTable(Connection conn,Statement stat) throws SQLException {
try {
String url="jdbc:mysql://127.0.0.1:3306/fte?useUnicode=true&characterEncoding=UTF-8&ueSSL=false&serverTimezone=GMT%2B8";
conn=DriverManager.getConnection(url,"root","root");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactory(conn);
sqlSessionFactory.schemaOperationsBuild("create");
} catch (SQLException e) {
e.printStackTrace();
}finally {
stat.close();
conn.close();
}
}After initializing new SqlSessionFactory(conn), you can use the Connection object that has been connected in this object.
public class SqlSessionFactory{
private Connection connection ;
public SqlSessionFactory(Connection connection) {
this.connection = connection;
}
......
}There are two cases of parameter transfer here, that is, "create" represents the function of creating a table structure, and "drop" represents the function of deleting a table structure:
sqlSessionFactory.schemaOperationsBuild("create");When you enter this method, you will make a judgment first——
public void schemaOperationsBuild(String type) {
switch (type){
case "drop":
this.dbSchemaDrop();break;
case "create":
this.dbSchemaCreate();break;
}
}If this Dbschematecreate(), perform table creation:
/**
* Add database table
*/
public void dbSchemaCreate() {
if (!this.isTablePresent()) {
log.info("Start execution create operation");
this.executeResource("create", "act");
log.info("implement create complete");
}
}this.executeResource("create", "act") represents the creation of a database table named act——
public void executeResource(String operation, String component) {
this.executeSchemaResource(operation, component, this.getDbResource(operation, operation, component), false);
}Where this Getdbresource (operation, operation, component) is the path to get the sql script. After entering the method, you can see——
public String getDbResource(String directory, String operation, String component) {
return "static/db/" + directory + "/mysql." + operation + "." + component + ".sql";
}Next, read the sql script under the path to generate the input stream byte stream:
public void executeSchemaResource(String operation, String component, String resourceName, boolean isOptional) {
InputStream inputStream = null;
try {
//Read sql script data
inputStream = IoUtil.getResourceAsStream(resourceName);
if (inputStream == null) {
if (!isOptional) {
log.error("resource '" + resourceName + "' is not available");
return;
}
} else {
this.executeSchemaResource(operation, component, resourceName, inputStream);
}
} finally {
IoUtil.closeSilently(inputStream);
}
}Finally, the core implementation of the whole sql script is in this Executeschemaresource (operation, component, resourcename, InputStream) method——
/**
* Execute sql script
* @param operation
* @param component
* @param resourceName
* @param inputStream
*/
private void executeSchemaResource(String operation, String component, String resourceName, InputStream inputStream) {
//sql statement splicing string
String sqlStatement = null;
Object exceptionSqlStatement = null;
try {
/**
* 1.jdbc Connect to mysql database
*/
Connection connection = this.connection;
Exception exception = null;
/**
* 2,Read the sql script data in "static/db/create/mysql.create.act.sql" in the branch
*/
byte[] bytes = IoUtil.readInputStream(inputStream, resourceName);
/**
* 3.Convert the data lines in the sql file into strings, and replace the newline with the escape character "\ n"
*/
String ddlStatements = new String(bytes);
/**
* 4.Read string data as character stream
*/
BufferedReader reader = new BufferedReader(new StringReader(ddlStatements));
/**
* 5.Read according to the escape character "\ n" in the string
*/
String line = IoUtil.readNextTrimmedLine(reader);
/**
* 6.Cycle through each row of the
*/
for(boolean inOraclePlsqlBlock = false; line != null; line = IoUtil.readNextTrimmedLine(reader)) {
/**
* 7.If there is still data in the next line, it proves that it has not been read completely, and it can still be read
*/
if (line.length() > 0) {
/**
8.When there is not enough complete table creation statement,! line.endsWith(";") Will be true,
That is, the splicing is carried out in a circular way all the time. When ";" is encountered Jump out of the if statement
**/
if ((!line.endsWith(";") || inOraclePlsqlBlock) && (!line.startsWith("/") || !inOraclePlsqlBlock)) {
sqlStatement = this.addSqlStatementPiece(sqlStatement, line);
} else {
/**
9.If the symbol ";" is encountered in circular splicing, This means that a complete sql table creation statement has been spliced, such as
create table ACT_GE_PROPERTY (
NAME_ varchar(64),
VALUE_ varchar(300),
REV_ integer,
primary key (NAME_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin
In this way, the table creation statement can be executed into the database through code, as follows:
**/
if (inOraclePlsqlBlock) {
inOraclePlsqlBlock = false;
} else {
sqlStatement = this.addSqlStatementPiece(sqlStatement, line.substring(0, line.length() - 1));
}
/**
* 10.Wrap the table creation Statement string into a Statement object
*/
Statement jdbcStatement = connection.createStatement();
try {
/**
* 11.Finally, execute the table creation statement into the database
*/
log.info("SQL: {}", sqlStatement);
jdbcStatement.execute(sqlStatement);
jdbcStatement.close();
} catch (Exception var27) {
log.error("problem during schema {}, statement {}", new Object[]{operation, sqlStatement, var27});
} finally {
/**
* 12.This step means that the execution of the previous sql table creation statement has ended,
* If there are no errors, it is proved that the first database table structure has been created,
* You can start splicing the next table creation statement,
*/
sqlStatement = null;
}
}
}
}
if (exception != null) {
throw exception;
}
} catch (Exception var29) {
log.error("couldn't " + operation + " db schema: " + exceptionSqlStatement, var29);
}
}The main function of this part of the code is to first read the data in the sql script in the form of byte stream and convert it into a string. Where there is a newline, it is replaced by the escape character "/ n". Then, the string is converted into a character stream BufferedReader for reading, and the reading of each line is divided according to the "/ n" compliance. The loop splices the read strings of each line. When the loop encounters a ";" in a line It means that it has been spliced into a complete create table creation statement, similar to this form——
create table ACT_PROCDEF_INFO (
ID_ varchar(64) not null,
PROC_DEF_ID_ varchar(64) not null,
REV_ integer,
INFO_JSON_ID_ varchar(64),
primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;At this time, you can first create a table string of the spliced create through JDBC statement Execute (SQLStatement) statement. When the execution is successful, the act_ PROCDEF_ The info table means that it has been created successfully, and then continue to read the next row in the form of BufferedReader character stream to build the next database table structure.
The whole process is probably this logic. On this basis, we can design more complex sql statements for creating tables. When the project starts, we can execute the corresponding sql statements to create tables.
The demo code has been uploaded to git and can be downloaded and run directly: https://github.com/z924931408/Springboot-AutoCreateMySqlTable.git