Springboot使用JPA操作数据库

头像
之剑
    阅读 12 分钟

    第七章 使用JPA操作数据库

    本章主要介绍如何在Spring Boot的Web应用中使用Mysq数据库，也充分展示Spring Boot的优势（尽可能少的代码和配置）.

    数据访问层我们将使用Spring Data JPA和Hibernate(JPA的实现之一).

    Maven pom.xml文件

    lightsword/pom.xml

    在项目中增加如下依赖文件

    
        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>



        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-data-jpa</artifactId>

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

        </dependency>

    配置文件application.properties

    在src/main/resources/application.properties中设置数据源和jpa配置:

    
#mysql



spring.datasource.url = jdbc:mysql://localhost:3306/lightsword?useUnicode=true&characterEncoding=UTF8

spring.datasource.username = root

#root@localhost ::TZaMojg3ntd

spring.datasource.password = root

spring.datasource.driverClassName = com.mysql.jdbc.Driver



spring.datasource.max-active=0

spring.datasource.max-idle=0

spring.datasource.min-idle=0

spring.datasource.max-wait=10000

spring.datasource.max-wait-millis=31536000



# Specify the DBMS

spring.jpa.database = MYSQL

# Show or not log for each sql query

spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update)

spring.jpa.hibernate.ddl-auto = update

# Naming strategy

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy



# stripped before adding them to the entity manager)

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

    全部的配置都在如上的文件中了，不需要另外的XML配置和Java配置。

    上文中的数据库配置，你需要换成你的数据库的地址和用户名密码。

    hibernate的ddl-auto=update配置表名，数据库的表和列会自动创建（根据Java实体类,在scala中,只要在实体类上标注@Entity,成员变量上标注@BeanProperty）,这里 可以看到更多得hibernate配置。

    实体类

    创建一个HttpApi实体类，实体和Mysql数据库的http_api表相对应(这个表字段会在应用启动的时候,自动生成)。

    
package com.springboot.in.action.entity



import java.util.Date

import javax.persistence.{ Entity, GeneratedValue, GenerationType, Id }

import scala.language.implicitConversions

import scala.beans.BeanProperty



@Entity

class HttpApi {

  @Id

  @GeneratedValue(strategy = GenerationType.AUTO)

  @BeanProperty

  var id: Integer = _



  @BeanProperty

  var httpSuiteId: Integer = _

  //用例名称

  @BeanProperty

  var name: String = _



  //用例状态: -1未执行 0失败 1成功

  @BeanProperty

  var state: Integer = _

  //接口

  @BeanProperty

  var url: String = _



  //方法GET,POST

  @BeanProperty

  var method: String = _



  //post参数json string

  @BeanProperty

  var paramJsonStr: String = _



  //期望输出

  @BeanProperty

  var expectOutput: String = _



  //实际输出

  @BeanProperty

  var actualOutput: String = _



  @BeanProperty

  var runTimes: Integer = _

  @BeanProperty

  var owner: String = _

  @BeanProperty

  var gmtCreate: Date = _

  @BeanProperty

  var gmtModify: Date = _



}

    实体的数据访问层HttpApiDao

    实体的数据访问层HttpApiDao非常简单，只需要继承CrudRespositroy即可，CrudRespositroy已经实现了save,delete,deleteAll,findOne和findAll.

    （比较神奇的时这些方法其实CrudRespositroy中其实并没有实现，并且通过对dao层的方法的命名还可以实现新的方法）.

    当然,如果基本的CRUD方法满足不了我们稍微复杂一些的sql查询,我们可以直接定义sql查询语句,绑定dao层的方法.实例在如下代码中可以看到:

    
package com.springboot.in.action.dao



import java.util.List



import com.springboot.in.action.entity.HttpApi

import org.springframework.data.jpa.repository.Query

import org.springframework.data.repository.CrudRepository



import scala.language.implicitConversions



trait HttpApiDao extends CrudRepository[HttpApi, Integer] {

  def findAll(): List[HttpApi] // JavaConversions



  def save(t: HttpApi): HttpApi



  def findOne(id: Integer): HttpApi



  @Query(value = "SELECT * FROM http_api where http_suite_id = ?1", nativeQuery = true)

  def listByHttpSuiteId(id: Integer): List[HttpApi]



  @Query(value = "SELECT id FROM http_api where http_suite_id = ?1", nativeQuery = true)

  def listTestCaseId(httpSuiteId: Integer): List[Integer] // 隐式转换,直接用scala的List会报错:javax.persistence.NonUniqueResultException: result returns more than one elements] with root cause



   @Query(value = "SELECT * FROM http_api where name like %?1% ", nativeQuery = true) // like '%?%'

   def findByName(name: String): List[HttpApi]



  @Query(value = "select count(*) from http_api where http_suite_id = ?1 and state = 1", nativeQuery = true)

  def countPass(httpSuiteId: Integer): Int



  @Query(value = "select count(*) from http_api where http_suite_id = ?1 and state = 0", nativeQuery = true)

  def countFail(httpSuiteId: Integer): Int



}

    重点看一下

    • @Query注解里面的value和nativeQuery=true,意思是使用原生的sql查询语句.

    • sql模糊查询like语法,我们在写sql的时候是这样写的

    
like '%?%'

    但是在@Query的value字符串中, 这样写

    
SELECT * FROM http_api where name like %?1%

    • 在Springboot跟scala集成开发过程中,集合类需要使用java里面的api,直接用scala的List会报错:

    
javax.persistence.NonUniqueResultException: result returns more than one elements] with root cause.

    可以显示声明:

    
import java.util.List

    也可以使用隐式转换:

    
import scala.collection.JavaConversions._

    控制器HttpApiController

    新建控制器HttpApiController.scala代码

    
package com.springboot.in.action.controller



import java.util.Date

import java.util.concurrent.CountDownLatch



import com.alibaba.fastjson.JSON

import com.springboot.in.action.dao.{HttpApiDao, HttpReportDao, HttpSuiteDao}

import com.springboot.in.action.engine.OkHttp

import com.springboot.in.action.entity.{HttpApi, HttpReport}

import org.springframework.beans.factory.annotation.Autowired

import org.springframework.ui.Model

import org.springframework.web.bind.annotation.{PathVariable, RequestMapping, RequestMethod, RequestParam, ResponseBody, RestController}

import org.springframework.web.servlet.ModelAndView

import scala.collection.JavaConversions._



@RestController

@RequestMapping(Array("/httpapi"))

class HttpApiController @Autowired() (

    val HttpSuiteDao: HttpSuiteDao,

    val HttpApiDao: HttpApiDao,

    val HttpReportDao: HttpReportDao) {



  @RequestMapping(value = {

    Array("", "/")

  }, method = Array(RequestMethod.GET))

  def list(model: Model) = {

    model.addAttribute("httpapis", HttpApiDao.findAll())

    new ModelAndView("/httpapi/list")

  }



  @RequestMapping(value = {

    Array("/json")

  }, method = Array(RequestMethod.GET))

  def listJson() = HttpApiDao.findAll()



  @RequestMapping(value = {

    Array("/listHttpSuiteTestCase")

  }, method = Array(RequestMethod.GET))

  def listHttpSuiteTestCase(model: Model, @RequestParam(value = "httpSuiteId") httpSuiteId: Integer) = {

    var httpapis = HttpApiDao.listByHttpSuiteId(httpSuiteId)

    model.addAttribute("httpapis", httpapis)

    model.addAttribute("httpSuiteId", httpSuiteId)

    model.addAttribute("httpSuiteName", HttpSuiteDao.findOne(httpSuiteId).name)

    new ModelAndView("/httpapi/listHttpSuiteTestCase")

  }



  @RequestMapping(value = {

    Array("/listHttpSuiteTestCaseJson")

  },

    method = Array(RequestMethod.GET))

  @ResponseBody

  def listHttpSuiteTestCaseJson(model: Model, @RequestParam(value = "httpSuiteId") httpSuiteId: Integer) = {

    HttpApiDao.listByHttpSuiteId(httpSuiteId)

  }



  @RequestMapping(Array("/newPage/{httpSuiteId}"))

  def goNewPage(@PathVariable(value = "httpSuiteId") httpSuiteId: Integer, model: Model) = {

    model.addAttribute("httpSuiteId", httpSuiteId)

    model.addAttribute("httpSuiteName", HttpSuiteDao.findOne(httpSuiteId).name)

    new ModelAndView("/httpapi/new")

  }



  /**

   * 项目下面的用例编辑

   */

  @RequestMapping(Array("/editPage/{caseId}"))

  def goEditPage(model: Model, @PathVariable(value = "caseId") caseId: Integer, @RequestParam(value = "httpSuiteId") httpSuiteId: Integer) = {

    val httpapi = HttpApiDao.findOne(caseId)

    model.addAttribute("httpapi", httpapi)

    model.addAttribute("httpSuiteId", httpSuiteId)

    model.addAttribute("httpSuiteName", HttpSuiteDao.findOne(httpSuiteId).name)

    new ModelAndView("/httpapi/edit")

  }



  @RequestMapping(Array("/copyPage/{caseId}"))

  def goCopyPage(model: Model, @PathVariable(value = "caseId") caseId: Integer, @RequestParam(value = "httpSuiteId") httpSuiteId: Integer) = {

    val httpapi = HttpApiDao.findOne(caseId)

    model.addAttribute("httpapi", httpapi)

    model.addAttribute("httpSuiteId", httpSuiteId)

    model.addAttribute("httpSuiteName", HttpSuiteDao.findOne(httpSuiteId).name)

    new ModelAndView("/httpapi/copy")

  }



  @RequestMapping(Array("/detailPage/{id}"))

  def goDetailPage(model: Model, @PathVariable(value = "id") id: Integer) = {

    val httpapi = HttpApiDao.findOne(id)

    model.addAttribute("httpapi", httpapi)

    new ModelAndView("/httpapi/detail")

  }



  @RequestMapping(value = Array("/postnew"),

    method = Array(RequestMethod.POST))

  @ResponseBody

  def newOne(@RequestParam(value = "httpSuiteId") httpSuiteId: Integer,

    @RequestParam(value = "name") name: String,

    @RequestParam(value = "url") url: String,

    @RequestParam(value = "method") method: String,

    @RequestParam(value = "paramJsonStr") paramJsonStr: String,

    @RequestParam(value = "expectOutput") expectOutput: String,

    @RequestParam(value = "actualOutput") actualOutput: String,

    @RequestParam(value = "owner") owner: String) = {

    val httpapi = new HttpApi()

    httpapi.httpSuiteId = httpSuiteId

    httpapi.name = name

    httpapi.url = url

    httpapi.method = method

    httpapi.paramJsonStr = paramJsonStr

    httpapi.expectOutput = expectOutput

    httpapi.actualOutput = actualOutput

    httpapi.runTimes = 0

    httpapi.state = -1

    httpapi.owner = owner

    httpapi.gmtCreate = new Date()

    httpapi.gmtModify = new Date()

    HttpApiDao.save(httpapi)

  }



  @RequestMapping(value = Array("/postedit"),

    method = Array(RequestMethod.POST))

  @ResponseBody

  def editOne(@RequestParam(value = "id") id: Integer,

    @RequestParam(value = "name") name: String,

    @RequestParam(value = "url") url: String,

    @RequestParam(value = "method") method: String,

    @RequestParam(value = "paramJsonStr") paramJsonStr: String,

    @RequestParam(value = "expectOutput") expectOutput: String) = {

    val httpapi = HttpApiDao.findOne(id)

    httpapi.name = name

    httpapi.url = url

    httpapi.method = method

    httpapi.paramJsonStr = paramJsonStr

    httpapi.expectOutput = expectOutput

    httpapi.gmtModify = new Date()

    HttpApiDao.save(httpapi)

  }



  /**

   * 在新建用例页面,调试用例用

   */

  @RequestMapping(value = Array("/debugTest"),

    method = Array(RequestMethod.GET))

  @ResponseBody

  def debugTest(@RequestParam(value = "url") url: String,

    @RequestParam(value = "method") method: String,

    @RequestParam(value = "paramJsonStr") paramJsonStr: String) = {

    OkHttp.run(url, method, paramJsonStr)

  }



  /**

   * 执行用例

   */

  @RequestMapping(value = Array("/runTest"),

    method = Array(RequestMethod.GET))

  @ResponseBody

  def debugTest(@RequestParam(value = "id") id: Integer) = { runTestCase(id) }



  /**

   * 回归项目全部用例,每个用例单独起一个线程跑

   */

  @RequestMapping(value = Array("/testHttpSuite"),

    method = Array(RequestMethod.GET))

  @ResponseBody

  def testProject(@RequestParam(value = "httpSuiteId") httpSuiteId: Integer) = {

    val caseIds = HttpApiDao.listTestCaseId(httpSuiteId)



    val threads = caseIds.size

    val countDownLatch = new CountDownLatch(threads)



    for (cid <- caseIds) {

      val t = new TestCaseRunner(cid, countDownLatch)

      t.start

    }

    println("回归测试开始......")

    countDownLatch.await // now waiting sub thread done.

    println("回归测试结束!")

    val HttpReport = getHttpReport(httpSuiteId)

    // 保存测试结果

    HttpReportDao.save(HttpReport)

    HttpReport

  }



  def getHttpReport(httpSuiteId: Integer) = {

    println("自动化回归测试报告:")



    val p = HttpSuiteDao.findOne(httpSuiteId)

    val httpSuiteName = p.name

    val pass = HttpApiDao.countPass(httpSuiteId)

    val fail = HttpApiDao.countFail(httpSuiteId)



    val HttpReport = new HttpReport

    HttpReport.httpSuiteId = httpSuiteId

    HttpReport.httpSuiteName = httpSuiteName

    HttpReport.pass = pass

    HttpReport.fail = fail

    HttpReport.time = new Date

    println(JSON.toJSONString(HttpReport, true))

    HttpReport

  }



  /**

   * 执行caseId这个用例

   */



  def runTestCase(id: Integer) = {

    val tc = HttpApiDao.findOne(id)

    val url = tc.url

    val method = tc.method

    val paramJsonStr = tc.paramJsonStr

    println("接口url:" + url)

    println("方法:" + method)

    println("输入参数:" + paramJsonStr)



    val result = OkHttp.run(url, method, paramJsonStr)

    //执行次数+1

    tc.runTimes = tc.runTimes + 1

    println("实际输出:" + result)

    tc.actualOutput = result

    // 结果断言

    val expectOutput = tc.expectOutput

    val contains = result.contains(expectOutput)

    tc.state = if (contains) 1 else 0

    // 执行事件

    tc.gmtModify = new Date

    HttpApiDao.save(tc)

  }



  /**

   * TestCaseRunner

   */

  class TestCaseRunner(val caseId: Integer, val countDownLatch: CountDownLatch) extends Thread {



    override def run() {

      runTestCase(caseId)

      countDownLatch.countDown

    }

  }

}

    自动装配@Autowired Dao层代码,在Controller实现业务逻辑.

    运行测试

    启动脚本lightsword/run.sh

    
mvn clean scala:compile scala:run -Dlauncher=app

    运行lightsword/run.sh,启动应用.

    新建用例集,然后在此用例集中新建一个测试用例,如图所示:

    图片描述

    可以直接运行,可以看到测试结果.

    也可以浏览器访问:

    http://localhost:8888/httpapi/listHttpSuiteTestCaseJson?httpSuiteId=1

    看到Restful接口的json返回:

    
[{"id":1,"httpSuiteId":1,"name":"HelloSB测试","state":1,"url":"http://localhost:8888/hello","method":"GET","paramJsonStr":"{}","expectOutput":"LightSword","actualOutput":"{\"conent\":\"Hello, LightSword! Now is: Mon Jun 27 13:23:20 CST 2016\"}","runTimes":1,"owner":"陈光剑","gmtCreate":1467004998000,"gmtModify":1467005001000}]
    springbootjpa
    之剑
    449 声望85 粉丝

    一个会写诗的程序员。参与过多种平台工具的开发，具有丰富的平台开发经验，精通Kotlin （布道者）, Java 等编程语言， Spring Boot 专家。项目实战操练过Java、Android、Scala、Groovy等语言进行领域建模、架构设...

    « 上一篇
    Springboot使用velocity模板引擎
    下一篇 »
    Clojure环境安装与QuickStart

    引用和评论

    推荐阅读
    头像
    《Kotlin项目实战开发》第5章 函数与函数式编程

    之剑赞 2阅读 2.2k

    头像
    AES 加密和解密demo

    zZ_jie赞 3阅读 782评论 1

    头像
    JPA实体的状态

    吴季分赞 4阅读 187

    头像
    Spring Boot起步，CRUD、错误处理与宝塔部署

    微芒不朽赞 3阅读 617

    头像
    Thymeleaf的使用

    zZ_jie赞 3阅读 368

    头像
    实现 Angular 程序处理进度条

    kexb赞 3阅读 227

    头像
    Spring boot连接Minio实现文件的上传以及下载

    zZ_jie赞 2阅读 518

    0 条评论
    得票最新