JAVA EE: JDBC : JDBC DataSource [Mysql DB]

Saturday, 6 September 2014

JDBC : JDBC DataSource [Mysql DB]

Click here to watch in Youtube : https://www.youtube.com/watch?v=Bj1V1JNBTn8

Click the below Image to Enlarge

JDBC DataSource [Mysql DB]

MyqlDataSource.java

import javax.sql.DataSource;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class MyqlDataSource
{

    public static DataSource getMySQLDataSource()
    {

        MysqlDataSource mysqlDS = null;
        try
        {
            mysqlDS = new MysqlDataSource();
            mysqlDS.setURL("jdbc:mysql://localhost:3306/world");
            mysqlDS.setUser("root");
            mysqlDS.setPassword("root");
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        return mysqlDS;
    }

}

JDBCDataSourceDemo.java

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

import javax.sql.DataSource;

public class JDBCDataSourceDemo
{

    public static void main( String[] args )
    {
        JDBCDataSourceDemo jdbcDataSourceDemo = new JDBCDataSourceDemo();
        jdbcDataSourceDemo.getCityInformation();

    }

    private void getCityInformation()
    {
        Connection connection = null;
        Statement stmt = null;
        /*
         * Get the MysqlDataSource
         */
        DataSource dataSource = MyqlDataSource.getMySQLDataSource();
        try
        {
            /*
             *  Get connection from the DataSource
             */
            connection = dataSource.getConnection();
            
            /*
             *  Execute the Query
             */
            
            stmt = connection.createStatement();
            String sql = "select ID,Name,CountryCode,District,Population from city";
            ResultSet rs = stmt.executeQuery(sql);

            /*
             *  Iterate the ResultSet and get each row
             *  Information.
             */
            while( rs.next() )
            {
                /*
                 * Retrieve by column name
                 */
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                String countryCode = rs.getString("CountryCode");
                String district = rs.getString("District");
                int population = rs.getInt("Population");

                /*
                 * Display values
                 */
                System.out.print("ID: " + id);
                System.out.print(", Name: " + name);
                System.out.print(", CountryCode: " + countryCode);
                System.out.print(", District: " + district);
                System.out.println(", Population: " + population);
            }
            rs.close();
        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if( stmt != null )
                {
                    stmt.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }
            try
            {
                if( connection != null )
                {
                    connection.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }

        }

    }
}

Output

ID: 2, Name: Qandahar, CountryCode: AFG, District: Qandahar, Population: 237500
ID: 3, Name: Herat, CountryCode: AFG, District: Herat, Population: 100
ID: 4, Name: Mazar-e-Sharif, CountryCode: AFG, District: Balkh, Population: 127800
ID: 5, Name: Amsterdam, CountryCode: NLD, District: Noord-Holland, Population: 50000
ID: 6, Name: Rotterdam, CountryCode: NLD, District: Zuid-Holland, Population: 4000
ID: 7, Name: Haag, CountryCode: NLD, District: Zuid-Holland, Population: 440900
ID: 8, Name: Utrecht, CountryCode: NLD, District: Utrecht, Population: 234323
ID: 9, Name: Eindhoven, CountryCode: NLD, District: Noord-Brabant, Population: 201843
ID: 10, Name: Tilburg, CountryCode: NLD, District: Noord-Brabant, Population: 193238
ID: 11, Name: Groningen, CountryCode: NLD, District: Groningen, Population: 172701