H2 database 2.2.224
Java 11.0.1
Spring Framework 4.3.8
Oracle 21c
Hi, I am using H2 database in oracle mode for test codes.
This is to enable test automation on Github Actions, as my local database is not reachable from the outside.
It is configured as such.
<!-- H2 Database Configuration -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.h2.Driver"/>
<property name="url" value="jdbc:h2:mem:testdb;MODE=Oracle;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"/>
<property name="username" value="sa"/>
<property name="password" value=""/>
</bean>
However, while many other queries work just fine in this setting, one of the queries I wrote does not work in H2 settings, although it works fine in Oracle database.
<select id="memberList" resultMap="memberMap">
<![CDATA[
SELECT * FROM (
SELECT rownum as rn, mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate,
(SELECT authority
FROM (SELECT authority, ROW_NUMBER() OVER (ORDER BY CASE authority WHEN 'ROLE_ADMIN' THEN 1 WHEN 'ROLE_MEMBER' THEN 2 ELSE 3 END) as rnk
FROM memberAuthority auth
WHERE auth.memberId = mem.memberId)
WHERE rnk = 1) as authority
FROM (
SELECT mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate
FROM member mem
]]>
<where>
<include refid="criteria"/>
</where>
<![CDATA[
ORDER BY mem.memberId DESC
) mem
WHERE rownum <= #{pageNum} * #{amount}
)
WHERE rn > (#{pageNum} - 1) * #{amount}
]]>
</select>
<sql id="criteria">
<choose>
<when test="type == 'N'.toString()">
nickname LIKE '%' || #{keyword} || '%'
</when>
<when test="type == 'E'.toString()">
memberId LIKE '%' || #{keyword} || '%'
</when>
</choose>
</sql>
This is the test code I made:
@Transactional
@Test
public void readMembersTest() {
//given
MemberDTO mDto = new MemberDTO();
Criteria cri = new Criteria();
//when
mDto.setMemberId("[email protected]");
mDto.setMemberPw("999999");
mDto.setNickname("member1");
service.join(mDto);
mDto.setMemberId("[email protected]");
mDto.setMemberPw("999999");
mDto.setNickname("member2");
service.join(mDto);
List<MemberDTO> members = service.memberList(cri);
//then
Assertions.assertThat(members.size()).isEqualTo(2);
}
This is the error I get:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "MEM.MEMBERID" not found; SQL statement:
SELECT * FROM (
SELECT rownum as rn, mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate,
(SELECT authority
FROM (SELECT authority, ROW_NUMBER() OVER (ORDER BY CASE authority WHEN 'ROLE_ADMIN' THEN 1 WHEN 'ROLE_MEMBER' THEN 2 ELSE 3 END) as rnk
FROM memberAuthority auth
WHERE auth.memberId = mem.memberId)
WHERE rnk = 1) as authority
FROM (
SELECT mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate
FROM member mem
ORDER BY mem.memberId DESC
) mem
WHERE rownum <= ? * ?
)
WHERE rn > (? - 1) * ? [42122-224]
### The error may exist in file [C:UsershanulgitPlantTycoonplanttycoontargetclassesmappersMemberMapper.xml]
### The error may involve kr.co.planttycoon.mapper.MemberMapper.memberList
### The error occurred while executing a query
### SQL: SELECT * FROM ( SELECT rownum as rn, mem.memberId, mem.memberPw, mem.nickname, mem.enabled, mem.lastLogin, mem.regDate, (SELECT authority FROM (SELECT authority, ROW_NUMBER() OVER (ORDER BY CASE authority WHEN 'ROLE_ADMIN' THEN 1 WHEN 'ROLE_MEMBER' THEN 2 ELSE 3 END) as rnk FROM memberAuthority auth WHERE auth.memberId = mem.memberId) WHERE rnk = 1) as authority FROM ( SELECT mem.memberId, mem.memberPw, mem.nickname, mem.enabled, mem.lastLogin, mem.regDate FROM member mem ORDER BY mem.memberId DESC ) mem WHERE rownum <= ? * ? ) WHERE rn > (? - 1) * ?
### Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "MEM.MEMBERID" not found; SQL statement:
SELECT * FROM (
SELECT rownum as rn, mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate,
(SELECT authority
FROM (SELECT authority, ROW_NUMBER() OVER (ORDER BY CASE authority WHEN 'ROLE_ADMIN' THEN 1 WHEN 'ROLE_MEMBER' THEN 2 ELSE 3 END) as rnk
FROM memberAuthority auth
WHERE auth.memberId = mem.memberId)
WHERE rnk = 1) as authority
FROM (
SELECT mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate
FROM member mem
ORDER BY mem.memberId DESC
) mem
WHERE rownum <= ? * ?
)
WHERE rn > (? - 1) * ? [42122-224]
; bad SQL grammar []; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "MEM.MEMBERID" not found; SQL statement:
SELECT * FROM (
SELECT rownum as rn, mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate,
(SELECT authority
FROM (SELECT authority, ROW_NUMBER() OVER (ORDER BY CASE authority WHEN 'ROLE_ADMIN' THEN 1 WHEN 'ROLE_MEMBER' THEN 2 ELSE 3 END) as rnk
FROM memberAuthority auth
WHERE auth.memberId = mem.memberId)
WHERE rnk = 1) as authority
FROM (
SELECT mem.memberId,
mem.memberPw,
mem.nickname,
mem.enabled,
mem.lastLogin,
mem.regDate
FROM member mem
ORDER BY mem.memberId DESC
) mem
WHERE rownum <= ? * ?
)
WHERE rn > (? - 1) * ? [42122-224], mergedContextConfiguration = [MergedContextConfiguration@3cd26422 testClass = MemberJoinTest, locations = '{file:src/test/resources/root-context-test.xml, file:src/main/webapp/WEB-INF/spring/security-context.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextCustomizers = set[[empty]], contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]].
INFO : org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@3e6ef8ad: startup date [Wed Jul 03 18:05:45 KST 2024]; root of context hierarchy
I could not find someone with a similar problem. it does say mem.memberId not found, but I don’t get why it doesn’t. Maybe It’s just a compatibility problem?
Since I’m new to programming, all I could do was ask chatGPT about the issue and search it on internet. Still no clue. At least I know that same SQL works fine in Oracle Database. Maybe I should consider different approach than using in-Memory database like H2?
New contributor
Sangjun Lee is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.