今天学习了在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文件如下：
1 <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"> 2 <modelVersion>4.0.0</modelVersion> 3 <groupId>com.allen.springboot.learn</groupId> 4 <artifactId>springboot_data-jpa</artifactId> 5 <version>0.0.1-SNAPSHOT</version> 6 7 <parent> 8 <groupId>org.springframework.boot</groupId> 9 <artifactId>spring-boot-starter-parent</artifactId> 10 <version>1.5.10.RELEASE</version> 11 </parent> 12 13 <dependencies> 14 <!-- data-jpa --> 15 <dependency> 16 <groupId>org.springframework.boot</groupId> 17 <artifactId>spring-boot-starter-data-jpa</artifactId> 18 </dependency> 19 <!-- mysql --> 20 <dependency> 21 <groupId>mysql</groupId> 22 <artifactId>mysql-connector-java</artifactId> 23 </dependency> 24 <!--测试--> 25 <dependency> 26 <groupId>org.springframework.boot</groupId> 27 <artifactId>spring-boot-starter-test</artifactId> 28 <scope>test</scope> 29 </dependency> 30 31 </dependencies> 32 33 </project>
创建启动类：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn; 5 6 import org.springframework.boot.SpringApplication; 7 import org.springframework.boot.autoconfigure.SpringBootApplication; 8 9 /** 10 * @author admin 11 * 12 */ 13 @SpringBootApplication 14 public class Application { 15 16 /** 17 * @param args 18 */ 19 public static void main(String[] args) { 20 SpringApplication.run(Application.class, args); 21 } 22 23 }
创建application.properties属性文件：
1 ##datasource config 2 #开启包的自动扫描 3 entitymanager.packagesToScan=com.allen.springboot.learn.entity 4 #数据库链接 5 spring.datasource.url=jdbc:mysql://localhost:3306/test 6 #用户 7 spring.datasource.username=root 8 #密码 9 spring.datasource.password=123456 10 #自动更新表 11 spring.jpa.hibernate.ddl-auto=update 12 #数据库访问方言 13 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect 14 #显示sql 15 spring.jpa.properties.hibernate.show_sql=true
创建实体类：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.entity; 5 6 import javax.persistence.Entity; 7 import javax.persistence.GeneratedValue; 8 import javax.persistence.GenerationType; 9 import javax.persistence.Id; 10 import javax.persistence.Table; 11 12 /** 13 * @author admin 14 * 15 */ 16 @Entity 17 @Table(name="t_customer") 18 public class Customer { 19 20 @Id 21 @GeneratedValue(strategy=GenerationType.AUTO) 22 private Long id; 23 private String name; 24 private int age; 25 26 protected Customer() {} 27 28 public Customer(String name, int age){ 29 this.name = name; 30 this.age = age; 31 } 32 33 public Customer(Long id, String name, int age){ 34 this.id = id; 35 this.name = name; 36 this.age = age; 37 } 38 39 @Override 40 public String toString(){ 41 return String.format("Customer[id=%d, name='%s', age=%d]", id, name, age); 42 } 43 44 public Long getId() { 45 return id; 46 } 47 48 public void setId(Long id) { 49 this.id = id; 50 } 51 52 public String getName() { 53 return name; 54 } 55 56 public void setName(String name) { 57 this.name = name; 58 } 59 60 public int getAge() { 61 return age; 62 } 63 64 public void setAge(int age) { 65 this.age = age; 66 } 67 68 }
创建一个CRUD的接口，来实现对象的增删改查，通过继承CrudRepository，使用data jpa提供的简单的CRUD方法，代码如下：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.repository; 5 6 import org.springframework.data.repository.CrudRepository; 7 8 import com.allen.springboot.learn.entity.Customer; 9 10 /** 11 * @author admin 12 * 13 */ 14 public interface CustomerCrudRepository extends CrudRepository<Customer, Long>{ 15 16 }
下来写个测试类，测试以上方法的执行结果，使用JUnit测试，代码如下：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.repository.test; 5 6 import java.util.ArrayList; 7 import java.util.Iterator; 8 import java.util.List; 9 10 import org.junit.Test; 11 import org.junit.runner.RunWith; 12 import org.springframework.beans.factory.annotation.Autowired; 13 import org.springframework.boot.test.context.SpringBootTest; 14 import org.springframework.test.context.junit4.SpringRunner; 15 16 import com.allen.springboot.learn.entity.Customer; 17 import com.allen.springboot.learn.repository.CustomerCrudRepository; 18 19 /** 20 * @author admin 21 * 22 */ 23 @SpringBootTest 24 @RunWith(SpringRunner.class) 25 public class CustomerCrudRepositoryTest { 26 27 @Autowired 28 private CustomerCrudRepository customerCrudRepository; 29 30 @Test 31 public void testAdd(){ 32 Customer customer = new Customer("smith", 8); 33 customerCrudRepository.save(customer); 34 } 35 36 //@Test 37 public void testAddList(){ 38 Customer customer = new Customer("kb", 3); 39 Customer customer1 = new Customer("kg", 4); 40 List<Customer> list = new ArrayList<Customer>(); 41 list.add(customer); 42 list.add(customer1); 43 customerCrudRepository.save(list); 44 } 45 46 //@Test 47 public void exists(){ 48 boolean flag = customerCrudRepository.exists(Long.valueOf(3)); 49 System.out.println(flag); 50 } 51 52 //@Test 53 public void testFindById(){ 54 Customer c = customerCrudRepository.findOne(Long.valueOf(1)); 55 System.out.println(c.toString()); 56 } 57 58 //@Test 59 public void testFindAll(){ 60 Iterable<Customer> customers = customerCrudRepository.findAll(); 61 Iterator<Customer> its = customers.iterator(); 62 while(its.hasNext()){ 63 System.out.println(its.next().toString()); 64 } 65 } 66 67 //@Test 68 public void testFindAllByParams(){ 69 List<Long> ids = new ArrayList<Long>(); 70 ids.add(Long.valueOf(2)); 71 ids.add(Long.valueOf(4)); 72 Iterable<Customer> customers = customerCrudRepository.findAll(ids); 73 Iterator<Customer> its = customers.iterator(); 74 while(its.hasNext()){ 75 System.out.println(its.next().toString()); 76 } 77 } 78 79 //@Test 80 public void testDelete(){ 81 //根据ID删 82 //customerCrudRepository.delete(Long.valueOf(2)); 83 //通过对象删 84 //Customer customer = new Customer(Long.valueOf(3), "kg", 4); 85 //customerCrudRepository.delete(customer); 86 //通过对象集合删 87 //List<Customer> list = new ArrayList<Customer>(); 88 //Customer customer1 = new Customer(Long.valueOf(5), "wd", 5); 89 //list.add(customer1); 90 //Customer customer2 = new Customer(Long.valueOf(4), "kd", 5); 91 //list.add(customer2); 92 //customerCrudRepository.delete(list); 93 //删除所有 94 //customerCrudRepository.deleteAll(); 95 } 96 97 }
一般我们在使用中，还会用到分页，排序查询，对于这种查询，springboot也提供了接口支持。实现很简单，我们只需要继承对应的接口就可以了。代码如下：
创建接口类，并继承PagingAndSortingRepository
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.repository; 5 6 import org.springframework.data.repository.PagingAndSortingRepository; 7 8 import com.allen.springboot.learn.entity.Customer; 9 10 /** 11 * @author admin 12 * 13 */ 14 public interface CustomerPagingAndSortingRepository extends PagingAndSortingRepository<Customer, Long> { 15 16 }
接下来写个测试类，测试排序和分页查询，代码如下：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.repository.test; 5 6 import java.util.Iterator; 7 import java.util.List; 8 9 import org.junit.Test; 10 import org.junit.runner.RunWith; 11 import org.springframework.beans.factory.annotation.Autowired; 12 import org.springframework.boot.test.context.SpringBootTest; 13 import org.springframework.data.domain.Page; 14 import org.springframework.data.domain.PageRequest; 15 import org.springframework.data.domain.Pageable; 16 import org.springframework.data.domain.Sort; 17 import org.springframework.data.domain.Sort.Direction; 18 import org.springframework.test.context.junit4.SpringRunner; 19 20 import com.allen.springboot.learn.entity.Customer; 21 import com.allen.springboot.learn.repository.CustomerPagingAndSortingRepository; 22 23 /** 24 * @author admin 25 * 26 */ 27 @SpringBootTest 28 @RunWith(SpringRunner.class) 29 public class CustomerPagingAndSortingRepositoryTest { 30 31 @Autowired 32 private CustomerPagingAndSortingRepository customerPagingAndSortingRepository; 33 34 //排序查询 35 //@Test 36 public void testQueryWithSort(){ 37 Sort sort = new Sort(Direction.DESC, "age");//排序方式和排序字段 38 Iterable<Customer> customers = customerPagingAndSortingRepository.findAll(sort); 39 Iterator<Customer> its = customers.iterator(); 40 while(its.hasNext()){ 41 System.out.println(its.next()); 42 } 43 } 44 45 //分页查询 46 //@Test 47 public void testQueryWithPage(){ 48 Pageable pageable = new PageRequest(2, 3);//页号和页面数据量 49 Page<Customer> customers = customerPagingAndSortingRepository.findAll(pageable); 50 List<Customer> list = customers.getContent(); 51 if(list != null && list.size()>0){ 52 for(int i=0;i<list.size();i++){ 53 System.out.println(list.get(i)); 54 } 55 } 56 } 57 58 //分页 排序 59 @Test 60 public void testQueryWithPageAndSort(){ 61 Sort sort = new Sort(Direction.DESC, "age"); 62 Pageable pageable = new PageRequest(1, 3, sort); 63 Page<Customer> customers = customerPagingAndSortingRepository.findAll(pageable); 64 List<Customer> list = customers.getContent(); 65 if(list != null && list.size()>0){ 66 for(int i=0;i<list.size();i++){ 67 System.out.println(list.get(i)); 68 } 69 } 70 } 71 72 }
尽管springboot提供了很多接口方法供我们使用，但有时我们仍然需要根据业务需要来写自己的SQL，这种springboot也是支持的，我们需要继承JpaRepository，然后书写自己的查询，代码如下：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.repository; 5 6 import java.util.List; 7 8 import org.springframework.data.jpa.repository.JpaRepository; 9 import org.springframework.data.jpa.repository.Modifying; 10 import org.springframework.data.jpa.repository.Query; 11 import org.springframework.transaction.annotation.Transactional; 12 13 import com.allen.springboot.learn.entity.Customer; 14 15 /** 16 * @author admin 17 * 18 */ 19 public interface CustomerSQLRepository extends JpaRepository<Customer, Long> { 20 21 @Query("select customer from Customer customer where customer.name = ?1 and customer.age = ?2") 22 List<Customer> selectByNameAndAge(String name, int age); 23 24 @Transactional 25 @Modifying// 非 只读，执行修改操作(增、删、改)时需要指定 26 @Query("update Customer set age = ?2 where name = ?1") 27 int updateByNameAndAge(String name, int age); 28 29 }
测试类：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.repository.test; 5 6 import java.util.List; 7 8 import org.junit.Test; 9 import org.junit.runner.RunWith; 10 import org.springframework.beans.factory.annotation.Autowired; 11 import org.springframework.boot.test.context.SpringBootTest; 12 import org.springframework.test.context.junit4.SpringRunner; 13 14 import com.allen.springboot.learn.entity.Customer; 15 import com.allen.springboot.learn.repository.CustomerSQLRepository; 16 17 /** 18 * @author admin 19 * 20 */ 21 @SpringBootTest 22 @RunWith(SpringRunner.class) 23 public class CustomerSQLRepositoryTest { 24 25 @Autowired 26 private CustomerSQLRepository customerSQLRepository; 27 28 //@Test 29 public void test1(){ 30 List<Customer> customerList = customerSQLRepository.selectByNameAndAge("kobe", 6); 31 if(customerList != null && customerList.size()>0){ 32 for(Customer customer : customerList){ 33 System.out.println(customer); 34 } 35 }else{ 36 System.out.println("没有查询到数据！"); 37 } 38 } 39 40 @Test 41 public void test2(){ 42 customerSQLRepository.updateByNameAndAge("kobe", 38); 43 } 44 45 46 }
下来看看如何使用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
1 ## dataSource 2 spring.datasource.driverClassName = com.mysql.jdbc.Driver 3 spring.datasource.url = jdbc:mysql://localhost:3306/springboot 4 spring.datasource.username = root 5 spring.datasource.password = 123456 6 7 ## mybatis 8 # 指定xml文件地址 9 mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
在resources目录下创建application.properties配置文件中指定路径下的xml文件，如下：
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.allen.springboot.learn.mapper.CustomerMapper" > 4 <resultMap id="BaseResultMap" type="com.allen.springboot.learn.entity.Customer" > 5 <id column="id" property="id" jdbcType="INTEGER" /> 6 <result column="name" property="name" jdbcType="VARCHAR" /> 7 <result column="age" property="age" jdbcType="INTEGER" /> 8 </resultMap> 9 <sql id="Base_Column_List" > 10 id, name, age 11 </sql> 12 <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > 13 select <include refid="Base_Column_List" /> 14 from customer where id = #{id,jdbcType=INTEGER} 15 </select> 16 <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > 17 delete from customer 18 where id = #{id,jdbcType=INTEGER} 19 </delete> 20 <insert id="insert" parameterType="com.allen.springboot.learn.entity.Customer" > 21 insert into customer (id, name, age) 22 values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}) 23 </insert> 24 <update id="updateByPrimaryKeySelective" parameterType="com.allen.springboot.learn.entity.Customer" > 25 update customer 26 <set > 27 <if test="name != null" > 28 name = #{name,jdbcType=VARCHAR}, 29 </if> 30 <if test="age != null" > 31 age = #{age,jdbcType=INTEGER}, 32 </if> 33 </set> 34 where id = #{id,jdbcType=INTEGER} 35 </update> 36 <update id="updateByPrimaryKey" parameterType="com.allen.springboot.learn.entity.Customer" > 37 update customer 38 set name = #{name,jdbcType=VARCHAR}, 39 age = #{age,jdbcType=INTEGER} 40 where id = #{id,jdbcType=INTEGER} 41 </update> 42 <select id="getAllCustomer" resultMap="BaseResultMap"> 43 select <include refid="Base_Column_List" /> from customer 44 </select> 45 </mapper>
下来写个测试类，测试mybatis的方法，代码如下：
1 /** 2 * 3 */ 4 package com.allen.springboot.learn.mapper.test; 5 6 import java.util.List; 7 8 import org.junit.Test; 9 import org.junit.runner.RunWith; 10 import org.springframework.beans.factory.annotation.Autowired; 11 import org.springframework.boot.test.context.SpringBootTest; 12 import org.springframework.test.context.junit4.SpringRunner; 13 14 import com.allen.springboot.learn.entity.Customer; 15 import com.allen.springboot.learn.mapper.CustomerMapper; 16 17 /** 18 * @author admin 19 * 20 */ 21 @SpringBootTest 22 @RunWith(SpringRunner.class) 23 public class CustomerMapperTest { 24 25 @Autowired 26 private CustomerMapper customerMapper; 27 28 //@Test 29 public void testAdd(){ 30 Customer customer = new Customer("kobe", 6); 31 int result = customerMapper.insert(customer); 32 System.out.println("添加结果："+result); 33 } 34 35 @Test 36 public void testQuery(){ 37 Customer customer = customerMapper.selectByPrimaryKey(2); 38 System.out.println(customer); 39 } 40 41 //@Test 42 public void testList(){ 43 List<Customer> customerList = customerMapper.getAllCustomer(); 44 if(customerList != null && customerList.size()>0){ 45 for(Customer customer : customerList){ 46 System.out.println(customer); 47 } 48 } 49 } 50 51 //@Test 52 public void testUpdate(){ 53 Customer customer = new Customer(Long.valueOf(1), "smith", 5); 54 customerMapper.updateByPrimaryKeySelective(customer); 55 } 56 57 //@Test 58 public void testDelete(){ 59 customerMapper.deleteByPrimaryKey(3); 60 } 61 62 }
以上是在springboot中访问数据库的两种方式，有不正确的地方，希望朋友们可以指正。
7810
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
