1.How do I use PrepareStatement for familyname and givenname?
2.Also, how do I case insensitive search by familyname or givenname?
String query ="SELECT agent.familyname, agent.givenname" +
" FROM agent" +
" WHERE agent.agentid = piececreation.agentid" +
" AND (LOWER(familyname) = '"+agent_lastname+"' OR LOWER(givenname) = '"+agent_name+"') ORDER by familyname";
PreparedStatement pst = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
pst.setString(1, agent_lastname);
pst.setString(2, agent_name);
// Executing the insert
pst.executeUpdate();
Make familyName or givenName to lowercase too since you are already using LOWER DB API
String query ="SELECT agent.familyname, agent.givenname" +
" FROM agent" +
" WHERE agent.agentid = piececreation.agentid" +
" AND (LOWER(familyname) = '"+agent_lastname.toLowerCase()+"' OR LOWER(givenname) = '"+agent_name.toLowerCase()+"') ORDER by familyname";
When you are using PreparedStatement
dont append values directly in your SQL, if you do that you are prone to SQL Attack instead parametrize your values.
String query =
"SELECT agent.familyname, agent.givenname"
+ " FROM agent"
+ " WHERE agent.agentid = ?"
+ " AND ("
+ " LOWER(familyname) = ? OR LOWER(givenname) = ?"
+ ") "
+ " ORDER by familyname";
pst.setInt(1, piececreation.agentid);
pst.setString(2, agent_lastname.toLowerCase());
pst.setString(3, agent_name.toLowerCase());
Then set values calling appropriate setXXX methods as defined here.
You can read tutorial here