Interview Questions

MySQL - CreateTrigger Using JDBC connection

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE IS DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

package in.techdive.mysql;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class is used to create the Trigger Query in the Database using JDBC driver.
 *
 * It performs the following operations,
 * 1. Connects to the MySQL database named "techdive"
 * 2. Read the Trigger Query from a file "conf/MySQL_Trigger.txt"
 * 3. Execute the Trigger Query in the Database
 */

public class MySqlTriggerExecution
{

        private static final String     MYSQL_JDBC_DRIVER       = "org.gjt.mm.mysql.Driver";

        private static final String     DATABASE_URL      = "jdbc:mysql://localhost:3306/techdive";

        public static void main(String args[])
        {
                System.out.println("Program to create Trigger in the Database using JDBC driver");

                Connection connection = null;
                Statement statement = null;
                ResultSet resultSet = null;

                try
                {
                        // Load the Mysql JDBC driver
                        Class.forName(MYSQL_JDBC_DRIVER);

                        /*
                         * JDBC operations:
                         * 1. Connect to the database named "techdive"
                         * 2. Using the connection object, create the Statement
                         * 3. Read the Trigger query from the file "conf/MySQL_Trigger.txt"
                         * 4. Execute the Trigger query using the Statement Object
                         */

                        connection = DriverManager.getConnection(DATABASE_URL, "root", "");
                        statement = connection.createStatement();

                        String query = getTriggerData();
                        System.out.println(query);
                        statement.execute( query );
                }
                catch (SQLException e)
                {
                        System.err.println("Caught SQL Exception - " + e.getMessage());
                        e.printStackTrace();
                }
                catch (ClassNotFoundException e)
                {
                        System.err.println("Caught Class not found Exception - " + e.getMessage());
                        e.printStackTrace();
                }
                catch (Exception e)
                {
                        System.err.println("Caught Other Exception - " + e.getMessage());
                        e.printStackTrace();
                }

                finally
                {
                        if (resultSet != null)
                        try
                        {
                                        resultSet.close();
                        }
                        catch (SQLException e)
                        {
                                e.printStackTrace();
                        }
                       
                        if (statement != null)
                        try
                        {
                                        statement.close();
                        }
                        catch (SQLException e)
                        {
                                e.printStackTrace();
                        }
                       
                        if (connection != null)
                        try
                        {
                                        connection.close();
                        }
                        catch (SQLException e)
                        {
                                e.printStackTrace();
                        }
                }
        }

        /**
         * Method used to read the Trigger Query from the file "conf/MySQL_Trigger.txt"
         *
         * @return Trigger Query as String
         */

        private static String getTriggerData()
        {
                StringBuffer triggerQuery = new StringBuffer();
                try
                {
                        FileInputStream inputStream = new FileInputStream("conf/MySQL_Trigger.txt");
                        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
                        String line = null;
                        while ((line = bufferedReader.readLine()) != null)
                        {
                                triggerQuery.append(line);
                        }
                }
                catch (Exception e)
                {
                        System.out.println("Exception while Reading MySQL_Trigger.txt File");
                        e.printStackTrace();
                }
                return triggerQuery.toString();
        }
}

Note:
Place your Trigger to be created in the file "conf/MySQL_Trigger.txt" so that MySqlTriggerExecution class will create the trigger in the database.