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.crm.business.demand.category;
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.Collection;
41
42
43
44
45
46
47 public final class CategoryDAO implements ICategoryDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_category ) FROM crm_category";
51 private static final String SQL_QUERY_SELECT = "SELECT id_category, name, description, code FROM crm_category WHERE id_category = ?";
52 private static final String SQL_QUERY_INSERT = "INSERT INTO crm_category ( id_category, name, description, code ) VALUES ( ?, ?, ?, ? ) ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM crm_category WHERE id_category = ? ";
54 private static final String SQL_QUERY_UPDATE = "UPDATE crm_category SET id_category = ?, name = ?, description = ?, code = ? WHERE id_category = ?";
55 private static final String SQL_QUERY_SELECTALL = "SELECT id_category, name, description, code FROM crm_category ORDER BY name ASC";
56 private static final String SQL_QUERY_SELECT_BY_CODE = "SELECT id_category, name, description, code FROM crm_category WHERE code = ?";
57 private static final String SQL_QUERY_SELECT_FIRST_CATEGORY = "SELECT id_category, name, description, code FROM crm_category ORDER BY name ASC LIMIT 1";
58
59
60
61
62 public int newPrimaryKey( Plugin plugin )
63 {
64 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin ) )
65 {
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
78 return nKey;
79 }
80 }
81
82
83
84
85 public synchronized int insert( Category category, Plugin plugin )
86 {
87 int nIndex = 1;
88 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
89 {
90 category.setIdCategory( newPrimaryKey( plugin ) );
91
92 daoUtil.setInt( nIndex++, category.getIdCategory( ) );
93 daoUtil.setString( nIndex++, category.getName( ) );
94 daoUtil.setString( nIndex++, category.getDescription( ) );
95 daoUtil.setString( nIndex++, category.getCode( ) );
96
97 daoUtil.executeUpdate( );
98
99 }
100
101 return category.getIdCategory( );
102 }
103
104
105
106
107 public Category load( int nId, Plugin plugin )
108 {
109 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin ) )
110 {
111 daoUtil.setInt( 1, nId );
112 daoUtil.executeQuery( );
113
114 Category category = null;
115
116 if ( daoUtil.next( ) )
117 {
118 int nIndex = 1;
119 category = new Category( );
120
121 category.setIdCategory( daoUtil.getInt( nIndex++ ) );
122 category.setName( daoUtil.getString( nIndex++ ) );
123 category.setDescription( daoUtil.getString( nIndex++ ) );
124 category.setCode( daoUtil.getString( nIndex++ ) );
125 }
126
127 return category;
128 }
129 }
130
131
132
133
134 public void delete( int nCategoryId, Plugin plugin )
135 {
136 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
137 {
138 daoUtil.setInt( 1, nCategoryId );
139 daoUtil.executeUpdate( );
140 }
141 }
142
143
144
145
146 public void store( Category category, Plugin plugin )
147 {
148 int nIndex = 1;
149 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
150 {
151 daoUtil.setInt( nIndex++, category.getIdCategory( ) );
152 daoUtil.setString( nIndex++, category.getName( ) );
153 daoUtil.setString( nIndex++, category.getDescription( ) );
154 daoUtil.setString( nIndex++, category.getCode( ) );
155 daoUtil.setInt( nIndex++, category.getIdCategory( ) );
156
157 daoUtil.executeUpdate( );
158 }
159 }
160
161
162
163
164 public Collection<Category> selectCategoriesList( Plugin plugin )
165 {
166 Collection<Category> categoryList = new ArrayList<Category>( );
167 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
168 {
169 daoUtil.executeQuery( );
170
171 while ( daoUtil.next( ) )
172 {
173 int nIndex = 1;
174 Categoryins/crm/business/demand/category/Category.html#Category">Category category = new Category( );
175
176 category.setIdCategory( daoUtil.getInt( nIndex++ ) );
177 category.setName( daoUtil.getString( nIndex++ ) );
178 category.setDescription( daoUtil.getString( nIndex++ ) );
179 category.setCode( daoUtil.getString( nIndex++ ) );
180
181 categoryList.add( category );
182 }
183 }
184 return categoryList;
185 }
186
187
188
189
190 public Category selectFirstCategory( Plugin plugin )
191 {
192 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_FIRST_CATEGORY, plugin ) )
193 {
194 daoUtil.executeQuery( );
195
196 Category category = null;
197
198 if ( daoUtil.next( ) )
199 {
200 int nIndex = 1;
201 category = new Category( );
202
203 category.setIdCategory( daoUtil.getInt( nIndex++ ) );
204 category.setName( daoUtil.getString( nIndex++ ) );
205 category.setDescription( daoUtil.getString( nIndex++ ) );
206 category.setCode( daoUtil.getString( nIndex++ ) );
207 }
208
209 return category;
210 }
211 }
212
213
214
215
216 @Override
217 public Category selectByCode( String strCode, Plugin plugin )
218 {
219 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_CODE, plugin ) )
220 {
221 daoUtil.setString( 1 , strCode );
222 daoUtil.executeQuery( );
223 Category category = null;
224
225 if ( daoUtil.next( ) )
226 {
227 category = new Category();
228 int nIndex = 1;
229
230 category.setIdCategory( daoUtil.getInt( nIndex++ ) );
231 category.setName( daoUtil.getString( nIndex++ ) );
232 category.setDescription( daoUtil.getString( nIndex++ ) );
233 category.setCode( daoUtil.getString( nIndex++ ) );
234 }
235
236 return category;
237 }
238 }
239 }