Thread: Retrieving a result by a user's different combination's using StringTokenizer (MYSQL)

Hi, I am creating a simple Symptom Checker application. The problem I have is that I'm trying to retrieve user input (JTextField) by comma's using StringTokenizer which contacts the database for a result which matches the user's input (SELECT * FROM DIAGNOSIS WHERE ?, ?, ?) . It successfully finds the correct result however only in a particular format. Not different combinations....

for example, if I enter say within the JTextField: "tearful, nausea, lack of motivation" it will find the result successfully (as that is how it is formatted within the particular column (in the database table) i wish to display a result from) however, if i enter a different combination of these symptoms: "nausea, lack of motivation, tearful" - it will not find any result. I'm very unsure how to make it work regardless of what is inputted first, second or last.

HERE IS THE CODE:

public void actionPerformed(ActionEvent e) {
 
    	        try {
 
    	        String abc = fieldsymp1.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 DIAGNOSIS WHERE SYMPTOM_1 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);
                   String s3 = rs.getString(4);
                   String s4 = rs.getString(5);
                   String s5 = rs.getString(6);
                   String s6 = rs.getString(7);
 
                   //Sets Records in TextFields.
                   field4.setText(s);
                   field5.setText(s1);
                   field6.setText(s2);
                   field7.setText(s3);
                   field8.setText(s4);
                   field9.setText(s5);
                   field10.setText(s6);
   	                } else {
   	                    JOptionPane.showMessageDialog(null, "No such input found");
   	                }
 
 
    	            //Create Exception Handler
    	        } catch (Exception ex) {
 
    	            System.out.println(ex);
    	        }
    	    }

Any help? Thank you.

Welcome to the forum! Thanks for taking the time to learn how to post code correctly. If you haven't already, please read this topic to learn other useful info for new members.

You could collect all data in the JTextField and add the comma delimited entries to a String[] array using String.split():

String[] complaints = textField.getText().split( "," );

Originally Posted by GregBrannon

Welcome to the forum! Thanks for taking the time to learn how to post code correctly. If you haven't already, please read this topic to learn other useful info for new members.

You could collect all data in the JTextField and add the comma delimited entries to a String[] array using String.split():

String[] complaints = textField.getText().split( "," );

Thank you mate.

Though, I'm not sure how to collect all the data in the textfield using an array?

Originally Posted by copeg

Your query as written relies on the variable abc, not subsets of it (eg the tokens). Thus it will only match in that order.



The above code does nothing with the variable returned by the method nextToken. My advice:
1) split the string on comma's as suggested above, and build your query from this array.
2) If you anticipate a variable number of comma delimed symptoms the you will need to build the query based upon the number entered.
3) I would recommend using the question mark form of the PreparedStatement, setting the String for each value as appropriate.
4) I would recommend normalizing the database - if I understand the data model correctly you should have a symptom table and a diagnosis table, connect them via a mapping table and query them all with a join.

Thank you! Sorry quite the novice, I'm just unsure what i'm storing within the array?

Originally Posted by copeg

The values the user input in the JTextField, with which your code searches the database for. So, in the case of "tearful, nausea, lack of motivation", the array will be
[0] = "tearful"
[1] = "nausea"
[2] = "lack of motivation"
These values are then the values used for each %value% clause in your SQL statement.

                try {
                         String[] symptoms = fieldsymp1.getText().split( ", " );
 
			symptoms[0] = "Tearful";
			symptoms[1] = "Lack of motivation";
			symptoms[2] = "Nausea";
 
    	     Class.forName("com.mysql.jdbc.Driver");
   	         Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/intelli_db", "root", "root");
   	         PreparedStatement st = con.prepareStatement("SELECT * FROM Diagnosis WHERE SYMPTOM_1 LIKE '" + symptoms[0] + "%' OR SYMPTOM_1 LIKE '" + symptoms[1] + "%' OR SYMPTOM_1 LIKE '" + symptoms[2] + "%'"); 
   	         st.setString(1, "'" + symptoms[0] + "%'");		
   	         st.setString(2, "'" + symptoms[1] + "%'");
   	         st.setString(3, "'" + symptoms[2] + "%'");

not picking up anything from the user input now. anything I've done incorrectly?