Results 1 to 12 of 12
  1. #1
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Initiating an Access SQL query within Java to display in a JPanel

    Hello,

    As part of a group project I have been asked to display a query from MS Access in Java, we are creating a money game (three items x=10, y=20 therefore the total amount is, items are displayed above four possible answers/buttons)

    Within the game I need to display the Highscores of all the users on a JPanel, although I am happy to get it to run in the compiler first. The database is showing as connected but I cant get the SQL code to work, this is my code, I cant get the DB attached but I have pasted the SQL code below

    XML Code:
    /*
     * To change this template, choose Tools | Templates
     * and open the template in the editor.
     */
    package moneygame;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class highScoreTest {
    
        //Path to database
        private static String dataSourceName = "C:\\Documents and Settings\\Martyn\\"
                + "My Documents\\Software Engineering Year 1\\Year 1 Semester 2\\"
                + "Programming 2\\Group Assignment\\Programming Group\\MoneyGame.mdb";
        //The connection to the specified DB
        private static Connection dbconn = null;
    
        //Connects
        /**
         * Connects to the db
         */
        private static void connectToDB() {
            try {
                System.out.println("About to connect to DB ....");
    
                // Connection to DB
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dbURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
                dbURL += dataSourceName + ";)";
                dbconn = DriverManager.getConnection(dbURL);
                System.out.println("DB Connected");
            } catch (Exception ex) {
                System.out.println("Cannot connect to DB. " + ex.toString());
            }
        }
    
        /**
         * Closes the DB
         */
        private static void closeConnection() {
            try {
                dbconn.close();
                System.out.println("Connection closed");
            } catch (Exception ex) {
                System.out.println("Problem closing DB connection. " + ex.toString());
            }
        }
    
        public static void main(String args[]) {
    
            connectToDB();
    
            //int userHighScore = checkUserHighScores("mar2727","WA345");
    
            StringBuilder sql = new StringBuilder();
    
            Statement stmt = dbconn.createStatement();
    
            System.out.println("About to check High Scores");
    
            sql.append("SELECT [tbl User].[Username], [tbl User].[Highscore]");
            sql.append("FROM [tbl User]; ");
            
            sql.append("WHERE (((TblUser.Username)='");
            sql.append(Username);
            
            sql.append("') AND ((TblUser.Highscore)='");
            sql.append(Highscore);
            
            sql.append("'))");
    
            System.out.println("[tbl User].[Username]" + "[tbl User].[Highscore]");
    
    
            //int highScores = userHighScores();
        }
    }


    The SQL code for the high scores is

    SELECT [tbl User].[Username], [tbl User].[Highscore]
    FROM [tbl User];


    any thoughts/help would be much appreciated

  2. #2
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

  3. #3
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    Hi yes I am, my bad I should have known to post the error, its something to do with line 61

    XML Code:
    About to connect to DB ....
    DB Connected
    Exception in thread "main" java.lang.RuntimeException: Uncompilable source code - unreported exception java.sql.SQLException; must be caught or declared to be thrown
            at moneygame.highScoreTest.main(highScoreTest.java:61)
    Java Result: 1
    BUILD SUCCESSFUL (total time: 8 seconds)
    Thanks
    Last edited by Martyn; 04-21-2012 at 07:41 PM.

  4. #4
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    Shoot, you're trying to run uncompilable code. Never do this. Instead fix the compile issues before trying to run.

  5. #5
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    thanks for that,

    I thought line 61 was compatible, what should be in its place as I have tried commenting it out but then it reads



    XML Code:
    run:
    About to connect to DB ....
    DB Connected
    About to check High Scores
    Exception in thread "main" java.lang.RuntimeException: Uncompilable source code
            at moneygame.highScoreTest.main(highScoreTest.java:69)
    Java Result: 1
    BUILD SUCCESSFUL (total time: 3 seconds)


    I thought I was on the right track as I have directed the code to the correct SQL path/query

    XML Code:
            sql.append("') AND ((TblUser.Highscore)='");
            sql.append(Highscore);
    The Highscore on the second line shows up as unknown when I have specified it in the line above, any thoughts?

    Thanks

    Martyn

  6. #6
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    Are you sure you're looking at the right line? It looks to me as if the error message is telling you that your code isn't handling exceptions correctly. Are you missing a try/catch block somewhere?

  7. #7
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    Hi,

    I have added a try/catch over the code but still nothing. Ile mess around with it and see what happens. If I make any progress ile post it.

    Good luck

  8. #8
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    You should compile the code and look carefully at the error message returned from the Javac compiler. If you don't understand it, post it here.

  9. #9
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    The new error message is

    XML Code:
    run:
    About to connect to DB ....
    DB Connected
    About to check High Scores
    Problem printing scores. java.lang.RuntimeException: Uncompilable source code - variable Username might not have been initialized
    BUILD SUCCESSFUL (total time: 2 seconds)
    This is with the new try catch, it says the "variable Username might not have been initialized" but I have initialised it above along with the high score above.

    I thought I was telling Java what information to retrieve by coding the following -

    sql.append("WHERE (((tblUser.Username)='");
    sql.append(Username);

    Is this right?

  10. #10
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    You are not setting the Username field anywhere in the code above that I can see. You can look yourself as it would be fairly obvious, something like

    userName = ... ;

    Also please adhere to Java naming conventions, where field names start with lower case, class names start upper case, and all use camel case. This is especially true if you are asking others for free help. It's not asking too much for you to make it easier for others to understand your code and help you.

  11. #11
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    Fudbarable thanks for all your help and the hint, i am adhering to the database conventions as the person (noNames) who created the database didn't and I completely understand that its annoying, thanks for all the help

  12. #12
    Martyn is offline Member
    Join Date
    Nov 2011
    Location
    Belfast
    Posts
    37
    Rep Power
    0

    Default Re: Initiating an Access SQL query within Java to display in a JPanel

    Managed to get the code working just thought I would post an update, thanks for all the help really appreciate it.

    XML Code:
    package moneygame;
    
    /*
     * To change this template, choose Tools | Templates
     * and open the template in the editor.
     */
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    /*
     * NewJFrame.java
     *
     * Created on Apr 15, 2012, 8:17:25 PM
     */
    /**
     *
     * @author davidwarren
     */
    public class HighScores extends javax.swing.JFrame {
    
    
        //The connection to the specified DB
        private static String dataSourceName = "C:/myfile/MoneyGame.mdb";
        private static Connection dbconn = null;
        private static ResultSet rs;
    
        //Connects
        /**
         * Connects to the db
         */
        private static void connectToDB() {
            try {
                System.out.println("About to connect to DB ....");
    
                // Connection to DB
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dbURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
                dbURL += dataSourceName + ";)";
                dbconn = DriverManager.getConnection(dbURL);
                System.out.println("DB Connected");
            } catch (Exception ex) {
                System.out.println("Cannot connect to DB. " + ex.toString());
            }
        }
    
        /**
         * Closes the DB
         */
        private static void closeConnection() {
            try {
                dbconn.close();
                System.out.println("Connection closed");
            } catch (Exception ex) {
                System.out.println("Problem closing DB connection. " + ex.toString());
            }
    
        }
    
        // public static void main (String args []){
        // int highScores = checkHighScores ();
        // System.out.println(highScores);
        public void showHighestScore() {
            try {
                connectToDB();
                StringBuilder scoreInfo = new StringBuilder();
                StringBuilder sql = new StringBuilder();
                Statement stmt = dbconn.createStatement();
                System.out.println("About to check High Scores");
    
                sql.append("SELECT [tbl User].[Username], [tbl User].[Highscore]");
                sql.append("FROM [tbl User] ORDER BY [tbl User].[Highscore] DESC");
    
                rs = stmt.executeQuery(sql.toString());
    
                while (rs.next()) {
                    scoreInfo.append(rs.getString("Username"));
                    scoreInfo.append(" - ");
                    scoreInfo.append(rs.getString("Highscore"));
                    scoreInfo.append("\n");
    
                }
                score.setText(scoreInfo.toString());
    
            } catch (Exception ex) {
    
                System.out.println("Problem printing scores. " + ex.toString());
    
            }
        }
    
        /** Creates new form NewJFrame */
        public HighScores() {
            initComponents();
            showHighestScore();
            setVisible(true);
            
    
        }
    
        /** This method is called from within the constructor to
         * initialize the form.
         * WARNING: Do NOT modify this code. The content of this method is
         * always regenerated by the Form Editor.
         */
        @SuppressWarnings("unchecked")
        // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
        private void initComponents() {
    
            jPanel1 = new javax.swing.JPanel();
            jLabel1 = new javax.swing.JLabel();
            backToMainMenu = new javax.swing.JButton();
            jScrollPane1 = new javax.swing.JScrollPane();
            score = new javax.swing.JTextArea();
    
            setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
            setName("Form"); // NOI18N
    
            jPanel1.setName("jPanel1"); // NOI18N
    
            org.jdesktop.application.ResourceMap resourceMap = org.jdesktop.application.Application.getInstance().getContext().getResourceMap(HighScores.class);
            jLabel1.setFont(resourceMap.getFont("jlblHighScore.font")); // NOI18N
            jLabel1.setText(resourceMap.getString("jlblHighScore.text")); // NOI18N
            jLabel1.setName("jlblHighScore"); // NOI18N
    
            backToMainMenu.setText(resourceMap.getString("jbtnMainMenu.text")); // NOI18N
            backToMainMenu.setName("jbtnMainMenu"); // NOI18N
            backToMainMenu.addActionListener(new java.awt.event.ActionListener() {
                public void actionPerformed(java.awt.event.ActionEvent evt) {
                    backToMainMenuActionPerformed(evt);
                }
            });
    
            jScrollPane1.setName("jScrollPane1"); // NOI18N
    
            score.setColumns(20);
            score.setRows(5);
            score.setName("score"); // NOI18N
            jScrollPane1.setViewportView(score);
    
            org.jdesktop.layout.GroupLayout jPanel1Layout = new org.jdesktop.layout.GroupLayout(jPanel1);
            jPanel1.setLayout(jPanel1Layout);
            jPanel1Layout.setHorizontalGroup(
                jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                .add(jPanel1Layout.createSequentialGroup()
                    .add(67, 67, 67)
                    .add(jLabel1)
                    .addContainerGap(66, Short.MAX_VALUE))
                .add(org.jdesktop.layout.GroupLayout.TRAILING, jPanel1Layout.createSequentialGroup()
                    .addContainerGap(52, Short.MAX_VALUE)
                    .add(jScrollPane1, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 249, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                    .add(49, 49, 49))
                .add(jPanel1Layout.createSequentialGroup()
                    .add(110, 110, 110)
                    .add(backToMainMenu, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 120, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                    .addContainerGap(120, Short.MAX_VALUE))
            );
            jPanel1Layout.setVerticalGroup(
                jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                .add(jPanel1Layout.createSequentialGroup()
                    .addContainerGap()
                    .add(jLabel1)
                    .add(11, 11, 11)
                    .add(jScrollPane1, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 108, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                    .add(18, 18, 18)
                    .add(backToMainMenu, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 44, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                    .addContainerGap(19, Short.MAX_VALUE))
            );
    
            org.jdesktop.layout.GroupLayout layout = new org.jdesktop.layout.GroupLayout(getContentPane());
            getContentPane().setLayout(layout);
            layout.setHorizontalGroup(
                layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                .add(layout.createSequentialGroup()
                    .addContainerGap()
                    .add(jPanel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addContainerGap())
            );
            layout.setVerticalGroup(
                layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                .add(layout.createSequentialGroup()
                    .addContainerGap()
                    .add(jPanel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addContainerGap())
            );
    
            pack();
        }// </editor-fold>                        
    
    private void backToMainMenuActionPerformed(java.awt.event.ActionEvent evt) {                                               
        new MainMenu();
        dispose();
    }                                              
    
        /**
         * @param args the command line arguments
         */
        public static void main(String args[]) {
            /* Set the Nimbus look and feel */
            //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
            /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
             * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
             */
            try {
                for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                    if ("Nimbus".equals(info.getName())) {
                        javax.swing.UIManager.setLookAndFeel(info.getClassName());
                        break;
                    }
                }
            } catch (ClassNotFoundException ex) {
                java.util.logging.Logger.getLogger(HighScores.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
            } catch (InstantiationException ex) {
                java.util.logging.Logger.getLogger(HighScores.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
            } catch (IllegalAccessException ex) {
                java.util.logging.Logger.getLogger(HighScores.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
            } catch (javax.swing.UnsupportedLookAndFeelException ex) {
                java.util.logging.Logger.getLogger(HighScores.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
            }
            //</editor-fold>
    
            /* Create and display the form */
            java.awt.EventQueue.invokeLater(new Runnable() {
    
                public void run() {
                    new HighScores();
                }
            });
        }
        // Variables declaration - do not modify                     
        private javax.swing.JButton backToMainMenu;
        private javax.swing.JLabel jLabel1;
        private javax.swing.JPanel jPanel1;
        private javax.swing.JScrollPane jScrollPane1;
        private javax.swing.JTextArea score;
        // End of variables declaration                   
    }

Similar Threads

  1. initiating an independent java project
    By snaketron in forum New To Java
    Replies: 4
    Last Post: 12-14-2011, 11:53 PM
  2. Java JDBC - Display Multiple Query Results in one GUI
    By nov072008 in forum AWT / Swing
    Replies: 5
    Last Post: 10-06-2011, 05:03 PM
  3. return output from a non select query (java/access)
    By kitiaradl in forum New To Java
    Replies: 2
    Last Post: 04-21-2010, 12:04 AM
  4. return output from a non select query (java/access)
    By kitiaradl in forum New To Java
    Replies: 0
    Last Post: 04-15-2010, 09:01 PM
  5. ms access jdbc query problem
    By Fleur in forum New To Java
    Replies: 2
    Last Post: 04-30-2008, 06:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •