Use JPA to dynamically invoke SQL query data based on spring Boot

In the "Spring boot, using JPA operations SQL Server database completion crud", based on spring boot, using JPA to invoke the SQL Server database's stored procedure and return the records collection, completed the crud and called the stored procedure query data.

In many complex cases, there is a need to execute SQL directly to get the data.

Create the Nativequery method with "Entitymanager" to execute dynamic SQL.

1. Query result set mappings

Write named result set mappings on the "contact" entity under Package "Com.kxh.example.demo.domain" because many mappings can be written.

@SqlResultSetMapping annotations are mappings.

Name parameter, you can map the result set to a name.

The entities parameter is used to describe the Association of entity and query result fields.

 PackageCom.kxh.example.demo.domain;Importjavax.persistence.Entity;ImportJavax.persistence.EntityResult;ImportJavax.persistence.FieldResult;ImportJavax.persistence.GeneratedValue;ImportJavax.persistence.GenerationType;Importjavax.persistence.Id;Importjavax.persistence.NamedStoredProcedureQueries;ImportJavax.persistence.NamedStoredProcedureQuery;ImportJavax.persistence.ParameterMode;Importjavax.persistence.SqlResultSetMapping;Importjavax.persistence.StoredProcedureParameter; @Entity@SqlResultSetMapping (name = "Conatctmapping", entities = @EntityResult (entityclass = contact.                 Class, fields = {@FieldResult (name = "Name", column = "name"),  @FieldResult (Name = "Phone", column = "Phone"), @FieldResult (name = "Mail", column = "Mail" }))@NamedStoredProcedureQueries ({@NamedStoredProcedureQuery (name= "Getcontactslikename", ProcedureName= "Proc_get_contacts_like_name", Resultclasses= {Contact.class}, Parameters={@StoredProcedureParameter (mode=parametermode.in, name= "Name", type= String.class)            }        )}) Public classContact {@Id @GeneratedValue (strategy=generationtype.identity)Private LongID; PrivateString name; PrivateString Phone; PrivateString Mail;  PublicContact () {Super(); }         PublicContact (string name, string phone, string mail) {Super();  This. Name =name;  This. Phone =phone;  This. Mail =Mail; }         Public LonggetId () {return  This. ID; }         Public voidSetId (Longvalue) {         This. ID =value; }         PublicString GetName () {return  This. Name; }         Public voidsetName (String value) { This. Name =value; }         PublicString Getphone () {returnphone; }         Public voidSetphone (String value) { This. Phone =value; }         PublicString Getmail () {return  This. Mail; }         Public voidSetmail (String value) { This. Mail =value; }}

3. Calling through a business object

Add an execution function to the class "Contactsservice" under Package "Com.kxh.example.demo.service".

Create the Nativequery function with "Entitymanager", the first parameter is SQL, and the second parameter is the result set mapping name defined above.

Then pass in the query condition parameter, set the maximum number of returned result records, get the query result set.

 PackageCom.kxh.example.demo.service;Importjava.util.List;ImportJavax.persistence.EntityManager;ImportJavax.persistence.StoredProcedureQuery;Importorg.springframework.beans.factory.annotation.Autowired;Importorg.springframework.stereotype.Component;Importcom.kxh.example.demo.domain.Contact; @Component Public classContactsservice {@AutowiredPrivateEntitymanager Entitymanager; @SuppressWarnings ("Unchecked")     PublicList<contact>Findallviaproc (String name) {storedprocedurequery Storedprocedurequery= This. Entitymanager.createnamedstoredprocedurequery ("Getcontactslikename"); Storedprocedurequery.setparameter ("Name", name);       Storedprocedurequery.execute (); returnstoredprocedurequery.getresultlist (); } @SuppressWarnings ( "Unchecked")  Public List<contact> findallbyviaquery (String name) {List<Contact> contacts = this. Enti Tymanager. Createnativequery ("select name, phone, mail from contact where name Like:name", "Conatctma                Pping "). Setparameter (" name ", name). Setmaxresults (5)                . Getresultlist ();  return  contacts; }}

4. Provision of services through the Restcontroller

Add a new access path mapping that calls Contactsservice.findallbyviaquery (Namewhere) in the processing method to get the query result set.

 PackageCom.kxh.example.demo.controller;Importjava.util.ArrayList;Importjava.util.List;Importorg.springframework.beans.factory.annotation.Autowired;ImportOrg.springframework.web.bind.annotation.RequestBody;Importorg.springframework.web.bind.annotation.RequestMapping;ImportOrg.springframework.web.bind.annotation.RequestMethod;ImportOrg.springframework.web.bind.annotation.RestController;Importcom.kxh.example.demo.dao.ContactsRepository;Importcom.kxh.example.demo.domain.Contact;ImportCom.kxh.example.demo.service.ContactsService, @RestController @requestmapping ("/contacts") Public classContactscontroller {@Autowired contactsservice contactsservice;//omitted//using dynamic SQL to check@RequestMapping (value= "/query/viadnq/likename", method=requestmethod.get) PublicList<contact>findcontactsusedyanamicquerylikename (String name) {System.out.println ("Kxh1"); String Namewhere= Org.apache.commons.lang.StringUtils.join (Newstring[]{"%", name, "%"}, ""); List <Contact> contacts = contactsservice.findallbyviaquery (namewhere); if(Contacts = =NULL) {System.out.println ("Kxh4"); return NewArraylist<contact>(); } Else{System.out.println ("Kxh5"); returncontacts; }    }}

Code

End

Use JPA to dynamically invoke SQL query data based on spring Boot