use of com.github.drinkjava2.jdialects.model.TableModel in project jDialects by drinkjava2.
the class DDLTest method testFKEY.
@Test
public void testFKEY() {
// FKEY
TableModel t1 = new TableModel("master1");
t1.column("id").INTEGER().pkey();
TableModel t2 = new TableModel("master2");
t2.column("name").VARCHAR(20).pkey();
t2.column("address").VARCHAR(20).pkey();
t2.column("fid").INTEGER().singleFKey("master1");
TableModel t3 = new TableModel("child");
t3.column("id").INTEGER().pkey();
t3.column("masterid1").INTEGER().singleFKey("master1", "id").fkeyTail("ON DELETE CASCADE ON UPDATE CASCADE").fkeyName("fknm");
t3.column("myname").VARCHAR(20).singleFKey("master2", "name").fkeyTail("ON DELETE CASCADE ON UPDATE CASCADE");
t3.column("myaddress").VARCHAR(20).singleFKey("master2", "address");
t3.fkey().columns("masterid1").refs("master1", "id").fkeyTail("ON DELETE CASCADE ON UPDATE CASCADE");
;
t3.fkey("FKNAME1").columns("myname", "myaddress").refs("master2", "name", "address");
t3.fkey("FKNAME2").columns("myname", "myaddress").refs("master2");
TableModel t4 = new TableModel("child2");
t4.column("id").INTEGER().pkey();
t4.column("masterid2").INTEGER();
t4.column("myname2").VARCHAR(20);
t4.column("myaddress2").VARCHAR(20);
t4.fkey().columns("masterid2").refs("master1", "id");
t4.fkey().columns("myname2", "myaddress2").refs("master2", "name", "address");
printAllDialectsDDLs(t1, t2, t3);
printOneDialectsDDLs(Dialect.MySQL5InnoDBDialect, t1, t2, t3, t4);
testOnCurrentRealDatabase(t1, t2, t3, t4);
}
use of com.github.drinkjava2.jdialects.model.TableModel in project jDialects by drinkjava2.
the class DDLTest method testNoPkey.
@Test
public void testNoPkey() {
// Test no Prime Key
// append() is a linked method
TableModel t = new TableModel("aa");
t.addColumn(new ColumnModel("aaaa"));
TableModel table = new TableModel("tb").addColumn(new ColumnModel("field1").INTEGER()).addColumn(new ColumnModel("field2").LONG());
printAllDialectsDDLs(table);
testOnCurrentRealDatabase(table);
}
use of com.github.drinkjava2.jdialects.model.TableModel in project jDialects by drinkjava2.
the class DDLCreateUtils method transferTableToObjectList.
/**
* Transfer table to a mixed DDL String or TableGen Object list
*/
/**
* @param dialect
* @param t
* @param objectResultList
*/
private static void transferTableToObjectList(Dialect dialect, TableModel t, List<Object> objectResultList) {
DDLFeatures features = dialect.ddlFeatures;
StringBuilder buf = new StringBuilder();
boolean hasPkey = false;
String pkeys = "";
String tableName = t.getTableName();
List<ColumnModel> columns = t.getColumns();
// Reserved words check
dialect.checkNotEmptyReservedWords(tableName, "Table name can not be empty");
// check index names
List<IndexModel> idexChks = t.getIndexConsts();
if (idexChks != null && !idexChks.isEmpty())
for (IndexModel index : idexChks) dialect.checkReservedWords(index.getName());
// check unique names
List<UniqueModel> ukChks = t.getUniqueConsts();
if (ukChks != null && !ukChks.isEmpty())
for (UniqueModel unique : ukChks) dialect.checkReservedWords(unique.getName());
// check Fkey names
List<FKeyModel> fkeyChks = t.getFkeyConstraints();
if (fkeyChks != null && !fkeyChks.isEmpty())
for (FKeyModel fkey : fkeyChks) dialect.checkReservedWords(fkey.getFkeyName());
for (// check column names
ColumnModel col : // check column names
columns) dialect.checkNotEmptyReservedWords(col.getColumnName(), "Column name can not be empty");
// idGenerator
for (IdGenerator idGen : t.getIdGenerators()) objectResultList.add(idGen);
// Foreign key
for (FKeyModel fkey : t.getFkeyConstraints()) objectResultList.add(fkey);
// check and cache prime keys
for (ColumnModel col : columns) {
if (col.getTransientable())
continue;
if (col.getPkey()) {
hasPkey = true;
if (StrUtils.isEmpty(pkeys))
pkeys = col.getColumnName();
else
pkeys += "," + col.getColumnName();
}
}
// create table
buf.append(hasPkey ? dialect.ddlFeatures.createTableString : dialect.ddlFeatures.createMultisetTableString).append(" ").append(tableName).append(" ( ");
for (ColumnModel c : columns) {
if (c.getTransientable())
continue;
if (c.getColumnType() == null)
DialectException.throwEX("Type not set on column \"" + c.getColumnName() + "\" at table \"" + tableName + "\"");
// column definition
buf.append(c.getColumnName()).append(" ");
// Identity
if (GenerationType.IDENTITY.equals(c.getIdGenerationType()) && !features.supportsIdentityColumns)
DialectException.throwEX("Unsupported identity setting for dialect \"" + dialect + "\" on column \"" + c.getColumnName() + "\" at table \"" + tableName + "\"");
// Column type definition
if (GenerationType.IDENTITY.equals(c.getIdGenerationType())) {
if (features.hasDataTypeInIdentityColumn)
buf.append(dialect.translateToDDLType(c.getColumnType(), c.getLengths()));
buf.append(' ');
if (Type.BIGINT.equals(c.getColumnType()))
buf.append(features.identityColumnStringBigINT);
else
buf.append(features.identityColumnString);
} else {
buf.append(dialect.translateToDDLType(c.getColumnType(), c.getLengths()));
// Default
String defaultValue = c.getDefaultValue();
if (defaultValue != null) {
buf.append(" default ").append(defaultValue);
}
// Not null
if (!c.getNullable())
buf.append(" not null");
else
buf.append(features.nullColumnString);
}
// Check
if (!StrUtils.isEmpty(c.getCheck())) {
if (features.supportsColumnCheck)
buf.append(" check (").append(c.getCheck()).append(")");
else
logger.warn("Ignore unsupported check setting for dialect \"" + dialect + "\" on column \"" + c.getColumnName() + "\" at table \"" + tableName + "\" with value: " + c.getCheck());
}
// Comments
if (c.getComment() != null) {
if (StrUtils.isEmpty(features.columnComment) && !features.supportsCommentOn)
logger.warn("Ignore unsupported comment setting for dialect \"" + dialect + "\" on column \"" + c.getColumnName() + "\" at table \"" + tableName + "\" with value: " + c.getComment());
else
buf.append(StrUtils.replace(features.columnComment, "_COMMENT", c.getComment()));
}
// tail String
if (!StrUtils.isEmpty(c.getTail()))
buf.append(c.getTail());
buf.append(",");
}
// PKEY
if (!StrUtils.isEmpty(pkeys)) {
buf.append(" primary key (").append(pkeys).append("),");
}
// Table Check
if (!StrUtils.isEmpty(t.getCheck())) {
if (features.supportsTableCheck)
buf.append(" check (").append(t.getCheck()).append("),");
else
logger.warn("Ignore unsupported table check setting for dialect \"" + dialect + "\" on table \"" + tableName + "\" with value: " + t.getCheck());
}
buf.setLength(buf.length() - 1);
buf.append(")");
// Engine for MariaDB & MySql only, for example "engine=innoDB"
String tableTypeString = features.tableTypeString;
if (!StrUtils.isEmpty(tableTypeString) && !DDLFeatures.NOT_SUPPORT.equals(tableTypeString)) {
buf.append(tableTypeString);
// EngineTail, for example:" DEFAULT CHARSET=utf8"
if (!StrUtils.isEmpty(t.getEngineTail()))
buf.append(t.getEngineTail());
}
objectResultList.add(buf.toString());
// table comment on
if (t.getComment() != null) {
if (features.supportsCommentOn)
objectResultList.add("comment on table " + t.getTableName() + " is '" + t.getComment() + "'");
else
logger.warn("Ignore unsupported table comment setting for dialect \"" + dialect + "\" on table \"" + tableName + "\" with value: " + t.getComment());
}
// column comment on
for (ColumnModel c : columns) {
if (features.supportsCommentOn && c.getComment() != null && StrUtils.isEmpty(features.columnComment))
objectResultList.add("comment on column " + tableName + '.' + c.getColumnName() + " is '" + c.getComment() + "'");
}
// index
buildIndexDLL(dialect, objectResultList, t);
// unique
buildUniqueDLL(dialect, objectResultList, t);
}
use of com.github.drinkjava2.jdialects.model.TableModel in project jDialects by drinkjava2.
the class DebugUtils method getFkeyDebugInfo.
public static String getFkeyDebugInfo(TableModel t) {
StringBuilder sb = new StringBuilder();
sb.append("Fkeys:\r");
for (FKeyModel k : t.getFkeyConstraints()) {
sb.append("FkeyName=" + k.getFkeyName());
sb.append(", ColumnNames=" + k.getColumnNames());
sb.append(", RefTableAndColumns=" + Arrays.deepToString(k.getRefTableAndColumns()));
sb.append("\r");
}
return sb.toString();
}
use of com.github.drinkjava2.jdialects.model.TableModel in project jDialects by drinkjava2.
the class TestDemo method doTest.
@Test
public void doTest() {
// DataSource
HikariDataSource ds = new HikariDataSource();
// H2 is a memory database
ds.setDriverClassName("org.h2.Driver");
ds.setJdbcUrl("jdbc:h2:mem:DBName;MODE=MYSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=0");
ds.setUsername("sa");
ds.setPassword("");
// MySQL
// ds.setDriverClassName("com.mysql.jdbc.Driver");
// ds.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test?rewriteBatchedStatements=true&useSSL=false");
// ds.setUsername("root");
// ds.setPassword("root888");
// MS-SqlServer
// ds.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// ds.setJdbcUrl("jdbc:sqlserver://localhost:1433;databaseName=test");
// ds.setUsername("sa");
// ds.setPassword("root888");
// ORACLE
// ds.setDriverClassName("oracle.jdbc.OracleDriver");
// ds.setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:XE");
// ds.setUsername("root");
// ds.setPassword("root888");
Dialect dialect = Dialect.guessDialect(ds);
// Dialect.setAllowShowDialectLog(true);
Connection conn = null;
try {
conn = ds.getConnection();
TableModel t = new TableModel("users");
t.column("firstName").VARCHAR(20).pkey();
t.column("lastName").VARCHAR(20).pkey();
t.column("age").INTEGER();
String[] ddlArray = dialect.toDropAndCreateDDL(t);
for (String ddl : ddlArray) try {
execute(conn, ddl);
} catch (Exception e) {
}
for (int i = 1; i <= 100; i++) execute(conn, "insert into users (firstName, lastName, age) values(?,?,?)", "Foo" + i, "Bar" + i, i);
Assert.assertEquals(100L, ((Number) queryForObject(conn, "select count(*) from users")).longValue());
List<Map<String, Object>> users = queryForMapList(conn, dialect.paginAndTrans(2, 10, "select concat(firstName, ' ', lastName) as UserName, age from users where age>?"), 50);
Assert.assertEquals(10, users.size());
for (Map<String, Object> map : users) System.out.println("UserName=" + map.get("USERNAME") + ", age=" + map.get("AGE"));
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
ds.close();
}
Aggregations