springboot使用JdbcTemplate（一）

发表于 | 分类于
字数统计: 1.5k 字 | 阅读时长 ≈ 8 分钟

项目地址：https://github.com/snjl/springboot.mysql.git

环境配置

修改 POM 文件，添加spring-boot-starter-jdbc依赖：

1
2
3
4
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

添加mysql依赖（spring-boot-starter-jdbc 默认使用tomcat-jdbc数据源，如果要使用其他数据源，需要添加额外的依赖，这里使用了阿里巴巴的数据池管理）：

1
2
3
4
5
6
7
8
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
</dependency>

数据源配置

在src/main/resources/application.properties中配置数据源信息:

1
2
3
4
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/sss?serverTimezone=UTC&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=123456

在Application.java中配置：

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
40
41
42
@SpringBootApplication
public class MyprojectApplication {

    public static void main(String[] args) {
        SpringApplication.run(MyprojectApplication.class, args);
    }

    @Autowired
    private Environment environment;

    /**
     * destroy-method="close"的作用是当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.
     * @return
     */
    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(environment.getProperty("spring.datasource.url"));
        dataSource.setUsername(environment.getProperty("spring.datasource.username"));
        //用户名
        dataSource.setPassword(environment.getProperty("spring.datasource.password"));
        //密码
        dataSource.setDriverClassName(environment.getProperty("spring.datasource.driver-class-name"));
        dataSource.setInitialSize(2);
        //初始化时建立物理连接的个数
        dataSource.setMaxActive(20);
        //最大连接池数量
        dataSource.setMinIdle(0);
        //最小连接池数量
        dataSource.setMaxWait(60000);
        //获取连接时最大等待时间，单位毫秒。
        dataSource.setValidationQuery("SELECT 1");
        //用来检测连接是否有效的sql
        dataSource.setTestOnBorrow(false);
        //申请连接时执行validationQuery检测连接是否有效
        dataSource.setTestWhileIdle(true);
        //建议配置为true，不影响性能，并且保证安全性。
        dataSource.setPoolPreparedStatements(false);
        //是否缓存preparedStatement，也就是PSCache
        return dataSource;
    }
}

数据

1
2
3
4
5
6
7
8
9
10
11
12
13
-- auto-generated definition
CREATE TABLE tb_student
(
  id       INT AUTO_INCREMENT
    PRIMARY KEY,
  NAME     VARCHAR(18) NULL,
  sex      CHAR(3)     NULL,
  age      INT         NULL,
  clazz_id INT         NULL,
  CONSTRAINT TB_STUDENT_id_uindex
  UNIQUE (id)
)
  ENGINE = InnoDB;

代码

实体类：

1
2
3
4
5
6
7
8
9
10
11
package com.example.myproject.domain;
@Component
public class TbStudent {

  private Integer id;
  private String name;
  private String sex;
  private Integer age;
  private Integer clazzId;

  //getter,setter

可以用idea自动生成，将long类型的全改为Integer，为了以后不报错。

DAO层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.example.myproject.dao;

import com.example.myproject.domain.TbStudent;

import java.util.List;

public interface TbStudentDao {

    int insert(TbStudent tbStudent);

    int update(TbStudent tbStudent);

    int delete(long id);

    TbStudent findTbStudent(long id);

    List<TbStudent> findAll();
}
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
40
41
42
43
44
45
46
47
48
49
50
51
52
package com.example.myproject.dao.impl;

import com.example.myproject.dao.TbStudentDao;
import com.example.myproject.domain.TbStudent;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class TbStudentDaoImpl implements TbStudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public int insert(TbStudent tbStudent) {
        return jdbcTemplate.update("insert into tb_student(NAME) VALUES (?)",tbStudent.getName());
    }

    @Override
    public int update(TbStudent tbStudent) {
        return jdbcTemplate.update("update tb_student set NAME=? where id=?",tbStudent.getName(),tbStudent.getId());
    }

    @Override
    public int delete(long id) {
        return jdbcTemplate.update("delete from tb_student where id=?", id);
    }

    @Override
    public TbStudent findTbStudent(long id) {
        List<TbStudent> list = jdbcTemplate.query("SELECT * FROM tb_student WHERE id=?",
                new Object[]{id}, new BeanPropertyRowMapper<>(TbStudent.class));
        if(list != null && list.size() > 0) {
            TbStudent tbStudent = list.get(0);
            return tbStudent;
        }else {
            return null;
        }
    }

    @Override
    public List<TbStudent> findAll() {
        List<TbStudent> list = jdbcTemplate.query("SELECT * FROM tb_student",
                new Object[]{}, new BeanPropertyRowMapper<>(TbStudent.class));

        return list;
    }
}

注：使用jdbcTemplate.query时，需要注意这个写法：new Object[]{id}, new BeanPropertyRowMapper<>(TbStudent.class)。并且如果只需要一个，需要进行null和size判定，之后取出一个，不然会报错。

Service层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.example.myproject.service;

import com.example.myproject.domain.TbStudent;

import java.util.List;

public interface TbStudentService {


    int insert(TbStudent tbStudent);

    int update(TbStudent tbStudent);

    int delete(long id);

    TbStudent findTbStudent(long id);

    List<TbStudent> findAll();

}
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
40
41
package com.example.myproject.service.impl;

import com.example.myproject.dao.TbStudentDao;
import com.example.myproject.domain.TbStudent;
import com.example.myproject.service.TbStudentService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class TbStudentServiceImpl implements TbStudentService{
    @Resource(name = "tbStudentDaoImpl")
    private TbStudentDao tbStudentDao;


    @Override
    public int insert(TbStudent tbStudent) {
        return tbStudentDao.insert(tbStudent);
    }

    @Override
    public int update(TbStudent tbStudent) {
        return tbStudentDao.update(tbStudent);
    }

    @Override
    public int delete(long id) {
        return tbStudentDao.delete(id);
    }

    @Override
    public TbStudent findTbStudent(long id) {
        return tbStudentDao.findTbStudent(id);
    }

    @Override
    public List<TbStudent> findAll() {
        return tbStudentDao.findAll();
    }
}

Controller

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
40
41
42
43
44
45
46
package com.example.myproject.web;

import com.example.myproject.domain.TbStudent;
import com.example.myproject.service.TbStudentService;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author 34924
 */
@RestController
@RequestMapping("/tbStudent")
public class TbStudentController {

    @Resource(name = "tbStudentServiceImpl")
    private TbStudentService tbStudentService;

    @RequestMapping("/findAll")
    public List<TbStudent> findAll() {
        return tbStudentService.findAll();
    }

    @RequestMapping("/delete/{id}")
    public int delete(@PathVariable long id) {
        return tbStudentService.delete(id);
    }

    @RequestMapping(value = "/insert",method = RequestMethod.POST)
    public int insert(@RequestBody TbStudent tbStudent) {
        return tbStudentService.insert(tbStudent);
    }

    @RequestMapping(value = "/update",method = RequestMethod.GET)
    public int update(@RequestParam Integer id,@RequestParam String name) {
        TbStudent tbStudent = tbStudentService.findTbStudent(id);
        tbStudent.setName(name);
        return tbStudentService.update(tbStudent);
    }

    @RequestMapping(value = "/findOne",method = RequestMethod.POST)
    public TbStudent findOne(@RequestBody TbStudent tbStudent) {
        return tbStudentService.findTbStudent(tbStudent.getId());
    }
}

注：此处全写的是接口

  • findAll用的是普通的get请求;
  • delete用的restful的形式；
  • insert用的post请求；
  • update用的get请求，使用@RequestParam，注意post请求使用@RequestBody，而get请求只能使用@RequestParam或者是使用@ModelAttribute（此处未使用）；
  • findOne用的post请求。

如果将update改为使用@ModelAttribute，如下：

1
2
3
4
@RequestMapping(value = "/update",method = RequestMethod.GET)
public int update(@ModelAttribute TbStudent tbStudent) {
    return tbStudentService.update(tbStudent);
}

使用JSONObject

改insert为JSONObject接受参数：

1
2
3
4
5
6
@RequestMapping(value = "/insert2",method = RequestMethod.POST)
public int insert2(@RequestBody JSONObject tbStudent) {
    TbStudent tbStudent1 = new TbStudent();
    tbStudent1.setName((String)tbStudent.get("name"));
    return tbStudentService.insert(tbStudent1);
}

引入的包为：

1
import net.minidev.json.JSONObject;

在pom.xml中加入：

1
2
3
4
5
<dependency>
    <groupId>net.minidev</groupId>
    <artifactId>json-smart</artifactId>
    <version>RELEASE</version>
</dependency>

测试效果

findAll：

image

delete：

image

image

insert：

image

image

update：

image

image

使用@ModelAttribute的注解：
image

image

findOne：

image

使用thymeleaf

添加文件：

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
package com.example.myproject.web;

import com.example.myproject.service.TbStudentService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;

/**
 * @author 34924
 */
@Controller
@RequestMapping("/tbStudent/html")
public class TbStudentControllerForHtml {


    @Resource(name = "tbStudentServiceImpl")
    private TbStudentService tbStudentService;

    @RequestMapping("/findAll")
    public String findAll(ModelMap model) {

        model.addAttribute("items", tbStudentService.findAll());
        return "student";
    }


}

student.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>student</title>
</head>
<body>
<h1>student</h1>
this is student.
<br>

<div th:each="item : ${items}">

    <a th:text="${item.id}">123</a>&nbsp;&nbsp;
    <a th:text="${item.name}">name</a>&nbsp;&nbsp;
    <a th:text="${item.clazzId}">name</a>&nbsp;&nbsp;
    <a th:if="${item.clazzId }">123132</a>
    <br>
</div>

</body>
</html>

即可显示。

-------------本文结束 感谢您的阅读-------------