Results 1 to 2 of 2
  1. #1
    richteri is offline Member
    Join Date
    Jan 2010
    Posts
    4
    Rep Power
    0

    Question Spring, iBatis and multiple Oracle tablespaces

    Hi there!
    I have a problem with managing multiple Oracle tablespaces (with Spring and iBatis). I have one administration tablespace that stores information about other tablespaces (like their name). Right after a user logs in, selects a tablespace to work with. I store this information in session. The user can only work with one tablespace in a time. These "work-tablespaces" share a common structure, so I try to hide the schema name prefix from my DAO classes.
    Earlier I tried to use $substitution$ syntax of iBatis, but I didn't like the HashMap I had to use as parameter in every query. Now I'm trying to issue an ALTER SESSION SET CURRENT_SCHEMA command, but I'm afraid of its impact on caching, and I couldn't find an elegant solution to run this command before every query.
    I read something about SqlMapClientCallback interface, but I don't know how to wire its implementation to my SqlMapClientTemplate and wether the doInSqlMapClient method of SqlMapClientCallbackImpl would run before or after the queries.
    Could you point me to the right direction how to manage these tablespaces?
    Thanks in advance.
    Istvan

  2. #2
    richteri is offline Member
    Join Date
    Jan 2010
    Posts
    4
    Rep Power
    0

    Arrow

    UPDATE:
    I managed to implement a simple solution, thanks to the flexibility of iBatis:

    Tables:
    ADMIN.PROJECT(id, oracle_ts_name, etc.)
    ADMIN.DATASET(id, project_id, name, description)
    $oracle_ts_name$.DATASET_STRUCTURE(id, dataset_id, variable_name, variable_datatype, etc.)
    $oracle_ts_name$.DS_001(cols are defined in DATASET_STRUCTURE)
    ...
    $oracle_ts_name$.DS_XYZ(cols are defined in DATASET_STRUCTURE)


    I simply have to provide tablespace property to the nested model objects:
    --------------------------------------
    selectDatasetsByProjectId:
    Java Code:
            
    SELECT 
           	d.id,
           	p.oracle_ts_name oracleTsName, 
           	d.name,
           	d.description 
    FROM 
           	${admin.tablespace}.dataset d
           	INNER JOIN ${admin.tablespace}.project p ON (d.project_id=p.id) 
    WHERE project_id = #value#
    ORDER BY d.id

    And then the $substitution$ syntax can be used:
    --------------------------------------
    selectDatasetStructureById
    Java Code:
    SELECT * FROM $oracleTsName$.DATASET_STRUCTURE 
    WHERE dataset_id = #datasetId#
    Everything works fine (including caching), but my next problem will be managing the DS_XYZ tables through Spring powered forms.

    Any suggestion? (I can't change the DB structure :) )

    Thanks

Similar Threads

  1. insert statement return problem in ibatis
    By dheerajsea123 in forum JDBC
    Replies: 1
    Last Post: 05-20-2010, 10:22 AM
  2. innoDB + ibatis
    By vinoopraj in forum Advanced Java
    Replies: 0
    Last Post: 05-19-2009, 09:57 AM
  3. Hibernate vs iBATIS
    By priyanka.dandekar in forum Web Frameworks
    Replies: 3
    Last Post: 11-11-2008, 10:59 AM
  4. Replies: 0
    Last Post: 08-27-2008, 09:27 AM
  5. Tomcat iBatis log4j
    By Tokajac in forum Web Frameworks
    Replies: 0
    Last Post: 07-17-2008, 03:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •