4.1 ์š”์ฒญ ๋งค๊ฐœ๋ณ€์ˆ˜

Hibernate๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ์ฟผ๋ฆฌ์— ๋งค๊ฐœ๋ณ€์ˆ˜๋ฅผ ์ „๋‹ฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ ์ฟผ๋ฆฌ ๋ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ๋Œ€ํ•œ ๋ชจ๋“  ์ž‘์—…์ด ํฌ๊ฒŒ ๋‹จ์ˆœํ™”๋ฉ๋‹ˆ๋‹ค.

๋ณ€๊ฒฝํ•  ์ˆ˜ ์—†๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ฐพ๋Š” ๊ฒƒ์€ ๋งค์šฐ ๋“œ๋ญ…๋‹ˆ๋‹ค. ์ฒ˜์Œ์—๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ์ƒํ’ˆ ๋ชฉ๋ก์„ ๋ฐ˜ํ™˜ํ•˜๊ธฐ๋งŒ ํ•˜๋ฉด ๋˜๋Š” ๊ฒƒ ๊ฐ™์Šต๋‹ˆ๋‹ค. ๊ทธ๋Ÿฐ ๋‹ค์Œ ํŠน์ • ๋‚ ์งœ์— ํŠน์ • ์‚ฌ์šฉ์ž๋ฅผ ์œ„ํ•œ ์ตœ์‹  ์ œํ’ˆ ๋ชฉ๋ก์ด ํ•„์š”ํ•˜๋‹ค๋Š” ๊ฒƒ์ด ๋ฐํ˜€์กŒ์Šต๋‹ˆ๋‹ค. ํ•„์ˆ˜ ํ•„๋“œ๋ณ„๋กœ ์ •๋ ฌ๋˜๋ฉฐ ์•„์ง ์ „์ฒด ๋ชฉ๋ก์ด ์•„๋‹ˆ๋ผ ํŠน์ • ํŽ˜์ด์ง€(์˜ˆ: 21์—์„œ 30๊นŒ์ง€์˜ ์ œํ’ˆ)๋กœ ์ •๋ ฌ๋ฉ๋‹ˆ๋‹ค.

์ด๊ฒƒ์ด ๋ฐ”๋กœ ๋งค๊ฐœ๋ณ€์ˆ˜ํ™”๋œ ์ฟผ๋ฆฌ๊ฐ€ ํ•ด๊ฒฐํ•˜๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. HQL์— ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•œ ๋‹ค์Œ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ๋Š” ๊ฐ’์„ "ํŠน์ˆ˜ ์ด๋ฆ„"์ธ ๋งค๊ฐœ ๋ณ€์ˆ˜๋กœ ๋ฐ”๊ฟ‰๋‹ˆ๋‹ค. ๊ทธ๋Ÿฐ ๋‹ค์Œ ์š”์ฒญ์„ ์‹คํ–‰ํ•  ๋•Œ ๋ณ„๋„๋กœ ์ด๋Ÿฌํ•œ ๋งค๊ฐœ ๋ณ€์ˆ˜์˜ ๊ฐ’์„ ์ „๋‹ฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

ํŠน์ • ์ด๋ฆ„์„ ๊ฐ€์ง„ ์‚ฌ์šฉ์ž์˜ ๋ชจ๋“  ์ž‘์—…์„ ๋ฐ˜ํ™˜ํ•˜๋Š” HQL ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

from EmployeeTask where employee.name = "Ivan Ivanovich"

์ด์ œ ์ด๋ฆ„์„ ๋งค๊ฐœ๋ณ€์ˆ˜๋กœ ๋ฐ”๊พธ๊ฒ ์Šต๋‹ˆ๋‹ค.

from EmployeeTask where employee.name = :username

์ž‘์—…์„ ์ฐพ๊ธฐ ์œ„ํ•œ Java ์ฝ”๋“œ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์Šต๋‹ˆ๋‹ค.


String hql = "from EmployeeTask where employee.name = :username";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter("username", "Ivan Ivanovich");
List<EmployeeTask> resultLIst = query.list();

๋˜ํ•œ ๋งค๊ฐœ๋ณ€์ˆ˜ ์ด๋ฆ„ ๋Œ€์‹  ์ˆซ์ž๋งŒ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.


String hql = "from EmployeeTask where employee.name = :1";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter(1, "Ivan Ivanovich");
List<EmployeeTask> resultLIst = query.list();

๋ฌผ๋ก  ์ด๋ฆ„์„ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ๋” ์ข‹์ง€๋งŒ ์ด๋Ÿฌํ•œ ์ฝ”๋“œ๋ฅผ ์ฝ๊ณ  ์œ ์ง€ ๊ด€๋ฆฌํ•˜๋Š” ๊ฒƒ์ด ํ›จ์”ฌ ์‰ฝ์Šต๋‹ˆ๋‹ค.

4.2 setParameterList() ๋ฉ”์„œ๋“œ.

๋งค๊ฐœ๋ณ€์ˆ˜ ๊ฐ’์ด ํ•˜๋‚˜๊ฐ€ ์•„๋‹ˆ๋ผ ๊ฐœ์ฒด ๋ชฉ๋ก์„ ๋‚˜ํƒ€๋‚ด๋Š” ๊ฒฝ์šฐ๋„ ์žˆ์Šต๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด ์ง์›์˜ ์ง์—…์ด ํŠน์ • ๋ชฉ๋ก์— ํฌํ•จ๋˜์–ด ์žˆ๋Š”์ง€ ํ™•์ธํ•˜๊ณ  ์‹ถ์Šต๋‹ˆ๋‹ค.

์–ด๋–ป๊ฒŒ ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๊นŒ?


String hql = "from EmployeeTask where occupation IN (:occupation_list)";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameterList("occupation_list", new String[] {"Programmer", "Tester"});
List<EmployeeTask> resultLIst = query.list();

4๊ฐ€์ง€ ์œ ํ˜•์˜ ๋ชฉ๋ก์„ ๋งค๊ฐœ๋ณ€์ˆ˜ ๊ฐ’์œผ๋กœ ์ „๋‹ฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

  • ๊ฐ์ฒด ๋ฐฐ์—ด: Object[]
  • ์ปฌ๋ ‰์…˜: ์ปฌ๋ ‰์…˜
  • ํ˜•์‹ํ™”๋œ ๋ฐฐ์—ด: T[]
  • ์ž…๋ ฅ๋œ ๋ชจ์Œ: Collection<T>

์œ ํ˜•์ด ์ง€์ •๋œ ์ปฌ๋ ‰์…˜์ด๋‚˜ ๋ฐฐ์—ด์„ ์ „๋‹ฌํ•˜๊ธฐ๋กœ ๊ฒฐ์ •ํ•œ ๊ฒฝ์šฐ ๋ฐ์ดํ„ฐ ์œ ํ˜•์„ ์„ธ ๋ฒˆ์งธ ๋งค๊ฐœ ๋ณ€์ˆ˜๋กœ ์ „๋‹ฌํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์˜ˆ:


String hql = "from EmployeeTask where occupation IN (:occupation_list)";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameterList("occupation_list", new String[] {"Programmer", "Tester"}, String.class);
List<EmployeeTask> resultLIst = query.list();

๋ชฉ๋ก ๋งค๊ฐœ๋ณ€์ˆ˜๋กœ ์ž‘์—…ํ•  ๋•Œ ๋งค๊ฐœ๋ณ€์ˆ˜ ์ด๋ฆ„ ๋Œ€์‹  ์ˆซ์ž๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค. ๊ทธ๋Ÿฌ๋‚˜ ๋‹ค์‹œ ๋งํ•˜์ง€๋งŒ ์ด๋ฆ„์ด ๋” ํŽธ๋ฆฌํ•ฉ๋‹ˆ๋‹ค.

4.3 SQL ์ธ์ ์…˜์— ๋Œ€ํ•œ ๋ณดํ˜ธ

๋งค๊ฐœ๋ณ€์ˆ˜์˜ ๊ฐ€์žฅ ์ค‘์š”ํ•œ ๋ชฉ์  ์ค‘ ํ•˜๋‚˜๋Š” SQL ์ฃผ์ž…์œผ๋กœ๋ถ€ํ„ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ๋ณดํ˜ธํ•˜๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. ๋งŽ์€ ์ดˆ๋ณด ํ”„๋กœ๊ทธ๋ž˜๋จธ๋Š” ๋งค๊ฐœ๋ณ€์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๋Œ€์‹  ๋‹จ์ˆœํžˆ ์—ฌ๋Ÿฌ ๋ถ€๋ถ„์˜ ๋ฌธ์ž์—ด์„ ํ•จ๊ป˜ ๋ถ™์ž…๋‹ˆ๋‹ค.

๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ž‘์„ฑํ•˜๋Š” ๋Œ€์‹ :


String hql = "from EmployeeTask where employee.name = :username";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
query.setParameter("username", "Ivan Ivanovich");
List<EmployeeTask> resultLIst = query.list();

๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ž‘์„ฑํ•ฉ๋‹ˆ๋‹ค.


String hql = "from EmployeeTask where employee.name = " + "Ivan Ivanovich";
Query<EmployeeTask> query = session.createQuery( hql, EmployeeTask.class);
List<EmployeeTask> resultLIst = query.list();

์ ˆ๋Œ€ ํ•˜์ง€๋งˆ!์—ฌ๋Ÿฌ ๋ถ€๋ถ„์—์„œ SQL/HQL ์ฟผ๋ฆฌ๋ฅผ ๊ฒฐํ•ฉํ•˜์ง€ ๋งˆ์‹ญ์‹œ์˜ค. ์กฐ๋งŒ๊ฐ„ ์‚ฌ์šฉ์ž ์ด๋ฆ„์ด ํด๋ผ์ด์–ธํŠธ์—์„œ ์˜ฌ ๊ฒƒ์ด๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ์‚ฌ์•…ํ•œ ํ•ด์ปค๋Š” ๋‹น์‹ ์—๊ฒŒ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋ฌธ์ž์—ด์„ ์ค„ ๊ฒƒ์ž…๋‹ˆ๋‹ค.""Ivan"; DROP TABLE user;"

๊ทธ๋Ÿฐ ๋‹ค์Œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ๋Œ€ํ•œ ์ฟผ๋ฆฌ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ํ˜•์‹์„ ์ทจํ•ฉ๋‹ˆ๋‹ค.


from EmployeeTask where employee.name = "Ivan"; DROP TABLE user;

๋ฐ์ดํ„ฐ๊ฐ€ ๋‹จ์ˆœํžˆ ์‚ญ์ œ๋˜๋Š” ๊ฒฝ์šฐ์—๋„ ์—ฌ์ „ํžˆ ์ข‹์Šต๋‹ˆ๋‹ค. ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ž‘์„ฑํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค.


from EmployeeTask where employee.name = "Ivan";
UPDATE user SET password = '1' WHERE user.role = 'admin'

๋˜๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์ด:


from EmployeeTask where employee.name = "Ivan";
UPDATE user SET role = 'admin' WHERE user.id = 123;