View Javadoc

1   package at.ac.tuwien.ifs.bpse.basic.dao;
2   
3   import java.sql.ResultSet;
4   import java.sql.SQLException;
5   import java.util.List;
6   
7   import javax.sql.DataSource;
8   
9   import org.apache.commons.logging.Log;
10  import org.apache.commons.logging.LogFactory;
11  import org.springframework.jdbc.core.RowMapper;
12  import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
13  import org.springframework.transaction.PlatformTransactionManager;
14  import org.springframework.transaction.TransactionStatus;
15  import org.springframework.transaction.support.TransactionCallback;
16  import org.springframework.transaction.support.TransactionTemplate;
17  
18  import at.ac.tuwien.ifs.bpse.basic.domain.Student;
19  
20  /**
21   * Implementation of the Student Data Access Object for JDBC. This class makes
22   * heavy use of the Spring Framework's facilities and provides access to the
23   * data stored in the database. It also defines how this data is mapped from
24   * the application objects to the database tables and vice-versa.
25   * Also see the Bean-Config file 
26   * ({@value at.ac.tuwien.ifs.bpse.basic.helper.Constants#SPRINGBEANS})
27   * for configuration.
28   * 
29   * @author The SE-Team
30   * @version 2.1
31   * @see IStudentDAO
32   */
33  public class JdbcStudentDAO implements IStudentDAO {
34  
35  	/**
36  	 * Retrieves the logger for this class.
37  	 */
38  	private static Log log = LogFactory.getLog(JdbcStudentDAO.class);
39  
40  	/**
41  	 * Provides access to the Datasource, set by setDataSource().
42  	 * Wrapper class for JdbcTemplate providing Java-5-based convenience and exposing
43  	 * only the most commonly required operations.
44  	 */
45  	protected SimpleJdbcTemplate simpleJdbcTemplate = null;
46  
47  	/**
48  	 * Transaction Manager. For encapsulating insert and updates in transaction.
49  	 */
50  	private PlatformTransactionManager transactionManager = null;
51  
52  	/**
53  	 * SQL Query Strings.
54  	 */
55  	private String sql_selectAllStudents = "";
56  	private String sql_selectStudent = "";
57  	private String sql_insertStudent = "";
58  	private String sql_getInsertId = "";
59  	private String sql_updateStudent = "";
60  	private String sql_deleteStudent = "";
61  
62  	/** ******************************************************************* */
63  	/** ******************************************************************* */
64  	/* C O N S T R U C T O R */
65  	/** ******************************************************************* */
66  	/** ******************************************************************* */
67  
68  	public JdbcStudentDAO() {
69  		super();
70  	}
71  
72  	/**
73  	 * The Initialise Method must be called after all bean values are set,
74  	 * particularly the datasource and the transaction manager. This is actually
75  	 * performed by the Spring Framework, which sets first of all, all Java Bean
76  	 * Properties and eventually calls this init method (see bean definition in
77  	 * beans.xml configuration file)
78  	 */
79  	public void init() {
80  		log.info("Initialise StudentDAO");
81  	}
82  
83  	/**
84  	 * The Destroy Method is called by the Spring Framework to end the lifecycle
85  	 * of this bean, but <b>only</b> when the bean is created as singleton.
86  	 * Check the bean definition in beans.xml configuration file for details.
87  	 */
88  	public void destroy() {
89  		log.info("Destroy StudentDAO");
90  	}
91  
92  	/** ******************************************************************* */
93  	/** ******************************************************************* */
94  	/*
95  	 * BEAN SETTERS FOR DEPENDENCY INJECTION
96  	 * 
97  	 * Dependency Injection is a design pattern in which the responsibility for
98  	 * object creation and object linking is removed from the objects themselves
99  	 * and transferred to a factory object. It is a way to achieve loose
100 	 * coupling between objects and results in highly testable objects
101 	 * (controlled unit tests).
102 	 * 
103 	 * Factory Object: (Design Pattern) is an object for creating other objects.
104 	 * Typically a factory has a method for every kind of object it is capable
105 	 * of creating. these methods optionally accept parameters defining how the
106 	 * object is created and then return the created object.
107 	 */
108 	/** ******************************************************************* */
109 	/** ******************************************************************* */
110 
111 	/**
112 	 * Sets the SQL String to get all students.
113 	 * 
114 	 * @param sql_selectAllStudents
115 	 *            SQL Statement as String
116 	 */
117 	public void setSql_selectAllStudents(String sql_selectAllStudents) {
118 		this.sql_selectAllStudents = sql_selectAllStudents;
119 	}
120 
121 	/**
122 	 * Sets the SQL String to get one student with one SQL parameter.
123 	 * 
124 	 * @param sql_selectStudent
125 	 *            SQL Statement as String
126 	 */
127 	public void setSql_selectStudent(String sql_selectStudent) {
128 		this.sql_selectStudent = sql_selectStudent;
129 	}
130 
131 	/**
132 	 * Sets the SQL String to insert one student into the database.
133 	 * 
134 	 * @param sql_insertStudent
135 	 *            SQL Statement as String
136 	 */
137 	public void setSql_insertStudent(String sql_insertStudent) {
138 		this.sql_insertStudent = sql_insertStudent;
139 	}
140 
141 	/**
142 	 * Sets the SQL String to retrieve the ID of the last executed SQL
143 	 * Statement.
144 	 * 
145 	 * @param sql_getInsertId
146 	 *            SQL Statement as String
147 	 */
148 	public void setSql_getInsertId(String sql_getInsertId) {
149 		this.sql_getInsertId = sql_getInsertId;
150 	}
151 
152 	/**
153 	 * Sets the SQL String to update a student.
154 	 * 
155 	 * @param sql_updateStudent
156 	 *            SQL Statement as String
157 	 */
158 	public void setSql_updateStudent(String sql_updateStudent) {
159 		this.sql_updateStudent = sql_updateStudent;
160 	}
161 
162 	/**
163 	 * Sets the SQL String to delete a student.
164 	 * 
165 	 * @param sql_deleteStudent
166 	 *            SQL Statement as String
167 	 */
168 	public void setSql_deleteStudent(String sql_deleteStudent) {
169 		this.sql_deleteStudent = sql_deleteStudent;
170 	}
171 
172 	/**
173 	 * Sets the Datasource to connect to database.
174 	 * 
175 	 * @param dataSource SQL Datasource
176 	 */
177 	public void setDataSource(DataSource dataSource) {
178 		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
179 	}
180 
181 	/**
182 	 * Sets the transaction manager.
183 	 * 
184 	 * @param transactionManager
185 	 *            central interface in Spring's transaction infrastructure
186 	 * @see #transactionManager
187 	 */
188 	public void setTransactionManager(
189 			PlatformTransactionManager transactionManager) {
190 		this.transactionManager = transactionManager;
191 	}
192 
193 	/**
194 	 * Maps data from a database row to an application object.
195 	 * This method is used by all DAO methods which query the database 
196 	 * with an SQL SELECT statement.
197 	 *
198 	 * @since 2.1
199 	 *
200 	 */
201 	public static final class StudentMapper implements RowMapper<Student> {
202 	      public Student mapRow(ResultSet rs, int rowNumber)
203 			throws SQLException {
204 	    	    Student student = new Student();
205 				student.setId(rs.getInt("id"));
206 				student.setMatnr(rs.getString("matnr"));
207 				student.setFirstname(rs.getString("firstname"));
208 				student.setLastname(rs.getString("lastname"));
209 				student.setEmail(rs.getString("email"));
210 				return student;
211 			}
212 	  } 
213 	
214 	/** ******************************************************************* */
215 	/** ******************************************************************* */
216 	/*
217 	 * DAO METHODS
218 	 * 
219 	 * A Data Access Object (DAO) is a component which provides a common
220 	 * interface between the application and one or more data storage devices,
221 	 * such as a database or a file. The advantage of using data access objects
222 	 * is that any business object (which contains application or operation
223 	 * specific details) does not require direct knowledge of the final
224 	 * destination for the information it manipulates. As a result, _if_ it is
225 	 * necessary to change where or how the data is stored, these modifications
226 	 * can be made without needing to change the main application.
227 	 */
228 	/** ******************************************************************* */
229 	/** ******************************************************************* */
230 
231 	public Student getStudent(int id) {
232 		log.info("Get Student ID = " + id);
233 		
234 		List<Student> students = simpleJdbcTemplate.query(sql_selectStudent, new StudentMapper(), id);
235 		//List<Student> students = query_getStudent.execute(id);
236 		if (students.size() == 1) {
237 			Student s = students.get(0);
238 			log.debug("Returning Student \"" + s.getFirstname() + " "
239 					+ s.getLastname() + "\"");
240 			return s;
241 		} else {
242 			log.debug("No Student data");
243 			return null;
244 		}
245 	}
246 
247 	/**
248 	 * Inserts one Student into the database. This method uses a transaction
249 	 * manager for performing two queries in one transaction:
250 	 * <ol>
251 	 * <li> the insert statement is performed adding the dataset to the database
252 	 * (first query)</li>
253 	 * <li> the database then automatically generates a unique ID for this
254 	 * dataset, </li>
255 	 * <li> the second query asks the database <i>which</i> id was used for
256 	 * this particular dataset</li>
257 	 * <li> this ID then is set in the student bean and returned by the function</li>
258 	 * </ol>
259 	 * 
260 	 * @param student
261 	 *            Student object holding data of one student
262 	 * @return unique id generated by database assigned to the newly created
263 	 *         Student
264 	 */
265 	public Student saveStudent(final Student student) {
266 		log.info("Add Student Name = " + student.getFirstname() + " "
267 				+ student.getLastname());
268 		log.debug("Initialise Transaction Manager");
269 		TransactionTemplate tt = new TransactionTemplate(transactionManager);
270 		Object result = tt.execute(new TransactionCallback<Object>() {
271 			public Object doInTransaction(TransactionStatus status) {
272 				// The transaction is run from here
273 				log.debug("Start Transaction");
274 				//query_insertStudent.update(param);
275 				//KeyHolder keyHolder = new GeneratedKeyHolder();
276 				simpleJdbcTemplate.update(sql_insertStudent, student.getMatnr(),
277 						student.getFirstname(), student.getLastname(), student.getEmail());
278 				
279 				/*
280 				 * activate the following error line to create an Error which
281 				 * terminates this method. One will see, that the complete
282 				 * transaction is rolled back, hence the insert statement above
283 				 * is not executed, alternatively the second rollback statement
284 				 * can be activated with the same result which in that case is a
285 				 * manual rollback of the transaction
286 				 */
287 
288 				// if (true) throw new Error("Test Exception");
289 				// or
290 				// status.setRollbackOnly();
291 				/*
292 				 * result from query is a list, actually containing only one row
293 				 * and one column
294 				 */
295 				Integer id = simpleJdbcTemplate.queryForInt(sql_getInsertId);
296 				log.debug("End Transaction");
297 				return id;
298 				/*
299 				 * and the transaction ends here! if no error occurs the
300 				 * transaction is committed by Spring otherwise it is rolled
301 				 * back
302 				 */
303 			}
304 		});
305 		Integer id = (Integer) result;
306 		student.setId(id);
307 		log.info("Return ID from inserted dataset = " + id);
308 		return (id!=0) ? student : null;
309 	}
310 	
311 	public Student updateStudent(Student student) {
312 		return updateStudent(student, student.getId());
313 	}
314 
315 	public Student updateStudent(Student student, long id) {
316 		log.info("Update Student, ID = " + student.getId() + " new ID = "
317 				+ id);
318 		log.debug("Execute Update");
319 		if (simpleJdbcTemplate.update(sql_updateStudent, student.getMatnr(),
320 				student.getFirstname(), student.getLastname(),
321 				student.getEmail(), id) == 1) {
322 			log.debug("Update Successfull");
323 			return student;
324 		}
325 		log.error("Update for Student ID = " + id + " failed.");
326 		return null;
327 	}
328 
329 	/**
330 	 * Retrieves all students from the database. <br>
331 	 * <b>Warning:</b> this type of DAO method would not be used in a real-
332 	 * world application because there may be thousands of students in the
333 	 * database and this method would retrieve them all. <br>
334 	 * This is usually not needed: it will generate a huge load on the database
335 	 * and also require enormous amounts of memory. Morover, there is hardly an
336 	 * application conceivable that needs more than a few dozen datasets at any
337 	 * one time.
338 	 * 
339 	 * @since 1.1
340 	 */
341 	public List<Student> getStudents(SortOrder order) {
342 		log.info("Get all Students order = " + order.toString());
343 		List<Student> students = null;
344 		if (order.equals(SortOrder.StudentId)) {
345 			students = simpleJdbcTemplate.query(sql_selectAllStudents+" order by matnr", new StudentMapper());
346 			log.debug("Student List contains " + students.size() + " students ordered by studentId");
347 		} else if (order.equals(SortOrder.LastName)) {
348 			students = simpleJdbcTemplate.query(sql_selectAllStudents+" order by lastname", new StudentMapper());
349 			log.debug("Student List contains " + students.size() + " students ordered by lastname");
350 		}
351 		return students;
352 	}
353 
354 	public boolean deleteStudent(int id) {
355 		log.info("Delete Student ID = " + id);
356 		log.debug("Initialize SQL Parameters");
357 		final Object[] param = new Object[] { id };
358 		log.debug("Executing SQL");
359 		if (simpleJdbcTemplate.update(sql_deleteStudent, param) == 1) {
360 			log.debug("Deleting successfull");
361 			return true;
362 		}
363 		log.error("Deleting Student ID = " + id + " failed");
364 		return false;
365 	}
366 
367 	public Student getStudentByMatrNr(String arg0) {
368 		// TODO Auto-generated method stub
369 		// TODO Implement this method
370 		return null;
371 	}
372 
373 }