package study.u43; import java.sql.DriverManager; import java.sql.SQLException; import java.util.List; import jakarta.persistence.*; import org.hibernate.SessionFactory; import org.springframework.boot.SpringApplication; import org.springframework.boot.WebApplicationType; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.ConfigurableApplicationContext; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Sort; import org.springframework.data.jpa.repository.config.EnableJpaAuditing; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.orm.jpa.SharedEntityManagerCreator; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.support.TransactionTemplate; import study.u43.model.Account; import study.u43.repo.AccountRepository; import study.u43.repo.EmployeeRepository; @SpringBootApplication @EnableJpaAuditing public class JpaLab { static void check(boolean ok, String message) { if (!ok) throw new AssertionError(message); } static void verifyTarget(javax.sql.DataSource ds, String schema) throws Exception { try (var c=ds.getConnection()) { if (!schema.equals(c.getCatalog()) || !c.getMetaData().getURL().startsWith("jdbc:mysql://127.0.0.1:3306/"+schema+"?")) throw new IllegalStateException("refusing non-U43 data source"); } } public static void main(String[] args) throws Exception { if (args.length!=1 || !args[0].matches("study_u43_[0-9a-f]{12}")) throw new IllegalArgumentException("expected unique U43 schema"); String schema=args[0], user=System.getenv().getOrDefault("U43_MYSQL_USER","root"), password=System.getenv().getOrDefault("U43_MYSQL_PASSWORD", ""); String adminUrl="jdbc:mysql://127.0.0.1:3306/?sslMode=DISABLED&allowPublicKeyRetrieval=true&connectTimeout=5000&socketTimeout=5000"; String url="jdbc:mysql://127.0.0.1:3306/"+schema+"?sslMode=DISABLED&allowPublicKeyRetrieval=true&connectTimeout=5000&socketTimeout=5000"; boolean created=false; try { try (var c=DriverManager.getConnection(adminUrl,user,password); var s=c.createStatement()) { s.executeUpdate("CREATE DATABASE `"+schema+"`"); created=true; System.out.println("U43 schema-created="+schema); if ("1".equals(System.getenv("U43_TEST_ABORT_AFTER_CREATE"))) Runtime.getRuntime().halt(77); try(var rs=s.executeQuery("SELECT VERSION()")){rs.next();System.out.println("U43 mysql="+rs.getString(1));} } try (var pool=new com.zaxxer.hikari.HikariDataSource()) { pool.setJdbcUrl(url); pool.setUsername(user); pool.setPassword(password); pool.setMaximumPoolSize(3); try { verifyTarget(pool,schema+"_wrong"); throw new AssertionError("wrong schema accepted"); } catch (IllegalStateException expected) { check(expected.getMessage().contains("refusing"),"wrong failure"); } verifyTarget(pool,schema); SpringApplication app=new SpringApplication(JpaLab.class); app.setWebApplicationType(WebApplicationType.NONE); app.addInitializers(context->{ context.getBeanFactory().registerSingleton("dataSource",pool); context.getEnvironment().getPropertySources().addFirst(new org.springframework.core.env.MapPropertySource("u43-isolated",java.util.Map.of( "spring.sql.init.mode","never", "spring.jpa.hibernate.ddl-auto","none", "spring.jpa.open-in-view","false", "spring.jpa.properties.hibernate.generate_statistics","true", "spring.jpa.properties.hibernate.jdbc.batch_size","3", "spring.jpa.properties.hibernate.order_inserts","true", "spring.main.banner-mode","off"))); }); try(ConfigurableApplicationContext ctx=app.run()) { check(ctx.getBean(javax.sql.DataSource.class)==pool,"unexpected datasource"); verifyTarget(pool,schema); check(ctx.getEnvironment().getProperty("spring.jpa.properties.hibernate.default_catalog")==null,"external Hibernate catalog was not filtered"); System.out.println("U43 target=PASS wrong-schema=rejected actual-schema=verified external-catalog=absent"); JdbcTemplate jdbc=ctx.getBean(JdbcTemplate.class); jdbc.execute("CREATE TABLE u43_account(id BIGINT PRIMARY KEY,version BIGINT NOT NULL,name VARCHAR(80) NOT NULL,created_at DATETIME(6) NOT NULL)"); jdbc.execute("CREATE TABLE u43_department(id BIGINT PRIMARY KEY,label VARCHAR(80) NOT NULL)"); jdbc.execute("CREATE TABLE u43_employee(id BIGINT PRIMARY KEY,name VARCHAR(80) NOT NULL,department_id BIGINT NOT NULL, FOREIGN KEY(department_id) REFERENCES u43_department(id))"); jdbc.execute("CREATE TABLE u43_board(id BIGINT PRIMARY KEY,title VARCHAR(80) NOT NULL)"); jdbc.execute("CREATE TABLE u43_work_item(id BIGINT PRIMARY KEY,title VARCHAR(80) NOT NULL,board_id BIGINT NOT NULL, FOREIGN KEY(board_id) REFERENCES u43_board(id))"); EntityManagerFactory emf=ctx.getBean(EntityManagerFactory.class); check("3".equals(String.valueOf(emf.getProperties().get("hibernate.jdbc.batch_size"))), "batch size not applied"); EntityManager em=SharedEntityManagerCreator.createSharedEntityManager(emf); AccountRepository accounts=ctx.getBean(AccountRepository.class); EmployeeRepository employees=ctx.getBean(EmployeeRepository.class); TransactionTemplate tx=new TransactionTemplate(ctx.getBean(PlatformTransactionManager.class)); tx.executeWithoutResult(status->{ var item=new Account(1,"rollback"); em.persist(item); check(em.contains(item),"new entity not managed"); em.flush(); try(var c=pool.getConnection(); var s=c.createStatement(); var rs=s.executeQuery("SELECT COUNT(*) FROM u43_account WHERE id=1")) { check(c.getTransactionIsolation()==java.sql.Connection.TRANSACTION_REPEATABLE_READ,"unexpected isolation"); rs.next(); check(rs.getInt(1)==0,"uncommitted insert visible to independent connection"); } catch(SQLException e){throw new RuntimeException(e);} status.setRollbackOnly(); }); check(!accounts.existsById(1L),"flush was commit"); tx.executeWithoutResult(status->{ var item=new Account(1,"alpha"); em.persist(item); }); Account initial=accounts.findById(1L).orElseThrow(); long initialVersion=initial.getVersion(); tx.executeWithoutResult(status->{ Account managed=em.find(Account.class,1L); managed.setName("alpha-updated"); }); Account detached=accounts.findById(1L).orElseThrow(); check("alpha-updated".equals(detached.getName()) && detached.getVersion()==initialVersion+1 && detached.getCreatedAt()!=null,"existing entity dirty check/version/audit"); detached.setName("detached-change"); check("alpha-updated".equals(accounts.findById(1L).orElseThrow().getName()),"detached change persisted"); Account merged=tx.execute(status->{ Account result=accounts.save(detached); check(result!=detached && em.contains(result) && !em.contains(detached),"merge managed identity"); return result; }); check(merged!=detached && "detached-change".equals(accounts.findById(1L).orElseThrow().getName()),"merge result or commit"); System.out.println("U43 lifecycle=PASS flush=not-commit isolation=repeatable-read rollback=empty dirty=committed detached=unchanged merge=returned audit=created"); tx.executeWithoutResult(status->{ var board=new study.u43.model.Board(10,"board"); board.add(new study.u43.model.WorkItem(11,"one")); board.add(new study.u43.model.WorkItem(12,"two")); em.persist(board); }); check(jdbc.queryForObject("SELECT COUNT(*) FROM u43_work_item WHERE board_id=10",Long.class)==2L,"cascade persist"); tx.executeWithoutResult(status->{ var board=em.find(study.u43.model.Board.class,10L); board.remove(board.getItems().get(0)); em.flush(); status.setRollbackOnly(); }); check(jdbc.queryForObject("SELECT COUNT(*) FROM u43_work_item WHERE board_id=10",Long.class)==2L,"orphan rollback"); tx.executeWithoutResult(status->{ var board=em.find(study.u43.model.Board.class,10L); board.remove(board.getItems().get(0)); }); check(jdbc.queryForObject("SELECT COUNT(*) FROM u43_work_item WHERE board_id=10",Long.class)==1L,"orphan removal"); System.out.println("U43 mapping=PASS cascade=two orphan-rollback=two orphan-commit=one"); accounts.save(new Account(2,"alpha-two")); accounts.save(new Account(3,"alpha-three")); var page=accounts.findByNameStartingWith("alpha",PageRequest.of(0,1,Sort.by("id"))); check(page.getContent().size()==1 && page.getTotalElements()==2,"repository page count"); check(accounts.findByNameStartingWithOrderById("alpha").size()==2,"projection"); org.springframework.data.jpa.domain.Specification startsAlpha = (root, query, cb) -> cb.like(root.get("name"), "alpha%"); check(accounts.findAll(startsAlpha).size()==2,"specification"); var probe=org.springframework.data.domain.Example.of(new Account(0,"alpha-two"), org.springframework.data.domain.ExampleMatcher.matching().withIgnorePaths("id","version","createdAt")); check(accounts.findAll(probe).size()==1,"query by example"); tx.executeWithoutResult(status->{ Account managed=em.find(Account.class,1L); int changed=accounts.bulkRename(1L,"bulk"); check(changed==1 && "detached-change".equals(managed.getName()),"bulk should leave managed instance stale"); em.clear(); check("bulk".equals(em.find(Account.class,1L).getName()),"clear did not refresh"); status.setRollbackOnly(); }); check("detached-change".equals(accounts.findById(1L).orElseThrow().getName()),"bulk rollback"); System.out.println("U43 repository=PASS derived-page=two projection-spec-qbe=ok bulk=stale clear=refresh rollback=restored"); jdbc.update("INSERT INTO u43_department VALUES(1,'A'),(2,'B'),(3,'C')"); jdbc.update("INSERT INTO u43_employee VALUES(1,'a',1),(2,'b',2),(3,'c',3)"); var statistics=emf.unwrap(SessionFactory.class).getStatistics(); statistics.clear(); tx.executeWithoutResult(status->{var list=employees.findAll(Sort.by("id")); list.forEach(e->check(e.getDepartment().getLabel()!=null,"department"));}); long lazyCount=statistics.getPrepareStatementCount(); statistics.clear(); tx.executeWithoutResult(status->{var list=employees.fetchDepartments(); list.forEach(e->check(e.getDepartment().getLabel()!=null,"fetch department"));}); long fetchCount=statistics.getPrepareStatementCount(); check(lazyCount==4 && fetchCount==1,"unexpected prepare counts "+lazyCount+"/"+fetchCount); System.out.println("U43 fetching=PASS lazy-prepares="+lazyCount+" entitygraph-prepares="+fetchCount); EntityManager first=emf.createEntityManager(), second=emf.createEntityManager(); try { first.getTransaction().begin(); second.getTransaction().begin(); Account a=first.find(Account.class,1L), b=second.find(Account.class,1L); a.setName("winner"); first.getTransaction().commit(); b.setName("loser"); boolean conflict=false; try { second.getTransaction().commit(); } catch(RollbackException expected) { Throwable t=expected; while(t!=null && !(t instanceof OptimisticLockException)) t=t.getCause(); conflict=t!=null; } check(conflict,"expected optimistic lock conflict"); } finally { if(first.getTransaction().isActive())first.getTransaction().rollback(); if(second.getTransaction().isActive())second.getTransaction().rollback(); first.close();second.close(); } check("winner".equals(accounts.findById(1L).orElseThrow().getName()),"optimistic conflict overwrote winner"); tx.executeWithoutResult(status->{ Account current=em.find(Account.class,1L); check("winner".equals(current.getName()),"retry must reread winner"); current.setName("winner-rechecked"); }); check("winner-rechecked".equals(accounts.findById(1L).orElseThrow().getName()),"retry after reread"); System.out.println("U43 optimistic=PASS stale-update=rejected winner=preserved retry=after-reread"); tx.executeWithoutResult(status->{ for(long id=10;id<15;id++) { em.persist(new Account(id,"batch-"+id)); if(id==12){em.flush();em.clear();} } status.setRollbackOnly(); }); check(jdbc.queryForObject("SELECT COUNT(*) FROM u43_account WHERE id BETWEEN 10 AND 14",Long.class)==0L,"flushed batch rollback"); tx.executeWithoutResult(status->{ for(long id=10;id<15;id++) { em.persist(new Account(id,"batch-"+id)); if(id==12){em.flush();em.clear();} } }); check(jdbc.queryForObject("SELECT COUNT(*) FROM u43_account WHERE id BETWEEN 10 AND 14",Long.class)==5L,"batch commit"); System.out.println("U43 batch=PASS configured-size=3 flushed-rollback=0 rerun-commit=5"); EntityManager locker=emf.createEntityManager(); try { locker.getTransaction().begin(); locker.find(Account.class,1L,LockModeType.PESSIMISTIC_WRITE); try(var c=DriverManager.getConnection(url,user,password); var s=c.createStatement()) { c.setAutoCommit(false); s.execute("SET SESSION innodb_lock_wait_timeout=1"); boolean timedOut=false; try { s.executeUpdate("UPDATE u43_account SET name='blocked' WHERE id=1"); } catch(SQLException expected) { timedOut=expected.getErrorCode()==1205; } check(timedOut,"expected MySQL lock timeout 1205"); c.rollback(); locker.getTransaction().commit(); check(s.executeUpdate("UPDATE u43_account SET name='recovered' WHERE id=1")==1,"update after release"); c.commit(); } } finally {if(locker.getTransaction().isActive())locker.getTransaction().rollback();locker.close();} check("recovered".equals(accounts.findById(1L).orElseThrow().getName()),"lock recovery"); check(pool.getHikariPoolMXBean().getActiveConnections()==0,"connection leaked"); System.out.println("U43 pessimistic=PASS mysql-timeout=1205 after-release=updated"); } } } finally { if(created){ try(var c=DriverManager.getConnection(adminUrl,user,password);var s=c.createStatement()){ s.executeUpdate("DROP DATABASE `"+schema+"`"); try(var rs=s.executeQuery("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='"+schema+"'")){check(!rs.next(),"cleanup failed");} } System.out.println("U43 cleanup=PASS own-schema=removed"); } } } }