User access to another schema in Oracle: Alternative to public synonyms

Consider that there are 2 users in your database having their own schemas, but second user having less permission is required to work on the first schema. Possible solutions :

1 . To  create public/private synonyms

2. Use schema name in the queries.

3. Modify user session to point the first schema. Create a trigger on login.

CREATE OR REPLACE TRIGGER &app_user..APP_USER_LOGON AFTER LOGON ON &app_user..SCHEMA DECLARE

BEGIN

            EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '|| UPPER('&owner_user');

   EXCEPTION

     WHEN OTHERS THEN raise_application_error(-20001, 'Error: ' || SQLERRM );

END ;

Blob Objects With Spring and Hibernate

You have a stream objects, i.e files , and you want to save them to the database using Hibernate and Spring.

First of all, I suggest to create a different object only for saving content. Let’s sayyou have files to save:

public class MyFileContent extends Entity {

  private InputStream content;

  MyFileContent () {
  }

  public MyFileContent(InputStream content) {
   this.content = content;
  }

  public InputStream getContent() {
    return content;
  }

  private void setContent(InputStream content) {
    this.content = content;
   }
}

Here Entity is just a base class including an id, and getter and setter methods for id.

Here your Hibernate user type for blobs.

import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.orm.hibernate3.support.AbstractLobType;

public class BlobUserType extends AbstractLobType {

 public int[] sqlTypes() {
   return new int[] { Types.BLOB };
 }

 public Class returnedClass() {
   return InputStream.class;
 }

 protected Object nullSafeGetInternal(ResultSet rs, String[] names, Object owner, LobHandler lobHandler)
  throws SQLException, HibernateException {
   return lobHandler.getBlobAsBinaryStream(rs, names[0]);
 }

 protected void nullSafeSetInternal(PreparedStatement ps, int index, Object value, LobCreator lobCreator)
  throws SQLException, HibernateException {
   if (value != null) {
    lobCreator.setBlobAsBinaryStream(ps, index, (InputStream) value, -1);
   } else {
    lobCreator.setBlobAsBytes(ps, index, null);
   }
 }
}

And here, your hibernate mappping for MyFileContent class.

<class name="MyFileContent" table="MY_FILE_CONTENT">
 <id name="id" column="ID" type="java.lang.Long" unsaved-value="null">
 <generator class="sequence">
 <param name="sequence">S_MY_FILE_CONTENT</param>
 </generator>
 </id>
 <property name="content" column="CONTENT" type="BlobUserType"/>
</class>

Here, hibernate configuration possibly in your HibernateContext.xml

 <bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" />

 <!-- Hibernate SessionFactory -->
 <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" ..>
   <property name="lobHandler" ref="defaultLobHandler"/>
    ...
 </bean>

For Oracle and Websphere; you need to use this lob handler definition instead of defaultLobHandler. Because of differences in Oracle’s Blob objects.

 <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" >
   <property name="nativeJdbcExtractor" >
       <bean class="org.springframework.jdbc.support.nativejdbc.WebSphereNativeJdbcExtractor"/>
   </property>
 </bean>

And finally use it :

InputStream inputStream = ...;
MyFileContent fileContent = new MyFileContent(inputStream);
myRepository.save(fileContent );

Don’t forget to write a test:)