Java fuzzy query method

When we need to develop a method to query the database, we often encounter such a problem: we do not know what conditions the user will enter, so how to handle the sql statement so that the method we develop can work normally no matter what conditions are accepted? where '1'='1' plus list is the perfect solution to this problem.

//  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's the trick: concatenate strings properly 
        + "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();
      // SQL When the statement composition is complete, it is generated pst object 
      PreparedStatement pst = con.prepareStatement(sql);
      //  Set up the ? The value of the 
      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;
  }

Comments:
1. The above code operates 1 Oracle database:

create table students(
id varchar(32),
name varchar(30),
tel varcher(15),
sex char(1),
constraint stud_pk primary key(id)
);

2. Get Connection using the tools class
3. proson is an javabean

Let me show you how to use Java to do fuzzy query results

import java.io.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.filechooser.*;
import java.util.*;
import java.util.regex.*;

 

// Fuzzy 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(" The song name of the query :");
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 the songs ");
btn.addActionListener(this);

btn1=new JButton(" Delete the song ");
btn1.addActionListener(this);

btn2=new JButton(" Empty the list ");
btn2.addActionListener(this);

btn3=new JButton(" To find the music ");
btn3.addActionListener(this);

btn4=new JButton(" The 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());

// Capture the last 1 a "/" All of the previous strings 

 

int str3=name1.lastIndexOf(".");

String name2=name1.substring(0,str3);

// The interception "." All the string suffixes that follow 

 

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+"+");// The regular expression selects all query results starting with the word you filled in 
Matcher match=p.matcher((String)hashtable.get(j));

if (match.find())
{
s++;

 

// Record index nodes into an array a[] In the 
a[s]=a[s]+m.getSize()-j;
System.out.println(hashtable.get(j));
System.out.println(a[s]);
}

}

 

// You can choose more than one option ( Because it was set up earlier JList Multiple choice )

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 whether you have a definite understanding of java fuzzy query method, I hope you like this site article, continue to pay attention to oh!