@Test public void testjOOQTransactionsNested() { AtomicBoolean rollback1 = new AtomicBoolean(false); AtomicBoolean rollback2 = new AtomicBoolean(false); try { // If using Spring transactions, we don't need the c1 reference dsl.transaction( c1 -> { // The first insertion will work dsl.insertInto(BOOK) .set(BOOK.ID, 5) .set(BOOK.AUTHOR_ID, 1) .set(BOOK.TITLE, "Book 5") .execute(); assertEquals(5, dsl.fetchCount(BOOK)); try { // Nest transactions using Spring. This should create a savepoint, right here // If using Spring transactions, we don't need the c2 reference dsl.transaction( c2 -> { // The second insertion shouldn't work for (int i = 0; i < 2; i++) dsl.insertInto(BOOK) .set(BOOK.ID, 6) .set(BOOK.AUTHOR_ID, 1) .set(BOOK.TITLE, "Book 6") .execute(); Assert.fail(); }); } catch (DataAccessException e) { rollback1.set(true); } // We should've rolled back to the savepoint assertEquals(5, dsl.fetchCount(BOOK)); throw new org.jooq.exception.DataAccessException("Rollback"); }); } // Upon the constraint violation, the transaction must already have been rolled back catch (org.jooq.exception.DataAccessException e) { assertEquals("Rollback", e.getMessage()); rollback2.set(true); } assertEquals(4, dsl.fetchCount(BOOK)); assertTrue(rollback2.get()); assertTrue(rollback2.get()); }
@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); }
@Override public void subscribe(int userId, int newsSourceId) { dsl.insertInto(SUBSCRIPTIONS, SUBSCRIPTIONS.NEWS_SOURCE_ID, SUBSCRIPTIONS.USER_ID) .values(newsSourceId, userId) .onDuplicateKeyIgnore() .execute(); }
@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); }
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]); }
@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(); }
/* (non-Javadoc) * @see * edu.dfci.cccb.mev.dataset.domain.contract.ValueStoreBuilder#add(double, * java.lang.String, java.lang.String) */ @Override public ValueStoreBuilder add(double value, String row, String column) throws ValueStoreException { context .insertInto(table) .set(this.row, row) .set(this.column, column) .set(this.value, value) .execute(); return this; }
@Test public void testSaveOrUpdateWhenDuplicate() { // Add a new author called "Koontz" with ID 3. // If that ID is already present, update the author's name create .insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME) .values(3, "Koontz") .onDuplicateKeyUpdate() .set(AUTHOR.LAST_NAME, "Koontz") .execute(); }
private int addChargePointInternal(DSLContext ctx, ChargePointForm form, Integer addressPk) { return ctx.insertInto(CHARGE_BOX) .set(CHARGE_BOX.CHARGE_BOX_ID, form.getChargeBoxId()) .set(CHARGE_BOX.DESCRIPTION, form.getDescription()) .set(CHARGE_BOX.LOCATION_LATITUDE, form.getLocationLatitude()) .set(CHARGE_BOX.LOCATION_LONGITUDE, form.getLocationLongitude()) .set(CHARGE_BOX.NOTE, form.getNote()) .set(CHARGE_BOX.ADDRESS_PK, addressPk) .returning(CHARGE_BOX.CHARGE_BOX_PK) .fetchOne() .getChargeBoxPk(); }
@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(); }
@Test public void testInsertAndReturn() { // Add another author, with a generated ID Record record = create .insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .values("Charlotte", "Roche") .returning(AUTHOR.ID) .fetchOne(); System.out.println(record.getValue(AUTHOR.ID)); // For some RDBMS, this also works when inserting several values // The following should return a 2x2 table Result<?> result = create .insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .values("Johann Wolfgang", "von Goethe") .values("Friedrich", "Schiller") // You can request any field. Also trigger-generated values .returning(AUTHOR.ID, AUTHOR.LAST_NAME) .fetch(); System.out.println(result.getValue(0, AUTHOR.ID)); System.out.println(result.getValue(1, AUTHOR.ID)); }
@Override public void updateBook(BookModel bookModel) { create .update(BOOK) .set(BOOK.TITLE, bookModel.getTitle()) .set(BOOK.DATE_PUBLISH, bookModel.getDatePublish()) .set(BOOK.SHORT_DESCRIPTION, bookModel.getShortDescription()) .where(BOOK.ID_BOOK.equal(bookModel.getIdBook())) .execute(); List<Integer> existsAuthors = create .select(AUTHOR_BOOK.ID_AUTHOR) .from(AUTHOR_BOOK) .where(AUTHOR_BOOK.ID_BOOK.equal(bookModel.getIdBook())) .fetchInto(Integer.class); logger.debug("Existing authors: " + existsAuthors); List<Integer> newAuthors = new ArrayList<>(); List<Integer> authors = new ArrayList<>(); /** */ for (String idAuthor : bookModel.getAuthors()) { int id = Integer.valueOf(idAuthor); authors.add(id); if (!existsAuthors.contains(id)) { newAuthors.add(id); } } existsAuthors.removeAll(authors); logger.debug("New authors: " + newAuthors); if (!newAuthors.isEmpty()) { InsertValuesStep2<AuthorBookRecord, Integer, Integer> insertStep = create.insertInto(AUTHOR_BOOK, AUTHOR_BOOK.ID_AUTHOR, AUTHOR_BOOK.ID_BOOK); InsertValuesStep2<AuthorBookRecord, Integer, Integer> valuesStep = null; for (Integer idAuthor : newAuthors) { valuesStep = insertStep.values(idAuthor, bookModel.getIdBook()); } valuesStep.execute(); } logger.debug("Old authors: " + existsAuthors); if (!existsAuthors.isEmpty()) { create.delete(AUTHOR_BOOK).where(AUTHOR_BOOK.ID_AUTHOR.in(existsAuthors)).execute(); } }
/** Blocking version of {@link AsyncSpanConsumer#accept} */ @Override public void accept(List<Span> spans) { if (spans.isEmpty()) return; try (Connection conn = datasource.getConnection()) { DSLContext create = context.get(conn); List<Query> inserts = new ArrayList<>(); for (Span span : spans) { Long overridingTimestamp = authoritativeTimestamp(span); Long timestamp = overridingTimestamp != null ? overridingTimestamp : guessTimestamp(span); Map<TableField<Record, ?>, Object> updateFields = new LinkedHashMap<>(); if (!span.name.equals("") && !span.name.equals("unknown")) { updateFields.put(ZIPKIN_SPANS.NAME, span.name); } // replace any tentative timestamp with the authoritative one. if (overridingTimestamp != null) { updateFields.put(ZIPKIN_SPANS.START_TS, overridingTimestamp); } if (span.duration != null) { updateFields.put(ZIPKIN_SPANS.DURATION, span.duration); } InsertSetMoreStep<Record> insertSpan = create .insertInto(ZIPKIN_SPANS) .set(ZIPKIN_SPANS.TRACE_ID, span.traceId) .set(ZIPKIN_SPANS.ID, span.id) .set(ZIPKIN_SPANS.PARENT_ID, span.parentId) .set(ZIPKIN_SPANS.NAME, span.name) .set(ZIPKIN_SPANS.DEBUG, span.debug) .set(ZIPKIN_SPANS.START_TS, timestamp) .set(ZIPKIN_SPANS.DURATION, span.duration); if (span.traceIdHigh != 0 && schema.hasTraceIdHigh) { insertSpan.set(ZIPKIN_SPANS.TRACE_ID_HIGH, span.traceIdHigh); } inserts.add( updateFields.isEmpty() ? insertSpan.onDuplicateKeyIgnore() : insertSpan.onDuplicateKeyUpdate().set(updateFields)); for (Annotation annotation : span.annotations) { InsertSetMoreStep<Record> insert = create .insertInto(ZIPKIN_ANNOTATIONS) .set(ZIPKIN_ANNOTATIONS.TRACE_ID, span.traceId) .set(ZIPKIN_ANNOTATIONS.SPAN_ID, span.id) .set(ZIPKIN_ANNOTATIONS.A_KEY, annotation.value) .set(ZIPKIN_ANNOTATIONS.A_TYPE, -1) .set(ZIPKIN_ANNOTATIONS.A_TIMESTAMP, annotation.timestamp); if (span.traceIdHigh != 0 && schema.hasTraceIdHigh) { insert.set(ZIPKIN_ANNOTATIONS.TRACE_ID_HIGH, span.traceIdHigh); } if (annotation.endpoint != null) { insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_SERVICE_NAME, annotation.endpoint.serviceName); insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_IPV4, annotation.endpoint.ipv4); if (annotation.endpoint.ipv6 != null && schema.hasIpv6) { insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_IPV6, annotation.endpoint.ipv6); } insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_PORT, annotation.endpoint.port); } inserts.add(insert.onDuplicateKeyIgnore()); } for (BinaryAnnotation annotation : span.binaryAnnotations) { InsertSetMoreStep<Record> insert = create .insertInto(ZIPKIN_ANNOTATIONS) .set(ZIPKIN_ANNOTATIONS.TRACE_ID, span.traceId) .set(ZIPKIN_ANNOTATIONS.SPAN_ID, span.id) .set(ZIPKIN_ANNOTATIONS.A_KEY, annotation.key) .set(ZIPKIN_ANNOTATIONS.A_VALUE, annotation.value) .set(ZIPKIN_ANNOTATIONS.A_TYPE, annotation.type.value) .set(ZIPKIN_ANNOTATIONS.A_TIMESTAMP, timestamp); if (span.traceIdHigh != 0 && schema.hasTraceIdHigh) { insert.set(ZIPKIN_ANNOTATIONS.TRACE_ID_HIGH, span.traceIdHigh); } if (annotation.endpoint != null) { insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_SERVICE_NAME, annotation.endpoint.serviceName); insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_IPV4, annotation.endpoint.ipv4); if (annotation.endpoint.ipv6 != null && schema.hasIpv6) { insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_IPV6, annotation.endpoint.ipv6); } insert.set(ZIPKIN_ANNOTATIONS.ENDPOINT_PORT, annotation.endpoint.port); } inserts.add(insert.onDuplicateKeyIgnore()); } } create.batch(inserts).execute(); } catch (SQLException e) { throw new RuntimeException(e); // TODO } }
@Override public void update() throws IOException { ResourceFileLocation location = getLocation("KEGG Reaction"); HSQLDBLocation connection = connection(); try { Hsqldb.createReactionSchema(connection.getConnection()); DSLContext create = DSL.using(connection.getConnection(), HSQLDB); Set<String> compoundIds = Sets.newHashSetWithExpectedSize(10000); InsertValuesStep2<?, String, String> reactionInsert = create.insertInto(REACTION, REACTION.ACCESSION, REACTION.EC); InsertValuesStep1<?, String> compoundInsert = create.insertInto(COMPOUND, COMPOUND.ACCESSION); List<String[]> reactants = new ArrayList<String[]>(10000); List<String[]> products = new ArrayList<String[]>(10000); KEGGReactionParser parser = new KEGGReactionParser( location.open(), KEGGField.ENTRY, KEGGField.EQUATION, KEGGField.ENZYME); Map<KEGGField, StringBuilder> entry; while ((entry = parser.readNext()) != null) { if (isCancelled()) break; String equation = entry.get(KEGGField.EQUATION).toString(); String ec = entry.containsKey(KEGGField.ENZYME) ? entry.get(KEGGField.ENZYME).toString().trim() : ""; String[] sides = equation.split("<=>"); String[][] left = getParticipants(sides[0]); String[][] right = getParticipants(sides[1]); Matcher matcher = ACCESSION.matcher(entry.get(KEGGField.ENTRY).toString()); if (!ec.isEmpty()) ec = ec.split("\\s+")[0].trim(); if (matcher.find()) { String accession = matcher.group(1); reactionInsert.values(accession, ec); for (String[] participant : left) { String cid = participant[1]; if (compoundIds.add(cid)) compoundInsert.values(cid); participant = Arrays.copyOf(participant, 3); participant[2] = accession; reactants.add(participant); } for (String[] participant : right) { String cid = participant[1]; if (compoundIds.add(cid)) compoundInsert.values(cid); participant = Arrays.copyOf(participant, 3); participant[2] = accession; products.add(participant); } } } // do the inserts fireProgressUpdate("inserting reactions and compounds"); reactionInsert.execute(); compoundInsert.execute(); fireProgressUpdate("inserting reaction relations"); for (int i = 0, end = reactants.size() - 1; i <= end; i++) { String[] participant = reactants.get(i); double coef = Double.parseDouble(participant[0]); String cid = participant[1]; String acc = participant[2]; create .insertInto(REACTANT) .set(REACTANT.COEFFICIENT, coef) .set( REACTANT.COMPOUND_ID, create.select(COMPOUND.ID).from(COMPOUND).where(COMPOUND.ACCESSION.eq(cid))) .set( REACTANT.REACTION_ID, create.select(REACTION.ID).from(REACTION).where(REACTION.ACCESSION.eq(acc))) .execute(); } for (int i = 0, end = products.size() - 1; i <= end; i++) { String[] participant = products.get(i); double coef = Double.parseDouble(participant[0]); String cid = participant[1]; String acc = participant[2]; create .insertInto(PRODUCT) .set(PRODUCT.COEFFICIENT, coef) .set( PRODUCT.COMPOUND_ID, create.select(COMPOUND.ID).from(COMPOUND).where(COMPOUND.ACCESSION.eq(cid))) .set( PRODUCT.REACTION_ID, create.select(REACTION.ID).from(REACTION).where(REACTION.ACCESSION.eq(acc))) .execute(); } } catch (SQLException e) { throw new IOException(e); } finally { location.close(); try { connection.commit(); } catch (SQLException e) { System.err.println(e.getMessage()); } finally { try { connection.close(); } catch (SQLException e) { } } } }