Data access layer

Plain resource related operations. Most of the time these resources are database related ones (hibernate, stored procedure, plain sql) but sometimes it is something else (like accessing a third party service).

Dao do not take care of transactional boundaries. Dao is not responsible for any business operation.

It is taking care of

  1. converting input to make it easy to use for database
  2. access database
  3. transform raw database result to more business oriented one.

Spring JDBC

Spring JDBC support simplifies database access dramatically. Less code and safer implementation.

Using spring JDBC template you should not take care of opening and closing resources. No need to take care of looping through result set.

Good start to setup a dao:

 1@Repository
 2public class SessionDao extends BaseDaoImpl<PericlesSession, Long> implements ISessionDao {
 3  protected NamedParameterJdbcTemplate namedTemplate;
 4  protected JdbcTemplate jdbcTemplate;
 5  protected DataSource dataSource;
 6  @Autowired
 7  public void setDataSource(DataSource ds) {
 8        this.dataSource = ds;
 9        this.namedTemplate = new NamedParameterJdbcTemplate(ds);
10        this.jdbcTemplate = new JdbcTemplate(ds);
11  }
12}

Simple JDBC aka plain SQL

Typical example:

 1@Override
 2public List<ServiceAssignmentDto> getServiceAssignments(Long sess_uid) {
 3        //@formatter:off
 4        final String sql =
 5          "        SELECT "+ 
 6          "        SA.SOURCE_UID as lang_uid, "+ 
 7          "        SA.START_DATE,  "+
 8          "        SA.END_DATE,  "+
 9          "        SA.SRAS_UID as service_assignment_uid, "+ 
10          "        SA.ASST_ASST_UID as assignment_status_uid,  "+
11          "        SA.TOWN_TOWN_UID as town_TOWN_UID,  "+
12          "        SA.INST_INST_UID as inst_inst_uid,  "+
13          "        li.SERL_UID request_line_uid,  "+
14          "        li.REST_REST_UID as request_status_uid, "+
15          "        S.SESS_UID,  "+
16          "        ss.comments "+
17          "      FROM Service_Request_Line Li, Sessions_Service SS, Sessions S, SERVICE_ASSIGNMENTS SA "+
18          "      WHERE Li.Sese_sese_uid=ss.Sese_uid "+
19          "      AND Ss.sess_sess_uid  = s.sess_uid "+
20          "      AND LI.SERL_UID       =SA.SERL_SERL_UID "+
21          "      AND SS.SETY_SETY_UID  =1 "+
22          "      and s.sess_uid = :sess_uid ";
23          //@formatter:on
24        List<ServiceAssignmentDto> res = namedTemplate.query(sql, ImmutableMap.of("sess_uid", sess_uid),
25                new BeanPropertyRowMapper<ServiceAssignmentDto>(ServiceAssignmentDto.class));
26        return res;
27}

Steps:

  1. have the SQL with named parameters like :sess_uid
  2. fill actual parameters (done automatically)
  3. declare a mapper (in this case a BeanPropertyRowMapper which is mapping by naming convention.

Another example:

 1@Override
 2public SessionDetail getSessionDetail(Long sess_uid) {
 3        return namedTemplate.queryForObject(SQL_getSessionDetail, new MapSqlParameterSource("sess_uid", sess_uid),
 4                new SessionDetailMapper());
 5}
 6private class SessionDetailMapper implements RowMapper<SessionDetail> {
 7        @Override
 8        public SessionDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
 9          SessionDetail res = new SessionDetail();
10          res.setSess_uid(rs.getLong("sess_uid"));
11          res.setSession_number(rs.getString("session_number"));
12          res.setTitle(AppStrings.replace_simple("{} ({} {})", i18n.text(rs.getString("sc_title"), "EN"),
13          formatIfNotNull(res.getMeeting_start_date(), "dd/MM/yyyy HH:mm"),
14          formatIfNotNull(res.getMeeting_end_date(), "dd/MM/yyyy HH:mm")));
15          ...
16          return res;
17        }
18}

If you could not map result by naming convention or you need some additional logic to add the mapping (like conversion, custom mapping) you could implement you own mapping.

Stored procedure

Basic stored procedures are easy to use. the reals difficulties will rise when you want to deal with Oracle specific features like table and object

It is not 100% automatic. For these operation you need to access the lover level Oracle JDBC driver.

To have it for sure you must to the following.

Declare connection pool implementation explicitly

In Tomcat, when you are not declaring connection pool implementation the default will be used. the default has the same implementation as commons-jdbc but in different packages. To ensure that commons-jdbc will be used you must set factory in context.xml:

1<Resource name="jdbc/PericlesDataSource" auth="Container"
2.....
3  connectionProperties="oracle.jdbc.V8Compatible=true"
4  factory="org.apache.commons.dbcp.BasicDataSourceFactory"
5/> 

We have a utility which gets the innermost driver (many thanks):

  1
  2/* Copyright (c) 2001 - 2007 TOPP - www.openplans.org. All rights reserved.
  3 * This code is licensed under the GPL 2.0 license, availible at the root
  4 * application directory.
  5 */
  6package org.geoserver.data.jdbc;
  7
  8import java.sql.Connection;
  9import java.sql.SQLException;
 10import java.sql.Statement;
 11import java.util.ArrayList;
 12import java.util.List;
 13import java.util.concurrent.CopyOnWriteArrayList;
 14
 15import org.geotools.data.jdbc.datasource.UnWrapper;
 16import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
 17import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor;
 18import org.springframework.jdbc.support.nativejdbc.JBossNativeJdbcExtractor;
 19import org.springframework.jdbc.support.nativejdbc.Jdbc4NativeJdbcExtractor;
 20import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
 21import org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor;
 22import org.springframework.jdbc.support.nativejdbc.WebLogicNativeJdbcExtractor;
 23import org.springframework.jdbc.support.nativejdbc.WebSphereNativeJdbcExtractor;
 24import org.springframework.jdbc.support.nativejdbc.XAPoolNativeJdbcExtractor;
 25
 26/**
 27 * Wires up the rich set of Spring native connection and statements un-wrappers to the
 28 * GeoTools UnWrapper API, whose model is different (they assume you know in advance
 29 * which un-wrapper you'll need, each un-wrapper works only in the environment it was
 30 * designed for)
 31 * @author Andrea Aime - OpenGeo
 32 */
 33public class SpringUnWrapper implements UnWrapper {
 34    
 35    static final List<NativeJdbcExtractor> EXTRACTORS;
 36    
 37    static {
 38        List<NativeJdbcExtractor> extractors = new ArrayList<NativeJdbcExtractor>();
 39        
 40        // some of these extractors will just blow up during initialization if
 41        // the environment does not contain the classes they are looking for, so we
 42        // guard their initialization and just skip them 
 43        try {
 44            extractors.add(new CommonsDbcpNativeJdbcExtractor());
 45        } catch(Throwable e) {};
 46        try {
 47            extractors.add(new JBossNativeJdbcExtractor());
 48        } catch(Throwable e) {};
 49        try {
 50            extractors.add(new Jdbc4NativeJdbcExtractor());
 51        } catch(Throwable e) {};
 52        try {
 53            extractors.add(new SimpleNativeJdbcExtractor());
 54        } catch(Throwable e) {};
 55        try {
 56            extractors.add(new WebLogicNativeJdbcExtractor());
 57        } catch(Throwable e) {};
 58        try {
 59            extractors.add(new WebSphereNativeJdbcExtractor());
 60        } catch(Throwable e) {};
 61        try {
 62            extractors.add(new XAPoolNativeJdbcExtractor());
 63        } catch(Throwable e) {};
 64        try {
 65            extractors.add(new C3P0NativeJdbcExtractor());
 66        } catch(Throwable e) {};
 67        
 68        // use a concurrent enabled data structure so that we can modify
 69        // the order of extractors at run time, in a way that the extractors
 70        // that can actually do the work end up first (the code is executed in
 71        // tight loops over features and handling over and over exceptions is expensive)
 72        EXTRACTORS = new CopyOnWriteArrayList<NativeJdbcExtractor>(extractors);
 73    }
 74    
 75
 76    public boolean canUnwrap(Connection conn) {
 77        Connection unwrapped = unwrapInternal(conn);
 78        return unwrapped != null;
 79    }
 80
 81    public Connection unwrap(Connection conn) {
 82        Connection unwrapped = unwrapInternal(conn);
 83        if(unwrapped != null)
 84            return unwrapped;
 85        else
 86            throw new IllegalArgumentException("This connection is not unwrappable, " +
 87                        "check canUnwrap before calling unwrap");
 88    }
 89
 90    private Connection unwrapInternal(Connection conn) {
 91        for (int i = 0; i < EXTRACTORS.size(); i++) {
 92            NativeJdbcExtractor extractor = EXTRACTORS.get(i);
 93            try {
 94                // the contract is that the original connection is returned
 95                // if unwrapping was not possible
 96                Connection unwrapped = extractor.getNativeConnection(conn);
 97                
 98                if(conn != unwrapped) {
 99                    if(i != 0) {
100                        // move the extractor to the top, so that we don't do
101                        // many useless attempts at unwrapping with the others
102                        // (this code is typically executed for each feature)
103                        EXTRACTORS.add(0, extractor);
104                        EXTRACTORS.remove(i);
105                    }
106                    return unwrapped;
107                }
108            } catch(Throwable t) {
109                // catch a throwable since some of the unwrappers do not blow up
110                // during initialization when the enviroment does not help, but
111                // they do at unwrap time and they throw Error suclasses
112                // We just want to skip the unwrapper and move on
113            }
114        }
115        return null;
116    }
117    
118    public boolean canUnwrap(Statement st) {
119        Statement unwrapped = unwrapInternal(st);
120        return unwrapped != null;
121    }
122
123    public Statement unwrap(Statement statement) {
124        Statement unwrapped = unwrapInternal(statement);
125        if(unwrapped != null)
126            return unwrapped;
127        else
128            throw new IllegalArgumentException("This statement is not unwrappable, " +
129                "check canUnwrap before calling unwrap");
130    }
131    
132    private Statement unwrapInternal(Statement st) {
133        for (int i = 0; i < EXTRACTORS.size(); i++) {
134            NativeJdbcExtractor extractor = EXTRACTORS.get(i);
135            try {
136                // the contract is that the original connection is returned
137                // if unwrapping was not possible
138                Statement unwrapped = extractor.getNativeStatement(st);
139                if(st != unwrapped) {
140                    if(i != 0) {
141                        // move the extractor to the beginning, so that we don't do
142                        // many useless attempts at unwrapping with the others
143                        // (this code is typically executed for each feature)
144                        EXTRACTORS.add(0, extractor);
145                        EXTRACTORS.remove(i);
146                    }
147                    
148                    return unwrapped;
149                }
150            } catch(SQLException e) {
151                // no problem, skip it
152            }
153        }
154        return null;
155    }
156}

Stored procedure complex example

  1        /**
  2         * custom sample oracle script
  3        --DELETE AFTER USE
  4        -- custom type
  5        create or replace TYPE "MY_TYPE"
  6        as object(name varchar(255),
  7        value varchar(255));
  8        /
  9
 10        -- array of MY_TYPE
 11        create or replace
 12        TYPE "MY_ARRAY"
 13        as table of MY_TYPE;
 14        /
 15
 16        -- echo like SP, doesn't do too much
 17        create or replace procedure foo(
 18        i_array in MY_ARRAY,
 19        o_array out MY_ARRAY)
 20        as
 21        begin
 22        o_array := MY_ARRAY();
 23        for i in 1 .. i_array.count loop
 24          o_array.extend;
 25          o_array(i) := MY_TYPE(i_array(i).name, i_array(i).value);
 26        end loop;
 27        end;
 28        /
 29         */
 30        public class FooStoredProcedure {
 31          private static final String SP_NAME = "FOO";
 32          private static final String MY_ARRAY = "MY_ARRAY";
 33          private static final String MY_TYPE = "MY_TYPE";
 34          private static final String I_ARRAY = "i_array";
 35          private static final String O_ARRAY = "o_array";
 36
 37          private final StoredProcedure storedProcedure;
 38
 39          public FooStoredProcedure(DataSource dataSource) {
 40                JdbcTemplate template = new JdbcTemplate(dataSource);
 41                template.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());
 42                //storedProcedure = new StoredProcedure(dataSource, SP_NAME) {
 43                storedProcedure = new StoredProcedure(template, SP_NAME) {
 44                  {
 45                        declareParameter(new SqlParameter(I_ARRAY, Types.ARRAY, MY_ARRAY));
 46                        declareParameter(new SqlOutParameter(O_ARRAY, Types.ARRAY, MY_ARRAY, new SqlReturnType() {
 47                          public Object getTypeValue(CallableStatement cs, int paramIndex,
 48                                  int sqlType, String typeName) throws SQLException {
 49                                Connection connection = AppDb.getInnermostDelegate(cs.getConnection());
 50                                Map<String, Class<?>> typeMap = connection.getTypeMap();
 51                                typeMap.put(MY_TYPE, MyType.class);
 52                                return cs.getObject(paramIndex);
 53                          }
 54                        }));
 55                        compile();
 56                  }
 57                };
 58          }
 59
 60          /**
 61           * @return array of {@link MyType} objects or <code>null</code> 
 62           */
 63          public MyType[] execute(final MyType... values) {
 64                Map<String, Object> params = new HashMap<String, Object>();
 65                params.put(I_ARRAY, new AbstractSqlTypeValue() {
 66                  @Override
 67                  protected Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
 68                        con = AppDb.getInnermostDelegate(con);
 69                        ArrayDescriptor descriptor = new ArrayDescriptor(typeName, con);
 70                        return new ARRAY(descriptor, con, values);
 71                  }
 72                });
 73
 74                Map<?, ?> result = storedProcedure.execute(params);
 75                if ((!result.containsKey(O_ARRAY) || result.get(O_ARRAY) == null)) {
 76                  return null;
 77                }
 78                try {
 79                  Object[] resultArray = (Object[]) ((ARRAY) result.get(O_ARRAY)).getArray();
 80                  return Arrays.copyOf(resultArray, resultArray.length, MyType[].class);
 81                }
 82                catch (SQLException e) {
 83                  throw new DataRetrievalFailureException("Unable to retrieve array", e);
 84                }
 85          }
 86
 87          public static class MyType implements SQLData {
 88                private String name;
 89                private String value;
 90                //... getter and setters are omited
 91                public String getSQLTypeName() throws SQLException {
 92                  return MY_TYPE;
 93                }
 94                public void readSQL(SQLInput stream, String typeName) throws SQLException {
 95                  name = stream.readString();
 96                  value = stream.readString();
 97                }
 98                public void writeSQL(SQLOutput stream) throws SQLException {
 99                  stream.writeString(name);
100                  stream.writeString(value);
101                }
102          }
103        }
104
105        public class FooStoredProcedureWithArrayTest extends ASpringServiceTest{
106          @Autowired
107          DataSource dataSource;
108          @Test
109          public void test()throws Exception{
110                FooStoredProcedure p = new FooStoredProcedure(dataSource);
111                MyType in = new MyType();
112                in.setName("Otto");
113                in.setValue("Takacs");
114                MyType[] res = p.execute(in);
115                assertNotNull(res);
116                assertEquals(1, res.length);
117          }
118        }

Technology stack
Jul 03, 2013
comments powered by Disqus

Links

Cool

RSS