78 lines
3.8 KiB
Java
78 lines
3.8 KiB
Java
|
package com.example.util;
|
||
|
|
|
||
|
|
public class SqlQueries {
|
||
|
|
// 查询
|
||
|
|
public static final String GET_ALL_USERS =
|
||
|
|
"SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users ORDER BY id";
|
||
|
|
|
||
|
|
public static final String GET_USER_BY_ID =
|
||
|
|
"SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users WHERE id = :id";
|
||
|
|
|
||
|
|
public static final String GET_USER_BY_USERNAME =
|
||
|
|
"SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users WHERE username = :username";
|
||
|
|
|
||
|
|
// 分页查询
|
||
|
|
public static final String GET_USERS_PAGED =
|
||
|
|
"SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users ORDER BY id LIMIT :limit OFFSET :offset";
|
||
|
|
|
||
|
|
// 高级分页查询（支持排序和条件筛选）
|
||
|
|
public static final String GET_USERS_ADVANCED =
|
||
|
|
"SELECT id, username, email, full_name, phone, create_time, update_time, active FROM users " +
|
||
|
|
"WHERE 1=1 " +
|
||
|
|
"#{if}(:username != '') AND username LIKE '%' || :username || '%'#{end} " +
|
||
|
|
"#{if}(:email != '') AND email LIKE '%' || :email || '%'#{end} " +
|
||
|
|
"#{if}(:phone != '') AND phone LIKE '%' || :phone || '%'#{end} " +
|
||
|
|
"#{if}(:active != null) AND active = :active#{end} " +
|
||
|
|
"#{if}(:fromDate != '') AND create_time >= TO_TIMESTAMP(:fromDate, 'YYYY-MM-DD')#{end} " +
|
||
|
|
"#{if}(:toDate != '') AND create_time <= TO_TIMESTAMP(:toDate, 'YYYY-MM-DD') + INTERVAL '1 day'#{end} " +
|
||
|
|
"ORDER BY " +
|
||
|
|
"#{if}(:sortField = 'username') username#{end} " +
|
||
|
|
"#{if}(:sortField = 'email') email#{end} " +
|
||
|
|
"#{if}(:sortField = 'createTime') create_time#{end} " +
|
||
|
|
"#{if}(:sortField = '') id#{end} " +
|
||
|
|
"#{if}(:sortOrder = 'ASC') ASC#{else} DESC#{end} " +
|
||
|
|
"LIMIT :limit OFFSET :offset";
|
||
|
|
|
||
|
|
public static final String COUNT_USERS_ADVANCED =
|
||
|
|
"SELECT COUNT(*) as total FROM users " +
|
||
|
|
"WHERE 1=1 " +
|
||
|
|
"#{if}(:username != '') AND username LIKE '%' || :username || '%'#{end} " +
|
||
|
|
"#{if}(:email != '') AND email LIKE '%' || :email || '%'#{end} " +
|
||
|
|
"#{if}(:phone != '') AND phone LIKE '%' || :phone || '%'#{end} " +
|
||
|
|
"#{if}(:active != null) AND active = :active#{end} " +
|
||
|
|
"#{if}(:fromDate != '') AND create_time >= TO_TIMESTAMP(:fromDate, 'YYYY-MM-DD')#{end} " +
|
||
|
|
"#{if}(:toDate != '') AND create_time <= TO_TIMESTAMP(:toDate, 'YYYY-MM-DD') + INTERVAL '1 day'#{end}";
|
||
|
|
|
||
|
|
public static final String COUNT_USERS =
|
||
|
|
"SELECT COUNT(*) as total FROM users";
|
||
|
|
|
||
|
|
// 插入
|
||
|
|
public static final String CREATE_USER =
|
||
|
|
"INSERT INTO users (username, email, password, full_name, phone, active, create_time, update_time) " +
|
||
|
|
"VALUES (:username, :email, :password, :fullName, :phone, :active, NOW(), NOW()) RETURNING id";
|
||
|
|
|
||
|
|
// 更新
|
||
|
|
public static final String UPDATE_USER =
|
||
|
|
"UPDATE users SET username = :username, email = :email, " +
|
||
|
|
"password = CASE WHEN :password = '' THEN password ELSE :password END, " +
|
||
|
|
"full_name = :fullName, phone = :phone, active = :active, update_time = NOW() " +
|
||
|
|
"WHERE id = :id";
|
||
|
|
|
||
|
|
// 删除
|
||
|
|
public static final String DELETE_USER =
|
||
|
|
"DELETE FROM users WHERE id = :id";
|
||
|
|
|
||
|
|
// 创建用户表 - 用于初始化数据库
|
||
|
|
public static final String CREATE_USERS_TABLE =
|
||
|
|
"CREATE TABLE IF NOT EXISTS users (" +
|
||
|
|
"id SERIAL PRIMARY KEY, " +
|
||
|
|
"username VARCHAR(50) NOT NULL UNIQUE, " +
|
||
|
|
"email VARCHAR(100) NOT NULL UNIQUE, " +
|
||
|
|
"password VARCHAR(255) NOT NULL, " +
|
||
|
|
"full_name VARCHAR(100), " +
|
||
|
|
"phone VARCHAR(20), " +
|
||
|
|
"active BOOLEAN DEFAULT TRUE, " +
|
||
|
|
"create_time TIMESTAMP NOT NULL, " +
|
||
|
|
"update_time TIMESTAMP NOT NULL" +
|
||
|
|
")";
|
||
|
|
}
|