I am facing error java.sql.SQLFeatureNotSupportedException in my prepare statement. I am using Mysql database.
Below is my code.
class tmp {
public static void main(String arg[]) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost/sample", "root", "root");
PreparedStatement pst = conn
.prepareStatement("select * from userinfo where firstname in(?)");
String[] Parameter = { "user1", "Administrator" };
Array sqlArray = conn.createArrayOf("VARCHAR", Parameter);
pst.setArray(1, sqlArray);
ResultSet rs = pst.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
For Mysql
-
Setting array is not possible in Mysql.
Instead of that you can form a query for (?,?,..) in the loop and same way for setting values.
String[] Parameter = { "user1", "Administrator" };
String query = "select * from userinfo where firstname in (";
String temp = "";
for(i = 0; i < Parameter.length; i++) {
temp += ",?";
}
temp = temp.replaceFirst(",", "");
temp += ")";
query = query + temp;
PreparedStatement pst = conn.prepareStatement(query);
so query becomes
select * from userinfo where firstname in (?,?)
and pass values also using loop.
For Oracle
-
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
String[] Parameter = { "user1", "Administrator" };
java.sql.Array sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);
.
.
pstmt.setArray(1, sqlArray);
Your answer is for
MySQL
orOracle
?It gives me the error "java.lang.ClassCastException: com.mysql.jdbc.JDBC4Connection cannot be cast to oracle.jdbc.OracleConnection"
Sorry that was for oracle.
how can i do it with Mysql
I have edited my answer..create (?,?...) programatically