Springboot（九）jdbcTemplate

雨点打透心脏的1/2处 2022-05-22 11:58 170阅读 0赞

##### 前言： #####

Springboot配置jdbcTemplate连接mysql数据库

##### 代码： #####

pom.xml

加入spring-boot-starter-jdbc与mysql的包

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.xhx.springboot</groupId>
        <artifactId>springboot4</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <packaging>jar</packaging>
    
        <name>springboot4</name>
        <description>Demo project for Spring Boot</description>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.1.RELEASE</version>
            <relativePath/> 
        </parent>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    
    </project>

application.properties

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://192.168.94.151:3306/mytest?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8
    spring.datasource.username=root
    spring.datasource.password=xuhaixing

Dao层注入jdbcTemplate

package com.xhx.springboot.dao.impl;
    
    import com.xhx.springboot.dao.AccountDao;
    import com.xhx.springboot.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;
    
    /**
     * @author xuhaixing
     * @date 2018/4/28 10:33
     */
    @Repository
    public class AccountDaoImpl implements AccountDao {
    
        @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 findById(int id) {
            List<Account> accountList = jdbcTemplate.query("select * from account where id=?", new Object[]{id}, new BeanPropertyRowMapper<>(Account.class));
            if (accountList != null && accountList.size() > 0) {
                return accountList.get(0);
            } else {
                return null;
            }
        }
    
        @Override
        public List<Account> findList() {
            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;
            }
        }
    }

service层注入Dao

package com.xhx.springboot.service.impl;
    
    import com.xhx.springboot.dao.AccountDao;
    import com.xhx.springboot.entity.Account;
    import com.xhx.springboot.service.AccountService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * @author xuhaixing
     * @date 2018/5/2 9:53
     */
    @Service
    public class AccountServiceImpl implements AccountService {
        @Autowired
        private AccountDao accountDao;
    
        @Override
        public int add(Account account) {
            return accountDao.add(account);
        }
    
        @Override
        public int update(Account account) {
            return accountDao.update(account);
        }
    
        @Override
        public int delete(int id) {
            return accountDao.delete(id);
        }
    
        @Override
        public Account findById(int id) {
            return accountDao.findById(id);
        }
    
        @Override
        public List<Account> findList() {
            return accountDao.findList();
        }
    }

Controller层

package com.xhx.springboot.controller;
    
    import com.xhx.springboot.entity.Account;
    import com.xhx.springboot.service.AccountService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    
    import java.util.List;
    
    /**
     * @author xuhaixing
     * @date 2018/5/2 9:55
     */
    @RestController
    @RequestMapping("account")
    public class AccountController {
    
        @Autowired
        private AccountService accountService;
    
        @RequestMapping(value = "add", method = RequestMethod.POST)
        public int add(@RequestBody Account account) {
            return accountService.add(account);
        }
    
        @RequestMapping(value = "update", method = RequestMethod.POST)
        public int update0(@RequestBody Account account) {
            return accountService.update(account);
        }
    
        @RequestMapping(value = "delete", method = RequestMethod.POST)
        public int delete(@RequestParam int id) {
            return accountService.delete(id);
        }
    
        @RequestMapping(value = "findById", method = RequestMethod.POST)
        public Account findById(@RequestParam int id) {
            return accountService.findById(id);
        }
    
        @RequestMapping(value = "findList", method = RequestMethod.POST)
        public List<Account> findList() {
            return accountService.findList();
        }
    
    }

启动类：

package com.xhx.springboot;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class Springboot4Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Springboot4Application.class, args);
        }
    }

数据库：

CREATE TABLE `account` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      `money` double DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

运行结果：

![70][]

![70 1][]

[我的git代码地址][git]

[70]: /images/20220522/5667b2fb618640b594f922bd8bf16aaa.png
[70 1]: /images/20220522/a01f33d9bd7f47308fe1aa22940a8dee.png
[git]: https://github.com/xuhaixing/myDocument.git