Mac Spring boot(1.5.9) 使用JDBCTemplate操作数据库

1、先在MySQLWorkbench的new_schema数据库中新建表user，有id、name、age

2、新建Spring Starter Project，选择jdbc和mysql依赖

3、看下项目目录

4、新建UserService接口，添加新增、删除等相关数据库操作

package com.yuna.service;

public interface UserService {

//  新增用户
    void create(String name, Integer age);

//  根据name删除用户
    void deleteByName(String name);

//  查询用户总数
    Integer getAllUsers();

//  删除所有用户
    void deleteAllUsers();
}

5、新建UserServiceImpl类，通过JdbcTemplate实现UserService中定义的数据库相关操作

package com.yuna.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

//注解一定要加，否则会报错
//@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private JdbcTemplate  jdbcTemplate;

    @Override
    public void create(String name, Integer age) {

        jdbcTemplate.update("insert into user(name, age) values(?,?)",name, age);
    }

    @Override
    public void deleteByName(String name) {

        jdbcTemplate.update("delete from user where name = ?", name);
    }

    @Override
    public Integer getAllUsers() {

        return jdbcTemplate.queryForObject("select count(1) from user", Integer.class);
    }

    @Override
    public void deleteAllUsers() {

        jdbcTemplate.update("delete from user");
    }

}

6、设置配置文件application.properties，跟之前项目设置的一样

spring.datasource.url =jdbc:mysql://localhost:3306/new_schema?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username = root
spring.datasource.password = 123456
spring.datasource.driverClassName = com.mysql.jdbc.Driver
# Specify the DBMS
spring.jpa.database = MYSQL
# Show or not log for each sql query
spring.jpa.show-sql = true
# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto = update
# Naming strategy
spring.jpa.hibernate.naming.strategy = org.hibernate.cfg.ImprovedNamingStrategy
# stripped before adding them to the entity manager)
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

7、写个单元测试

package com.yuna.demo;

import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import com.yuna.service.UserService;


@RunWith(SpringRunner.class)
@SpringBootTest
public class ApplicationTests {

    @Autowired
    private UserService userService;


    @Before
    public void setUp() {
        userService.deleteAllUsers();
    }


    @Test
    public void test() throws Exception {
         //插入4条记录
        userService.create("yuna", 18);
        userService.create("sam", 25);
        userService.create("jackson", 3);
        userService.create("vernal", 13);

        //当前user表中有几条记录
        System.out.println("count(*)=" + userService.getAllUsers().intValue());

        Assert.assertEquals(4, userService.getAllUsers().intValue());

        // 删除1条记录
        userService.deleteByName("vernal");
        //当前user表中有几条记录
        System.out.println("getAllUsers  count(*)=" + userService.getAllUsers().intValue());

        Assert.assertEquals(3, userService.getAllUsers().intValue());
    }

}

8、运行单元测试的时候，出现如下问题

…………………..

9、可以看到caused by后面的，是说UserService没有注入。我们需要在Application.java中添加如下

package com.yuna.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication(scanBasePackages= {"com.yuna"})
public class Application {

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

10、单元测试运行，我们先测试新增4条记录

11、删除一条记录看下

参考链接