executeQuery in postgresql using netbeans?

Question

I have a basic code but its not working. i don't know why. I checked throughout the code by printing after each line but it seems like executequery is giving me hard time. Need help from experts please..

{

    package com.pack.database.userinformation;

    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;

    /**
     *
     * @author Shokouh
     */
    class UserDAO {

         static Connection currentCon = null;
          static ResultSet rs = null;  



          public static SurveyData login(SurveyData bean) {

             //preparing some objects for connection 
             Statement stmt = null;    

             String username = bean.getUserName();    
             String password = bean.getPassword();   

             String searchQuery =
                   "SELECT * FROM userinfo WHERE username='"
                            + username
                            + "' AND password='"
                            + password
                            + "'";

          // "System.out.println" prints in the console; Normally used to trace the process
          System.out.println("Your user name is " + username);          
          System.out.println("Your password is " + password);
          System.out.println("Query: "+searchQuery);

          try 
          {
              System.out.println("testssssssssssssssssssssssss");
             //connect to DB 
             currentCon = ConnectionManager.getConnection();
             stmt=currentCon.createStatement();
              System.out.println("1111111111111111111111");
             rs = stmt.executeQuery(searchQuery);       
             System.out.println("kkkkkkkkkkkkkkkkkkk");
             boolean more = rs.next();
              System.out.println("222222222222222222");

             // if user does not exist set the isValid variable to false
             if (!more) 
             {
                System.out.println("Sorry, you are not a registered user! Please sign up first");
                bean.setValid(false);
             } 

             //if user exists set the isValid variable to true
             else if (more) 
             {

                String firstName = rs.getString("username");
                String lastName = rs.getString("lastname");

                System.out.println("Welcome " + firstName);
                bean.setFirstName(firstName);
                bean.setLastName(lastName);
                bean.setValid(true);
             }
          } 

          catch (Exception ex) 
          {
             System.out.println("Log In failed: An Exception has occurred! " + ex);
          } 

          //some exception handling
          finally 
          {
             if (rs != null)    {
                try {
                   rs.close();
                } catch (Exception e) {}
                   rs = null;
                }

             if (stmt != null) {
                try {
                   stmt.close();
                } catch (Exception e) {}
                   stmt = null;
                }

             if (currentCon != null) {
                try {
                   currentCon.close();
                } catch (Exception e) {
                }

                currentCon = null;
             }
          }

    return bean;

          }


    }

}

The output is:

{

        NFO:   test was successfully deployed in 1,146 milliseconds.
        INFO:   Your user name is m.rezai
        INFO:   Your password is mina123
        INFO:   Query: SELECT * FROM userinfo WHERE username='m.rezai' AND password='mina123'
    INFO:   testssssssssssssssssssssssss
    INFO:   1111111111111111111111
    INFO:   kkkkkkkkkkkkkkkkkkk
    INFO:   222222222222222222
    INFO:   Sorry, you are not a registered user! Please sign up first

}

Thank for your help :-) I should create a table with small word in postgresql! — Shokouh Dareshiri, Jul 29 '15 at 03:56
Now its run until '{System.out.println("222222222222222222");}' but there is error in more.. can u know whats the problem here? — Shokouh Dareshiri, Jul 29 '15 at 03:58
@ShokouhDareshiri by seeing your output your not getting any errors, your code executes fine but no data coming from the database, try running your query in your database and check with result. Any exception while executing your java code post full stacktrace so that we can help you in a better way. — Srinu Chinka, Jul 29 '15 at 04:53
You are wide open to SQL injection. Please use a `PreparedStatement` instead. — , Jul 29 '15 at 05:50

score 0 · Answer 1 · answered Jul 28 '15 at 21:36

0

 String searchQuery =
           "SELECT * FROM [SchemaNameGoesHere].Users WHERE username='"
                    + username
                    + "' AND password='"
                    + password
                    + "'";

answered Jul 28 '15 at 21:36

FirebladeDan

1,069
6
14

`[SchemaNameGoesHere].` is invalid standard SQL and won't work in Postgres. SQL requires identifiers to be quoted using double quotes (which _then_ makes the identifier case sensitive) – Jul 29 '15 at 05:49

score 0 · Answer 2 · answered Jul 29 '15 at 07:19

0

Actually, the problem was in the name of tables with capital letters! It seems the Postgres is case sensitive. So, i create the new table with all small letters. It worked :-)

String searchQuery =
           "SELECT * FROM users WHERE username='"
                    + username
                    + "' AND password='"
                    + password
                    + "'";

answered Jul 29 '15 at 07:19

Shokouh Dareshiri

826
1
12
24

Postgres is **not** case sensitive for identifiers unless you put them in double quotes. `SELECT * FROM users` is the same as `SELECT * FROM USERS` or `SELECT * FROM Users`. Please read the manual for a detailed explanation: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS String comparison on the other hand _is_ case sensitive. – Jul 31 '15 at 05:57

executeQuery in postgresql using netbeans?

2 Answers2