jmysql.java

some java class and example

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

public class JMysql {
    public static JTextField id, name, amount;
    public static JTextArea textArea;
    
    public static void main (String [] args) {
        JFrame f = new JFrame("JMysql - Database Maintenance");
        f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        f.setLocation(200,100);
        f.setSize(500,500);
        Container cont = f.getContentPane();
        cont.setLayout(new BorderLayout());        
        ActionListener al = new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String action = e.getActionCommand();
                ClientRecord rcd = new ClientRecord(id.getText(),name.getText(),amount.getText());
                executeSql(rcd, action);
                
            }
        };
        JPanel p = new JPanel();
        p.setLayout(new GridBagLayout());
        GridBagConstraints g = new GridBagConstraints();
        g.fill = GridBagConstraints.BOTH;
        g.gridwidth= 1;
        g.weightx = 1;
        g.weighty = 1;
        Font myFont = new Font("Arial",Font.BOLD,20);
        id = new JTextField();     id.setFont(myFont);
        name = new JTextField();   name.setFont(myFont);
        amount = new JTextField(); amount.setFont(myFont);
        JLabel idLabel = new JLabel("ID");         idLabel.setFont(myFont);
        JLabel nameLabel = new JLabel("Name");     nameLabel.setFont(myFont);
        JLabel amountLabel = new JLabel("Amount"); amountLabel.setFont(myFont);
        g.gridwidth=1; p.add(idLabel,g);
        g.gridwidth= GridBagConstraints.REMAINDER; p.add(id,g);        
        g.gridwidth=1;  p.add(nameLabel,g);
        g.gridwidth= GridBagConstraints.REMAINDER; p.add(name,g);
        g.gridwidth=1; p.add(amountLabel,g);
        g.gridwidth= GridBagConstraints.REMAINDER; p.add(amount,g);
        
        g.gridwidth=1;
        
        addButton(p,"GET",g,1,al);
        addButton(p,"ADD",g,1,al);
        addButton(p,"UPDATE",g,1,al);
        addButton(p,"DELETE",g,1,al);
        addButton(p,"LIST",g,1,al);        
                
        cont.add(p,BorderLayout.NORTH); 
        textArea = new JTextArea(); textArea.setFont(myFont);        
        cont.add(new JScrollPane(textArea),BorderLayout.CENTER);
        f.setVisible(true);
    }
    private static void addButton(JPanel p, String label, GridBagConstraints g, 
            int gridwidth,ActionListener al) {
        g.gridwidth = gridwidth;
        JButton tempjb = new JButton(label);
        tempjb.addActionListener(al);
        tempjb.setFont(new Font("Arial",Font.BOLD,20));
        p.add(tempjb,g);        
    }
    public static void executeSql(ClientRecord rcd, String action) {    
                
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sqlStmt = "";
        try {
            Class.forName("org.gjt.mm.mysql.Driver");
            con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/test","","");
            stmt = con.createStatement();
            if (action.equals("GET")) {                
                sqlStmt = rcd.getGetSql();
                rs = stmt.executeQuery(sqlStmt);                
                if (rs.next()) {                
                    name.setText(rs.getString("name"));
                    amount.setText(rs.getString("amount"));
                    textArea.setText("1 Record Found");
                } else {
                    textArea.setText("Record Not Found");
                    name.setText("");
                    amount.setText("");
                }                                
            } else if (action.equals("LIST")) {
                sqlStmt = rcd.getListSql();
                rs = stmt.executeQuery(sqlStmt);
                int count = 0;
                textArea.setText("");
                while (rs.next()) { 
                    String tempStr = "ID : "+rs.getString("id")+
                                     ", Name : "+rs.getString("name")+
                                     ", Amount : "+ rs.getBigDecimal("amount",2).toString();                    
                    textArea.append(tempStr+"\n");                                
                    count ++;
                }                                
                textArea.append(count + " Records Found");
            } else if (action.equals("ADD")) {
                sqlStmt = rcd.getInsertSql();
                try {
                    stmt.executeUpdate(sqlStmt);
                    textArea.setText("Record Added Successfully");
                } catch (Exception addException) {                    
                    textArea.setText("Record Add Fail : "+addException);
                }
            } else if(action.equals("UPDATE")) {
                sqlStmt = rcd.getUpdateSql();                
                int count = stmt.executeUpdate(sqlStmt);
                textArea.setText(count +" Record Updated");                
            } else if(action.equals("DELETE")) {
                sqlStmt = rcd.getDeleteSql();
                int count = stmt.executeUpdate(sqlStmt);
                textArea.setText(count +" Record Deleted");                
            } 
            
        } catch(Exception e1) {
          System.out.println("Exception 122 : "+e1);
        } finally {
            try {
                if ( rs != null ) rs.close();
                stmt.close();       
                con.close();
                
            } catch (Exception e2) {
                System.out.println("Exception 130 : "+e2);
            }
        }
    }
}

class ClientRecord {
    private String id ="";
    private String name ="";
    private BigDecimal amount = new BigDecimal("0");
    
    public ClientRecord(){ }
    public ClientRecord(String id, String name, String amount) {
        this.id = id;
        this.name = name;
        this.amount = new BigDecimal("0");
        try {            
            this.amount = new BigDecimal(amount);
        } catch (Exception e) {} 
    }
    public String getId () { return id; }
    public String getName() { return name; }
    public BigDecimal getAmount() { return amount; }
    
    public void setId(String id) {this.id = id; }
    public void setName(String name) {this.name = name; }
    public void setAmount(BigDecimal amount) {this.amount = amount;} 
    
    public String getGetSql(){        
        return "select * from client where id='"+sqlHandle(id)+"'";
    }
    public String getListSql(){
        return "select * from client order by id";
        
    }
    public String getInsertSql(){
        return "insert into client set name='"+sqlHandle(name)+"',id='"+sqlHandle(id)+
               "',amount='"+amount.toString()+"'";
    }
           
    public String getUpdateSql() {
        return "update client set name='"+sqlHandle(name)+"',amount='"+amount.toString()+
               "' where id = '"+sqlHandle(id)+"'";
    }
           
    public String getDeleteSql(){
        return "delete from client where id = '"+sqlHandle(id)+"'";
    }
    public String sqlHandle(String sql) {
        String rv = sql;
        rv = rv.replaceAll("'","''");
        rv = rv.replaceAll("\\\\","\\\\\\\\");
        return rv;        
    }
}        

/* for create table in mysql
create table client (id varchar(20) primary key not null, 
                     name varchar(50), 
                     amount decimal(10,2));
*/