This is my test method:
@RunWithApplicationComposer(mode = ExtensionMode.PER_ALL)
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
public class MyTest{
@PersistenceContext
EntityManager entityManager;
@Resource
private UserTransaction userTransaction;
private DB db;
private void startDb() throws ManagedProcessException {
DBConfigurationBuilder config = DBConfigurationBuilder.newBuilder();
config.setPort(0); // random port
db = DB.newEmbeddedDB(config.build());
db.start();
db.createDB("testdb", "root", "root");
db.source("schemaonly.sql", "root", "root", "testdb");
}
//resources.xml equivalent
@Configuration
public Properties config() throws ManagedProcessException {
startDb();
Properties p = new Properties();
p.put("db", "new://Resource?type=DataSource");
p.put("db.JdbcDriver", "com.mysql.cj.jdbc.Driver");
p.put("db.JdbcUrl", "jdbc:mysql://localhost:"+db.getConfiguration().getPort()+"/testdb?user=root&password=root");
return p;
}
//persistence.xml equivalent
@Module
public PersistenceUnit setupPU(){
PersistenceUnit pu = new PersistenceUnit("testPU");
pu.setProvider(HibernatePersistenceProvider.class);
pu.setJtaDataSource("db");
pu.setProperty("tomee.jpa.factory.lazy", "true");
pu.setProperty("hibernate.format_sql" ,"true");
pu.setProperty("hibernate.use_sql_comments" ,"true");
pu.setProperty("hibernate.dialect" ,"org.hibernate.dialect.MySQL8Dialect");
pu.setProperty("hibernate.physical_naming_strategy",
"org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy");
return pu;
}
@Test
void fetchPost() throws Exception{
userTransaction.begin();
User user = new User();
user.setUsername("username");
user.setAdmin(false);
user.setEmail("email");
user.setPassword("password".getBytes(StandardCharsets.UTF_8));
user.setSalt("salt".getBytes(StandardCharsets.UTF_8));
entityManager.persist(user);
Section section = new Section();
section.setName("section");
entityManager.persist(section);
IntStream.range(1, 10).mapToObj(n -> {
Post post = new Post();
post.setContent("content" + n);
post.setTitle("title" + n);
post.setType(Post.Type.TEXT);
post.setAuthor(user);
post.setSection(section);
return post;
}).forEach(entityManager::persist);
List<Post> from_post = entityManager.createQuery("select p from Post p", Post.class).getResultList();
Assertions.assertEquals(9,from_post.size());
userTransaction.commit();
}
}
I'm using MariaDB4j in order to get a temporary embedded database for my tests. There's no need to clear the database between tests since I'm only doing one test in this class (For demonstration purposes).
I'm also using OpenEJB's application composer in order to run a Java EE container in my tests.
The test begins with an empty schema that gets populated with 1 user, 1 section and 9 posts. The test checks if the retrieved posts are really 9, otherwise the test is failed.
The problem is that i get the following result:
org.opentest4j.AssertionFailedError:
Expected :9
Actual :1
This is what i tried so far:
Having 2 separate transactions: one that persists and one that retrieves. I get the same result
Asserting that the resulting value from select count(p) from Post p is 9: the test passes.
Calling a native query right after the persist operations (tried both inside and outside the transaction) :
entityManager.unwrap(Session.class).doWork(connection -> {
ResultSet resultSet = connection.createStatement().executeQuery("SELECT * From post");
while(resultSet.next()){
System.out.println(resultSet.getString("content"));
}
} );
I get content1 content2 content3 ...
Fetching another entity:
@Test
void fetchUsers() throws Exception {
userTransaction.begin();
IntStream.range(1,10).mapToObj(n -> {
User user = new User();
user.setUsername("username" + n);
user.setAdmin(false);
user.setEmail("email" + n);
user.setPassword("password".getBytes(StandardCharsets.UTF_8));
user.setSalt("salt".getBytes(StandardCharsets.UTF_8));
return user;
}).forEach(entityManager::persist);
userTransaction.commit();
userTransaction.begin();
Assertions.assertEquals(9,entityManager.createQuery("from User",User.class).getResultList().size());
userTransaction.commit();
}
The test misteriously passes. I made sure to have a clean database before any test.
Calling flush() and clear() right after the persist operation. Nothing changes
I execute em.createQuery("select p from Post p", Post.class).getResultList(). It prints 9 titles
I just can't wrap my head around it. What can be the cause (and possibly the solution)?
@Entity
@DynamicUpdate
public class Post implements Serializable {
public enum Type {TEXT, IMG}
@Getter @Setter
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
protected Integer id;
@Setter @Getter
@Column(length = 255, nullable = false)
protected String title;
@Getter @Setter
@Column(columnDefinition = "TEXT", nullable = false)
protected String content;
@Setter @Getter
@Column(nullable = false) @Enumerated(EnumType.STRING)
protected Type type;
@Getter
@Column(nullable = false, updatable = false, insertable = false)
protected Instant creationDate; //generato da sql
@Getter
@Column(name = "votes", nullable = false, insertable = false, updatable = false)
protected Integer votesCount;
@Getter
@Formula("(select count(id) from Comment c where c.post_id = id group by c.post_id)") //native sql ew
protected Integer commentCount;
@Getter @Setter
@ManyToOne(fetch = FetchType.LAZY, optional = false)
protected Section section;
@Getter @Setter
@ManyToOne(fetch = FetchType.LAZY, optional = false)
protected User author;
@OneToMany(mappedBy="post")
@MapKeyJoinColumn(name="user_id", updatable = false, insertable = false)
protected Map<User, PostVote> votes = new HashMap<>();
public PostVote getVote(User user){
return votes.get(user);
}
public Post(){}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Post)) return false;
Post post = (Post) o;
return id != null && id.equals(post.id);
}
@Override
public int hashCode() {
return getClass().hashCode();
}
}
@Entity
public class User implements Serializable {
@Getter @Setter
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
protected Integer id;
@Getter @Setter
@NaturalId(mutable = false) @Column(length = 30, unique = true, nullable = false)
protected String username;
@Getter @Setter
@Column(length = 16, nullable = false)
protected byte[] password;
@Getter @Setter
@Column(length = 16, nullable = false)
protected byte[] salt;
@Getter @Setter
@Column(length = 255, unique = true, nullable = false)
protected String email;
@Getter @Setter
@Column(length = 255)
protected String description;
@Getter @Setter
@Column(length = 4096)
protected String picture;
@Getter
@Column(insertable = false, updatable = false, nullable = false)
protected Instant creationDate;
@Getter @Setter
@Column(nullable = false)
protected Boolean admin;
@OneToMany(mappedBy = "user")
@OrderBy("endTime desc")
protected List<Ban> bans = new ArrayList<>();
public List<Ban> getBans(){
return Collections.unmodifiableList(bans);
}
public User(){}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof User)) return false;
User user = (User) o;
return id.equals(user.id);
}
@Override
public int hashCode() {
return getClass().hashCode();
}
}
@Entity
public class Section implements Serializable {
@Getter @Setter
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
protected Integer id;
@Getter @Setter
@Column(length = 255)
protected String description;
@Getter @Setter
@Column(length = 50, nullable = false, unique = true) @NaturalId(mutable = true)
protected String name;
@Getter @Setter
@Column(length = 4096)
protected String picture;
@Getter @Setter
@Column(length = 4096)
protected String banner;
@OneToMany(mappedBy="section")
@MapKeyJoinColumn(name="user_id", updatable = false, insertable = false)
@LazyCollection(LazyCollectionOption.EXTRA)
protected Map<User, Follow> follows;
public Follow getFollow(User user){
return follows.get(user);
}
public int getFollowCount(){
return follows.size();
}
public Section(){}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Section)) return false;
Section section = (Section) o;
return id.equals(section.id);
}
@Override
public int hashCode() {
return getClass().hashCode();
}
}
The problem was in the Post#commentCount field. It was mistakenly calculated through the @Formula annotation with this native subquery:
SELECT count(id) FROM Comment c WHERE c.post_id = id group by c.post_id
where count(id) is equivalent to count(post.id). The generated query was the following:
select
post0_.id as id1_4_,
post0_.author_id as author_i7_4_,
post0_.content as content2_4_,
post0_.creation_date as creation3_4_,
post0_.section_id as section_8_4_,
post0_.title as title4_4_,
post0_.type as type5_4_,
post0_.votes as votes6_4_,
(select
count(post0_.id)
from
Comment c
where
c.post_id = post0_.id
group by
c.post_id) as formula1_
from
post post0_
I just had to replace count(id) with count(c.id)