how to write long mysql queries in spring mvc in Dao layer-CodePudding

So I have been trying implement a sql query in my spring mvc project to insert data into the database. The query is working in the mysql workbench. but its giving me an error when i am writing same query in DAO layer in spring project. error i'm getting is below

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)

here is the link of text file of table creation and query https://drive.google.com/file/d/1C966vW9GNs9MFtEheCr2dhs8rztAROaw/view?usp=sharing

here is part of code in DAO layer

 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()});
}

Edits: here the junit test class I have been using to test that query package qnaapp;

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);
  }
}

Please suggest some solution for these problem.

CodePudding user response：

There is a problem with your sql writing, try the following way of writing： Be careful not to have extra quotes ' and parentheses ()

 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()});
}

CodePudding user response：

You have redundant single quotes (') around the where clause. Remove them and you should be fine:

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