5 Replies Latest reply: Mar 7, 2009 11:56 AM by 798635 RSS

    How to write a SQL query in Java

    801497
      Hi, I'm writing a program that pulls all data from the database with a simple "SELECT * FROM [TABLE NAME]. The problem is that I don't know how to set it up properly. My code will only connect to the database but not execute the query. Please help.



      {code}import java.sql.*;
      import java.util.Properties;
      import java.sql.*;

      public class DatabaseSelect {

      public static void main(String[] args) {
      System.out.println("Connected to the database!");
      Connection conn = null;
      String url = "jdbc:oracle://localhost:1571/";
      String dbName = "jdbc";
      String driver = "com.oracle.jdbc.Driver";
      String userName = "HR";
      String password = "database";
      String query = "SELECT * FROM HR";

      try {
      Class.forName(driver).newInstance();
      conn = DriverManager.getConnection(url+dbName,userName,password);
      System.out.println("Connected to the database");

      } catch (Exception e) {
      e.printStackTrace();
      }
      }
      }{code}
        • 1. Re: How to write a SQL query in Java
          843785
          Add these lines after your System.out

                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery(query);
          You will need to then get your data from the the ResultSet. Read here on how to do that
          • 2. Re: How to write a SQL query in Java
            801497
            I added it between my System.out statement and the catch exception. This is what I put, but I'm getting the same result. I looks like a lot of errors. The only thing that prints is the "Connected to database".
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                      ResultSet.CONCUR_READ_ONLY);
                      ResultSet srs = stmt.executeQuery("SELECT * FROM HR");
            Here is what displays in the console.

            Connected to the database!
            java.lang.ClassNotFoundException: com.oracle.jdbc.Driver
                 at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
                 at java.security.AccessController.doPrivileged(Native Method)
                 at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
                 at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
                 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
                 at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
                 at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
                 at java.lang.Class.forName0(Native Method)
                 at java.lang.Class.forName(Class.java:164)
                 at Discussion10.DatabaseSelect.main(DatabaseSelect.java:26)
            • 3. Re: How to write a SQL query in Java
              801497
              I realized that I put in under the wrong print statement so Ip moved it above the Try/Catch. So must most of the error messages are gone, but it's still not displaying the information from the database.
              import java.sql.*;
              import java.util.Properties;
              import java.sql.*;
              
              public class DatabaseSelect {
              
                  private static Connection con;
              
                  public static void main(String[] args) throws SQLException {
                      System.out.println("Connected to the database!");
                      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                              ResultSet.CONCUR_READ_ONLY);
                              ResultSet rs = stmt.executeQuery("SELECT *, FROM HR");
                      
                      Connection conn = null;
                      String url = "jdbc:oracle://localhost:1571/";
                      String dbName = "jdbc";
                      String driver = "com.oracle.jdbc.Driver";
                      String userName = "HR"; 
                      String password = "database";
                      String query = "SELECT * FROM HR";
                              
                      try {
                        Class.forName(driver).newInstance();
                        conn = DriverManager.getConnection(url+dbName,userName,password);
                        System.out.println("Connected to the database");
                        
                      } catch (Exception e) {
                        e.printStackTrace();
                      }
                    }
                  }
              • 4. Re: How to write a SQL query in Java
                798635
                Come on. Just look around in the same place you've got the sample code... ;)

                [http://www.exampledepot.com/egs/java.sql/GetRsData.html?l=rel]
                • 5. Re: How to write a SQL query in Java
                  843785
                  pbsacct_1 wrote:
                  I added it between my System.out statement and the catch exception. This is what I put, but I'm getting the same result. I looks like a lot of errors. The only thing that prints is the "Connected to database".
                  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                  ResultSet.CONCUR_READ_ONLY);
                  ResultSet srs = stmt.executeQuery("SELECT * FROM HR");
                  Here is what displays in the console.

                  Connected to the database!
                  java.lang.ClassNotFoundException: com.oracle.jdbc.Driver
                       at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
                       at java.security.AccessController.doPrivileged(Native Method)
                       at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
                       at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
                       at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
                       at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
                       at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
                       at java.lang.Class.forName0(Native Method)
                       at java.lang.Class.forName(Class.java:164)
                       at Discussion10.DatabaseSelect.main(DatabaseSelect.java:26)
                  You are not connecting.

                  Because you aren't even loading the driver.

                  To be honest if you can't decipher a stack trace with "java.lang.ClassNotFoundException" on your own you are not ready for JDBC anyway.