SpringBoot---整合mysql数据库

喜欢ヅ旅行 2022-04-22 10:22 203阅读 0赞

> **          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**
> 
> **项目大致结构如下：**
> 
> **![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x2aGFvZ3Vhbmcw_size_16_color_FFFFFF_t_70][]**
> 
> **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一条记录成功。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x2aGFvZ3Vhbmcw_size_16_color_FFFFFF_t_70]: /images/20220422/a3c31174174f4efda2a1b388f7d986f4.png