今天学习了在springboot项目中访问数据库，做下笔记，以备后期查看。
Spring Data JPA 是 Spring 基于 ORM 框架和JPA 规范 封装的一套应用框架，包含了增删改查等常用功能，可以让用户用较少的代码实现对数据的访问和操作进而提高开发效率！
目前我在web开发中，访问数据库的常用ORM框架是hibernate和mybatis，而springboot默认提供的是使用Hibernate操作数据库，下面分别看看在springboot中如何使用hibernate和mybatis。
一 data jpa，springboot-data-jpa帮我们定义了一些简单的接口实现了一些简单的功能，如CURD。我们要使用这些功能，只需要继承接口CrudRepository。
创建建maven的springboot项目，引入所需要的jar，pom.xml文件如下：
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.allen.springboot.learn</groupId>
<artifactId>springboot_data-jpa</artifactId>
<version>0.0.1-SNAPSHOT</version> <parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.10.RELEASE</version>
</parent> <dependencies>
<!-- data-jpa -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--测试-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency> </dependencies> </project>
创建启动类：
/**
*
*/
package com.allen.springboot.learn; import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication; /**
* @author admin
*
*/
@SpringBootApplication
public class Application { /**
* @param args
*/
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
} }
创建application.properties属性文件：
##datasource config
#开启包的自动扫描
entitymanager.packagesToScan=com.allen.springboot.learn.entity
#数据库链接
spring.datasource.url=jdbc:mysql://localhost:3306/test
#用户
spring.datasource.username=root
#密码
spring.datasource.password=123456
#自动更新表
spring.jpa.hibernate.ddl-auto=update
#数据库访问方言
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
#显示sql
spring.jpa.properties.hibernate.show_sql=true
创建实体类：
/**
*
*/
package com.allen.springboot.learn.entity; import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table; /**
* @author admin
*
*/
@Entity
@Table(name="t_customer")
public class Customer { @Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
private String name;
private int age; protected Customer() {} public Customer(String name, int age){
this.name = name;
this.age = age;
} public Customer(Long id, String name, int age){
this.id = id;
this.name = name;
this.age = age;
} @Override
public String toString(){
return String.format("Customer[id=%d, name='%s', age=%d]", id, name, age);
} public Long getId() {
return id;
} public void setId(Long id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public int getAge() {
return age;
} public void setAge(int age) {
this.age = age;
} }
创建一个CRUD的接口，来实现对象的增删改查，通过继承CrudRepository，使用data jpa提供的简单的CRUD方法，代码如下：
/**
*
*/
package com.allen.springboot.learn.repository; import org.springframework.data.repository.CrudRepository; import com.allen.springboot.learn.entity.Customer; /**
* @author admin
*
*/
public interface CustomerCrudRepository extends CrudRepository<Customer, Long>{ }
下来写个测试类，测试以上方法的执行结果，使用JUnit测试，代码如下：
/**
*
*/
package com.allen.springboot.learn.repository.test; import java.util.ArrayList;
import java.util.Iterator;
import java.util.List; 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 com.allen.springboot.learn.entity.Customer;
import com.allen.springboot.learn.repository.CustomerCrudRepository; /**
* @author admin
*
*/
@SpringBootTest
@RunWith(SpringRunner.class)
public class CustomerCrudRepositoryTest { @Autowired
private CustomerCrudRepository customerCrudRepository; @Test
public void testAdd(){
Customer customer = new Customer("smith", 8);
customerCrudRepository.save(customer);
} //@Test
public void testAddList(){
Customer customer = new Customer("kb", 3);
Customer customer1 = new Customer("kg", 4);
List<Customer> list = new ArrayList<Customer>();
list.add(customer);
list.add(customer1);
customerCrudRepository.save(list);
} //@Test
public void exists(){
boolean flag = customerCrudRepository.exists(Long.valueOf(3));
System.out.println(flag);
} //@Test
public void testFindById(){
Customer c = customerCrudRepository.findOne(Long.valueOf(1));
System.out.println(c.toString());
} //@Test
public void testFindAll(){
Iterable<Customer> customers = customerCrudRepository.findAll();
Iterator<Customer> its = customers.iterator();
while(its.hasNext()){
System.out.println(its.next().toString());
}
} //@Test
public void testFindAllByParams(){
List<Long> ids = new ArrayList<Long>();
ids.add(Long.valueOf(2));
ids.add(Long.valueOf(4));
Iterable<Customer> customers = customerCrudRepository.findAll(ids);
Iterator<Customer> its = customers.iterator();
while(its.hasNext()){
System.out.println(its.next().toString());
}
} //@Test
public void testDelete(){
//根据ID删
//customerCrudRepository.delete(Long.valueOf(2));
//通过对象删
//Customer customer = new Customer(Long.valueOf(3), "kg", 4);
//customerCrudRepository.delete(customer);
//通过对象集合删
//List<Customer> list = new ArrayList<Customer>();
//Customer customer1 = new Customer(Long.valueOf(5), "wd", 5);
//list.add(customer1);
//Customer customer2 = new Customer(Long.valueOf(4), "kd", 5);
//list.add(customer2);
//customerCrudRepository.delete(list);
//删除所有
//customerCrudRepository.deleteAll();
} }
一般我们在使用中，还会用到分页，排序查询，对于这种查询，springboot也提供了接口支持。实现很简单，我们只需要继承对应的接口就可以了。代码如下：
创建接口类，并继承PagingAndSortingRepository
/**
*
*/
package com.allen.springboot.learn.repository; import org.springframework.data.repository.PagingAndSortingRepository; import com.allen.springboot.learn.entity.Customer; /**
* @author admin
*
*/
public interface CustomerPagingAndSortingRepository extends PagingAndSortingRepository<Customer, Long> { }
接下来写个测试类，测试排序和分页查询，代码如下：
/**
*
*/
package com.allen.springboot.learn.repository.test; import java.util.Iterator;
import java.util.List; 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.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.test.context.junit4.SpringRunner; import com.allen.springboot.learn.entity.Customer;
import com.allen.springboot.learn.repository.CustomerPagingAndSortingRepository; /**
* @author admin
*
*/
@SpringBootTest
@RunWith(SpringRunner.class)
public class CustomerPagingAndSortingRepositoryTest { @Autowired
private CustomerPagingAndSortingRepository customerPagingAndSortingRepository; //排序查询
//@Test
public void testQueryWithSort(){
Sort sort = new Sort(Direction.DESC, "age");//排序方式和排序字段
Iterable<Customer> customers = customerPagingAndSortingRepository.findAll(sort);
Iterator<Customer> its = customers.iterator();
while(its.hasNext()){
System.out.println(its.next());
}
} //分页查询
//@Test
public void testQueryWithPage(){
Pageable pageable = new PageRequest(2, 3);//页号和页面数据量
Page<Customer> customers = customerPagingAndSortingRepository.findAll(pageable);
List<Customer> list = customers.getContent();
if(list != null && list.size()>0){
for(int i=0;i<list.size();i++){
System.out.println(list.get(i));
}
}
} //分页 排序
@Test
public void testQueryWithPageAndSort(){
Sort sort = new Sort(Direction.DESC, "age");
Pageable pageable = new PageRequest(1, 3, sort);
Page<Customer> customers = customerPagingAndSortingRepository.findAll(pageable);
List<Customer> list = customers.getContent();
if(list != null && list.size()>0){
for(int i=0;i<list.size();i++){
System.out.println(list.get(i));
}
}
} }
尽管springboot提供了很多接口方法供我们使用，但有时我们仍然需要根据业务需要来写自己的SQL，这种springboot也是支持的，我们需要继承JpaRepository，然后书写自己的查询，代码如下：
/**
*
*/
package com.allen.springboot.learn.repository; import java.util.List; import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional; import com.allen.springboot.learn.entity.Customer; /**
* @author admin
*
*/
public interface CustomerSQLRepository extends JpaRepository<Customer, Long> { @Query("select customer from Customer customer where customer.name = ?1 and customer.age = ?2")
List<Customer> selectByNameAndAge(String name, int age); @Transactional
@Modifying// 非 只读，执行修改操作(增、删、改)时需要指定
@Query("update Customer set age = ?2 where name = ?1")
int updateByNameAndAge(String name, int age); }
测试类：
/**
*
*/
package com.allen.springboot.learn.repository.test; import java.util.List; 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 com.allen.springboot.learn.entity.Customer;
import com.allen.springboot.learn.repository.CustomerSQLRepository; /**
* @author admin
*
*/
@SpringBootTest
@RunWith(SpringRunner.class)
public class CustomerSQLRepositoryTest { @Autowired
private CustomerSQLRepository customerSQLRepository; //@Test
public void test1(){
List<Customer> customerList = customerSQLRepository.selectByNameAndAge("kobe", 6);
if(customerList != null && customerList.size()>0){
for(Customer customer : customerList){
System.out.println(customer);
}
}else{
System.out.println("没有查询到数据！");
}
} @Test
public void test2(){
customerSQLRepository.updateByNameAndAge("kobe", 38);
} }
下来看看如何使用mybatis。使用mybatis时的配置方式有2种，一种是XML配置方式，一种是annotation注解方法，本文我们使用annotation方式。
创建工程，实体类，启动类和上文一样，我们只需要的启动类上加入注解扫描mybatis接口，通过注解@MapperScan指定要扫描的mapper接口
@MapperScan("com.allen.springboot.learn.mapper") //指定扫描包(com.allen.springboot.learn.mapper)下的接口文件
在pom.xml文件中引入mybatis相关jar文件，依赖：
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
创建属性配置文件application.properties
## dataSource
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/springboot
spring.datasource.username = root
spring.datasource.password = 123456 ## mybatis
# 指定xml文件地址
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
在resources目录下创建application.properties配置文件中指定路径下的xml文件，如下：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.allen.springboot.learn.mapper.CustomerMapper" >
<resultMap id="BaseResultMap" type="com.allen.springboot.learn.entity.Customer" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, name, age
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select <include refid="Base_Column_List" />
from customer where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from customer
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.allen.springboot.learn.entity.Customer" >
insert into customer (id, name, age)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.allen.springboot.learn.entity.Customer" >
update customer
<set >
<if test="name != null" >
name = #{name,jdbcType=VARCHAR},
</if>
<if test="age != null" >
age = #{age,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.allen.springboot.learn.entity.Customer" >
update customer
set name = #{name,jdbcType=VARCHAR},
age = #{age,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
<select id="getAllCustomer" resultMap="BaseResultMap">
select <include refid="Base_Column_List" /> from customer
</select>
</mapper>
下来写个测试类，测试mybatis的方法，代码如下：
/**
*
*/
package com.allen.springboot.learn.mapper.test; import java.util.List; 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 com.allen.springboot.learn.entity.Customer;
import com.allen.springboot.learn.mapper.CustomerMapper; /**
* @author admin
*
*/
@SpringBootTest
@RunWith(SpringRunner.class)
public class CustomerMapperTest { @Autowired
private CustomerMapper customerMapper; //@Test
public void testAdd(){
Customer customer = new Customer("kobe", 6);
int result = customerMapper.insert(customer);
System.out.println("添加结果："+result);
} @Test
public void testQuery(){
Customer customer = customerMapper.selectByPrimaryKey(2);
System.out.println(customer);
} //@Test
public void testList(){
List<Customer> customerList = customerMapper.getAllCustomer();
if(customerList != null && customerList.size()>0){
for(Customer customer : customerList){
System.out.println(customer);
}
}
} //@Test
public void testUpdate(){
Customer customer = new Customer(Long.valueOf(1), "smith", 5);
customerMapper.updateByPrimaryKeySelective(customer);
} //@Test
public void testDelete(){
customerMapper.deleteByPrimaryKey(3);
} }
以上是在springboot中访问数据库的两种方式，有不正确的地方，希望朋友们可以指正。