Java Code Examples for org.jooq.impl.DSL#using()

The following examples show how to use org.jooq.impl.DSL#using() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: V10__CreatePasswordCredentials.java    From bouncr with Eclipse Public License 1.0 6 votes vote down vote up 
private void createPasswordCredentials(Connection connection) throws SQLException {
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("password_credentials"))
                .column(field("user_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("password", SQLDataType.VARBINARY(256).nullable(false)))
                .column(field("salt", SQLDataType.VARCHAR(16).nullable(false)))
                .column(field("initial", SQLDataType.BOOLEAN.nullable(false)))
                .column(field("created_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_id")),
                        constraint().foreignKey(field("user_id")).references(table("users"), field("user_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);

    }
}
Example 2
Source File: V3__CreateApplications.java    From bouncr with Eclipse Public License 1.0 6 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("applications"))
                .column(field("application_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("virtual_path", SQLDataType.VARCHAR(50).nullable(false)))
                .column(field("pass_to", SQLDataType.VARCHAR(255).nullable(false)))
                .column(field("top_page", SQLDataType.VARCHAR(255).nullable(false)))
                .column(field("description", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("application_id")),
                        constraint().unique(field("name")),
                        constraint().unique(field("virtual_path"))
                ).getSQL();

        stmt.execute(ddl);
    }

}
Example 3
Source File: JooqMapperTest.java    From SimpleFlatMapper with MIT License 6 votes vote down vote up 
@Test
public void testIgnoreFields() throws Exception {
	Connection conn = DbHelper.objectDb();

	DSLContext dsl = DSL
			.using(new DefaultConfiguration().set(conn)
					.set(SQLDialect.HSQLDB)
					.set(SfmRecordMapperProviderFactory.newInstance().addAlias("id", "noId").ignorePropertyNotFound().newProvider()));
	
	List<DbObject> list = dsl.select()
			.from("TEST_DB_OBJECT").fetchInto(DbObject.class);
	
	assertEquals(2, list.size());

	assertEquals(0, list.get(0).getId());
	list.get(0).setId(1);
	DbHelper.assertDbObjectMapping(list.get(0));
}
Example 4
Source File: V2__CreateGroups.java    From bouncr with Eclipse Public License 1.0 6 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("groups"))
                .column(field("group_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("description", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("group_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
Example 5
Source File: V5__CreatePermissions.java    From bouncr with Eclipse Public License 1.0 6 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("permissions"))
                .column(field("permission_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("description", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("write_protected", SQLDataType.BOOLEAN.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("permission_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);
    }
}
Example 6
Source File: JooqRepository.java    From datashare with GNU Affero General Public License v3.0 6 votes vote down vote up 
@Override
public void create(Document doc) {
    DSLContext ctx = DSL.using(connectionProvider, dialect);
    try {
        ctx.insertInto(DOCUMENT, DOCUMENT.PROJECT_ID,
                DOCUMENT.ID, DOCUMENT.PATH, DOCUMENT.CONTENT, DOCUMENT.STATUS,
                DOCUMENT.CHARSET, DOCUMENT.LANGUAGE, DOCUMENT.CONTENT_TYPE,
                DOCUMENT.EXTRACTION_DATE, DOCUMENT.PARENT_ID, DOCUMENT.ROOT_ID,
                DOCUMENT.EXTRACTION_LEVEL, DOCUMENT.CONTENT_LENGTH, DOCUMENT.METADATA, DOCUMENT.NER_MASK).
                values(doc.getProject().getId(), doc.getId(), doc.getPath().toString(), doc.getContent(), doc.getStatus().code,
                        doc.getContentEncoding().toString(), doc.getLanguage().iso6391Code(), doc.getContentType(),
                        new Timestamp(doc.getExtractionDate().getTime()), doc.getParentDocument(), doc.getRootDocument(),
                        doc.getExtractionLevel(), doc.getContentLength(),
                        MAPPER.writeValueAsString(doc.getMetadata()), doc.getNerMask()).execute();
    } catch (JsonProcessingException e) {
        throw new RuntimeException(e);
    }
}
Example 7
Source File: V25__AddRedirectUriToOidcProvider.java    From bouncr with Eclipse Public License 1.0 5 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try(Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE)) {
        DSLContext create = DSL.using(connection);
        addRedirectUri(create, stmt);
    }
}
Example 8
Source File: V1__CreateUserLicense.java    From bouncr with Eclipse Public License 1.0 5 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try (Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        createUserLicense(create, stmt);
        createLastActivity(create, stmt);
    }
}
Example 9
Source File: HmfGenePanelBuilder.java    From hmftools with GNU General Public License v3.0 5 votes vote down vote up 
@NotNull
private static Result<Record> queryEnsembldb(@NotNull final String database) throws SQLException, IOException {
    // Disable annoying jooq self-ad message
    System.setProperty("org.jooq.no-logo", "true");
    final Connection conn = DriverManager.getConnection(database, DB_USER, "");
    final DSLContext context = DSL.using(conn, SQLDialect.MYSQL);
    final String query = readEnsemblQuery();
    return context.fetch(query);
}
Example 10
Source File: JooqRepository.java    From datashare with GNU Affero General Public License v3.0 5 votes vote down vote up 
@Override
public Set<String> getRecommentationsBy(Project project, List<User> users){
    DSLContext create = DSL.using(connectionProvider,dialect);
    return create.select(DOCUMENT_USER_RECOMMENDATION.DOC_ID).from(DOCUMENT_USER_RECOMMENDATION)
            .where(DOCUMENT_USER_RECOMMENDATION.USER_ID.in(users.stream().map(x -> x.id).collect(toList())))
            .and(DOCUMENT_USER_RECOMMENDATION.PRJ_ID.eq(project.getId()))
            .fetch().getValues(DOCUMENT_USER_RECOMMENDATION.DOC_ID).stream().collect(Collectors.toSet());
}
Example 11
Source File: JooqDBUnitTest.java    From database-rider with Apache License 2.0 5 votes vote down vote up 
@Test
@DataSet(cleanBefore = true, disableConstraints = true)
public void shouldClearDataBaseDisablingConstraints() throws Exception {
    DSLContext dsl = DSL.using(connection);
    int size = dsl.fetchCount(Tables.AUTHOR);
    assertEquals(0, size);
}
Example 12
Source File: SfmRecordUnmapperProvider.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up 
@Deprecated
/**
 * please use SfmRecorMapperProviderFactory.
 */
public SfmRecordUnmapperProvider(
		Function<Type, MapperConfig<JooqFieldKey, org.jooq.Record>> mapperConfigFactory, ReflectionService reflectionService, final Configuration configuration) {
	this(mapperConfigFactory, reflectionService, new DSLContextProvider() {
		@Override
		public DSLContext provide() {
			return DSL.using(configuration);
		}
	});
}
Example 13
Source File: JooqRepository.java    From datashare with GNU Affero General Public License v3.0 5 votes vote down vote up 
@Override
public List<String> getDocuments(Project project, Tag... tags) {
    DSLContext create = DSL.using(connectionProvider, dialect);
    return create.selectDistinct(DOCUMENT_TAG.DOC_ID).from(DOCUMENT_TAG).
            where(DOCUMENT_TAG.LABEL.in(stream(tags).map(t -> t.label).collect(toSet()))).
            and(DOCUMENT_TAG.PRJ_ID.eq(project.getId())).
            fetch().getValues(DOCUMENT_TAG.DOC_ID);
}
Example 14
Source File: ContainerSchemaDefinition.java    From hadoop-ozone with Apache License 2.0 5 votes vote down vote up 
@Override
public void initializeSchema() throws SQLException {
  Connection conn = dataSource.getConnection();
  dslContext = DSL.using(conn);
  if (!TABLE_EXISTS_CHECK.test(conn, CONTAINER_HISTORY_TABLE_NAME)) {
    createContainerHistoryTable();
  }
  if (!TABLE_EXISTS_CHECK.test(conn, UNHEALTHY_CONTAINERS_TABLE_NAME)) {
    createUnhealthyContainersTable();
  }
}
Example 15
Source File: V12__CreateUserActions.java    From bouncr with Eclipse Public License 1.0 4 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    DSLContext create = DSL.using(connection);
    try(Statement stmt = connection.createStatement()) {
        String ddl = create.createTable(table("actions"))
                .column(field("action_id", SQLDataType.BIGINT.identity(true)))
                .column(field("name", SQLDataType.VARCHAR(100).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("action_id")),
                        constraint().unique(field("name"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("user_actions"))
                .column(field("user_action_id", SQLDataType.BIGINT.identity(true)))
                .column(field("action_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("actor", SQLDataType.VARCHAR(100).nullable(false)))
                .column(field("actor_ip", SQLDataType.VARCHAR(50).nullable(false)))
                .column(field("options", SQLDataType.CLOB))
                .column(field("created_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("user_action_id"))
                ).getSQL();
        stmt.execute(ddl);
    }

    try (PreparedStatement stmt = connection.prepareStatement(create
            .insertInto(table("actions"))
            .columns(
                    field("action_id"),
                    field("name")
            )
            .values("?", "?")
            .getSQL())) {
        for (ActionType actionType : ActionType.values()) {
            stmt.setLong(1, actionType.getId());
            stmt.setString(2, actionType.getName());
            stmt.executeUpdate();
        }
        connection.commit();
    }
}
Example 16
Source File: PostgresPersistenceManager.java    From FROST-Server with GNU Lesser General Public License v3.0 4 votes vote down vote up 
public DSLContext getDslContext() {
    if (dslContext == null) {
        dslContext = DSL.using(connectionProvider.get(), SQLDialect.POSTGRES);
    }
    return dslContext;
}
Example 17
Source File: CMSCrawler.java    From oneops with Apache License 2.0 4 votes vote down vote up 
private void syncCloudVariables(long cloudId, String cloudName, String cloudNS, Connection conn) {
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    Integer valueForRelationId = create.select().from(MD_RELATIONS)
            .where(MD_RELATIONS.RELATION_NAME.eq("account.ValueFor"))
            .fetchAny().getValue(MD_RELATIONS.RELATION_ID);

    int varClassId = create.select().from(MD_CLASSES)
            .where(MD_CLASSES.CLASS_NAME.eq("account.Cloudvar"))
            .fetchAny().getValue(MD_CLASSES.CLASS_ID);

    int valueAttributeId = create.select().from(MD_CLASSES)
            .join(MD_CLASS_ATTRIBUTES).on(MD_CLASSES.CLASS_ID.eq(MD_CLASS_ATTRIBUTES.CLASS_ID))
            .where(MD_CLASS_ATTRIBUTES.ATTRIBUTE_NAME.eq("value")
                    .and(MD_CLASSES.CLASS_NAME.eq("base.Cloudvar")))
            .fetchAny().getValue(MD_CLASS_ATTRIBUTES.ATTRIBUTE_ID);

    int secureAttributeId = create.select().from(MD_CLASSES).join(MD_CLASS_ATTRIBUTES).on(MD_CLASSES.CLASS_ID.eq(MD_CLASS_ATTRIBUTES.CLASS_ID))
            .where(MD_CLASS_ATTRIBUTES.ATTRIBUTE_NAME.eq("secure")
                    .and(MD_CLASSES.CLASS_NAME.eq("base.Cloudvar")))
            .fetchAny().getValue(MD_CLASS_ATTRIBUTES.ATTRIBUTE_ID);

    String varNS = cloudNS + "/" + cloudName;

    String value = "M";

    if (cloudName.toLowerCase().contains("prod")
            || cloudName.toLowerCase().contains("azure")) {
        value = "M";
    }

    long varNsId = create.select().from(NS_NAMESPACES)
            .where(NS_NAMESPACES.NS_PATH.eq(varNS))
            .fetchAny().getValue(NS_NAMESPACES.NS_ID);

    //insert variable ci
    long nextID = create.nextval(Sequences.CM_PK_SEQ);
    long newVarId = nextID;
    create.insertInto(CM_CI, CM_CI.CI_ID, CM_CI.NS_ID, CM_CI.CI_NAME, CM_CI.CLASS_ID, CM_CI.CI_GOID, CM_CI.CI_STATE_ID)
            .values(nextID, varNsId, "size", varClassId, varNsId + "-" + varClassId + "-" + nextID, 100).returning(CM_CI.CI_ID)
            .fetchOne().getValue(CM_CI.CI_ID);

    nextID = create.nextval(Sequences.CM_PK_SEQ);
    //insert attributes
    create.insertInto(CM_CI_ATTRIBUTES, CM_CI_ATTRIBUTES.CI_ATTRIBUTE_ID, CM_CI_ATTRIBUTES.CI_ID, CM_CI_ATTRIBUTES.ATTRIBUTE_ID, CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE, CM_CI_ATTRIBUTES.DJ_ATTRIBUTE_VALUE)
            .values(nextID, newVarId, valueAttributeId, value, value).execute();

    nextID = create.nextval(Sequences.CM_PK_SEQ);
    create.insertInto(CM_CI_ATTRIBUTES, CM_CI_ATTRIBUTES.CI_ATTRIBUTE_ID, CM_CI_ATTRIBUTES.CI_ID, CM_CI_ATTRIBUTES.ATTRIBUTE_ID, CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE, CM_CI_ATTRIBUTES.DJ_ATTRIBUTE_VALUE)
            .values(nextID, newVarId, secureAttributeId, "false", "false").execute();

    String relationGoId = newVarId + "-" + valueForRelationId + "-" + cloudId;
    nextID = create.nextval(Sequences.CM_PK_SEQ);
    create.insertInto(CM_CI_RELATIONS, CM_CI_RELATIONS.CI_RELATION_ID, CM_CI_RELATIONS.NS_ID, CM_CI_RELATIONS.RELATION_ID,
            CM_CI_RELATIONS.FROM_CI_ID, CM_CI_RELATIONS.TO_CI_ID, CM_CI_RELATIONS.RELATION_GOID, CM_CI_RELATIONS.CI_STATE_ID)
            .values(nextID, varNsId, valueForRelationId, newVarId, cloudId, relationGoId, 100).execute();

}
Example 18
Source File: CMSCrawler.java    From oneops with Apache License 2.0 4 votes vote down vote up 
public Map<String, Organization> populateOrganizations(Connection conn) {

    log.info("Populating organizations cache");
    DSLContext create = DSL.using(conn, SQLDialect.POSTGRES);
    Map<String, Organization> organizationsMap = new HashMap<>();

    Result<Record4<Long, String, Integer, String>> OrganizationsWithAttributesRecords = create
        .select(CM_CI.CI_ID, CM_CI.CI_NAME, CM_CI_ATTRIBUTES.ATTRIBUTE_ID,
            CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE)
        .from(CM_CI).join(CM_CI_ATTRIBUTES).on(CM_CI.CI_ID.eq(CM_CI_ATTRIBUTES.CI_ID))
        .where(CM_CI.CLASS_ID.in(create.select(MD_CLASSES.CLASS_ID).from(MD_CLASSES)
            .where(MD_CLASSES.CLASS_NAME.eq("account.Organization"))))
        .fetch();

    List<Long> OrganizationIds = OrganizationsWithAttributesRecords.getValues(CM_CI.CI_ID);
    log.debug("OrganizationIds: " + OrganizationIds.toString());

    Set<Long> setOfOrganizationIds = new HashSet<Long>(OrganizationIds);
    log.debug("setOfOrganizationIds <" + setOfOrganizationIds.size() + "> " + setOfOrganizationIds);

    List<String> OrganizationNames = OrganizationsWithAttributesRecords.getValues(CM_CI.CI_NAME);
    log.debug("OrganizationNames: " + OrganizationNames.toString());

    Set<String> setOfOrganizationNames = new HashSet<String>(OrganizationNames);
    log.debug("setOfOrganizationNames: <" + setOfOrganizationNames.size() + "> "
        + setOfOrganizationNames);

    int description_AttribID =
        this.baseOrganizationMDClassAttributes_NameIdMapCache.get("description");
    int full_name_AttribID = this.baseOrganizationMDClassAttributes_NameIdMapCache.get("full_name");
    int owner_AttribID = this.baseOrganizationMDClassAttributes_NameIdMapCache.get("owner");
    int tags_AttribID = this.baseOrganizationMDClassAttributes_NameIdMapCache.get("tags");

    for (Record4<Long, String, Integer, String> OrganizationsWithAttributesRecord : OrganizationsWithAttributesRecords) {
      long organizationId = OrganizationsWithAttributesRecord.getValue(CM_CI.CI_ID);

      String organizationName = OrganizationsWithAttributesRecord.getValue(CM_CI.CI_NAME);
      Organization organization = organizationsMap.get(organizationName);
      log.debug("organizationId: " + organizationId);
      if (organization == null) {
        organization = new Organization();
        organizationsMap.put(organizationName, organization);

      }

      int attributeID = OrganizationsWithAttributesRecord.getValue(CM_CI_ATTRIBUTES.ATTRIBUTE_ID);

      if (attributeID == description_AttribID) {
        organization.setDescription(
            OrganizationsWithAttributesRecord.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));

        continue;
      } else if (attributeID == full_name_AttribID) {
        organization.setFull_name(
            OrganizationsWithAttributesRecord.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));

        continue;

      } else if (attributeID == owner_AttribID) {
        organization.setOwner(
            OrganizationsWithAttributesRecord.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE));

        continue;
      } else if (attributeID == tags_AttribID) {
        @SuppressWarnings("unchecked") 
        Map<String, String> tags = gson.fromJson(
            OrganizationsWithAttributesRecord.getValue(CM_CI_ATTRIBUTES.DF_ATTRIBUTE_VALUE),
            Map.class);
        organization.setTags(tags);
        
        continue;
      }


    }

    log.info("Caching for Org Data Complete");
    return organizationsMap;
  }
Example 19
Source File: GroupDAO.java    From keywhiz with Apache License 2.0 4 votes vote down vote up 
@Override public GroupDAO using(Configuration configuration) {
  DSLContext dslContext = DSL.using(checkNotNull(configuration));
  return new GroupDAO(dslContext, groupMapper, mapper);
}
Example 20
Source File: V17__CreateInvitations.java    From bouncr with Eclipse Public License 1.0 4 votes vote down vote up 
@Override
public void migrate(Context context) throws Exception {
    Connection connection = context.getConnection();
    try(Statement stmt = connection.createStatement()) {
        DSLContext create = DSL.using(connection);
        String ddl = create.createTable(table("invitations"))
                .column(field("invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("email", SQLDataType.VARCHAR(100)))
                .column(field("code", SQLDataType.VARCHAR(8)))
                .column(field("invited_at", SQLDataType.TIMESTAMP.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("invitation_id")),
                        constraint().unique(field("code"))
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("group_invitations"))
                .column(field("group_invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("invitation_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("group_id", SQLDataType.BIGINT.nullable(false)))
                .constraints(
                        constraint().primaryKey(field("group_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")).onDeleteCascade(),
                        constraint().foreignKey(field("group_id")).references(table("groups"), field("group_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);

        ddl = create.createTable(table("oidc_invitations"))
                .column(field("oidc_invitation_id", SQLDataType.BIGINT.identity(true)))
                .column(field("invitation_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_provider_id", SQLDataType.BIGINT.nullable(false)))
                .column(field("oidc_sub", SQLDataType.VARCHAR(2048).nullable(false)))
                .constraints(
                        constraint().primaryKey(field("oidc_invitation_id")),
                        constraint().foreignKey(field("invitation_id")).references(table("invitations"), field("invitation_id")).onDeleteCascade(),
                        constraint().foreignKey(field("oidc_provider_id")).references(table("oidc_providers"), field("oidc_provider_id")).onDeleteCascade()
                ).getSQL();
        stmt.execute(ddl);

    }
}