private static SelectOp.FjList<String> selectByDescOp(String desc) { return new SelectOp.FjList<>("SELECT * FROM MySqlTest_DATA WHERE DESCRIPTION=?", (preparedStatement) -> preparedStatement.setString(1, desc), resultSet -> resultSet.getString(2)); }
public static DB<List<Order>> selectOrdersByEmail(String userEmail) { return new SelectOp.FjList<>( "SELECT ORDER_ID, USER_EMAIL, TEXT FROM ORDERS WHERE USER_EMAIL=?", ps -> ps.setString(1, userEmail), rs -> new Order(rs.getInt(1), rs.getString(2), rs.getString(3)) ); }
private static DB<Option<String>> selectByUniqueDescOp(String desc) { return DbOps.unique(new SelectOp.FjList<>("SELECT * FROM MySqlTest_DATA WHERE DESCRIPTION=?", (preparedStatement) -> preparedStatement.setString(1, desc), resultSet -> resultSet.getString(2))); }
new SelectOp.FjList<>("SELECT WHATEVER FROM FOO", NO_BINDER, resultSet -> resultSet.getString(1)) );
@Test public void iterableBinder() { List<P2<String, String>> data = arrayList(p("x1", "y1"), p("x2", "y2"), p("x3", "y3")); String sql = format( "INSERT INTO MySqlTest_FOO(X,Y) VALUES ({0})", SqlStringUtils.placeholderRows(data.length(), 2) ); UpdateOp insert = new UpdateOp( sql, Binders.iterableBinder((pos, ps, x) -> { int currentPosition = pos; ps.setString(currentPosition++, x._1()); ps.setString(currentPosition++, x._2()); return currentPosition; }, data) ); int updateCount = DB.transact(insert); assertThat(updateCount, is(3)); List<P2<String, String>> readData = DB.submit(new SelectOp.FjList<>("SELECT X,Y FROM MySqlTest_FOO", NO_BINDER, rs -> p(rs.getString(1), rs.getString(2)))); assertThat(readData, is(data)); }
@After public void cleanup() { SyncDbInterpreter dbi = new SyncDbInterpreter( () -> DriverManager.getConnection("jdbc:hsqldb:mem:DescribeVsInterpret", "sa", "") ); DB<List<String>> selectAllTables = new SelectOp.FjList<>( "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DescribeVsInterpret'", NO_BINDER, rs -> rs.getString(1) ); DB<Unit> dropAllTables = selectAllTables .bind(tables -> sequence(tables.map(tableName -> new UpdateOp(format("DROP TABLE {0}", tableName), NO_BINDER)), fjListCanBuildFrom())) .map(ignore -> Unit.unit()); dbi.submit(dropAllTables); } }
public static DB<List<Stuff>> selectByDescription(String description) { /* A select operation takes SQL, a binder and a mapper, and returns an iterable of results A mapper is a function which maps a single row from the resultset to a single object A generic SelectOp also needs to be told what collection to use in the result The SelectOp.FjList specialization uses fj.data.List, which is an immutable singly-linked list. (Also known as Cons list) There is also a specialization for java Lists; or you can supply your own collection builder */ return new SelectOp.FjList<>( "SELECT ID, DESCRIPTION FROM STUFF WHERE DESCRIPTION=?", ps -> ps.setString(1, description), //build a Stuff from a resultset row rs -> new Stuff(rs.getInt(1), rs.getString(2)) ); }
@Test public void shouldSupportWhereSelects() throws ExecutionException, InterruptedException { //CREATE TABLE MySqlTest_EXPIRING_STUFF (ID INTEGER PRIMARY KEY AUTO_INCREMENT, DESCRIPTION VARCHAR(100),STAMP BIGINT)" UpdateOp insert = new UpdateOp("INSERT INTO MySqlTest_EXPIRING_STUFF(DESCRIPTION,STAMP) VALUES (?,?)", (stmt) -> { stmt.setString(1, "a"); stmt.setLong(2, 1L); }); final DB<List<String>> selectOp = insert .bind(ignore -> new SelectOp.FjList<>("SELECT * FROM MySqlTest_EXPIRING_STUFF WHERE STAMP < ? ", (stmt) -> stmt.setLong(1, 2L), resultSet -> resultSet.getString(2))); List<String> result = DB.submit(selectOp); assertEquals(list("a"), result); }
private DB<List<String>> selectAll() { return new SelectOp.FjList<>("SELECT * FROM BAR", NO_BINDER, rs -> rs.getString(1)); }
@Test public void foldLeft() { FoldLeftSelectOp<Integer> fold = new FoldLeftSelectOp<>("SELECT * FROM MySqlTest_IDS", NO_BINDER, (x, rs) -> x + rs.getInt(1), 0); Integer result = DB.submit(fold); assertThat(result, is(0)); DB.submit(insertKeysOp(asList("a", "b", "c", "d"))); Integer expected = DB.submit(new SelectOp.FjList<>("SELECT ID FROM MySqlTest_IDS", NO_BINDER, x -> x.getInt(1))) .foldLeft((x, y) -> x + y, 0); result = DB.submit(fold); assertThat(result, is(expected)); }
public static DB<Option<Stuff>> selectByKey(int id) { //given a regular operation which returns an iterable: DB<List<Stuff>> temp = new SelectOp.FjList<>( "SELECT ID, DESCRIPTION FROM STUFF WHERE ID=?", ps -> ps.setInt(1, id), rs -> new Stuff(rs.getInt(1), rs.getString(2))); // using the unique() function, // we can convert it to an operation which expects at most one result, and returns that optional result: return unique(temp); }
private DB<List<String>> selectAll() { return new SelectOp.FjList<>("SELECT * FROM BAR", NO_BINDER, rs -> rs.getString(1)); }
@Test public void nothingHappens() { @SuppressWarnings("unused") SelectOp.FjList<String> selectThem = new SelectOp.FjList<>( "SELECT FOO FROM WHATEVER", NO_BINDER, rs -> rs.getString(1) ); }
private static DB<Option<User>> selectUser(String email) { return unique(new SelectOp.FjList<>( "SELECT * FROM USERS WHERE EMAIL=?", ps -> ps.setString(1, email), rs -> new User(rs.getString(1), rs.getString(2)) )); }
@Test public void nextValFromSequence() { DB.submit(new EffectOp("CREATE SEQUENCE MY_SEQ START WITH 1 MINVALUE 1")); final Option<Long> firstValFromSeqOp = DB.submit(unique(new SelectOp.FjList<>("call NEXT VALUE FOR MY_SEQ", NO_BINDER, rs -> rs.getLong(1)))); assertThat(firstValFromSeqOp.some(), is(1L)); final Option<Long> secondsValFromSeqOp = DB.submit(unique(new SelectOp.FjList<>("call NEXT VALUE FOR MY_SEQ", NO_BINDER, rs -> rs.getLong(1)))); assertThat(secondsValFromSeqOp.some(), is(2L)); }