3.1 Лабораторийн зорилго
Энэ лабораторид та:
- PostgreSQL (эсвэл H2)-д хүснэгтүүд үүсгэх
- SQL командуудаар өгөгдөл удирдах
- JOIN, агрегат функц, индекс ашиглах
- Spring Boot JPA Entity Mapping хийх
Хэл: SQL (PostgreSQL) + Java 17+ | IDE: Eclipse IDE + DBeaver (эсвэл H2 Console)
3.2 Лаб 1: Мэдээллийн сангийн хүснэгтүүд үүсгэх (DDL)
Алхам 1: Хүснэгтүүд үүсгэх
-- 1. Тэнхимүүд
CREATE TABLE departments (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(100) NOT NULL UNIQUE,
code VARCHAR(10) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 2. Оюутнууд
CREATE TABLE students (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
gpa DECIMAL(3,2) CHECK (gpa >= 0.00 AND gpa <= 4.00),
status VARCHAR(20) DEFAULT 'ACTIVE',
department_id BIGINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- 3. Хичээлүүд
CREATE TABLE courses (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(100) NOT NULL,
code VARCHAR(10) NOT NULL UNIQUE,
credits INTEGER NOT NULL CHECK (credits > 0 AND credits <= 6),
department_id BIGINT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- 4. Бүртгэл (M:N завсрын хүснэгт)
CREATE TABLE enrollments (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
grade VARCHAR(2),
enrolled_at DATE DEFAULT CURRENT_DATE,
UNIQUE (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);
-- 5. Оюутны профайл (1:1)
CREATE TABLE student_profiles (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
student_id BIGINT NOT NULL UNIQUE,
bio TEXT,
avatar_url VARCHAR(500),
phone VARCHAR(20),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);
Алхам 2: Индексүүд үүсгэх
CREATE INDEX idx_students_email ON students(email);
CREATE INDEX idx_students_dept ON students(department_id);
CREATE INDEX idx_students_status ON students(status);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
3.3 Лаб 2: Өгөгдөл нэмэх ба хайх (DML + DQL)
Алхам 1: Өгөгдөл нэмэх
-- Тэнхимүүд
INSERT INTO departments (name, code) VALUES
('Компьютерийн шинжлэх ухаан', 'CSE'),
('Математик', 'MATH'),
('Физик', 'PHYS');
-- Оюутнууд
INSERT INTO students (name, email, gpa, department_id) VALUES
('Батболд', 'batbold@example.com', 3.75, 1),
('Сарангэрэл', 'sarangerel@example.com', 3.90, 1),
('Дорж', 'dorj@example.com', 3.20, 2),
('Оюуна', 'oyuuna@example.com', 3.60, 1),
('Ганбаатар', 'ganbaatar@example.com', 2.80, 3),
('Нарангэрэл', 'narangerel@example.com', 3.45, 2),
('Болдбаатар', 'boldbaatar@example.com', 3.10, 3),
('Цэцэгмаа', 'tsetsegmaa@example.com', 3.85, 1),
('Энхбат', 'enkhbat@example.com', 2.50, 2),
('Алтанцэцэг', 'altantsetseg@example.com', 3.95, 1);
-- Хичээлүүд
INSERT INTO courses (name, code, credits, department_id) VALUES
('Java програмчлал', 'CS101', 3, 1),
('Мэдээллийн сан', 'CS201', 3, 1),
('Веб хөгжүүлэлт', 'CS301', 4, 1),
('Математик анализ', 'MATH101', 4, 2),
('Статистик', 'MATH201', 3, 2),
('Квантум физик', 'PHYS301', 3, 3);
-- Бүртгэл
INSERT INTO enrollments (student_id, course_id, grade) VALUES
(1, 1, 'A'), (1, 2, 'A-'), (1, 3, 'B+'),
(2, 1, 'A+'), (2, 2, 'A'),
(3, 4, 'B'), (3, 5, 'B+'),
(4, 1, 'A-'), (4, 3, 'A'),
(5, 6, 'C+'),
(6, 4, 'A'), (6, 5, 'A-'),
(8, 1, 'A'), (8, 2, 'A+'), (8, 3, 'A'),
(10, 1, 'A+'), (10, 2, 'A+');
-- Профайл
INSERT INTO student_profiles (student_id, bio, phone) VALUES
(1, 'Java хөгжүүлэгч болох зорилготой', '99001122'),
(2, 'Full-stack developer', '99003344');
Алхам 2: SELECT хайлтууд
-- 1. GPA >= 3.5 оюутнууд
SELECT name, email, gpa FROM students WHERE gpa >= 3.5 ORDER BY gpa DESC;
-- 2. Тэнхим бүрийн оюутны тоо, дундаж GPA
SELECT d.name as department, COUNT(s.id) as student_count,
ROUND(AVG(s.gpa), 2) as avg_gpa
FROM departments d
LEFT JOIN students s ON d.id = s.department_id
GROUP BY d.name
ORDER BY avg_gpa DESC;
-- 3. Хамгийн олон оюутантай тэнхим
SELECT d.name, COUNT(s.id) as count
FROM departments d
JOIN students s ON d.id = s.department_id
GROUP BY d.name
ORDER BY count DESC
LIMIT 1;
-- 4. Java хичээл авсан оюутнууд
SELECT s.name, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE c.code = 'CS101'
ORDER BY s.name;
-- 5. Оюутан бүрийн авсан хичээлийн тоо
SELECT s.name, COUNT(e.course_id) as course_count
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.name
ORDER BY course_count DESC;
-- 6. A+ дүнтэй оюутнууд
SELECT DISTINCT s.name, s.email
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.grade = 'A+';
-- 7. Хичээл аваагүй оюутнууд
SELECT s.name, s.email
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;
-- 8. Дундаж GPA-аас дээш оюутнууд (Subquery)
SELECT name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students);
-- 9. Хичээл бүрийн оюутны тоо
SELECT c.name as course, c.code, COUNT(e.student_id) as students
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id
GROUP BY c.name, c.code
ORDER BY students DESC;
-- 10. UPDATE: GPA шинэчлэх
UPDATE students SET gpa = 3.80, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
3.4 Лаб 3: JPA Entity Mapping (Spring Boot)
Алхам 1: Department Entity
// src/main/java/com/example/studentapi/entity/Department.java
package com.example.studentapi.entity;
import jakarta.persistence.*;
import java.util.ArrayList;
import java.util.List;
@Entity
@Table(name = "departments")
public class Department {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, unique = true, length = 100)
private String name;
@Column(nullable = false, unique = true, length = 10)
private String code;
@OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
private List<Student> students = new ArrayList<>();
public Department() {}
// Getter, Setter
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getCode() { return code; }
public void setCode(String code) { this.code = code; }
public List<Student> getStudents() { return students; }
public void setStudents(List<Student> students) { this.students = students; }
}
Алхам 2: Student Entity (харилцаатай)
// src/main/java/com/example/studentapi/entity/Student.java
package com.example.studentapi.entity;
import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.HashSet;
import java.util.Set;
@Entity
@Table(name = "students")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, length = 100)
private String name;
@Column(nullable = false, unique = true, length = 150)
private String email;
@Column(precision = 3, scale = 2)
private BigDecimal gpa;
@Enumerated(EnumType.STRING)
@Column(length = 20)
private StudentStatus status = StudentStatus.ACTIVE;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "department_id")
private Department department;
@OneToOne(mappedBy = "student", cascade = CascadeType.ALL,
fetch = FetchType.LAZY)
private StudentProfile profile;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(
name = "enrollments",
joinColumns = @JoinColumn(name = "student_id"),
inverseJoinColumns = @JoinColumn(name = "course_id")
)
private Set<Course> courses = new HashSet<>();
@Column(name = "created_at")
private LocalDateTime createdAt = LocalDateTime.now();
@Column(name = "updated_at")
private LocalDateTime updatedAt = LocalDateTime.now();
public Student() {}
// Getter, Setter
public Long getId() { return id; }
public void setId(Long 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; }
public BigDecimal getGpa() { return gpa; }
public void setGpa(BigDecimal gpa) { this.gpa = gpa; }
public StudentStatus getStatus() { return status; }
public void setStatus(StudentStatus status) { this.status = status; }
public Department getDepartment() { return department; }
public void setDepartment(Department department) { this.department = department; }
public StudentProfile getProfile() { return profile; }
public void setProfile(StudentProfile profile) { this.profile = profile; }
public Set<Course> getCourses() { return courses; }
public void setCourses(Set<Course> courses) { this.courses = courses; }
}
Алхам 3: N+1 шийдэл — JOIN FETCH
// StudentRepository.java
public interface StudentRepository extends JpaRepository<Student, Long> {
// N+1 асуудалгүй — 1 query-ээр бүгдийг авна
@Query("SELECT s FROM Student s JOIN FETCH s.department")
List<Student> findAllWithDepartment();
// Тэнхимээр шүүх
@Query("SELECT s FROM Student s JOIN FETCH s.department d WHERE d.code = :code")
List<Student> findByDepartmentCode(@Param("code") String code);
// GPA-аар шүүх + тэнхимтэй
@Query("SELECT s FROM Student s JOIN FETCH s.department WHERE s.gpa >= :minGpa")
List<Student> findByMinGpaWithDepartment(@Param("minGpa") BigDecimal minGpa);
}
3.5 Лаб 4: Transaction ба Бизнес логик
// StudentService.java — Transaction жишээ
@Service
public class StudentService {
private final StudentRepository studentRepository;
private final EnrollmentRepository enrollmentRepository;
public StudentService(StudentRepository studentRepository,
EnrollmentRepository enrollmentRepository) {
this.studentRepository = studentRepository;
this.enrollmentRepository = enrollmentRepository;
}
@Transactional
public void enrollStudentInCourse(Long studentId, Long courseId) {
// 1. Оюутан олох
Student student = studentRepository.findById(studentId)
.orElseThrow(() -> new StudentNotFoundException(
"ID=" + studentId + " оюутан олдсонгүй"
));
// 2. Бүртгэлтэй эсэхийг шалгах
if (enrollmentRepository.existsByStudentIdAndCourseId(studentId, courseId)) {
throw new DuplicateEnrollmentException(
"Оюутан энэ хичээлд бүртгэлтэй байна"
);
}
// 3. Бүртгэл үүсгэх
Enrollment enrollment = new Enrollment();
enrollment.setStudentId(studentId);
enrollment.setCourseId(courseId);
enrollmentRepository.save(enrollment);
// 4. Оюутны хичээлийн тоо шинэчлэх
// Алдаа гарвал бүгд ROLLBACK — Atomicity!
}
}
---
---