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
35
36 package fr.paris.lutece.plugins.graphite.business;
37
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.Collection;
43
44
45
46
47
48
49 public final class CategoryDAO implements ICategoryDAO
50 {
51
52
53 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_category ) FROM graphite_category";
54 private static final String SQL_QUERY_SELECT = "SELECT id_category, category_title, category_role, category_workgroup, display_front, display_back FROM graphite_category WHERE id_category = ?";
55 private static final String SQL_QUERY_INSERT = "INSERT INTO graphite_category ( id_category, category_title, category_role, category_workgroup, display_front, display_back ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
56 private static final String SQL_QUERY_DELETE = "DELETE FROM graphite_category WHERE id_category = ? ";
57 private static final String SQL_QUERY_UPDATE = "UPDATE graphite_category SET id_category = ?, category_title = ?, category_role = ?, category_workgroup = ?, display_front = ?, display_back = ? WHERE id_category = ?";
58 private static final String SQL_QUERY_SELECTALL = "SELECT id_category, category_title, category_role, category_workgroup, display_front, display_back FROM graphite_category";
59
60
61
62
63
64
65
66
67 public int newPrimaryKey( Plugin plugin)
68 {
69 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK , plugin );
70 daoUtil.executeQuery( );
71
72 int nKey = 1;
73
74 if( daoUtil.next( ) )
75 {
76 nKey = daoUtil.getInt( 1 ) + 1;
77 }
78
79 daoUtil.free();
80
81 return nKey;
82 }
83
84
85
86
87
88
89
90 @Override
91 public void insert( Category category, Plugin plugin )
92 {
93 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
94
95 category.setIdCategory( newPrimaryKey( plugin ) );
96
97 daoUtil.setInt( 1, category.getIdCategory( ) );
98 daoUtil.setString( 2, category.getCategoryTitle( ) );
99 daoUtil.setString( 3, category.getCategoryRole( ) );
100 daoUtil.setString( 4, category.getWorkgroup( ) );
101 daoUtil.setInt( 5, category.getDisplayFront( ) );
102 daoUtil.setInt( 6, category.getDisplayBack( ) );
103
104 daoUtil.executeUpdate( );
105 daoUtil.free( );
106 }
107
108
109
110
111
112 @Override
113 public Category load( int nKey, Plugin plugin )
114 {
115 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
116 daoUtil.setInt( 1 , nKey );
117 daoUtil.executeQuery( );
118
119 Category category = null;
120
121 if ( daoUtil.next( ) )
122 {
123 category = new Category();
124 category.setIdCategory( daoUtil.getInt( 1 ) );
125 category.setCategoryTitle( daoUtil.getString( 2 ) );
126 category.setCategoryRole( daoUtil.getString( 3 ) );
127 category.setCategoryWorkgroup( daoUtil.getString( 4 ) );
128 category.setDisplayFront( daoUtil.getInt( 5 ) );
129 category.setDisplayBack( daoUtil.getInt( 6 ) );
130 }
131
132 daoUtil.free( );
133 return category;
134 }
135
136
137
138
139
140 @Override
141 public void delete( int nCategoryId, Plugin plugin )
142 {
143 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
144 daoUtil.setInt( 1 , nCategoryId );
145 daoUtil.executeUpdate( );
146 daoUtil.free( );
147 }
148
149
150
151
152
153 @Override
154 public void store( Category category, Plugin plugin )
155 {
156 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
157
158 daoUtil.setInt( 1, category.getIdCategory( ) );
159 daoUtil.setString( 2, category.getCategoryTitle( ) );
160 daoUtil.setString( 3, category.getCategoryRole( ) );
161 daoUtil.setString( 4, category.getWorkgroup( ) );
162 daoUtil.setInt( 5, category.getDisplayFront( ) );
163 daoUtil.setInt( 6, category.getDisplayBack( ) );
164 daoUtil.setInt( 7, category.getIdCategory( ) );
165
166 daoUtil.executeUpdate( );
167 daoUtil.free( );
168 }
169
170
171
172
173
174
175 @Override
176 public Collection<Category> selectCategorysList( Plugin plugin )
177 {
178 Collection<Category> categoryList = new ArrayList<Category>( );
179 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
180 daoUtil.executeQuery( );
181
182 while ( daoUtil.next( ) )
183 {
184 Category category = new Category( );
185
186 category.setIdCategory( daoUtil.getInt( 1 ) );
187 category.setCategoryTitle( daoUtil.getString( 2 ) );
188 category.setCategoryRole( daoUtil.getString( 3 ) );
189 category.setCategoryWorkgroup( daoUtil.getString( 4 ) );
190 category.setDisplayFront( daoUtil.getInt( 5 ) );
191 category.setDisplayBack( daoUtil.getInt( 6 ) );
192
193 categoryList.add( category );
194 }
195
196 daoUtil.free( );
197 return categoryList;
198 }
199
200 }