英文:
How to run strored procedure CREATE query using jpa
问题
以下是翻译好的部分：
@Test
public void procedureTest(){
EntityManager em = getEntityManager();
em.getTransaction().begin();
StoredProcedureQuery storedProcedureQuery = em.createStoredProcedureQuery("DELIMITER //\n" +
"CREATE PROCEDURE INSERT_SECURITY_PARAMETERS()\n" +
"BEGIN\n" +
"DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000' ErrorCode;\n" +
"INSERT INTO TB_ASMI_SECURITY_PARAMETERS (APP_ID,MIN_NUM_NUM,MIN_NUM_SPCL_CHAR,MIN_NUM_UPPER_CASE_CHAR,MIN_LENGTH,MAX_LENGTH,PASS_CHANGE_FREQ,LAST_N_PASS_NOT_TO_USE,SESSION_TIMEOUT,NOOFFAILEDCOUNTS,CREATE_USER_ID,CREATE_TS,VERSION_NO,SERVER_TOKEN,FAIL_COUNT_TIMEOUT,PASSWORD_COUNT,DEFAULT_AUTHORIZATION,ALLOW_USER_PASSWORD_ENTRY,AUTO_APPROVE,PWD_COMM_CHANNEL,AUTH_STATUS,MULTIPLE_SESSION_ALLOWED,OTP_LENGTH,OTP_VALIDATION_COUNT,OTP_EXPIRY,OTP_RESEND,OTP_FORMAT,OTP_RESEND_COUNT,OTP_RESEND_LOCK_TIMEOUT,OTP_REGEN_COUNT,DATA_INTEGRITY,TXN_LOG_REQ,TXN_LOG_PAYLOAD,FMW_TXN_REQ,FMW_TXN_PAYLOAD, ACCESS_TOKEN_EXPIRY)\n" +
" VALUES('Admin',1,1,1,8,32,30,1,'3000',5,'System',null,1,'ADMIN',5,5,'Y','N','Y','BOTH','A','Y','6','5','600','N','numeric','3','300','0','Y','Y','NONE','Y','NONE','1800');\n" +
"END\n" +
"//\n" +
"DELIMITER ;");
storedProcedureQuery.execute();
em.getTransaction().commit();
em.close();
}
希望这对您有所帮助！如果您需要进一步的翻译或帮助，请随时提问。
英文:
I want create stored procedure using existing query. I am not able run the whole query, it's throwing SQLGrammer exception.
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//
CREATE PROCEDURE INSERT_SECURITY_PARAMETERS()
BEGIN
DECLARE CONTINUE HANDLER ' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:391)
at com.mysql.cj.jdbc.CallableStatement.execute(CallableStatement.java:842)
My test code like below:
@Test
public void procedureTest(){
EntityManager em = getEntityManager();
em.getTransaction().begin();
StoredProcedureQuery storedProcedureQuery = em.createStoredProcedureQuery("DELIMITER //\n" +
"CREATE PROCEDURE INSERT_SECURITY_PARAMETERS()\n" +
"BEGIN\n" +
"DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000' ErrorCode;\n" +
"INSERT INTO TB_ASMI_SECURITY_PARAMETERS (APP_ID,MIN_NUM_NUM,MIN_NUM_SPCL_CHAR,MIN_NUM_UPPER_CASE_CHAR,MIN_LENGTH,MAX_LENGTH,PASS_CHANGE_FREQ,LAST_N_PASS_NOT_TO_USE,SESSION_TIMEOUT,NOOFFAILEDCOUNTS,CREATE_USER_ID,CREATE_TS,VERSION_NO,SERVER_TOKEN,FAIL_COUNT_TIMEOUT,PASSWORD_COUNT,DEFAULT_AUTHORIZATION,ALLOW_USER_PASSWORD_ENTRY,AUTO_APPROVE,PWD_COMM_CHANNEL,AUTH_STATUS,MULTIPLE_SESSION_ALLOWED,OTP_LENGTH,OTP_VALIDATION_COUNT,OTP_EXPIRY,OTP_RESEND,OTP_FORMAT,OTP_RESEND_COUNT,OTP_RESEND_LOCK_TIMEOUT,OTP_REGEN_COUNT,DATA_INTEGRITY,TXN_LOG_REQ,TXN_LOG_PAYLOAD,FMW_TXN_REQ,FMW_TXN_PAYLOAD, ACCESS_TOKEN_EXPIRY)\n" +
" VALUES('Admin',1,1,1,8,32,30,1,'3000',5,'System',null,1,'ADMIN',5,5,'Y','N','Y','BOTH','A','Y','6','5','600','N','numeric','3','300','0','Y','Y','NONE','Y','NONE','1800');\n" +
"END\n" +
"//\n" +
"DELIMITER ;");
storedProcedureQuery.execute();
em.getTransaction().commit();
em.close();
}
If I run from mysql worckbench it's working fine.
I tried different ways to run the script. Please help me in this. Thank you.
专注分享java语言的经验与见解，让所有开发者获益！
评论