Automatically create database tables through classes, so as to get rid of the trouble of manually creating tables

When we are working on a java project, when we create a class, if we want to create a corresponding database to store multiple pieces of information, if there are many properties of this class, how can we avoid making mistakes when creating tables or reduce the workload? I recently developed a database gadget CreateTableUtil that automatically creates the corresponding class. See the following article for the source code. Now I'll focus on how to use it.

Tool usage background

The project database stores class information, but there are dozens of attributes of this class. When creating a table with sql script, errors can occur and a lot of code is written. When creating a table with database connection tool, attributes or data type errors can be missing. For a corresponding class, it is very important for users to customize the gadget for generating the database. For the student party, using this gadget CreateTableUtil during graduation design can greatly reduce the error probability of creating tables. The most important thing is to save time!

1, Introduce jdbc dependency required by CreateTableUtil

Kindly recommend my article: On JDBC Technology

1.1 use Spring framework to integrate CreateTableUtil mode;

In POM The following dependencies are introduced into XML:

      <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
       <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

Our tool mainly uses jdbc for database connection.

1.1.1 create util toolkit in the project directory;

1. Create Createtableutil package in util Toolkit
2. Create Config class under Createtableutil package;

package com.vaccine.mywork.util.Createtableutil;

import lombok.Data;

@Data
public class Config {
    private int id;
    private String driverClassName="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/yourdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC" ;
    private String username="root";
    private String password="root";
    }

Note: I use lombok tool here. If you don't have lombok tool when using it, remember to build classes manually

3. Create CreateTable implementation class;

package com.vaccine.mywork.util.Createtableutil;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class Createtable {
    //Support map to obtain values through K-v
    private  List getClassInfo(Object o) {

        Field[] fields = o.getClass().getDeclaredFields();

        String[] fieldNames = new String[fields.length];

        List list = new ArrayList();

        Map infoMap = null;

        for (int i = 0; i < fieldNames.length; i++) {
            infoMap = new HashMap();

            infoMap.put("type", fields[i].getType().toString());

            infoMap.put("name", fields[i].getName());

            list.add(infoMap);
        }
        return list;

    }
    //Gets the property name of the class
    private  String[] getClassdatatype(Object o){
        Field[] fields = o.getClass().getDeclaredFields();

        String[] fieldtypes = new String[fields.length];


        for (int i = 0; i < fieldtypes.length; i++) {
            fieldtypes[i] = fields[i].getType().toString();
        }
        return fieldtypes;
    }

    //Gets the property type of the class
    private  String[] getClassdataName(Object o){
        Field[] fields = o.getClass().getDeclaredFields();

        String[] fieldNames = new String[fields.length];


        for (int i = 0; i < fieldNames.length; i++) {
            fieldNames[i] = fields[i].getName();
        }
        return fieldNames;
    }

    //Automatically generate sql statements
    public String createTablesql(String tablename,Object obs){
        //Judge whether it meets the specification
        if(tablename.isEmpty()||obs==null){
            return "Incomplete parameters";
        }
        //Gets the property name of the class
        String[] name = getClassdataName(obs);
        String[] type = getClassdatatype(obs);
        String sql ="create table if not exists "+tablename+"(";
        //shifting clause
        for(int i = 0;i<type.length;i++){
            if(type[i].equals("class java.lang.String")){
                type[i] = "varchar(255)";
            }
        }
        for(int i = 0;i<name.length-1;i++){
            sql=sql+name[i]+" "+type[i]+",";
        }
        sql=sql+name[name.length-1]+" "+type[name.length-1]+")";

      return sql;
    }

    public static String createTable(Config config, String sql,String tablename) throws ClassNotFoundException, SQLException {
        Class.forName(config.getDriverClassName());
        String url = config.getUrl();
        String user=config.getUsername();
        String password = config.getPassword();
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement st = conn.createStatement();
        //4. Execute sql
        st.executeUpdate(sql);//Create table
        String sql1 = "select * from "+tablename;
        int res =0;
        if(st.executeQuery(sql1)!=null){
            res=1;
        }
        //5. Release resources
        st.close();
        conn.close();
        return  res>0? "Created successfully":"Creation failed";
    }

    public String createTableUtils(Object obs,String tablename) throws SQLException, ClassNotFoundException {
        if(obs!=null){
            Config config = new Config();
            Createtable con = new Createtable();
            String sql =  con.createTablesql(tablename,obs);
            System.out.println(sql);
            System.out.println(createTable(config,sql,tablename));
        }
        return null;
    }

}

1.2 frameless integration CreateTableUtil mode;

1. Prepare the database driver package and add it to the project dependency:
Create the folder Lib in the project and rely on the package mysql-connector-java-5.1.47 Copy the jar to lib. Reconfigure the jar
Package into the dependencies of this project: right click the project Open Module Settings, and in Modules, click the project to configure
Dependencies, click +, JARS or Directories, and configure the lib folder into dependencies, indicating the
jar packages are introduced as dependencies.
Document directory reference:

Mysql-connector-java-5.1.36 jar package has been uploaded to GitHub and is ready to use!

2. The configuration of Config needs to be implemented manually;

package Createtableutil;

public class Config {
    private String driverClassName="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/vaccines?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC" ;
    private String username="root";
    private String password="318422";

    public String getDriverClassName() {
        return driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }
}

2, Use steps

2.1 create an instance class

In order to show the powerful function of CreateTableUtil, I create a super multi attribute class;

package com.vaccine.mywork.model;

import com.vaccine.mywork.util.Createtableutil.Createtable;
import lombok.Data;
import java.sql.SQLException;
@Data
public class Vaccine {
    private  int id;
    private  String vaccid; //Vaccine number
    private  String name; //Vaccine name
    private  String type; //Vaccine type
    private  String temp1; //Storage temperature
    private  String temp2; //Transport temperature
    private  int coldroomid; //Cold storage id
    private String createtime; //Production time
    private String keep; //quality guarantee period
    private String shop; //manufacturer
    private String intoroomtime; //Warehousing time
    private String rootid; //Administrator id
    private String guige; //Vaccine specification
    private int price; //Vaccine price
    private String desction; //describe
    private String canperson; //Vaccinated population
    private String body; //Inoculation site
    private String way; //Inoculation process
    private String usefultime; //term of validity
    private String warn; //remarks
}

2.2 create the main method in this class and start calling the tool;

  public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Vaccine  vaccine= new Vaccine();//Create the entity class that needs to create the database
        Createtable con = new Createtable(); //Create tool class
        con.createTableUtils(vaccine,"vaccdb");
        //The first parameter is the class to create the database, and the second parameter is to set the name of the table;
    }

3.3 run the main method and check whether the table is created in the database;

Automatically generated sql statements:

create table if not exists vaccdb(id int,vaccid varchar(255),name varchar(255),type varchar(255),temp1 varchar(255),temp2 varchar(255),coldroomid int,createtime varchar(255),keep varchar(255),shop varchar(255),intoroomtime varchar(255),rootid varchar(255),guige varchar(255),price int,desction varchar(255),canperson varchar(255),body varchar(255),way varchar(255),usefultime varchar(255),warn varchar(255))
Created successfully

summary

This tool is mainly realized by the reflection mechanism with jdbc and java classes. I just developed this tool for automatically generating database tables on the basis of predecessors; The project needs to be optimized. For example, when automatically generating sql statements, I only process the conversion of String to varchar. For example, the Date type is not converted, and the primary key is not set and the real data length of the project is not given; It needs to be modified through your project requirements; You only need to modify the createTablesql method in the Createtable class;

//Automatically generate sql statements
    public String createTablesql(String tablename,Object obs){
        //Judge whether it meets the specification
        if(tablename.isEmpty()||obs==null){
            return "Incomplete parameters";
        }
        //Gets the property name of the class
        String[] name = getClassdataName(obs);
        String[] type = getClassdatatype(obs);
        String sql ="create table if not exists "+tablename+"(";
        //shifting clause
        for(int i = 0;i<type.length;i++){
            if(type[i].equals("class java.lang.String")){
                type[i] = "varchar(255)";
            }
        }
        for(int i = 0;i<name.length-1;i++){
            sql=sql+name[i]+" "+type[i]+",";
        }
        sql=sql+name[name.length-1]+" "+type[name.length-1]+")";

      return sql;
    }