SpringBoot---整合mysql数据库

          SpringBoot 连接mysql数据库，与Spring MVC 相比较而言，省去了配置文件，直接将配置通过字符串的方式写到application.properties 文件中，配置如下：

spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jackson.time-zone=Asia/Chongqing

项目大致结构如下：

dao 接口层：

package com.nyist.sql.dao;

import com.nyist.sql.model.RoncooUser;

public interface RoncooUserDao {
    int insert(RoncooUser roncooUser);
    int deleteById(int id);
    int updateById(RoncooUser roncooUser);
    RoncooUser selectById(int id);
}

dao Impl 实现层：

package com.nyist.sql.dao.Impl;

import com.nyist.sql.dao.RoncooUserDao;
import com.nyist.sql.model.RoncooUser;
import com.nyist.sql.util.base.JdbcDaoImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;

@Repository
public class RoncooUserDaoImpl extends JdbcDaoImpl implements RoncooUserDao {

    //JdbcTemplate 是 SpringBoot 特有的一个包  支持对数据库的原生开发
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(RoncooUser roncooUser) {
        String sql = "insert into roncoo_user(name, create_time) values (?, ?)";
        return jdbcTemplate.update(sql, roncooUser.getName(),roncooUser.getCreateTime());
    }

    @Override
    public int deleteById(int id) {
        String sql = "delete from roncoo_user where id=?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public int updateById(RoncooUser roncooUser) {
        String sql = "update roncoo_user set name=?, create_time=? where id=?";
        return jdbcTemplate.update(sql, roncooUser.getName(),
                roncooUser.getCreateTime(), roncooUser.getId());
    }

    @Override
    public RoncooUser selectById(int id) {
        String sql = "select * from roncoo_user where id=?";
        /*RoncooUser roncooUser = jdbcTemplate.queryForObject(sql, new RowMapper<RoncooUser>() {
            @Override
            public RoncooUser mapRow(ResultSet rs, int rowNum) throws SQLException {
                RoncooUser roncooUser = new RoncooUser();
                roncooUser.setId(rs.getInt("id"));
                roncooUser.setName(rs.getString("name"));
                roncooUser.setCreateTime(rs.getDate("create_time"));
                return roncooUser;
            }
        }, id);*/
        return queryForObject(sql,RoncooUser.class,id);
    }
}

JdbcDaoImpl 封装类

/**
 * 2015-2016 龙果学院 (www.roncoo.com)
 */
package com.nyist.sql.util.base;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.Assert;

/**
 * spirng jdbc 基本支撑类
 * 
 * @author wujing
 */
public abstract class JdbcDaoImpl {

	@Autowired
	protected JdbcTemplate jdbcTemplate;

	/**
	 * 获取当前事务最后一次更新的主键值
	 */
	public Long getLastId() {
		return jdbcTemplate.queryForObject("select last_insert_id() as id", Long.class);
	}

	/**
	 * 获取对象信息
	 */
	public <T> T queryForObject(String sql, Class<T> clazz, Object... args) {
		Assert.hasText(sql, "sql 语句不能为空");
		return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(clazz), args);
	}

	/**
	 * 获取对象集合信息
	 */
	public <T> List<T> queryForObjectList(String sql, Class<T> clazz, Object... args) {
		Assert.hasText(sql, "sql 语句不能为空");
		return jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<T>(clazz));
	}

	/**
	 * 分页，jdbcTemplate 不支持like
	 */
	public Page<Map<String, Object>> queryForPage(String sql, int pageCurrent, int pageSize, Object... args) {
		Assert.hasText(sql, "sql 语句不能为空");
		Assert.isTrue(pageCurrent >= 1, "pageNo 必须大于等于1");
		String sqlCount = Sql.countSql(sql);
		int count = jdbcTemplate.queryForObject(sqlCount, Integer.class, args);
		pageCurrent = Sql.checkPageCurrent(count, pageSize, pageCurrent);
		pageSize = Sql.checkPageSize(pageSize);
		int totalPage = Sql.countTotalPage(count, pageSize);
		String sqlList = sql + Sql.limitSql(count, pageCurrent, pageSize);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sqlList, args);
		return new Page<Map<String, Object>>(count, totalPage, pageCurrent, pageSize, list);
	}

	/**
	 * 分页，jdbcTemplate 不支持like
	 */
	public <T> Page<T> queryForPage(String sql, int pageCurrent, int pageSize, Class<T> clazz, Object... args) {
		Assert.hasText(sql, "sql 语句不能为空");
		Assert.isTrue(pageCurrent >= 1, "pageNo 必须大于等于1");
		Assert.isTrue(clazz != null, "clazz 不能为空");
		String sqlCount = Sql.countSql(sql);
		int count = jdbcTemplate.queryForObject(sqlCount, Integer.class, args);
		pageCurrent = Sql.checkPageCurrent(count, pageSize, pageCurrent);
		pageSize = Sql.checkPageSize(pageSize);
		int totalPage = Sql.countTotalPage(count, pageSize);
		String sqlList = sql + Sql.limitSql(count, pageCurrent, pageSize);
		List<T> list = jdbcTemplate.query(sqlList, new BeanPropertyRowMapper<T>(clazz), args);
		return new Page<T>(count, totalPage, pageCurrent, pageSize, list);
	}

}

application.properties 配置：

#开发环境
server.port=8888

spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jackson.time-zone=Asia/Chongqing

SpringBootTest 测试类：

package com.nyist.sql;

import com.nyist.sql.dao.RoncooUserDao;
import com.nyist.sql.model.RoncooUser;
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 java.util.Date;

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


    @Autowired
    private RoncooUserDao roncooUserDao;


    @Test
    public void insert() {

        RoncooUser roncooUser = new RoncooUser();
        roncooUser.setName("测试");

        roncooUser.setCreateTime(new Date());
        int result = roncooUserDao.insert(roncooUser);
        System.out.println(result);
    }

    @Test
    public void delete() {
        int result = roncooUserDao.deleteById(1);
        System.out.println(result);
    }

    @Test
    public void update() {
        RoncooUser roncooUser = new RoncooUser();
        roncooUser.setId(2);
        roncooUser.setName("测试 2");
        roncooUser.setCreateTime(new Date());
        int result = roncooUserDao.updateById(roncooUser);
        System.out.println(result);
    }

    @Test
    public void select() {
        RoncooUser result = roncooUserDao.selectById(2);
        System.out.println(result);
    }
}

运行结果：

    通过Navicate 数据库可视化工具可以看到  插入yi't一条记录成功。