MySQL-Ruby Tutorial and Code Samples

Introduction

This short article demonstrates how to write a Ruby implementation of the DAO (Data Access Object) design pattern for interacting with a MySQL database using the mysql-ruby library.

Installing mysql-ruby

Download mysql-ruby from http://www.tmtm.org/en/mysql/ruby/ and follow the installation instructions on that page to make and install Ruby from source.

Set the RUBYLIB environment variable so that mysql-ruby can be located by the Ruby interpreter. For example:

export RUBYLIB=~/mysql-ruby-2.8/

Write a DTO class

Write a class to represent the data for a single row in a table. This is often referred to as a Data Transfer Object (DTO) or Value Object (VO). This class simply contains a single attribute for each column in the table. We will pass instances of this class to the DAO object when we want to insert, update or delete a row and we will receive instances of this class when we invoke a 'finder' on the DAO object.

Example of simple DTO class:

class Country

def id; @id; end # The getter method for @id
def name; @name; end # The getter method for @name

def id=(value) # The setter method for @id
@id = value
end

def name=(value) # The setter method for @name
@name = value
end
end

Writing the DAO class

The DAO class will contain an insert, update and delete methods and a number of finder methods which perform pre-defined select queries.

The first step to writing the DAO class it to provide a constructor that stores the mysql-ruby connection object.

class CountryDao

  def initialize(my)
    @my = my;
  end

  # other methods will go here shortly ...
end

Example DAO insert method

The insert method creates a prepared statement (a mysql-ruby Stmt object that can have parameters bound to it before execution). Here is a sample insert method that binds parameters to the insert statement based on the supplied DTO, which should be an instance of the Country class defined earlier in the tutorial.

def insert(dto)
  sql = "INSERT INTO country ( id, name ) VALUES ( ?, ? )"
  st = @my.prepare(sql)
  st.execute(dto.id, dto.name)
  st.close
end

Example DAO update method

The update method is very similar to the insert method.

def update(dto)
  sql = "UPDATE country SET id = ?, name = ? WHERE id = ?";
  st = @my.prepare(sql);
  st.execute(dto.id, dto.name, dto.id);
  st.close
end

Example DAO delete method

The delete method is very similar to the insert and update methods method.

def delete(dto)
  sql = "DELETE FROM country WHERE id = ?"
  st = @my.prepare(sql)
  st.execute(dto.id)
  st.close
end

Writing the DAO finder methods

Finder methods execute a SELECT statement and then fetch results from the database, creating a DTO instance for each row.

def findAll();
  sql = "SELECT id, name FROM country ORDER BY id"
  st = @my.prepare(sql)
  st.execute()
  result = fetchResults(st)
  st.close
  return result
end

def findWhereNameEquals(name);
  sql = "SELECT id, name FROM country WHERE name = ?"
  st = @my.prepare(sql)
  st.execute(name)
  result = fetchResults(st)
  st.close
  return result
end

The finder methods rely on a shared fetchResults() method.

def fetchResults(stmt)
  rows = []
  while row = stmt.fetch do
    dto = Country.new
    dto.id = row[0]
    dto.name = row[1]
    rows << dto
  end
  return rows
end

Example client code to invoke the Ruby DAO

It is necessary for the Ruby script to import the mysql-ruby library and the DAO/DTO classes.

require "mysql"
require "CountryDao"
require "Country"

The following lines of code establish a connection with a MySQL database.

my = Mysql.new("localhost", "myusername", "mypassword", "mydatabase")
my.autocommit(false);

Inserting a row

The following code creates and populates a Country DTO and then passes it the DAO which inserts a row into the database.

country = Country.new();
country.id = 1;
country.name = "United States";
countryDao = CountryDao.new(my);
countryDao.insert(country);

Finding rows

The following code gets a list of countries from the database.

countryDao.findAll.each() { |y|
printf "country: %s\n", y.name
}

The following code retrieves a country by name.

usa = countryDao.findWhereNameEquals("United States")

Updating a row

usa.name = "United States of America"
countryDao.update(usa)

Ruby DAO Code Generator

The code samples in this tutorial were generated directly from a database schema using FireStorm/DAO Enterprise Edition 4.0 which is available for download free of charge here.