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
- converting input to make it easy to use for database
- access database
- 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:
- have the SQL with named parameters like
:sess_uid
- fill actual parameters (done automatically)
- 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
:
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
- Physical software project structure
- Service layer
- Data access layer
- Spring MVC
- Web view
- Toolbox
- Testing
- Jawr, webjars, bootstrap, Spring setup trick
- Jakarta Equivalence Relation
- Difficult to test example refactoring