Examples with Dialect com.github.drinkjava2.jdialects.Dialect used on opensource projects

Example 11 with Dialect

use of com.github.drinkjava2.jdialects.Dialect 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();
}

Example 12 with Dialect

use of com.github.drinkjava2.jdialects.Dialect in project jDialects by drinkjava2.

the class DDLUtils method buildTableGeneratorDDL.

private static void buildTableGeneratorDDL(Dialect dialect, List<String> stringList, List<TableGenerator> tbGeneratorList) {
    for (TableGenerator tg : tbGeneratorList) {
        //@formatter:off
        DialectException.assureNotEmpty(tg.getName(), "TableGenerator name can not be empty");
        DialectException.assureNotEmpty(tg.getTableName(), "TableGenerator tableName can not be empty of \"" + tg.getName() + "\"");
        DialectException.assureNotEmpty(tg.getPkColumnName(), "TableGenerator pkColumnName can not be empty of \"" + tg.getName() + "\"");
        DialectException.assureNotEmpty(tg.getPkColumnValue(), "TableGenerator pkColumnValue can not be empty of \"" + tg.getName() + "\"");
        DialectException.assureNotEmpty(tg.getValueColumnName(), "TableGenerator valueColumnName can not be empty of \"" + tg.getName() + "\"");
    //@formatter:on
    }
    for (TableGenerator tg : tbGeneratorList) {
        for (TableGenerator tg2 : tbGeneratorList) {
            if (tg != tg2 && (tg2.getAllocationSize() != 0)) {
                if (tg.getName().equalsIgnoreCase(tg2.getName())) {
                    // set to 0 to skip repeated
                    tg.setAllocationSize(0);
                } else {
                    if (tg.getTableName().equalsIgnoreCase(tg2.getTableName()) && tg.getPkColumnName().equalsIgnoreCase(tg2.getPkColumnName()) && tg.getPkColumnValue().equalsIgnoreCase(tg2.getPkColumnValue()) && tg.getValueColumnName().equalsIgnoreCase(tg2.getValueColumnName()))
                        DialectException.throwEX("Dulplicated tableGenerator setting \"" + tg.getName() + "\" and \"" + tg2.getName() + "\" found.");
                }
            }
        }
    }
    Set<String> tableExisted = new HashSet<>();
    Set<String> columnExisted = new HashSet<>();
    for (TableGenerator tg : tbGeneratorList) if (tg.getAllocationSize() != 0) {
        String tableName = tg.getTableName().toLowerCase();
        String tableAndPKColumn = tg.getTableName().toLowerCase() + "..XXOO.." + tg.getPkColumnName();
        String tableAndValColumn = tg.getTableName().toLowerCase() + "..XXOO.." + tg.getValueColumnName();
        if (!tableExisted.contains(tableName)) {
            String s = dialect.ddlFeatures.createTableString + " " + tableName + " (";
            s += tg.getPkColumnName() + " " + dialect.translateToDDLType(Type.VARCHAR, 100) + ",";
            s += tg.getValueColumnName() + " " + dialect.translateToDDLType(Type.BIGINT) + " )";
            stringList.add(s);
            tableExisted.add(tableName);
            columnExisted.add(tableAndPKColumn);
            columnExisted.add(tableAndValColumn);
        } else {
            if (!columnExisted.contains(tableAndPKColumn)) {
                stringList.add("alter table " + tableName + " " + dialect.ddlFeatures.addColumnString + " " + tg.getPkColumnName() + dialect.ddlFeatures.addColumnSuffixString);
                columnExisted.add(tableAndPKColumn);
            }
            if (!columnExisted.contains(tableAndValColumn)) {
                stringList.add("alter table " + tableName + " " + dialect.ddlFeatures.addColumnString + " " + tg.getValueColumnName() + dialect.ddlFeatures.addColumnSuffixString);
                columnExisted.add(tableAndValColumn);
            }
        }
    }
}

Example 13 with Dialect

use of com.github.drinkjava2.jdialects.Dialect in project jDialects by drinkjava2.

the class DDLUtils method toCreateDDLwithoutFormat.

/**
	 * Transfer table to DDL by given dialect and without format it
	 */
public static String[] toCreateDDLwithoutFormat(Dialect dialect, Table... tables) {
    // resultList store mixed DDL String + TableGenerator + Sequence
    List<Object> objectResultList = new ArrayList<>();
    for (Table table : tables) transferTableToObjectList(dialect, table, objectResultList);
    List<String> stringResultList = new ArrayList<>();
    List<TableGenerator> tbGeneratorList = new ArrayList<>();
    List<Sequence> sequenceList = new ArrayList<>();
    List<GlobalIdGenerator> globalIdGeneratorList = new ArrayList<>();
    List<FKeyConstraint> fKeyConstraintList = new ArrayList<>();
    for (Object ddl : objectResultList) {
        if (!StrUtils.isEmpty(ddl)) {
            if (ddl instanceof String)
                stringResultList.add((String) ddl);
            else if (ddl instanceof TableGenerator)
                tbGeneratorList.add((TableGenerator) ddl);
            else if (ddl instanceof Sequence)
                sequenceList.add((Sequence) ddl);
            else if (ddl instanceof GlobalIdGenerator)
                globalIdGeneratorList.add((GlobalIdGenerator) ddl);
            else if (ddl instanceof FKeyConstraint)
                fKeyConstraintList.add((FKeyConstraint) ddl);
        }
    }
    buildSequenceDDL(dialect, stringResultList, sequenceList);
    buildTableGeneratorDDL(dialect, stringResultList, tbGeneratorList);
    buildGolbalIDGeneratorDDL(dialect, stringResultList, globalIdGeneratorList);
    buildFKeyConstraintDDL(dialect, stringResultList, fKeyConstraintList);
    return stringResultList.toArray(new String[stringResultList.size()]);
}

Example 14 with Dialect

use of com.github.drinkjava2.jdialects.Dialect in project jDialects by drinkjava2.

the class TableModelUtilsOfDb method db2Model.

/**
 * Convert JDBC connected database structure to TableModels, note: <br/>
 * 1)This method does not close connection <br/>
 * 2)This method does not support sequence, foreign keys, primary keys...,
 * but will improve later.
 */
public static TableModel[] db2Model(Connection con, Dialect dialect) {
    // NOSONAR
    List<String> tableNames = new ArrayList<String>();
    List<TableModel> tableModels = new ArrayList<TableModel>();
    SQLException sqlException = null;
    ResultSet rs = null;
    PreparedStatement pst = null;
    try {
        DatabaseMetaData meta = con.getMetaData();
        if (dialect.isOracleFamily()) {
            // NOSONAR
            pst = con.prepareStatement("SELECT TABLE_NAME FROM USER_TABLES");
            rs = pst.executeQuery();
            while (rs.next()) tableNames.add(rs.getString(TABLE_NAME));
            rs.close();
            pst.close();
        // } else if (dialect.isSQLServerFamily()) {
        // pst = con.prepareStatement("select name from sysobjects where
        // xtype='U'");
        // rs = pst.executeQuery();
        // while (rs.next())
        // tableNames.add(rs.getString(TABLE_NAME));
        // rs.close();
        // pst.close();
        } else {
            rs = meta.getTables(null, null, null, new String[] { "TABLE" });
            while (rs.next()) tableNames.add(rs.getString(TABLE_NAME));
            rs.close();
        }
        for (String dbTableName : tableNames) {
            rs = con.getMetaData().getColumns(null, null, dbTableName, null);
            TableModel oneTable = new TableModel(dbTableName);
            while (rs.next()) {
                // NOSONAR
                String colName = rs.getString("COLUMN_NAME");
                oneTable.column(colName);
                ColumnModel col = oneTable.getColumn(colName);
                int javaSqlType = rs.getInt("DATA_TYPE");
                try {
                    col.setColumnType(TypeUtils.javaSqlTypeToDialectType(javaSqlType));
                } catch (Exception e1) {
                    throw new DialectException("jDialect does not supported java.sql.types value " + javaSqlType, e1);
                }
                col.setLength(rs.getInt("COLUMN_SIZE"));
                col.setNullable(rs.getInt("NULLABLE") > 0);
                col.setPrecision(rs.getInt("DECIMAL_DIGITS"));
                try {
                    if (((Boolean) (true)).equals(rs.getBoolean("IS_AUTOINCREMENT")))
                        col.identityId();
                } catch (Exception e) {
                }
                try {
                    if ("YES".equalsIgnoreCase(rs.getString("IS_AUTOINCREMENT")))
                        col.identityId();
                } catch (Exception e) {
                }
            }
            tableModels.add(oneTable);
            rs.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
        sqlException = e;
    } finally {
        if (pst != null)
            try {
                pst.close();
            } catch (SQLException e1) {
                if (sqlException != null)
                    sqlException.setNextException(e1);
                else
                    sqlException = e1;
            }
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e2) {
            if (sqlException != null)
                sqlException.setNextException(e2);
            else
                sqlException = e2;
        }
    }
    if (sqlException != null)
        throw new DialectException(sqlException);
    return tableModels.toArray(new TableModel[tableModels.size()]);
}

Example 15 with Dialect

use of com.github.drinkjava2.jdialects.Dialect in project jDialects by drinkjava2.

the class DDLCreateUtils method buildSequenceDDL.

private static void buildSequenceDDL(Dialect dialect, List<String> stringList, List<SequenceIdGenerator> sequenceList) {
    Set<SequenceIdGenerator> notRepeatedSequences = new HashSet<SequenceIdGenerator>();
    for (SequenceIdGenerator seq : sequenceList) checkAndInsertToNotRepeatSeq(notRepeatedSequences, seq);
    DDLFeatures features = dialect.ddlFeatures;
    for (SequenceIdGenerator seq : notRepeatedSequences) {
        if (!features.supportBasicOrPooledSequence()) {
            DialectException.throwEX("Dialect \"" + dialect + "\" does not support sequence setting on sequence \"" + seq.getName() + "\"");
        }
        if (features.supportsPooledSequences) {
            // create sequence _SEQ start with 11 increment by 33
            String pooledSequence = StrUtils.replace(features.createPooledSequenceStrings, "_SEQ", seq.getSequenceName());
            pooledSequence = StrUtils.replace(pooledSequence, "11", "" + seq.getInitialValue());
            pooledSequence = StrUtils.replace(pooledSequence, "33", "" + seq.getAllocationSize());
            stringList.add(pooledSequence);
        } else {
            if (seq.getInitialValue() >= 2 || seq.getAllocationSize() >= 2)
                DialectException.throwEX("Dialect \"" + dialect + "\" does not support initialValue and allocationSize setting on sequence \"" + seq.getName() + "\", try set initialValue and allocationSize to 1 to fix");
            // "create sequence _SEQ"
            String simepleSeq = StrUtils.replace(features.createSequenceStrings, "_SEQ", seq.getSequenceName());
            stringList.add(simepleSeq);
        }
    }
}