springboot系列九 Spring-Data-JDBC

最新推荐文章于 2024-04-23 13:01:43 发布

weixin_33834679

最新推荐文章于 2024-04-23 13:01:43 发布

阅读量585

点赞数

文章标签： java

原文链接：https://my.oschina.net/yimingkeji/blog/2966967

版权

2019独角兽企业重金招聘Python工程师标准>>>

DEMO

依赖

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
    </dependencies>

配置

server:
  port: 8080
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver # mysql8推荐使用cj.jdbc， com.mysql.jdbc.Driver不推荐使用了
    #数据库连接池默认使用 tomcat-jdbc
    tomcat:
      max-idle: 10
      min-idle: 1
      initial-size: 1

实体

@Data
public class User {
    private int id;
    private String name;
    private int age;
    private String phoneNumber;
    private boolean available;
    private Date createTime = new Date();
}

使用jdbc

@RestController
@RequestMapping("/user")
public class UserResource {

    @Autowired private JdbcTemplate jdbcTemplate;

    @GetMapping("")
    public List list(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user");
        return list;
    }

    @PostMapping("")
    public User add(@RequestBody User user){
        String sql = "insert into user(name,age) values(?, ?)";
        jdbcTemplate.update(connection -> {
            PreparedStatement ps= connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,user.getName());
            ps.setInt(2,user.getAge());
            return ps;
        });
        return user;
    }
}

测试

当前数据库数据：

查询

GET http://localhost:8080/user

[
    {
        "id": 1,
        "name": "张三",
        "phone_number": "15500000001",
        "create_time": "2018-12-02T12:20:32.000+0000",
        "age": 20,
        "available": false
    },
    {
        "id": 2,
        "name": "李四",
        "phone_number": "15500000002",
        "create_time": "2018-12-01T13:04:56.000+0000",
        "age": 0,
        "available": false
    },
    {
        "id": 3,
        "name": "王五",
        "phone_number": "15500000003",
        "create_time": "2018-12-01T13:05:02.000+0000",
        "age": 0,
        "available": false
    }
]

添加

POST http://localhost:8080/user

请求数据：

{
	"name":"add1",
	"age":100
}

再次查询GET http://localhost:8080/user返回：

[
    {
        "id": 1,
        "name": "张三",
        "phone_number": "15500000001",
        "create_time": "2018-12-02T12:20:32.000+0000",
        "age": 20,
        "available": false
    },
    {
        "id": 2,
        "name": "李四",
        "phone_number": "15500000002",
        "create_time": "2018-12-01T13:04:56.000+0000",
        "age": 0,
        "available": false
    },
    {
        "id": 3,
        "name": "王五",
        "phone_number": "15500000003",
        "create_time": "2018-12-01T13:05:02.000+0000",
        "age": 0,
        "available": false
    },
    {
        "id": 6,
        "name": "add1", #新加的数据
        "phone_number": "1001",
        "create_time": "2018-12-03T08:27:46.000+0000",
        "age": 100,
        "available": false
    }
]