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

public class DatabaseTest
{
    private static Connection connect = null;
    private String sql;

    public DatabaseTest()
    {
    }

    /**
     * Returns a connection to the mysql database
     * @return Connection to Database
     */
    private static Connection getConnection()
    {
        try
        {
            // This will load the MySQL driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    .getConnection("jdbc:mysql://localhost/dbTestJava?"
                            + "user=root&password=");
        }
        catch (Exception e)
        {

            e.printStackTrace();
        }

        return connect;
    }

    private void terminateDatabase( Connection db )
    {
        try
        {
            //close the connection
            db.close();
        }
        catch( SQLException sqlE )
        {
            sqlE.getStackTrace();
        }
    }

    /**
     * Creates a row in the database
     */
    public void createRow(String[] args)
    {
        Connection db = getConnection();
        PreparedStatement preState;
        try
        {
            sql =   "INSERT INTO books (title, author) VALUES (?, ?); ";

            preState    =   db.prepareStatement( sql );
            preState.setString(1, args[0] );
            preState.setString(2, args[1]);

            preState.executeUpdate();
            //close the connection
            db.close();
        }
        catch(SQLException e)
        {
            e.getStackTrace();
        }
        finally
        {
            this.terminateDatabase( db );
        }

    }

    /**
     * delets a row in the databas
     * @param int id
     */
    public void deleteRow( int id )
    {
        Connection db = getConnection();
        try {
            sql =   "DELETE FROM books WHERE id=" + id;
            Statement stmt  =   db.createStatement();
            stmt.execute(sql);

        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            this.terminateDatabase( db );
        }


    }

    /**
     * Updates a row
     * @param int id
     */
    public void updateRow( int id, String[] args )
    {

        Connection db = getConnection();
        PreparedStatement preState;
        try
        {
            sql =   "UPDATE books SET title=?, author=? WHERE id=" + id;

            preState    =   db.prepareStatement( sql );
            preState.setString(1, args[0] );
            preState.setString(2, args[1]);

            preState.executeUpdate();
        }
        catch(SQLException e)
        {
            e.getStackTrace();
        }
        finally
        {
            this.terminateDatabase( db );
        }
    }

    /**
     * selects a row
     * @param int id
     */
    public void selectRow( int rowId )
    {
        Connection db = getConnection();
        sql =   "SELECT id, title, author FROM books WHERE id=" + rowId;

        try {
            Statement   stmt    =   db.createStatement();
            ResultSet rs    =   stmt.executeQuery( sql );

            while ( rs.next() )
            {
                System.out.println( rs.getString("author") );
            }
        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            this.terminateDatabase( db );
        }
    }

    /**
     * selects all rows
     */
    public void selectAll()
    {
        Connection db = getConnection();
        sql =   "SELECT id, title, author FROM books";

        try {
            Statement   stmt    =   db.createStatement();
            ResultSet rs    =   stmt.executeQuery( sql );

            while ( rs.next() )
            {
                System.out.println( rs.getString( "title" ) + " (" + rs.getString("author") + ")" );
            }
        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            this.terminateDatabase( db );
        }

    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        DatabaseTest    dbTest  =   new DatabaseTest();

        //String[]  val = {"telekinese", "Bpr" };
        //dbTest.createRow( val );

        //dbTest.selectRow( 5 );

        //dbTest.deleteRow( 2 );

        //dbTest.selectAll();
        //dbTest.updateRow(5, val);
        //dbTest.selectAll();
    }

}