2 Replies - 230 Views - Last Post: 17 January 2013 - 04:02 AM Rate Topic: -----

#1 John-acme  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 65
  • Joined: 08-February 11

Querying a Database

Posted 15 January 2013 - 08:23 AM

Somebody can please explain to me why this happen.. I do learn a bit MySQL, with relation to Java through JDBC Driver.

I follow a listing from a book. If the listing perfectly worked then if I choose an ID and press then button then it will come out sort of information in textfields. Here the Code:

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
import java.util.*;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Dell
 */
public class Accounts extends JFrame {

    private JButton getTransactionButton;
    private JList transactionNumberList;
    Connection connection;
    public JTextField transactionIDText, dateText, descriptionText, balanceText;

    public Accounts() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception e) {
            System.err.println("unable to find and load Driver");
            System.exit(1);
        }
        
    }

    private void buildGUI() {
        Container c = getContentPane();
        c.setLayout(new FlowLayout());

        //Do Account List
        Vector v = new Vector();
        
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT TransactionID From journal");

            while (rs.next()) {
                v.addElement(rs.getString("TransactionID"));
            }
            rs.close();

        } catch (Exception e) {
        }

        transactionNumberList = new JList(v);
        transactionNumberList.setVisibleRowCount(2);
        
        JScrollPane transNumScrollPane = new JScrollPane(transactionNumberList);
        //Do Getr Account Button
        getTransactionButton = new JButton("Get Button");
        getTransactionButton.addActionListener(new ActionListener() {
            
            public void actionPerformed(ActionEvent e) {
                try {
                    Statement statement = connection.createStatement();
                    ResultSet rs = statement.executeQuery("Select * From journal where transactionID =" + transactionNumberList.getSelectedValue());
                    
                    if (rs.next()==true) {
                        transactionIDText.setText(rs.getString("TransactionID"));
                        dateText.setText(rs.getString("Date"));
                        descriptionText.setText(rs.getString("Description"));
                        balanceText.setText(rs.getString("Balance"));
                        
                    }
                    else{
                        System.out.println("Empty set");
                    }
                } catch (SQLException ex) {
                }
            }
        });
        
        JPanel first = new JPanel();
        first.add(transNumScrollPane);
        first.add(getTransactionButton);
        
        transactionIDText = new JTextField(20);
        dateText = new JTextField(20);
        descriptionText = new JTextField(20);
        balanceText = new JTextField(20);
        
        JPanel second =new JPanel();
        second.setLayout(new GridLayout(5,1));
        second.add(transactionIDText);
        second.add(dateText);
        second.add(descriptionText);
        second.add(balanceText);
        
        c.add(first);
        c.add(second);
        setSize(200,200);
        show();
        
    }
    
    public void connectToDB(){
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost/personalaccounting?user=root&password=dell");
        } catch (Exception e) {
            System.err.println("Unable to connect to database");
            System.exit(1);;
        }
    }
    
    private void displaySQLError(SQLException sqle){
        System.out.println("SQLException : "+ sqle.getMessage());
        System.out.println("SQLState : "+ sqle.getSQLState());
        System.out.println("VendorError : "+ sqle.getErrorCode());
    }
    
    private void init(){
        connectToDB();
    }
    
    public static void main(String[] args) {
        Accounts accounts = new Accounts();
        
        accounts.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e){
                System.exit(0);
            }
        });
        
        accounts.init();
        accounts.buildGUI();
    }
}




Here is my pre-builded database
mysql> use personalaccounting
Database changed
mysql> select * from journal
    -> ;
+---------------+------------+--------------------+---------+--------+---------+

| TransactionID | Date       | Description        | Debit   | Credit | Balance |

+---------------+------------+--------------------+---------+--------+---------+

| 120101-00-00  | 2012-01-01 | Previous Record    |  300000 |      0 |  300000 |

| 120903-02-00  | 2012-09-03 | From Mom           | 1000000 |      0 | 1300000 |

| 120903-02-01  | 2012-09-03 | Additional Private |   50000 |      0 | 1350000 |

| 120918-02-00  | 2012-09-18 | Additional Private |   50000 |      0 | 1400000 |

| 120921-02-00  | 2012-09-21 | Additional Private |   50000 |      0 | 1450000 |

+---------------+------------+--------------------+---------+--------+---------+

5 rows in set (0.00 sec)

mysql> select * from journal where transactionID = 120903-02-00;
Empty set, 5 warnings (0.00 sec)   //--->>> EMPTY SET 

mysql> select * from journal where transactionID = '120903-02-00';
+---------------+------------+-------------+---------+--------+---------+
| TransactionID | Date       | Description | Debit   | Credit | Balance |
+---------------+------------+-------------+---------+--------+---------+
| 120903-02-00  | 2012-09-03 | From Mom    | 1000000 |      0 | 1300000 |
+---------------+------------+-------------+---------+--------+---------+
1 row in set (0.00 sec)

mysql>




SO when I run the program.. it only work on one ID. 120101-00-00. only this ID's information printed in textfields... When I chose other ID no information printed in my textfields...

I try to trace the problem.. so other the result is shown in second code frame.. that is the result of my trace..

My transactionID using char(12).

Can anyone explain to me why this happen?? Thanks in advance..

This post has been edited by macosxnerd101: 15 January 2013 - 05:36 PM
Reason for edit:: Please use a descriptive title


Is This A Good Question/Topic? 0
  • +

Replies To: Querying a Database

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10195
  • View blog
  • Posts: 37,654
  • Joined: 27-December 08

Re: Querying a Database

Posted 15 January 2013 - 05:39 PM

Don't ignore your Exceptions. Always printStackTrace() on them to see if you are encountering any Exceptions. Second, you should really be using PreparedStatements to guard against SQL Injection attacks.
Was This Post Helpful? 1
  • +
  • -

#3 John-acme  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 65
  • Joined: 08-February 11

Re: Querying a Database

Posted 17 January 2013 - 04:02 AM

Using preparedStatement solved my problem.. but i am still not sure if my usage of PreparedStatement is right: So here is my correction:
private PreparedStatement statement1 = null;
    
String statementSTR = "Select * From journal where transactionID = ?" ;

getTransactionButton.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent e) {
        try {
            connection.setAutoCommit(false);

            statement1 = connection.prepareStatement(statementSTR);

            statement1.setString(1, transactionNumberList.getSelectedValue().toString());
            //Statement statement = connection.createStatement();
            ResultSet rs = statement1.executeQuery();
            connection.commit();

            if (rs.next()==true) {
                transactionIDText.setText(rs.getString("TransactionID"));
                //System.out.println(rs.getString("Date"));
                dateText.setText(rs.getString("Date"));
                descriptionText.setText(rs.getString("Description"));
                balanceText.setText(rs.getString("Balance"));

            }/*
            else{
                System.out.println("Empty set");
            }*/
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Accounts.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
});



Thanks.. I am taking your advices..

This post has been edited by John-acme: 17 January 2013 - 04:02 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1