/*          
    * MyH2GUI: 			program for easier LOBs testing 
    *
    * Author:  			Michael Bissell  
    * Date:    			4/19/2011
    * Version:  		1.0
    * Last Updated:     4/22 
    * Purpose:  		New users can see testing of LOBs faster  
    *  
    * Usage:   			If placed in org/h2/samples, files 
    *      	   			will appear in project home	  
   */



package org.h2.samples;

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

import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

import org.h2.tools.DeleteDbFiles;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Blob;


public class MyH2GUI extends JFrame implements ActionListener{

	FlowLayout fl = new FlowLayout();
	
	JButton    connectButton = new JButton("Connect Database");
	JButton    initButton = new JButton("Init Database");
	JButton    insertButton = new JButton("Insert BLOB");
	JButton    removeButton = new JButton("Remove BLOB");
	JLabel     dfl = new JLabel("Specify Datafile:");
	JTextField filename = new JTextField(10);
	JButton    disConnectButton = new JButton("Disconnect Database");
	
	JTextArea  fb = new JTextArea("");
	JScrollPane jsp = new JScrollPane(fb);
	
	private Connection conn;
	private Statement stat;
	private String fileString = "";
	private PreparedStatement pstmt;
	
	
	
	
	
    public MyH2GUI(String name) {
    	
        super(name);

    }

    public void addMyComponents(final Container pane) {
	
        pane.setLayout(fl);
        
        JPanel controls = new JPanel();
        controls.setLayout(new FlowLayout());
        
        pane.add(connectButton);
        pane.add(initButton);
        pane.add(insertButton);
        pane.add(removeButton);
        pane.add(dfl);
        pane.add(filename);
        pane.add(disConnectButton);
        
        fb.setLineWrap(true);
        fb.setWrapStyleWord(true);
        pane.add(jsp);
        jsp.setPreferredSize(new Dimension(350, 200));
        
        
        connectButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                connectDatabase();
            }
        });  
        
        initButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                initDatabase();
            }
        });  
        
        insertButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                insertBlob();
            }
        });

        removeButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                removeBlob();
            }
        });
        
        disConnectButton.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                disconnectDatabase();
            }
        });
        
    }  
        
        private static void createAndShowGUI(){
        	MyH2GUI frame = new MyH2GUI("MyH2GUI");
        	      	
            frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
            
            //Set up the content pane.
            frame.addMyComponents(frame.getContentPane());

            //Display the window.
            frame.pack();
            frame.setSize(800, 400);
            frame.setVisible(true);

        } 
        
        public void actionPerformed(ActionEvent e) {

        }
        

        private void connectDatabase() {
        	
        	
        	try {
                // delete the database named 'test' in the user home directory
                DeleteDbFiles.execute("~", "test", true);

                Class.forName("org.h2.Driver");
                conn = DriverManager.getConnection("jdbc:h2:~/test");
                stat = conn.createStatement();
                fb.append(">Connected to \"test\"\n");

        	}catch (java.lang.Exception ex) {
         		fb.append( ex.getMessage() );
        	}
        }
        
        private void initDatabase() {
        	try {
        		
        		stat.execute("CREATE TABLE myBlobTable ( id INT PRIMARY KEY NOT NULL, blobData BLOB(5M) ) ");
        		fb.append( ">myBlobTable created...\n" );
        		
        	}catch(java.lang.Exception ex){
        		
         		fb.append( ex.getMessage() );
        	}
        }
    


        private void insertBlob() {
        	
            
        	fileString = filename.getText();
        	try{
        		
        		pstmt = conn.prepareStatement ("INSERT INTO myBlobTable VALUES (?,?)");
        		pstmt.setInt (1, 100);
        		File fBlob = new File ( fileString );
        		FileInputStream fis = new FileInputStream ( fBlob );
        		pstmt.setBinaryStream ( 2, fis, (long) fBlob.length() );
        		pstmt.execute();
        		fb.append(fileString + "inserted for " + fBlob.length() + " byte(s)...\n");
        		fis.close();

        	}catch(Exception ex){
        		fb.append(">File not found...\n");
        		fb.append( ex.getMessage() );
        	}
        }
        
        private void removeBlob(){
        	
        	try{
        		fileString = filename.getText();
        		pstmt = conn.prepareStatement ("DELETE FROM myBlobTable WHERE blobData = '(?,?)' ;");
        		pstmt.setString(1, fileString);
        		pstmt.execute();
        		fb.append(fileString + "removed...\n");

        	}catch(Exception ex){
        		fb.append(">Could not remove " + fileString + "...\n");
        		fb.append( ex.getMessage() );
        	}
        	
        	
        }
        
        private void disconnectDatabase() {
        	
        	
        	try {
            	stat.close();
            	pstmt.close();
                conn.close();
                fb.append(">Disconnected from \"test\"\n");
                
        	}catch (java.lang.Exception ex) {
        		fb.append( ex.getMessage() );
        	    }
        	} 	
        

        
        
        
        
        public static void main(String ... args) throws Exception {
            javax.swing.SwingUtilities.invokeLater( 	
            		new Runnable(){
            			public void run(){
            				createAndShowGUI();
            			}
            		}
            );
        }  
        
}