springboot jdbctemplate string concat failing with error "Missing IN or OUT parameter at index:: 2"

Question

I am using Stringbuilder to concatenate the not null fields for select statement. But its failing with code "SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 2; nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 2"

I tried to print the used columns, they are printing fine. Please check and let me know where I am making the mistake.

	public List<Dbinv> listdbbysearch(String database, String server, String node, String dc , String env, String instancename ) {
	
		StringBuilder sqlstmt = new StringBuilder ("select *  from dbinv where ");
		StringBuilder tempval = new StringBuilder ("");

		  if ( database !=null) {
		  sqlstmt.append ( " database = ?");
		  tempval.append ("database");
		  }

		  if ( server !=null) {
		  sqlstmt.append ( " and server =?");
		  tempval.append(", server");
		  }

		  if ( node != null) {
		  sqlstmt.append ( " and node =?");
		  tempval.append(", node");
		  }

		  if ( dc != null) {
		  sqlstmt.append ( " and dc =?");
		  tempval.append(", dc");
		  }

		  if ( env != null) {
		  sqlstmt.append ( " and env =?");
		  tempval.append(", env");
		  }

		  if ( instancename != null) {
		  sqlstmt.append ( " and instancename =?");
		  tempval.append(", instancename");
		  }


		  System.out.println(sqlstmt);
		  System.out.println(tempval);
		

        
			String query = "Select * from dbinv where ( database = ? or database=null) AND (server = ?) AND ( node = ?) AND (dc = ?) AND (env = ?) AND (instancename = ?)" ;
		List<Dbinv> dbinvs = template.query(sqlstmt.toString(),new Object[]{tempval.toString()},new BeanPropertyRowMapper<>(Dbinv.class));

        return dbinvs;

		
		        }



}

Please note when I hard code the same parameters for test case its working without any errors

Also can anyone suggest option where I can skip using "new Object[]" in my query section.

Answer 1

JpaRepository option in springboot, solved my problem, I was able to create custom findby option based on my requirement.