Page History

public interface AddressRepo extends CrudRepository<Address, Long>{
	
	List<Address> findBySex(String sex);
	
	List<Address> findBySex(String sex, Sort sort);
	
	List<Address> findBySexOrderByAgeDesc(String sex);
	
	List<Address> findByAgeGreaterThan(int age);
	
	List<Address> findByAgeGreaterThanEqual(int age);
		
	List<Address> findByAgeLessThan(int age);
		
	List<Address> findByAgeBetween(int low,int high);
	
	List<Address> findByAgeGreaterThanAndSex(int age,String sex);
	
	List<Address> findByAgeGreaterThanOrSex(int age,String sex);
		
	List<Address> findByAgeIn(int age[]);
}

	@Query("select new com.example.demo.data.AddressStatistics(t.address,AVG(t.age)) from Address t where t.age > :minage GROUP BY t.address HAVING AVG(t.age) > :filterage ")
	List<AddressStatistics> findRegionAvgage(
			@Param("minage") int minage,
			@Param("filterage") double filterage
			);

	@Query(value="SELECT address,age,name,phoneNbr,sex FROM address " + 
			"UNION " + 
			"SELECT address,age,name,phoneNbr,sex FROM address2 ",
			nativeQuery = true)
	List<?> makeUniOn();

	public void RankTest() {		
	     int[] score = {Integer.MIN_VALUE};
	     int[] no = {0};
	     int[] rank = {0};	
	     List<AddressAgeRank> ageRankList =  
				addressRepo.findByAgeBetween(10, 90).stream()
				.sorted((a,b) -> b.getAge() - a.getAge() )
				.map(p -> {
		             ++no[0];
		             if (score[0] != p.getAge()) rank[0] = no[0];
		             return new AddressAgeRank(p.getName(),score[0] = p.getAge(),  rank[0] );					
				})
				.collect(Collectors.toList());
	     
	     ageRankList.forEach(item ->{
	    	 System.out.println(item.toString());	    	 
	     });	    
	}

//인터페이스 선언부
public interface AddressRepoDSL extends JpaRepository<Address, Long>,
QueryDslPredicateExecutor<Address>{
	
}
//사용부
	@Autowired
	private AddressRepoDSL addressRepoDSL;
	public void jpa_queryDslTest() {
		
		QAddress	userAddress = QAddress.address1;
		BooleanBuilder builder = new BooleanBuilder();
		
		builder.and(userAddress.name.eq("민수1") )
			   .and(userAddress.address.like("%" + "서" +"%"));		
				
		Iterable<Address> addressList =  addressRepoDSL.findAll(builder);
		addressList.forEach( item -> {
			String itemString = String.format("%d%s %s %s %s %s",item.getId(),item.getName(),
					item.getPhoneNbr(), item.getSex(),item.getAddress(),item.getAge());
			System.out.println(itemString);
		});
		
	}

CREATE OR REPLACE FUNCTION addaddress(
	address character varying,
	age integer,	
	name character varying,
	sex character varying,
	phonenbr character varying )
 
RETURNS integer AS $BODY$	
	BEGIN 
		INSERT INTO address ( address, age, name,sex,phonenbr) VALUES( address, age, name,sex,phonenbr);
		return 0;
	END; 
$BODY$
LANGUAGE plpgsql VOLATILE

public interface AddressRepo extends CrudRepository<Address, Long>{
	@Procedure
	public Integer addaddress(String address,Integer age, String name, String sex, String phonenbr);	
}


//SP 호출방법 : 커넥션풀 관리가 안되어, Loop로 트랜잭션을 유발하는것은 위험합니다.
addressRepo.addaddress("독도", 45, "포켓몬", "남", "010-1233-3321");


//호출로그 : 실제 호출로그에는 아규멘트가 5개가아닌 6개입니다. 이것은 리턴처리가,OUT 파라메터로 자동연결되었기때문입니다. 
Hibernate: 
    {call addaddress(?,?,?,?,?,?)}