Solution Cours 1 ORM EPSI B3

Requests.java

package fr.ccavalier.hibernate.course.requests;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by charlotte on 20/02/17.
 */
@Repository
public class Requests {

    JdbcTemplate jdbcTemplate;

    @Autowired
    public Requests(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * retourner les numéros et libellés des articles dont le stock est inférieur à 10
     *
     */
    public List findNameQuantityInf10() {
        String sql = "SELECT ID, LIBELLE FROM PRODUITS WHERE STOCK < 10";//Ecrire votre requete ici
        List result = jdbcTemplate.queryForList(sql);
        return result;

    }

    /**
     * retourner les noms et adresses des fournisseurs qui proposent des articles
     * pour lesquels le délai d'approvisionnement est supérieur à 20 jours
     */
    public List findFourDelaiSup20(){
        String sql = "SELECT distinct(f.NOM) , f.VILLE "+
                "FROM FOURNISSEURS f "+
                "INNER JOIN ACHETER a ON f.id = a.id_four "+
                "WHERE a.DELAI > 20";
        List result = jdbcTemplate.queryForList(sql);
        return result;
    }

    /**
     * Supprimer les fraises de la table des produits
     */
    public void deleteFraises(){
        String sql = "Delete from ACHETER WHERE id_prod IN "+
                "(Select id from PRODUITS WHERE LIBELLE = 'Fraises')";//Ecrire votre requete ici
        jdbcTemplate.execute(sql);
        sql = "Delete from PRODUITS WHERE LIBELLE = 'Fraises'";//Ecrire votre requete ici
        jdbcTemplate.execute(sql);
    }

    public List queryForList(String sql){
        return jdbcTemplate.queryForList(sql);
    }

}

User.java

package fr.ccavalier.hibernate.course.mapping;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by ccavalie on 31/01/2017.
 */

public class User {

    Integer id;
    String firstName;
    String lastName;

    String city;
    String email;

    List<Media> contacts;

    // Complete Media class
    static class Media{
        Integer id;
        String type;
        String value;

        public Integer getId() {
            return id;
        }

        public void setId(Integer aId) {
            id = aId;
        }

        public String getType() {
            return type;
        }

        public void setType(String aType) {
            type = aType;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String aValue) {
            value = aValue;
        }
    }

    public List<Media> getContacts() {
        return contacts;
    }

    public void setContacts(List<Media> aContacts) {
        contacts = aContacts;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + firstName + ", city=" + city + "]";
    }

}

UserDao.java

package fr.ccavalier.hibernate.course.mapping;
//[imports] { autofold

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
//}

/**
 * Created by ccavalie on 31/01/2017.
 */
@Repository
public class UserDao {

    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public UserDao(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }


    public User findByFirstName(String name) {

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("name", name);

        String sql = "Select * from users where first_name=:name";//Ecrire votre requete ici pour recuperer le User

        User result = namedParameterJdbcTemplate.queryForObject(
                sql,
                params,
                new UserMapper());

        params.put("user", result.getId());

        result.setContacts(namedParameterJdbcTemplate.query(
                "SELECT m.*, a.contact from Media m "
                        + "inner join media_users a on m.id = a.id_media "
                        + "where a.id_user = :user",
                params,
                new MediaMapper()));

        return result;

    }


    public void add(User user) {
        Map<String, Object> params = new HashMap<String, Object>();
        String sql = "Insert into users(first_name, last_name, address) values(:firstName, :lastName, :address)";
        params.put("firstName", user.getFirstName());
        params.put("lastName", user.getLastName());
        params.put("address", user.getCity());
        int value = namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * get all users from database
     *
     * @return List<User> All Users
     */
    public List<User> findAll() {
        Map<String, Object> params = new HashMap<String, Object>();
        String sql = "Select * from USERS";//write your request here
        List<User> result = namedParameterJdbcTemplate.query(sql, params, new UserMapper());
        return result;

    }


    private static final class UserMapper implements RowMapper<User> {

        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setFirstName(rs.getString("first_name"));
            user.setLastName(rs.getString("last_name"));
            user.setCity(rs.getString("address"));
            return user;
        }
    }

    private static final class MediaMapper implements RowMapper<User.Media> {

        @Override
        public User.Media mapRow(ResultSet resultSet, int i) throws SQLException {
            User.Media media = new User.Media();
            media.setId(resultSet.getInt("id"));
            media.setType(resultSet.getString("type"));
            media.setValue(resultSet.getString("contact"));
            return media;
        }
    }

}