书接上回，上回说到我们已经完成了数据库初始化，现在需要对数据库的数据进行读取并构建仪表板概览页面。
选择如何获取数据
API层
API 是应用程序代码和数据库之间的中间层。在以下几种情况下，您可能会使用 API：
1、如果您正在使用提供 API 的第三方服务。
2、如果您从客户端获取数据，则需要有一个在服务器上运行的API层，以避免向客户端暴露您的数据库机密。
数据库查询
在创建全栈应用程序时，您还需要编写逻辑来与数据库交互。对于关系数据库像 Postgres 一样，你可以使用 SQL 或ORM来执行此操作。在某些情况下你必须编写数据库查询：
1、创建 API 端点时，您需要编写逻辑来与数据库交互。
2、如果您正在使用 React Server Components（在服务器上获取数据），则可以跳过API层，直接查询数据库，而不必担心向客户端泄露数据库机密。
使用服务器组件获取数据
默认情况下，Next.js 应用程序使用React服务器组件。使用服务器组件获取数据是一种相对较新的方法，使用它们有几个好处：
1、服务器组件支持承诺，为数据获取等异步任务提供更简单的解决方案。您可以使用async/await语法，而无需使用useEffect、useState或数据获取库。
2、服务器组件在服务器上执行，因此您可以在服务器上保留昂贵的数据提取和逻辑，而仅将结果发送给客户端。
如前所述，由于服务器组件在服务器上执行，因此您可以直接查询数据库，而无需额外的 API 层。
使用 SQL
对于仪表板项目，您将使用Vercel Postgres SDK编写数据库查询和 SQL。我们使用 SQL 的原因如下：
1、SQL 是查询关系数据库的行业标准（例如，ORM 在后台生成 SQL）。
2、对 SQL 有基本的了解可以帮助您理解关系数据库的基础知识，从而让您将知识应用到其他工具中。
3、SQL 功能多样，可让您获取和操作特定数据。
4、Vercel Postgres SDK 提供针对SQL 注入的保护。
获取仪表盘概览页的数据
再上面我们已经了解了有上面方法获取数据，让我们来获取仪表板概览页面的数据。首先导航到/app/dashboard/page.tsx
我先现在要达到这样的效果，导航栏我们在前面章节已经实现了，现在我们需要实现Recent Revenue（近期收入）、Latest Invoices（近期发票）两个模块。
好，理解了上面要实现的功能，首先我们需要写一个data.ts用于实现读取数据库数据的函数 /app/lib/data.ts和 /app/lib/db.ts
首先我们编辑db.ts，现在创建一个单独的文件来管理数据库连接，然后在 data.ts 中使用它，这样可以实现连接的复用和更好的代码组织。
编辑db.ts,在根目录上面创建.env 这样是为了安全，杜绝数据库账号密码硬编码在代码里面
#Postgresql 数据库
DB_USER=postgres
DB_HOST=localhost
DB_NAME=nextjsdbs
DB_PASSWORD=12345678
DB_PORT=5432
import { Pool, QueryResult, QueryResultRow } from 'pg';
const pool = new Pool({
user: process.env.DB_USER,
host: process.env.DB_HOST,
database: process.env.DB_NAME,
password: process.env.DB_PASSWORD,
port: parseInt(process.env.DB_PORT || '5432'),
});
export async function query<T extends QueryResultRow>(
text: string,
params?: unknown[]
): Promise<T[]> {
const client = await pool.connect();
try {
const result: QueryResult<T> = await client.query(text, params);
return result.rows;
} finally {
client.release();
}
}
export default pool;
query函数一个泛型函数，其中 T 是一个类型参数，它必须是 QueryResultRow 的子类型。
函数接受两个参数：text（SQL 查询字符串）和可选的 params（查询参数数组）。
函数返回一个 Promise，解析为 T 类型的数组。
<T extends QueryResultRow>: 这是泛型声明。T 是一个类型变量，它必须是 QueryResultRow 的子类型。这允许我们为不同的查询指定具体的返回类型。
接下来要编辑data.ts实现Recent Revenue近期收入的查询函数。首先需要定义收入的类型，这里要和数据库的一致，然后请求数据库。
最后回到我们的page.tsx页面，然后我们调用data.ts中的fetchRevenue函数，测试是否能够从数据库中获取数据。
在这里，只是简单的打印出函数获取的结果，如果成功则进行下一步将数据封装到前端页面
import { fetchRevenue } from "@/app/lib/data";
export default async function Page() {
const revenue = await fetchRevenue();
console.log(`revenue => ${JSON.stringify(revenue, null, 2)}`);
return <div>仪表盘 页面</div>;
}
刷新页面http://172.16.100.104/dashboard，结果从数据库获取成功 ！！！
现在我们需要将结果封装到我们的树状图里面，创建文/app/ui/dashboard/revenue-chart.tsx，/app/lib/utils/utils.ts实现树状图。
revenue-chart.tsx 用于实现页面结构
import { generateYAxis } from "@/app/lib/utils";
import { CalendarIcon } from "@heroicons/react/24/outline";
import { lusitana } from "@/app/ui/fonts";
import { Revenue } from "@/app/lib/data";
// This component is representational only.
// For data visualization UI, check out:
// https://www.tremor.so/
// https://www.chartjs.org/
// https://airbnb.io/visx/
export default async function RevenueChart({
revenue,
}: {
revenue: Revenue[];
}) {
const chartHeight = 350;
// NOTE: Uncomment this code in Chapter 7
const { yAxisLabels, topLabel } = generateYAxis(revenue);
if (!revenue || revenue.length === 0) {
return <p className="mt-4 text-gray-400">No data available.</p>;
}
return (
<div className="w-full md:col-span-4">
<h2 className={`${lusitana.className} mb-4 text-xl md:text-2xl`}>
Recent Revenue
</h2>
{/* NOTE: Uncomment this code in Chapter 7 */}
<div className="rounded-xl bg-gray-50 p-4">
<div className="mt-0 flex items-end gap-2 rounded-md bg-white p-4 md:gap-4">
<div
className="mb-6 flex flex-col justify-between text-xs text-gray-400"
style={{ height: `${chartHeight}px` }}
>
{yAxisLabels.map((label) => (
<p key={label}>{label}</p>
))}
</div>
<div className="flex-1 flex items-end gap-2 md:gap-4">
{revenue.map((month) => (
<div
key={month.month}
className="flex flex-col items-center flex-1"
>
<div
className="w-full rounded-md bg-blue-300"
style={{
height: `${(chartHeight / topLabel) * month.revenue}px`,
}}
></div>
<p className="-rotate-90 text-sm text-gray-400 sm:rotate-0 mt-2">
{month.month}
</p>
</div>
))}
</div>
</div>
<div className="flex items-center pb-2 pt-6">
<CalendarIcon className="h-5 w-5 text-gray-500" />
<h3 className="ml-2 text-sm text-gray-500 ">Last 12 months</h3>
</div>
</div>
</div>
);
}
utils.ts 用于格式化货币、日期和生成 Y 轴标签
import { Revenue } from "./data";
export const formatCurrency = (amount: number) => {
return (amount / 100).toLocaleString('zh-CN', {
style: 'currency',
currency: 'CNY',
});
};
export const formatDateToLocal = (
dateStr: string,
locale: string = 'zh-CN',
) => {
const date = new Date(dateStr);
const options: Intl.DateTimeFormatOptions = {
day: 'numeric',
month: 'short',
year: 'numeric',
};
const formatter = new Intl.DateTimeFormat(locale, options);
return formatter.format(date);
};
export const generateYAxis = (revenue: Revenue[]) => {
// Calculate what labels we need to display on the y-axis
// based on highest record and in 1000s
const yAxisLabels = [];
const highestRecord = Math.max(...revenue.map((month) => month.revenue));
const topLabel = Math.ceil(highestRecord / 1000) * 1000;
for (let i = topLabel; i >= 0; i -= 1000) {
yAxisLabels.push(`$${i / 1000}K`);
}
return { yAxisLabels, topLabel };
};
同理，我们现在实现发票的栏目组件，按照上面的套路，首先写一个读取数据库发票表的函数。
编辑 /app/lib/data.ts，定义最新发票的数据结构
export type LatestInvoice = {
id: string;
name: string;
image_url: string;
email: string;
amount: string;
};
编辑/app/lib/utils.ts，实现处理费用单位函数
export const formatCurrency = (amount: number) => {
return (amount / 100).toLocaleString('en-US', {
style: 'currency',
currency: 'USD',
});
};
编辑/app/lib/data.ts，实现查询数据库发票数据
export type LatestInvoiceRaw = Omit<LatestInvoice, "amount"> & {
amount: number;
};
export async function fetchLatestInvoices(): Promise<LatestInvoice[]> {
try {
const rows: LatestInvoiceRaw[] = await query<LatestInvoiceRaw>(`
SELECT invoices.amount, customers.name, customers.image_url, customers.email, invoices.id
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
ORDER BY invoices.date DESC
LIMIT 5
`);
const latestInvoices: LatestInvoice[] = rows.map(
(invoice: LatestInvoiceRaw) => ({
...invoice,
amount: formatCurrency(invoice.amount),
})
);
return latestInvoices;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取最新发票失败。");
}
}
为什么要使用LatestInvoiceRaw这样的结构
export type LatestInvoiceRaw = Omit<LatestInvoice, "amount"> & {
amount: number;
};
数据库存储
在数据库中，金额通常以数字形式存储（例如：1000）。这是为了方便进行数学运算和排序。应用程序显示
在应用程序中，我们通常希望以格式化的字符串形式显示金额（例如："$1,000.00"）。这样更易读，符合用户期望。类型安全
通过定义两种类型，我们可以在代码的不同部分明确使用正确的类型，减少错误。
为什么要使用 Omit 和 &
Omit<LatestInvoice, "amount"> 创建了一个新类型，它包含 LatestInvoice 的所有属性，除了 "amount"。
& { amount: number } 然后添加了一个新的 "amount" 属性，类型为 number。
这种方法允许我们重用大部分 LatestInvoice 的定义，只改变 "amount" 的类型。
当从数据库获取数据时，我们使用 LatestInvoiceRaw（amount 是 number）。
在处理数据时，我们将amount转换为格式化的字符串。最终，我们返回 LatestInvoice 类型的数组，其中 amount 是字符串。
查询函数实现之后，就可以在page.tsx进行使用了，编辑 /app/dashboard/page.tsx
import { fetchLatestInvoices } from "@/app/lib/data";
在函数里面实现功能
const latestInvoices = await fetchLatestInvoices();
console.log(latestInvoices);
完成数据获取之后，现在需要将数据实现在页面当中，编辑app\ui\dashboard\latest-invoices.tsx
import { ArrowPathIcon } from "@heroicons/react/24/outline";
import clsx from "clsx";
import Image from "next/image";
import { lusitana } from "@/app/ui/fonts";
import { LatestInvoice } from "@/app/lib/data";
export default async function LatestInvoices({
latestInvoices,
}: {
latestInvoices: LatestInvoice[];
}) {
return (
<div className="flex w-full flex-col md:col-span-4">
<h2 className={`${lusitana.className} mb-4 text-xl md:text-2xl`}>
最新发票
</h2>
<div className="flex grow flex-col justify-between rounded-xl bg-gray-50 p-4">
{/* NOTE: Uncomment this code in Chapter 7 */}
<div className="bg-white px-6">
{latestInvoices.map((invoice, i) => {
return (
<div
key={invoice.id}
className={clsx(
"flex flex-row items-center justify-between py-4",
{
"border-t": i !== 0,
}
)}
>
<div className="flex items-center">
<Image
src={invoice.image_url}
alt={`${invoice.name}'s profile picture`}
className="mr-4 rounded-full"
width={32}
height={32}
/>
<div className="min-w-0">
<p className="truncate text-sm font-semibold md:text-base">
{invoice.name}
</p>
<p className="hidden text-sm text-gray-500 sm:block">
{invoice.email}
</p>
</div>
</div>
<p
className={`${lusitana.className} truncate text-sm font-medium md:text-base`}
>
{invoice.amount}
</p>
</div>
);
})}
</div>
<div className="flex items-center pb-2 pt-6">
<ArrowPathIcon className="h-5 w-5 text-gray-500" />
<h3 className="ml-2 text-sm text-gray-500 ">Updated just now</h3>
</div>
</div>
</div>
);
}
在page页面中引用发票模版<LatestInvoices latestInvoices={latestInvoices} />
接下来我们要在页面的上方创建一个统计卡片目录，用于显示目前的统计数据，按照管理手下我们先要在数据库中提取统计数据。
编辑 /app/lib/data.ts , 完整代码，包含之前的改动
import { query } from "@/app/lib/db";
import { formatCurrency } from "@/app/lib/utils";
export type Revenue = {
month: string;
revenue: number;
};
export async function fetchRevenue(): Promise<Revenue[]> {
try {
const rows: Revenue[] = await query<Revenue>("SELECT * FROM revenue");
return rows;
} catch (error) {
throw new Error("获取数据失败");
}
}
export type LatestInvoice = {
id: string;
name: string;
image_url: string;
email: string;
amount: string;
};
export type LatestInvoiceRaw = Omit<LatestInvoice, "amount"> & {
amount: number;
};
export async function fetchLatestInvoices(): Promise<LatestInvoice[]> {
try {
const rows: LatestInvoiceRaw[] = await query<LatestInvoiceRaw>(`
SELECT invoices.amount, customers.name, customers.image_url, customers.email, invoices.id
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
ORDER BY invoices.date DESC
LIMIT 5
`);
const latestInvoices: LatestInvoice[] = rows.map(
(invoice: LatestInvoiceRaw) => ({
...invoice,
amount: formatCurrency(invoice.amount),
})
);
return latestInvoices;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取最新发票失败。");
}
}
export type CardData = {
numberOfCustomers: number;
numberOfInvoices: number;
totalPaidInvoices: string;
totalPendingInvoices: string;
};
type CardDataRaw = {
numberofcustomers: string;
numberofinvoices: string;
paid: string;
pending: string;
};
export async function fetchCardData(): Promise<CardData> {
try {
const data = await query<CardDataRaw>(`
SELECT
(SELECT COUNT(*) FROM customers) AS numberOfCustomers,
(SELECT COUNT(*) FROM invoices) AS numberOfInvoices,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending
FROM invoices
`);
// 确保我们有结果，并且取第一行
if (!data || data.length === 0) {
throw new Error("没有返回数据");
}
const cardData = data[0];
return {
numberOfCustomers: Number(cardData.numberofcustomers),
numberOfInvoices: Number(cardData.numberofinvoices),
totalPaidInvoices: formatCurrency(Number(cardData.paid)),
totalPendingInvoices: formatCurrency(Number(cardData.pending)),
};
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取卡片数据失败。");
}
}
const ITEMS_PER_PAGE = 6;
export type Invoice = {
id: string;
amount: number;
date: string;
status: string;
name: string;
email: string;
image_url: string;
};
export async function fetchFilteredInvoices(
searchQuery: string,
currentPage: number
): Promise<Invoice[]> {
const offset = (currentPage - 1) * ITEMS_PER_PAGE;
try {
const invoices = await query<Invoice>(
`
SELECT
invoices.id,
invoices.amount,
invoices.date,
invoices.status,
customers.name,
customers.email,
customers.image_url
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
WHERE
customers.name ILIKE $1 OR
customers.email ILIKE $1 OR
invoices.amount::text ILIKE $1 OR
invoices.date::text ILIKE $1 OR
invoices.status ILIKE $1
ORDER BY invoices.date DESC
LIMIT $2 OFFSET $3
`,
[`%${searchQuery}%`, ITEMS_PER_PAGE, offset]
);
return invoices;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取发票失败。");
}
}
export async function fetchInvoicesPages(searchQuery: string): Promise<number> {
try {
const result = await query<{ count: string }>(
`
SELECT COUNT(*)
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
WHERE
customers.name ILIKE $1 OR
customers.email ILIKE $1 OR
invoices.amount::text ILIKE $1 OR
invoices.date::text ILIKE $1 OR
invoices.status ILIKE $1
`,
[`%${searchQuery}%`]
);
const totalPages = Math.ceil(Number(result[0].count) / ITEMS_PER_PAGE);
return totalPages;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取发票总页数失败。");
}
}
export type InvoiceForm = {
id: string;
customer_id: string;
amount: number;
status: string;
};
export async function fetchInvoiceById(id: string): Promise<InvoiceForm> {
try {
const invoices = await query<InvoiceForm>(
`
SELECT
invoices.id,
invoices.customer_id,
invoices.amount,
invoices.status
FROM invoices
WHERE invoices.id = $1
`,
[id]
);
if (invoices.length === 0) {
throw new Error("发票未找到。");
}
const invoice = {
...invoices[0],
amount: invoices[0].amount / 100, // 将分转换为元
};
return invoice;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取发票失败。");
}
}
export type CustomerField = {
id: string;
name: string;
};
export async function fetchCustomers(): Promise<CustomerField[]> {
try {
const customers = await query<CustomerField>(`
SELECT
id,
name
FROM customers
ORDER BY name ASC
`);
return customers;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取所有客户失败。");
}
}
// 修改 CustomerTableType 定义
export type CustomerTableType = {
id: string;
name: string;
email: string;
image_url: string;
total_invoices: number;
total_pending: string; // 改为 string
total_paid: string; // 改为 string
};
// 修改 fetchFilteredCustomers 函数
export async function fetchFilteredCustomers(
searchQuery: string
): Promise<CustomerTableType[]> {
try {
const customersRaw = await query<
Omit<CustomerTableType, "total_pending" | "total_paid"> & {
total_pending: number;
total_paid: number;
}
>(
`
SELECT
customers.id,
customers.name,
customers.email,
customers.image_url,
COUNT(invoices.id) AS total_invoices,
SUM(CASE WHEN invoices.status = 'pending' THEN invoices.amount ELSE 0 END) AS total_pending,
SUM(CASE WHEN invoices.status = 'paid' THEN invoices.amount ELSE 0 END) AS total_paid
FROM customers
LEFT JOIN invoices ON customers.id = invoices.customer_id
WHERE
customers.name ILIKE $1 OR
customers.email ILIKE $1
GROUP BY customers.id, customers.name, customers.email, customers.image_url
ORDER BY customers.name ASC
`,
[`%${searchQuery}%`]
);
const customers: CustomerTableType[] = customersRaw.map((customer) => ({
...customer,
total_pending: formatCurrency(customer.total_pending),
total_paid: formatCurrency(customer.total_paid),
}));
return customers;
} catch (error) {
console.error("数据库错误:", error);
throw new Error("获取客户表格失败。");
}
}
编辑 /app/ui/dashboard/Cards.tsx , 完整代码
import {
BanknotesIcon,
ClockIcon,
UserGroupIcon,
InboxIcon,
} from "@heroicons/react/24/outline";
import { lusitana } from "@/app/ui/fonts";
const iconMap = {
collected: BanknotesIcon,
customers: UserGroupIcon,
pending: ClockIcon,
invoices: InboxIcon,
};
export function Card({
title,
value,
type,
}: {
title: string;
value: number | string;
type: "invoices" | "customers" | "pending" | "collected";
}) {
const Icon = iconMap[type];
return (
<div className="rounded-xl bg-gray-50 p-2 shadow-sm">
<div className="flex p-4">
{Icon ? <Icon className="h-5 w-5 text-gray-700" /> : null}
<h3 className="ml-2 text-sm font-medium">{title}</h3>
</div>
<p
className={`${lusitana.className}
truncate rounded-xl bg-white px-4 py-8 text-center text-2xl`}
>
{value}
</p>
</div>
);
}
编辑 /app/dashboard/page.tsx , 完整代码
import { fetchRevenue } from "@/app/lib/data";
import { lusitana } from "@/app/ui/fonts";
import RevenueChart from "@/app/ui/dashboard/revenue-chart";
import LatestInvoices from "@/app/ui/dashboard/latest-invoices";
import { fetchLatestInvoices } from "@/app/lib/data";
import { Card } from "@/app/ui/dashboard/Cards";
import { fetchCardData } from "@/app/lib/data";
export default async function Page() {
const revenue = await fetchRevenue();
const latestInvoices = await fetchLatestInvoices();
const {
numberOfInvoices,
numberOfCustomers,
totalPaidInvoices,
totalPendingInvoices,
} = await fetchCardData();
return (
<main>
<h1 className={`${lusitana.className} mb-4 text-xl md:text-2xl`}>
Dashboard
</h1>
<div className="grid gap-6 sm:grid-cols-2 lg:grid-cols-4">
<Card title="Collected" value={totalPaidInvoices} type="collected" />
<Card title="Pending" value={totalPendingInvoices} type="pending" />
<Card title="Total Invoices" value={numberOfInvoices} type="invoices" />
<Card
title="Total Customers"
value={numberOfCustomers}
type="customers"
/>
</div>
<div className="grid gap-6 sm:grid-cols-2 lg:grid-cols-4"></div>
<div className="mt-6 grid grid-cols-1 gap-6 md:grid-cols-4 lg:grid-cols-8">
<RevenueChart revenue={revenue} />
<LatestInvoices latestInvoices={latestInvoices} />
</div>
</main>
);
}
以上就完成了数据库的读取和页面展示啦，更多详细内容，请继续关注我，我们下次再见。更详细内容查看更详细内容查看
独立博客 https://www.dataeast.cn/
CSDN博客 https://blog.csdn.net/siberiaWarpDrive
B站视频空间 https://space.bilibili.com/25871614?spm_id_from=333.1007.0.0
关注 “曲速引擎 Warp Drive” 微信公众号
