📄 querydatabase.java~17~
字号:
package mmsproject;
import java.sql.*;
import java.io.*;
import java.util.*;
import mmsproject.model.*;
import mmsproject.common.GetProperties;
import mmsproject.common.ConnectionManager;
/**
* <p>Title: 彩信发送接收项目</p>
* <p>Description: 用于查询数据库中有无需要发送的信息，如果有，则将其发送</p>
* <p>Copyright: Copyright (c) 2004</p>
* <p>Company: </p>
* @author tomato
* @version 1.0
*/
public class QueryDataBase implements Runnable {
//控制线程执行的标记
public boolean boolFlag = true;
public QueryDataBase() {
}
/**
* 用于轮询数据库的线程执行的方法
*/
public void run() {
Connection conn = null;
CallableStatement stmt = null;
//不断查询数据库有无需要发送的彩信
while (boolFlag) {
//不断执行接收分发存储过程 sp_get_receive
try {
conn = ConnectionManager.getSqlConnection();
stmt = conn.prepareCall("{call sp_get_receive}");
stmt.execute();
} catch (Exception e) {
e.printStackTrace();
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(temp+e.getMessage() + "get_all_receive错误:" + "\n" + temp);
}
//检测多行文本框中的数据大小，如果过大则清空
if (FrmMMSManage.m_txtStatus.getText().length() > 100000) {
FrmMMSManage.m_txtStatus.setText("");
}
//1.取得需要发送的彩信
Vector vData = new Vector();
try {
vData = searchForSend();
} catch (Exception ex) {
ex.printStackTrace();
}
//3.将重数据库中取得的信息发送出去,并且将其存入表TB_SEND,并通过SendId将缓存表中发送未超时的数据删除
//取回缓存数据
if (!vData.isEmpty()) {
Enumeration enuData = vData.elements();
while (enuData.hasMoreElements()) {
SendData sd = (SendData) enuData.nextElement();
//发送数据到彩信中心
MyMMSender sender = new MyMMSender();
sender.send(sd);
}
}
//每隔5秒运行一次
try {
//设置查询数据库时间间隔
//此处应该替换为读取配置文件的属性取得
Thread.sleep(Integer.parseInt(GetProperties.QueryDelay));
} catch (Exception w) {
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(temp + w.getMessage() +
"用于不断查询数据库的线程执行的方法" + "\n");
}
}
}
/**
* 通过SendId将缓存表中的数据删除
* @param nSendId int
*/
public static void deleteTempData(int nSendId) {
//创建查询语句
String strSql = "DELETE TB_MMS_SEND_TEMP WHERE SendID=" + nSendId;
Connection conn = null;
Statement stmt = null;
try {
conn = ConnectionManager.getSqlConnection();
stmt = conn.createStatement();
stmt.execute(strSql);
} catch (Exception ex) {
//显示出错信息
//ex.printStackTrace();
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(ex.getMessage() + "Querybase查询数据库" + "\n" + temp);
} finally {
//关闭数据库连接
ConnectionManager.closeSqlStatement(stmt);
ConnectionManager.closeSqlConnection(conn);
}
}
/**
* 通过SendId将缓存表（TB_SEND_TEMP）中的数据存入历史表（TB_SEND）中
* @param nSendId int
*/
public static void moveTempData(int nSendId) {
//创建查询语句
//insert into tb_send_temp select * from tb_send where sendid=
//String strSql = "SELECT * INTO TB_SEND FROM TB_SEND_TEMP WHERE SendId=" + nSendId;
String strSql = "insert into TB_MMS_SEND select * from TB_MMS_SEND_TEMP where SendId=" +
nSendId;
Connection conn = null;
Statement stmt = null;
try {
conn = ConnectionManager.getSqlConnection();
stmt = conn.createStatement();
stmt.execute(strSql);
} catch (Exception ex) {
//显示出错信息(迟点再改...)
ex.printStackTrace();
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(ex.getMessage() + "Querybase查询数据库" + "\n" + temp);
} finally {
//关闭数据库连接
ConnectionManager.closeSqlStatement(stmt);
ConnectionManager.closeSqlConnection(conn);
}
}
/**
* 寻找需要发送的信息(将所有需要发送的信息缓存到vector中)
*/
private Vector searchForSend() {
//创建vector对象用于缓存取得的数据
Vector vData = new Vector();
//创建查询语句
//--------------------------------------------------------------------------//
//这段sql语句需要修改//
String strSqlSend = "SELECT"
+ " top 5 s.*,"
+ " sys.contentpath+service.contentpath+pic.ContentPath as PicPath,"
+ " sys.contentpath+service.contentpath+ring.ContentPath as RingPath,"
+ " sys.contentpath+service.contentpath+mix.ContentPath as MixPath"
+ " FROM "
+ " TB_MMS_SEND_TEMP s "
+ " left join"
+ " TB_MMS_PIC_CONTENT pic"
+ " on"
+ " s.PicContentID=pic.PicID"
+ " left join"
+ " TB_MMS_RING_CONTENT ring"
+ " on"
+ " s.RingContentID=ring.RingID"
+ " left join"
+ " TB_MMS_MIX_CONTENT mix"
+ " on"
+ " s.MixContentID=mix.MixID"
+ " left join TB_SERVICES service"
+ " on"
+ " s.MyServiceCode=service.MyServiceCode,"
+ " TB_SYSTEM_DEFINED sys"
+ " WHERE "
+ " s.FlagSend=? "
+ " AND s.TimeToSend<=getdate() order by s.sendID";
Connection conn = null;
PreparedStatement stmt = null;
//创建结果集
ResultSet rsSend = null;
try {
conn = ConnectionManager.getSqlConnection();
stmt = conn.prepareStatement(strSqlSend);
stmt.setInt(1, 0);
rsSend = stmt.executeQuery();
while (rsSend.next()) {
//创建需要发送数据的对象模型
SendData sd = new SendData();
sd.TransactionID = rsSend.getString("TransactionID");
sd.SendID = rsSend.getInt("SendID");
sd.ServiceCode = rsSend.getString("ServiceCode").trim();
sd.MMContentType = rsSend.getInt("MMContentType");
sd.MessageClass = rsSend.getInt("MessageClass");
sd.Title = rsSend.getString("Title");
sd.RecvPhoneNum = rsSend.getString("RecvPhoneNum").trim();
//2005-07-11修改
//如果是有"+86"则去掉
if(sd.RecvPhoneNum.indexOf("+86")>=0){
sd.RecvPhoneNum=sd.RecvPhoneNum.substring(3);
}
//sd.PrivacyNum = rsSend.getString("PrivacyNum");
//sd.CopyNum = rsSend.getString("CopyNum");
//linkid is added 2005
try {
sd.LinkId = rsSend.getString("LinkID").trim();
} catch (Exception ex) {
sd.LinkId = "";
}
//sd.IsShowPhone = (!rsSend.getBoolean("IsShowPhone"));
sd.IsShowPhone = true;
/*设置VASP所提交MM的付费方，例如，发送方、接收方、发送方和接收方或两方均不付费，
//可选，0：Sender、1：Recipients、2：Both、3：Neither、4：ThirdParty*/
sd.ChangeType = rsSend.getInt("ChargedParty");
sd.FromPhoneNum = rsSend.getString("FromPhoneNum").trim();
// /*消息的优先级（重要性）（0=最低优先级，1=正常，2=紧急），byte类型的值，可选*/
sd.Priority = 1;
//计费号码
if(sd.ChangeType==1){
sd.ChargedNumber = sd.RecvPhoneNum;
}else //if(sd.ChangeType==0){
{ sd.ChargedNumber = sd.FromPhoneNum;
}
//需要处理路径为空的问题哦.......2005-07-05
//主路径
String MixContentPath = "";
try {
MixContentPath = rsSend.getString("MixPath").trim();
} catch (Exception ex) {
ex.printStackTrace();
}
//如果内容路径
if (MixContentPath.equals("")) {
String PicContentPath = "";
try {
PicContentPath = rsSend.getString("PicPath").trim();
} catch (Exception ex) {
ex.printStackTrace();
}
String RingContentPath = "";
try {
RingContentPath = rsSend.getString("RingPath").trim();
} catch (Exception ex) {
ex.printStackTrace();
}
try {
if (!PicContentPath.equals("")) {
if (!RingContentPath.equals("")) {
sd.ContentNames = new String[2];
sd.ContentPaths = new String[2];
sd.ContentPaths[0] = PicContentPath;
sd.ContentPaths[1] = RingContentPath;
sd.ContentNames[0] = PicContentPath.substring(PicContentPath.
lastIndexOf("\\"));
sd.ContentNames[1] = RingContentPath.substring(PicContentPath.
lastIndexOf("\\"));
} else {
sd.ContentNames = new String[1];
sd.ContentPaths = new String[1];
sd.ContentPaths[0] = PicContentPath;
sd.ContentNames[0] = PicContentPath.substring(PicContentPath.
lastIndexOf("\\"));
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
} else {
try {
//设置彩信标题为文件夹的名称
//sd.Title = MixContentPath.substring(MixContentPath.lastIndexOf("\\") + 1);
File MyDir = new File(MixContentPath);
//String[] strFileNames = MyDir.list();
sd.ContentNames = MyDir.list();
sd.ContentPaths = new String[sd.ContentNames.length];
for (int i = 0; i < sd.ContentNames.length; i++) {
sd.ContentPaths[i] = MixContentPath + "\\" + sd.ContentNames[i];
System.out.println(sd.ContentPaths[i]);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
vData.add(sd);
}
} catch (Exception ex) {
//显示出错信息
ex.printStackTrace();
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(temp + ex.getMessage() + "Querybase查询数据库" + "\n");
} finally {
//关闭数据库连接
ConnectionManager.closeSqlResultSet(rsSend);
ConnectionManager.closeSqlPreparedStatement(stmt);
ConnectionManager.closeSqlConnection(conn);
}
return vData;
}
/**
* 更新数据库中的发送状态(参数见api)
*/
public static void updateSendStatus(String strCode, int nSendId) {
String strSql = "UPDATE TB_MMS_SEND_TEMP SET "
+ "RetCode=?"
+ " WHERE SendId=?";
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = ConnectionManager.getSqlConnection();
stmt = conn.prepareStatement(strSql);
stmt.setString(1, strCode);
stmt.setInt(2, nSendId);
stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(temp + e.getMessage() + "mymmssender更新数据库" + "\n");
} finally {
ConnectionManager.closeSqlPreparedStatement(stmt);
ConnectionManager.closeSqlConnection(conn);
}
}
/**
* 更新数据库中的发送状态(参数见api)
*/
public static void GetSystemDefiniens() {
String strSql = "select * from TB_SYSTEM_DEFINED";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = ConnectionManager.getSqlConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(strSql);
while (rs.next()) {
MMSManage.strContentPath = rs.getString("ContentPath");
MMSManage.strReceivePath = rs.getString("ReceivedPath");
}
} catch (Exception e) {
e.printStackTrace();
String temp = FrmMMSManage.m_txtStatus.getText();
FrmMMSManage.m_txtStatus.setText(e.getMessage() + "mymmssender获取系统设置" + "\n" + temp);
} finally {
ConnectionManager.closeSqlStatement(stmt);
ConnectionManager.closeSqlConnection(conn);
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -