Postgres Pro
Downloads
Home   >   mailing lists

Re: JPA + Postgres = autocommit? - Mailing list pgsql-jdbc

From Dennis Gesker
Subject Re: JPA + Postgres = autocommit?
Date
Msg-id CAJRXDBPWORR=Lfz10j-y3AnVGfECYeMhSr9=s_CB5Des-oBqjw@mail.gmail.com
Whole thread Raw
In response to JPA + Postgres = autocommit?  ("Davygora, Yuriy" <Yuriy.Davygora@sulzer.de>)
List pgsql-jdbc
Tree view
Hi Yuriy:

Not sure a lot of JPA is covered on this list. But, that being said I do believe that JPA has options for batch writing which I think will speed things up for you. I think this can be done both in the persistence.xml and as hints.
Also, if  the data your are writing is joined to another table you can cache the selected results (using a jpa hint) of the table to which you need to join which can speed things up as well.


Maybe in a simple class we'll call "Constant" and put something like....

    public static final String PERSISTENCEUNIT_PRIMARY = "PrimaryPersistenceUnit";

    @SuppressWarnings("unchecked")
    public static final Map getFastWriteEntityManagerFactoryMap() {
        Map map = new HashMap();

        map.put(PersistenceUnitProperties.LOGGING_LEVEL, SessionLog.FINE_LABEL);

        map.put(PersistenceUnitProperties.TARGET_SERVER, TargetServer.Glassfish);
        map.put(PersistenceUnitProperties.TARGET_DATABASE, TargetDatabase.PostgreSQL);
        map.put(PersistenceUnitProperties.JTA_DATASOURCE, DATASOURCE_PRIMARY);
        map.put(PersistenceUnitProperties.TRANSACTION_TYPE, "JTA");
        map.put(PersistenceUnitProperties.NATIVE_SQL, TRUE_STRING);
        map.put(PersistenceUnitProperties.JDBC_BIND_PARAMETERS, TRUE_STRING);
        map.put(PersistenceUnitProperties.WEAVING, TRUE_STRING);
        map.put(PersistenceUnitProperties.CACHE_STATEMENTS, TRUE_STRING);
        map.put(PersistenceUnitProperties.BATCH_WRITING, BatchWriting.JDBC);
        map.put(PersistenceUnitProperties.BATCH_WRITING_SIZE, "10000");
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_FLUSH_MODE, "commit");
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_CLOSE_ON_COMMIT, TRUE_STRING);
        map.put(PersistenceUnitProperties.PERSISTENCE_CONTEXT_PERSIST_ON_COMMIT, FALSE_STRING);

//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_PRODUCT_NAME, TargetDatabase.PostgreSQL);
//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_MAJOR_VERSION, 9);
//        map.put(PersistenceUnitProperties.SCHEMA_DATABASE_MINOR_VERSION, 3);
//        map.put(PersistenceUnitProperties.SCHEMA_GENERATION_DATABASE_ACTION, "none");
//        
//        map.put(PersistenceUnitProperties.NATIVE_QUERY_UPPERCASE_COLUMNS, FALSE_STRING);
//        map.put(PersistenceUnitProperties.UPPERCASE_COLUMN_NAMES, FALSE_STRING);
//        map.put(PersistenceUnitProperties.FLUSH_CLEAR_CACHE, FlushClearCache.Drop);
//        map.put(PersistenceUnitProperties.CACHE_SHARED_DEFAULT, FALSE_STRING);
//        map.put("eclipselink.cache.shared.Project", TRUE_STRING);
//        map.put("eclipselink.cache.type.Project", "FULL");
//        map.put("eclipselink.cache.size.Project", 100000);
//        map.put(PersistenceUnitProperties.ORDER_UPDATES, TRUE_STRING);        
        return map;
    }



Then in your EJB use something like...

     // PersistenceUnit Style
        EntityManagerFactory emf;
        EntityManager em;


            emf = Persistence.createEntityManagerFactory(Constant.PERSISTENCEUNIT_PRIMARY);
            em = emf.createEntityManager(Constant.getFastWriteEntityManagerFactoryMap());
            em.joinTransaction();

Call em.persist() as often as you wish but JPA will/should use the properties in the map. The upside of the above is that probably most of your application can just use the standard settings in your persistence.xml but for the occassions where you need to write a bunch of data you can use some custom properties on the fly.

I hope that was helpful and addressed your question.


Cordially,
Dennis

On Mon, Jul 25, 2016 at 12:54 AM, Davygora, Yuriy <Yuriy.Davygora@sulzer.de> wrote:

 

  Hello,

 

  I am having a small but severe Problem with PostgreSQL. At our company, we are currently developing a business application in Java EE 7 running on a Payara (Glassfish fork) 4 server (with Eclipselink) and we are using Postgres 9.5 as our database and the postgresql-9.4.1208 JDBC driver. Now, it seems that the connection to the PostgreSQL database is in an autocommit mode. Transactions (both container and bean managed) cannot be rolled back, every single SQL command is executed and commited on flush().

 

  Here is my persistence.xml:

 

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"

             version="2.1">

 

      <persistence-unit name="tsms_pdm" transaction-type="JTA">

            <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

            <jta-data-source>jdbc/PDMDataSource</jta-data-source>

 

            <properties>

                  <property name="eclipselink.jdbc.cache-statements" value="true" />

                  <property name="eclipselink.cache.shared.default" value="false" />

                  <property name="eclipselink.cache.type.default" value="NONE" />

                  <property name="javax.persistence.query.timeout" value="300000" />

            </properties>

      </persistence-unit>

</persistence>

 

 

  And here is an excerpt from the Payara domain.xml:

 

<jdbc-connection-pool datasource-classname="org.postgresql.ds.PGSimpleDataSource" name="PDMDataSource" res-type="javax.sql.DataSource">

                  <property name="TargetServerType" value="any"/>

                  <property name="BinaryTransfer" value="true"/>

                  <property name="UnknownLength" value="2147483647"/>

                  <property name="DisableColumnSanitiser" value="false"/>

                  <property name="UseSpNego" value="false"/>

                  <property name="SspiServiceClass" value="POSTGRES"/>

                  <property name="ProtocolVersion" value="0"/>

                  <property name="LogLevel" value="0"/>

                  <property name="SendBufferSize" value="-1"/>

                  <property name="ReceiveBufferSize" value="-1"/>

                  <property name="LoadBalanceHosts" value="false"/>

                  <property name="ReadOnly" value="false"/>

                  <property name="LogUnclosedConnections" value="false"/>

                  <property name="DatabaseName" value="pdm"/>

                  <property name="GssLib" value="auto"/>

                  <property name="CurrentSchema" value="pdm"/>

                  <property name="PortNumber" value="5432"/>

                  <property name="DefaultRowFetchSize" value="0"/>

                  <property name="User" value="pdm_user"/>

                  <property name="Url" value="jdbc:postgresql://localhost/pdm?"/>

                  <property name="AllowEncodingChanges" value="false"/>

                  <property name="PrepareThreshold" value="5"/>

                  <property name="SocketTimeout" value="0"/>

                  <property name="HostRecheckSeconds" value="10"/>

                  <property name="Password" value="tssb"/>

                  <property name="ConnectTimeout" value="0"/>

                  <property name="PreparedStatementCacheQueries" value="256"/>

                  <property name="Compatible" value="9.4"/>

                  <property name="Ssl" value="false"/>

                  <property name="PreparedStatementCacheSizeMiB" value="5"/>

                  <property name="Loglevel" value="0"/>

                  <property name="LoginTimeout" value="0"/>

                  <property name="ServerName" value="localhost"/>

                  <property name="TcpKeepAlive" value="false"/>

            </jdbc-connection-pool>

            <jdbc-resource pool-name="PDMDataSource" jndi-name="jdbc/PDMDataSource"/>

 

  Am I missing something here? How do I disable the autocommit? Any help would be greatly appreciated.

 

  Best regards,

  Yuriy

Sulzer GmbH
Geschäftsführende Gesellschafter: Dr. Johann Sulzer, Albert Euba, Thomas Kahabka
Geschäftsführer: Angelika Rudolph, Harald Lothspeich
Sitz und Registergericht: Stuttgart HRB 7608
http://www.sulzer.de




--
LinkedIn Wordpress Facebook Twitter Family Home Page Public Encryption Key dennis@gesker.com
“Be without fear in the face of your enemies. Be brave and upright that God may love thee. Speak the truth always, even if it leads to your death. Safeguard the helpless and do no wrong – that is your oath.” -The Knight’s Oath (Kingdom of Heaven)

pgsql-jdbc by date:

Previous
From: "Davygora, Yuriy"
Date:
Subject: JPA + Postgres = autocommit?
Next
From: Dennis Gesker
Date:
Subject: Re: JPA + Postgres = autocommit?