我正在从JTextField中检索用户输入，例如“茶，咖啡，拿铁”，然后将以下输入查询到包含数据库中的字符串“tea，coffee，latte”的特定结果：
public void actionPerformed(ActionEvent e) { try { String abc = field.getText(); StringTokenizer str = new StringTokenizer(abc); while (str.hasMoreTokens()) { str.nextToken((", ")).trim(); } Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/intelli_db", "root", "root"); PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS LIKE '%" + abc + "%' OR '%" + abc + "%' OR '%" + abc + "%' OR '%" + abc + "%'"); ResultSet rs = st1.executeQuery(); if (rs.next()) { String s = rs.getString(1); String s1 = rs.getString(2); String s2 = rs.getString(3); //Sets Records in TextFields. field6.setText(s2); } else { JOptionPane.showMessageDialog(null, "No such input found"); } //Create Exception Handler } catch (Exception ex) { System.out.println(ex); } }但是，我希望它能找到这些字符串的任何组合的结果，所以“咖啡，拿铁，茶”也会产生与“茶，咖啡，拿铁咖啡”相同的结果。
数据库结构：
Menu_ID -------- 1 Drinks ------- tea, coffee, latte Hot_Drinks_Description ------------------------ There are various hot drinks available, including Earl Grey tea, white or black coffee or a Latte.因此，如果用户输入茶，咖啡或拿铁或其组合（用逗号分隔），结果将显示可用热饮的描述。
I'm retrieving user input such as "tea, coffee, latte" from a JTextField, the following input is then queried to a particular result that includes the strings "tea, coffee, latte" within the database:
public void actionPerformed(ActionEvent e) { try { String abc = field.getText(); StringTokenizer str = new StringTokenizer(abc); while (str.hasMoreTokens()) { str.nextToken((", ")).trim(); } Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/intelli_db", "root", "root"); PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS LIKE '%" + abc + "%' OR '%" + abc + "%' OR '%" + abc + "%' OR '%" + abc + "%'"); ResultSet rs = st1.executeQuery(); if (rs.next()) { String s = rs.getString(1); String s1 = rs.getString(2); String s2 = rs.getString(3); //Sets Records in TextFields. field6.setText(s2); } else { JOptionPane.showMessageDialog(null, "No such input found"); } //Create Exception Handler } catch (Exception ex) { System.out.println(ex); } }however, I wish it to find the result of any combination of these strings, so "coffee, latte, tea" would also give the same result as ""tea, coffee, latte" would.
The database structure:
Menu_ID -------- 1 Drinks ------- tea, coffee, latte Hot_Drinks_Description ------------------------ There are various hot drinks available, including Earl Grey tea, white or black coffee or a Latte.Thus if the user types in either, tea or coffee or latte or a combination - separated by comma's, the result would display the description of the hot drinks available.
最满意答案
看来这个问题有点乱。 这似乎是这样的情况：
您有一个用户输入List 。 您有一个数据库字段，它本身就是逗号分隔列表。
您需要查看数据库中是否存在与输入的其中一个排列匹配的排列。
首先是你的查询。 从这个答案来看， 这是如何使用IN子句创建预准备语句。
PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS IN (?)"); Array array = con.createArrayOf("VARCHAR", stringPermutations); st1.setArray(1, array);这基本上会查询您的数据库，其中DRINKS是IN 。
现在开始创建stringPermutations 。 这需要是逗号分隔排列的Object[] - 以匹配数据库。
首先，获取用户输入的Set<String> ：这样的事情应该做：
public Set<String> getInputs(final JTextComponent component) { final Set<String> inputs = new LinkedHashSet<>(); for (final String input : component.getText().split("\\s+")) { inputs.add(input); } return inputs; }在Java 8中：
public Set<String> getInputs(final JTextComponent component) { return Stream.of(component.getText().split("\\s+")). collect(Collectors.toSet()); }现在，您需要置换输入以获得所有可能的组合。 你会有n! 他们 - 这很多 ：
public List<Set<String>> permutations(final Set<String> input) { if (input.size() == 1) { return Arrays.asList(input); } final List<Set<String>> output = new ArrayList<>(); final Iterator<String> iter = input.iterator(); while (iter.hasNext()) { final String item = iter.next(); final Set<String> copy = new LinkedHashSet<>(input); copy.remove(item); for (final Set<String> rest : permutations(copy)) { rest.add(item); output.add(rest); } } return output; }这是一个递归算法，它非常标准。 输出是这样的：
[D, C, B, A] [C, D, B, A] [D, B, C, A] [B, D, C, A] [C, B, D, A] [B, C, D, A] [D, C, A, B] [C, D, A, B] [D, A, C, B] [A, D, C, B] [C, A, D, B] [A, C, D, B] [D, B, A, C] [B, D, A, C] [D, A, B, C] [A, D, B, C] [B, A, D, C] [A, B, D, C] [C, B, A, D] [B, C, A, D] [C, A, B, D] [A, C, B, D] [B, A, C, D] [A, B, C, D]所以，对于4输入你得到4! = 24 4! = 24个排列。 现在我们需要将这些单独的项加入逗号分隔列表：
public List<String> combine(final List<Set<String>> toJoin) { final List<String> outputs = new ArrayList<>(toJoin.size()); for (final Set<String> items : toJoin) { outputs.add(join(items)); } return outputs; } private String join(final Set<String> items) { final Iterator<String> iter = items.iterator(); final StringBuilder sb = new StringBuilder(); sb.append(iter.next()); while (iter.hasNext()) { sb.append(", ").append(iter.next()); } return sb.toString(); }再次，在Java 8中更整洁：
public List<String> combine(final List<Set<String>> toJoin) { return toJoin.stream(). map(items -> items.stream().collect(Collectors.joining(", "))). collect(Collectors.toList()); }最后，我们可以获得PeparedStatement的Object[]输入：
final Object[] stringPermutations = combine(permutations(getInputs(field))).toArray(); PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS IN (?)"); Array array = con.createArrayOf("VARCHAR", stringPermutations); st1.setArray(1, array);还应该注意，您应该关闭资源 ，可以使用try-with-resources构造：
final Object[] stringPermutations = combine(permutations(getInputs(field))).toArray(); try (final Connection con = getConnection()) { try (final PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS IN (?)")) { Array array = con.createArrayOf("VARCHAR", stringPermutations); st1.setArray(1, array); try (final ResultSet rs = st1.executeQuery()) { //do stufd } } }It seems that the problem is a little messy. This seems to be the situation:
You have a List of user inputs. You have have a database field that is, itself, as comma separated list.
You need to see if there is a permutation in the database that matches one of the permutations of your input.
First your query. From this answer this is how to create a prepared statement with an IN clause.
PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS IN (?)"); Array array = con.createArrayOf("VARCHAR", stringPermutations); st1.setArray(1, array);This essentially queries your database where DRINKS is one of IN.
Now onto creating the stringPermutations. This needs to be an Object[] of the comma separated permutations - to match the database.
First, get a Set<String> of the user inputs: something like this ought to do:
public Set<String> getInputs(final JTextComponent component) { final Set<String> inputs = new LinkedHashSet<>(); for (final String input : component.getText().split("\\s+")) { inputs.add(input); } return inputs; }In Java 8:
public Set<String> getInputs(final JTextComponent component) { return Stream.of(component.getText().split("\\s+")). collect(Collectors.toSet()); }Now you need to permute the inputs to get all possible combinations. You will have n! of them - this is many:
public List<Set<String>> permutations(final Set<String> input) { if (input.size() == 1) { return Arrays.asList(input); } final List<Set<String>> output = new ArrayList<>(); final Iterator<String> iter = input.iterator(); while (iter.hasNext()) { final String item = iter.next(); final Set<String> copy = new LinkedHashSet<>(input); copy.remove(item); for (final Set<String> rest : permutations(copy)) { rest.add(item); output.add(rest); } } return output; }This is a recursive algorithm, it's pretty standard. The output is something like this:
[D, C, B, A] [C, D, B, A] [D, B, C, A] [B, D, C, A] [C, B, D, A] [B, C, D, A] [D, C, A, B] [C, D, A, B] [D, A, C, B] [A, D, C, B] [C, A, D, B] [A, C, D, B] [D, B, A, C] [B, D, A, C] [D, A, B, C] [A, D, B, C] [B, A, D, C] [A, B, D, C] [C, B, A, D] [B, C, A, D] [C, A, B, D] [A, C, B, D] [B, A, C, D] [A, B, C, D]So, for 4 input you get 4! = 24 permutations. Now we need to join those individual items into comma separated lists:
public List<String> combine(final List<Set<String>> toJoin) { final List<String> outputs = new ArrayList<>(toJoin.size()); for (final Set<String> items : toJoin) { outputs.add(join(items)); } return outputs; } private String join(final Set<String> items) { final Iterator<String> iter = items.iterator(); final StringBuilder sb = new StringBuilder(); sb.append(iter.next()); while (iter.hasNext()) { sb.append(", ").append(iter.next()); } return sb.toString(); }Again, tidier in Java 8:
public List<String> combine(final List<Set<String>> toJoin) { return toJoin.stream(). map(items -> items.stream().collect(Collectors.joining(", "))). collect(Collectors.toList()); }Finally we can get the Object[] input to the PeparedStatement:
final Object[] stringPermutations = combine(permutations(getInputs(field))).toArray(); PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS IN (?)"); Array array = con.createArrayOf("VARCHAR", stringPermutations); st1.setArray(1, array);It should further be noted that you should be closing your resources, you can use the try-with-resources construct:
final Object[] stringPermutations = combine(permutations(getInputs(field))).toArray(); try (final Connection con = getConnection()) { try (final PreparedStatement st1 = con.prepareStatement("SELECT * FROM SHOP WHERE DRINKS IN (?)")) { Array array = con.createArrayOf("VARCHAR", stringPermutations); st1.setArray(1, array); try (final ResultSet rs = st1.executeQuery()) { //do stufd } } }更多推荐
发布评论