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.dbpage.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.List;
42
43
44
45
46
47 public final class DbPageDatabaseDAO implements IDbPageDatabaseDAO
48 {
49
50 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_page ) FROM dbpage_page ";
51 private static final String SQL_QUERY_SELECT = " SELECT id_page, param_name, title, workgroup_key FROM dbpage_page WHERE id_page = ? ";
52 private static final String SQL_QUERY_INSERT = " INSERT INTO dbpage_page ( id_page, param_name, title, workgroup_key ) VALUES ( ?, ?, ?, ? ) ";
53 private static final String SQL_QUERY_DELETE = " DELETE FROM dbpage_page WHERE id_page = ? ";
54 private static final String SQL_QUERY_UPDATE = " UPDATE dbpage_page SET id_page = ?, param_name = ?, title = ?, workgroup_key = ? WHERE id_page = ? ";
55 private static final String SQL_QUERY_SELECTALL = " SELECT id_page, param_name, title, workgroup_key FROM dbpage_page ";
56 private static final String SQL_QUERY_SELECT_COMBO = " SELECT id_page , param_name FROM dbpage_page ";
57 private static final String SQL_QUERY_SELECT_BY_NAME = "SELECT id_page, param_name, title, workgroup_key FROM dbpage_page where param_name = ? ";
58
59
60
61
62
63
64 private int newPrimaryKey( Plugin plugin )
65 {
66 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
67 daoUtil.executeQuery( );
68
69 int nKey;
70
71 if ( !daoUtil.next( ) )
72 {
73
74 nKey = 1;
75 }
76
77 nKey = daoUtil.getInt( 1 ) + 1;
78 daoUtil.free( );
79
80 return nKey;
81 }
82
83
84
85
86
87
88 public void insert( DbPageDatabase dbPageDatabase, Plugin plugin )
89 {
90 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
91 dbPageDatabase.setId( newPrimaryKey( plugin ) );
92 daoUtil.setInt( 1, dbPageDatabase.getId( ) );
93 daoUtil.setString( 2, dbPageDatabase.getParamName( ) );
94 daoUtil.setString( 3, dbPageDatabase.getTitle( ) );
95 daoUtil.setString( 4, dbPageDatabase.getWorkgroup( ) );
96
97 daoUtil.executeUpdate( );
98 daoUtil.free( );
99 }
100
101
102
103
104
105
106
107 public DbPageDatabase load( int nDbPageDatabaseId, Plugin plugin )
108 {
109 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
110 daoUtil.setInt( 1, nDbPageDatabaseId );
111 daoUtil.executeQuery( );
112
113 DbPageDatabase dbPageDatabase = null;
114
115 if ( daoUtil.next( ) )
116 {
117 dbPageDatabase = new DbPageDatabase( );
118 dbPageDatabase.setId( daoUtil.getInt( 1 ) );
119 dbPageDatabase.setParamName( daoUtil.getString( 2 ) );
120 dbPageDatabase.setTitle( daoUtil.getString( 3 ) );
121 dbPageDatabase.setWorkgroup( daoUtil.getString( 4 ) );
122 }
123
124 daoUtil.free( );
125
126 return dbPageDatabase;
127 }
128
129
130
131
132
133
134
135 public DbPageDatabase loadByName( String strDbPageName, Plugin plugin )
136 {
137 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_NAME, plugin );
138 daoUtil.setString( 1, strDbPageName );
139 daoUtil.executeQuery( );
140
141 DbPageDatabase dbPageDatabase = null;
142
143 if ( daoUtil.next( ) )
144 {
145 dbPageDatabase = new DbPageDatabase( );
146 dbPageDatabase.setId( daoUtil.getInt( 1 ) );
147 dbPageDatabase.setParamName( daoUtil.getString( 2 ) );
148 dbPageDatabase.setTitle( daoUtil.getString( 3 ) );
149 dbPageDatabase.setWorkgroup( daoUtil.getString( 4 ) );
150 }
151
152 daoUtil.free( );
153
154 return dbPageDatabase;
155 }
156
157
158
159
160
161
162 public void delete( int nDbPageDatabaseId, Plugin plugin )
163 {
164 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
165 daoUtil.setInt( 1, nDbPageDatabaseId );
166 daoUtil.executeUpdate( );
167 daoUtil.free( );
168 }
169
170
171
172
173
174
175 public void store( DbPageDatabase dbPageDatabase, Plugin plugin )
176 {
177 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
178 daoUtil.setInt( 1, dbPageDatabase.getId( ) );
179 daoUtil.setString( 2, dbPageDatabase.getParamName( ) );
180 daoUtil.setString( 3, dbPageDatabase.getTitle( ) );
181 daoUtil.setString( 4, dbPageDatabase.getWorkgroup( ) );
182 daoUtil.setInt( 5, dbPageDatabase.getId( ) );
183 daoUtil.executeUpdate( );
184 daoUtil.free( );
185 }
186
187
188
189
190
191
192 public List<DbPageDatabase> selectDbPageDatabaseList( Plugin plugin )
193 {
194 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
195 daoUtil.executeQuery( );
196
197 List<DbPageDatabase> list = new ArrayList<DbPageDatabase>( );
198
199 while ( daoUtil.next( ) )
200 {
201 DbPageDatabase dbPageDatabase = new DbPageDatabase( );
202 dbPageDatabase.setId( daoUtil.getInt( 1 ) );
203 dbPageDatabase.setParamName( daoUtil.getString( 2 ) );
204 dbPageDatabase.setTitle( daoUtil.getString( 3 ) );
205 dbPageDatabase.setWorkgroup( daoUtil.getString( 4 ) );
206 list.add( dbPageDatabase );
207 }
208
209 daoUtil.free( );
210
211 return list;
212 }
213
214
215
216
217
218
219 public ReferenceList selectDbPagesList( Plugin plugin )
220 {
221 ReferenceList dbPageList = new ReferenceList( );
222 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_COMBO, plugin );
223 daoUtil.executeQuery( );
224
225 while ( daoUtil.next( ) )
226 {
227 dbPageList.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
228 }
229
230 daoUtil.free( );
231
232 return dbPageList;
233 }
234 }