Sunday, May 1, 2011

Java Database Connectivity : Searching Data

How to search for data on a database through Java GUI. We will be using a list to search for data in a table according to its primary key and showing its details on the text-fields.
JDBC

Steps:-
1 Follow the 1-5 steps of Viewing Data in GUI.
2 In the design view,insert text-fields according to your database to show the details and a search button.
3 Insert a list swing control.Now do as directed to create a new list model - Right click on list » properties » click on the ellipsis button of model property » select Custom Code from the drop down menu » write new DefaultListModel () in the given space.
4 Under your package and before class starts write the following codes in the source tab.
  import java.sql.*;
 import javax.swing.JOptionPane;
 import javax.swing.DefaultListModel;
5 Write the coding on the button's actionPerformed event for the connectivity and for populating the data in the list.
DefaultListModel list=(DefaultListModel) jList1.getModel();
    String sql="Select * from library";
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con= (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/database1","root","");
        Statement stmt=con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()){
            String no=rs.getString("no");
            list.addElement(no);
        }
        jList1.setModel(list);
    }
    catch(Exception e) {
        JOptionPane.showMessageDialog(this, e.getMessage());
    }
6 Now write these codes on the list's mouseClicked event for making the data appear on text-fields when we select a row in the list.
try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con= (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/database1","root","");
        Statement stmt=con.createStatement();
        String num  = (String) jList1.getSelectedValue();
        String sql1 = "Select * from library where no = '" + (num) + "'";
        ResultSet rs = stmt.executeQuery(sql1);
            while (rs.next())
        {
            String no = rs.getString("NO");
            String title= rs.getString("TITLE");
            String auth= rs.getString("AUTHOR");
            jTextField1.setText(""+no);
            jTextField2.setText(""+title);
            jTextField3.setText(""+auth);
        }
    }
    catch(Exception e) {
        JOptionPane.showMessageDialog(this, e.getMessage());
    }
7 Change the codes in blue according to your database. Finally, run the file(Shift +F6)
Run View
Add some labels and uncheck the editable property of text-fields to make the design look better.
After searching the data, you can allow users to update or delete rows.
// watch a video tutorial for java database connectivity on the videos page

25 comments:

  1. Access denied for user 'Root'@localhost(using password:NO) yeh kya aur kyu hai?

    ReplyDelete
  2. @Anonymous(pls write ur name also)
    ☻check the coding again.
    its root and not Root
    ☻Write your database_name and not 'database1'
    ☻type the password in the last "" ...if u have set in MySQl

    ReplyDelete
  3. Iv Done All of this but i want to use a combo box list instead if just simple list what changes will i have to make, to this code, would really appreciate any feedback.

    Just Need to know the changes made on on step 5

    Thanks

    ReplyDelete
  4. @khanny
    DefaultComboBoxModel model=(DefaultComboBoxModel) jComboBox1.getModel();
    --------------------------------
    while(rs.next()){
    String no=rs.getString("no");
    model.addElement(no);
    }

    ReplyDelete
  5. Ok Thanks, Ive managed to do that,

    ReplyDelete
  6. Can you do a tutorial on how to apply a auto-complete function onto a jtextfield , from a row of SQL, if you can, or can you refer me to any links, i would really appreciate any feedback I've been trying for 5 days now no look.

    Thanks

    ReplyDelete
  7. @Khanny Sorry! I can't help u this time, I have never tried this in Java and I have no time because of my exams. I will surely look at it later.

    ReplyDelete
  8. great site thanks

    ReplyDelete
  9. hi, i want to make a query which will use select * from library WHERE date = txtdate.text()

    so the condition comes from a textbox. how do i do that?

    ReplyDelete
  10. @Anonymous for simplicity and for ensuring that user enters the correct date, use 3 text-fields differently for date-month-year.
    concat the 3 variables with '-' in between and then pass it through the sql query.

    ReplyDelete
  11. when i click the Search button several times, the data again show themselves again and again making the List bigger and bigger.

    Can i make it as a Refresh button for getting the data in the list.

    ReplyDelete
  12. when i click the Search button several times, the data again show themselves again and again making the List bigger and bigger.

    Can i make it as a Refresh button for getting the data in the list.

    PLEASE HELP !
    I need urgent help..
    pls .

    ReplyDelete
  13. @Anonymous

    list.clear();

    Add the above code BELOW the line DefaultListModel list=(DefaultListModel) jList1.getModel(); on the Search button.

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Hey guys this site is really good. It helped me when doing my project.

    ReplyDelete
  16. hello
    I have a problem at the third point.I`ve inserted a list swing control, but how can I create a new list model, I don`t understand? I need urgent help.

    ReplyDelete
  17. @nekken100 i don't why u are not able to do it, it's simple just Right-Click your list which u have dropped on the form. Now, go to properties and find the where model is written on the left-side. Click on the model and press ctrl+space, a dialog would open and now from the drop-down menu which is situated at top select custom-code. Finally, paste the code given in the article above.

    ReplyDelete
  18. I am using textfield(to search) instead of list.What should i do?

    ReplyDelete
  19. rohan tried it and it worked but the button's code when clicking it several times it displays alot of the same output

    ReplyDelete
  20. I need for my jcombobox and jtextfiel search?
    the idea is to seacrh using button by selecting jcombobox and typing on txtfield..eg. I select lastname in jcombobox then I type duanez...the result is all names lastname duanez will open on datagrid....can you help me everyone... more thanks

    ancie...adepalac@gmail.com

    ReplyDelete
  21. how to display data in database using jlable

    ReplyDelete
  22. can modified the search code to search like %, so upper case or lower case can search

    ReplyDelete
  23. hi i nid help, cant find the code to search a record in mysql using java swing

    ReplyDelete
  24. how to create a login using access 2013 in java netbeans

    ReplyDelete
  25. cant compile, am getting this error.

    Updating property file: C:\Users\Billions\Documents\NetBeansProjects\OJDBC\build\built-jar.properties
    Compiling 1 source file to C:\Users\Billions\Documents\NetBeansProjects\OJDBC\build\classes
    C:\Users\Billions\Documents\NetBeansProjects\OJDBC\src\oraclecon\Workers.java:372: error: cannot find symbol
    jList1.setModel(newDefaultListModel());
    symbol: method newDefaultListModel()
    location: class Workers
    Note: C:\Users\Billions\Documents\NetBeansProjects\OJDBC\src\oraclecon\Workers.java uses unchecked or unsafe operations.
    Note: Recompile with -Xlint:unchecked for details.
    1 error

    what do i do?

    ReplyDelete