SpringBoot JDBC简单操作数据库

一、引入依赖

<!-- 核心启动器, 包括auto-configuration、logging and YAML -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- using Spring Data JDBC, JdbcTemplate或NamedParameterJdbcTemplate都是由spring jdbc提供的 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<!-- 数据库操作需要的mysql 驱动包 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.48</version>
</dependency>

<!-- testing Spring Boot applications with libraries including JUnit, Hamcrest and Mockito -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

二、application.properties配置

spring.datasource.url=jdbc:mysql://192.168.178.5:12345/mydb?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource

三、service和dao

1. dao代码

(1) JdbcDao接口

public interface JdbcDao {
    List<Map<String,Object>> listUsers();
}

(2) JdbcDaoImpl.class

@Repository
public class JdbcDaoImpl implements JdbcDao{
    /**
     * JdbcTemplate和NamedParameterJdbcTemplate只要配置了数据源就自动注入了，
     * 实际开发中，可以将这两个注入放到公共的dao类中, 其他到继承公共的即可
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * @MethodName listUsers
     * @Description 查询所有用户
     */
    public List<Map<String,Object>> listUsers(){
        String sql = "select id, name, age from user";
        return jdbcTemplate.queryForList(sql);
    }
}

2. service代码

(1) JdbcService接口

public interface JdbcService {
    List<Map<String,Object>> listUsers();
}

(2) JdbcServiceImpl

@Service
public class JdbcServiceImpl implements JdbcService{

    @Autowired
    private JdbcDao jdbcDao;

    @Override
    public List<Map<String, Object>> listUsers() {
        return jdbcDao.listUsers();
    }
}

四、单元测试

@RunWith(SpringRunner.class)
@SpringBootTest
class SpringdatajdbcApplicationTests {

    @Autowired
    private JdbcService jdbcService;

    @Test
    public void testListUsers() {
        List<Map<String, Object>> userList = jdbcService.listUsers();
        if(userList != null && userList.size()>0){
            for(Map<String,Object> user : userList){
                System.out.println("id：" + user.get("id"));
                System.out.println("username：" + user.get("name"));
                System.out.println("age：" + user.get("age"));
            }
        }
    }
}