Solved

java derby database query using user inputted text from the textfield

Posted on 2016-09-22
7
24 Views
Last Modified: 2016-10-17
I have a java derby database, I can write to and read from the database.

I am having trouble:

Making it so that the text that the user enters into the text field, is then incorporated into the database query to determine the results displayed.

I TRIED it this way, the results were, if I click the search button, it will return the info/query into the "run" screen, **not** actually incorporating the user input into the query tho, I have to do that in the code, by replacing the abc to the number in the database.

Do I have to create some kind of command line argument? set the variable differently? Can I just replace the query info where the database info goes with a variable like how I tried in the upcoming example?

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        
            String abc = jTextField1.getText();
            String data = "jdbc:derby://localhost:1527/sample";
        try (
              Connection conn = DriverManager.getConnection(
              data, "app", "app");
              Statement st = conn.createStatement())   { 
          
          Class.forName("org.apache.derby.jdbc.ClientDriver");
          ResultSet rec = st.executeQuery(
                  "select ROW1, ROW2, ROW3, ROW4, ROW5 from APP.NAME1 "
                          + "where (ROW4 = 'abc')");
            while (rec.next())  {
              System.out.println("ROW1:\t"
              + rec.getString(1));
              System.out.println("ROW2:\t"  + rec.getString(2));
              System.out.println("ROW3:\t"  + rec.getString(3));
              System.out.println("ROW4:\t" + rec.getString(4));
              System.out.println("ROW5:\t"  + rec.getString(5));
              System.out.println();
          }
          st.close();
          
            } catch (SQLException s)  {
          System.out.println("SQL Error: " + s.toString()  + " "
                  + s.getErrorCode() + " " + s.getSQLState());
      } catch (Exception e) {
          System.out.println("Error: " + e.toString()
          + e.getMessage());
    }                                        
    }
    private void jTextField1ActionPerformed(java.awt.event.ActionEvent evt) {                                            
        
    }    

Open in new window

               

Thank you for the time!
0
Comment
Question by:Evan Redmond
  • 3
7 Comments
 
LVL 35

Accepted Solution

by:
mccarl earned 500 total points
ID: 41811899
The seemingly easiest way is to just replace that part of the query with the variable, HOWEVER, that is not considered best practise. Most DB's can cache the SQL that you send and then just replace particular values each time you call, and it can help the DB save a reasonable amount of processing.

So, the way to do this is to use a PreparedStatement instead of the plain old vanilla Statement. Check out these small changes to your code...

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
            String sqlQuery = "select ROW1, ROW2, ROW3, ROW4, ROW5 from APP.NAME1 "
                          + "where (ROW4 = ?)";

            String abc = jTextField1.getText();
            String data = "jdbc:derby://localhost:1527/sample";
        try (
              Connection conn = DriverManager.getConnection(
              data, "app", "app");
              PreparedStatement st = conn.prepareStatement(sqlQuery))   { 
          
          Class.forName("org.apache.derby.jdbc.ClientDriver");
          st.setString(1, abc);
          ResultSet rec = st.executeQuery();
            while (rec.next())  {
              System.out.println("ROW1:\t"
              + rec.getString(1));
              System.out.println("ROW2:\t"  + rec.getString(2));
              System.out.println("ROW3:\t"  + rec.getString(3));
              System.out.println("ROW4:\t" + rec.getString(4));
              System.out.println("ROW5:\t"  + rec.getString(5));
              System.out.println();
          }
          st.close();
          
            } catch (SQLException s)  {
          System.out.println("SQL Error: " + s.toString()  + " "
                  + s.getErrorCode() + " " + s.getSQLState());
      } catch (Exception e) {
          System.out.println("Error: " + e.toString()
          + e.getMessage());
    }                                        
    }
    private void jTextField1ActionPerformed(java.awt.event.ActionEvent evt) {                                            
        
    }    

Open in new window


Note the following about the code...

Lines 2,3 :  Moved the SQL out into it's own variable just for clarity. Also, notice the use of the ? character as a placeholder for the parameter that you will supply to the query later.

Line 10 :  Here we are creating a PreparedStatement instead of a Statement. In this case, you have to provide the SQL query at creation time.

Line 13 :  This is where you actually tell the DB driver what value to supply for the 1st ? parameter that appears in your SQL query. Because you supply the index of the parameter, this means that you can have many ?'s in your query and you replace each one with a value from your code. There are various st.set..... methods for setting different type of values.

Line 14 :  Now you can execute your query, but because you have already supplied both the SQL text and the parameter values, there are no arguments to this call.



The other idea about these PreparedStatements are that they are reusable. Once you have processed the results from that call, if needed you could go back and just set a different parameter value to replace the ? and just call st.executeQuery() again to run it again with different parameters. This is where the benefit further comes into play, but even just for a once of query, it is best to do things this way.


If you have any questions about the above, let us know!
0
 

Author Comment

by:Evan Redmond
ID: 41812735
It worked! I also made it so that it outputs the info to a few new textfields on the app. As far as displaying the database info for the user to see, I want to display text(either a few lines of text or a paragraph) and possibly an image. Do you happen to know the best swing component to use for that part of the app? text area?

Thanks for the help!
0
 
LVL 35

Expert Comment

by:mccarl
ID: 41813241
Yeah, you can use a JTextArea for the multiline text and there a number of ways to show images, but a JLabel can do it, or you can draw it onto a JPanel with a bit more code.

You're welcome!
0
 
LVL 35

Expert Comment

by:mccarl
ID: 41835734
@Evan,

You messaged and said you don't need any further assistance but did my posts help you achieve a working solution? It certainly sounded that way from your reply. If that's the case, the way to finalise the question is to accept the post(s) that helped as the "Accepted Solution" awarding the expert(s) that have helped with points. If you need help in doing this, you can use the Request Attention button near the top of this question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now