如何在Dao層的springmvc中撰寫長mysql查詢

所以我一直在嘗試在我的spring mvc專案中實作一個sql查詢來將資料插入到資料庫中。該查詢在 mysql 作業臺中運行。但是當我在 Spring 專案的 DAO 層中撰寫相同的查詢時，它給了我一個錯誤。我得到的錯誤如下

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar 
[insert into question (question_id,questions,question_text,user_id) SELECT(?,?,?,?) from user 'where user_id ='2]; nested exception is 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 ''where user_id ='2' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1025)
at qnaapp.dao.UserDaoImpl.question(UserDaoImpl.java:44)
at qnaapp.service.UserServiceImpl.question(UserServiceImpl.java:27)
at qnaapp.UserServiceTest1.testQuestion(UserServiceTest1.java:45)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

這是表創建和查詢的文本檔案的鏈接 https://drive.google.com/file/d/1C966vW9GNs9MFtEheCr2dhs8rztAROaw/view?usp=sharing

這是DAO層中的部分代碼

 public int question(Question question) {
        String sql = "insert into question (question_id,questions,question_text,user_id) 
 SELECT(?,?,?,?) from user "
                      "'where user_id ='"  question.getUser_id();

    return jdbcTemplate.update(sql, new Object[] { question.getQuestion_id(),question.getQuestions(), question.getQuestion_text(), question.getUser_id()});
}

編輯：這里是我用來測驗查詢包 qnaapp 的 junit 測驗類；

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;


import qnaapp.model.Login;
import qnaapp.model.User;
import qnaapp.model.Question;
import qnaapp.service.UserService;


 @RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:qna/config/user-beans.xml" })
public class UserServiceTest1 {
 @Autowired
  private UserService userService;

 @Test
  public void testValidateUser() {
    Login login = new Login();
    login.setUsername("krishnadubey");
    login.setPassword("123456789");

    User user = userService.validateUser(login);
    Assert.assertEquals("Krishna", user.getFirstname());
  }

  @Test
  public void testQuestion() {
    //User user = new User();
    Question question = new Question();
    question.setQuestion_id(4);
    
    question.setQuestions("Who is founder of c?");
    question.setQuestion_text("Please Explain");
    question.setUser_id(2);
  

    int result = userService.question(question);
    Assert.assertEquals(1, result);
  }
}

請為這些問題提出一些解決方案。

uj5u.com熱心網友回復：

你的sql寫法有問題，試試下面的寫法： 注意不要有多余的引號'和括號()

 public int question(Question question) {
        String sql = "insert into question (question_id,questions,question_text,user_id) 
 SELECT ?,?,?,? from user where user_id ="  question.getUser_id();

    return jdbcTemplate.update(sql, new Object[] { question.getQuestion_id(),question.getQuestions(), question.getQuestion_text(), question.getUser_id()});
}

uj5u.com熱心網友回復：

子句周圍有多余的單引號 ( ') 。where洗掉它們，你應該沒問題：

String sql = 
    "insert into question (question_id,questions,question_text,user_id) "   
    "SELECT(?,?,?,?) from user "  
    "where user_id = ?";

標籤：

上一篇：SpringBoot中針對多個屬性值的單元測驗

下一篇：在oracle中的合并/插入命令期間出現列名CLUSTER和COMMENT的問題