Sql_function

SQL Function

Write contect Date
๊น€ํ˜„ํ˜ธ lhyun058@gmail.com 2022.08.13

๐Ÿ“์ •๋ฆฌ

์ขŒํ‘œ ๊ฐ„ ๊ฑฐ๋ฆฌ๋ฅผ ๊ณ„์‚ฐํ•ด ๊ฐ€๊นŒ์šด ๊ฑฐ๋ฆฌ ์ˆœ์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๊ธฐ ์œ„ํ•ด MySQL์— ST_Distance_Sphere()ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•  ๊ฒƒ์ด๋ฉฐ. ํ˜„์žฌ ์ง„ํ–‰ํ•˜๋Š” ํ”„๋กœ์ ํŠธ์—์„œ๋Š” Querydsl์„ ์‚ฌ์šฉ ํ•˜๊ธฐ๋•Œ๋ฌธ์— Querydsl์—์„œ SQL Function์‚ฌ์šฉ์„ ํ†ตํ•ด ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด์•ผ ํ•ด์„œ ์‚ฌ์šฉ๋ฒ•์„ ์ •๋ฆฌํ•˜๋ ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค.

1. ST_Distance_Sphere()

ST_Distance_Sphere()๋Š” ๋‘ ์  ์‚ฌ์ด์˜ ๊ฑฐ๋ฆฌ๋ฅด meter๋‹จ์œ„๋กœ ๋ณ€ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜์ž…๋‹ˆ๋‹ค.

SET @location = POINT(127.036377, 37.500643);
SELECT ST_Distance_Sphere(@location,
                          POINT(trade_longitude, trade_latitude)) AS distance,
       deal.trade_longitude,
       deal.trade_latitude
FROM deal
order by distance;

image

2.querydsl์—์„œ SQL Function ํ˜ธ์ถœํ•˜๊ธฐ

SQL Function์€ Expressions.stringTemplate()๋ฅผ ํ†ตํ•ด ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. Expressions.stringTemplate()์„ ์‚ฌ์šฉํ•ด ๋ฌธ์ž์—ด๋กœ ์ง์ ‘ ํ•จ์ˆ˜๋ฅผ ์ž‘์„ฑ ํ•˜๋ฉฐ, StringExpression์„ ๋ฐ˜ํ™˜ํ•˜์—ฌ select๋ฌธ์—์„œ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๊ฒŒ ํ•ด์ค๋‹ˆ๋‹ค.

์‚ฌ์šฉ๋ฐฉ๋ฒ• - Expressions.stringTemplate("ST_Distance_Sphere({0}, {1})",ํŒŒ๋ผ๋ฏธํ„ฐ1, ํŒŒ๋ผ๋ฏธํ„ฐ2)

List<Deal> content = queryFactory
        .selectFrom(deal)
        .where(
                categoryEq(dealFilterRequest.getCategory()),
                categoryDetailEq(dealFilterRequest.getCategoryDetail()),
                deal.deletedAt.isNull()
        )
        .join(deal.user, user).fetchJoin()
        .join(deal.location, location).fetchJoin()
        .limit(pageable.getPageSize())
        .offset(pageable.getOffset())
        .orderBy(Expressions.stringTemplate("ST_Distance_Sphere({0}, {1})",
                        Expressions.stringTemplate("POINT({0}, {1})",
                                dealFilterRequest.getCurrentLongitude(),
                                dealFilterRequest.getCurrentLatitude()
                        ),
                        Expressions.stringTemplate("POINT({0}, {1})",
                                deal.tradeLongitude,
                                deal.tradeLatitude
                        )
                ).asc(),
                deal.createdAt.desc())
        .fetch();

์œ„์— ์ฝ”๋“œ๋ฅผ ์‹คํ–‰ํ•˜๋ฉด ์•„๋ž˜์ฒ˜๋Ÿผ ์ฟผ๋ฆฌ๊ฐ€ ๋‚˜๊ฐ€๋Š”๊ฑธ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

image


์ฐธ๊ณ 

๐Ÿ’ก MySQL_ST_Distance_Sphere

Notes mentioning this note


Here are all the notes in this garden, along with their links, visualized as a graph.