dumptable.scala

/**
 * Dumps (possibly large) database table to SQL file or more files
 * according to MaxRecordsPerFile parameter.  
 * @author Radek Beran
 */
import java.io._
import java.sql._

// --- SCRIPT PARAMETERS ---
val DriverName = "com.mysql.jdbc.Driver"
val ConnectionUrl = "jdbc:mysql://localhost:3306/database"
val UserName = "root"; 
val Password = "root"
val TableName = "address"
val TableColumns = "ID, ADDRESS, CITY, COUNTRY, REGION, ZIP"
val SelectSql = "SELECT " + TableColumns + " FROM " + TableName + 
  " ORDER BY ID"
val MaxRecordsPerFile = 1000000L
val OutputFileEncoding = "UTF-8"

def buildRecord(rs: ResultSet): String = {
  val sqlBuilder = new StringBuilder("INSERT INTO " + TableName + 
    " (" + TableColumns + ") VALUES (")
  sqlBuilder.append(getValue(rs.getLong("ID")) + ", ")
  sqlBuilder.append(getValueQuoted(rs.getString("ADDRESS")) + ", ")
  sqlBuilder.append(getValueQuoted(rs.getString("CITY")) + ", ")
  sqlBuilder.append(getValueQuoted(rs.getString("COUNTRY")) + ", ")
  sqlBuilder.append(getValueQuoted(rs.getString("REGION")) + ", ")
  sqlBuilder.append(getValueQuoted(rs.getString("ZIP")))
  sqlBuilder.append(");")
  sqlBuilder.toString
}

// --- SCRIPT INTERNALS ---
val startNanos = System.nanoTime()
using(getConnection()) {
  con =>
    using(con.prepareStatement(SelectSql, 
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
      stmt =>
        stmt.setFetchSize(Integer.MIN_VALUE);
        using(stmt.executeQuery()) {
          rs => processRecords(rs, None, 0, 0L)
        }
    }
}
println("Completed in " + ((System.nanoTime() - startNanos) / 1000000000) + 
  " seconds")
  
@annotation.tailrec
def processRecords(rs: ResultSet, fileOs: Option[OutputStreamWriter], 
  filesWritten: Int, recordsWrittenToFile: Long): Unit = {
  if (rs.next()) {
    val record = buildRecord(rs)
    if (fileOs.isEmpty || recordsWrittenToFile == MaxRecordsPerFile) {
      // new file must be created
      closeIfOpen(fileOs)
      processRecords(rs, 
        Some(appendToFile(initFile(filesWritten + 1), record)), 
        filesWritten + 1, 1)
    } else {
      processRecords(rs, Some(appendToFile(fileOs.get, record)), 
        filesWritten, recordsWrittenToFile + 1)
    }
  } else closeIfOpen(fileOs)
}

def getValueQuoted(value: Any, useQuotes: Boolean = false): String = 
  getValue(value, true) 
  
def getValue(value: Any, useQuotes: Boolean = false): String =
  if (value == null) "NULL"
  else if (useQuotes) "'" + escapeSql(value + "") + "'"
  else value + ""
    
def escapeSql(str: String): String = 
  if (str == null) null else str.replaceAll("'", "''")

def getConnection(): Connection = {
  Class.forName(DriverName)
  DriverManager.getConnection(ConnectionUrl, UserName, Password)
}

def using[A <: { def close(): Unit }, B](resource: A)(f: A => B): B =
  try {f(resource)} finally {if (resource != null) {resource.close()}}
    
def closeIfOpen[A <: { def close(): Unit }](resource: Option[A]): Unit = 
  if (!resource.isEmpty) resource.get.close()

def initFile(fileCount: Long): OutputStreamWriter =
  new OutputStreamWriter(new FileOutputStream(
    new File(TableName + fileCount + ".sql")), OutputFileEncoding)

def appendToFile(fileOs: OutputStreamWriter, record: String): 
    OutputStreamWriter = {
  fileOs.append(record + "\n")
  fileOs
}