Data access for Spring Boot: JPA and MyBatis

The JPA (Java persistence API) is a standard specification based on the O/R mapping (object-relational Mapping), mainly implemented including Hibernate, Eclipselink, and OPENJPA.

The nature of the ORM framework is to simplify the coding of operational databases in programming [2],JPA makes it easy for programmers not to write SQL statements, while MyBatis is suitable for flexible debugging of dynamic SQL.
This paper springboot the general process of integrating JPA and MyBatis, and gives two demos.

1 running the database in a Docker environment

Install the VMware virtual machine First, and then install Docker.
Pull the Oracle image in Docker and start the container.
Finally, the 1521 port of the virtual machine is mapped to the host.

• Database: Oracle XE 11g
• Host Database client: SQL developer

Note You must first disable the SELinux option in/etc/selinux/config and then restart the system. Otherwise, the installed Oracle does not have a default instance [[email protected] fsj]# Docker pull wnameless/oracle-xe-11g view installed mirrors [[email protected] fsj]# Docker Images boot container [[email protected] fsj]# Docker run-d-P9091:8080-P1521:1521--name XE Wnameless/oracle-xe-11g9bf0a03006471a2268b239c32bed00737ee94ef93f92650226c056b0fb891b40[[email  Protected] fsj]# netstat-nlpactive Internet connections (only servers) Proto recv-q send-q Local Address Foreign Address State Pid/program Name TCP0      0 0.0.0.0: A              0.0.0.0:* LISTEN948/sshd TCP0      0 127.0.0.1: -            0.0.0.0:* LISTEN1051/master TCP60      0:::1521:::* LISTEN5403/docker-proxy-c TCP60      0::: A:::* LISTEN948/sshd TCP60      0::1: -:::* LISTEN1051/master TCP60      0:::9091:::* LISTEN5396/docker-proxy-c UDP0      0 0.0.0.0: the              0.0.0.0:*767/dhclient UDP0      0 0.0.0.0:47439           0.0.0.0:*767/dhclient UDP60      0:::17787:::*767/dhclient RAW60      0::: -:::*7           646/networkmanager[[email protected] fsj]# docker ps-acontainer ID IMAGE COMMAND CREATED STATUS PORTS names2865df7bf94 A wnameless/oracle-xe-11g"/bin/sh-c '/usr/sbi"   5Seconds ago Up2Seconds A/TCP,0.0.0.0:1521-1521/TCP,0.0.0.0:9091-8080/tcp XE into the container of Shell:docker exec-it container-id/container-name Bash[[email protected] fsj]# Docker exec-it XE bash then with S Ystem/oracle landed. $ sqlplus system/oracle

Table Space

创建SQL> create tablespace ts_fsj    datafile‘/u01/app/oracle/oradata/XE/ts_fsj.dbf‘    size 50m    autoextend on    maxsize 10g;查看表空间SQL>  select name from v$datafileSQL> select file_name,tablespace_name from dba_data_files;

New user

create user boot  identified by boot  default tablespace ts_fsj  temporary tablespace temp  profile defaultgrant connect,resource,dba to boot

2 Springboot Integrated JPA

To create a new MAVEN project:

mvn archetype:generate   -DgroupId=com.fsj   -DartifactId=ex01   -DarchetypeArtifactId=maven-archetype-quickstart   -DinteractiveMode=false

Specific code

To establish a data access DAO layer, you need to define an interface that inherits the Jparepository. Then write the query method, for example

publicinterfaceextends JpaRepository<Person, Long> {    // 通过Address相等查询，参数为address    findByAddress(String address);    findByNameAndAddress(String name, String address);    @Query("select p from Person p where p.name= :name and p.address= :address")    withNameAndAddressQuery(@Param("name"@Param("address") String address);    // 对应Person实体中的 @NameQuery    withNameAndAddressNamedQuery(String name, String address);}

The simple query methods include:

1. Query by property name

Using FindBy, like, and and other keyword naming methods, you do not need to write specific SQL, such as

By address equality query, the parameter is address, you can writeList<Person> findByAddress(String address);

Equivalent to JPQL:select p from Person p where p.address=?1

Full query keywords see: Key words supported by Spring Data JPA Query method

2. Using @query annotations

3. Using @namequery annotations

Supports the use of JPA namequery to define query methods, a name mapping a query statement

When the DAO layer is written, it can be called directly at the controller layer.

3 Springboot Integrated MyBatis

Specific code

1. New MAVEN Project

2. Modify the Pom file to add a dependency package

3, modify application.properties Add related configuration

4. Add the Test city table and data in the database

CREATE TABLECity (ID VARCHAR2( +) not NULL, nameVARCHAR2( -), stateVARCHAR2( -), CountryVARCHAR2( -),PRIMARY KEY(ID));INSERT  intoCity (ID, name, State, country)VALUES(' 1 ',' San Francisco ',' CA ',' US ');INSERT  intoCity (ID, name, State, country)VALUES(' 2 ',' Beijing ',' BJ ',' CN ');INSERT  intoCity (ID, name, State, country)VALUES(' 3 ',' Guangzhou ',' GD ',' CN ');COMMIT;

5, Development mapper. Two ways of using annotations and XML

publicinterface CityMapper {    @Select("SELECT id, name, state, country FROM city WHERE state = #{state}")    queryByState(String state);    @Select("select * from city")    queryAll();    //xml方式，适合复杂查询    fuzzyQuery(@Param("name") String name);}

The corresponding Citymapper.xml

<?xmlVersion= "1.0" encoding= "UTF-8"?><! DOCTYPEMapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="Com.fsj.dao.CityMapper">    <sqlid="Base_column_list">ID, name, State, country</sql>    <resultmapid="Baseresultmap"type="Com.fsj.entity.City">        <resultcolumn="id"property="id"/>        <resultcolumn="Name"property="Name"/>        <resultcolumn="State"property="State"/>        <resultcolumn="Country"property="Country"/>    </resultMap>    <selectid="Fuzzyquery"resultmap="Baseresultmap">SELECT * from City WHERE 1>0<iftest="Name! = NULL and name! =" ">            <bindname="Fuzzyname"value="'% ' +name+ '% '"/>and UPPER (name) like #{fuzzyname, Jdbctype=varchar}</if>    </select></mapper>

6. Add test

@RunWith(Springrunner.class)@SpringBootTest//@Transactional Public classcitytest {PrivateMockmvc MVC1;PrivateMockmvc MVC2;PrivateString Url_get_all;@AutowiredCitymapper Citymapper;@AutowiredWebapplicationcontext Webapplicationcontext;@Before     Public void setUp()throwsException {mvc1 = mockmvcbuilders.Standalonesetup(New Citycontroller()).Build(); MVC2 = Mockmvcbuilders.Webappcontextsetup(Webapplicationcontext).Build(); Url_get_all ="/mybatis/queryall"; }@Test     Public void Testqueryall()throwsException {list<city> expected = Citymapper.Queryall(); list<city> actual = Citymapper.Queryall(); Assert.assertequals("Queryall test Failed"Json.tojsonstring(expected), JSON.tojsonstring(actual)); }/*** Verify that the controller responds properly and prints the returned results* http://www.ityouknow.com/springboot/2017/05/09/springboot-deploy.html* Mockmvc Object request failed here, may only apply to springboot1.3.6 old version     * @throws Exception     */    @Test     Public void testrequest()throwsException {mvc1.Perform(Mockmvcrequestbuilders.Get(Url_get_all).Accept(mediatype.Application_json))                .Andexpect(Mockmvcresultmatchers.Status().isOk())                .Anddo(Mockmvcresulthandlers.Print())                .Andreturn();/*output* org.springframework.web.util.NestedServletException:Request processing failed; nested exception is Java.lang.Nu Llpointerexception*/}@Test     Public void TestRequest2()throwsException {Mvcresult res = MVC2.Perform(Mockmvcrequestbuilders.Get(Url_get_all).Accept(mediatype.Application_json))                .Andreturn();intStatus = Res.GetResponse().GetStatus(); String content = Res.GetResponse().getcontentasstring(); List<city> expected = Citymapper.Queryall(); Assert.assertequals( $, status); Assert.assertequals(JSON.tojsonstring(expected), content);the//json element order is different, but the test        /*json Object comparison, automatic sorting in Python, object comparison to Trueexpected: [{"Country": "US", "id": "1", "name": "San Francisco", "state": "CA"},{"Country": "CN", "id": "2", "Name": "Bes" Ijing "," state ":" BJ "},{" Country ":" CN "," id ":" 3 "," Name ":" Guangzhou "," state ":" GD "}]Actual: [{"id": "1", "name": "San Francisco", "state": "CA", "Country": "US"},{"id": "2", "name": "Beijing", "state": " BJ "," country ":" CN "},{" id ":" 3 "," Name ":" Guangzhou "," state ":" GD "," country ":" CN "}]         * */}}

References

1. Wang Yunfei. "Spring Boot Combat"
2. Http://www.cnblogs.com/ityouknow/p/6037431.html
3. Https://github.com/mybatis/spring-boot-starter/wiki/Quick-Start
4. https://www.bysocket.com/?p=1610

Data access for Spring Boot: JPA and MyBatis