Index: modules/uoa-user-management/trunk/src/main/java/eu/dnetlib/openaire/user/dao/UserVerificationDAO.java
===================================================================
--- modules/uoa-user-management/trunk/src/main/java/eu/dnetlib/openaire/user/dao/UserVerificationDAO.java	(nonexistent)
+++ modules/uoa-user-management/trunk/src/main/java/eu/dnetlib/openaire/user/dao/UserVerificationDAO.java	(revision 49215)
@@ -0,0 +1,237 @@
+package eu.dnetlib.openaire.user.dao;
+
+import eu.dnetlib.openaire.user.UserVerification;
+import eu.dnetlib.openaire.user.store.DataSourceConnector;
+import eu.dnetlib.openaire.user.store.Statement;
+import org.springframework.beans.factory.annotation.Autowired;
+
+import javax.sql.DataSource;
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
+
+import static eu.dnetlib.openaire.user.queries.UserVerificationQueries.*;
+
+/**
+ * Created by sofia on 3/10/2017.
+ */
+public class UserVerificationDAO {
+
+    private final DataSource ds;
+
+    @Autowired
+    private DataSourceConnector dataSourceConnector;
+
+    public UserVerificationDAO() {
+        this.ds = dataSourceConnector.getDatasource();
+    }
+
+    // FETCH
+
+    public List<UserVerification> fetchAll()
+            throws SQLException
+    {
+        return (List<UserVerification>) executeQuery(FETCH_ALL);
+    }
+
+    public UserVerification fetchById(final int id)
+            throws SQLException
+    {
+        List<UserVerification> userVerifications = executeQuery(FETCH_BY_ID, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt) throws SQLException {
+                stmt.setInt(1, id);
+            }
+        });
+
+        if (userVerifications.isEmpty())
+            return null;
+
+        return userVerifications.get(0);
+    }
+
+    public UserVerification fetchByUsername(final String username)
+            throws SQLException
+    {
+        List<UserVerification> userVerifications = executeQuery(FETCH_BY_USERNAME, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt) throws SQLException {
+                stmt.setString(1, username);
+            }
+        });
+
+        if (userVerifications.isEmpty())
+            return null;
+
+        return userVerifications.get(0);
+    }
+
+    public UserVerification fetchByVerificationCode(final String verificationCode)
+            throws SQLException
+    {
+        List<UserVerification> userVerifications = executeQuery(FETCH_BY_VERIFICATION_CODE, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt) throws SQLException {
+                stmt.setString(1, verificationCode);
+            }
+        });
+
+        if (userVerifications.isEmpty())
+            return null;
+
+        return userVerifications.get(0);
+    }
+
+    public UserVerification fetchByDate(final String date)
+            throws SQLException
+    {
+        List<UserVerification> userVerifications = executeQuery(FETCH_BY_DATE, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt) throws SQLException {
+                stmt.setDate(1, Date.valueOf(date));
+            }
+        });
+
+        if (userVerifications.isEmpty())
+            return null;
+
+        return userVerifications.get(0);
+    }
+
+    public long countAll() throws SQLException
+    {
+        return executeCount(COUNT_ALL);
+    }
+
+    public boolean insert(final UserVerification userVerification)
+            throws SQLException
+    {
+        return executeUpdate(INSERT, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt)
+                    throws SQLException {
+                stmt.setString(1, userVerification.getUsername());
+            }
+        }) > 0;
+
+    }
+
+    public boolean delete(final UserVerification userVerification)
+            throws SQLException
+    {
+        return executeUpdate(DELETE, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt)
+                    throws SQLException {
+                stmt.setString(1, userVerification.getUsername());
+            }
+        }) > 0;
+    }
+
+    public boolean update(final UserVerification userVerification)
+            throws SQLException
+    {
+        return executeUpdate(UPDATE, new Statement.Initializer() {
+            @Override
+            public void prepare(PreparedStatement stmt)
+                    throws SQLException {
+                stmt.setString(1, userVerification.getUsername());
+            }
+        }) > 0;
+    }
+
+    protected UserVerification fromResultSet(ResultSet set)
+            throws SQLException
+    {
+        UserVerification userVerification = new UserVerification(set.getString("username"));
+
+        userVerification.setId(set.getString("id"));
+
+        return userVerification;
+    }
+
+    protected int executeUpdate(String sql, Statement.Initializer init)
+            throws SQLException
+    {
+        Connection connection = ds.getConnection();
+
+        try {
+            PreparedStatement stmt = connection.prepareStatement(sql);
+            init.prepare(stmt);
+            return stmt.executeUpdate();
+        } finally {
+            connection.close();
+        }
+    }
+
+    protected List<UserVerification> executeQuery(String sql, Statement.Initializer init)
+            throws SQLException
+    {
+        Connection connection = ds.getConnection();
+
+        try {
+            PreparedStatement stmt = connection.prepareStatement(sql);
+            init.prepare(stmt);
+
+            ResultSet set = stmt.executeQuery();
+
+            try {
+                List<UserVerification> results = new ArrayList<>();
+
+                while (set.next())
+                    results.add(fromResultSet(set));
+
+                return results;
+            } finally {
+                set.close();
+            }
+        } finally {
+            connection.close();
+        }
+    }
+
+    protected List<UserVerification> executeQuery(String sql)
+            throws SQLException
+    {
+        return executeQuery(sql, Statement.emptyInitializer());
+    }
+
+    public long executeCount(String sql)
+            throws SQLException
+    {
+        return executeCount(sql, Statement.emptyInitializer());
+    }
+
+    public long executeCount(String sql, Statement.Initializer init)
+            throws SQLException
+    {
+        Connection connection = ds.getConnection();
+
+        try {
+            PreparedStatement stmt = connection.prepareStatement(sql);
+            init.prepare(stmt);
+
+            ResultSet set = stmt.executeQuery();
+
+            try {
+                if (set.next())
+                    return set.getLong(1);
+                throw new IllegalArgumentException(stmt.toString());
+            } finally {
+                set.close();
+            }
+        } finally {
+            connection.close();
+        }
+    }
+
+
+    public DataSourceConnector getDataSourceConnector() {
+        return dataSourceConnector;
+    }
+
+    public void setDataSourceConnector(DataSourceConnector dataSourceConnector) {
+        this.dataSourceConnector = dataSourceConnector;
+    }
+
+}
