对于需要快速开发的小型项目,开发前期数据库表可能会经常改动;
由于懒于写SQL,自己就弄了个Java精简建表框架。有多精简?代码只有200行!只有一个类。复制粘贴就能使用。
当然这个框架还不完整,只能用来建一些结构稍简单的表。
以下是框架的核心也是唯一一个类的代码:
public class TB {
//表前缀
private static final String TB_PREFIX = "TS_";
//表名称
private String tb_name;
/**
* 以下是建表元素
*/
public static final String PK_SIGN = "PRIMARY KEY";
public static final String PK_AUTO = "PRIMARY KEY AUTO_INCREMENT";
public static final String NUL = "NULL";
public static final String NO_NUL = "NOT NULL";
public static final String UNIQ = "UNIQUE";
public static final String UNIQ_NO_NUL = "NOT NULL UNIQUE";
public static final String FK_NO_NUL = "FOREIGN KEY(@CN) REFERENCES @OTHER";
public static final String FK_NUL = "FOREIGN KEY(@CN) REFERENCES @OTHER NULL";
private static final String CREATE_TB_TEMPLATE = "CREATE TABLE @TB (";
private static final String DROP_TB_TEMPLATE = "DROP TABLE IF EXISTS @TB";
//最后生成的sql语言
private String sql = "";
/**
* long类型,长度14
*/
public static final long TIME = 14L;
/**
* int 类型,长度为1
*/
public static final int BOOL = 1;
/**
* 构造方法
* @param tb_name 表名
*/
public TB(String tb_name) {
this.tb_name = TB_PREFIX + tb_name;
// init sql
sql = CREATE_TB_TEMPLATE.replace("@TB", this.tb_name) + "\n";
}
/**
* 生成指定size的VARCHAR类型列
*/
public static String CHAR(int size){
return size+"";
}
/**
* 生成double类型的列
*/
public static double FLOAT = 1.1;
/**
* 生成主键列(自增)
*/
public TB pa(String name, Object val){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
sql = sql + temp + " "+PK_AUTO+",\n";
}
return this;
}
/**
* 生成主键列(自定义赋值)
*/
public TB ps(String name, Object val){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
sql = sql + temp + " "+PK_SIGN+",\n";
}
return this;
}
/**
* 生成一个可为null的列
*/
public TB ll(String name, Object val){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
sql = sql + temp + " "+NUL+",\n";
}
return this;
}
/**
* 生成一个not null列
*/
public TB nn(String name, Object val){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
sql = sql + temp + " "+NO_NUL+",\n";
}
return this;
}
/**
* 生成一个unique且可为null的列
*/
public TB ul(String name, Object val){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
sql = sql + temp + " "+UNIQ+",\n";
}
return this;
}
/**
* 生成一个unique且not null的列
*/
public TB un(String name, Object val){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
sql = sql + temp + " "+UNIQ_NO_NUL+",\n";
}
return this;
}
/**
* 生成一个可为null的外键列
*/
public TB fl(String name, Object val, String others){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
String col_type = FK_NUL;
col_type = col_type.replace("@CN", name).replace("@OTHER", others);
sql = sql + temp + " "+col_type+",\n";
}
return this;
}
/**
* 生成一个not null的外键列
*/
public TB fn(String name, Object val, String others){
String temp = createLineSql(name, val);
if (!temp.equals("")) {
String col_type = FK_NO_NUL;
col_type = col_type.replace("@CN", name).replace("@OTHER", others);
sql = sql + temp + " "+col_type+",\n";
}
return this;
}
/**
* 给表增加一个列
* @param col_name 列名
* @param col_type 列类型
* @param val 值
* @param others 外键属性
*/
public TB add(String col_name, String col_type, Object val, String others) {
String temp = createLineSql(col_name, val);
if (!temp.equals("")) {
if(col_type==null){
col_type = NUL;
}
if(col_type.equals(FK_NO_NUL) || col_type.equals(FK_NUL)){
col_type = col_type.replace("@CN", col_name).replace("@OTHER", others);
}
sql = sql + temp + " "+col_type+",\n";
}
return this;
}
/**
* 提交
* @param dropTable 是否删除表
*/
public void commit(boolean dropTable){
if(dropTable){
drop();
}
sql =sql+");";
sql = sql.replace(",\n)", ")");
sql = sql.toLowerCase();
System.out.println(sql);
}
private String createLineSql(String col_name, Object val) {
String temp = "";
if (val == null) {
return temp;
}
// val is double
if (val instanceof Double) {
temp = col_name + " double";
} else if (val instanceof Long) {
temp = col_name + " bigint(" + val + ")";
} else if (val instanceof Integer) {
temp = col_name + " int(" + val + ")";
} else if (val instanceof String) {
temp = col_name + " varchar(" + val + ")";
}
return temp;
}
public void drop() {
String sql = DROP_TB_TEMPLATE.replace("@TB", tb_name) + ";";
sql = sql.toLowerCase();
System.out.println(sql);
}
public static TB create(String tb_name) {
return new TB(tb_name);
}
}
下面是其使用方法(最简单的main方法调用):
public static void main(String[] args) {
TB tb = TB("member");
tb.pa("id", TB.TIME);
tb.nn("name",TB.CHAR(10));
tb.nn("birth", TB.TIME);
tb.nn("sex", TB.BOOL);
tb.nn("create_date", TB.TIME);
tb.fn("type_id", TB.TIME, "ts_type(id)");
tb.commit(true);
}
运行main方法,最终生成的SQL语句:
drop table if exists ts_member;
create table ts_member (
id bigint(14) primary key auto_increment,
name varchar(10) not null,
birth bigint(14) not null,
sex int(1) not null,
create_date bigint(14) not null,
type_id bigint(14) foreign key(type_id) references ts_type(id));
将生成的语句复制到各种SQL IDE运行即可~~~