5 Replies Latest reply: Sep 15, 2012 3:15 AM by 813991 RSS

    Problem with mysql jdbc query

    813991

      Hello

      I am a newbie working with database and java.

      I have written this code in order to execute and take the result column I want from the connection. The code is:

      public static ComboBoxModel BDquery(String sqlquery,String result_column){         Connection con =null;         String [] list=null;         ResultSet rs=null;         List objectlist = new ArrayList();         objectlist.clear();         PreparedStatement pst = null;         try {           con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:*******/***","root","");         } catch (SQLException ex) {             System.err.println(ex.getMessage());         }              int count=0;         try {                      pst=(PreparedStatement) con.prepareStatement(sqlquery);                  } catch (SQLException ex) {             System.err.println(ex.getMessage());         }         try {boolean flag=pst.execute(sqlquery);             while (flag){                 rs=pst.getResultSet();                 while(rs.next()){                                    objectlist.add(rs.getObject(result_column));                 count=count+1;                                                  }  System.err.println(count);             flag=pst.getMoreResults();             }             pst.close();             con.close();             rs.close();         } catch (SQLException ex) {             System.err.println(ex.getMessage());         }                              list=(String[]) objectlist.toArray(new String[objectlist.size()]);                return new javax.swing.DefaultComboBoxModel((objectlist.toArray()));     }

      The sqlquery is the query I want to execute and result_column the result column I want to get.
      Executing a simple

       Select * FROM table; 

      I get my results. But if I execute an

       SELECT column1 FROM table1 JOIN table2 WHERE table1.column1=table2.column2 AND table1.column2=" parameter" ; 

      then it doesnt return results.

        • 1. Re: Problem with mysql jdbc query
          rp0428
          >
          I get my results. But if I execute an

          SELECT column1
          FROM table1 JOIN table2
          WHERE table1.column1=table2.column2 AND table1.column2=" parameter" ;

          then it doesnt return results.
          >
          Well the query above is using double quotes and SQL requires single quotes so that query is invalid.

          And if you are attempting to use 'parameter' as a bind variable then your query and your code are both wrong.
          Placeholders for bind variables would be represented by a '?'. And in your code you would need to use one of the setXXX methods to set the value and you have nothing like that in your code.,

          See the examples in the JDBC Developer's guide
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/getsta.htm#sthref74
          The following example shows how to use a prepared statement to run INSERT operations that add two rows to the EMP table.
          // Prepare to insert new names in the EMP table
          PreparedStatement pstmt = null;
          try{
              pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");
          
              // Add LESLIE as employee number 1500
              pstmt.setInt (1, 1500);          // The first ? is for EMPNO
              pstmt.setString (2, "LESLIE");   // The second ? is for ENAME
              // Do the insertion
              pstmt.execute ();
          
              // Add MARSHA as employee number 507
              pstmt.setInt (1, 507);           // The first ? is for EMPNO
              pstmt.setString (2, "MARSHA");   // The second ? is for ENAME
              // Do the insertion
              pstmt.execute ();
          }
          
          finally{
                          if(pstmt!=null)
          
              // Close the statement
              pstmt.close();
          }
          • 2. Re: Problem with mysql jdbc query
            813991
            Sorry the specific query was wrong indeed. I had written it in a hurry.
            The actual code I in put is a string like:
             
            "SELECT column1 "
            +"FROM table1 JOIN table2 "
            +"WHERE table1.column1=table2.column2 AND table1.column2= '"+parameter+"' ;" 
            the insert paradigm is very helpful. thanks!

            Edited by: 810988 on Sep 13, 2012 11:49 PM
            • 3. Re: Problem with mysql jdbc query
              rp0428
              >
              The actual code I in put is a string like:

              "SELECT column1 "
              +"FROM table1 JOIN table2 "
              +"WHERE table1.column1=table2.column2 AND table1.column2= '"+parameter+"' ;"
              >
              With that query you will not get ANY rows if the WHERE conditions are not met.

              Maybe table1.column2 has no records with a value that matches the contents of 'parameter'. The value has to match EXACTLY including upper/lower case and all characters.

              Or you could be joining the two tables on the wrong column and there are no table1.column1 values that match table2.column2.
              • 4. Re: Problem with mysql jdbc query
                813991
                I fixed the problem. I had created my database using tables names in English dictionary but my data where Greek characters. Calling a
                Select * from table1 
                caused no exceptions or errors. But using Greek in the connection statement I established caused the null returns. I needed to use parameter
                localhost:port/database?useUnicode=true&characterEncoding=utf-8
                in order characters to be recognized. I was using command line to test my querys first and then pass them to my code.
                Thank you for your time and help
                • 5. Re: Problem with mysql jdbc query
                  813991
                  Using English table names and Greek data or vice versa casused 'malfunction' on the connection statement.
                  localhost:port/database?useUnicode=true&characterEncoding=utf-8
                  solved the problem