When we need to develop a method to query the database, we often encounter a problem: we don’t know what conditions the user will enter, so how can we handle SQL statements so that the methods we develop can work normally no matter what conditions they accept? At this time, where '1'='1' plus list can perfectly solve this problem. Less nonsense, add the code:
// Fuzzy query method public List<person> query() { List<person> list = new ArrayList<>(); Connection con = null; Scanner sc = new Scanner(System.in); System.err.println("enter name:"); String name = sc.nextLine(); System.err.println("enter id:"); String id = sc.nextLine(); System.err.println("enter tel:"); String tel = sc.nextLine(); System.err.println("enter sex:"); String sex = sc.nextLine(); String sql = "select id,name,tel,sex from students " // Here is the trick, reasonably splicing string + "where 1=1"; List<Object> list1 = new ArrayList<Object>(); //Use commons-lang package if (StringUtils.isNotEmpty(name)) { sql += " and title like ?"; list1.add("%" + name + "%"); } if (!StringUtils.isEmpty(id)) { sql += " and content like ?"; list1.add("%" + id + "%"); } if (!StringUtils.isEmpty(tel)) { sql += " and addr like ?"; list1.add("%" + tel + "%"); } try { con = DSUtlis.getConnection(); // After the SQL statement is composed, a pst object is generated. PreparedStatement pst = con.prepareStatement(sql); // Set the value of ? for (int i = 0; i < list1.size(); i++) { pst.setObject(i + 1, list.get(i)); } ResultSet rs = pst.executeQuery(); while (rs.next()) { person p = new person(); p.setId(rs.getString("id")); p.setName(rs.getString("name")); p.setTel(rs.getString("tel")); p.setSex(rs.getString("sex").equals("1") ? "Male" : "Female"); list.add(p); } rs.close(); pst.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } annotation:
1. The above code operates on an Oracle database:
create table students(id varchar(32), name varchar(30), tel varcher(15), sex char(1), constraint stud_pk primary key(id));
2. Use tool classes to get Connection
3. Proson is a javabean
Below is a guide to how to use Java to make fuzzy query results
import java.io.*;import java.awt.*;import java.awt.event.*;import javax.swing.event.*;import javax.swing.event.*;import javax.swing.filechooser.*;import java.util.*;import java.util.regex.*; //Flurry query public class Media{public static void main(String args[]){JFrame frame=new MediaFrame();frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);frame.setVisible(true);}}class MediaFrame extends JFrame implements ActionListener,ListSelectionListener{private JList list;private DefaultListModel m;private JButton btn;private JButton btn1;private JButton btn2;private JButton btn3;private JButton btn4;private JFileChooser chooser;private JTextField textField;Map hashtable=new Hashtable();private int i=0;int s=0;public MediaFrame(){setTitle("Media");setSize(600,500);JMenuBar menu=new JMenuBar();setJMenuBar(menu);JLabel label=new JLabel("Query song name:");textField=new JTextField();menu.add(label);menu.add(textField);JToolBar TB=new JToolBar();m=new DefaultListModel();list=new JList(m);list.setFixedCellWidth(100);list.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);list.addListSelectionListener(this);JScrollPane pane=new JScrollPane(list);chooser=new JFileChooser();btn=new JButton("Add song");btn.addActionListener(this);btn1=new JButton("Delete Song");btn1.addActionListener(this);btn2=new JButton("Clear List");btn2.addActionListener(this);btn3=new JButton("Look for track");btn3.addActionListener(this);btn4=new JButton("Sorting");btn4.addActionListener(this);JPanel panel=new JPanel();panel.setLayout(new GridLayout(5,1));panel.add(btn);panel.add(btn1);panel.add(btn2);panel.add(btn3);panel.add(btn4);TB.setLayout(new GridLayout(1,2));TB.add(pane);TB.add(panel);add(TB,BorderLayout.WEST);}public void actionPerformed(ActionEvent event){if (event.getSource()==btn){i++;chooser.setCurrentDirectory(new File("."));int result=chooser.showOpenDialog(MediaFrame.this);if (result==JFileChooser.APPROVE_OPTION){System.out.println(i);String name=chooser.getSelectedFile().getPath();String str1=name;int str2=name.lastIndexOf("//");String name1=name.substring(str2+1,str1.length());//Intercept all strings before the last "/" int str3=name1.lastIndexOf(".");String name2=name1.substring(0,str3);//Intercept all string suffixes after "." hashtable.put(i,name2);m.add(0,hashtable.get(i));System.out.println(hashtable);}}if (event.getSource()==btn1){m.removeElement(list.getSelectedValue());System.out.println(m);}if (event.getSource()==btn2){System.out.println(m);i=0;hashtable.clear();m.clear();}if (event.getSource()==btn3){int [] a=new int[m.getSize()];try{int j;String name=textField.getText();System.out.println(m.getSize());for (j=1;j<=m.getSize();j++){Pattern p=Pattern.compile("^"+name+"+");// Regular expression selects all query results headed by the word you fill in Matcher match=p.matcher((String)hashtable.get(j));if (match.find()){s++; //Record the index node into the array a[] a[s]=a[s]+m.getSize()-j;System.out.println(hashtable.get(j));System.out.println(a[s]);}} //You can select no multiple options (because JList is set before, you can choose multiple options) list.setSelectedIndices(a);}catch (Exception e){}}if (event.getSource()==btn4){//int j;//for (j=0;j<m.length();j++)//{//if (hashtable.containsValue(Integer.parseInt(j)+"*")//hashtable.put(j,//}}} public void valueChanged(ListSelectionEvent event){System.out.println(list.getSelectedIndex());}}Through these two examples, do you have a certain understanding of Java fuzzy query methods? I hope you like the editor’s article and continue to follow it!