How to insert sqlite DB and use dynamic search in JAVA GUI JTable Swing?

Question

I am using try catch block to filter the data from the sqlite database, i have two issues this program is case sensitive i need it to be non case sensitive, then it filter the data only when i write the exact name given in the database. Please help to make it a dynamic filter.

    textFieldSearch = new JTextField();
    textFieldSearch.addKeyListener(new KeyAdapter() {
        @Override
        public void keyReleased(KeyEvent arg0) {
            try {
            String query = "select sno,universityname,state,courses,applicationfees,deadline,admissionrequirment,undergrad,grad from UsaCollegeList where  state=? ";
            PreparedStatement pst = connection.prepareStatement(query);
            pst.setString(1, (String)textFieldSearch.getText());
            ResultSet rs=pst.executeQuery();

            table.setModel(DbUtils.resultSetToTableModel(rs));

            pst.close();

        } catch (Exception e) {
            // TODO: handle exception
            JOptionPane.showMessageDialog(null, "error");
            e.printStackTrace();
        }
    }
    });

you should use `state like ?` and not `state =?`. case sensitivity is more likely related to sqlite https://stackoverflow.com/questions/15480319/case-sensitive-and-insensitive-like-in-sqlite/15480401 — guleryuz, May 04 '18 at 12:19
can i use filter in all the column of the db instead of filtering only in the state? — udayan udaya, May 04 '18 at 12:32
`select sno,universityname,state,courses,applicationfees,deadline,admissionrequirment,undergrad,grad from UsaCollegeList where state like ? or sno like ? or universityname like ? or courses like ?` and so on — guleryuz, May 04 '18 at 12:39
only the first column state works rest didn't work, i tried to change the order even then only the first column works. ex: where state like ? or sno like? --> in which state gets filtered — udayan udaya, May 04 '18 at 12:51
Almost my project is compete only this last part of my project is taking days.. Is there any way i could use a Dynamic filter? — udayan udaya, May 04 '18 at 13:08

score 0 · Answer 1 · answered May 04 '18 at 12:23

0

You can compare it as follows:

        String query = "select sno,universityname,state,courses,applicationfees,deadline,admissionrequirment,undergrad,grad from UsaCollegeList where  upper(state)=? ";
        PreparedStatement pst = connection.prepareStatement(query);
        pst.setString(1, textFieldSearch.getText().toUpperCase());

answered May 04 '18 at 12:23

mistyk

84
3

Thanks for the correction in code, but i tried earlier using .toUpprCAse() but it didn't work. – udayan udaya May 04 '18 at 12:30
can i add more column to search like where upper (state,course)=?; – udayan udaya May 04 '18 at 12:40
Upper function has String as input, so you can use more columns either in one String (concatenation - || - for example) or in more conditions like `where upper(state) = ? and upper(course) = ?` – mistyk May 10 '18 at 11:50

score 0 · Answer 2 · answered May 04 '18 at 14:01

String text = "%" + textFieldSearch.getText() + "%";

String query = "select sno,universityname,state,courses,applicationfees,deadline,admissionrequirment,undergrad,grad from UsaCollegeList where sno = ? or universityname = ? or state = ? or courses = ? or applicationfees = ? or deadline = ? or admissionrequirment = ? or undergrad = ? or grad = ? ";
PreparedStatement pst = connection.prepareStatement(query);
pst.setString(1, text);
pst.setString(2, text);
pst.setString(3, text);
pst.setString(4, text);
pst.setString(5, text);
pst.setString(6, text);
pst.setString(7, text);
pst.setString(8, text);
pst.setString(9, text);
ResultSet rs=pst.executeQuery();

Thanks my friend it works, but the text needed to be converted to a string in 'pst.setString(1, (String)text);' — udayan udaya, May 05 '18 at 03:54

score 0 · Answer 3 · answered May 05 '18 at 03:59

Thanks my dear friends, everyone helped me a lot, i got my project done.

    textFieldSearch = new JTextField();
    textFieldSearch.addKeyListener(new KeyAdapter() {
        @Override
        public void keyReleased(KeyEvent arg0) {
            try {
                String text = "%" + textFieldSearch.getText() + "%";
                String query = "select sno,state from UsaCollegeList where sno like ? or state like ?";
                PreparedStatement pst = connection.prepareStatement(query);
                pst.setString(1, (String)text);
                pst.setString(2, (String)text);
                ResultSet rs=pst.executeQuery();

            table.setModel(DbUtils.resultSetToTableModel(rs));

            pst.close();

        } catch (Exception e) {
            // TODO: handle exception
            JOptionPane.showMessageDialog(null, "error");
            e.printStackTrace();
        }
    }
    });

How to insert sqlite DB and use dynamic search in JAVA GUI JTable Swing?

3 Answers3