스프링/--/하이파/ 타이타닉 PassengerRepository.java

 

package com.chatbot.web.passenger;
/*
Variable Notes
* 0-PassengerId, 1-Survived,2-Pclass,3-Name,4-Gender,
* 5-Age,6-SibSp, 7-Parch, 8-Ticket, 9-Fare,
* 10-Cabin, 11-Embarked
1-PassengerId: Primary Key
2-pclass: Ticket class
A proxy for socio-economic status (SES)
1st = Upper
2nd = Middle
3rd = Lower
5-age: Age is fractional if less than 1. If the age is estimated, is it in the form of xx.5
6-sibsp: # of siblings / spouses aboard the Titanic
The dataset defines family relations in this way...
Sibling = brother, sister, stepbrother, stepsister
Spouse = husband, wife (mistresses and fiancés were ignored)
7-parch: # of parents / children aboard the Titanic
The dataset defines family relations in this way...
Parent = mother, father
Child = daughter, son, stepdaughter, stepson
Some children travelled only with a nanny, therefore parch=0 for them.
8-Ticket: Ticket number
9-Fare: Passenger fare
10-Cabin: Cabin number
11-Embarked: Port of Embarkation,  C = Cherbourg, Q = Queenstown, S = Southampton
* */
import lombok.*;
 
import javax.persistence.*;
 
@Entity
@Getter
@Setter
@ToString
@Table(name = "passenger")
@NamedQuery(name="Passenger.findByPclass",
        query = "select e from Passenger e where e.pclass = :pclass")
public class Passenger {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "passenger_id") private int passengerId;
    @Column(name = "survived", nullable = false) private int survived;
    @Column(name = "pclass") private int pclass;
    @Column(name = "name") private String name;
    @Column(name = "gender") private String gender;
    @Column(name = "age") private String age;
    @Column(name = "sib_sp") private String sibSp;
    @Column(name = "parch") private String parch;
    @Column(name = "ticket") private String ticket;
    @Column(name = "fare") private String fare;
    @Column(name = "cabin") private String cabin;
    @Column(name = "embarked") private String embarked;
 
    public Passenger(){}
    @Builder
    public Passenger(int survived,
                     int pclass,
                     String name,
                     String gender,
                     String age,
                     String sibSp,
                     String parch,
                     String ticket,
                     String fare,
                     String cabin,
                     String embarked) {
        this.survived = survived;
        this.pclass = pclass;
        this.name = name;
        this.gender = gender;
        this.age = age;
        this.sibSp = sibSp;
        this.parch = parch;
        this.ticket = ticket;
        this.fare = fare;
        this.cabin = cabin;
        this.embarked = embarked;
    }
}

 

PassengerRepository.java

 

package com.chatbot.web.passenger;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
 
import java.util.List;
import java.util.stream.Stream;
/*
0-PassengerId, 1-Survived,2-Pclass,3-Name,4-Gender,
5-Age,6-SibSp, 7-Parch, 8-Ticket, 9-Fare,
10-Cabin, 11-Embarked
* */
 
public interface PassengerRepository extends JpaRepository<Passenger, Integer>, IPassengerRepository{
    /* 1. 공통 인터페이스 구현은 JpaService 통해 구함
     save(S), findOne(ID), exists(ID), count, delete(T)*/
 
    /* 2. QueryMethod 기능
    And, Or, Is.Equals, Between, LessThan, GreaterThan, GreaterThanEqual,
    After, Before, IsNull, IsNotNull, Like, NotLike, StartingWith,
    EndingWith, Containing, OrderBy, NotIn, TRUE, FALSE, IgnoreCore* */
 
    List<Passenger> findBySurvived(int survived);
    Passenger findByPassengerId(int passengerId);
 
    /* 3. Named Query 구현 */
    List<Passenger> selectByPclass(@Param("pclass") String pclass);
 
    /* 4. Anonoymous Named Query 구현*/
    @Query("select e from Passenger e")
    Stream<Passenger> selectAllStream();
 
    // @Async CompletableFuture<Passenger> selectByName(String name);
 
    List<Passenger> findByEmbarked(String embarked);
 
    @Query(value = "SELECT * FROM Passenger WHERE age = :age", nativeQuery = true)
    List<Passenger> selectByPassenger(@Param("age")String age);
 
    @Query("select e from #{#entityName} e where e.cabin = :cabin or e.embarked = :embarked")
    List<Passenger> selectByCabinAndEmbarked(@Param("cabin") String cabin,
                                @Param("embarked") String embarked);
    @Modifying
    @Query("update Passenger e set e.name = :name where e.age = :age")
    int updateAgeForNameFromPassenger(@Param("name") String name,
                                      @Param("age") String age);
 
    void deleteByPassengerId(String passengerId);
 
    @Modifying
    @Query("delete from Passenger e where e.passengerId = :passengerId")
    void deletePassenger(@Param("passengerId")String passengerId);
 
    @Query("select e from Passenger e")
    List<Passenger> selectAllPassenger();
    /*
    querydsl 구현은 IPassengerRepository 통함
    * */
 
}

 

PassengerRepositoryImpl.java

 

package com.chatbot.web.passenger;
 
import static com.chatbot.web.passenger.QPassenger.passenger;
 
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.querydsl.jpa.impl.JPAUpdateClause;
import com.querydsl.jpa.sql.JPASQLQuery;
import com.querydsl.sql.Configuration;
import com.querydsl.sql.DerbyTemplates;
import com.querydsl.sql.SQLQueryFactory;
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.jpa.JPAExpressions;
import static com.querydsl.core.types.ExpressionUtils.count;
/*
0-PassengerId, 1-Survived,2-Pclass,3-Name,4-Gender,
5-Age,6-SibSp, 7-Parch, 8-Ticket, 9-Fare,
10-Cabin, 11-Embarked
* */
interface PassengerCustomRepository{
/*
querydsl category
1- JPAQuery : EntityManager 담당, JPQL 사용
2- JPAQueryFactory : EntityManager 담당, JPQL 사용
3- JPASQLQuery : EntityManager 담당, SQL 사용
4- SQLQuery : JDBC 담당, SQL 사용
5- SQLQueryFactory : JDBC 담당, SQL 사용
6- JPAUpdateClause : 배치프로그램(수정)
* */
 
    List<Passenger> fetchByName(String name); // 1- JPAQuery
    List<Passenger> fetchByGenderAndGreaterThanAge(String gender, String age); // 1- JPAQuery
    List<Passenger> fetchBySurvived(String survived); // 2- JPAQueryFactory
    List<PassengerCounter> countPassengersByAgeAndEmbarked(String gender, String embarked); // 2- JPAQueryFactory
    List<Passenger> fetchByPclass(String pclass); // 3- JPASQLQuery
    List<Passenger> fetchByGender(String gender); // 5- SQLQueryFactory
    Long updateAge(String passengerId, String age);
}
 
@Repository public class PassengerRepositoryImpl extends QuerydslRepositorySupport implements PassengerCustomRepository {
 
    private final JPAQueryFactory queryFactory;
    private final DataSource dataSource;
 
    PassengerRepositoryImpl(JPAQueryFactory queryFactory, DataSource dataSource){
        super(Passenger.class);
        this.queryFactory = queryFactory;
        this.dataSource = dataSource;
    }
    @Override public List<Passenger> fetchByName(String name) {
        // 1- JPAQuery : EntityManager 담당, JPQL 사용
        return new JPAQuery<Passenger>().select(passenger)
                .from(passenger)
                .where(passenger.name.eq(""))
                .orderBy(passenger.name.desc())
                .fetch();
        // fetch 는 복수의 데이터값 리턴
    }
    @Override public List<Passenger> fetchByGenderAndGreaterThanAge(String gender, String age) {
        // 1- JPAQuery : EntityManager 담당, JPQL 사용
        return new JPAQuery<Passenger>().select(passenger)
                .from(passenger)
                .where(passenger.gender.eq(gender).and(passenger.age.gt(age)))
                .orderBy(passenger.passengerId.asc())
                .fetch();
 
        // where(passenger.gender.eq(gender), (passenger.age.gt(age))) 로 변경 가능
    }
 
    @Override public List<Passenger> fetchBySurvived(String survived) {
        // 2- JPAQueryFactory : EntityManager 담당, JPQL 사용
        return queryFactory.selectFrom(passenger)
                .where(passenger.survived.eq(1))
                .fetch();
    }
 
    @Override public List<PassengerCounter> countPassengersByAgeAndEmbarked(String gender, String embarked) {
        // 2- JPAQueryFactory : 항구별로 승선한 승객 중 성별에 따른 수 출력
        return queryFactory
                .select(Projections.fields(PassengerCounter.class,
                        passenger.embarked.as("embarked"),
                        passenger.gender.as("gender"),
                        ExpressionUtils.as(
                                JPAExpressions.select(count(passenger.passengerId))
                                        .from(passenger)
                                        .where(passenger.gender.eq(gender), passenger.embarked.eq(embarked)),
                                "passengerCounter")
                ))
                .from(passenger)
                .fetch();
        // ExpressionUtils은 Querydsl 내부에서 새로운 Expression을 사용할 수 있도록 지원
        // as를 통해 서브쿼리의 결과물을 alias
    }
 
    @PersistenceContext private EntityManager entityManager;
    @Override public List<Passenger> fetchByPclass(String pclass) {
        // 3- JPASQLQuery : EntityManager 담당, SQL 사용
        return new JPASQLQuery<>(entityManager, new DerbyTemplates())
                .select(passenger).from(passenger)
                .where(passenger.pclass.eq(Integer.parseInt(pclass))).fetch();
    }
 
    @Override public List<Passenger> fetchByGender(String gender) {
        // 5- SQLQueryFactory : JDBC 담당, SQL 사용
        return new SQLQueryFactory(new Configuration(new DerbyTemplates()), dataSource)
                .select(passenger).from(passenger)
                .where(passenger.gender.eq(gender))
                .fetch();
    }
    @Override public Long updateAge(String passengerId, String age){
        return new JPAUpdateClause(entityManager, passenger)
                .where(passenger.passengerId.eq(Integer.valueOf(passengerId)))
                .set(passenger.age, age)
                .execute();
    }
 
}

 

PassengerServiceImpl.java

 

package com.chatbot.web.passenger;
 
import com.chatbot.web.util.Box;
import com.chatbot.web.util.GenericService;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.springframework.stereotype.Service;
// https://bezkoder.com/spring-boot-upload-csv-file/
import java.io.*;
import java.util.List;
import java.util.Optional;
 
interface PassengerService extends GenericService<Passenger> {
    // QueryMethod 구현
    public Box<List<Passenger>> findBySurvived(int survived);
    public void readCsv();
    // Anonoymous Named Query 구현
 
    // Named Query 구현
}
@Service
public class PassengerServiceImpl implements PassengerService{
    private final PassengerRepository repository;
    private final Box<List<Passenger>> passengers;
 
    public PassengerServiceImpl(PassengerRepository repository,
                                Box<List<Passenger>> passengers) {
        this.repository = repository;
        this.passengers = passengers;
    }
 
    @Override public Optional<Passenger> findById(String id) {
        return repository.findById(Integer.parseInt(id));
    }
    @Override public Iterable<Passenger> findAll(){
        return repository.findAll();
    }
    @Override public int count() {
        return (int)repository.count();
    }
    @Override
    public void delete(String id) {
        repository.delete(findById(id).orElse(new Passenger()));
    }
    @Override
    public boolean exists(String passengerId) {
        return repository.existsById(Integer.parseInt(passengerId));
    }
 
    @Override
    public Box<List<Passenger>> findBySurvived(int survived){
        passengers.put((survived == 1)? "Survivors":"Deads",repository.findBySurvived(survived));
        return passengers;
    }
 
    @Override
    public void readCsv(){
        InputStream is = getClass().getResourceAsStream("/static/train.csv");
 
        try {
            BufferedReader fileReader = new BufferedReader(new InputStreamReader(is,"UTF-8"));
            CSVParser csvParser = new CSVParser(fileReader, CSVFormat.DEFAULT);
            Iterable<CSVRecord> csvRecords = csvParser.getRecords();
            for(CSVRecord csvRecord : csvRecords){
                // PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,
                // Parch,Ticket,Fare,Cabin,Embarked
                repository.save(new Passenger(
                        Integer.parseInt(csvRecord.get(1)),
                        Integer.parseInt(csvRecord.get(2)),
                        csvRecord.get(3),
                        csvRecord.get(4),
                        csvRecord.get(5),
                        csvRecord.get(6),
                        csvRecord.get(7),
                        csvRecord.get(8),
                        csvRecord.get(9),
                        csvRecord.get(10),
                        csvRecord.get(11)));
 
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
}

 

PassengerController.java

 

package com.chatbot.web.passenger;
 
import com.chatbot.web.util.Box;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import java.util.List;
import java.util.Optional;
 
@RestController @AllArgsConstructor
@RequestMapping("/passengers")
public class PassengerController {
    private PassengerService passengerService;
 
    @GetMapping("/csv")
    public void csvRead(){ passengerService.readCsv(); }
    @GetMapping("/survived/{survived}")
    public Box<List<Passenger>> findBySurvived(@PathVariable int survived){
        return passengerService.findBySurvived(survived);
    }
    @GetMapping("/passengerNumber/{id}")
    public Optional<Passenger> findById(@PathVariable String id){
        return passengerService.findById(id);
    }
}

 

PassengerController.java

 

package com.chatbot.web.passenger;
 
import com.chatbot.web.util.Box;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import java.util.List;
import java.util.Optional;
 
@RestController @AllArgsConstructor
@RequestMapping("/passengers")
public class PassengerController {
    private PassengerService passengerService;
 
    @GetMapping("/csv")
    public void csvRead(){ passengerService.readCsv(); }
    @GetMapping("/survived/{survived}")
    public Box<List<Passenger>> findBySurvived(@PathVariable int survived){
        return passengerService.findBySurvived(survived);
    }
    @GetMapping("/passengerNumber/{id}")
    public Optional<Passenger> findById(@PathVariable String id){
        return passengerService.findById(id);
    }
}

 

PassengerCounter.java

 

package com.chatbot.web.passenger;
 
import lombok.Getter;
import lombok.NoArgsConstructor;
 
@Getter
@NoArgsConstructor
public class PassengerCounter {
    private String gender, embarked, passengerCount;
}