02-DBHANDLE with JPA

package com.psmon.springdb;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class
public class User {
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Integer id;
	
	private String name;
	
	private String email;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

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

}

package com.psmon.springdb;

import org.springframework.data.repository.CrudRepository;

//This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
//CRUD refers Create, Read, Update, Delete

public interface UserRepository extends CrudRepository<User, Long> {

}

package com.psmon.springdb;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class JparestdemoApplicationTests {
	
	@Autowired
	private UserRepository userRepository;

	@Test
	public void contextLoads() {		
		jpaTest1();		
	}
	
	public void jpaTest1() {
		// 사용자 생성
		User addUser = new User();
		addUser.setName("minsu");
		addUser.setEmail("test@x.com");
		userRepository.save(addUser);
		
		// 사용자 조회
		Iterable<User> userList = userRepository.findAll();		
		userList.forEach(item->System.out.println(item.getName() ));		
	}
}

-- 사용자생성
INSERT INTO `spring`.`user`
(`id`,
`email`,
`name`)
VALUES
(<{id:}>,
<{email: >,
<{name:}>);

-- 사용자 조회
SELECT * FROM user

class User{
	int id;   // 특별한 구현없이, 사용자의 객체만으로 자신을 포함하는 특정정보(클릭로그)만 추출할수가 없습니다.
	string name;
	string email;
}
class ClickLog{
	int clickid;
	User user; // User객체 참조를 포함합니다.
	string clickurl;	
}

TABLE User{
   int id   //자신의 키를 참조하는 다른 테이블에 접근가능합니다. on u.id = c.clickid
   varchar name
   varchar email   
}
TABLE ClickLog{
	int clickid
	int userid  //외래키를 통해서 접근가능합니다. on c.clickid = u.id
	varchar clickurl;
}

CREATE TABLE `group_info` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `FKa36i4ekojwk70bxen390i6tek` (`group_id`),
  CONSTRAINT `FKa36i4ekojwk70bxen390i6tek` FOREIGN KEY (`group_id`) REFERENCES `group_info` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

@Entity
public class GroupInfo {
	@Id
    @GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name = "GROUP_ID")
    private Integer id; 
    
    private String name;    
}

@Entity
public class User {
	@Id
    @GeneratedValue
    @Column(name = "USER_ID")
    private Integer id;

	private String name;

	private String email;
 
	@ManyToOne
	@JoinColumn(name = "GROUP_ID")
    private GroupInfo groupInfo;	
}

@Autowired
private GroupRepository groupRepository;


@Autowired
private UserRepository userRepository;

GroupInfo newGroup = new GroupInfo();
newGroup.setName("학생");
groupRepository.save(newGroup);

// 사용자 생성
User addUser = new User();
addUser.setName("minsu");
addUser.setEmail("test@x.com");
addUser.setGroupInfo(newGroup);        
        
userRepository.save(addUser);
 
// select * from user join group_info 과 동일한 효과로, 분리된 테이블에서 그룹명을 가지고 온다.
Iterable<User> userList = userRepository.findAll();      
userList.forEach(item->System.out.println( String.format("Name:%s  GroupName:%s", item.getName(),item.getGroupInfo().getName() )  ));

-- 그룹을 추가한다.
INSERT INTO test.group_info(
   group_id
  ,name
) VALUES (
   NULL -- group_id - IN int(11)
  ,''  -- name - IN varchar(255)
)


-- 사용자를 추가할시 그룹을 지정하거나, 나중에 지정한다.
INSERT INTO test.user(
   user_id
  ,email
  ,name
  ,group_id
) VALUES (
   NULL -- user_id - IN int(11)
  ,''  -- email - IN varchar(255)
  ,''  -- name - IN varchar(255)
  ,0   -- group_id - IN int(11)
)


-- 여러명을 등록할시 위 과정이 반복됩니다.




--학생인 사용자만 조회시..Join을 통해 해결
select * from user join group_info gi on gi.name='학생'

@Entity
public class GroupInfo {	  
  @OneToMany(mappedBy = "groupInfo", cascade = {CascadeType.PERSIST},fetch=FetchType.EAGER)
  private Set<User> users;

	@Override
    public String toString() {
        String result = String.format(
                "GroupInfo[id=%d, name='%s']%n",
                id, name);        
        if (users != null) {        	        	
            for(User user : users) {
                result += String.format(
                        "User[id=%d, name='%s']%n",
                        user.getId(), user.getName());
            }
        }
        return result;
    }
}


public interface GroupRepository extends CrudRepository<GroupInfo, Long> {
	public GroupInfo findByName(String name);	
}

GroupInfo newGroupA = new GroupInfo("학생");
Set usersA = new HashSet<User>() {{
	add(new User("minsu2","min2@x.com",newGroupA));			
	add(new User("minsu3","min3@x.com",newGroupA));
}};
newGroupA.setUsers(usersA);

GroupInfo newGroupB = new GroupInfo("선생");
Set usersB = new HashSet<User>() {{
	add(new User("tom1","tom1@x.com",newGroupB));			
	add(new User("tom2","tom2@x.com",newGroupB));
}};
newGroupB.setUsers(usersB);

    groupRepository.save(new HashSet<GroupInfo>() {{
    	add(newGroupA);
    	add(newGroupB);        	
    }});
    

GroupInfo groupInfo = groupRepository.findByName("학생");        
    System.out.println( String.format("학생정보: %s", groupInfo.toString() ) );

public interface UserPageRepo extends Repository<User, Long>{

	Optional<User> findOne(Long id);
	
	// 전체 페이징처리
	Page<User> findAll(Pageable pageRequest);
	
	// 검색 확장-그룹명으로 페이지 필터
	Page<User> findByGroupInfoName(String groupName,Pageable pageRequest);
		
	// Update
	void delete(User deleted);
	
	User save(User persisted);
	
	void flush();
}

		//Test를 위해 100개의 데이터 인입
		GroupInfo newGroupA = new GroupInfo("학생");
		Set usersA = new HashSet<User>() {{
			for(int i=0; i<100 ; i++) {
				String userName = String.format("minsu%d", i);
				String email = String.format("min%d@x.com", i);
				add(new User(userName,email,newGroupA));
			}					
		}};
		newGroupA.setUsers(usersA);
        groupRepository.save(new HashSet<GroupInfo>() {{
        	add(newGroupA);
        }});


		//원하는 페이지를 조회합니다.( 페이지번호 , 페이지당 처리수)
        PageRequest  pageRequest =  new PageRequest(1,10);                       
        Page<User> sPage = userPageRepo.findAll(pageRequest);
        System.out.println( String.format(" %d:Contents %d:Page", sPage.getNumberOfElements(),sPage.getNumber() ) );
        
        Page<User> sPage2 = userPageRepo.findByGroupInfoName("학생", pageRequest);
        System.out.println( String.format(" %d:Contents %d:Page", sPage2.getNumberOfElements(),sPage2.getNumber() ) );

public interface UserPageRepo extends Repository<User, Long>{

	// QueryMode
	@Query(value="select t from User t "			
			+ "where  t.name =:name  "
			+ "order by t.id " , nativeQuery=false )
	List<User> findBySomeName( @Param("name") String name, Pageable pageable);
	
}