0

I have been earlier injecting datasource (and using jdbctemplate for db operations) using spring DI individually for my two web application but then i consider its better to use datasource and connection pool on the Tomcat Level. So, now i am getting datasource from jndi and creating jdbctemplate using this datasource 

public static DataSource getTomcatDataSource() {
        DataSource dataSource = null;

        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            dataSource = (DataSource) envContext.lookup("jdbc/vendorDB");
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return dataSource;
    }

Adding more details for explaining my scenario, I am updating an sub-arraylist into db inside a for loop. So,i thought having connection pool would complete the task faster

for(int fromIndex = 0, toIndex = (fromIndex +  batchSize  - 1); 
                fromIndex < (sheetList.size() - 1) ; 
                fromIndex = toIndex + 1, toIndex += batchSize){
            ....
List<GoogleSheetPojo> subSheetList = sheetList.subList(fromIndex, toIndex);

            try {
                jdbcTemplate = new JdbcTemplate(DatabaseUtility.getTomcatDataSource());

                rowsEffected = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

                    @Override
                    public void setValues(PreparedStatement pstmt, int i) throws SQLException {
                        MyPojo bean = subSheetList.get(i);
                    ....
}

}

}

}

So, I had few doubts here:

1) Do update operations will become fast ?

2) my datasource method is static, so application would single database connection or should i get datasource without static in order to use database connection pool?

2) since, i created jdbctemplate using new operator, will jdbctemplate and pstmt will be closed safely by application

Lastly, would multithreading would make update operation faster ?

I hope my problem statement is understandable

Ankit
  • 2,126
  • 4
  • 33
  • 53
  • 1. No. 2. You should do the lookup once and create a single `jdbcTemplate` which you should inject (it is thread safe!). 3. `JdbcTemplate` handles all that for you but (as mentioned by 2) you shouldn't create a new template when you need one (it is quite a large and heavy object to create). – M. Deinum Mar 17 '17 at 14:17
  • so, creating connection pool on tomcat will not do any purpose? Also, with single jdbctemplate there will be single database connection for the whole application ? – Ankit Mar 17 '17 at 14:22
  • Yes it will as you will have fewer connections to your database (as depending on how you defined it you will share the connections between the applications). No for each execution you get a new connection from the pool (or reuse one if your method is transactional). – M. Deinum Mar 17 '17 at 14:26

0 Answers0