[에러해결] sts | java.sql.SQLExeption: No value specified for parameter

• java.sql.SQLException: No value specified for parameter 1
• 첫번째 파라미터 부재

❗파라미터가 전해지지 안했을 때 발생하는 오류

UserDaoImpl.java

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    DataSource ds;
    
    @Override
    public User selectUser(String id) throws Exception {
        User user = null;
        String sql = "SELECT * FROM user_info WHERE id= ? ";

        try (
                Connection conn = ds.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
                ResultSet rs = pstmt.executeQuery(); //  select
        ) {
            pstmt.setString(1, id);
            
            if (rs.next()) {
                user = new User();
                user.setId(rs.getString(1));
                user.setPwd(rs.getString(2));
                user.setName(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setBirth(new Date(rs.getDate(5).getTime()));
                user.setSns(rs.getString(6));
                user.setReg_date(new Date(rs.getTimestamp(7).getTime()));
            }
        }
        return user;
    }
}

UserDaoImplTest.java

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/**/root-context.xml"})
public class UserDaoImplTest {
    @Autowired
    UserDao userDao;
    Calendar cal;
    
    @Test
    public void selectUser() throws Exception {
        userDao.deleteAll();
        assertTrue(userDao.count()==0);

        User user = new User("asdf", "1234", "abc", "aaa@aaa.com", new Date(cal.getTimeInMillis()), "fb", new Date());
        assertTrue(userDao.insertUser(user)==1);

        System.out.println("user = " + user);
        User user2 = userDao.selectUser(user.getId());
        assertTrue(user.equals(user2));

        user2 = userDao.selectUser("aaaaaaa");
        assertTrue(user2==null);
    }
}

✊🏻문제 원인

sql의 ?(파라미터)에 값을 pstmt.setString(1, id); 먼저 대입한 후에 pstmt.executeQuery(); 를 실행해야함

✨해결방법

try-with-resource를 사용하여 try()내에는 자원할당만 들어갈 수 있으므로 ResultSet rs = pstmt.executeQuery();을 try문 내로 빼주어 pstmt.setString(1, id); 뒤에 선언
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();

@Override
public User selectUser(String id) throws Exception {
	User user = null;
    String sql = "SELECT * FROM user_info WHERE id= ? ";

    try (
    	Connection conn = ds.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
    ) {
        pstmt.setString(1, id);
        ResultSet rs = pstmt.executeQuery(); //  select

        if (rs.next()) { 생략 }
      }
      return user;
}

[참고]