如何使用PreparedStatement和不区分大小写(How to use PreparedSt

1.How我用PrepareStatement用于familyname和给定名称？
2.Also，我怎么通过familyname不区分大小写或给定名称？

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();

Answer 1:

让familyName或给定名称为小写过，因为你已经在使用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";

当您使用PreparedStatement不直接在SQL附加价值，如果你这样做你很容易SQL攻击 ，而不是你的参数化值。

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());

然后设置值调用适当的setXXX方法定义在这里 。

你可以阅读教程在这里

Answer 2:

您可以使用caseinsensetive搜索下面的查询。

String query = 
                "SELECT agent.familyname, agent.givenname"
                + " FROM agent"
                + " WHERE agent.agentid = ?"
                + " AND (" 
                + " familyname ilike ? OR givenname ilike ?" 
                + ") " 
                + " ORDER by familyname";


        pst.setInt(1, piececreation.agentid);
        pst.setString(2, agent_lastname.toLowerCase());
        pst.setString(3, agent_name.toLowerCase());

文章来源: How to use PreparedStatement and Case INsensitive search