1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package fr.paris.lutece.plugins.quiz.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceItem;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.Collection;
43
44 import org.apache.commons.lang.StringUtils;
45
46
47
48
49
50 public class QuizProfileDAO implements IQuizProfileDAO
51 {
52 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_profil ) FROM quiz_profil ";
53 private static final String SQL_QUERY_INSERT_PROFIL = "INSERT INTO quiz_profil ( id_profil, name, description, id_quiz ) VALUES ( ?, ?, ?, ? )";
54 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_profil, name, description FROM quiz_profil WHERE id_quiz = ? ORDER BY id_profil";
55 private static final String SQL_QUERY_LOAD_PROFIL_BY_ID = "SELECT name FROM quiz_profil WHERE id_profil = ?";
56 private static final String SQL_QUERY_SELECT_PROFIL = "SELECT id_profil, name, description FROM quiz_profil WHERE id_profil = ?";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM quiz_profil WHERE id_profil = ? ";
58 private static final String SQL_QUERY_DELETE_BY_QUIZ = "DELETE FROM quiz_profil WHERE id_quiz = ? ";
59 private static final String SQL_QUERY_UPDATE = " UPDATE quiz_profil SET name = ?, description = ? WHERE id_profil = ? ";
60
61
62
63
64
65
66
67 private int newPrimaryKey( Plugin plugin )
68 {
69 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
70 daoUtil.executeQuery( );
71
72 int nKey;
73
74 if ( !daoUtil.next( ) )
75 {
76
77 nKey = 1;
78 }
79
80 nKey = daoUtil.getInt( 1 ) + 1;
81 daoUtil.free( );
82
83 return nKey;
84 }
85
86
87
88
89 @Override
90 public void insert( QuizProfile profil, Plugin plugin )
91 {
92 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_PROFIL, plugin );
93 profil.setIdProfile( newPrimaryKey( plugin ) );
94
95 daoUtil.setInt( 1, profil.getIdProfile( ) );
96 daoUtil.setString( 2, profil.getName( ) );
97 daoUtil.setString( 3, profil.getDescription( ) );
98 daoUtil.setInt( 4, profil.getIdQuiz( ) );
99
100 daoUtil.executeUpdate( );
101 daoUtil.free( );
102 }
103
104
105
106
107 @Override
108 public ReferenceList selectQuizProfilsReferenceList( int nIdQuiz, Plugin plugin )
109 {
110 ReferenceList profilsReferenceList = new ReferenceList( );
111
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
113 daoUtil.setInt( 1, nIdQuiz );
114
115 daoUtil.executeQuery( );
116
117 while ( daoUtil.next( ) )
118 {
119 ReferenceItem item = new ReferenceItem( );
120 item.setCode( String.valueOf( daoUtil.getInt( 1 ) ) );
121 item.setName( daoUtil.getString( 2 ) );
122 profilsReferenceList.add( item );
123 }
124
125 daoUtil.free( );
126
127 return profilsReferenceList;
128 }
129
130
131
132
133 @Override
134 public String getName( int nIdProfil, Plugin plugin )
135 {
136 String name = StringUtils.EMPTY;
137
138 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_LOAD_PROFIL_BY_ID, plugin );
139 daoUtil.setInt( 1, nIdProfil );
140
141 daoUtil.executeQuery( );
142
143 if ( daoUtil.next( ) )
144 {
145 name = daoUtil.getString( 1 );
146 }
147
148 daoUtil.free( );
149
150 return name;
151 }
152
153
154
155
156 @Override
157 public QuizProfile load( int nKey, Plugin plugin )
158 {
159 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PROFIL, plugin );
160 daoUtil.setInt( 1, nKey );
161 daoUtil.executeQuery( );
162
163 QuizProfile quizProfil = null;
164
165 if ( daoUtil.next( ) )
166 {
167 quizProfil = new QuizProfile( );
168 quizProfil.setIdProfile( daoUtil.getInt( 1 ) );
169 quizProfil.setName( daoUtil.getString( 2 ) );
170 quizProfil.setDescription( daoUtil.getString( 3 ) );
171 }
172
173 daoUtil.free( );
174
175 return quizProfil;
176 }
177
178
179
180
181 @Override
182 public Collection<QuizProfile> findAll( int nIdQuiz, Plugin plugin )
183 {
184 Collection<QuizProfile> result = new ArrayList<QuizProfile>( );
185
186 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
187 daoUtil.setInt( 1, nIdQuiz );
188
189 daoUtil.executeQuery( );
190
191 while ( daoUtil.next( ) )
192 {
193 QuizProfile profil = new QuizProfile( );
194 profil.setIdProfile( daoUtil.getInt( 1 ) );
195 profil.setName( daoUtil.getString( 2 ) );
196 profil.setDescription( daoUtil.getString( 3 ) );
197 result.add( profil );
198 }
199
200 daoUtil.free( );
201
202 return result;
203 }
204
205
206
207
208 @Override
209 public void delete( int nIdProfil, Plugin plugin )
210 {
211 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
212 daoUtil.setInt( 1, nIdProfil );
213 daoUtil.executeUpdate( );
214 daoUtil.free( );
215 }
216
217
218
219
220 @Override
221 public void store( QuizProfile quizProfil, Plugin plugin )
222 {
223 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
224 daoUtil.setString( 1, quizProfil.getName( ) );
225 daoUtil.setString( 2, quizProfil.getDescription( ) );
226 daoUtil.setInt( 3, quizProfil.getIdProfile( ) );
227
228 daoUtil.executeUpdate( );
229
230 daoUtil.free( );
231 }
232
233
234
235
236 @Override
237 public void deleteByQuiz( int nIdQuiz, Plugin plugin )
238 {
239 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_QUIZ, plugin );
240 daoUtil.setInt( 1, nIdQuiz );
241 daoUtil.executeUpdate( );
242 daoUtil.free( );
243 }
244 }