Hi, approach above doesn't fit web-app. There are multiple simultaneous upserts comes from different threads.
So the only thing is to put connection into thread-local and use one connection per thread.

try (Connection conn = DriverManager.getConnection(url)) {
conn.setAutoCommit(false);
int batchSize = 0;
int commitSize = 1000; // number of rows you want to commit per batch. Change this value according to your needs.
try (Statement stmt = conn.prepareStatement(upsert)) {
stmt.set ...
while (there are records to upsert) {
     stmt.executeUpdate();
     batchSize++;
     if (batchSize % commitSize == 0) {
          conn.commit();
     }
}
conn.commit(); // commit the last batch of records
}


2015-09-03 22:28 GMT+02:00 Jaime Solano <jdjsolano@gmail.com>:
Thanks for the explanation, Samarth!

On Thu, Sep 3, 2015 at 4:11 PM, Samarth Jain <samarth@apache.org> wrote:
Hi Jaime,

Phoenix is an embedded JDBC driver i.e. the connections are not meant to be pooled. Creating phoenix connections is a light weight operation. This is because all phoenix connections to a cluster from the Phoenix JDBC driver use the same underlying HConnection. 

If you end up pooling Phoenix connections then you will likely run into unexpected issues. It is likely that a request will leave a Phoenix connection not in a kosher state for the next request to use it.

On Thu, Sep 3, 2015 at 12:58 PM, Jaime Solano <jdjsolano@gmail.com> wrote:
Hi Samarth,
In our project we were thinking about using Tomcat JDBC Connection Pool, to handle Phoenix connections. You're saying this type of approach should be avoided? What other approach should be followed if, in the scenario Serega described, you experience a heavy load of users, all trying to upsert at the same time (therefore, a lot of connections) ??
Also, can you expand a little bit more on the implications of having a pooling mechanism for Phoenix connections?
Thanks in advance!
-Jaime

On Thu, Sep 3, 2015 at 3:35 PM, Samarth Jain <samarth.jain@gmail.com> wrote:
Yes. PhoenixConnection implements java.sql.Connection.

On Thu, Sep 3, 2015 at 12:34 PM, Serega Sheypak <serega.sheypak@gmail.com> wrote:
>Phoenix doesn't cache connections. You shouldn't pool them and you shouldn't share them with multiple threads.
We are talking about java.sql.Connection, right?

2015-09-03 21:26 GMT+02:00 Samarth Jain <samarth@apache.org>:
Your pattern is correct. 

Phoenix doesn't cache connections. You shouldn't pool them and you shouldn't share them with multiple threads.

For batching upserts, you could do something like this:

You can do this via phoenix by doing something like this:

try (Connection conn = DriverManager.getConnection(url)) {
conn.setAutoCommit(false);
int batchSize = 0;
int commitSize = 1000; // number of rows you want to commit per batch. Change this value according to your needs.
try (Statement stmt = conn.prepareStatement(upsert)) {
stmt.set ...
while (there are records to upsert) {
     stmt.executeUpdate();
     batchSize++;
     if (batchSize % commitSize == 0) {
          conn.commit();
     }
}
conn.commit(); // commit the last batch of records
}

You don't want commitSize to be too large since Phoenix client keeps the uncommitted rows in memory till they are sent over to HBase. 



On Thu, Sep 3, 2015 at 12:19 PM, Serega Sheypak <serega.sheypak@gmail.com> wrote:
Hi, I'm using  phoenix in java web-application. App does upsert or select by primary key.
What is the right pattern to do it?
- I create new connection for each request
- prepare and execute statement
- close stmt
- close connection

Does phoenix caches connections internally? What is the right way to batch upserts in current case?