202226701045/poem-life-serve/db/dbServe.js

const express = require('express');
const mysql = require('mysql2');
const cors = require('cors');

const app = express();
const PORT = 3000;

// CORS 设置
app.use(cors());
app.use(express.json());

// MySQL 连接设置
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root', // 替换为您的MySQL用户名
    password: '123456', // 替换为您的MySQL密码
    database: 'poemLifeApp',
    ssl: false  // 禁用SSL连接（如果不需要）
});

// 连接到数据库
db.connect(err => {
    if (err) {
        console.error('Database connection failed: ' + err.stack);
        return;
    }
    console.log('Connected to database.');
});

// 获取书籍列表的 API
app.get('/api/books', (req, res) => {
    let query = `
        SELECT 
            b.id, 
            b.title, 
            c.name AS category, 
            COUNT(p.id) AS count 
        FROM 
            books b 
        LEFT JOIN 
            categories c ON b.category_id = c.id 
        LEFT JOIN 
            poems p ON b.id = p.book_id 
        GROUP BY 
            b.id, b.title, c.name`;
    const categoryId = req.query.category_id;
    if (categoryId) {
        query += ` WHERE b.category_id = ${categoryId}`;
    }
    db.query(query, (err, results) => {
        if (err) {
            return res.status(500).json({ error: err });
        }
        res.json(results);
    });
});

// 获取诗词列表的 API
app.get('/api/poems/:bookId', (req, res) => {
    const bookId = req.params.bookId;
    const query = 'SELECT * FROM poems WHERE book_id = ?';
    db.query(query, [bookId], (err, results) => {
        if (err) return res.status(500).json({ error: err });
        res.json(results);
    });
});

// 获取诗句内容的 API
app.get('/api/poem-contents/:poemId', (req, res) => {
    const poemId = req.params.poemId;
    const query = 'SELECT content FROM poem_contents WHERE poem_id = ?';
    db.query(query, [poemId], (err, results) => {
        if (err) return res.status(500).json({ error: err });
        if (results.length === 0) return res.status(404).json({ message: 'Content not found' });
        res.json(results[0]);
    });
});

// 获取分类列表的 API
app.get('/api/categories', (req, res) => {
    const query = 'SELECT * FROM categories';
    db.query(query, (err, results) => {
        if (err) {
            return res.status(500).json({ error: err });
        }
        res.json(results); // 返回分类数据
    });
});

// 启动服务器
app.listen(PORT, () => {
    console.log(`Server is running on http://localhost:${PORT}`);
});