산정보 보기 웹사이트 만들기(1) - sql을 사용해서 데이터 가져오기

nextjs/웹사이트 만들기

산정보 보기 웹사이트 만들기(1) - sql을 사용해서 데이터 가져오기

lamarcK 2025. 4. 14. 04:53

활용 자료

https://www.bigdata-forest.kr/frn/index

산림빅테이터 거래소의 산림관광정보 csv입니다. 한국산림복지진흥원에서 무료로 제공하는 데이터입니다.

데이터 상품 정보 - 산림 관광지와 관련된 산정보소재지, 산정보상세내용, 산이미지 등

■ 기간 및 범위 ∙ 2019년 1월 ~ 2019년 12월 (월단위)

■ 컬럼(속성) 정보

∙ 산정보 목록 : 산정보 카테고리(산정보소재지. 산정보상세내용 등)

∙ 산 이미지 정보 : 산 이미지 카테고리(이미지명, 이미지파일명 등)

∙ 전통마을숲 : 전통마을숲 카테고리(전통마을숲명, 전통마을숲소재지명 등)

∙ 명산정보 목록 : 명산정보 카테고리(100대명산 선정이유 내용, 소재지 등)

■ 활용 예제 ∙ 본 데이터 상품을 활용하여 사용자는 다음과 같은 정보를 확인할 수 있습니다.

1) 국내의 100대 명산 소재지, 산정보관리주체명, 산정보관리자 전화번호 등의 산림관광에 대한 정보 파악 가능

초기코드(프로토타입)

**1. Next.js 프로젝트 생성
npx create-next-app@latest my-mountain-app

**옵션 선택
Would you like to use TypeScript? › Yes
Would you like to use ESLint? › Yes
Would you like to use Tailwind CSS? › Yes
Would you like to use `src/` directory? › Yes
Would you like to use App Router? (recommended) › Yes
Would you like to customize the default import alias? › No

**2. 필요한 추가 패키지 설치
npm install sqlite sqlite3

**3. 프로젝트 구조:
src/
├── app/
│   ├── page.tsx
│   ├── layout.tsx
│   ├── states/
│   │   └── [state]/
│   │       └── page.tsx
│   └── mountains/
│       └── [id]/
│           └── page.tsx
├── components/
│   └── MountainCard.tsx
├── lib/
│   └── db.ts
└── types/
    └── index.ts

**4. src/types/index.ts
export interface Mountain {
  ID: number;
  NAME: string;
  PROVINCE: string;
  HEIGHT: number;
  DESCRIPTION: string;
  IMAGE: string;
}

export interface Province {
  PROVINCE: string;
}

**5. src/lib/db.ts
import sqlite3 from 'sqlite3';
import { open } from 'sqlite';
import { Mountain, Province } from '@/types';

let db: any = null;

async function openDb() {
  if (!db) {
    db = await open({
      filename: './mountain01.db',
      driver: sqlite3.Database
    });
  }
  return db;
}

export async function getStates(): Promise<Province[]> {
  const db = await openDb();
  try {
    return await db.all('SELECT DISTINCT PROVINCE FROM T');
  } catch (error) {
    console.error('Error fetching provinces:', error);
    return [];
  }
}

export async function getMountainsByState(province: string): Promise<Mountain[]> {
  const db = await openDb();
  try {
    return await db.all('SELECT * FROM T WHERE PROVINCE = ?', [province]);
  } catch (error) {
    console.error('Error fetching T:', error);
    return [];
  }
}

export async function getMountainById(id: string): Promise<Mountain | null> {
  const db = await openDb();
  try {
    return await db.get('SELECT * FROM T WHERE id = ?', [id]);
  } catch (error) {
    console.error('Error fetching T:', error);
    return null;
  }
}

csv를 sql데이터로 변경해서 사용
필터 활용을 위해서 주소 부분을 province 부분만 따로 분리해서 별도의 테이블을 가지도록 수정
원래는 MNTN_INFO_POFLC 부분만 존재

**6. src/app/layout.tsx
import './globals.css'
import type { Metadata } from 'next'

export const metadata: Metadata = {
  title: '한국의 산',
  description: '대한민국의 아름다운 산들을 소개합니다.',
}

export default function RootLayout({
  children,
}: {
  children: React.ReactNode
}) {
  return (
    <html lang="ko">
      <body>
        <nav className="bg-gray-800 text-white p-4">
          <div className="container mx-auto">
            <a href="/" className="text-xl font-bold">한국의 산</a>
          </div>
        </nav>
        <main className="container mx-auto p-4">
          {children}
        </main>
      </body>
    </html>
  )
}

**7. src/app/page.tsx
import { getStates } from '@/lib/db'
import Link from 'next/link'

export default async function Home() {
  const provinces = await getStates()

  return (
    <div className="space-y-6">
      <h1 className="text-3xl font-bold">대한민국 산 정보</h1>
      <div className="grid grid-cols-2 md:grid-cols-4 gap-4">
        {provinces.map((province) => (
          <Link 
            href={`/provinces/${province.PROVINCE}`} 
            key={province.PROVINCE}
            className="block p-6 bg-white rounded-lg shadow hover:shadow-lg transition-shadow"
          >
            <h2 className="text-xl font-semibold">{province.PROVINCE}</h2>
          </Link>
        ))}
      </div>
    </div>
  )
}

**8. src/app/provinces/[PROVINCE]/page.tsx
import { getMountainsByState } from '@/lib/db'
import MountainCard from '@/components/MountainCard'

export default async function StatePage({
  params
}: {
  params: { state: string }
}) {
  const mountains = await getMountainsByState(params.state)

  return (
    <div className="space-y-6">
      <h1 className="text-3xl font-bold">{params.state} 산 목록</h1>
      <div className="grid grid-cols-1 md:grid-cols-3 gap-6">
        {mountains.map((mountain) => (
          <MountainCard key={mountain.ID} mountain={mountain} />
        ))}
      </div>
    </div>
  )
}

**9. src/app/mountains/[id]/page.tsx
import { getMountainById } from '@/lib/db'
import Image from 'next/image'
import { notFound } from 'next/navigation'

export default async function MountainPage({
  params
}: {
  params: { id: string }
}) {
  const mountain = await getMountainById(params.id)

  if (!mountain) {
    notFound()
  }

  return (
    <div className="max-w-4xl mx-auto">
      <h1 className="text-3xl font-bold mb-6">{mountain.NAME}</h1>
      <div className="relative h-96 mb-6">
        <Image
          src={`/images/mountains/${mountain.IMAGE}`}
          alt={mountain.NAME}
          fill
          className="rounded-lg object-cover"
        />
      </div>
      <div className="bg-white p-6 rounded-lg shadow-lg">
        <h2 className="text-xl font-semibold mb-4">상세 정보</h2>
        <div className="space-y-2">
          <p><strong>높이:</strong> {mountain.HEIGHT}m</p>
          <p><strong>위치:</strong> {mountain.PROVINCE}</p>
          <p><strong>설명:</strong> {mountain.DESCRIPTION}</p>
        </div>
      </div>
    </div>
  )
}

**10. src/components/MountainCard.tsx
import { Mountain } from '@/types'
import Image from 'next/image'
import Link from 'next/link'

export default function MountainCard({ mountain }: { mountain: Mountain }) {
  return (
    <Link 
      href={`/mountains/${mountain.ID}`}
      className="block bg-white rounded-lg shadow hover:shadow-lg transition-shadow overflow-hidden"
    >
      <div className="relative h-48">
        <Image
          src={`/images/mountains/${mountain.IMAGE}`}
          alt={mountain.NAME}
          fill
          className="object-cover"
        />
      </div>
      <div className="p-4">
        <h2 className="text-xl font-semibold">{mountain.NAME}</h2>
        <p className="text-gray-600">{mountain.HEIGHT}m</p>
      </div>
    </Link>
  )
}

**11. next.config.js
/** @type {import('next').NextConfig} */
const nextConfig = {
  images: {
    domains: ['localhost'],
  },
}

module.exports = nextConfig

1차 개선 : null 처리

기본적으로 province 부분을 가져오기 때문에 해당 부분에 null이 있을 경우 공백으로 반환하여 빈 공간이 생겨버림

        {provinces.map((province) => (
          <Link 
            href={`/provinces/${province.PROVINCE}`} 
            key={province.PROVINCE}
            className="block p-6 bg-white rounded-lg shadow hover:shadow-lg transition-shadow"
          >
            <h2 className="text-xl font-semibold">{province.PROVINCE}</h2>
          </Link>
        ))}

이부분 때문인데 데이터 무결성을 위해서 빈칸은 데이터를 입력하고 사용하지만 테스트 부분같이 태생적으로 null인 부분을 굳이 지우지 않고 사용할 수 있도록 null처리 구분을 추가했다.

**변경 전/후
// 변경 전
return await db.all('SELECT DISTINCT PROVINCE FROM T');
return await db.all('SELECT * FROM T WHERE PROVINCE = ?', [province]);
return await db.get('SELECT * FROM T WHERE id = ?', [id]);

// 변경 후
return await db.all('SELECT DISTINCT PROVINCE FROM T WHERE PROVINCE IS NOT NULL AND trim(PROVINCE) != ""');
return await db.all('SELECT * FROM T WHERE PROVINCE IS NOT NULL AND trim(PROVINCE) != "" AND PROVINCE = ?', [province]);
return await db.get('SELECT * FROM T WHERE id IS NOT NULL AND id = ?', [id]);

기본적으로 db를 얻어오는 부분만 수정하면 된다.

**주요 함수와 연산자
WHERE : WHERE 뒤쪽의 필터링 조건에 따라 데이터를 필터링한다
trim(): 문자열 앞뒤의 공백을 제거
IS NOT NULL: NULL 값이 아닌지 확인
!=: 같지 않음을 비교
AND: 여러 조건을 모두 만족해야 함
=: 정확히 일치하는지 비교
?: 파라미터가 들어갈 자리 (SQL injection 방지)

**SQL 실행 순서 (Logical Query Processing)
1. FROM: 테이블 지정
2. WHERE: 행 필터링
3. GROUP BY: 그룹화
4. HAVING: 그룹 필터링
5. SELECT: 열 선택/계산
6. DISTINCT: 중복 제거
7. ORDER BY: 정렬
8. LIMIT/OFFSET: 행 수 제한

//이러한 구조로 인해
NULL 값 제외
빈 문자열 제외
원하는 데이터만 정확히 필터링
SQL injection 방지
데이터 정확성 보장

**NULL 처리의 특징
-- NULL vs 빈 문자열('')
WHERE column IS NOT NULL  -- NULL이 아닌 값
WHERE column != ''        -- 빈 문자열이 아닌 값
WHERE column IS NULL     -- NULL인 값

-- trim() 함수 사용 : 앞뒤 공백만 제거하고 중간에 있는 공백은 제거하지 않는다.
TRIM([LEADING | TRAILING | BOTH] [문자열] FROM 문자열)
LTRIM(): 왼쪽 공백 제거
RTRIM(): 오른쪽 공백 제거

**Prepared Statement
-- 장점
1. SQL Injection 방지
2. 쿼리 실행 계획 재사용 (성능 향상)
3. 타입 안정성 보장
4. 쿼리 캐싱 가능

-- 사용 예
const query = 'SELECT * FROM users WHERE id = ?';
db.get(query, [userId]);

**데이터 필터링 예시
-- NULL과 빈 문자열 모두 처리
SELECT * FROM table 
WHERE column IS NOT NULL 
  AND TRIM(column) != '';

-- 복합 조건
SELECT * FROM table 
WHERE column IS NOT NULL 
  AND TRIM(column) != ''
  AND column LIKE 'pattern%';

**데이터 무결성 관련
-- 제약조건 설정
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT DEFAULT ''
);

-- CHECK 제약조건
ALTER TABLE example 
ADD CONSTRAINT check_name 
CHECK (TRIM(name) != '');

결과적으로 null 자체를 제외해서 데이터를 가져오기 때문에 null 값이 있던 곳은 반영이 되지 않는다.