Interview Questions

MySQL - Fetch records from table 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 ARE 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.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class is used to fetch records from the database table.
 *
 * It performs the following operations,
 * 1. Connects to the MySQL database named "techdive"
 * 2. Fetches the records from the table named "student"
 * 3. Print the results
 *
 * STUDENT table schema details:
 * +-------+-------------+
 * | Field | Type        |
 * +-------+-------------+
 * | Name  | varchar(20) |
 * | age   | int(11)     |
 * +-------+-------------+
 *
 */

public class MySqlJdbcConnection
{

        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 started to retrieve data from the database");

                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. Execute the SQL query using the Statement Object
                         * 4. Iterate the Result Set object and print the results
                         */

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

                        System.out.println("Executing the query...");
                        resultSet = statement.executeQuery("SELECT NAME,AGE FROM STUDENT");
                        while (resultSet.next())
                        {
                                String studentName = resultSet.getString("NAME");
                                int studentAge = resultSet.getInt("AGE");
                                System.out.println("Student Details: Name = " + studentName + " ; Age = " + studentAge);
                        }
                }
                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();
                                }
                }
        }
}

Output:

Program started to retrieve data from the database
Executing the query...
Student Details: Name = Sibi ; Age = 18
Student Details: Name = Kaushik ; Age = 26
Student Details: Name = Madhan ; Age = 23

Note: For deleting the STUDENT table, use the below method
      statement.executeUpdate("drop table STUDENT");