package dbexample;

import com.opensymphony.xwork2.ActionSupport;
import java.sql.*;
import java.util.*;

@SuppressWarnings("serial")
public class Example extends ActionSupport {

    public String execute() throws Exception {
        return SUCCESS;
    }

    public String createtable() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Create table");
            Statement st = con.createStatement();
            st.executeUpdate("create table books (id int PRIMARY KEY, title varchar(100) NOT NULL, price int NOT NULL)");
            System.out.println("Created");

            st.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }

    public String insertdata1() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Insert data1");
            Statement st = con.createStatement();
            st.executeUpdate("insert into books values(1, 'book1', 2940)");
            System.out.println("Inserted");

            st.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }

    public String insertdata2() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Insert data2");
            PreparedStatement ps = con.prepareStatement("insert into books values (?, ?, ?)");
            ps.setInt(1, id);
            ps.setString(2, title);
            ps.setInt(3, price);
            int result = ps.executeUpdate();
            System.out.println("Inserted " + result);

            ps.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }

    public String updatedata() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Update data");
            PreparedStatement ps = con.prepareStatement("update books set title=?, price=? where id=?");
            ps.setString(1, title);
            ps.setInt(2, price);
            ps.setInt(3, id);
            int result = ps.executeUpdate();
            System.out.println("Updated " + result);

            ps.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }

    public String deletedata() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("Delete data");
            PreparedStatement ps = con.prepareStatement("delete from books where id=?");
            ps.setInt(1, id);
            int result = ps.executeUpdate();
            System.out.println("Deleted " + result);

            ps.close();
            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }

    private ArrayList<Book> resultlist = new ArrayList<Book>();
    public ArrayList<Book> getResultlist() {
        return this.resultlist;
    }

    public String selectall() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("select all data");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from books");
            System.out.println("selected");

            resultlist.clear();
            while ( rs.next() )
            {
                int id = rs.getInt( "id" );
                String title = rs.getString( "title" );
                int price = rs.getInt( "price" );
                System.out.println( id + ", " + title + ", " + price );
                resultlist.add( new Book(id, title, price) );
            }

            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }

    public String selectdata() throws Exception {
        try {
            Connection con = getDatabaseConnection();

            System.out.println("select data");
            PreparedStatement ps = con.prepareStatement("select * from books where title like ?");
            ps.setString(1, title);
            ResultSet rs = ps.executeQuery();
            System.out.println("selected");

            resultlist.clear();
            while ( rs.next() )
            {
                int id = rs.getInt( "id" );
                String title = rs.getString( "title" );
                int price = rs.getInt( "price" );
                System.out.println( id + ", " + title + ", " + price );
                resultlist.add( new Book(id, title, price) );
            }

            con.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return SUCCESS;
    }


    private Connection getDatabaseConnection() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        String url = "jdbc:hsqldb:file:c:\\mydb\\example1\\hsqldb;create=true";
        System.out.println("Connecting to " + url);
        Connection con = DriverManager.getConnection(url, "sa", "");
        System.out.println("Connected");

        return con;
    }


    private int id;
    public int getId() {
    	return id;
    }
    public void setId(int id) {
    	this.id = id;
    }

    private String title;
    public String getTitle() {
    	return title;
    }
    public void setTitle(String title) {
    	this.title = title;
    }

    private int price;
    public int getPrice() {
    	return price;
    }
    public void setPrice(int price) {
    	this.price = price;
    }

}