SpringBoot的持久化层可以是Spring内置的轻量级JdbcTemplate、也可以是Hibernate或Mybatis等等，只需要在在工程pom.xml文件中添加对应的依赖就可以了。

新建工程我们能发现，SpringBoot对数据库操作的支持有以下几种：

可见SpringBoot对各种的支持还是挺多的。

入正题。看看对SQL的支持。主要选了比较传统/流行/有前景的4个进行操作：

均是采用mysql。

所以应该添加对mysql操作的依赖：

<!--MySQL--><dependency> <groupid>mysql</groupid> mysql-connector-java</artifactid> <scope>runtime</scope></dependency>

同时，需要对web进行支持，添加web相关依赖

<!--web支持--><dependency> <groupid>org.springframework.boot</groupid> spring-boot-starter-web</artifactid></dependency>

需要在application.properties中配置mysql相关信息（也可以使用*.yml）。配置如下：

spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url= jdbc:mysql://localhost:3306/springboottest?useUnicode=true&characterEncoding=utf-8spring.datasource.username=rootspring.datasource.password=root

实际应用的时候，需要添加数据库连接池，为了方便，就暂时不添加了。

1. SpringBoot用JdbcTemplates访问Mysql

首先需要添加对JdbcTemplates的依赖，可以在新建工程中点击添加，或手动添加

<!--JDBC支持--><dependency> <groupid>org.springframework.boot</groupid> spring-boot-starter-jdbc</artifactid></dependency>

目录结构如上，代码如下：

@Datapublic class Account { private int id ; private String name ; private double money;}

这里是安装了一个插件，然后只需要添加如下依赖，添加@Data注解，就会帮我们自动生成getter和setter，也可以用@getter或@Setter注解进行标注。

<dependency> <groupid>org.projectlombok</groupid> lombok</artifactid></dependency>

Dao层：

public interface IAccountDao { int add(Account account); int update(Account account); int delete(int id); Account findAccountById(int id); Account selectAccountById(int id); List findAccountList();}</account>

实现类：

@Repositorypublic class AccountDaoImpl implements IAccountDao{ @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 findAccountById(int id) { List list = jdbcTemplate.query("SELECT * FROM account WHERE id = ?", new Object[]{id}, new BeanPropertyRowMapper(Account.class)); if(list!=null && list.size()>0){ Account account = list.get(0); return account; }else{ return null; } } @Override public Account selectAccountById(int id){ return jdbcTemplate.queryForObject("SELECT * FROM account WHERE id = ?", new RowMapper() { @Override public Account mapRow(ResultSet resultSet, int i) throws SQLException { Account account = new Account(); account.setId(resultSet.getInt("id")); account.setName(resultSet.getString("name")); account.setMoney(resultSet.getDouble("money")); return account; } },id); } @Override public List findAccountList() { List list = jdbcTemplate.query("SELECT * FROM account", new Object[]{}, new BeanPropertyRowMapper(Account.class)); if(list!=null && list.size()>0) return list; else return null; }}</account></account></account></account>

后续也可以添加更多的操作，包括分页等等。这里主要就是jdbcTemplate的操作了，详情也可以查相关资料

Service层：

public interface IAccountService { int add(Account account); int update(Account account); int delete(int id); Account findAccountById(int id); Account selectAccountById(int id); List findAccountList();}</account>

实现

@Servicepublic class JdbcAccountService implements IAccountService { @Autowired private IAccountDao 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 findAccountById(int id) { return accountDao.findAccountById(id); } @Override public Account selectAccountById(int id) { return accountDao.selectAccountById(id); } @Override public List findAccountList() { return accountDao.findAccountList(); }}</account>

Controller：

@RestController@RequestMapping("/jdbc/account")public class JdbcAccountController { @Autowired private IAccountService accountService; @RequestMapping(value = "/list",method = RequestMethod.<em>GET</em>) public List getAccounts(){ return accountService.findAccountList(); } @RequestMapping(value = "/{id}",method = RequestMethod.<em>GET</em>) public Account getAccountById(@PathVariable("id") int id){// return accountService.findAccountById(id); return accountService.selectAccountById(id); } @RequestMapping(value = "/{id}",method = RequestMethod.<em>PUT</em>) public String updateAccount(@PathVariable("id")int id , @RequestParam(value = "name",required = true)String name, @RequestParam(value = "money",required = true)double money){ Account account=new Account(); account.setMoney(money); account.setName(name); account.setId(id); int t=accountService.update(account); if(t==1){ return account.toString(); }else { return "fail"; } } @RequestMapping(value = "") public String postAccount( @RequestParam(value = "name")String name, @RequestParam(value = "money")double money){ Account account=new Account(); account.setMoney(money); account.setName(name); int t= accountService.add(account); if(t==1){ return account.toString(); }else { return "fail"; } }}</account>

难度不大，只是简单的对数据库进行访问和修改，注意注解不要忘记标注了。

2.SpringBoot 整合JPA

Jpa算是比较方便的，用起来只需注意相关规则就可以了，不管jpa还是mybatis，都是有各自优点的，在那里使用方便，就采用哪个，没好坏之分。

首先，也是需要添加相关的依赖

<!--JPA支持--><dependency> <groupid>org.springframework.boot</groupid> spring-boot-starter-data-jpa</artifactid></dependency>

创建实体（注意注解）

Dao层：这里只做了简单的演示，所以没有添加特殊的操作，直接继承jpa给我们提供的接口，就可以了，后续需要添加一些东西的话，可以添加对应的方法

public interface AccountDao extends JpaRepository {}</account,integer>

Controller层：

@RestController@RequestMapping("/jpa/account")public class JpaAccountController { @Autowired private AccountDao accountDao; @RequestMapping(value = "/list",method = RequestMethod.<em>GET</em>) public List getAccounts(){ return accountDao.findAll(); } @RequestMapping(value = "/{id}",method = RequestMethod.<em>GET</em>) public Account getAccountById(@PathVariable("id") int id){ return accountDao.findOne(id); } @RequestMapping(value = "/{id}",method = RequestMethod.<em>PUT</em>) public String updateAccount(@PathVariable("id")int id , @RequestParam(value = "name",required = true)String name, @RequestParam(value = "money",required = true)double money){ Account account=new Account(); account.setMoney(money); account.setName(name); account.setId(id); Account account1 = accountDao.saveAndFlush(account); return account1.toString(); } @RequestMapping(value = "") public String postAccount( @RequestParam(value = "name")String name, @RequestParam(value = "money")double money){ Account account=new Account(); account.setMoney(money); account.setName(name); Account account1 = accountDao.save(account); return account1.toString(); }}</account>

总体来说，jpa代码很简洁，也很强大，能够实现一些常规的操作，对于开发者来说还是挺高效的，对于一些特殊的需求，也可以看官方给我们的文档，3.springboot整合mybatisMybatis在现在也是一个非常流行的持久化层框架了，和Hibernate比起来，可以自己编写sql语句，从而利用高效的sql语句制作好的产品。但缺点也是在编写sql语句，这方面有点花时间，相比jpa来说。入正题：

添加依赖：

<!--MyBatis支持--><dependency> <groupid>org.mybatis.spring.boot</groupid> mybatis-spring-boot-starter</artifactid> <version>1.2.0</version></dependency>

Mybatis可以采用两种方式进行编写，一种是基于xml的配置方式，一种是基于注解的方式，选择何种方式，当然是萝卜白菜啦，选最合适自己的。

实体还是上一个例子的实体，这里就不添加了，dao层设计我们先来说一下用注解的方式：注解的方式相比下还是挺方便的，

@Mapperpublic interface AccountMapper { @Insert("INSERT INFO account(name,money) VALUES(#{name},#{money})") int add(@Param("name")String name, @Param("money")double money); @Update("UPDATE account SET name = #{name}, money = #{money} WHERE id = #{id}") int update(@Param("name") String name, @Param("money") double money, @Param("id") int id); @Delete("DELETE FROM account WHERE id = #{id}") int delete(int id); @Select("SELECT id, name AS name, money AS money FROM account WHERE id = #{id}") Account findAccount(@Param("id") int id); @Select("SELECT id, name AS name, money AS money FROM account") List findAccountList();}</account>

注解单词简单明了，就不解释了，但要注意的是，方法名称也要适当的取好，不然以后对数据库操作多了，有可能会乱，需要不断回来看代码。

Service层：

@Servicepublic class MybatisAccountService { @Autowired private AccountMapper accountMapper; public int add(String name, double money) { return accountMapper.add(name, money); } public int update(String name, double money, int id) { return accountMapper.update(name, money, id); } public int delete(int id) { return accountMapper.delete(id); } public Account findAccount(int id) { return accountMapper.findAccount(id); } public List findAccountList() { return accountMapper.findAccountList(); }}</account>

对应的Controller与上面的没有多大区别

@RestController@RequestMapping("/mybatis/account")public class MybatisAccountController { @Autowired private MybatisAccountService mybatisAccountService; @RequestMapping(value = "/list", method = RequestMethod.<em>GET</em>) public List getAccounts() { return mybatisAccountService.findAccountList(); } @RequestMapping(value = "/{id}", method = RequestMethod.<em>GET</em>) public Account getAccountById(@PathVariable("id") int id) { return mybatisAccountService.findAccount(id); } @RequestMapping(value = "/{id}", method = RequestMethod.<em>PUT</em>) public String updateAccount(@PathVariable("id") int id, @RequestParam(value = "name", required = true) String name, @RequestParam(value = "money", required = true) double money) { int t= mybatisAccountService.update(name,money,id); if(t==1) { return "success"; }else { return "fail"; } } @RequestMapping(value = "/{id}", method = RequestMethod.<em>DELETE</em>) public String delete(@PathVariable(value = "id")int id) { int t= mybatisAccountService.delete(id); if(t==1) { return "success"; }else { return "fail"; } } @RequestMapping(value = "", method = RequestMethod.<em>POST</em>) public String postAccount(@RequestParam(value = "name") String name, @RequestParam(value = "money") double money) { int t= mybatisAccountService.add(name,money); if(t==1) { return "success"; }else { return "fail"; } } }</account>

对比之下，我们再来看看基于xml的配置方式。（偷懒就只做一个了）

public interface AccountMapper1 { int update(@Param("money") double money, @Param("id") int id);}

首先需要写一个接口，然后在application中添加配置

mybatis.mapper-locations=classpath*:mybatis/*Mapper.xmlmybatis.type-aliases-package=com.jinwen..jinwen.www.BeetlSQL.Dao"); conf.setDaoSuffix("Dao"); conf.setSqlManagerFactoryBeanName("sqlManagerFactoryBean"); return conf; } @Bean(name = "sqlManagerFactoryBean") @Primary public SqlManagerFactoryBean getSqlManagerFactoryBean(@Qualifier("datasource") DataSource datasource) { SqlManagerFactoryBean factory = new SqlManagerFactoryBean(); BeetlSqlDataSource source = new BeetlSqlDataSource(); source.setMasterSource(datasource); factory.setCs(source); factory.setDbStyle(new MySqlStyle()); factory.setInterceptors(new Interceptor[]{new DebugInterceptor()}); factory.setNc(new UnderlinedNameConversion());//开启驼峰 factory.setSqlLoader(new ClasspathLoader("/sql"));//sql文件路径 return factory; } //配置数据库 @Bean(name = "datasource") public DataSource getDataSource() { return DataSourceBuilder.create().url("jdbc:mysql://localhost:3306/springboottest").username("root").password("root").build(); } // //开启事务// @Bean(name = "txManager")// public DataSourceTransactionManager getDataSourceTransactionManager(@Qualifier("datasource") DataSource datasource) {// DataSourceTransactionManager dsm = new DataSourceTransactionManager();// dsm.setDataSource(datasource);// return dsm;// } }

注意这里的配置包扫描，需要扫描自己的包，不然会包no find

根据配置，编写的“sql”代码将在sql路径下，需要在resources下创建这个文件夹

编写相关操作

实体：

@Datapublic class Account { private int id ; private String name ; private double money;}

dao层：

@SqlResource("account")public interface BeetlSQLAccountDao extends BaseMapper {// @SqlStatement(params = "name") Account selectAccountByName(String name);}</account>

@SqlResource注解就是在sql目录下对应的.md文件。如果是java.account则对应是sql目录下的java目录下的account.md文件。这个文件写有相关的对数据库的操作。

注意，这里继承的BaseMapper是由BeetlSQL提供的。

account.md 编写

<em>selectAccountByName</em><em>===</em>*根据name获account select * from account where name= #name#

Controller层：

@RestController@RequestMapping("/beetlsql/account")public class BeetlSQLAccountController { @Autowired private BeetlSQLAccountDao beetlSQLAccountDao; @RequestMapping(value = "/list",method = RequestMethod.<em>GET</em>) public List getAccounts(){ return beetlSQLAccountDao.all(); } @RequestMapping(value = "/{id}",method = RequestMethod.<em>GET</em>) public Account getAccountById(@PathVariable("id") int id){ return beetlSQLAccountDao.unique(id); } @RequestMapping(value = "",method = RequestMethod.<em>GET</em>) public Account getAccountById(@RequestParam("name") String name){ return beetlSQLAccountDao.selectAccountByName(name); } @RequestMapping(value = "/{id}",method = RequestMethod.<em>PUT</em>) public String updateAccount(@PathVariable("id")int id , @RequestParam(value = "name",required = true)String name, @RequestParam(value = "money" ,required = true)double money){ Account account=new Account(); account.setMoney(money); account.setName(name); account.setId(id); int t= beetlSQLAccountDao.updateById(account); if(t==1){ return account.toString(); }else { return "fail"; } } @RequestMapping(value = "",method = RequestMethod.<em>POST</em>) public String postAccount( @RequestParam(value = "name")String name, @RequestParam(value = "money" )double money) { Account account = new Account(); account.setMoney(money); account.setName(name); KeyHolder t = beetlSQLAccountDao.insertReturnKey(account); if (t.getInt() > 0) { return account.toString(); } else { return "fail"; } } }</account>

注意：SpringBoot没有提供BeetlSQL的@Transactional支持，需要自己添加事务的支持，上面被注释的代码中有编写到