SpringBoot - 08整合JDBC

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数据库批量建数据表