0

I use java jdbc to connect database sql server but the question mark inside %% not working

This is my code:

public List<Account> findBetween(String search, int start, int size) {
    try {
        ArrayList<Account> list = new ArrayList<Account>();
        String sql = "SELECT * FROM Account WHERE uname LIKE '%?%' ORDER BY user_id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        Connection conn = ConnectDB.openConnection();
        PreparedStatement st = conn.prepareStatement(sql);
        st.setNString(1, search);
        st.setInt(2, start);
        st.setInt(3, size);
        ResultSet rs = st.executeQuery();
        System.out.println(rs.getFetchSize());
        while (rs.next()) {
            list.add(new Account(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getNString(4), rs.getNString(5),
                    rs.getNString(6), rs.getString(7), rs.getString(8), rs.getDate(9), rs.getInt(10),
                    rs.getBoolean(11)));
        }
        ConnectDB.closeConnection();
        return list;
    } catch (Exception e) {
        e.printStackTrace();
        ConnectDB.closeConnection();
        return null;
    }           
}
robocon321
  • 363
  • 5
  • 8
  • 1
    I can't find a good source for this, but basically a prepared statement parameter needs to be an entire value, so you'll need to use `LIKE ?` and add the `%` characters to `search`. – tgdavies Jan 03 '21 at 02:20

1 Answers1

1

Bind variables do not work inside of string literals.

You have to either bind the whole string

uname LIKE ?
st.setNString(1, "%" + search + "%")

or use string concatenation

uname LIKE '%' || ? || '%'
set.setNString(1, search)

Note that in both cases, any additional wildcards contained in search will also be effective, so if this is direct user input, you may want to sanitise it first.

Thilo
  • 257,207
  • 101
  • 511
  • 656