SpringBoot - 08整合JDBC

2022-02-02 02:11:23

SpringBoot - 08整合JDBC

（1）搭建项目环境

（1.1）修改POM文件

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.43</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

 

（2）配置数据源

（2.1）通过自定义配置文件方式配置数据源信息

  • 通过@PropertySource注解读取配置文件，使用Druid连接池 
@Configuration     // 数据源的JDBC配置类
@PropertySource("classpath:/jdbc.properties") // 加载指定的Properties配置文件
public class JdbcConfiguration {

    @Value("${jdbc.driverClassName}")
    private String driverClassName;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;
    // 实例化Druid
    @Bean  
    public DataSource getDataSource(){
        DruidDataSource source = new DruidDataSource();
        source.setDriverClassName(this.driverClassName);
        source.setUrl(this.url);
        source.setUsername(this.username);
        source.setPassword(this.password);
        return source;
    }
}

测试

    @Autowired
    private DataSource dataSource;

    @RequestMapping("/showInfo")
    public String showInfo(){
        System.out.println(this.dataSource.getClass().getPackage());
        return "ok";
    }
package com.alibaba.druid.pool

（2.2）通过@ConfigurationProperties注解读取配置信息

// 是SpringBoot的注解不能读取其他配置，只能读取SpringBoot配置application.properties
@ConfigurationProperties(prefix = "jdbc")
public class JdbcProperties {

    private String driverClassName;
    private String url;
    private String username;
    private String password;
    // 省略其他代码
}
@Configuration
//@PropertySource("classpath:/jdbc.properties")
@EnableConfigurationProperties(JdbcProperties.class) // 指定加载哪个配置信息属性类
public class JdbcConfiguration {

    @Autowired
    private JdbcProperties jdbcProperties;
    
    @Bean
    public DataSource getDataSource(){
        DruidDataSource source = new DruidDataSource();
        source.setDriverClassName(this.jdbcProperties.getDriverClassName());
        source.setUrl(this.jdbcProperties.getUrl());
        source.setUsername(this.jdbcProperties.getUrl());
        source.setPassword(this.jdbcProperties.getPassword());
        return source;
    }
}
//    @Autowired
    private JdbcProperties jdbcProperties;
    
    public JdbcConfiguration(JdbcProperties jdbcProperties){
        this.jdbcProperties = jdbcProperties;
    }

（2.3）使用@ConfigurationProperties注解的优雅使用方式

@Configuration  
public class JdbcConfiguration {

    @Bean
    @ConfigurationProperties(prefix = "jdbc")  //不需要其他类
    public DataSource getDataSource(){
        DruidDataSource source = new DruidDataSource();
        return source;
    }
}

（2.4）使用SpringBoot配置文件配置数据源

在SpringBoot 1.x版本中spring-boot-starter-jdbc启动器中默认使用的数据源是org.apache.tomcat.jdbc.pool.DataSource
在SpringBoot 2.x版本中spring-boot-starter-jdbc启动器中默认使用的数据源是com.zaxxer.hikariDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/dev?useUnicode=true
spring.datasource.username=root
spring.datasource.password=root
#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
package com.zaxxer.hikari

 

（3） 用户功能测试

（3.1）编写UserDao实现类

@Component
public class UsersDaoImpl implements UsersDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional
    public void insertUsers(Users users) {
        String sql = "insert into users(username, usersex) values(?,?)";
        this.jdbcTemplate.update(sql,users.getUsername(),users.getUsersex());
    }

    @Override
    public List<Users> selectUsersAll() {
        String sql = "select * from users";
        return this.jdbcTemplate.query(sql, new RowMapper<Users>() {
            @Override
            public Users mapRow(ResultSet resultSet, int i) throws SQLException{
                Users users = new Users();
                users.setUserid(resultSet.getInt("userid"));
                users.setUsername(resultSet.getString("username"));
                users.setUsersex(resultSet.getString("usersex"));
                return users;
            }
        });
    }

    @Override
    public Users selectUserById(Integer id) {
        String sql = "select * from users where userid = ?";
        Object[] agr = new Object[]{id};
        Users users = new Users();
        this.jdbcTemplate.query(sql, agr, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                users.setUserid(resultSet.getInt("userid"));
                users.setUsername(resultSet.getString("username"));
                users.setUsersex(resultSet.getString("usersex"));
            }
        });
        return users;
    }

    @Override
    public void updateUsers(Users users) {
        String sql = "update users set username=?,usersex=? where userid = ?";
        this.jdbcTemplate.update(sql, users.getUsername(), users.getUsersex(), users.getUserid());
    }

    @Override
    public void deleteUserById(Integer id) {
        String sql = "delete from users where userid = ?";
        this.jdbcTemplate.update(sql,id);
    }
}

（3.2）编写UserService实现类

public class UserServiceImpl implements UserService {

    @Autowired
    private UsersDao usersDao;

    @Override
    public void addUser(Users users) {  usersDao.insertUsers(users); }

    @Override
    public List<Users> selectUsersAll() {  return usersDao.selectUsersAll(); }

    @Override
    public Users findUserById(Integer id) { return usersDao.selectUserById(id); }

    @Override
    @Transactional
    public void modifyUser(Users users) {  this.usersDao.updateUsers(users); }

    @Override
    @Transactional
    public void dropUser(Integer id){  this.usersDao.deleteUserById(id); }
}

（3.3）页面跳转

@Controller
public class PageController {

    @RequestMapping("/{page}")
    public String showPage(@PathVariable String page){
        System.out.println(page);
        return page;
    }
}

（3.4）新增用户

templates/addUsers.html

<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>新增用户</title>
</head>
<body>
    <form th:action="@{/user/addUser}" method="post">
        <input type="text" name="username"><br/>
        <input type="text" name="usersex"><br/>
        <input type="submit" value="OK">
    </form>
</body>
</html>
    @Autowired
    private UserService userService;

    @PostMapping("/addUser")
    public String addUsers(Users users){
        try{
            this.userService.addUser(users);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "ok";  // return "redirect:/ok"
    }

SpringBoot - 08整合JDBC                  SpringBoot - 08整合JDBC   redirect:/ok SpringBoot - 08整合JDBC

（3.5） 添加favicon.ico

SpringBoot - 08整合JDBC

在/static目录下 添加 favicon.ico文件

<link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}">

（3.6）查询用户

templates/showUsers.html

<body>
    <table border="1px">
        <tr>
            <th>用户ID</th>
            <th>用户姓名</th>
            <th>用户性别</th>
            <th>操作</th>
        </tr>
        <tr th:each="u: ${list}">
            <td th:text="${u.userid}"></td>
            <td th:text="${u.username}"></td>
            <td th:text="${u.usersex}"></td>
            <td>
                <a th:href="@{/user/preUpdateUser(id=${u.userid})}">修改</a>
                <a th:href="@{/user/deleteUser(id=${u.userid})}">删除</a>
            </td>
        </tr>
    </table>
    <a href="/addUsers">新增用户</a>

</body>
    @RequestMapping("/showUsers")
    public String findUserAll(Model model){
        List<Users> list = null;
        try{
            list = this.userService.selectUsersAll();
            model.addAttribute("list", list);
            System.out.println(list);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "showUsers";
    }

SpringBoot - 08整合JDBC

（3.7）更新用户

<body>
    <form th:action="@{/user/updateUser}" method="post">
        <input type="hidden" name="userid" th:value="${user.userid}">
        <input type="text" name="username" th:value="${user.username}"><br/>
        <input type="text" name="usersex" th:value="${user.usersex}"><br/>
        <input type="submit" value="OK">
    </form>
</body>
  @RequestMapping("/preUpdateUser")
    public String preUpdateUser(Integer id, Model model){
        try{
            Users users = this.userService.findUserById(id);
            model.addAttribute("user",users);
            System.out.println(users);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "updateUsers";
    }

    @RequestMapping("/updateUser")
    public String updateUser(Users users){
        try{
            this.userService.modifyUser(users);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/ok";
    }

SpringBoot - 08整合JDBC     SpringBoot - 08整合JDBC

（3.8）删除用户

    @RequestMapping("/deleteUser")
    public String updateUser(Integer id){
        try{
            this.userService.dropUser(id);
        }catch (Exception e){
            e.printStackTrace();
            return "error";
        }
        return "redirect:/ok";
    }

 

SpringBoot - 08整合JDBC

上一篇：数据库的查询


下一篇：postgresql数据库批量建数据表