自动生成代码数据库查询语句

 

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface GeneratorMapper {
   @Select("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables"
         + " where table_schema = (select database())")
   List<Map<String, Object>> list();

   @Select("select count(*) from information_schema.tables where table_schema = (select database())")
   int count(Map<String, Object> map);

   @Select("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables \r\n"
         + "    where table_schema = (select database()) and table_name = #{tableName}")
   Map<String, String> get(String tableName);

   @Select("select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns\r\n"
         + " where table_name = #{tableName} and table_schema = (select database()) order by ordinal_position")
   List<Map<String, String>> listColumns(String tableName);
}

• 自动生成代码的时候用到的数据语句1：

 

@Select("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables"
      + " where table_schema = (select database())")
List<Map<String, Object>> list();

 

SELECT table_name tableName, ENGINE, table_comment tableComment, create_time createTime FROM information_schema.tables

 

根据库名查数据库的表名、引擎、表注解、创建时间

SELECT table_name tableName, ENGINE, table_comment tableComment, create_time createTime FROM information_schema.tables  WHERE table_schema = 'jtdb'

备注：jdbc为库名

 

 

• 自动生成代码的时候用到的数据语句2：

 

@Select("select count(*) from information_schema.tables where table_schema = (select database())")
int count(Map<String, Object> map);

SELECT DATABASE()       此语句是查询当前的数据库库名

 

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'mtshop'       此语句是查询mtshop数据库有多少表

 

 

 

• 自动生成代码的时候用到的数据语句3：

 

@Select("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables \r\n"
      + "    where table_schema = (select database()) and table_name = #{tableName}")
Map<String, String> get(String tableName);

SELECT table_name tableName, ENGINE, table_comment tableComment, create_time createTime FROM information_schema.tables 

WHERE table_schema = (SELECT DATABASE()) AND table_name = "user"     

查询当前库的表名为user的这些字段

 

 

• 自动生成代码的时候用到的数据语句4：

 

@Select("select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns\r\n"
      + " where table_name = #{tableName} and table_schema = (select database()) order by ordinal_position")
List<Map<String, String>> listColumns(String tableName);

SELECT column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra FROM information_schema.columns

WHERE table_name = 'testuser' AND table_schema = (SELECT DATABASE()) ORDER BY ordinal_position

这个语句是查询当前数据库的testuser表的 这些字段