import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Test
public void shouldWorkInMemory() throws Exception {
	
	HsqlDatabaseDescriptor memoryDb = new HsqlDatabaseDescriptor( "mem", new MemoryStorage(), new HsqlCompatibility(), "usr", "pwd" );
	
	HsqlDbms dbms = new HsqlDbms( memoryDb );		
	dbms.start();
	
	DBI dbi = new DBI(memoryDb);
	Handle h = dbi.open();
	h.execute("CREATE TABLE PEOPLE(NAME VARCHAR(64))");
	h.execute("INSERT INTO PEOPLE(NAME) VALUES('John')");
	int people = h.createQuery("SELECT COUNT(*) FROM PEOPLE").map(IntegerMapper.FIRST).first();
	h.close();
	
	assertThat( people, equalTo(1));
	
	dbms.stop();
	
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
public Integer getTotalChildCount(Long workId) {
    Integer numChildren = new Integer(0);
    try (Handle h = graph.dbi().open()) {
        numChildren = h.createQuery(
                "SELECT COUNT(v_out) " +
                "FROM node v, flatedge e, work p " +
                "WHERE e.v_in = :workId " +
                " AND e.v_out = v.id " +
                " AND e.label = 'isPartOf' " +
                " AND p.id = v.id " +
                " AND p.type IN ('Work', 'Page', 'EADWork'); ")
                .bind("workId", workId)
                .map(IntegerMapper.FIRST).first();
    }
    return numChildren;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsKey(Object key) {
    try (Handle handle = dbi.open()){
        return handle
                .createQuery(String.format(
                        "select count(*) %s from %s where %s = ?", keyColumnName, tableName, keyColumnName))
                .bind(0, key)
                .map(IntegerMapper.FIRST)
                .first() > 0;
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsValue(Object value) {
    try (Handle handle = dbi.open()) {
        return handle
                .createQuery(String.format(
                        "select count(*) %s from %s where %s = ?", valueColumnName, tableName, valueColumnName))
                .bind(0, serializer.objectToBytes(value))
                .map(IntegerMapper.FIRST)
                .first() > 0;
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public int size() {
    try (Handle handle = dbi.open()) {
        return handle
                .createQuery(String.format("select count(*) from %s", tableName))
                .map(IntegerMapper.FIRST)
                .first();
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Test
public void putStoresExtraColumns() {
    ExtraColumnsJdbcMap extraMap = new ExtraColumnsJdbcMap("map", "key", "value", serializer);
    SerializableThing thing = new SerializableThing(42);
    extraMap.put("thing", thing);
    int num = handle.createQuery("select num from map where key = ?")
            .bind(0, "thing")
            .map(IntegerMapper.FIRST).first();
    assertThat(num, equalTo(42));
    String str = handle.createQuery("select str from map where key = ?")
            .bind(0, "thing")
            .map(StringMapper.FIRST).first();
    assertThat(str, equalTo("value42"));
    assertThat(extraMap.get("thing"), equalTo(thing));
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public int size() {
    final String fieldsToCount = Joiner.on(", ").join(selectionMapFactory.keyFields());
    return handle.createQuery(String.format("SELECT COUNT(DISTINCT (%2$s)) FROM %1$s WHERE %3$s", tableName, fieldsToCount, selectionMap.asSql()))
            .bindFromMap(selectionMap.asMap())
            .map(IntegerMapper.FIRST)
            .first();
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsKey(Object key) {
    final SelectionMap valueMap = selectionMapFactory.get(key);
    return handle.createQuery(String.format("SELECT 1 FROM %1$s WHERE %2$s AND %3$s", tableName, valueMap.asSql(), selectionMap.asSql()))
            .bindFromMap(selectionMap.asMap())
            .bindFromMap(valueMap.asMap())
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsValue(Object value) {
    return handle.createQuery(String.format("SELECT 1 FROM %1$s WHERE value_field = :value_field AND %2$s", tableName, selectionMap.asSql()))
            .bindFromMap(selectionMap.asMap())
            .bind("value_field", value)
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public int size() {
    final String fieldsToCount = Joiner.on(", ").join(selectionMapFactory.keyFields());
    return handle.createQuery(String.format("SELECT COUNT(%2$s) FROM %1$s WHERE %3$s", tableName, fieldsToCount, selectionMap.asSql()))
            .bindFromMap(selectionMap.asMap())
            .map(IntegerMapper.FIRST)
            .first();
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
@SuppressWarnings("unchecked")
public boolean contains(Object value) {
    final SelectionMap valueMap = selectionMapFactory.get(value);
    return handle.createQuery(String.format("SELECT 1 FROM %1$s WHERE %2$s AND %3$s", tableName, valueMap.asSql(), selectionMap.asSql()))
            .bindFromMap(selectionMap.asMap())
            .bindFromMap(valueMap.asMap())
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean contains(Object row, Object column) {
    return handle.createQuery(String.format("SELECT 1 FROM %s WHERE row_field = :row_field AND column_field = :column_field", tableName))
            .bind("row_field", row)
            .bind("column_field", column)
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsRow(Object row) {
    return handle.createQuery(String.format("SELECT 1 FROM %s WHERE row_field = :row_field", tableName))
            .bind("row_field", row)
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsColumn(Object column) {
    return handle.createQuery(String.format("SELECT 1 FROM %s WHERE column_field = :column_field", tableName))
            .bind("column_field", column)
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@Override
public boolean containsValue(Object value) {
    return handle.createQuery(String.format("SELECT 1 FROM %s WHERE value_field = :value_field", tableName))
            .bind("value_field", value)
            .map(IntegerMapper.FIRST)
            .first() != null;
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
public void updateUserInfo(int id, String name) {
    try (Handle handle = dbi.open()) {
        handle.createStatement("UPDATE web_user SET name = :name WHERE id = :id")
                .bind("id", id)
                .bind("name", name)
                .executeAndReturnGeneratedKeys(IntegerMapper.FIRST).first();
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
public void giveAccessToExistingUser(int projectId, int userId, String email, boolean readPermission, boolean writePermission, boolean masterPermission, UIFeatures activeUiFeatures) {
    try (Handle handle = dbi.open()) {
        if (!hasMasterAccess(handle, projectId, userId)) {
            throw new RakamException("You do not have master key permission", UNAUTHORIZED);
        }

        Integer newUserId = handle.createQuery("SELECT id FROM web_user WHERE lower(email) = lower(:email)").bind("email", email)
                .map(IntegerMapper.FIRST).first();

        if (newUserId == null) {
            throw new RakamException(NOT_FOUND);
        }

        int exists = handle.createStatement("UPDATE web_user_api_key_permission SET " +
                "read_permission = :readPermission, write_permission = :writePermission, master_permission = :masterPermission, active_ui_features = :activeUiFeatures " +
                "WHERE user_id = :newUserId")
                .bind("newUserId", newUserId)
                .bind("readPermission", readPermission)
                .bind("writePermission", writePermission)
                .bind("masterPermission", masterPermission)
                .bind("activeUiFeatures", JsonHelper.encode(activeUiFeatures))
                .bind("project", projectId).execute();

        if (exists == 0) {
            throw new RakamException(NOT_FOUND);
        }
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@JsonRequest
@ApiOperation(value = "Create dashboard")
@Path("/create")
@ProtectEndpoint(writeOperation = true)
public Dashboard create(
        @Named("user_id") Project project,
        @ApiParam("name") String name,
        @ApiParam(value = "shared_everyone", required = false) Boolean sharedEveryone,
        @ApiParam(value = "options", required = false) Map<String, Object> options,
        @ApiParam(value = "refresh_duration", required = false) Duration refreshDuration) {
    try (Handle handle = dbi.open()) {
        int id;
        try {
            id = handle.createQuery("INSERT INTO dashboard (project_id, name, user_id, options, refresh_interval) VALUES (:project, :name, :user, :options, :refreshInterval) RETURNING id")
                    .bind("project", project.project)
                    .bind("user", project.userId)
                    .bind("options", JsonHelper.encode(options))
                    .bind("refreshInterval", refreshDuration != null ? refreshDuration.getSeconds() : null)
                    .bind("name", name).map(IntegerMapper.FIRST).first();
        } catch (Exception e) {
            if (handle.createQuery("SELECT 1 FROM dashboard WHERE (project_id, name) = (:project, :name)")
                    .bind("project", project.project)
                    .bind("name", name).first() != null) {
                throw new AlreadyExistsException("Dashboard", BAD_REQUEST);
            }

            throw e;
        }
        return new Dashboard(id, project.userId, name, null, options, TRUE.equals(sharedEveryone));
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@JsonRequest
@ApiOperation(value = "Get dashboard users")
@Path("/users/set")
public SuccessMessage setUsers(@Named("user_id") Project project, @ApiParam("dashboard") int id, @ApiParam("user_ids") int[] users) {
    TransactionHandler transactionHandler = dbi.getTransactionHandler();
    try (Handle handle = dbi.open()) {
        transactionHandler.begin(handle);

        Integer userId = handle.createQuery("SELECT user_id FROM dashboard where id = :id")
                .bind("id", id).map(IntegerMapper.FIRST).first();
        if (project.userId != userId) {
            throw new RakamException(FORBIDDEN);
        }

        handle.createStatement("DELETE FROM dashboard_permission WHERE dashboard = :dashboard")
                .bind("dashboard", id).execute();

        handle.createStatement("UPDATE dashboard SET shared_everyone = :sharedEveryone" +
                " WHERE id = :id AND project_id = :project")
                .bind("id", id)
                .bind("sharedEveryone", false)
                .bind("project", project.project)
                .execute();

        for (int user : users) {
            handle.createStatement("INSERT INTO dashboard_permission (dashboard, user_id) VALUES (:dashboard, :user_id) ")
                    .bind("dashboard", id)
                    .bind("user_id", user)
                    .execute();
        }

        transactionHandler.commit(handle);
        return SuccessMessage.success();
    }
}
 

import org.skife.jdbi.v2.util.IntegerMapper; //导入依赖的package包/类
@JsonRequest
@ApiOperation(value = "Create dashboard")
@Path("/create")
@ProtectEndpoint(writeOperation = true)
public ScheduledEmailTask create(
        @Named("user_id") UIPermissionParameterProvider.Project project,
        @ApiParam("name") String name,
        @ApiParam("date_interval") String date_interval,
        @ApiParam("hour_of_day") int hour_of_day,
        @ApiParam("type") TaskType type,
        @ApiParam("type_id") int type_id,
        @ApiParam("emails") List<String> emails) {
    try (Handle handle = dbi.open()) {
        int id = handle.createQuery("INSERT INTO scheduled_email (project_id, user_id, date_interval, hour_of_day, name, type, type_id, emails, enabled) " +
                "VALUES (:project, :user_id, :date_interval, :hour_of_day, :name, :type, :type_id, :emails, true) RETURNING id")
                .bind("project", project.project)
                .bind("user_id", project.userId)
                .bind("date_interval", date_interval)
                .bind("hour_of_day", hour_of_day)
                .bind("name", name)
                .bind("type", type)
                .bind("type_id", type_id)
                .bind("emails", handle.getConnection().createArrayOf("text", emails.toArray()))
                .map(IntegerMapper.FIRST).first();

        return new ScheduledEmailTask(
                id, name, date_interval,
                hour_of_day, type, type_id, emails,
                true, null, project.userId, project.project);
    } catch (SQLException e) {
        throw Throwables.propagate(e);
    }
}