dbservice.java

用ExtJS做的一个网页聊天工具

package com.shyhao.qq.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class DBService {

	// 数据库连接参数
	private String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private String URL = "jdbc:sqlserver://localhost:1433;   DatabaseName=shyhao";
	private String NAME = "songyinghao";
	private String PASSWORD = "159";

	public DBService() {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void sendMsg(String toUserName, String fromUserName, String message) {
		Connection conn = null;
		Statement stmt = null;
		String sql = "";
		String sendDate = null;
		try {
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
			stmt = conn.createStatement();
			sendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date());
			sql = "insert into Messages(toUserName,fromUserName,message,sendDate) values('"
					+ toUserName
					+ "','"
					+ fromUserName
					+ "','"
					+ message
					+ "','" + sendDate + "')";
			stmt.execute(sql);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private int getLastChatId(String toUserName) {
		int returnValue = -1;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		try {
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
			stmt = conn.createStatement();
			sql = "select lastChatId from MessagesMap where toUserName='"
					+ toUserName + "'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				returnValue = rs.getInt("lastChatId");
			}else{
				stmt.execute("insert into MessagesMap (toUserName,lastChatId) values('"
						+ toUserName + "','-1')");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return returnValue;
	}

	private int getNewChatId(String toUserName) {
		int returnValue = 0 ;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		try {
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
			stmt = conn.createStatement();
			sql = "select top 1 chatId from Messages where toUserName= '"
					+ toUserName + "' order by chatId desc";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				returnValue=rs.getInt("chatId");
				System.out.println("returnValue :"+returnValue);
			}else{
				System.out.println("else");
				returnValue = -1;
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return returnValue;

	}

	public String getMsg(String toUserName) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		JSONObject jsonObj = new JSONObject();
		JSONArray jsonArr = new JSONArray();
		int lastId = getLastChatId(toUserName);
		int newId = getNewChatId(toUserName);
		System.out.println("toUserName :"+toUserName);
		System.out.println("lastId :"+lastId);
		System.out.println("newId :"+newId);
		if (newId > lastId) {
			try {
				conn = DriverManager.getConnection(URL, NAME, PASSWORD);
				stmt = conn.createStatement();
				sql = "select fromUserName,sendDate,message from Messages where chatId>"
						+ lastId
						+ "and chatId<="
						+ newId
						+"and toUserName= '"+toUserName
						+ "' order By chatId asc";
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					JSONObject tempJsonObj = new JSONObject();
					tempJsonObj.put("fromUserName", rs.getString("fromUserName"));
					tempJsonObj.put("sendDate", rs.getString("sendDate"));
					tempJsonObj.put("message", rs.getString("message"));
					jsonArr.add(tempJsonObj);
				}
				jsonObj.put("jsonArr", jsonArr);
				setDefaultId(newId,toUserName);
				conn.close();
				return jsonObj.toString();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return "";
	}
	private void setDefaultId(int newId,String toUserName){
		Connection conn = null;
		Statement stmt = null;
		String sql = "";
		try {
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
			stmt = conn.createStatement();
			sql="update MessagesMap set lastChatId="+newId+"where toUserName='"+toUserName+"'";
            stmt.execute(sql);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}