D-Net

package eu.dnetlib.openaire.user.registeredService;

import eu.dnetlib.openaire.user.pojos.RegisteredService;

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

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

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

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;

@Component(value = "registeredServiceSQL")

public class RegisteredServiceSQL implements RegisteredServiceDao {

    @Autowired

    DataSourceConnector dataSourceConnector;

    private final static String INSERT = "INSERT INTO registered_service (" +

            "aai_id, " +

            "owner, " +

            "name, " +

            "creation_date " +

            ") VALUES (?,?,?,?)";

    private final static String UPDATE = "UPDATE registered_service SET " +

            "id = ?, " +

            "aai_id = ?, " +

            "owner = ?, " +

            "name = ?, " +

            "creation_date = ?, " +

            "WHERE id = ?";

    private final static String DELETE = "DELETE FROM registered_service WHERE id = ?";

    private final static String SELECT = "SELECT * FROM registered_service WHERE owner = ?";

    @Override

    public int insertRegisteredService(final RegisteredService registeredService)

            throws SQLException {

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

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, registeredService.getAai_id());

                stmt.setString(2, registeredService.getOwner());

                stmt.setString(3, registeredService.getName());

                stmt.setTimestamp(4, registeredService.getDate());

            }

        });

    }

    @Override

    public boolean update(final RegisteredService registeredService) throws SQLException {

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

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(2, registeredService.getAai_id());

                stmt.setString(3, registeredService.getOwner());

                stmt.setString(4, registeredService.getName());

                stmt.setTimestamp(5, registeredService.getDate());

                stmt.setString(1, registeredService.getId());

            }

        }) > 0;

    }

    public boolean delete(final String id)

            throws SQLException {

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

            @Override

            public void prepare(PreparedStatement stmt)

                    throws SQLException {

                stmt.setString(1, id);

            }

        }) > 0;

    }

    @Override

    public RegisteredService fetchRegisteredServiceById(final int id) throws SQLException {

        System.out.println("SELECT * FROM registered_service WHERE id = " +id);

        List<RegisteredService> registeredServices = executeQuery("SELECT * FROM registered_service WHERE id = ? ", new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setInt(1, id);

            }

        });

        System.out.println("HELLO");

        System.out.println("Regitered service " +registeredServices);

        if (registeredServices.isEmpty()) {

            return null;

        }

        return registeredServices.get(0);

    }

    @Override

    public List<RegisteredService> fetchAllRegisteredServicesByOwner(final String owner) throws SQLException {

        List<RegisteredService> registeredServices = executeQuery(SELECT, new Statement.Initializer() {

            @Override

            public void prepare(PreparedStatement stmt) throws SQLException {

                stmt.setString(1, owner);

            }

        });

        return registeredServices;

    }

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

            throws SQLException {

        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            ResultSet set = stmt.executeQuery();

            try {

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

                while (set.next())

                    results.add(fromResultSet(set));

                return results;

            } finally {

                set.close();

            }

        } finally {

            connection.close();

        }

    }

    protected List<RegisteredService> executeQuery(String sql)

            throws SQLException {

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

    }

    protected RegisteredService fromResultSet(ResultSet set) throws SQLException {

        RegisteredService registeredService = new RegisteredService();

        registeredService.setId(set.getString("id"));

        registeredService.setAai_id(set.getString("aai_id"));

        registeredService.setOwner(set.getString("owner"));

        registeredService.setName(set.getString("name"));

        registeredService.setDate(set.getTimestamp("creation_date"));

        return registeredService;

    }

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

            throws SQLException {

        Connection connection = dataSourceConnector.getDatasource().getConnection();

        try {

            PreparedStatement stmt = connection.prepareStatement(sql);

            init.prepare(stmt);

            return stmt.executeUpdate();

        } finally {

            connection.close();

        }

    }

}