Exercise 11 - Scala application using JDBC using Scala IDE

eclipse
jdbc
scala
#1

Setup retail_db on your desktop. Here is the reference video

Sample Code

import java.sql.DriverManager
import java.sql.Connection

class Department(
departmentId: Int,
departmentName: String) {
override def toString = s"departmentId: $departmentId, departmentName: $departmentName"
}

object ScalaJdbcConnectSelect {

def main(args: Array[String]) {
// connect to the database named “mysql” on the localhost
val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://localhost/retail_db"
val username = "retail_dba"
val password = “itversity”

// there's probably a better way to do this
var connection:Connection = null
var departmentList: List[Department] = List()
try {
  // make the connection
  Class.forName(driver)
  connection = DriverManager.getConnection(url, username, password)
  // create the statement, and run the select query
  val statement = connection.createStatement()
  val resultSet = statement.executeQuery("SELECT department_id, department_name FROM departments")
  while ( resultSet.next() ) {
    val department_id = resultSet.getInt("department_id")
    val department_name = resultSet.getString("department_name")
    val department: Department = new Department(department_id, department_name)
    departmentList = departmentList:+ department
  }
  for(department: Department <- departmentList) {
    println(department)
  }
} catch {
  case e: Throwable => e.printStackTrace
}
connection.close()
}

}

Problem Statement:

  • Create scala based java application to get revenue per day per dapartments
    Hints
  • Define new plugin for mysql connector libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.24"
  • Run sbt package
  • Join 5 tables in mysql to get valid query
  • Use Scala IDE

Please provide the code and sample output of 1 date, all the departments and revenue

0 Likes

#2

import java.sql.DriverManager
import java.sql.Connection

class Department(
dailyDates: String,
departmentName: String,
revenuePerDay: Int) {
override def toString = s"dates: $dailyDates, departmentName: $departmentName, revenuePerDay: $revenuePerDay"
}

object ScalaJdbcConnectSelect {
def main(args: Array[String]) {

val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://nn01.itversity.com/retail_db"
val username = "retail_dba"
val password = "itversity"

var connection: Connection = null
var departmentList: List[Department] = List()

try {
  Class.forName(driver)
  connection = DriverManager.getConnection(url, username, password)
  val statement = connection.createStatement()
  val resultSet = statement.executeQuery("select o.order_date,d.department_name,sum(oi.order_item_subtotal)" +
    " from orders o join order_items oi " +
    " on oi.order_item_order_id=o.order_id " +
    " join products p " +
    " on p.product_id=oi.order_item_product_id " +
    " join categories c " +
    " on c.category_id=p.product_category_id " +
    " join departments d " +
    " on d.department_id=c.category_department_id " +
    " where order_status='COMPLETE' " +
    " group by o.order_date,d.department_name " +
    "LIMIT 10")

  while (resultSet.next()) {
    val daily_dates = resultSet.getString("o.order_date")
    val department_name = resultSet.getString("d.department_name")
    val revenue_per_day = resultSet.getInt("sum(oi.order_item_subtotal)")
    val department: Department = new Department(daily_dates, department_name, revenue_per_day)
    departmentList = departmentList :+ department
  }
  for (department: Department <- departmentList) {
    println(department)
  }

} catch {
  case e: Throwable => e.printStackTrace
} finally {
  connection.close()
}

}
}


E:\scalaWS\demoApplication>sbt compile
Java HotSpot™ 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
[info] Loading global plugins from C:\Users\snehasish.dutta.sbt\0.13\plugins
[info] Set current project to ScalaJdbcConnectSelect (in build file:/E:/scalaWS/demoApplication/)
[info] Updating {file:/E:/scalaWS/demoApplication/}demoapplication…
[info] Resolving jline#jline;2.12.1 …
[info] Done updating.
[info] Compiling 1 Scala source to E:\scalaWS\demoApplication\target\scala-2.11\classes…
[success] Total time: 3 s, completed Dec 16, 2016 2:32:36 PM

E:\scalaWS\demoApplication>sbt package
Java HotSpot™ 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
[info] Loading global plugins from C:\Users\snehasish.dutta.sbt\0.13\plugins
[info] Set current project to ScalaJdbcConnectSelect (in build file:/E:/scalaWS/demoApplication/)
[info] Packaging E:\scalaWS\demoApplication\target\scala-2.11\scalajdbcconnectselect_2.11-1.0.jar …
[info] Done packaging.
[success] Total time: 0 s, completed Dec 16, 2016 2:32:42 PM


[infosnehasish@gw01 ~]$ scala scalajdbcconnectselect_2.11-1.0.jar
dates: 2013-07-25 00:00:00.0, departmentName: Apparel, revenuePerDay: 3279
dates: 2013-07-25 00:00:00.0, departmentName: Development, revenuePerDay: 394
dates: 2013-07-25 00:00:00.0, departmentName: Fan Shop, revenuePerDay: 9798
dates: 2013-07-25 00:00:00.0, departmentName: Footwear, revenuePerDay: 3899
dates: 2013-07-25 00:00:00.0, departmentName: Golf, revenuePerDay: 2029
dates: 2013-07-25 00:00:00.0, departmentName: Outdoors, revenuePerDay: 627
dates: 2013-07-26 00:00:00.0, departmentName: Apparel, revenuePerDay: 8828
dates: 2013-07-26 00:00:00.0, departmentName: Development, revenuePerDay: 183
dates: 2013-07-26 00:00:00.0, departmentName: Fan Shop, revenuePerDay: 20847
dates: 2013-07-26 00:00:00.0, departmentName: Footwear, revenuePerDay: 5129

0 Likes

#3

#Code

import java.sql.DriverManager
import java.sql.Connection

class RevPerDepartment(
    revenue_per_day: String,
    orderDate: String,
    departmentName: String) {
  override def toString = s"revenue_per_day: $revenue_per_day, departmentName: $departmentName , order_date : $orderDate"
}

object SQLInScala {

  def main(args: Array[String]) {
    // connect to the database named "mysql" on the localhost
    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://nn01.itversity.com:3306/retail_db"
    val username = "retail_dba"
    val password = "itversity"

    // there's probably a better way to do this
    var connection: Connection = null

    var departmentList: List[RevPerDepartment] = List()

    val queryForRevenuePerDay = "select o.order_date , d.department_name , sum(oi.order_item_subtotal) from orders o join order_items oi on o.order_id = oi.order_item_order_id join products p on oi.order_item_product_id = p.product_id join categories c on p.product_category_id = c.category_id join departments d on c.category_department_id = d.department_id where o.order_status = 'COMPLETE' group by o.order_date, d.department_name limit 3;"

    try {
      // make the connection
      Class.forName(driver)
      connection = DriverManager.getConnection(url, username, password)

      // create the statement, and run the select query
      val statement = connection.createStatement()
      val resultSet = statement.executeQuery(queryForRevenuePerDay)
      while (resultSet.next()) {
        val rev_per_day = resultSet.getString("sum(oi.order_item_subtotal)")
        val order_date = resultSet.getString("o.order_date")
        val department_name = resultSet.getString("d.department_name")
        val department: RevPerDepartment = new RevPerDepartment(rev_per_day, order_date, department_name)
        departmentList = departmentList :+ department
      }
      for (department: RevPerDepartment <- departmentList) {
        println(department)
      }
    } catch {
      case e: Throwable => e.printStackTrace
    }
    connection.close()
  }
}

#Sample Output

[farhanmisarwala@gw01 ~]$ scala scala-sql_2.11-1.0.jar
revenue_per_day: 3279.5701179504395, departmentName: Apparel , order_date : 2013-07-25 00:00:00.0
revenue_per_day: 394.92999267578125, departmentName: Development , order_date : 2013-07-25 00:00:00.0
revenue_per_day: 9798.69021987915, departmentName: Fan Shop , order_date : 2013-07-25 00:00:00.0
0 Likes

#4

import java.sql.DriverManager
import java.sql.Connection

class Department(
dailyDates: String,
departmentName: String,
revenuePerDay: Int) {
override def toString = s"dates: $dailyDates, departmentName: $departmentName, revenuePerDay: $revenuePerDay"
}

object ScalaJdbcConnectSelect {
def main(args: Array[String]) {

val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://nn01.itversity.com/retail_db"
val username = "retail_dba"
val password = "itversity"

var connection: Connection = null
var departmentList: List[Department] = List()

try {
  Class.forName(driver)
  connection = DriverManager.getConnection(url, username, password)
  val statement = connection.createStatement()
  val resultSet = statement.executeQuery("select o.order_date,d.department_name,sum(oi.order_item_subtotal)" +
    " from orders o join order_items oi " +
    " on oi.order_item_order_id=o.order_id " +
    " join products p " +
    " on p.product_id=oi.order_item_product_id " +
    " join categories c " +
    " on c.category_id=p.product_category_id " +
    " join departments d " +
    " on d.department_id=c.category_department_id " +
    " where order_status='COMPLETE' " +
    " group by o.order_date,d.department_name " +
    "LIMIT 10")

  while (resultSet.next()) {
    val daily_dates = resultSet.getString("o.order_date")
    val department_name = resultSet.getString("d.department_name")
    val revenue_per_day = resultSet.getInt("sum(oi.order_item_subtotal)")
    val department: Department = new Department(daily_dates, department_name, revenue_per_day)
    departmentList = departmentList :+ department
  }
  for (department: Department <- departmentList) {
    println(department)
  }

} catch {
  case e: Throwable => e.printStackTrace
} finally {
  connection.close()
}

}
}


[parulshine92@gw01 parul]$ scala scalajdbcconnectselect_2.11-1.0.jar
orderDate: 2013-07-25 00:00:00.0, departmentName: Apparel, revenuePerDay: 3279
orderDate: 2013-07-25 00:00:00.0, departmentName: Development, revenuePerDay: 394
orderDate: 2013-07-25 00:00:00.0, departmentName: Fan Shop, revenuePerDay: 9798
orderDate: 2013-07-25 00:00:00.0, departmentName: Footwear, revenuePerDay: 3899
orderDate: 2013-07-25 00:00:00.0, departmentName: Golf, revenuePerDay: 2029
orderDate: 2013-07-25 00:00:00.0, departmentName: Outdoors, revenuePerDay: 627
orderDate: 2013-07-26 00:00:00.0, departmentName: Apparel, revenuePerDay: 8828
orderDate: 2013-07-26 00:00:00.0, departmentName: Development, revenuePerDay: 183
orderDate: 2013-07-26 00:00:00.0, departmentName: Fan Shop, revenuePerDay: 20847
orderDate: 2013-07-26 00:00:00.0, departmentName: Footwear, revenuePerDay: 5129

0 Likes

#5

Code:

package com.scala.jdbc

import java.sql.DriverManager
import java.sql.Connection

class Retail_Export_Jason_Rdpd(
    order_date: String,
    department_name: String,
    revenue: Float) {
  override def toString = s"order_date: $order_date, department_name: $department_name, revenue: $revenue"
}

object ScalaJdbcConnectSelect {

  def main(args: Array[String]) {
    // connect to the database named "mysql" on the localhost
    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://nn01.itversity.com:3306/retail_db"
    val username = "retail_dba"
    val password = "itversity"

    // there's probably a better way to do this
    var connection: Connection = null

    var retail_export_jason_rdpd_list: List[Retail_Export_Jason_Rdpd] = List()

    try {
      // make the connection
      Class.forName(driver)
      connection = DriverManager.getConnection(url, username, password)

      // create the statement, and run the select query
      val statement = connection.createStatement()
      val resultSet = statement.executeQuery("SELECT o.order_date, d.department_name, round(sum(oi.order_item_subtotal),2) AS revenue" +
        " FROM orders o JOIN order_items oi ON o.order_id = oi.order_item_order_id" +
        " JOIN products p ON oi.order_item_product_id = p.product_id" +
        " JOIN categories c ON c.category_id = p.product_category_id" +
        " JOIN departments d ON d.department_id = c.category_department_id" +
        " WHERE o.order_status = 'COMPLETE' " +
        " GROUP BY o.order_date, d.department_name LIMIT 5;")
      while (resultSet.next()) {
        val order_date = resultSet.getString("order_date")
        val department_name = resultSet.getString("department_name")
        val revenue = resultSet.getFloat("revenue")
        val retail_export_jason_rdpd: Retail_Export_Jason_Rdpd = new Retail_Export_Jason_Rdpd(order_date, department_name, revenue)
        retail_export_jason_rdpd_list = retail_export_jason_rdpd_list :+ retail_export_jason_rdpd
      }
      for (retail_export_jason_rdpd: Retail_Export_Jason_Rdpd <- retail_export_jason_rdpd_list) {
        println(retail_export_jason_rdpd)
      }
    } catch {
      case e: Throwable => e.printStackTrace
    }
    connection.close()
  }
}

Output

[jasonbourne@gw01 ~]$ scala scalajdbcmysql_2.11-1.0.jar
order_date: 2013-07-25 00:00:00.0, department_name: Apparel, revenue: 3279.57
order_date: 2013-07-25 00:00:00.0, department_name: Development, revenue: 394.93
order_date: 2013-07-25 00:00:00.0, department_name: Fan Shop, revenue: 9798.69
order_date: 2013-07-25 00:00:00.0, department_name: Footwear, revenue: 3899.61
order_date: 2013-07-25 00:00:00.0, department_name: Golf, revenue: 2029.72
0 Likes

#6

package com.scala

import java.sql.DriverManager
import java.sql.Connection

class Retail_Reprt(
order_date: String,
department_name: String,
revenue: Float) {
override def toString = s"order_date: $order_date, department_name: $department_name, revenue: $revenue"
}

object SaclaJoin {

def main(args: Array[String]) {
// connect to the database named “mysql” on the localhost
val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://nn01.itversity.com:3306/retail_db"
val username = "retail_dba"
val password = “itversity”

// there's probably a better way to do this
var connection: Connection = null

var Retail_Reprt_list: List[Retail_Reprt] = List()

try {
  // make the connection
  Class.forName(driver)
  connection = DriverManager.getConnection(url, username, password)

  // create the statement, and run the select query
  val statement = connection.createStatement()
  val resultSet = statement.executeQuery("SELECT o.order_date, d.department_name, round(sum(oi.order_item_subtotal),2) AS revenue" +
    " FROM orders o JOIN order_items oi ON o.order_id = oi.order_item_order_id" +
    " JOIN products p ON oi.order_item_product_id = p.product_id" +
    " JOIN categories c ON c.category_id = p.product_category_id" +
    " JOIN departments d ON d.department_id = c.category_department_id" +
    " WHERE o.order_status = 'COMPLETE' " +
    " GROUP BY o.order_date, d.department_name LIMIT 5;")
  while (resultSet.next()) {
    val order_date = resultSet.getString("order_date")
    val department_name = resultSet.getString("department_name")
    val revenue = resultSet.getFloat("revenue")
    val Retail_Reprt: Retail_Reprt = new Retail_Reprt(order_date, department_name, revenue)
    Retail_Reprt_list = Retail_Reprt_list :+ Retail_Reprt
  }
  for (retail_Reprt: Retail_Reprt <- Retail_Reprt_list) {
    println(retail_Reprt)
  }
} catch {
  case e: Throwable => e.printStackTrace
}
connection.close()

}
}

Output

[saswat232@gw01 ~]$ scala scalamysql_2.11-1.0.jar
order_date: 2013-07-25 00:00:00.0, department_name: Apparel, revenue: 3279.57
order_date: 2013-07-25 00:00:00.0, department_name: Development, revenue: 394.93
order_date: 2013-07-25 00:00:00.0, department_name: Fan Shop, revenue: 9798.69
order_date: 2013-07-25 00:00:00.0, department_name: Footwear, revenue: 3899.61
order_date: 2013-07-25 00:00:00.0, department_name: Golf, revenue: 2029.72

0 Likes

#7

import java.sql.DriverManager
import java.sql.Connection

class Department(
orderDate: String,
departmentName: String,
revenuePerDay: Int) {
override def toString = s"orderDate: $orderDates, departmentName: $departmentName, revenuePerDay: $revenuePerDay"
}

object ScalaJdbcConnectSelect {
def main(args: Array[String]) {

val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://nn01.itversity.com/retail_db"
val username = "retail_dba"
val password = “itversity”

var connection: Connection = null
var departmentList: List[Department] = List()

try {
Class.forName(driver)
connection = DriverManager.getConnection(url, username, password)
val statement = connection.createStatement()
val resultSet = statement.executeQuery(“select o.order_date,d.department_name,sum(oi.order_item_subtotal)” +
" from orders o join order_items oi " +
" on oi.order_item_order_id=o.order_id " +
" join products p " +
" on p.product_id=oi.order_item_product_id " +
" join categories c " +
" on c.category_id=p.product_category_id " +
" join departments d " +
" on d.department_id=c.category_department_id " +
" where order_status=‘COMPLETE’ " +
" group by o.order_date,d.department_name " +
“LIMIT 10”)

while (resultSet.next()) {
val order_date = resultSet.getString(“o.order_date”)
val department_name = resultSet.getString(“d.department_name”)
val rev_per_day = resultSet.getInt(“sum(oi.order_item_subtotal)”)
val department: Department = new Department(order_date, department_name, rev_per_day)
departmentList = departmentList :+ department
}
for (department: Department <- departmentList) {
println(department)
}

} catch {
case e: Throwable => e.printStackTrace
} finally {
connection.close()
}
}
}

[mahesh007@gw01 ~]$ scala scalajdbcconnectselect_2.11-1.0.jar
orderDate: 2013-07-25 00:00:00.0, departmentName: Apparel, revenuePerDay: 3279
orderDate: 2013-07-25 00:00:00.0, departmentName: Development, revenuePerDay: 394
orderDate: 2013-07-25 00:00:00.0, departmentName: Fan Shop, revenuePerDay: 9798
orderDate: 2013-07-25 00:00:00.0, departmentName: Footwear, revenuePerDay: 3899
orderDate: 2013-07-25 00:00:00.0, departmentName: Golf, revenuePerDay: 2029
orderDate: 2013-07-25 00:00:00.0, departmentName: Outdoors, revenuePerDay: 627
orderDate: 2013-07-26 00:00:00.0, departmentName: Apparel, revenuePerDay: 8828
orderDate: 2013-07-26 00:00:00.0, departmentName: Development, revenuePerDay: 183
orderDate: 2013-07-26 00:00:00.0, departmentName: Fan Shop, revenuePerDay: 20847
orderDate: 2013-07-26 00:00:00.0, departmentName: Footwear, revenuePerDay: 5129

0 Likes