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
- Wang Yunfei. "Spring Boot Combat"
- Http://www.cnblogs.com/ityouknow/p/6037431.html
- Https://github.com/mybatis/spring-boot-starter/wiki/Quick-Start
- https://www.bysocket.com/?p=1610
Data access for Spring Boot: JPA and MyBatis