博客

1 .创建postgre数据库

docker-compose.yml

version: “3”

services:

postgresql:

hostname: dqpostgre

restart: unless-stopped

image: postgres:9.6-alpine

volumes:

– ./configuration/initdb:/docker-entrypoint-initdb.d

– ./postgresqldata:/var/lib/postgresql/data

ports:

– “5432:5432/tcp”

2. 创建用户，数据库

sudo apt-get install postgresql-client

psql -U postgres -h localhost -p 5432

CREATE USER dbuser WITH PASSWORD ‘123’;

CREATE DATABASE exampledb OWNER dbuser;

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

\q

3.登录查询

psql -U dbuser -d exampledb -h localhost -p 5432

\dt

查询tables

exampledb=> select * from dbsxdl;

id | ia | ib | ic | dev_id

—-+—–+—–+—–+——–

1 | 5.3 | 6.2 | 7.8 | 125

(1 row)

4.application.yml

spring:
  datasource:
    url: jdbc:postgresql://192.168.0.123:5432/exampledb
    username: dbuser
    password: 123
    driverClassName: org.postgresql.Driver
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        temp:
           use_jdbc_metadata_defaults: false


server:
  port: 8999

5. Entity,reposity

public interface DbsxdlReposity extends JpaRepository<Dbsxdl,Integer> {
    @Query(value = "select * from dbsxdl where dev_id between ?1 and ?2",nativeQuery = true)
    List<Dbsxdl> findByDevid(long startid,long stopid);

}

@Slf4j
@Setter
@Getter
@ToString
@Entity
public class Dbsxdl {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    long devId;
    float IA;
    float IB;
    float IC;

    public Dbsxdl(long devId, float IA, float IB, float IC) {
        this.devId = devId;
        this.IA = IA;
        this.IB = IB;
        this.IC = IC;
    }

    public Dbsxdl(){

    }
}

测试

@RestController
public class TestControl {
    @Autowired
    DbsxdlReposity dbsxdlReposity;


    //写入例子
    @RequestMapping("/hello")
    public String getHelloString(){
        Dbsxdl dbsxdl = new Dbsxdl(125,5.3f,6.2f,7.8f);
        dbsxdlReposity.save(dbsxdl);
        return "hello world";
    }

    // localhost:8999/findid?startid=3&stopid=155  查询例子
    @RequestMapping(value =  "/findid",method = RequestMethod.GET)
    @ResponseBody
    public List<Dbsxdl> findIdBetweenMaxMin(@RequestParam(value = "startid",required = true) long startid,@RequestParam(value = "stopid") long stopid){
        return dbsxdlReposity.findByDevid(startid,stopid);
    }
}