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.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45
46 public final class AnswerDAO implements IAnswerDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_answer ) FROM quiz_answer";
50 private static final String SQL_QUERY_SELECT = "SELECT id_answer, id_question, label_answer, is_valid, id_profil FROM quiz_answer WHERE id_answer = ?";
51 private static final String SQL_QUERY_INSERT = "INSERT INTO quiz_answer ( id_answer, id_question, label_answer, is_valid, id_profil ) VALUES ( ?, ?, ?, ?, ? ) ";
52 private static final String SQL_QUERY_DELETE = "DELETE FROM quiz_answer WHERE id_answer = ? ";
53 private static final String SQL_QUERY_DELETE_ANSWERS_BY_QUESTION = "DELETE FROM quiz_answer WHERE id_question = ? ";
54 private static final String SQL_QUERY_UPDATE = "UPDATE quiz_answer SET id_answer = ?, id_question = ?, label_answer = ?, is_valid = ?, id_profil = ? WHERE id_answer = ?";
55 private static final String SQL_QUERY_SELECTALL = "SELECT id_answer, id_question, label_answer, is_valid, id_profil FROM quiz_answer WHERE id_question = ? ORDER BY id_answer";
56 private static final String SQL_QUERY_SELECT_BY_PROFIL = "SELECT id_answer FROM quiz_answer WHERE id_profil = ?";
57
58
59
60
61
62
63 public int newPrimaryKey( Plugin plugin )
64 {
65 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
66 daoUtil.executeQuery( );
67
68 int nKey;
69
70 if ( !daoUtil.next( ) )
71 {
72
73 nKey = 1;
74 }
75
76 nKey = daoUtil.getInt( 1 ) + 1;
77 daoUtil.free( );
78
79 return nKey;
80 }
81
82
83
84
85
86
87
88 public void insert( int nIdQuestion, Answer answer, Plugin plugin )
89 {
90 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
91
92 answer.setIdAnswer( newPrimaryKey( plugin ) );
93
94 daoUtil.setInt( 1, answer.getIdAnswer( ) );
95 daoUtil.setInt( 2, answer.getIdQuestion( ) );
96 daoUtil.setString( 3, answer.getLabelAnswer( ) );
97 daoUtil.setInt( 4, answer.getValid( ) );
98 daoUtil.setInt( 5, answer.getIdProfile( ) );
99
100 daoUtil.executeUpdate( );
101 daoUtil.free( );
102 }
103
104
105
106
107
108
109
110 public Answer load( int nId, Plugin plugin )
111 {
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
113 daoUtil.setInt( 1, nId );
114 daoUtil.executeQuery( );
115
116 Answer answer = null;
117
118 if ( daoUtil.next( ) )
119 {
120 answer = new Answer( );
121
122 answer.setIdAnswer( daoUtil.getInt( 1 ) );
123 answer.setIdQuestion( daoUtil.getInt( 2 ) );
124 answer.setLabelAnswer( daoUtil.getString( 3 ) );
125 answer.setValid( daoUtil.getInt( 4 ) );
126 answer.setIdProfile( daoUtil.getInt( 5 ) );
127 }
128
129 daoUtil.free( );
130
131 return answer;
132 }
133
134
135
136
137
138
139 public void delete( int nAnswerId, Plugin plugin )
140 {
141 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
142 daoUtil.setInt( 1, nAnswerId );
143 daoUtil.executeUpdate( );
144 daoUtil.free( );
145 }
146
147
148
149
150
151
152 public void store( Answer answer, Plugin plugin )
153 {
154 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
155
156 daoUtil.setInt( 1, answer.getIdAnswer( ) );
157 daoUtil.setInt( 2, answer.getIdQuestion( ) );
158 daoUtil.setString( 3, answer.getLabelAnswer( ) );
159 daoUtil.setInt( 4, answer.getValid( ) );
160 daoUtil.setInt( 5, answer.getIdProfile( ) );
161 daoUtil.setInt( 6, answer.getIdAnswer( ) );
162
163 daoUtil.executeUpdate( );
164 daoUtil.free( );
165 }
166
167
168
169
170
171
172
173 public List<Answer> selectAnswersList( int nIdQuestion, Plugin plugin )
174 {
175 List<Answer> answerList = new ArrayList<Answer>( );
176 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
177 daoUtil.setInt( 1, nIdQuestion );
178 daoUtil.executeQuery( );
179
180 while ( daoUtil.next( ) )
181 {
182 Answer answer = new Answer( );
183
184 answer.setIdAnswer( daoUtil.getInt( 1 ) );
185 answer.setIdQuestion( daoUtil.getInt( 2 ) );
186 answer.setLabelAnswer( daoUtil.getString( 3 ) );
187 answer.setValid( daoUtil.getInt( 4 ) );
188 answer.setIdProfile( daoUtil.getInt( 5 ) );
189
190 answerList.add( answer );
191 }
192
193 daoUtil.free( );
194
195 return answerList;
196 }
197
198
199
200
201
202
203 public void deleteAnswersByQuestion( int nIdQuestion, Plugin plugin )
204 {
205 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ANSWERS_BY_QUESTION, plugin );
206 daoUtil.setInt( 1, nIdQuestion );
207 daoUtil.executeUpdate( );
208 daoUtil.free( );
209 }
210
211
212
213
214
215
216
217
218 public boolean isAnswersWithProfil( int nIdProfil, Plugin plugin )
219 {
220 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_PROFIL, plugin );
221 daoUtil.setInt( 1, nIdProfil );
222 daoUtil.executeQuery( );
223
224 boolean result = false;
225
226 if ( daoUtil.next( ) )
227 {
228 result = true;
229 }
230
231 daoUtil.free( );
232
233 return result;
234 }
235 }