OPENTSP Connecting SQL Server databases &JPA calling stored procedures

OPENTSP framework of the module are connected to the MySQL database, the recent green steam has a connection to SQL Server database requirements, after the investigation of SQL Server database configuration, using JPA call stored procedures, the middle also stepped on a lot of pits, summed up, to avoid everyone fell into.

1. Configure SQL Server dependencies first

　　

        <Dependency>            <groupId>Com.microsoft.sqlserver</groupId>            <Artifactid>Sqljdbc4</Artifactid>            <version>4.0</version>        </Dependency>

　　Note: The download of the jar package from the MAVEN is unsuccessful, for ease of use, you can download the Sqljdbc4.jar and then switch to the path where the jar package is located with the command line, performing MVN Install:install-file-dfile=sqljdbc4.jar- dpackaging=jar-dgroupid=com.microsoft.sqlserver-dartifactid=sqljdbc4-dversion=4.0, this installs the jar package to the local dependent warehouse.

2. Modify the database connection configuration file Application-xxx.properties

　　

Spring.datasource.url=jdbc:sqlserver://ip:1433;databasename=testspring.datasource.username= Testspring.datasource.password=testspring.datasource.validation-query=select 1;spring.datasource.test-on-borrow =truespring.datasource.test-while-idle=truespring.datasource.driverclassname= com.microsoft.sqlserver.jdbc.sqlserverdriverspring.datasource.max-active=200spring.datasource.max-idle= 8spring.datasource.min-idle=8spring.datasource.initial-size=10spring.jpa.database = Sqlserverspring.jpa.database-platform=org.hibernate.dialect.sqlserver2005dialectspring.jpa.show-sql= Truespring.jta.bitronix.datasource.allow-local-transactions=true

SQL Server's Driverclass is Com.microsoft.sqlserver.jdbc.SQLServerDriver

　　

3. Java Connect SQL Server database 1) Create SQL file Test-dynamic.xml

　　

    <name= "querytest">        <![ cdata[           SELECT                jf0,                jf1,                jf2,                jf3,                jf4            from                testtable         ]]>    </sql-query>    

2) DAO layer invocation

　　

 Public String getJF01 (Testcommand command)    {        list<TestPojo> list = Dao.sqlfind ("querytest", Command, Testpojo.  Class);          for (Testpojo pojo:list) {            System.out.println ("pojo=" + Pojo);        }         return list.get (0). toString ();    }

Testpojo is set jf0 to jf5 these properties and set, Get method, no longer repeat.

4. JPA calls SQL Server stored procedure 1) Entity

　　In the way of annotations, to be associated with the database of 1 tables, any table can be. Multiple stored procedures with namedstoredprocedurequeries.

@Entity @table (name= "TestTable") @NamedStoredProcedureQueries ({@NamedStoredProcedureQuery (name= "TestProc1", procedurename = "TestProc1", parameters = {//Name is the name of the stored procedure in JPA, ProcedureName is the name of the database stored procedure@StoredProcedureParameter (mode = parametermode.in, name = "InParam1", type = String.)class), @StoredProcedureParameter (mode= parametermode.in, name = "InParam2", type = String.class)}), @NamedStoredProcedureQuery (name= "TestProc", procedurename = "TestProc", parameters ={@StoredProcedureParameter (mode= parametermode.in, name = "InParam1", type = String.class), @StoredProcedureParameter (mode= parametermode.in, name = "InParam2", type = String.class), @StoredProcedureParameter (mode= parametermode.out, name = "Outparam", type = String.class) })}) Public classTestentityImplementsjava.io.serializable{@IdPrivateString name;}

2) Repository

The parameter uses @param, and the annotation content is consistent with the parameter name of the entity.

@Repository  Public Interface extends Jparepository<testentity, String>, jpaspecificationexecutor<testentity>{    @Procedure (name = "TestProc")// and entity JPA stored procedure name consistent with    string TestProc (@Param ("inParam1") string inParam1, @Param ("inParam2") String inParam2); }

3) Service

When you call repository directly, you have a single return value.

@Service  Public class extends Baseservice {        @Autowired    private  testrepository repository;      Public String testproc (Testcommand command)    {        = Repository.testproc1 ("Chen", String.valueof (System.currenttimemillis ()));        System.out.println ("outparam =" + out );         return Out ;    }}

5. Multiple out-of-parameter issues

Note: JPA only supports 1 out-of-out stored procedures, and cannot invoke multiple out-of-parameters stored procedures temporarily (you can receive multiple arguments for a SELECT statement). Our project happens to be multiple out of the parameter stored procedure, but also can not modify the stored procedure, only 1 single-out parameter stored procedures, encapsulating the multi-parameter stored procedures, and the multiple out of the parameters to merge into 1.

Inner Multi-parameter stored procedure:

ALTER PROCEDURE [dbo].[InProc]  @inParam1 VARCHAR( -),  @inParam2 VARCHAR( -),  @outParam1 VARCHAR( -) OUTPUT,@outParam2 VARCHAR( -) OUTPUT asBEGIN  Insert  intoMyTableValues(@inParam1,@inParam2); Set @outParam1 = 'result123'; Set @outParam2 = 'result456';END

View Code

Single out parameter stored procedure in outer package

ALTER PROCEDURE [dbo]. Outproc@inParam1 varchar( -) ,  @inParam2  varchar( -) ,  @outParam1  varchar( -) Output asDeclare @out1 varchar( -)Declare @out2 varchar( -)BEGIN    execInProc@inParam1,@inParam2,@out1Output@out2output; Set @outParam1 = @out1 + @out2 ;END

View Code

In the face of multiple out parameters, we can also use our most familiar JDBC mode, the key point is to get the database connection connection.

Session session =(org.hibernate.Session) entitymanager.getdelegate (); Sessionfactoryimplementor SF=(Sessionfactoryimplementor) session.getsessionfactory ();Try{Connection Connection= SF.Getconnectionprovider(). getconnection ();//Get database Connection connectionCallableStatement cs = Connection.preparecall ("{Call InProc (?,?,?,?)}"); Cs.setstring (1, "Chen"); Cs.setstring (2, String.valueof (System.currenttimemillis ())); Cs.registeroutparameter (3, Types.varchar);//register the first out parameterCs.registeroutparameter (4, Types.varchar);//register the second out parameterCs.execute (); String Code= Cs.getstring (3);//gets the first out parameter of outString message = cs.getstring (4);//gets the second out parameterSystem.out.println ("code=" +code+ ", message=" +message);} Catch(SQLException e) {e.printstacktrace ();}finally{cs.close (); Connection.close ();}

OPENTSP Connecting SQL Server databases &JPA calling stored procedures