1. 简介

Druid SQL Parser内置了一个SchemaRepository，在内存中缓存SQL Schema信息，用于SQL语义解析中的ColumnResolve等操作。

2. 如何使用SchemaRepository

  1. import com.alibaba.druid.sql.repository.SchemaRepository;
  2.  
  3. // SchemaRepository是和数据库类型相关的，构造时需要传入dbType
  4. final String dbType = JdbcConstants.MYSQL;
  5. SchemaRepository repository = new SchemaRepository(dbType);
  6.  
  7. repository.console("use sc00;");
  8.  
  9. String sql = "CREATE TABLE `test1` (\n" +
  10.         "  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',\n" +
  11.         "  `c_tinyint` tinyint(4) DEFAULT '1' COMMENT 'tinyint',\n" +
  12.         "  `c_smallint` smallint(6) DEFAULT 0 COMMENT 'smallint',\n" +
  13.         "  `c_mediumint` mediumint(9) DEFAULT NULL COMMENT 'mediumint',\n" +
  14.         "  `c_int` int(11) DEFAULT NULL COMMENT 'int',\n" +
  15.         "  `c_bigint` bigint(20) DEFAULT NULL COMMENT 'bigint',\n" +
  16.         "  `c_decimal` decimal(10,3) DEFAULT NULL COMMENT 'decimal',\n" +
  17.         "  `c_date` date DEFAULT '0000-00-00' COMMENT 'date',\n" +
  18.         "  `c_datetime` datetime DEFAULT '0000-00-00 00:00:00' COMMENT 'datetime',\n" +
  19.         "  `c_timestamp` timestamp NULL DEFAULT NULL COMMENT 'timestamp'  ON UPDATE CURRENT_TIMESTAMP ,\n" +
  20.         "  `c_time` time DEFAULT NULL COMMENT 'time',\n" +
  21.         "  `c_char` char(10) DEFAULT NULL COMMENT 'char',\n" +
  22.         "  `c_varchar` varchar(10) DEFAULT 'hello' COMMENT 'varchar',\n" +
  23.         "  `c_blob` blob COMMENT 'blob',\n" +
  24.         "  `c_text` text COMMENT 'text',\n" +
  25.         "  `c_mediumtext` mediumtext COMMENT 'mediumtext',\n" +
  26.         "  `c_longblob` longblob COMMENT 'longblob',\n" +
  27.         "  PRIMARY KEY (`id`,`c_tinyint`),\n" +
  28.         "  UNIQUE KEY `uk_a` (`c_varchar`,`c_mediumint`),\n" +
  29.         "  KEY `k_c` (`c_tinyint`,`c_int`),\n" +
  30.         "  KEY `k_d` (`c_char`,`c_bigint`)\n" +
  31.         ") ENGINE=InnoDB AUTO_INCREMENT=1769503 DEFAULT CHARSET=utf8mb4 COMMENT='10000000'";
  32.  
  33. repository.console(sql);
  34.  
  35. // 在如下的代码中可以知道repository中已经存在表test1
  36. MySqlCreateTableStatement createTableStmt = (MySqlCreateTableStatement) repository.findTable("test1").getStatement();
  37. assertEquals(21, createTableStmt.getTableElementList().size());
  38.  
  39. // 通过执行命令"show columns from test1"可以获得mysql console风格的输出
  40. assertEquals("+--------------+---------------+------+-----+---------------------+-----------------------------+\n" +
  41.         "| Field        | Type          | Null | Key | Default             | Extra                       |\n" +
  42.         "+--------------+---------------+------+-----+---------------------+-----------------------------+\n" +
  43.         "| id           | bigint(20)    | NO   | PRI | NULL                | auto_increment              |\n" +
  44.         "| c_tinyint    | tinyint(4)    | YES  | PRI | 1                   |                             |\n" +
  45.         "| c_smallint   | smallint(6)   | YES  |     | 0                   |                             |\n" +
  46.         "| c_mediumint  | mediumint(9)  | YES  |     | NULL                |                             |\n" +
  47.         "| c_int        | int(11)       | YES  |     | NULL                |                             |\n" +
  48.         "| c_bigint     | bigint(20)    | YES  |     | NULL                |                             |\n" +
  49.         "| c_decimal    | decimal(10,3) | YES  |     | NULL                |                             |\n" +
  50.         "| c_date       | date          | YES  |     | 0000-00-00          |                             |\n" +
  51.         "| c_datetime   | datetime      | YES  |     | 0000-00-00 00:00:00 |                             |\n" +
  52.         "| c_timestamp  | timestamp     | YES  |     | NULL                | on update CURRENT_TIMESTAMP |\n" +
  53.         "| c_time       | time          | YES  |     | NULL                |                             |\n" +
  54.         "| c_char       | char(10)      | YES  | MUL | NULL                |                             |\n" +
  55.         "| c_varchar    | varchar(10)   | YES  | MUL | hello               |                             |\n" +
  56.         "| c_blob       | blob          | YES  |     | NULL                |                             |\n" +
  57.         "| c_text       | text          | YES  |     | NULL                |                             |\n" +
  58.         "| c_mediumtext | mediumtext    | YES  |     | NULL                |                             |\n" +
  59.         "| c_longblob   | longblob      | YES  |     | NULL                |                             |\n" +
  60.         "+--------------+---------------+------+-----+---------------------+-----------------------------+\n", 
  61. repository.console("show columns from test1"));
  62.  
  63. // 执行alter语句，修改repository中内容
  64. repository.console("alter table test1 drop column c_decimal;");
  65. assertEquals(20, createTableStmt.getTableElementList().size());
  66.  
  67. assertEquals("+--------------+--------------+------+-----+---------------------+-----------------------------+\n" +
  68.         "| Field        | Type         | Null | Key | Default             | Extra                       |\n" +
  69.         "+--------------+--------------+------+-----+---------------------+-----------------------------+\n" +
  70.         "| id           | bigint(20)   | NO   | PRI | NULL                | auto_increment              |\n" +
  71.         "| c_tinyint    | tinyint(4)   | YES  | PRI | 1                   |                             |\n" +
  72.         "| c_smallint   | smallint(6)  | YES  |     | 0                   |                             |\n" +
  73.         "| c_mediumint  | mediumint(9) | YES  |     | NULL                |                             |\n" +
  74.         "| c_int        | int(11)      | YES  |     | NULL                |                             |\n" +
  75.         "| c_bigint     | bigint(20)   | YES  |     | NULL                |                             |\n" +
  76.         "| c_date       | date         | YES  |     | 0000-00-00          |                             |\n" +
  77.         "| c_datetime   | datetime     | YES  |     | 0000-00-00 00:00:00 |                             |\n" +
  78.         "| c_timestamp  | timestamp    | YES  |     | NULL                | on update CURRENT_TIMESTAMP |\n" +
  79.         "| c_time       | time         | YES  |     | NULL                |                             |\n" +
  80.         "| c_char       | char(10)     | YES  | MUL | NULL                |                             |\n" +
  81.         "| c_varchar    | varchar(10)  | YES  | MUL | hello               |                             |\n" +
  82.         "| c_blob       | blob         | YES  |     | NULL                |                             |\n" +
  83.         "| c_text       | text         | YES  |     | NULL                |                             |\n" +
  84.         "| c_mediumtext | mediumtext   | YES  |     | NULL                |                             |\n" +
  85.         "| c_longblob   | longblob     | YES  |     | NULL                |                             |\n" +
  86.         "+--------------+--------------+------+-----+---------------------+-----------------------------+\n", 
  87. repository.console("show columns from test1"));

3. Column Resolve

  1. final String dbType = JdbcConstants.MYSQL;
  2.  
  3. SchemaRepository repository = new SchemaRepository(dbType);
  4.  
  5. repository.console("create table t_emp(emp_id bigint, name varchar(20));");
  6. repository.console("create table t_org(org_id bigint, name varchar(20));");
  7.  
  8. String sql = "SELECT emp_id, a.name AS emp_name, org_id, b.name AS org_name\n" +
  9.         "FROM t_emp a\n" +
  10.         "\tINNER JOIN t_org b ON a.emp_id = b.org_id";
  11.  
  12. List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
  13. assertEquals(1, stmtList.size());
  14.  
  15. SQLSelectStatement stmt = (SQLSelectStatement) stmtList.get(0);
  16. SQLSelectQueryBlock queryBlock = stmt.getSelect().getQueryBlock();
  17.  
  18. // 大小写不敏感
  19. assertNotNull(queryBlock.findTableSource("A"));
  20. assertSame(queryBlock.findTableSource("a"), queryBlock.findTableSource("A"));
  21.  
  22. assertNull(queryBlock.findTableSourceWithColumn("emp_id"));
  23.  
  24. // 使用repository做column resolve
  25. repository.resolve(stmt);
  26.  
  27. assertNotNull(queryBlock.findTableSourceWithColumn("emp_id"));
  28.  
  29. SQLExprTableSource tableSource = (SQLExprTableSource) queryBlock.findTableSourceWithColumn("emp_id");
  30. assertNotNull(tableSource.getSchemaObject());
  31.  
  32. SQLCreateTableStatement createTableStmt = (SQLCreateTableStatement) tableSource.getSchemaObject().getStatement();
  33. assertNotNull(createTableStmt);
  34.  
  35. SQLSelectItem selectItem = queryBlock.findSelectItem("org_name");
  36. assertNotNull(selectItem);
  37. SQLPropertyExpr selectItemExpr = (SQLPropertyExpr) selectItem.getExpr();
  38. SQLColumnDefinition column = selectItemExpr.getResolvedColumn();
  39. assertNotNull(column);
  40. assertEquals("name", column.getName().toString());
  41. assertEquals("t_org", (((SQLCreateTableStatement)column.getParent()).getName().toString()));
  42.  
  43. assertSame(queryBlock.findTableSource("B"), selectItemExpr.getResolvedTableSource());