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.digglike.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 public class ReportedMessageDAO implements IReportedMessageDAO
44 {
45
46 private static final String SQL_FILTER_ID_DIGG_SUBMIT = " id_digg_submit = ? ";
47 private static final String SQL_FILTER_ID_REPORTED_MESSAGE = " id_reported_message = ? ";
48 private static final String SQL_ORDER_BY_DATE = " ORDER BY date_reported DESC ";
49 private static final String SQL_QUERY_NEW_PK = "SELECT MAX( id_reported_message ) FROM digglike_reported_message";
50 private static final String SQL_QUERY_INSERT = "INSERT INTO digglike_reported_message ( id_reported_message,id_digg_submit,date_reported,reported_value)" +
51 " VALUES(?,?,?,?)";
52 private static final String SQL_QUERY_FIND = "SELECT id_reported_message,id_digg_submit,date_reported,reported_value " +
53 "FROM digglike_reported_message WHERE ";
54 private static final String SQL_QUERY_FIND_BY_PRIMARY_KEY = SQL_QUERY_FIND + SQL_FILTER_ID_REPORTED_MESSAGE;
55 private static final String SQL_QUERY_FIND_BY_DIGG_SUBMIT = SQL_QUERY_FIND + SQL_FILTER_ID_DIGG_SUBMIT +
56 SQL_ORDER_BY_DATE;
57 private static final String SQL_QUERY_DELETE_BY_DIGG_SUBMIT = "DELETE FROM digglike_reported_message WHERE " +
58 SQL_FILTER_ID_DIGG_SUBMIT;
59
60
61
62
63 @Override
64 public void insert( ReportedMessage reportedMessage, Plugin plugin )
65 {
66 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
67 reportedMessage.setIdReported( newPrimaryKey( plugin ) );
68 daoUtil.setInt( 1, reportedMessage.getIdReported( ) );
69 daoUtil.setInt( 2, reportedMessage.getDiggSubmit( ).getIdDiggSubmit( ) );
70 daoUtil.setTimestamp( 3, reportedMessage.getDateReported( ) );
71 daoUtil.setString( 4, reportedMessage.getValue( ) );
72 daoUtil.executeUpdate( );
73 daoUtil.free( );
74 }
75
76
77
78
79 @Override
80 public ReportedMessage load( int nKey, Plugin plugin )
81 {
82
83 ReportedMessage reportedMessage = null;
84
85 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_PRIMARY_KEY, plugin );
86 daoUtil.setInt( 1, nKey );
87 daoUtil.executeQuery( );
88
89 if ( daoUtil.next( ) )
90 {
91 reportedMessage = getRow( daoUtil );
92 }
93
94 daoUtil.free( );
95
96 return reportedMessage;
97 }
98
99
100
101
102 @Override
103 public void deleteByDiggSubmit( int nIdDiggSubmit, Plugin plugin )
104 {
105 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_BY_DIGG_SUBMIT, plugin );
106 daoUtil.setInt( 1, nIdDiggSubmit );
107 daoUtil.executeUpdate( );
108 daoUtil.free( );
109 }
110
111
112
113
114 @Override
115 public List<ReportedMessage> selectListByDiggSubmit( int nIdDiggSubmit, Plugin plugin )
116 {
117 List<ReportedMessage> reportedMessageList = new ArrayList<ReportedMessage>( );
118
119 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_DIGG_SUBMIT, plugin );
120 daoUtil.setInt( 1, nIdDiggSubmit );
121 daoUtil.executeQuery( );
122
123 while ( daoUtil.next( ) )
124 {
125 reportedMessageList.add( getRow( daoUtil ) );
126 }
127
128 daoUtil.free( );
129
130 return reportedMessageList;
131 }
132
133
134
135
136
137
138
139 private int newPrimaryKey( Plugin plugin )
140 {
141 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
142 daoUtil.executeQuery( );
143
144 int nKey;
145
146 if ( !daoUtil.next( ) )
147 {
148
149 nKey = 1;
150 }
151
152 nKey = daoUtil.getInt( 1 ) + 1;
153 daoUtil.free( );
154
155 return nKey;
156 }
157
158
159
160
161
162
163 private ReportedMessage getRow( DAOUtil daoUtil )
164 {
165 ReportedMessage reportedMessage = null;
166 DiggSubmit diggSubmit = null;
167
168 reportedMessage = new ReportedMessage( );
169 reportedMessage.setIdReported( daoUtil.getInt( 1 ) );
170
171 diggSubmit = new DiggSubmit( );
172 diggSubmit.setIdDiggSubmit( daoUtil.getInt( 2 ) );
173 reportedMessage.setDiggSubmit( diggSubmit );
174
175 reportedMessage.setDateReported( daoUtil.getTimestamp( 3 ) );
176 reportedMessage.setValue( daoUtil.getString( 4 ) );
177
178 return reportedMessage;
179 }
180 }