How to solve java sql SQLException: Missing IN or OUT parameter at index:: 1

Posted on Edited on In Views: Disqus: Symbols count in article: 1k Reading time ≈ 2 mins.

How to solve java sql SQLException: Missing IN or OUT parameter at index:: 1

這篇介紹How to solve java sql SQLException: Missing IN or OUT parameter at index:: 1

發生錯誤的case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
try (Connection conn = DBPool.getReadConnection();
	PreparedStatement stmt = conn.prepareStatement(sql);
	ResultSet rs = stmt.executeQuery();) {
	stmt.setString(1, "server1");
	while (rs.next()) {
		String userId = rs.getString("userid");
		if (userId == null) {
			continue;
		}
		//do something
	}
}

當PreparedStatement設定參數在PreparedStatement的executeQuery方法之後，
就會throw java.sql.SQLException: Missing IN or OUT parameter at index:: 1
原因是sql的where條件找不到對應的值。

解決方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
try (Connection conn = DBPool.getReadConnection();
	PreparedStatement stmt = conn.prepareStatement(sql)) {
	stmt.setString(1, "server1");
	try (ResultSet rs = stmt.executeQuery()) {
		while (rs.next()) {
			String userId = rs.getString("userid");
			if (userId == null) {
				continue;
			}
			//do something
		}
	}
}

將PreparedStatement設定參數移到PreparedStatement的executeQuery方法之前，
即可解決。