1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE TABLE "users"
(
    user_id       SERIAL PRIMARY KEY,                  -- 用户ID，自动递增
    user_name     VARCHAR(50)         NOT NULL,        -- 用户名
    email         VARCHAR(100) UNIQUE NOT NULL,        -- 用户的邮箱，唯一且不能为空
    password      VARCHAR(255)        NOT NULL,        -- 用户的密码
    date_of_birth DATE,                                -- 用户的出生日期
    phone_number  VARCHAR(20),                         -- 用户的电话号码
    address       TEXT,                                -- 用户的地址
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 用户创建时间
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 用户更新时间
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
    RETURNS TRIGGER AS
$$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP; -- 更新为当前时间
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER set_updated_at
    BEFORE UPDATE
    ON "user"
    FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mincai.study.postgresql.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.mincai.study.postgresql.domain.User">
            <id property="userId" column="user_id" jdbcType="INTEGER"/>
            <result property="userName" column="user_name" jdbcType="VARCHAR"/>
            <result property="email" column="email" jdbcType="VARCHAR"/>
            <result property="password" column="password" jdbcType="VARCHAR"/>
            <result property="dateOfBirth" column="date_of_birth" jdbcType="DATE"/>
            <result property="phoneNumber" column="phone_number" jdbcType="VARCHAR"/>
            <result property="address" column="address" jdbcType="VARCHAR"/>
            <result property="createdAt" column="created_at" jdbcType="TIMESTAMP"/>
            <result property="updatedAt" column="updated_at" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        user_id,user_name,email,
        password,date_of_birth,phone_number,
        address,created_at,updated_at
    </sql>
</mapper>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

@SpringBootTest
class PostgresqlApplicationTests {

    @Resource
    UserService userService;

    @Test
    void testUserService() {
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUserName("用户" + i);
            user.setEmail(i + "@qq.com");
            user.setPassword(UUID.randomUUID().toString());
            user.setDateOfBirth(DateTime.now());
            user.setPhoneNumber(String.valueOf(i));
            user.setAddress("111");
            userList.add(user);
        }

        // 增
        userService.saveBatch(userList);

        // 删除
        userService.removeById(10);

        // 改
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.ge(User::getPhoneNumber, "3");
        User user = new User();
        user.setUserName("new id");
        userService.update(user, queryWrapper);
        userService.update(user, queryWrapper);

        // 查
        List<User> users = userService.list(queryWrapper);
        System.out.println(users);
    }
}