Examples with SqlBoxContext com.github.drinkjava2.jsqlbox.SqlBoxContext used on opensource projects

Example 11 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class SqlBoxContextUtils method load.

@SuppressWarnings("unchecked")
public static <T> T load(SqlBoxContext ctx, Class<?> entityClass, Object pkeyValue) {
    SqlBoxException.assureNotNull(entityClass, "entityClass can not be null");
    SqlBoxException.assureNotNull(entityClass, "pkey can not be null");
    Map<String, Object> pkValueMap = null;
    if (pkeyValue instanceof Map)
        // NOSONAR
        pkValueMap = (Map<String, Object>) pkeyValue;
    else {
        pkValueMap = new HashMap<String, Object>();
        pkValueMap.put("ooxxooxx", pkeyValue);
    }
    Object entity = null;
    try {
        entity = entityClass.newInstance();
    } catch (Exception e) {
        throw new SqlBoxException(e);
    }
    SqlBox box = SqlBoxUtils.findAndBindSqlBox(ctx, entity);
    TableModel model = box.getTableModel();
    StringBuilder sb = new StringBuilder("select ");
    List<Object> pkParams = new ArrayList<Object>();
    List<ColumnModel> pkeyColumns = new ArrayList<ColumnModel>();
    List<ColumnModel> allColumns = new ArrayList<ColumnModel>();
    List<String> allFieldNames = new ArrayList<String>();
    Map<String, Method> writeMethods = ClassCacheUtils.getClassWriteMethods(entityClass);
    for (String fieldName : writeMethods.keySet()) {
        ColumnModel col = findMatchColumnForJavaField(fieldName, box);
        if (!col.getTransientable()) {
            allColumns.add(col);
            allFieldNames.add(fieldName);
            sb.append(col.getColumnName()).append(", ");
            if (col.getPkey()) {
                pkeyColumns.add(col);
                if (pkValueMap.size() == 1)
                    pkParams.add(pkValueMap.entrySet().iterator().next().getValue());
                else
                    pkParams.add(pkValueMap.get(fieldName));
            }
        }
    }
    if (pkeyColumns.isEmpty())
        throw new SqlBoxException("No primary key set for entityBean");
    // delete the last ", "
    sb.setLength(sb.length() - 2);
    if (pkParams.size() != pkValueMap.size())
        throw new SqlBoxException("Wrong number of primary key parameters: expected " + pkParams.size() + ", was given " + pkValueMap.size());
    sb.append(" from ").append(model.getTableName()).append(" where ");
    for (ColumnModel col : pkeyColumns) sb.append(col.getColumnName()).append("=? and ");
    // delete the last " and "
    sb.setLength(sb.length() - 5);
    try {
        Object[] values = ctx.nQuery(new ArrayHandler(), sb.toString(), pkParams.toArray(new Object[pkParams.size()]));
        for (int i = 0; i < values.length; i++) {
            Method writeMethod = writeMethods.get(allFieldNames.get(i));
            writeMethod.invoke(entity, values[i]);
        }
    } catch (Exception e) {
        throw new SqlBoxException(e);
    }
    return (T) entity;
}

Example 12 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class SqlBoxContextUtils method insert.

/**
 * Insert entityBean into database, and change ID fields to values generated by
 * IdGenerator (identity or sequence or UUID...)
 */
public static void insert(SqlBoxContext ctx, Object entityBean) {
    SqlBox box = SqlBoxUtils.findAndBindSqlBox(ctx, entityBean);
    checkBeanAndBoxExist(entityBean, box);
    TableModel tableModel = box.getTableModel();
    StringBuilder sb = new StringBuilder();
    sb.append("insert into ").append(tableModel.getTableName()).append(" (");
    List<Object> params = new ArrayList<Object>();
    String identityFieldName = null;
    Type identityType = null;
    Map<String, Method> readMethods = ClassCacheUtils.getClassReadMethods(entityBean.getClass());
    for (String fieldName : readMethods.keySet()) {
        ColumnModel col = findMatchColumnForJavaField(fieldName, box);
        if (!col.getTransientable() && col.getInsertable()) {
            if (col.getIdGenerationType() != null || !StrUtils.isEmpty(col.getIdGeneratorName())) {
                IdGenerator idGen = col.getIdGenerator();
                if (GenerationType.IDENTITY.equals(idGen.getGenerationType())) {
                    if (identityFieldName != null)
                        throw new SqlBoxException("More than 1 identity field found for model '" + tableModel.getTableName() + "'");
                    identityFieldName = fieldName;
                } else {
                    sb.append(col.getColumnName()).append(", ");
                    Object id = idGen.getNextID(ctx, ctx.getDialect(), col.getColumnType());
                    params.add(id);
                    ClassCacheUtils.writeValueToBeanField(entityBean, fieldName, id);
                }
            } else {
                Object value = ClassCacheUtils.readValueFromBeanField(entityBean, fieldName);
                sb.append(col.getColumnName()).append(", ");
                params.add(value);
            }
        }
    }
    if (!params.isEmpty())
        // delete the last ", " character
        sb.setLength(sb.length() - 2);
    sb.append(") values(").append(SqlBoxStrUtils.getQuestionsStr(params.size())).append(")");
    int result = ctx.nExecute(sb.toString(), params.toArray(new Object[params.size()]));
    if (ctx.isBatchEnabled())
        return;
    if (result != 1)
        throw new SqlBoxException(result + " row record be inserted.");
    if (identityFieldName != null) {
        // write identity id to Bean field
        Object identityId = IdentityIdGenerator.INSTANCE.getNextID(ctx, ctx.getDialect(), identityType);
        ClassCacheUtils.writeValueToBeanField(entityBean, identityFieldName, identityId);
    }
}

Example 13 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class ActiveRecordDemoTest 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");
    SqlBoxContext ctx = new SqlBoxContext(ds);
    SqlBoxContext.setGlobalSqlBoxContext(ctx);
    String[] ddlArray = ctx.getDialect().toDropAndCreateDDL(UserEntity.class);
    for (String ddl : ddlArray) ctx.quiteExecute(ddl);
    for (int i = 1; i <= 100; i++) {
        UserEntity u = new UserEntity();
        u.setFirstName("Foo" + i);
        u.setLastName("Bar" + i);
        u.setAge(i);
        u.insert();
    }
    Assert.assertEquals(100L, ((Number) ctx.nQueryForObject("select count(*) from users")).longValue());
    List<Map<String, Object>> users = ctx.nQuery(new MapListHandler(), ctx.pagin(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"));
    ds.close();
}

Example 14 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class HelloWorldTest method doText.

@Test
public void doText() {
    HikariDataSource ds = new HikariDataSource();
    ds.setJdbcUrl("jdbc:h2:mem:DBName;MODE=MYSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=0");
    ds.setDriverClassName("org.h2.Driver");
    ds.setUsername("sa");
    ds.setPassword("");
    ds.setMaximumPoolSize(8);
    ds.setConnectionTimeout(2000);
    SqlBoxContext ctx = new SqlBoxContext(ds);
    String[] ddls = ctx.getDialect().toCreateDDL(HelloWorldTest.class);
    for (String ddl : ddls) ctx.nExecute(ddl);
    HelloWorldTest hello = new HelloWorldTest();
    hello.setName("ActiveRecordDemoTest");
    ctx.insert(hello);
    Assert.assertEquals("ActiveRecordDemoTest", ctx.nQueryForString("select name from HelloWorldTest"));
    ds.close();
}

Example 15 with SqlBoxContext

use of com.github.drinkjava2.jsqlbox.SqlBoxContext in project jSqlBox by drinkjava2.

the class UsuageAndSpeedTest method nXxxStyle.

@Test
public void nXxxStyle() {
    SqlBoxContext ctx = new SqlBoxContext(dataSource);
    for (int i = 0; i < REPEAT_TIMES; i++) {
        ctx.nExecute("insert into users (name,address) values(?,?)", "Sam", "Canada");
        ctx.nExecute("update users set name=?, address=?", "Tom", "China");
        Assert.assertEquals(1L, ctx.nQueryForObject("select count(*) from users where name=? and address=?", "Tom", "China"));
        ctx.nExecute("delete from users where name=? or address=?", "Tom", "China");
    }
}