D-Net

package eu.dnetlib.openaire.user.dao;

import eu.dnetlib.openaire.user.MigrationUser;

import eu.dnetlib.openaire.user.store.DataSourceConnector;

import eu.dnetlib.openaire.user.store.Statement;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.stereotype.Component;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import static eu.dnetlib.openaire.user.queries.SQLMigrationUserQueries.*;

/**

 * Created by sofia on 1/11/2016.

 */

@Component(value = "sqlMigrationUserDAO")

public class SQLMigrationUserDAO {

    @Autowired

    private DataSourceConnector dataSourceConnector;

    // FETCH

    public List<MigrationUser> fetchAll()

            throws SQLException

    {

        return (List<MigrationUser>) executeQuery(FETCH_ALL);

    }

    public MigrationUser fetchById(final int id)

            throws SQLException {

        List<MigrationUser> users = executeQuery(FETCH_BY_ID, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setInt(1, id);

            }

        });

        if (users.isEmpty())

            return null;

        return users.get(0);

    }

    public MigrationUser fetchByUsername(final String username)

            throws SQLException

    {

        List<MigrationUser> users = executeQuery(FETCH_BY_USERNAME, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setString(1, username);

            }

        });

        if (users.isEmpty())

            return null;

        return users.get(0);

    }

    public MigrationUser fetchByFullname(final String fullname)

            throws SQLException

    {

        List<MigrationUser> users = executeQuery(FETCH_BY_FULLNAME, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setString(1, fullname);

            }

        });

        if (users.isEmpty())

            return null;

        return users.get(0);

    }

    public MigrationUser fetchByEmail(final String email)

            throws SQLException

    {

        List<MigrationUser> users = executeQuery(FETCH_BY_EMAIL, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setString(1, email);

            }

        });

        if (users.isEmpty())

            return null;

        return users.get(0);

    }

    public List<MigrationUser> fetchByRoleId(String roleId)

            throws SQLException

    {

        return executeQuery(FETCH_BY_ROLE_ID);

    }

    public long countAll() throws SQLException

    {

        return executeCount(COUNT_ALL);

    }

    public boolean insert(final MigrationUser mUser)

            throws SQLException

    {

        return executeUpdate(INSERT, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, mUser.getUsername());

                stmt.setString(2, mUser.getFullname());

                stmt.setString(3, mUser.getEmail());

                stmt.setInt(4, mUser.getRoleId());

            }

        }) > 0;

    }

    public boolean delete(final String username)

            throws SQLException

    {

        return executeUpdate(DELETE, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, username);

            }

        }) > 0;

    }

    public boolean update(final MigrationUser mUser)

            throws SQLException

    {

        return executeUpdate(UPDATE, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, mUser.getUsername());

                stmt.setString(2, mUser.getFullname());

                stmt.setString(3, mUser.getEmail());

                stmt.setInt(4, mUser.getRoleId());

                stmt.setInt(5, mUser.getId());

            }

        }) > 0;

    }

    protected MigrationUser fromResultSet(ResultSet set)

            throws SQLException

    {

        MigrationUser mUser = new MigrationUser(set.getString("username"));

        mUser.setId(set.getInt("id"));

        mUser.setFullname(set.getString("fullname"));

        mUser.setEmail(set.getString("email"));

        mUser.setRoleId(set.getInt("role_id"));

        return mUser;

    }

    protected int executeUpdate(String sql, Statement.Initializer init)

            throws SQLException

    {

        Connection connection = dataSourceConnector.getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            return stmt.executeUpdate();

        } finally {

            connection.close();

        }

    }

    protected List<MigrationUser> executeQuery(String sql, Statement.Initializer init)

            throws SQLException {

        Connection connection = dataSourceConnector.getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {

                List<MigrationUser> results = new ArrayList<>();

                while (set.next())

                    results.add(fromResultSet(set));

                return results;

            } finally {

                set.close();

            }

        } finally {

            connection.close();

        }

    }

    protected int executeUpdate(String sql)

            throws SQLException

    {

        return executeUpdate(sql, Statement.emptyInitializer());

    }

    protected List<MigrationUser> 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 = dataSourceConnector.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;

    }

}