Hello Team,
I have a requirement to import bulk entities from excel sheet, in
backend, first I am checking if the record exists by using snippet1 and
then inserting the record using snippet2.
Below code snippet are composed using apache openjpa library version 3.2.2
Datasource and Connection pooling setting in spring xml configuration :
<bean id="dataSource1"
class="org.apache.commons.dbcp.datasources.SharedPoolDataSource">
<property name="maxActive" value="-1"/>
<property name="maxIdle" value="${db.sentinel.datasource.maxIdle}"/>
<property name="maxWait" value="-1"/>
<property name="connectionPoolDataSource"
ref="connectionPoolDataSource1"/>
<property name="testOnBorrow" value="true"/>
<property name="testOnReturn" value="true"/>
</bean>
<bean id="connectionPoolDataSource1"
class="org.postgresql.ds.PGConnectionPoolDataSource">
<property name="serverName" value="${db.sentinel.server}"/>
<property name="portNumber" value="${db.sentinel.port}"/>
<property name="databaseName" value="${db.sentinel.dbName}"/>
<property name="user" value="${db.sentinel.user}"/>
<property name="password" value="${db.sentinel.password}"/>
<property name="socketTimeout"
value="${db.sentinel.datasource.socketTimeout}"/>
</bean>
added below ConnectionFactoryProperties property in persistence.xml file :
<persistence-unit name="sentinel" transaction-type="RESOURCE_LOCAL">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<properties>
<property name="openjpa.DynamicEnhancementAgent" value="true"/>
<property name="openjpa.RuntimeUnenhancedClasses"
value="supported"/>
<property name="openjpa.Log" value="SQL=INFO"/>
<property name="openjpa.ConnectionFactoryProperties"
value="PrettyPrint=true, PrettyPrintLineLength=72, PrintParameters=true,
MinSize=5, MaxSize=20, MaxActive=10, MaxIdle=5, MinIdle=2, MaxWait=60000"/>
<property name="openjpa.jdbc.QuerySQLCache"
value="true(EnableStatistics=false)"/>
<property name="openjpa.jdbc.DBDictionary"
value="postgres(StoreCharsAsNumbers=false, BatchLimit=25)"/>
</properties>
</persistence-unit>
*Code Snippet1 to fetch data from Postgres database : we have used mainly
two types of fetch code, as mentioned in points 1 and 2 below marked in
red.*
*1. Criteria Builder is optimized by using implementing the
predicatesCriteriaBuilder criteriaBuilder =
entityManager.getCriteriaBuilder();CriteriaQuery<T> criteriaQuery =
criteriaBuilder.createQuery(this.persistentClass);Root<T> root =
criteriaQuery.from(this.persistentClass);criteriaQuery.select(root);Predicate
namePredicate =
criteriaBuilder.equal(criteriaBuilder.lower(root.get(propertyName)),
propertyValue.toString().toLowerCase());criteriaQuery.where(namePredicate);List<T>
resultList = entityManager.createQuery(criteriaQuery).getResultList(); */*
*2. FindByProperty method is optimized using TypedQuery and executed it
directly using createQueryString jpql = "SELECT a FROM "
persistentClass.getName() " a WHERE LOWER(a.name <http://a.name>) =
LOWER(:name)";TypedQuery<T> query = entityManager.createQuery(jpql,
this.persistentClass);query.setParameter(propertyName,
propertyValue.toString().toLowerCase());List<T> resultList =
query.getResultList();*/*
*Code snippet 2 is generic saveandupdate call we are using to persist
entities :*
*Snippet2 : public T saveOrUpdate(T entity) { return (T)
jpaRepository.save(entity); }*
*Issue with above calls is with large records update, Since I have
thousands of records in excel and processing them sequentially, the fetch
and update calls takes a longer time than expected and keeps on **growing
exponentially in time taken, so i**nitially, it is faster and eventually
becomes slower consuming more memory as well.*
Any suggestions would be appreciated.
Thanks in advance!
With Best Regards,
Abhishek Srivastava
