Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 8 of 8

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

  1. #1
    Junior Member
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default 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.


  2. #2
    Super Moderator
    Join Date
    Jun 2013
    Location
    So. Maryland, USA
    Posts
    5,520
    My Mood
    Mellow
    Thanks
    215
    Thanked 698 Times in 680 Posts

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

    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( "," );

  3. The Following User Says Thank You to GregBrannon For This Useful Post:

    lyzt (March 30th, 2014)

  4. #3
    Junior Member
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    Quote Originally Posted by GregBrannon View Post
    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?

  5. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

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

    PreparedStatement st1 = con.prepareStatement("SELECT * FROM DIAGNOSIS WHERE SYMPTOM_1 LIKE '%" + abc + "%' OR '%" + abc + "%' OR '%" + abc + "%' OR '%" + abc + "%'");
    Your query as written relies on the variable abc, not subsets of it (eg the tokens). Thus it will only match in that order.


    String abc = fieldsymp1.getText();

    StringTokenizer str = new StringTokenizer(abc);


    while (str.hasMoreTokens()) {

    str.nextToken((", ")).trim();

    }
    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.

  6. #5
    Junior Member
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    Quote Originally Posted by copeg View Post
    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?

  7. #6
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

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

    Quote Originally Posted by lyzt View Post
    Thank you! Sorry quite the novice, I'm just unsure what i'm storing within the array?
    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.

  8. The Following User Says Thank You to copeg For This Useful Post:

    lyzt (March 30th, 2014)

  9. #7
    Junior Member
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    Quote Originally Posted by copeg View Post
    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?

  10. #8
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

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

    I recommend reading the following tutorial for how to use a PreparedStatment.
    Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)
    You are not using placeholders and are including additional information not relevant within the setString arguments.

Similar Threads

  1. Replies: 5
    Last Post: December 15th, 2013, 01:43 AM
  2. Using session atttributes and DAO class to delete mySql user records
    By jaltaie in forum JavaServer Pages: JSP & JSTL
    Replies: 13
    Last Post: September 4th, 2013, 01:39 AM
  3. jdbc integration with Gui to show user information retrieved from mysql
    By harem_najat in forum What's Wrong With My Code?
    Replies: 1
    Last Post: May 22nd, 2013, 07:25 PM
  4. Updating MySQL with user entered values
    By Talksin in forum JDBC & Databases
    Replies: 0
    Last Post: May 16th, 2013, 10:13 AM
  5. getobject error retrieving data from mysql
    By zeberrun in forum Other Programming Languages
    Replies: 1
    Last Post: September 19th, 2011, 05:27 AM

Tags for this Thread