006.MyBatis访问PostgreSQL-常规方式

1.引入MyBatis和PostgreSQL依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

2.配置数据库链接和使用的驱动

spring.datasource.url=jdbc:postgresql://localhost:5432/javacore
spring.datasource.username=postgres
spring.datasource.password=asdf-1234
spring.datasource.driver-class-name=org.postgresql.Driver

3.创建表+创建自增长ID

-- Table: public.User

-- DROP TABLE IF EXISTS public."User";

CREATE TABLE IF NOT EXISTS public."User"
(
    id bigint NOT NULL,
    name text COLLATE pg_catalog."default",
    age bigint,
    CONSTRAINT "User_pkey" PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."User"
OWNER to postgres;


创建序列
create sequence table_name_id_seq start with 1 increment by 1 no minvalue no maxvalue cache 1;

设置表的ID字段的默认值为nextval(table_name_id_seq)
alter table public."User" alter column id set default nextval('table_name_id_seq');

4.创建实体类

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
public class User {
    private Long id;

    private String name;
    private Integer age;

    public User(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

5.创建Mapper

package com.java.core;

import org.apache.ibatis.annotations.*;

@Mapper
public interface UserMapper {

    @Select("SELECT * FROM \"User\" WHERE name = #{name}")
    User findByName(@Param("name") String name);

    @Insert("INSERT INTO \"User\"(name, age) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);

    @Update("UPDATE \"User\" SET age=#{age} WHERE name=#{name}")
    void update(User user);

    @Delete("DELETE FROM \"User\" WHERE id =#{id}")
    void delete(Long id);
}

6.测试调用

@SpringBootTest
class CoreApplicationTests {

    @Autowired
    private UserMapper userMapper;


    @Test
    public void testUserController() throws Exception {

        // insert一条数据，并select出来验证
        userMapper.insert("AAA", 20);
        User u = userMapper.findByName("AAA");

        // update一条数据，并select出来验证
        u.setAge(30);
        userMapper.update(u);
        u = userMapper.findByName("AAA");

        // 删除这条数据，并select验证
        userMapper.delete(u.getId());
        u = userMapper.findByName("AAA");


    }

}