How to view data from SQL to GUI on Java?

So, the program will be like this:
There is a menu that called "REGISTER" and we input the member id there (then it is stored to SQL database).
Then there is a menu that called "HISTORY" to view what is the member history (it could be purchase items, etc).
My questions is, How to check their history with just the user input his/her member id on the HISTORY menu and then the result shows it based from that input?

Thanks

"SELECT * FROM history WHERE userid = ?"

Not that your question is particularly clear.

Show the code till you tried..

Noone can give solution with assumptions.

okay, here I'll give the pic
so I have create a program that connects to phpmyadmin and it looks like this :

http://i759.photobucket.com/albums/xx236/RedFux/1-1.jpg
http://i759.photobucket.com/albums/xx236/RedFux/2-1.jpg
http://i759.photobucket.com/albums/xx236/RedFux/3-1.jpg
http://i759.photobucket.com/albums/xx236/RedFux/4.jpg

I have 3 table from phpmyadmin (film,member,history)

film table consist of 2 collum :
*filmcode
*filmname

member table consist of 2 collum :
*idmember
*name

history table consist of 4 collums :
*idmember
*name
*filmcode
*filmname

And I have several questions :
1. How to input data to 2 different table at the same time? Like at the image 1 "INPUT MOVIE DATA". I'm using twice executeUpdate (One to film table, and one to history table).
I'm doing it like this :

Code:

---

Statement stat = null;
Statement stat2 = null;

stat.executeUpdate("INSERT INTO film VALUES(\"" + temp3 + "\",\"" + temp4 + "\") ");
stat2.executeUpdate("INSERT INTO history(filmcode,filmname) VALUES (\"" + temp3 + "\", \"" +temp4 + "\")");

---

but it gives me an error. Same problem goes on INPUT MEMBER DATA..

2. How to make my delete data working? So I'm just input my movie code (in the database its called filmcode) and the program will scan the code and then delete it from my database.

3. In History menu, I'd like to show what is my latest inputted movie, registered member, and If could last deleted data. How to do that?

Really Really needs Help.....
Thank You So Much!

NB : Here is my code :

Code:

---

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

public class FinalProject extends JFrame implements ActionListener {
        Connection koneksi = null;
        Statement stat = null;
        Statement stat2 = null;
        ResultSet hasil = null;
        ResultSet hasil2 = null;
       
        private String [] menu = {"List Film", "Input Movie Data", "Input Member Data","Delete Data","History"};
       
        String tp = "";
        String tp2 = "";
       

        GregorianCalendar calendar = new GregorianCalendar();
        Date date = new Date();
        Date now = calendar.getTime();
       

JTabbedPane tab = new JTabbedPane();
       
JApplet apl = new JApplet();


JButton next = new JButton(">>");
JButton prev = new JButton("<<");
JButton input = new JButton("INPUT");
JButton input2 = new JButton("INPUT");
JButton del = new JButton("DELETE");
JButton submit = new JButton("SUBMIT");
JButton view = new JButton("VIEW");


JTextField tf1 = new JTextField(20);
JTextField tf2 = new JTextField(20);
JTextField tf3 = new JTextField(8);
JTextField tf4 = new JTextField(8);
JTextField tf5 = new JTextField(8);
JTextField tf6 = new JTextField(8);
JTextField tf7 = new JTextField(8);
JTextField tf8 = new JTextField(8);
JTextField tf9 = new JTextField(20);


JMenuBar bar = new JMenuBar();


JMenu menu1 = new JMenu("Help");


JMenuItem mi1 = new JMenuItem("About");


JPanel p1 = new JPanel();
JPanel p2 = new JPanel();
JPanel p3 = new JPanel();
JPanel p4 = new JPanel();
JPanel p5 = new JPanel();
JPanel p6 = new JPanel();
JPanel p7 = new JPanel();
JPanel p8 = new JPanel();
JPanel p9 = new JPanel();
JPanel p10 = new JPanel();
JPanel p11 = new JPanel();
JPanel p12 = new JPanel();
JPanel p13 = new JPanel();
JPanel p14 = new JPanel();
JPanel p15 = new JPanel();

//membuat textarea
JTextArea ta1 = new JTextArea();
JTextArea ta2 = new JTextArea();


ImageIcon ikon = new ImageIcon("movie.jpg");

        public FinalProject() {
                koneksiDatabase();
                data();
                setMenu();
                tab();
                setTampilan();
        }

public void setTampilan(){
        setTitle("Movie Organizer");
        setIconImage(ikon.getImage());
        setVisible(true);
        setJMenuBar(bar);
        bar.add(menu1);
        menu1.add(mi1);
        setSize(500,270);
        setDefaultCloseOperation(EXIT_ON_CLOSE);
       
        next.addActionListener(this);
        prev.addActionListener(this);
        input.addActionListener(this);
        del.addActionListener(this);
        input2.addActionListener(this);
}

public void koneksiDatabase(){
        try{
                Class.forName("com.mysql.jdbc.Driver");
                koneksi = DriverManager.getConnection("jdbc:mysql://localhost/finalproject","root","");
                stat = koneksi.createStatement();
       
        }catch(Exception e){
                System.out.println(e.getMessage());
        }
}

public void data(){
        try{
                hasil = stat.executeQuery("Select * from film");
               
                if(hasil.next()){
                        tf1.setText(hasil.getString(1));
                        tf2.setText(hasil.getString(2));

                        }
        }catch(Exception e){
                JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
        }
}

public void tab(){
                   
                      tab.addTab(menu[0],p3);
                      tab.addTab(menu[1],p4);
                      tab.addTab(menu[2],p12);
                      tab.addTab(menu[3],p7);
                      tab.addTab(menu[4],p10);
                     
                    tab.addChangeListener(new ChangeListener() {
                      public void stateChanged(ChangeEvent e) {
                      }
                    });
                    Container cp = getContentPane();
                    //cp.add(BorderLayout.NORTH,tf1);
                    cp.add(tab);
                    }
       
public void setMenu(){

        p1.setLayout(new GridLayout(3,2));
        p1.add(new JLabel("Kode Film"));
        p1.add(tf1);
        p1.add(new JLabel("JudulFilm"));
        p1.add(tf2);
       
        p2.setLayout(new FlowLayout());
        p2.add(prev);
        p2.add(new JLabel(""));
        p2.add(next);
       
        p3.setLayout(new BorderLayout());
        p3.add(p1,"North");
        p3.add(p2,"South");
       
        p4.setLayout(new BorderLayout());
       
        p5.setLayout(new GridLayout(2,4));
        p5.add(new JLabel("Add new movie code : "));
        p5.add(tf3);
        p5.add(new JLabel("Add new movie list : "));
        p5.add(tf4);
       
        p6.setLayout(new FlowLayout());
        p6.add(input);
       
        p4.add(p5,"North");
        p4.add(p6,"South");
       
        p7.setLayout(new BorderLayout());
       
        p8.setLayout(new GridLayout(1,2));
        p8.add(new JLabel("Input the movie code : "));
        p8.add(tf5);
       
        p9.setLayout(new FlowLayout());
        p9.add(del);
       
        p7.add(p8,"North");
        p7.add(p9,"South");
       
        p10.setLayout(new BorderLayout());
       
        p11.setLayout(new GridLayout(3,2));
        p11.add(new JLabel("Last Member Registered : "));
        p11.add(new JLabel(tp));
        p11.add(new JLabel("Last movie inserted : "));
        p11.add(new JLabel(""));
        p11.add(new JLabel("Las movie deleted : "));
        p11.add(new JLabel(""));
       
        p10.add(p11,"North");
       
        p12.setLayout(new BorderLayout());
       
        p13.setLayout(new GridLayout(2,2));
        p13.add(new JLabel("Add new member : "));
        p13.add(tf8);
        p13.add(new JLabel("Member name : "));
        p13.add(tf9);
       
        p14.setLayout(new FlowLayout());
        p14.add(input2);
       
        p12.add(p13,"North");
        p12.add(p14,"South");
}

public void history(){
        try{

        hasil = stat.executeQuery("Select * from film order by time desc");
        tp = hasil.toString();
       
        hasil2 = stat.executeQuery("Select * from history order by time desc");
        tp2 = hasil2.toString();
       
       
}catch(Exception t){
        JOptionPane.showMessageDialog(null, t.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
}
}


        public static void main(String[] args) {
                new FinalProject();
        }

        //membuat action listener
        @Override
        public void actionPerformed(ActionEvent e){
                if(e.getSource()==next){
                        try{
                        hasil.next();
                        tf1.setText(hasil.getString(1));
                        tf2.setText(hasil.getString(2));
                        }catch(Exception f){
                                JOptionPane.showMessageDialog(null, f.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                        }
                       
                        }else if(e.getSource()==prev){
                                try{
                                hasil.previous();
                                tf1.setText(hasil.getString(1));
                                tf2.setText(hasil.getString(2));
                               
        }catch(Exception a){
                JOptionPane.showMessageDialog(null, a.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
        }
        }else if(e.getSource()==input){
                String temp = tf3.getText();
                String temp2 = tf4.getText();
               
                if(temp.equals("")){
                        JOptionPane.showMessageDialog(null, "Please enter your keycode!","Attention!",JOptionPane.WARNING_MESSAGE);
                }else if(temp2.equals("")){
                        JOptionPane.showMessageDialog(null, "Please input the movie title!","Attention!",JOptionPane.WARNING_MESSAGE);
        }else{
                        try{
                        stat.executeUpdate("INSERT INTO film VALUES (\"" + temp + "\", \"" +temp2 + "\")");
                        stat2.executeUpdate("INSERT INTO history(filmcode,filmname) VALUES (\"" + temp + "\", \"" +temp2 + "\")");
                        JOptionPane.showMessageDialog(null, "Success!","Attention!",JOptionPane.INFORMATION_MESSAGE);
                        hasil = stat.executeQuery("Select * from film");
                }catch(Exception v){
                        JOptionPane.showMessageDialog(null, v.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                }
               
        }
        }else if(e.getSource()==input2){
                String temp3 = tf8.getText();
                String temp4 = tf9.getText();
               
                        if(temp3.equals("")){
                                JOptionPane.showMessageDialog(null, "Please input member id!","Attention!",JOptionPane.WARNING_MESSAGE);
                        }else if (temp4.equals("")){
                                JOptionPane.showMessageDialog(null, "Please input member id!","Attention!",JOptionPane.WARNING_MESSAGE);
                        }else{
                        try{
                                stat.executeUpdate("INSERT INTO member VALUES(\"" + temp3 + "\",\"" + temp4 + "\") ");
                                stat2.executeUpdate("INSERT INTO history(idmember,nama) VALUES (\"" + temp3 + "\", \"" +temp4 + "\")");
                                JOptionPane.showMessageDialog(null, "Success!","Information!",JOptionPane.INFORMATION_MESSAGE);
                                hasil = stat.executeQuery("Select * from film");
                        }catch(Exception y){
                        JOptionPane.showMessageDialog(null, y.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                }
                        }
                       
        }else if(e.getSource()==del){
                String temp3 = tf5.getText();
                try{
                       
                if(temp3.equals(tp)==false){
                        JOptionPane.showMessageDialog(null, "wrong input!","Attention!",JOptionPane.WARNING_MESSAGE);
                }else{
                        JOptionPane.showMessageDialog(null, "Success!","Information!",JOptionPane.INFORMATION_MESSAGE);
                }
                }catch(Exception x){
                        JOptionPane.showMessageDialog(null, x.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                }
               
                if(temp3.equals("")){
                        JOptionPane.showMessageDialog(null, "Please enter your movie code!","Attention!",JOptionPane.WARNING_MESSAGE);
                }else{
                        try{
                                stat.executeUpdate("DELETE FROM film where filmcode = \"" + temp3 +"\")");
                                JOptionPane.showMessageDialog(null, "Success! "+temp3+" deleted!","Attention!",JOptionPane.INFORMATION_MESSAGE);
                        }catch(Exception g){
                                JOptionPane.showMessageDialog(null, g.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE);
                        }
                       
                }
        }
        }
}

---

Quote:

---

Originally Posted by BluXit

And I have several questions :
1. How to input data to 2 different table at the same time? Like at the image 1 "INPUT MOVIE DATA". I'm using twice executeUpdate (One to film table, and one to history table).
I'm doing it like this :

Code:

---

Statement stat = null;
Statement stat2 = null;

stat.executeUpdate("INSERT INTO film VALUES(\"" + temp3 + "\",\"" + temp4 + "\") ");
stat2.executeUpdate("INSERT INTO history(filmcode,filmname) VALUES (\"" + temp3 + "\", \"" +temp4 + "\")");

---

but it gives me an error. Same problem goes on INPUT MEMBER DATA..

---

What error?
"It gives me an error" tells us nothing.
Exception and stack trace so we know which line the exception is coming from, anything less will result in guess work on our part...however I wouldn't be surprised if it's down to you concatenating your queries rather than using a PreparedStatement.

Quote:

---

Originally Posted by BluXit

2. How to make my delete data working? So I'm just input my movie code (in the database its called filmcode) and the program will scan the code and then delete it from my database.

---

What does it do at the moment?
And again, use a PreparedStatement.
Until you do that there's no point continuing since I would lay odds that that's where your problems are occurring...and always printStackTrace() for exceptions.

Quote:

---

Originally Posted by BluXit

3. In History menu, I'd like to show what is my latest inputted movie, registered member, and If could last deleted data. How to do that?

---

The only way you can do any of these is to put a timestamp on each table for a creation_date. For deleted data you can only do it by not actually deleting the data but putting in a deleted_date column, and anything with a non-NULL deleted_date is deleted and therefore ignored by the rest of the system...well, it would be once you include the deleted_date in the WHERE clause.