Announcement Announcement Module
Collapse
No announcement yet.
How to connect to a read only database? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to connect to a read only database?

    Hi,

    There is something you could possibly help me with. Does anyone know how to connect and configure spring to a read only database?

    Following the configuration of the connections I've to read/write databases I have in my applicationContext:

    Code:
    <bean id="membersDataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> 
    <property name="jndiName"><value>java:comp/env/jdbc/membersdb</value></property> 
    </bean>
    
        <!--  Members db Session Factory -->
         <bean id="membersSessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">        
            <property name="dataSource"><ref bean="membersDataSource"/></property>
                    <property name="annotatedClasses">
                <list>
                    <value>my.company.model.Members</value>       
                </list>
            </property>
            <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.jdbc.batch_size">0</prop>
                <prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
                <prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
                <prop key="hibernate.connection.autocommit">false</prop>
            </props>
            </property>
        </bean>
    
        <bean id="membersTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
            <property name="sessionFactory"><ref local="membersSessionFactory"/></property>
        </bean>
    
        <bean id="membersService" 
            class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
            <property name="transactionManager"><ref bean="membersTransactionManager"/></property>
            <property name="target">
                <bean class="my.company.service.MembersServiceImpl">
                    <property name="membersDAO"><ref bean="membersDAO"/></property>                
                </bean>
            </property>
            <property name="transactionAttributes">
                <props>
                   <prop key="*">readOnly</prop>               
                </props>
            </property>
        </bean>
    But, that gives me the following exception:
    Code:
    org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00604: error occurred at recursive SQL level 1
    ORA-01552: cannot use system rollback segment for non-system tablespace 'DATA01'
    ORA-06512: at line 19
    )
    	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
    	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
    	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
    Thank you very much for your help.

  • #2
    Hi,
    Yesterday, when reading the transaction documentation at spring docs, I thought about removing the transaction service.

    Code:
    <bean id="membersDataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> 
    <property name="jndiName"><value>java:comp/env/jdbc/membersdb</value></property> 
    </bean>
    
        <!--  Members db Session Factory -->
         <bean id="membersSessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">        
            <property name="dataSource"><ref bean="membersDataSource"/></property>
                    <property name="annotatedClasses">
                <list>
                    <value>my.company.model.Members</value>       
                </list>
            </property>
            <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.jdbc.batch_size">0</prop>
                <prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
                <prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
                <prop key="hibernate.connection.autocommit">false</prop>
            </props>
            </property>
        </bean>
    
    
        <bean id="membersService" 
                    <property name="membersDAO"><ref bean="membersDAO"/></property>                
        </bean>
    But I still get the same exception:
    Code:
    org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00604: error occurred at recursive SQL level 1
    ORA-01552: cannot use system rollback segment for non-system tablespace 'DATA01'
    ORA-06512: at line 19
    )
    	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
    	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
    	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.
    I attempted a raw db connection and that one worked just fine:

    Code:
    public void testConn()
        {
            try
            {
                String driver = "oracle.jdbc.driver.OracleDriver";
                String url = "jdbc:oracle:thin:@host:port:sid";
                String username = "username";
                String password = "pwd";
                Class.forName(driver);
                java.sql.Connection conn = java.sql.DriverManager.getConnection(url, username, password);
                _logger.debug(conn);
    
                Statement st = conn.createStatement();
    
                ResultSet rs = st.executeQuery("SELECT count(*) FROM table1");
                if (rs.next())
                {
    
                    int count = rs.getInt("count(*)");
                    _logger.debug(count);
                }
                rs.close();
                st.close();
                conn.close();
    
            }
    
            catch (Exception ex)
            {
                _logger.error(ex.getMessage());
            }
        }
    Has anyone faced this before? Any suggestions will be greatly appreciated.
    Thanks.

    Comment

    Working...
    X