Shulou(Shulou.com)06/03 Report--

This article focuses on "how to get the table structure". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to get the table structure.

I. introduction

I remember when I first entered the Internet industry, according to the idea and model of MVC, every time I developed new functions, I would write dao, service, controller related service classes in turn, including the corresponding dto, entity, vo and other entity classes. If I had multiple single tables, I would write similar code repeatedly. In retrospect, I felt as if I was in the Stone Age!

In fact, when carefully summed up, for any single table operation, basically around add (Create), delete (Delete), change (Update), check (Retrieve) four directions for data operation, referred to as CRUD!

Their basic CRUD ideas are basically the same except for the differences in table names and storage space.

In order to solve the pain points of these repetitive efforts, the industry has gradually opened up a number of code generators, the purpose is also very simple, is to reduce the tedious manual operation, focus on business development, and improve development efficiency.

Today, what we are going to introduce is also the code generator, which many beginners may think is very advanced. The code generator is actually a very simple thing, not at all sophisticated.

When you finish reading this article, you will have a complete grasp of the logic of the code generator and can even be deeply customized according to your own project.

Second, the realization train of thought

Next we take the SpringBoot project as an example, the data persistence operation uses Mybatis, the database uses Mysql, and compiles a code generator that automatically generates basic functions such as add, delete, change, query, etc., including controller, service, dao, entity, dto, vo and other information.

The idea of implementation is as follows:

Step 1: get information such as table field name, type, table comments, etc.

Step 2: write the corresponding template based on the freemarker template engine

Step 3: generate the corresponding java code according to the corresponding template

2.1. Get the table structure

First, let's create a test_db table with the following script:

CREATE TABLE test_db (id bigint (20) unsigned NOT NULL COMMENT 'primary key ID', name varchar (50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT' 'COMMENT' name', is_delete tinyint (4) NOT NULL DEFAULT'0' COMMENT 'whether to delete 1: deleted 0: not deleted', create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time', PRIMARY KEY (id), KEY idx_create_time (create_time) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=' test table'

After the table is created, based on the test_db table, we query the corresponding table result field name, type, and memo information, which will be used by the code generator later!

# get the corresponding table structure SELECT column_name, data_type, column_comment FROM information_schema.columns WHERE table_schema = 'yjgj_base' AND table_name =' test_db'

At the same time, get the corresponding table comments, which are used to generate comment information!

# get the corresponding table comment SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yjgj_base' AND table_name =' test_db'

2.2. Write templates

Write mapper.ftl templates that cover new, modified, deleted, queried and other information

${pro.fieldName}, ${pro.fieldName} insert into ${tableName} (${pro.fieldName}, ${pro.fieldName}, ${pro.fieldName}) values ${r "# {obj." + pro.proName + r "}"} Insert into ${tableName} ${pro.fieldName}, ${r "# {" + pro.proName + r ", jdbcType=" + pro.fieldType + r "} Update ${tableName} ${pro.fieldName} = ${r "# {" + pro.proName + r ", jdbcType=" + pro.fieldType + r "}}, where ${primaryId} = ${r" # "+" ${primaryId} "+ r" JdbcType=BIGINT} "} update ${tableName} when id = ${r" # + "obj.id" + r "}"} then ${r "# {obj." + pro.proName + r " JdbcType= "+ pro.fieldType + r"} where id = ${r "# {" + "obj.id" + r "}"} delete from ${tableName} where ${primaryId} = ${r "#" + "${primaryId}" + r " JdbcType=BIGINT} "} select from ${tableName} where ${primaryId} = ${r" # "+" ${primaryId} "+ r" JdbcType=BIGINT} "} select from ${tableName} select from ${tableName} and ${primaryId} in ${r" # {"+" item "+ r"} "} select from ${tableName} select count (${primaryId}) from ${tableName} select from ${ TableName} limit ${r "#" {"+" start JdbcType=INTEGER "+ r"} "}, ${r" # {"+" end,jdbcType=INTEGER "+ r"} "}

Write dao.ftl data access templates

Package ${daoPackageName}; import com.example.generator.core.BaseMapper; import java.util.List; import ${entityPackageName}. ${entityName}; import ${dtoPackageName}. ${dtoName}; / * * @ ClassName: ${daoName} * @ Description: data access interface * @ author ${authorName} * @ date ${currentTime} * * / public interface ${daoName} extends BaseMapper {int countPage (${dtoName} ${dtoName?uncap_first}) List selectPage (${dtoName} ${dtoName?uncap_first});}

Write service.ftl service interface templates

Package ${servicePackageName}; import com.example.generator.core.BaseService; import com.example.generator.common.Pager; import ${voPackageName}. ${voName}; import ${dtoPackageName}. ${dtoName}; import ${entityPackageName}. ${entityName} / * @ ClassName: ${serviceName} * @ Description: ${entityName} Business access Interface * @ author ${authorName} * @ date ${currentTime} * / public interface ${serviceName} extends BaseService {/ * paged list query * @ param request * / Pager getPage (${dtoName} request);}

Write serviceImpl.ftl service implementation class templates

Package ${serviceImplPackageName}; import com.example.generator.common.Pager; import com.example.generator.core.BaseServiceImpl; import com.example.generator.test.service.TestEntityService; import org.springframework.beans.BeanUtils; import org.springframework.stereotype.Service; import org.springframework.util.CollectionUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; import ${daoPackageName}. ${daoName}; import ${entityPackageName}. ${entityName}; import ${dtoPackageName}. ${dtoName} Import ${voPackageName}. ${voName}; @ Service public class ${serviceImplName} extends BaseServiceImpl implements ${serviceName} {private static final Logger log = LoggerFactory.getLogger (${serviceImplName} .class); / * * paged list query * @ param request * / public Pager getPage (${dtoName} request) {List resultList = new ArrayList (); int count = super.baseMapper.countPage (request); List dbList = count > 0? Super.baseMapper.selectPage (request): new ArrayList (); if (! CollectionUtils.isEmpty (dbList)) {dbList.forEach (source- > {${voName} target = new ${voName} (); BeanUtils.copyProperties (source, target); resultList.add (target);} return new Pager (request.getCurrPage (), request.getPageSize (), count, resultList);}}

Write controller.ftl control layer templates

Package ${controllerPackageName}; import com.example.generator.common.IdRequest; import com.example.generator.common.Pager; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.Objects; import ${servicePackageName}. ${serviceName} Import ${entityPackageName}. ${entityName}; import ${dtoPackageName}. ${dtoName}; import ${voPackageName}. ${voName}; / * @ ClassName: ${controllerName} * @ Description: external access interface * @ author ${authorName} * @ date ${currentTime} * / @ RestController @ RequestMapping ("/ ${entityName?uncap_first}") public class ${controllerName} {@ Autowired private ${serviceName} ${serviceName?uncap_first} / * * paged list query * @ param request * / @ PostMapping (value = "/ getPage") public Pager getPage (@ RequestBody ${dtoName} request) {return ${serviceName?uncap_first} .getPage (request) Query details * @ param request * / @ PostMapping (value = "/ getDetail") public ${voName} getDetail (@ RequestBody IdRequest request) {${entityName} source = ${serviceName?uncap_first} .selectById (request.getId ()); if (Objects.nonNull (source)) {${voName} result = new ${voName} (); BeanUtils.copyProperties (source, result); return result;} return null } / * New operation * @ param request * / @ PostMapping (value = "/ save") public void save (${dtoName} request) {${entityName} entity = new ${entityName} (); BeanUtils.copyProperties (request, entity); ${serviceName?uncap_first} .insert (entity) } / * Edit operation * @ param request * / @ PostMapping (value = "/ edit") public void edit (${dtoName} request) {${entityName} entity = new ${entityName} (); BeanUtils.copyProperties (request, entity); ${serviceName?uncap_first} .updateById (entity) Delete operation * @ param request * / @ PostMapping (value = "/ delete") public void delete (IdRequest request) {${serviceName?uncap_first} .deleteById (request.getId ());}}

Write entity.ftl entity class templates

Package ${entityPackageName}; import java.io.Serializable; import java.math.BigDecimal; import java.util.Date; / * @ ClassName: ${entityName} * @ Description: ${tableDes!} entity class * @ author ${authorName} * @ date ${currentTime} * * / public class ${entityName} implements Serializable {private static final long serialVersionUID = 1L / * {pro.proDes!} * / private ${pro.proType} ${pro.proName}; public ${pro.proType} get$ {pro.proName?cap_first} () {return this.$ {pro.proName};} public ${entityName} set$ {pro.proName?cap_first} (${pro.proType} ${pro.proName}) {this.$ {pro.proName} = ${pro.proName}; return this }}

Write dto.ftl entity class templates

Package ${dtoPackageName}; import com.example.generator.core.BaseDTO; import java.io.Serializable; / * * @ ClassName: ${dtoName} * @ Description: request entity class * @ author ${authorName} * @ date ${currentTime} * * / public class ${dtoName} extends BaseDTO {}

Write vo.ftl view entity class templates

Package ${voPackageName}; import java.io.Serializable; / * * @ ClassName: ${voName} * @ Description: return the view entity class * @ author ${authorName} * @ date ${currentTime} * * / public class ${voName} implements Serializable {private static final long serialVersionUID = 1L;}

As careful netizens may have seen, we use BaseMapper, BaseService, BaseServiceImpl and other service classes in the template.

The reason for these three classes is that in the template, we have a large number of the same method names, including similar logic, except for the different entity classes, so we can use generic classes to extract these services into common parts.

BaseMapper, which is mainly responsible for extracting the public methods of the dao layer

Package com.example.generator.core; import org.apache.ibatis.annotations.Param; import java.io.Serializable; import java.util.List; import java.util.Map; / * * @ author pzblog * @ Description * @ since 2020-11-11 * / public interface BaseMapper {/ * * bulk insert * @ param list * @ return * / int insertList (@ Param ("list") List list) / * insert a record as needed * @ param entity * @ return * / int insertPrimaryKeySelective (T entity); / * modify a record as needed (via the primary key ID) * @ return * / int updatePrimaryKeySelective (T entity) / * * modify records on demand in batches (via primary key ID) * @ param list * @ return * / int updateBatchByIds (@ Param ("list") List list); / * delete * @ param id primary key ID * @ return * / int deleteByPrimaryKey (Serializable id) according to ID / * * query according to ID * @ param id key ID * @ return * / T selectByPrimaryKey (Serializable id); / * * query on demand * @ param entity * @ return * / List selectByPrimaryKeySelective (T entity) / * bulk query * @ param ids primary key ID collection * @ return * / List selectByIds (@ Param ("ids") List

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.