database.java

使用smslib和GSP modem 发送和接收手机短息

// SMSLib for Java v3
// A Java API library for sending and receiving SMS via a GSM modem
// or other supported gateways.
// Web Site: http://www.smslib.org
//
// Copyright (C) 2002-2008, Thanasis Delenikas, Athens/GREECE.
// SMSLib is distributed under the terms of the Apache License version 2.0
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package org.smslib.smsserver.interfaces;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.smslib.smsserver.InterfaceTypes;

/**
 * Interface for database communication with SMSServer. <br />
 * Inbound messages and calls are logged in special tables, outbound messages
 * are retrieved from another table.
 */
public class Database extends org.smslib.smsserver.AInterface
{
	public Database(String infId, Properties props, org.smslib.smsserver.SMSServer server, InterfaceTypes type)
	{
		super(infId, props, server, type);
		description = "Default database interface.";
	}

	public void start() throws Exception
	{
		Connection con;
		Statement cmd;
		Class.forName(getProperty("driver"));
		con = getDbConnection();
		if (con != null)
		{
			cmd = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			cmd.executeUpdate("update " + getProperty("tables.sms_out", "smsserver_out") + " set status = 'U' where status = 'Q'");
			con.commit();
			cmd.close();
			con.close();
		}
		logInfo("SMSServer: Interface Database started.");
	}

	public void stop() throws Exception
	{
		logInfo("SMSServer: Interface Database started.");
	}

	public void CallReceived(String gtwId, String callerId) throws Exception
	{
		Connection con;
		Statement cmd;
		ResultSet rs;
		con = getDbConnection();
		cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		rs = cmd.executeQuery("select * from " + getProperty("tables.calls", "smsserver_calls") + " where id = -1");
		rs.moveToInsertRow();
		rs.updateTimestamp("call_date", new Timestamp(new java.util.Date().getTime()));
		rs.updateString("gateway_id", gtwId);
		rs.updateString("caller_id", callerId);
		rs.insertRow();
		con.commit();
		rs.close();
		cmd.close();
		con.close();
	}

	public void MessagesReceived(List msgList) throws Exception
	{
		org.smslib.InboundMessage msg;
		Connection con;
		Statement cmd;
		ResultSet rs;
		con = getDbConnection();
		cmd = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		rs = cmd.executeQuery("select * from " + getProperty("tables.sms_in", "smsserver_in") + " where id = -1");
		for (int i = 0; i < msgList.size(); i++)
		{
			msg = (org.smslib.InboundMessage) msgList.get(i);
			if ((msg.getType() == org.smslib.MessageTypes.INBOUND) || (msg.getType() == org.smslib.MessageTypes.STATUSREPORT))
			{
				rs.moveToInsertRow();
				rs.updateInt("process", 0);
				rs.updateString("originator", msg.getOriginator());
				if (msg.getType() == org.smslib.MessageTypes.INBOUND) rs.updateString("type", "I");
				else if (msg.getType() == org.smslib.MessageTypes.STATUSREPORT) rs.updateString("type", "S");
				if (msg.getEncoding() == org.smslib.MessageEncodings.ENC7BIT) rs.updateString("encoding", "7");
				else if (msg.getEncoding() == org.smslib.MessageEncodings.ENC8BIT) rs.updateString("encoding", "8");
				else if (msg.getEncoding() == org.smslib.MessageEncodings.ENCUCS2) rs.updateString("encoding", "U");
				if (msg.getDate() != null) rs.updateTimestamp("message_date", new Timestamp(msg.getDate().getTime()));
				rs.updateTimestamp("receive_date", new Timestamp(new java.util.Date().getTime()));
				rs.updateString("text", msg.getText().replaceAll("'", "''"));
				rs.updateString("gateway_id", msg.getGatewayId());
				rs.insertRow();
			}
		}
		rs.close();
		cmd.close();
		con.commit();
		con.close();
	}

	public List getMessagesToSend() throws Exception
	{
		ArrayList msgList = new ArrayList();
		org.smslib.OutboundMessage msg;
		Connection con;
		Statement cmd;
		ResultSet rs;
		int msgCount;
		msgCount = 0;
		con = getDbConnection();
		if (con != null)
		{
			cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			if (getProperty("type").equalsIgnoreCase("mysql")) rs = cmd.executeQuery("select * from " + getProperty("tables.sms_out", "smsserver_out") + " where status = 'U' order by priority, id");
			else rs = cmd.executeQuery("select *, case when priority = 'H' then 1 when priority = 'N' then 2 when priority = 'L' then 3 else 4 end as prioritynum from " + getProperty("tables.sms_out", "smsserver_out") + " where status = 'U' order by prioritynum, id");
			while (rs.next())
			{
				msg = new org.smslib.OutboundMessage(rs.getString("recipient"), rs.getString("text"));
				if (rs.getString("priority").equalsIgnoreCase("L")) msg.setPriority(org.smslib.MessagePriorities.LOW);
				else if (rs.getString("priority").equalsIgnoreCase("N")) msg.setPriority(org.smslib.MessagePriorities.NORMAL);
				else if (rs.getString("priority").equalsIgnoreCase("H")) msg.setPriority(org.smslib.MessagePriorities.HIGH);
				if (server.checkPriorityTimeFrame(msg.getPriority()))
				{
					msg.setId("" + rs.getString("id"));
					if (rs.getString("encoding").equals("7")) msg.setEncoding(org.smslib.MessageEncodings.ENC7BIT);
					else if (rs.getString("encoding").equals("8")) msg.setEncoding(org.smslib.MessageEncodings.ENC8BIT);
					else msg.setEncoding(org.smslib.MessageEncodings.ENCUCS2);
					if (rs.getInt("status_report") == 1) msg.setStatusReport(true);
					if (rs.getInt("flash_sms") == 1) msg.setFlashSms(true);
					if (rs.getInt("src_port") != -1)
					{
						msg.setSrcPort(rs.getInt("src_port"));
						msg.setDstPort(rs.getInt("dst_port"));
					}
					if (rs.getString("originator") != null) msg.setFrom(rs.getString("originator"));
					msg.setGatewayId(rs.getString("gateway_id"));
					msgList.add(msg);
					rs.updateString("status", "Q");
					rs.updateRow();
					con.commit();
					msgCount++;
					if (msgCount > Integer.parseInt(getProperty("batch_size"))) break;
				}
			}
			rs.close();
			cmd.close();
			con.close();
		}
		return msgList;
	}

	public void markMessage(org.smslib.OutboundMessage msg) throws Exception
	{
		Connection con = null;
		ResultSet rs = null;
		Statement cmd = null;
		con = getDbConnection();
		cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		rs = cmd.executeQuery("select * from " + getProperty("tables.sms_out", "smsserver_out") + " where id = " + msg.getId());
		if (rs.next())
		{
			if (msg.getMessageStatus() == org.smslib.MessageStatuses.SENT)
			{
				rs.updateString("status", "S");
				rs.updateTimestamp("sent_date", new Timestamp(msg.getDispatchDate().getTime()));
				rs.updateString("gateway_id", msg.getGatewayId());
				rs.updateString("ref_no", msg.getRefNo());
			}
			else if (msg.getMessageStatus() == org.smslib.MessageStatuses.FAILED)
			{
				int errors = rs.getInt("errors");
				errors++;
				rs.updateInt("errors", errors);
				if (errors > Integer.parseInt(getProperty("retries", "2"))) rs.updateString("status", "F");
				else rs.updateString("status", "U");
			}
			rs.updateRow();
			con.commit();
			rs.close();
			cmd.close();
			con.close();
		}
	}

	private Connection getDbConnection()
	{
		Connection dbCon = null;
		try
		{
			dbCon = DriverManager.getConnection(getProperty("url"), getProperty("username", ""), getProperty("password", ""));
			dbCon.setAutoCommit(false);
		}
		catch (SQLException e)
		{
			logError("SMSServer: Database Interface: error!", e);
		}
		return dbCon;
	}
}