@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);
}
@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);
}
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]);
}
@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();
}
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();
}
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();
}
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);
}
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);
}
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));
}