Announcement Announcement Module
Collapse
No announcement yet.
Spring, iBatis and multiple Oracle tablespaces Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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:
    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
    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

    Comment

    Working...
    X