package com.vladmihalcea.book.hpjp.jdbc.caching;

import com.vladmihalcea.book.hpjp.util.AbstractOracleIntegrationTest;
import org.junit.Test;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Tuple;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.fail;

/**
 * @author Vlad Mihalcea
 */
public class OraclePreparedStatementLifecycleTest extends AbstractOracleIntegrationTest {

    public static final String INSERT_POST = "INSERT INTO post (id, title) VALUES (:1 , :2 )";

    @Override
    protected Class<?>[] entities() {
        return new Class<?>[] {
            Post.class
        };
    }

    @Test
    public void testPreparedStatement() {
        doInJDBC(connection -> {
            assertEquals(0, getOpenCursorsForStatement(INSERT_POST).size());
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(INSERT_POST);
                assertEquals(0, getOpenCursorsForStatement(INSERT_POST).size());

                int index  = 0;
                preparedStatement.setLong(++index, 1L);
                preparedStatement.setString(++index, "High-Performance SQL");

                preparedStatement.executeUpdate();

                List<Tuple> openCursors = getOpenCursorsForStatement(INSERT_POST);
                assertEquals(1, openCursors.size());
            } catch (SQLException e) {
                fail(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                assertEquals(1, getOpenCursorsForStatement(INSERT_POST).size());
            }
        });
        assertEquals(0, getOpenCursorsForStatement(INSERT_POST).size());
    }

    private List<Tuple> getOpenCursorsForStatement(String sql) {
        return doInJPA(entityManager -> {
            return entityManager.createNativeQuery("""
                SELECT  
                    sql_text, 
                    count(*) AS "OPEN CURSORS", 
                    user_name 
                FROM v$open_cursor oc
                WHERE 
                    sql_text = :sql
                GROUP BY 
                    sql_text, 
                    user_name 
                ORDER BY 
                    count(*) DESC
                """, Tuple.class)
            .setParameter("sql", sql)
            .getResultList();
        });
    }

    @Entity(name = "Post")
    @Table(name = "post")
    public static class Post {

        @Id
        private Long id;

        private String title;

        public Long getId() {
            return id;
        }

        public Post setId(Long id) {
            this.id = id;
            return this;
        }

        public String getTitle() {
            return title;
        }

        public Post setTitle(String title) {
            this.title = title;
            return this;
        }
    }
}

@@ -653,16 +653,88 @@
 , :2 )%22;
+%0A    public static final String INSERT_POST_PREFIX = %22INSERT INTO post%22;
 %0A%0A    @O
@@ -933,24 +933,158 @@
 ection -%3E %7B%0A
+            // This setting doesn't influence the outcome%0A            executeStatement(%22ALTER SESSION SET session_cached_cursors=0%22);%0A
             
@@ -1129,32 +1129,39 @@
 ment(INSERT_POST
+_PREFIX
 ).size());%0A     
@@ -1202,16 +1202,74 @@
 tatement
+1 = null;%0A            PreparedStatement preparedStatement2
  = null;
@@ -1320,16 +1320,17 @@
 tatement
+1
  = conne
@@ -1428,32 +1428,39 @@
 ment(INSERT_POST
+_PREFIX
 ).size());%0A%0A    
@@ -1516,24 +1516,25 @@
 redStatement
+1
 .setLong(++i
@@ -1577,16 +1577,17 @@
 tatement
+1
 .setStri
@@ -1623,17 +1623,16 @@
  SQL%22);%0A
-%0A
         
@@ -1656,16 +1656,224 @@
 tatement
+1.executeUpdate();%0A%0A                preparedStatement2 = connection.prepareStatement(%22INSERT INTO post (id) VALUES (:1)%22);%0A                preparedStatement2.setLong(1, 2L);%0A                preparedStatement2
 .execute
@@ -1955,32 +1955,39 @@
 ment(INSERT_POST
+_PREFIX
 );%0A             
@@ -2002,17 +2002,17 @@
 tEquals(
-1
+2
 , openCu
@@ -2164,16 +2164,17 @@
 tatement
+1
  != null
@@ -2214,16 +2214,133 @@
 tatement
+1.close();%0A                %7D%0A                if (preparedStatement2 != null) %7B%0A                    preparedStatement2
 .close()
@@ -2388,17 +2388,17 @@
 tEquals(
-1
+2
 , getOpe
@@ -2421,32 +2421,39 @@
 ment(INSERT_POST
+_PREFIX
 ).size());%0A     
@@ -2451,33 +2451,56 @@
 ));%0A            
+   
-%7D
+ connection.commit();
 %0A        %7D);%0A   
@@ -2488,28 +2488,24 @@
 ();%0A        
-%7D);%0A
         asse
@@ -2554,16 +2554,23 @@
 ERT_POST
+_PREFIX
 ).size()
@@ -2568,24 +2568,50 @@
 X).size());%0A
+            %7D%0A        %7D);%0A
     %7D%0A%0A    p
@@ -2666,16 +2666,22 @@
 ring sql
+Prefix
 ) %7B%0A    
@@ -2879,41 +2879,8 @@
 ORS%22
-, %0A                    user_name 
 %0A   
@@ -2937,16 +2937,61 @@
  WHERE %0A
+                    user_name= 'ORACLE' AND %0A
         
@@ -3015,14 +3015,23 @@
 ext 
-=
+LIKE
  :sql
+Prefix
 %0A   
@@ -3243,14 +3243,32 @@
 %22sql
-%22, sql
+Prefix%22, sqlPrefix + %22%25%22
 )%0A  

Change test to use two PreparedStatements and disable the session_cached_cursors setting