Will you consider taking the Dao/Model pattern of JDBI and SpringData?

[gencube]

Will you please also consider taking these code pattern from JDBI or SpringData? http://jdbi.org/

public interface MyDAO
{
  @SqlUpdate("create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);

  /**
   * close with no args is used to close the connection
   */
  void close();
}```

OR Model+Repository pattern from springdata:
https://github.com/spring-projects/spring-data-book/tree/master/mongodb

```java
package com.oreilly.springdata.mongodb.core;
import org.springframework.data.repository.Repository;

/**
 * Repository interface to access {@link Customer}s.
 * 
 * @author Oliver Gierke
 */
public interface CustomerRepository extends Repository<Customer, Long> {

    /**
     * Returns the customer with the given identifier.
     * 
     * @param id
     * @return
     */
    Customer findOne(Long id);

    /**
     * Saves the given {@link Customer}. #
     * 
     * @param customer
     * @return
     */
    Customer save(Customer customer);

    /**
     * Returns the {@link Customer} with the given {@link EmailAddress}.
     * 
     * @param string
     * @return
     */
    Customer findByEmailAddress(EmailAddress emailAddress);
}

However have OPTIONS to handle the commonly used pattern in Reactive or Sync model??

[gencube]

It would be a good pattern to reduce the LOC for myql coding and also implemented efficiently.

[davidmoten]

Yeah, I like the idea of the bind interface from jdbi. I'll have to ponder how it will fit in with the reactive model. Using dynamic proxies with an interface would be nicer than using the mapped class.

[gencube]

:dancers: Please do consider this mapper feature also: http://jdbi.org/sql_object_api_queries/

public class SomethingMapper implements ResultSetMapper<Something>
{
  public Something map(int index, ResultSet r, StatementContext ctx) throws SQLException
  {
    return new Something(r.getInt("id"), r.getString("name"));
  }
}
...
 @SqlQuery("select id, name from something where id = :id")
  @Mapper(SomethingMapper.class)  // <<< Fast(better than Reflection) and ORM
  Something findById(@Bind("id") int id);

AND also

http://jdbi.org/sql_object_api_dml/

public static interface Update
{
...
 @SqlUpdate("update something set name = :name where id = :id")
  int update(@BindBean Something s);  // <<< @BindBean
}

[davidmoten]

Thank you very much for that stuff. I've made a minor start by improving autoMap so you can use an annotated interface. See here. I've just released it as 0.5.8.

I'll keep checking this stuff out and add things bit by bit.

[gencube]

Good work!! Even cleaner code. I too was considering AutoMap Example for the @Mapper(SomethingMapper.class) Suggestion. How would that also impact the update/insert example?

[davidmoten]

The mapper functionality to avoid reflection overhead already exists (though not with an annotation):

        String name = db()
                .select("select name from person order by name")
                .get(new Func1<ResultSet, String>() {
                    @Override
                    public String call(ResultSet rs) {
                        try {
                            return rs.getString(1);
                        } catch (SQLException e) {
                            throw new RuntimeException(e);
                        }
                    }
                }).first().toBlocking().single();
    }

Using java 8 and rxjava-extras Checked class:

        String name = db()
                 .select("select name from person order by name")
                .get(Checked.f1(rs -> rs.getString(1))
                .first().toBlocking().single();

[davidmoten]

Updated docs about custom mapping: https://github.com/davidmoten/rxjava-jdbc/blob/master/README.md#custom-mapping

I would like to have get be called with say a CheckedFunc1 that can throws an Exception so we can get rid of the try catch.

[davidmoten]

rxjava-jdbc 0.5.9 will use a new ResultSetMapper<T> interface instead of a Func1<ResultSet,Boolean> as parameter to get.

This will enable usage like:

Observable<Person> persons = db()
                .select("select name, score from person order by name")
                .get(rs -> new Person(rs.getString(1), rs.getInt(2)));

[gencube]

The above progress looks good:

Actually, I am new to rxjava, and like to clarify with another feature, you recommended not to use .toBlocking():

... 
Observable<Person> persons = db()
                .select("select name, score from person order by name")
                .get(rs -> new Person(rs.getString(1), rs.getInt(2)))
               .first().toBlocking().single();
// vs

// I knows this can be run async and automatically in multithread as you 
Observable<Person> persons = db()
                .select("select name, score from person order by name")
                .get(rs -> new Person(rs.getString(1), rs.getInt(2)));

IF I have JaxRS, because of existing code base on client side:

@Path("/persons")
public class PersonController {

[davidmoten]

toBlocking is ok, you just get more benefit from one flow as opposed to many discrete flows.

[gencube]

Accidentally, closed. IF I have JaxRS, because of existing code base on client side:

@Path("/persons") public class PersonController {

@GET    
@Produces({"application/json", "application/xml"})
public List<Person> get() {
    List<Person> persons= ...
    // How do I use rxjava + jdbc ASYNC here???
   ...
    return persons.toArray();
}

}

[gencube]

try { return new Person(rs.get(1), rs.get(2));} catch (SqlException e) { throw new RuntimeException(e);}} <<< Too noisy.

How to hide it with the this feature?? https://github.com/ReactiveX/RxJava/wiki/Error-Handling-Operators

[davidmoten]

toBlocking is ok for those application boundaries. If an error occurs it will rethrow it as a RuntimeException of some sort. There are other ways of doing things using a Subscriber which I can show you if you want.

[gencube]

Subscriber which I can show you if you want. Please do. in the context of the JaxRS?

[davidmoten]

try { return new Person(rs.get(1), rs.get(2));} catch (SqlException e) { throw new RuntimeException(e);}} <<< Too noise.

Fixed in 0.5.9 when it comes out as mentioned above. The try catch will be gone.

How to hide it with the this feature?? https://github.com/ReactiveX/RxJava/wiki/Error-Handling-Operators

That's for something else.

[gencube]

Please help here?

@Path("/persons")
public class PersonController {

@GET    
@Produces({"application/json", "application/xml"})
public List<Person> get() {
    List<Person> persons= ...
    // How do I use rxjava + jdbc ASYNC here???
   ...
    return persons.toArray();
}
}

You are kind! I been to Aussie(Sydney, Melbourne) many times. Missed the Harry beef pie in Sydney. Aussies are very well known for being kind. :D

[davidmoten]

By the way, you could also use Retrofit's integration with RxJava for an endpoint like this http://square.github.io/retrofit/

[gencube]

is retrofit for server side? I had the impression it is for client side code only.

Btw, This Beef Pie https://wanderlustforfood.files.wordpress.com/2011/01/img_1188.jpg

[davidmoten]

@Path("/persons")
public class PersonController {

@GET    
@Produces({"application/json", "application/xml"})
public List<Person> get() {
    List<Person> persons= db
              .select("select name, score from person")
              .get(rs -> new Person(rs.get(1), rs.get(2))
              .toList().toBlocking().single();
   ...
    return persons;
}
}

[davidmoten]

yep retrofit for server side, the signature of your server side method like you have can return Observable<Person> instead of List<Person>

[gencube]

Thanks, how about insert?

@Path("/persons")
public class PersonController {
@POST
@Produces({"application/json", "application/xml"})
public String add(Person person) {
     String primaryId="none";
    // rx-java-jdbc?

   return   primaryId
}```

[davidmoten]

Ah so you are in Malaysia right, good stuff. You'd have a heart attack here, forecast is for -6 degrees tonight.

[gencube]

Yes. Malaysia. Was working in Singapore. If you visit penang, let me know. I show you how to gain plenty of winter weight. With some porkchop + friedrice: http://www.opensnap.com.cn/en/photo/201166817

forecast is for -6 degrees tonight. << Where? I experience 3-4degreess with windy Sydney.

[davidmoten]

newId = ...
db
        // insert record if does not exist
                .update("insert into person(id, name,score) values(?,?)")
                // get parameters from last query
                .parameters(newId, person.name(), person.score()))
                // return num rows affected
                .count().toBlocking().single();
return newId;
`
You've already mentioned you'd like to get the generated key returned. That will have to wait.

[davidmoten]

Canberra, which is 550m higher than Sydney

[davidmoten]

I'd be delighted to meet you there, I love Malaysian food (all of its many varieties).

[gencube]

Canberra? Never been there. I drove from Sydney to Melbourne(2007 Jun/july) coastline. Beautiful country.

[davidmoten]

0.5.9 is released to Maven Central now and includes @Query annotation for the autoMap interface and get now takes a ResultSetMapper<T> which throws the exception so you don't have to catch it.

[gencube]

Also Consider Backing me up on this suggestion? It might reduce your work also: https://github.com/ReactiveX/RxJava/issues/3004

[gencube]

Such ideas are inspired from reading this URL from project lombok: http://jnb.ociweb.com/jnb/jnbJan2010.html @Data, @Cleanup and @SneakyThrows Reducing Boilerplate Code with Project Lombok

[gencube]

This is new?? I was about the suggest this to you but does not want to swap the issue, : Database Connection Pools
Include the dependency below:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP-java6</artifactId>
    <version>2.3.2</version>
</dependency>
and you can use a Hikari database connection pool like so:

```java
Database db = Database.builder().url(url).pool(minPoolSize,maxPoolSize).build();
// Once finished with a Database that has used a connection pool you should call
db.close();

This will close the connection pool and release its resources.

[gencube]

By the way, you could also use Retrofit's integration with RxJava for an endpoint like this http://square.github.io/retrofit/ <<< Trying it out today.

[davidmoten]

Connection pools are not new no.

On Thu, 4 Jun 2015 13:16 Matthew Ong notifications@github.com wrote:

By the way, you could also use Retrofit's integration with RxJava for an endpoint like this http://square.github.io/retrofit/ <<< Trying it out today.

— Reply to this email directly or view it on GitHub https://github.com/davidmoten/rxjava-jdbc/issues/19#issuecomment-108701352 .

[gencube]

Yes. I saw ConnectionPool. But I think I remember I did not see Hikari. Anyway, you picked the right choices. CLAPx3.

[gencube]

Hi,

I tried out retrofit. It is merely an JaxRS Client side JAVA api which is used for calling REST Service. I double confirmed it with reading this link: http://www.coolcoder.in/2015/02/6-powerful-frameworks-for-creating.html

[gencube]

I found out this is also not hard to use, a bit more raw than JaxRS, but workable: And just translated the PersonController into their code: http://sparkjava.com/documentation.html And Many Good full working examples: https://github.com/perwendel/spark/blob/master/README.md Perhaps you like to consider coding a Good Reference model to promote your code also.

[gencube]

public class MyJsonTransformer implements ResponseTransformer { } // Mapper patterns nice and simple to support custom protocol and object serialization.

get("/hello", "application/json", (request, response) -> { return new MyMessage("Hello World"); }, new MyJsonTransformer()); // <<< USED here.

:D x3.