How to send/receive SMS text messages from Java through a database server

This guide will provide you a detailed information on how to send and receive SMS messages for Java using a mobile phone attached to your computer or through the Internet by connecting to an SMS service provider. Source code is also provided to help you exploit the possibilities offered by this solution.

One of the best methods to add SMS functionality to a Java application is to setup a configuration where you can send and receive text messages using database server (MySql, Oracle, MSSQL, etc.). Data tables inside the database server can be shared between your application and an SMS gateway thus your Java application can easily send messages by simple creating and reading database records.

To implement this method two database tables should be created inside the database server. One will be used for storing incoming messages, the other will be used for storing outgoing SMS messages. If the Ozeki NG SMS gateway receives a message it will store it into a database table called ozekimessagein. Similarly if you wish to send an SMS from Java, you should store it in a table called ozekimessageout. The Ozeki NG SMS gateway software will read the ozekimessageout database table periodically and if it finds a new record in it, it will send it as an SMS message (Figure 1). The table structure for the ozekimessagein and ozekimessageout database tables can be found at the following links:

• Table structure for sending and receiving SMS using Oracle
• Table structure for sending and receiving SMS using MySQL
• Table structure for sending and receiving SMS using MSSQL
• Table structure for sending and receiving SMS using Postgres
• Table structure for sending and receiving SMS using Sybase
• A similar database structure can be setup for other database servers

One of the advantages to this solution is that all of your messages will be automatically archived and you can easily supervise which message was delivered or processed successfully.

Prerequisites

To implement this solution, you need an SMS gateway. We recommend you to use Ozeki NG SMS Gateway because it is very reliable and efficient. You also need Java SE Development Kit. We recommend you to use Eclipse as your development environment, MySQL as your database server and the Java MySQL connector to be able to attach your Java application to MySQL. Of course you can use any other database server or developer tool.

Quick checklist:

• Ozeki NG SMS Gateway
• Java SE Development Kit
• Eclipse IDE for Java developers
• MySQL
• MySQL Connector

Installation and configuration guide

The following simple steps will guide you through the installation process.

In order to start the process first you need to install the Java SE development Kit. You find the free download link at the top of this page. Click on the link and follow the steps. After you are finished with the installation also install MySQL using the link provided above. Once MySQL is installed you need to create a database where the messages will be stored. Furthermore you also need to create two tables for the incoming (ozekimessagein) and for the outgoing SMS messages (ozekimessageout). Open MySQL and insert the following code.

     CREATE DATABASE `ozeki` /*!40100 DEFAULT CHARACTER SET utf8 */;
    CREATE TABLE  `ozeki`.`ozekimessagein` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `sender` varchar(30) DEFAULT NULL,
    `receiver` varchar(30) DEFAULT NULL,
    `msg` varchar(160) DEFAULT NULL,
    `senttime` varchar(100) DEFAULT NULL,
    `receivedtime` varchar(100) DEFAULT NULL,
    `operator` varchar(30) DEFAULT NULL,
    `msgtype` varchar(30) DEFAULT NULL,
    `reference` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
    )

CREATE TABLE  `ozeki`.`ozekimessageout` (
    `id` int NOT NULL AUTO_INCREMENT,
    `sender` varchar(30) DEFAULT NULL,
    `receiver` varchar(30) DEFAULT NULL,
    `msg` varchar(160) DEFAULT NULL,
    `senttime` varchar(100) DEFAULT NULL,
    `receivedtime` varchar(100) DEFAULT NULL,
    `operator` varchar(100) DEFAULT NULL,
    `msgtype` varchar(30) DEFAULT NULL,
    `preference` varchar(30) DEFAULT NULL,
    `status` varchar(30) DEFAULT NULL,
    `errormsg` varchar(250) DEFAULT NULL,
    PRIMARY KEY (`id`)
   )

CREATE USER 'ozekiuser'@'%' IDENTIFIED BY 'ozekipass';
	GRANT ALL PRIVILEGES ON *.* TO 'ozekiuser'@'%';
	FLUSH PRIVILEGES; 

The next step is to install the Ozeki NG SMS Gateway on your computer. The best way to do this is to follow the steps in the quick start guide. After the SMS gateway has been setup and connected to the mobile network, you have to configure the database connection in Ozeki NG. The database connection should point to the same database you will use in your Java application. The following webpages give you information on how to setup database connectivity in the SMS gateway:

• How to create a new database connection (general information).
  
• How to create a new database connection (mysql specific steps).
  

In short, you need to install a "Database user" in ozeki NG and you must set the Connection String Type property to MySQL than change Connection String to

Server=localhost; Database=ozeki; User=ozekiuser;
    Password=ozekipass;

The next stage in the implementation process is to unpack MySQLConnector/J and to create the project in Eclipse. To do this, open Eclipse and create a new project (File/New). After the project has benn created you have to add the mysql-connector-java-5.1.8-bin.zip file - which is one of MYSQL's components. If you want to use another database server, you can use a JDBC connection or any other native Java connector to attach your java program to the database. To implement SMS functionality a new class needs to be created. You can create this class by copying the following code into Eclipse:

Source code sample

Please feel free to use and modify this source code sample!

package hu.ozeki;

import java.sql.*;

public class Java_example_db_insert {

        public static void main(String[] args) {

                try {
                        Class.forName("com.mysql.jdbc.Driver").newInstance();

                        Statement smnt     = null;;
                        String dbHost      = "localhost";
                        String database    = "ozeki";
                        String dbUsername  = "ozekiuser";
                        String dbPassword  = "ozekipass";
                        String smsReceiver = "+441112222222";
                        String message           = "Hello world, this is my first SMS message";

                        String dbUrl = "jdbc:mysql://"+ dbHost +"/" + database +"";

                        Connection conn = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
                        smnt = conn.createStatement();

                        String sqlInsert =
                                "INSERT INTO "+
                "ozekimessageout (receiver,msg,status) "+
                "VALUES "+
                "('" + smsReceiver + "','"+ message +"','send')";

                        if(smnt.executeUpdate(sqlInsert) != 0)
                        {
                                System.out.println("OK");

                        }
                        else
                        {
                                System.out.println("ERROR");
                        }

                        smnt.close();
                        conn.close();
                }
                catch(Exception ex)
                {
                        System.out.println("Exception: " + ex.getMessage());
                }
        }

}

Note that you will probably change the database connection details: dbHost, database, dbUsername, dbPassword. You will also provide a different telephone number for sending the SMS message. The telephone number should be put into the smsReceiver string variable. Of course the message text in should also be changed from "Hello world..." to whatever you wish to send.