Beispiel #1
0
  @Test
  public void testExplicitTransactions() {
    boolean rollback = false;

    TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
    try {

      // This is a "bug". The same book is created twice, resulting in a
      // constraint violation exception
      for (int i = 0; i < 2; i++)
        dsl.insertInto(BOOK)
            .set(BOOK.ID, 5)
            .set(BOOK.AUTHOR_ID, 1)
            .set(BOOK.TITLE, "Book 5")
            .execute();

      Assert.fail();
    }

    // Upon the constraint violation, we explicitly roll back the transaction.
    catch (DataAccessException e) {
      txMgr.rollback(tx);
      rollback = true;
    }

    assertEquals(4, dsl.fetchCount(BOOK));
    assertTrue(rollback);
  }
Beispiel #2
0
  @SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE")
  public static void execute(
      Configuration jooqConf,
      IndexStorage.CollectionSchema colSchema,
      @Nonnull DatabaseInterface databaseInterface) {

    ConnectionProvider provider = jooqConf.connectionProvider();
    Connection connection = provider.acquire();
    Statement st = null;
    try {
      st = connection.createStatement();
      st.executeUpdate(databaseInterface.dropSchemaStatement(colSchema.getName()));

      DSLContext dsl = DSL.using(jooqConf);
      int deleted =
          dsl.deleteFrom(CollectionsTable.COLLECTIONS)
              .where(CollectionsTable.COLLECTIONS.NAME.eq(colSchema.getCollection()))
              .execute();
      assert deleted == 1;
    } catch (SQLException ex) {
      throw new ToroImplementationException(ex);
    } finally {
      AutoCloser.close(st);
    }
  }
 @Inject
 public JooqBasedDatasourceValueStoreBuilder(
     @Named("mev-datasource") DataSource dataSource, String id, boolean isTemporary)
     throws SQLException {
   this.id = id;
   this.isTemporary = isTemporary;
   context = using(dataSource.getConnection());
   table = tableByName(id);
   row = fieldByName(String.class, ROW_FIELD_NAME);
   column = fieldByName(String.class, COLUMN_FIELD_NAME);
   value = fieldByName(Double.class, VALUE_FIELD_NAME);
   context
       .query(
           "CREATE "
               + (isTemporary ? "TEMPORARY" : "")
               + " TABLE IF NOT EXISTS {0}({1} VARCHAR(255), {2} VARCHAR(255), {3} DOUBLE)",
           table,
           row,
           column,
           value)
       .execute();
   context
       .query("CREATE UNIQUE INDEX IF NOT EXISTS {0} ON {0}({1}, {2})", table, row, column)
       .execute();
   if (log.isDebugEnabled())
     log.debug("Created " + table + " with " + row + ", " + column + ", " + value);
 }
Beispiel #4
0
  private void testPostgresEnumArrayCRUD0(DSLContext create) throws Exception {
    jOOQAbstractTest.reset = false;

    TArrays a = T_ARRAYS;

    assertEquals(
        4,
        create
            .insertInto(a, a.ID, a.ENUM_ARRAY)
            .values(11, null)
            .values(12, new UCountry[0])
            .values(13, new UCountry[] {null})
            .values(14, new UCountry[] {UCountry.Brazil})
            .execute());

    List<UCountry[]> countries =
        create.select(a.ENUM_ARRAY).from(a).where(a.ID.gt(10)).orderBy(a.ID).fetch(a.ENUM_ARRAY);

    assertNull(countries.get(0));
    assertEquals(0, countries.get(1).length);
    assertEquals(1, countries.get(2).length);
    assertNull(countries.get(2)[0]);
    assertEquals(1, countries.get(3).length);
    assertEquals(UCountry.Brazil, countries.get(3)[0]);
  }
Beispiel #5
0
  @Test
  public void testjOOQTransactionsSimple() {
    boolean rollback = false;

    try {
      dsl.transaction(
          c -> {

            // This is a "bug". The same book is created twice, resulting in a
            // constraint violation exception
            for (int i = 0; i < 2; i++)
              dsl.insertInto(BOOK)
                  .set(BOOK.ID, 5)
                  .set(BOOK.AUTHOR_ID, 1)
                  .set(BOOK.TITLE, "Book 5")
                  .execute();

            Assert.fail();
          });
    }

    // Upon the constraint violation, the transaction must already have been rolled back
    catch (DataAccessException e) {
      rollback = true;
    }

    assertEquals(4, dsl.fetchCount(BOOK));
    assertTrue(rollback);
  }
 public static Msg<String> updateAdressLibrary(Context context, Addresslibrary al) {
   DSLContext create = context.getJc().getDefaultClient().getContext();
   Msg<String> msg = new Msg<String>();
   try {
     create
         .update(ADDRESSLIBRARY)
         .set(ADDRESSLIBRARY.NAME, al.getName())
         .set(ADDRESSLIBRARY.PROVINCIAL, al.getProvincial())
         .set(ADDRESSLIBRARY.CITY, al.getCity())
         .set(ADDRESSLIBRARY.DISTRICT, al.getDistrict())
         .set(ADDRESSLIBRARY.TEL, al.getTel())
         .set(ADDRESSLIBRARY.ADDRESS, al.getAddress())
         .set(ADDRESSLIBRARY.TYPE, al.getType())
         .set(ADDRESSLIBRARY.USERID, al.getUserid())
         .set(ADDRESSLIBRARY.ISDEFAULT, al.getIsdefault())
         .where(ADDRESSLIBRARY.ADDRESSLIBRARYID.eq(al.getAddresslibraryid()))
         .execute();
   } catch (Exception e) {
     e.printStackTrace();
     msg.setResult(false);
     return msg;
   }
   msg.setResult(true);
   return msg;
 }
  @Override
  public void addChargePoint(List<String> chargeBoxIdList) {
    BatchBindStep batch = ctx.batch(ctx.insertInto(CHARGE_BOX).set(CHARGE_BOX.CHARGE_BOX_ID, ""));

    for (String s : chargeBoxIdList) {
      batch.bind(s);
    }

    batch.execute();
  }
  @Test
  public void testInsertByMultipleValues() {
    DSLContext create = using(conn, SQLDialect.MYSQL);

    create
        .insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
        .values("li", "san")
        .values("wang", "san")
        .execute();
  }
Beispiel #9
0
 public int executeUpdate(DSLContext dsl, String statement, Context context) {
   Connection c = dsl.configuration().connectionProvider().acquire();
   try (PreparedStatement ps = c.prepareStatement(statement)) {
     return ps.executeUpdate();
   } catch (SQLException ex) {
     throw errorHandler.handleException(context, ex);
   } finally {
     dsl.configuration().connectionProvider().release(c);
   }
 }
  @Test
  public void testInsertBySet() {
    DSLContext create = using(conn, SQLDialect.MYSQL);

    create
        .insertInto(AUTHOR)
        .set(AUTHOR.FIRST_NAME, "Hermann")
        .set(AUTHOR.LAST_NAME, "Hesse")
        .newRecord()
        .set(AUTHOR.FIRST_NAME, "Alfred")
        .set(AUTHOR.LAST_NAME, "Döblin")
        .execute();
  }
Beispiel #11
0
  private final int[] executeStatic() {
    List<Query> queries = new ArrayList<Query>();

    for (Object[] bindValues : allBindValues) {
      for (int i = 0; i < bindValues.length; i++) {
        query.bind(i + 1, bindValues[i]);
      }

      queries.add(create.query(query.getSQL(INLINED)));
    }

    return create.batch(queries).execute();
  }
Beispiel #12
0
 public Result<Record> executeStatementWithResult(
     DSLContext dsl, String statement, Context context, SetupPreparedStatement statementSetup) {
   Connection c = dsl.configuration().connectionProvider().acquire();
   try (PreparedStatement ps = c.prepareStatement(statement)) {
     statementSetup.accept(ps);
     try (ResultSet resultSet = ps.executeQuery()) {
       return dsl.fetch(resultSet);
     }
   } catch (SQLException ex) {
     throw errorHandler.handleException(context, ex);
   } finally {
     dsl.configuration().connectionProvider().release(c);
   }
 }
  public static void runMigration(DSLContext jooq, String fileName) {
    if (jooq.fetchExists(MIGRATION, MIGRATION.FILE_NAME.eq(fileName))) {
      return;
    }

    try {
      runSqlFile(jooq, fileName);

      Migration migration = new Migration(fileName);
      jooq.newRecord(MIGRATION, migration).insert();
    } catch (IOException e) {
      throw new RuntimeException("Error executing sql file", e);
    }
  }
 @Override
 public void subscribe(int userId, int newsSourceId) {
   dsl.insertInto(SUBSCRIPTIONS, SUBSCRIPTIONS.NEWS_SOURCE_ID, SUBSCRIPTIONS.USER_ID)
       .values(newsSourceId, userId)
       .onDuplicateKeyIgnore()
       .execute();
 }
 public List<Customer> findAll() {
   return dslContext
       .select()
       .from(CUSTOMERS)
       .orderBy(CUSTOMERS.FIRST_NAME.asc())
       .fetchInto(Customer.class);
 }
  public String getJsonWebToken(String username) {
    AdminUser a = AdminUser.ADMIN_USER;
    AuthCredential ac = AuthCredential.AUTH_CREDENTIAL;
    Company c = Company.COMPANY;
    Record3<Integer, Integer, String> rs =
        context
            .select(a.ID, c.ID, ac.JWT_TOKEN)
            .from(a)
            .join(c)
            .on(a.COMPANY_ID.equal(c.ID))
            .join(ac)
            .on(a.USERNAME.equal(a.USERNAME))
            .where(a.USERNAME.equal(username))
            .fetchOne();
    if (rs == null) {
      return null;
    }

    Map<String, Object> claims = new HashMap<>();
    claims.put("username", username);
    claims.put("userId", rs.value1());
    claims.put("companyId", rs.value2());
    Map<String, Object> headers = new HashMap<>();
    AuthSigningKeyResolver.KeyDTO key = signingKeyResolver.getRandomKey();
    headers.put("kid", key.getKeyId());
    return Jwts.builder()
        .setSubject(username)
        .setIssuedAt(new Date())
        .setHeader(headers)
        .setClaims(claims)
        .signWith(key.getAlgo(), key.getKey())
        .compact();
  }
 @Override
 public Map<String, Integer> getChargeBoxIdPkPair(List<String> chargeBoxIdList) {
   return ctx.select(CHARGE_BOX.CHARGE_BOX_ID, CHARGE_BOX.CHARGE_BOX_PK)
       .from(CHARGE_BOX)
       .where(CHARGE_BOX.CHARGE_BOX_ID.in(chargeBoxIdList))
       .fetchMap(CHARGE_BOX.CHARGE_BOX_ID, CHARGE_BOX.CHARGE_BOX_PK);
 }
  /**
   * Difference from getInternalCSV: Joins with CHARGE_BOX and OCPP_TAG tables, selects
   * CHARGE_BOX_PK and OCPP_TAG_PK additionally
   */
  @SuppressWarnings("unchecked")
  private SelectQuery<
          Record10<
              Integer,
              String,
              Integer,
              String,
              DateTime,
              String,
              DateTime,
              String,
              Integer,
              Integer>>
      getInternal(TransactionQueryForm form) {

    SelectQuery selectQuery = ctx.selectQuery();
    selectQuery.addFrom(TRANSACTION);
    selectQuery.addJoin(CONNECTOR, TRANSACTION.CONNECTOR_PK.eq(CONNECTOR.CONNECTOR_PK));
    selectQuery.addJoin(CHARGE_BOX, CHARGE_BOX.CHARGE_BOX_ID.eq(CONNECTOR.CHARGE_BOX_ID));
    selectQuery.addJoin(OCPP_TAG, OCPP_TAG.ID_TAG.eq(TRANSACTION.ID_TAG));
    selectQuery.addSelect(
        TRANSACTION.TRANSACTION_PK,
        CONNECTOR.CHARGE_BOX_ID,
        CONNECTOR.CONNECTOR_ID,
        TRANSACTION.ID_TAG,
        TRANSACTION.START_TIMESTAMP,
        TRANSACTION.START_VALUE,
        TRANSACTION.STOP_TIMESTAMP,
        TRANSACTION.STOP_VALUE,
        CHARGE_BOX.CHARGE_BOX_PK,
        OCPP_TAG.OCPP_TAG_PK);

    return addConditions(selectQuery, form);
  }
 @Override
 public BookModel selectBook(int id) {
   return create
       .select(
           BOOK.ID_BOOK,
           BOOK.TITLE,
           BOOK.SHORT_DESCRIPTION,
           BOOK.DATE_PUBLISH,
           PostgresDSL.arrayAgg(
                   concat(
                       AUTHOR.ID_AUTHOR,
                       val(","),
                       AUTHOR.FIRST_NAME,
                       val(","),
                       AUTHOR.SECOND_NAME))
               .as("authors"))
       .from(AUTHOR)
       .join(AUTHOR_BOOK)
       .on(AUTHOR_BOOK.ID_AUTHOR.equal(AUTHOR.ID_AUTHOR))
       .join(BOOK)
       .on(BOOK.ID_BOOK.equal(AUTHOR_BOOK.ID_BOOK))
       .where(BOOK.STATUS.equal(true))
       .and(BOOK.ID_BOOK.equal(id))
       .groupBy(BOOK.ID_BOOK)
       .fetchOneInto(BookModel.class);
 }
Beispiel #20
0
 public PageBuilder<Signon> findAllByPage(Pageable pageable) {
   SelectConditionStep<SignonRecord> where = dsl.selectFrom(SIGNON).where();
   String sqlBeforePage = where.getSQL(ParamType.INLINED);
   where.limit(pageable.getPageSize()).offset(pageable.getOffset());
   List<Signon> signons = where.fetchInto(Signon.class);
   return new PageBuilder<>(signons, pageable, sqlBeforePage, dsl);
 }
 @Override
 public List<Integer> getConnectorIds(String chargeBoxId) {
   return ctx.select(CONNECTOR.CONNECTOR_ID)
       .from(CONNECTOR)
       .where(CONNECTOR.CHARGE_BOX_ID.equal(chargeBoxId))
       .fetch(CONNECTOR.CONNECTOR_ID);
 }
Beispiel #22
0
  private final int[] executeStatic() {
    List<Query> queries = new ArrayList<Query>();
    QueryCollector collector = new QueryCollector();

    Configuration local =
        configuration.derive(
            Utils.combine(
                configuration.executeListenerProviders(),
                new DefaultExecuteListenerProvider(collector)));

    for (int i = 0; i < records.length; i++) {
      Configuration previous = ((AttachableInternal) records[i]).configuration();

      try {
        records[i].attach(local);
        executeAction(i);
      } catch (QueryCollectorSignal e) {
        Query query = e.getQuery();

        if (query.isExecutable()) {
          queries.add(query);
        }
      } finally {
        records[i].attach(previous);
      }
    }

    // Resulting statements can be batch executed in their requested order
    int[] result = create.batch(queries).execute();
    updateChangedFlag();
    return result;
  }
 @Override
 public List<NewsSource> getAllNewsSources(int userId) {
   return dsl.select(
           count(SUBSCRIPTIONS.NEWS_SOURCE_ID),
           NEWS_SOURCES.ID,
           NEWS_SOURCES.NAME,
           NEWS_SOURCES.RECIPE_FILE,
           NEWS_SOURCES.DESCRIPTION,
           NEWS_SOURCES.LANGUAGE,
           NEWS_SOURCES.CONTENT_DAYS,
           NEWS_SOURCES.CATEGORY_1)
       .from(
           NEWS_SOURCES
               .leftOuterJoin(SUBSCRIPTIONS)
               .on(
                   NEWS_SOURCES
                       .ID
                       .eq(SUBSCRIPTIONS.NEWS_SOURCE_ID)
                       .and(SUBSCRIPTIONS.USER_ID.eq(userId))))
       .groupBy(NEWS_SOURCES.ID)
       .fetch()
       .map(
           record -> {
             return new NewsSource(
                 record.getValue(NEWS_SOURCES.ID),
                 record.getValue(NEWS_SOURCES.NAME),
                 record.getValue(NEWS_SOURCES.RECIPE_FILE),
                 record.getValue(NEWS_SOURCES.CATEGORY_1),
                 record.getValue(NEWS_SOURCES.LANGUAGE),
                 record.getValue(NEWS_SOURCES.DESCRIPTION),
                 record.getValue(NEWS_SOURCES.CONTENT_DAYS),
                 record.getValue(0, Integer.class) != 0);
           });
 }
  @Override
  public boolean isRegistered(String chargeBoxId) {
    Record1<Integer> r =
        ctx.selectOne().from(CHARGE_BOX).where(CHARGE_BOX.CHARGE_BOX_ID.eq(chargeBoxId)).fetchOne();

    return (r != null) && (r.value1() == 1);
  }
  @Override
  public int findByTieIdAndTeachTypeIdAndPageCount(
      ClassroomTimetableListVo classroomTimetableListVo, int tieId) {
    Condition a =
        Tables.CLASSROOM_COURSE_TIMETABLE_INFO
            .TIE_ID
            .eq((tieId))
            .and(
                Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TEACH_TYPE_ID.eq(
                    classroomTimetableListVo.getTeachTypeId()));

    if (StringUtils.hasLength(classroomTimetableListVo.getTimetableInfoFileName())) {
      a =
          a.and(
              Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_FILE_NAME.like(
                  "%" + classroomTimetableListVo.getTimetableInfoFileName() + "%"));
    }

    if (StringUtils.hasLength(classroomTimetableListVo.getTimetableInfoTerm())) {
      a =
          a.and(
              Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_TERM.like(
                  "%" + classroomTimetableListVo.getTimetableInfoTerm() + "%"));
    }

    if (StringUtils.hasLength(classroomTimetableListVo.getClassroom())) {
      a =
          a.and(
              Tables.CLASSROOM_COURSE_TIMETABLE_INFO.CLASSROOM.like(
                  "%" + classroomTimetableListVo.getClassroom() + "%"));
    }
    Record1<Integer> record1 =
        create.selectCount().from(Tables.CLASSROOM_COURSE_TIMETABLE_INFO).where(a).fetchOne();
    return record1.value1();
  }
  public static void main(String[] args) throws IOException, SQLException {
    ResourceLoader loader = Hsqldb.keggReactionLoader();
    loader.addLocation("kegg.reaction", new SystemLocation("/databases/kegg/ligand/reaction"));
    if (loader.canBackup()) loader.backup();
    long t0 = System.nanoTime();
    loader.update();
    long t1 = System.nanoTime();
    System.out.println(TimeUnit.NANOSECONDS.toMillis(t1 - t0) + " ms");

    Connection connection = Hsqldb.keggReactionConnection().getConnection();
    DSLContext context = DSL.using(connection, HSQLDB);
    System.out.println(context.select().from(REACTION).fetch());
    System.out.println(context.select().from(COMPOUND).fetch());
    System.out.println(context.select().from(REACTANT).fetch());
    System.out.println(context.select().from(PRODUCT).fetch());
    connection.close();
  }
 public static Msg<String> addAdressLibrary(Context context, Addresslibrary al) {
   DSLContext create = context.getJc().getDefaultClient().getContext();
   Msg<String> msg = new Msg<String>();
   AddresslibraryRecord record = create.newRecord(ADDRESSLIBRARY);
   try {
     record.from(al);
     record.setAddresslibraryid(UUIDTool.getUUID());
     record.store();
   } catch (Exception e) {
     e.printStackTrace();
     msg.setResult(false);
     return msg;
   }
   msg.setValue(record.getAddresslibraryid());
   msg.setResult(true);
   return msg;
 }
 public static Msg<String> updateDeAdressLibrary(Context context, String alid) {
   DSLContext create = context.getJc().getDefaultClient().getContext();
   Msg<String> msg = new Msg<String>();
   try {
     create
         .update(ADDRESSLIBRARY)
         .set(ADDRESSLIBRARY.ISDEFAULT, 0)
         .where(ADDRESSLIBRARY.ADDRESSLIBRARYID.ne(alid))
         .execute();
   } catch (Exception e) {
     e.printStackTrace();
     msg.setResult(false);
     return msg;
   }
   msg.setResult(true);
   return msg;
 }
 public static Msg<String> delAdressLibrary(Context context, Addresslibrary al) {
   DSLContext create = context.getJc().getDefaultClient().getContext();
   Msg<String> msg = new Msg<String>();
   try {
     create
         .update(ADDRESSLIBRARY)
         .set(ADDRESSLIBRARY.ISDEL, 1)
         .where(ADDRESSLIBRARY.ADDRESSLIBRARYID.eq(al.getAddresslibraryid()))
         .execute();
   } catch (Exception e) {
     e.printStackTrace();
     msg.setResult(false);
     return msg;
   }
   msg.setResult(true);
   return msg;
 }
 @Override
 public List<String> getRecipesForToday() {
   String dayOfWeek = getDayAsRegex();
   return dsl.select(NEWS_SOURCES.RECIPE_FILE)
       .from(NEWS_SOURCES)
       .where(NEWS_SOURCES.CONTENT_DAYS.like(dayOfWeek))
       .fetch()
       .map(x -> x.getValue(0, String.class));
 }