Project

General

Profile

1
package eu.dnetlib.openaire.user.dao;
2

    
3
import eu.dnetlib.openaire.user.UserVerification;
4
import eu.dnetlib.openaire.user.store.DataSourceConnector;
5
import eu.dnetlib.openaire.user.store.Statement;
6
import org.apache.log4j.Logger;
7
import org.springframework.beans.factory.annotation.Autowired;
8
import org.springframework.stereotype.Component;
9

    
10
import javax.sql.DataSource;
11
import java.sql.*;
12
import java.util.ArrayList;
13
import java.util.List;
14

    
15
import static eu.dnetlib.openaire.user.queries.UserVerificationQueries.*;
16

    
17
/**
18
 * Created by sofia on 3/10/2017.
19
 */
20
@Component(value = "userVertificationDao")
21
public class UserVerificationDAO {
22

    
23
    //private final DataSource ds;
24

    
25
    private static final Logger logger = Logger.getLogger(UserVerificationDAO.class);
26

    
27
    @Autowired
28
    private DataSourceConnector dataSourceConnector;
29

    
30
    public UserVerificationDAO() {
31
        //this.ds = null;
32
       // this.ds = dataSourceConnector.getDatasource();
33
    }
34

    
35
    // FETCH
36

    
37
    public List<UserVerification> fetchAll()
38
            throws SQLException
39
    {
40
        return (List<UserVerification>) executeQuery(FETCH_ALL);
41
    }
42

    
43
    public UserVerification fetchById(final int id)
44
            throws SQLException
45
    {
46
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_ID, new Statement.Initializer() {
47
            @Override
48
            public void prepare(PreparedStatement stmt) throws SQLException {
49
                stmt.setInt(1, id);
50
            }
51
        });
52

    
53
        if (userVerifications.isEmpty())
54
            return null;
55

    
56
        return userVerifications.get(0);
57
    }
58

    
59
    public  UserVerification fetchByUsername(final String username)
60
            throws SQLException
61
    {
62
        logger.info("Datasource -> " + dataSourceConnector.getDatasource());
63
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_USERNAME, new Statement.Initializer() {
64
            @Override
65
            public void prepare(PreparedStatement stmt) throws SQLException {
66
                stmt.setString(1, username);
67
            }
68
        });
69

    
70
        if (userVerifications.isEmpty())
71
            return null;
72

    
73
        return userVerifications.get(0);
74
    }
75

    
76
    public UserVerification fetchByVerificationCode(final String verificationCode)
77
            throws SQLException
78
    {
79
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_VERIFICATION_CODE, new Statement.Initializer() {
80
            @Override
81
            public void prepare(PreparedStatement stmt) throws SQLException {
82
                stmt.setString(1, verificationCode);
83
            }
84
        });
85

    
86
        if (userVerifications.isEmpty())
87
            return null;
88

    
89
        return userVerifications.get(0);
90
    }
91

    
92
    public UserVerification fetchByDate(final String date)
93
            throws SQLException
94
    {
95
        List<UserVerification> userVerifications = executeQuery(FETCH_BY_DATE, new Statement.Initializer() {
96
            @Override
97
            public void prepare(PreparedStatement stmt) throws SQLException {
98
                stmt.setDate(1, Date.valueOf(date));
99
            }
100
        });
101

    
102
        if (userVerifications.isEmpty())
103
            return null;
104

    
105
        return userVerifications.get(0);
106
    }
107

    
108
    public long countAll() throws SQLException
109
    {
110
        return executeCount(COUNT_ALL);
111
    }
112

    
113
    public boolean insert(final UserVerification userVerification)
114
            throws SQLException
115
    {
116
        return executeUpdate(INSERT, new Statement.Initializer() {
117
            @Override
118
            public void prepare(PreparedStatement stmt)
119
                    throws SQLException {
120
                stmt.setString(1, userVerification.getUsername());
121
            }
122
        }) > 0;
123

    
124
    }
125

    
126
    public boolean delete(final UserVerification userVerification)
127
            throws SQLException
128
    {
129
        return executeUpdate(DELETE, new Statement.Initializer() {
130
            @Override
131
            public void prepare(PreparedStatement stmt)
132
                    throws SQLException {
133
                stmt.setString(1, userVerification.getUsername());
134
            }
135
        }) > 0;
136
    }
137

    
138
    public boolean update(final UserVerification userVerification)
139
            throws SQLException
140
    {
141
        return executeUpdate(UPDATE, new Statement.Initializer() {
142
            @Override
143
            public void prepare(PreparedStatement stmt)
144
                    throws SQLException {
145
                stmt.setString(1, userVerification.getUsername());
146
            }
147
        }) > 0;
148
    }
149

    
150
    protected UserVerification fromResultSet(ResultSet set)
151
            throws SQLException
152
    {
153
        UserVerification userVerification = new UserVerification(set.getString("username"));
154

    
155
        userVerification.setId(set.getString("id"));
156

    
157
        return userVerification;
158
    }
159

    
160
    protected int executeUpdate(String sql, Statement.Initializer init)
161
            throws SQLException
162
    {
163
        Connection connection = dataSourceConnector.getDatasource().getConnection();
164

    
165
        try {
166
            PreparedStatement stmt = connection.prepareStatement(sql);
167
            init.prepare(stmt);
168
            return stmt.executeUpdate();
169
        } finally {
170
            connection.close();
171
        }
172
    }
173

    
174
    protected List<UserVerification> executeQuery(String sql, Statement.Initializer init)
175
            throws SQLException
176
    {
177
        Connection connection = dataSourceConnector.getDatasource().getConnection();
178

    
179
        try {
180
            PreparedStatement stmt = connection.prepareStatement(sql);
181
            init.prepare(stmt);
182

    
183
            ResultSet set = stmt.executeQuery();
184

    
185
            try {
186
                List<UserVerification> results = new ArrayList<>();
187

    
188
                while (set.next())
189
                    results.add(fromResultSet(set));
190

    
191
                return results;
192
            } finally {
193
                set.close();
194
            }
195
        } finally {
196
            connection.close();
197
        }
198
    }
199

    
200
    protected List<UserVerification> executeQuery(String sql)
201
            throws SQLException
202
    {
203
        return executeQuery(sql, Statement.emptyInitializer());
204
    }
205

    
206
    public long executeCount(String sql)
207
            throws SQLException
208
    {
209
        return executeCount(sql, Statement.emptyInitializer());
210
    }
211

    
212
    public long executeCount(String sql, Statement.Initializer init)
213
            throws SQLException
214
    {
215
        Connection connection = dataSourceConnector.getDatasource().getConnection();
216

    
217
        try {
218
            PreparedStatement stmt = connection.prepareStatement(sql);
219
            init.prepare(stmt);
220

    
221
            ResultSet set = stmt.executeQuery();
222

    
223
            try {
224
                if (set.next())
225
                    return set.getLong(1);
226
                throw new IllegalArgumentException(stmt.toString());
227
            } finally {
228
                set.close();
229
            }
230
        } finally {
231
            connection.close();
232
        }
233
    }
234

    
235

    
236
    public DataSourceConnector getDataSourceConnector() {
237
        return dataSourceConnector;
238
    }
239

    
240
    public void setDataSourceConnector(DataSourceConnector dataSourceConnector) {
241
        this.dataSourceConnector = dataSourceConnector;
242
    }
243

    
244
}
(6-6/6)