企业级 SpringBoot教程（三）SpringBoot用JdbcTemplates访问Mysql

落日映苍穹つ 2022-03-12 03:56 208阅读 0赞

准备工作

jdk 1.8

maven 3.0

idea

mysql

初始化mysql:

完整项目的源码来源 技术支持一七九一七四三三八零

-- create table `account`
    DROP TABLE `account` IF EXISTS
    CREATE TABLE `account` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      `money` double DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    INSERT INTO `account` VALUES ('1', 'aaa', '1000');
    INSERT INTO `account` VALUES ('2', 'bbb', '1000');
    INSERT INTO `account` VALUES ('3', 'ccc', '1000');

创建工程

引入依赖：

在pom文件引入spring-boot-starter-jdbc的依赖：

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>

引入mysql连接类和连接池：

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
     
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.0.29</version>
            </dependency>

开启web:

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>

配置相关文件

在application.properties文件配置mysql的驱动类，数据库地址，数据库账号、密码信息。

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://localhost:3306/test
    spring.datasource.username=root
    spring.datasource.password=123456

通过引入这些依赖和配置一些基本信息，springboot就可以访问数据库类。

具体编码

实体类

public class Account {
        private int id ;
        private String name ;
        private double money;
     
    ....省略了getter. setter
     
    }

dao层

public interface IAccountDAO {
        int add(Account account);
     
        int update(Account account);
     
        int delete(int id);
     
        Account findAccountById(int id);
     
        List<Account> findAccountList();
    }

具体的实现类：

package com.forezp.dao.impl;
     
    import com.forezp.dao.IAccountDAO;
    import com.forezp.entity.Account;
    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;
     
    /**
     * Created by fangzhipeng on 2017/4/20.
     */
    @Repository
    public class AccountDaoImpl implements IAccountDAO {
     
        @Autowired
        private JdbcTemplate jdbcTemplate;
        @Override
        public int add(Account account) {
            return jdbcTemplate.update("insert into account(name, money) values(?, ?)",
                  account.getName(),account.getMoney());
     
        }
     
        @Override
        public int update(Account account) {
            return jdbcTemplate.update("UPDATE  account SET NAME=? ,money=? WHERE id=?",
                    account.getName(),account.getMoney(),account.getId());
        }
     
        @Override
        public int delete(int id) {
            return jdbcTemplate.update("DELETE from TABLE account where id=?",id);
        }
     
        @Override
        public Account findAccountById(int id) {
            List<Account> list = jdbcTemplate.query("select * from account where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Account.class));
            if(list!=null && list.size()>0){
                Account account = list.get(0);
                return account;
            }else{
                return null;
            }
        }
     
        @Override
        public List<Account> findAccountList() {
            List<Account> list = jdbcTemplate.query("select * from account", new Object[]{}, new BeanPropertyRowMapper(Account.class));
            if(list!=null && list.size()>0){
                return list;
            }else{
                return null;
            }
        }
    }