본문 바로가기
국비 교육/SQL(Oracle)

[자바 - 오라클] JPQL (Java Persitence Query Language)

by 육츠 2024. 2. 14.
Contents 접기

** 잊지 않기!

 

사용 테이블

-- ============ JPQL_TEST
DROP TABLE test_user;
DROP SEQUENCE testuser_seq;

CREATE TABLE test_user(
    userid NUMBER PRIMARY KEY
    , username VARCHAR2(250) NOT NULL
    , pwd VARCHAR2(200) NOT NULL
    , email VARCHAR2(200) NOT NULL
);
CREATE SEQUENCE testuser_seq;

SELECT * FROM test_user;

 

패키지 :  com.kdigital.jpql_test.entity;

lombok 사용하여 생성자,  Setter, Getter, ToString 생성

userid 는 SEQUENCE 객체를 사용하여 번호를 부여 한다. 때문에 lombok 이 아닌 오버라이딩한 생성자는 따로 만든다.
@SequenceGenerator , @GeneratedValue(generator = "user_seq_gen") 필수!

package com.kdigital.jpql_test.entity;


import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.SequenceGenerator;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@NoArgsConstructor
@Setter
@Getter
@ToString

@Entity
@Table(name = "test_user")
public class Member {
	
	@SequenceGenerator(
			name = "user_seq_gen",
			sequenceName = "testuser_seq",
			initialValue = 1,
			allocationSize = 1
			)
	
	@Id
	@Column(name = "userid")
	@GeneratedValue(generator = "user_seq_gen")
	private Long userid;
	
	// nullable -> NOT NULL
	@Column(name = "username", nullable = false)
	private String username;
	
	@Column(name = "pwd", nullable = false)
	private String pwd;
	
	@Column(name = "email", nullable = false)
	private String email;

	// 시퀀스로 값이 증가하는 userid 는 전달받지 않는다.
	public Member(String username, String pwd, String email) {
		super();
		this.username = username;
		this.pwd = pwd;
		this.email = email;
	}
	

}

 

패키지 : package com.kdigital.jpql_test.util;

db 와 연결하는 메소드

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;

public class ConnectionManager {
	private static EntityManagerFactory factory;
	
	static {
		factory = Persistence.createEntityManagerFactory("jpastudy");
		// 객체가 생성되자마자 바로 가능해진다.
	}
	
	public static EntityManager getManager(){
		return factory.createEntityManager();
	}
	
	public static void close() {
		factory.close();
	}
	
	
}

 

패키지 : package com.kdigital.jpql_test;

package com.kdigital.jpql_test;

import java.util.Iterator;
import java.util.List;

import com.kdigital.jpql_test.entity.Member;
import com.kdigital.jpql_test.util.ConnectionManager;

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityTransaction;
import jakarta.persistence.TypedQuery;

public class MainJpql {

	public static void main(String[] args) {
		// 1) Member 를 여러명 저장

		for(int i =1; i<=50; ++i) {
			insertMember(i);
		}

		// userid 가 15번인 회원 1명 조회
		findById(15L); 
		//select * from test_user where userid = 15;

		// userid 가 50번인 회원 1명 삭제
		deleteMember(50L);
        //deleteMember(2L);
		//deleteMember(3L);
		// deleteMember(7L);

		// 회원 수정
		Member member = new Member("Choi", "pwd_choi", "choi@naver.com");
		member.setUserid(1L);
		updateMember(member);


	}

	// 회원을 저장하는 메소드
	private static void insertMember(int i) {
		System.out.println("----- INSERT MEMBER ------");
		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member member = 
					new Member("name_" + i, "pwd_" + i, "email_"+i+"@naver.com");
			manager.persist(member);

			tx.commit();
		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();
		} finally {
			manager.close();
		}

	}

	// 회원 1명 조회
	private static void findById(long userid) {
		System.out.println("----- SELECT MEMBER ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member member = manager.find(Member.class, userid);

			if(member == null) {
				System.out.println("사용자가 없습니다.");

			} else {
				System.out.println(member);
			}
			tx.commit();

		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		}finally {
			manager.close();
		}

	}


	private static void deleteMember(long userid) {
		System.out.println("----- DELETE MEMBER ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member member = manager.find(Member.class, userid);

			if(member == null) {
				System.out.println("사용자가 없습니다.");

			} else {
				manager.remove(member);
				System.out.println("삭제 완료");
			}
			tx.commit();

		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		}finally {
			manager.close();
		}

	}

	private static void updateMember(Member member) {
		System.out.println("----- UPDATE MEMBER ------");

		Long userid = member.getUserid();
		String username = member.getUsername();
		String pwd = member.getPwd();
		String email = member.getEmail();

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member mem = manager.find(Member.class, userid);

			if (mem == null) {
				System.out.println("회원이 없습니다.");
			} else {
				mem.setUsername(username);
				mem.setPwd(pwd);
				mem.setEmail(email);
				System.out.println("수정 완료");
			}
			tx.commit();
			// 수정정보가 반영됨ㄱ

		} catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		} finally {
			manager.close();
		}

	}


}

 

JPQL 사용

- user 전체 조회
- 전체 중에 1명만  조회

	// 전체 조회 JPQL (Java Persitence Query Language)
		selectAllMember();
        
        // JPQL 로 1 명만 조회되는 
		findById();
        
        
        // JPQL로 검색하기
		private static void findById() {
			System.out.println("----- SELECT MEMBER (JPQL) ------");

            Long uid = 11L; 

            EntityManager manager = ConnectionManager.getManager();
            EntityTransaction tx = manager.getTransaction();

            try {
                tx.begin();

                String query = "select m from Member m where m.userid = :uid";

                /*
                TypedQuery<Member> member = manager.createQuery(query, Member.class);
                member.setParameter("uid",uid);

                Member myMember = member.getSingleResult();
                 */

                Member myMember = manager.createQuery(query, Member.class)
                        .setParameter("uid",uid)
                        .getSingleResult();

                System.out.println(myMember);		
                tx.commit();
            }catch(Exception e) {
                tx.rollback();
                e.printStackTrace();

            }finally {
                manager.close();
		}
        
        
        // 전체 회원 명단 조회
	private static void selectAllMember() {
		System.out.println("----- SELECT ALL MEMBER ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			String query = "select m from Member m";
			// 테이블명 (대소문자 주의)
			// Alias 반드시 사용

			TypedQuery<Member> tq = manager.createQuery(query,Member.class);

			// 리스트 형태로 재 반환
			List<Member> members = tq.getResultList();

			// iterator 변환
			Iterator<Member> iter = members.iterator();
			while(iter.hasNext())
				System.out.println(iter.next());

			tx.commit();
		} catch(Exception e) {
			tx.rollback();
			e.printStackTrace();
		}finally{
			manager.close();
		}


	}
 TypedQuery<Member> member = 
        manager.createQuery(query, Member.class);
                member.setParameter("uid",uid);

                Member myMember = member.getSingleResult();		   Member myMember = manager.createQuery(query, Member.class)
                        .setParameter("uid",uid)
                        .getSingleResult();
두 코드는 같은 의미이다. 왼쪽의 코드보다 간결하게 쓴 형태가 오른쪽의 코드이다.
> TypedQuery 형채로 받으면 다시 Member 객체로 받아야 하기 때문이다.

 

페이징 조회

JPQL 사용한다.  ( 현재 코드는 오라클 11 => 오류, 오라클 19 = 가능) 

		//int srow = 1; 시작값은 0부터 
		int srow = 0; 
		int pagePerCount = 10;

		selectAllMember(srow, pagePerCount);
        	
    
    // 페이징 조회
	// srow : 시작 번호 / pagePerCount : 한 화면에 보여질 개수
	private static void selectAllMember(int srow, int pagePerCount) {
		System.out.println("----- SELECT ALL MEMBER (PAGING) ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();
            
			String query = "select m from Member m";
			// 테이블명 (대소문자 주의)
			// Alias 반드시 사용

			TypedQuery<Member> tq = manager.createQuery(query,Member.class);

			tq.setFirstResult(srow);
			tq.setMaxResults(pagePerCount); //10 개 조회


			// 리스트 형태로 재 반환
			List<Member> members = tq.getResultList();

			// iterator 변환
			Iterator<Member> iter = members.iterator();
			while(iter.hasNext())
				System.out.println(iter.next());

			tx.commit();
		} catch(Exception e) {
			tx.rollback();
			e.printStackTrace();
		}finally{
			manager.close();
		}

 

- 전체 코드

더보기 
package com.kdigital.jpql_test;

import java.util.Iterator;
import java.util.List;

import com.kdigital.jpql_test.entity.Member;
import com.kdigital.jpql_test.util.ConnectionManager;

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityTransaction;
import jakarta.persistence.TypedQuery;

public class MainJpql {

	public static void main(String[] args) {
		// 1) Member 를 여러명 저장

		//for(int i =1; i<=50; ++i) {
		//	insertMember(i);
		//}

		// userid 가 15번인 회원 1명 조회
		//findById(15L); 
		//select * from test_user where userid = 15;

		// userid 가 50번인 회원 1명 삭제
		//deleteMember(2L);
		//deleteMember(3L);
		// deleteMember(7L);

		// 회원 수정
		Member member = new Member("Choi", "pwd_choi", "choi@naver.com");
		member.setUserid(1L);
		// updateMember(member);

		// 전체 조회 JPQL (Java Persitence Query Language)
		//selectAllMember();

		// JPQL 로 1 명만 조회되는 
		// findById();

		//int srow = 1; 시작값은 0부터 
		int srow = 0; 
		int pagePerCount = 10;

		selectAllMember(srow, pagePerCount);
	}


	// 회원을 저장하는 메소드
	private static void insertMember(int i) {
		System.out.println("----- INSERT MEMBER ------");
		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member member = 
					new Member("name_" + i, "pwd_" + i, "email_"+i+"@naver.com");
			manager.persist(member);

			tx.commit();
		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();
		} finally {
			manager.close();
		}

	}

	// 회원 1명 조회
	private static void findById(long userid) {
		System.out.println("----- SELECT MEMBER ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member member = manager.find(Member.class, userid);

			if(member == null) {
				System.out.println("사용자가 없습니다.");

			} else {
				System.out.println(member);
			}
			tx.commit();

		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		}finally {
			manager.close();
		}

	}

	// JPQL로 검색하기
	private static void findById() {
		System.out.println("----- SELECT MEMBER (JPQL) ------");

		Long uid = 11L; 

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			String query = "select m from Member m where m.userid = :uid";

			/*
			TypedQuery<Member> member = manager.createQuery(query, Member.class);
			member.setParameter("uid",uid);

			Member myMember = member.getSingleResult();
			 */

			Member myMember = manager.createQuery(query, Member.class)
					.setParameter("uid",uid)
					.getSingleResult();

			System.out.println(myMember);		
			tx.commit();
		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		}finally {
			manager.close();
		}


	}


	private static void deleteMember(long userid) {
		System.out.println("----- DELETE MEMBER ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member member = manager.find(Member.class, userid);

			if(member == null) {
				System.out.println("사용자가 없습니다.");

			} else {
				manager.remove(member);
				System.out.println("삭제 완료");
			}
			tx.commit();

		}catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		}finally {
			manager.close();
		}

	}

	private static void updateMember(Member member) {
		System.out.println("----- UPDATE MEMBER ------");

		Long userid = member.getUserid();
		String username = member.getUsername();
		String pwd = member.getPwd();
		String email = member.getEmail();

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			Member mem = manager.find(Member.class, userid);

			if (mem == null) {
				System.out.println("회원이 없습니다.");
			} else {
				mem.setUsername(username);
				mem.setPwd(pwd);
				mem.setEmail(email);
				System.out.println("수정 완료");
			}
			tx.commit();
			// 수정정보가 반영됨ㄱ

		} catch(Exception e) {
			tx.rollback();
			e.printStackTrace();

		} finally {
			manager.close();
		}

	}

	// 페이징 조회ㅣ
	// srow : 시작 번호 / pagePerCount : 한 화면에 보여질 개수
	private static void selectAllMember(int srow, int pagePerCount) {
		System.out.println("----- SELECT ALL MEMBER (PAGING) ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			// String query = "select m from Member m";
			String query = "select m from Member m";
			// 테이블명 (대소문자 주의)
			// Alias 반드시 사용

			TypedQuery<Member> tq = manager.createQuery(query,Member.class);

			tq.setFirstResult(srow);
			tq.setMaxResults(pagePerCount); //10 개 조회


			// 리스트 형태로 재 반환
			List<Member> members = tq.getResultList();

			// iterator 변환
			Iterator<Member> iter = members.iterator();
			while(iter.hasNext())
				System.out.println(iter.next());

			tx.commit();
		} catch(Exception e) {
			tx.rollback();
			e.printStackTrace();
		}finally{
			manager.close();
		}

	}

	// 전체 회원 명단 조회
	private static void selectAllMember() {
		System.out.println("----- SELECT ALL MEMBER ------");

		EntityManager manager = ConnectionManager.getManager();
		EntityTransaction tx = manager.getTransaction();

		try {
			tx.begin();

			String query = "select m from Member m";
			// 테이블명 (대소문자 주의)
			// Alias 반드시 사용

			TypedQuery<Member> tq = manager.createQuery(query,Member.class);

			// 리스트 형태로 재 반환
			List<Member> members = tq.getResultList();

			// iterator 변환
			Iterator<Member> iter = members.iterator();
			while(iter.hasNext())
				System.out.println(iter.next());

			tx.commit();
		} catch(Exception e) {
			tx.rollback();
			e.printStackTrace();
		}finally{
			manager.close();
		}
	}



}

 

 

저작자표시 비영리 변경금지 (새창열림)

'국비 교육 > SQL(Oracle)' 카테고리의 다른 글

[개인공부 / 오라클] TOP N Query, Pivot  (0) 2024.03.10
[개인공부 / 오라클] 윈도우 함수 Window Function  (0) 2024.03.10
[자바 - 오라클] JPA 연결 - 2  (0) 2024.02.13
[자바 - 오라클] (잊지 않으려고 적어둠)JPA 연결 - 1  (0) 2024.02.13

관련글

티스토리툴바