DB 접속유지 검사용 validationQuery를 아시나요?

validationQuery 설정

 

ibatis에서 DB접속 연결 유지용으로 사용하는 validationQuery 설정문제로 아래와 같은 오류가 발생햇어요.

java.sql.SQLException: Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

 

oracle과 postgresql의 기본 쿼리요건이 좀 차이가 있어요

select 1 from dual   vs  select 1

 

전자정부프레임워크에서 기존 postgresql DB를 사용하다가

다른 DB접속이 필요해서 다른 DB연결설정을 셋팅하던중.

위 오류로 뻘짓을 좀했네요.

 

아래 validationQuery(DB연결유지 확인용 쿼리)값이 postgresql과 oracle에 각각 맞도록 잘 수정해서

오류를 해결했어요.ㅠㅠ

 

 

 

    <!-- postgresql -->
    <bean id="dataSource-postgresql" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${Globals.DriverClassName}"/>
        <property name="url" value="${Globals.Url}" />
        <property name="username" value="${Globals.UserName}"/>
        <property name="password" value="${Globals.Password}"/>
        <property name="initialSize" value="10"/>
        <property name="maxTotal" value="100"/>
        <property name="maxIdle" value="10"/>
        <property name="maxWaitMillis" value="10000"/>      
        <property name="validationQuery" value="select 1" />
    </bean>
    
    <!-- oracle -->
    <alias name="dataSource-oracle" alias="oracle.dataSource"/>
    <bean id="dataSource-oracle" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${Oracle.Globals.Db.DriverClassName}"/>
        <property name="url" value="${Oracle.Globals.Db.Url}" />
        <property name="username" value="${Oracle.Globals.Db.UserName}"/>
        <property name="password" value="${Oracle.Globals.Db.Password}"/>
        <property name="initialSize" value="10"/>
        <property name="maxTotal" value="100"/>
        <property name="maxIdle" value="10"/>
        <property name="maxWaitMillis" value="10000"/>      
        <property name="validationQuery" value="select 1 from dual" />
    </bean>    

 

oracle

<property name="validationQuery" value="select 1 from dual" />

 

postgresql

<property name="validationQuery" value="select 1" />