12My1.0版本聊天系统

实现功能:
1.登陆
2.注册
3.发送在线消息
4.发送离线消息
5.上线提醒服务
6.下线提醒服务
7.服务器发送广播消息
8.正常退出
9.异常退出
10.数据库操作采用静态内部类的单例模式

服务器端:
package server;

import java.io.IOException;
import java.io.PrintWriter;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Map.Entry;
import java.util.Date;
import java.util.NoSuchElementException;
import java.util.Scanner;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

import db.DbConnector;

class HandleClientTask implements Runnable{
	private Socket client;
	private Scanner scanner;
	private PrintWriter out;
	private String username;
	
	public HandleClientTask(Socket client){
		this.client = client;
		try {
			scanner = new Scanner(client.getInputStream());
			out = new PrintWriter(client.getOutputStream(), true);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public String getCurTime(){
		Date date = new Date(System.currentTimeMillis());
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return sdf.format(date);
	}
	
	public void login(JsonObject jobject){
		//访问数据库,鉴定 name password
		String name = jobject.get("name").getAsString();
		String pwd = jobject.get("pwd").getAsString();
		boolean bloginstate = false;
		
		DbConnector db = DbConnector.getInstance();
		ResultSet rset = db.select("select * from chatuser");
		
		try {
			while (rset.next()){
				String curname = rset.getString("username");
				if (curname.compareTo(name) == 0){
					if (rset.getString("password").compareTo(pwd) == 0){
						//登陆成功
						username = name;
						Server.map.put(name, client);
						bloginstate = true;
						break;
					}
				}
			}
			rset.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//给客户端回复
		JsonObject json = new JsonObject();
		json.addProperty("msgtype", 20);  //20表示服务器的响应登陆消息
		if (bloginstate){
			json.addProperty("ack", "loginok");
		}else{
			json.addProperty("ack", "loginfail");
			json.addProperty("reason", "username or password is wrong!!!");
		}
		out.println(json.toString());
		
		
		if (!bloginstate){  //登陆失败,直接返回
			return;
		}
		
		ResultSet offmsg = db.checkOffMsg(name);//检查是否存在离线消息
		try {
			while (offmsg.next()){
				JsonObject offjson = new JsonObject();
				offjson.addProperty("msgtype", 16);  //16表示离线消息
				String userfrom = offmsg.getString("userfrom");
				String sendtime = offmsg.getString("sendtime");
				String message = offmsg.getString("message");
				
				if (userfrom.compareTo("SuperUser") == 0){
					offjson.addProperty("ack", "servermsg");  //系统管理员发送的离线消息
				}
				else{
					offjson.addProperty("ack", "usermsg");  //普通用户发送的离线消息
					offjson.addProperty("userfrom", userfrom);
				}
				offjson.addProperty("sendtime", sendtime);
				offjson.addProperty("message", message);
				
				out.println(offjson.toString());
			}
			offmsg.close();
			
			db.delOffMsg(name);  //删除该用户的离线消息
			
			//在线登陆提醒服务
			String time = getCurTime();
			
			Set<Entry<String, Socket>> users = Server.map.entrySet();
			for (Entry<String, Socket> user : users){
				JsonObject onlineCall = new JsonObject();
				onlineCall.addProperty("msgtype", 17);
				onlineCall.addProperty("ack", "servermsg");
				onlineCall.addProperty("sendtime", time);
				onlineCall.addProperty("message", "user " + name + " has logged in!");
				try {
					String recvUserName = user.getKey();
					if (recvUserName.compareTo(name) != 0){
						PrintWriter oncall = new PrintWriter(user.getValue().getOutputStream(), true);
						oncall.println(onlineCall.toString());
					}
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void register(JsonObject jobject){
		//访问数据库,完成用户注册
		String name = jobject.get("name").getAsString();
		String pwd = jobject.get("pwd").getAsString();
		String pnumber = jobject.get("pnumber").getAsString();
		String addr = jobject.get("addr").getAsString();
		
		DbConnector db = DbConnector.getInstance();
		String recv = db.register(name, pwd, pnumber, addr);
		
		JsonObject json = new JsonObject();
		json.addProperty("msgtype", 19);   //19表示服务器响应用户信息注册
		if (recv.compareTo("reg_ok") == 0){
			json.addProperty("ack", "reg_ok");
		}
		else if (recv.compareTo("pri_error") == 0){
			json.addProperty("ack", "pri_error");
			json.addProperty("reason", "username has been userd!!!");
		}
		else if (recv.compareTo("data_long_error") == 0){
			json.addProperty("ack", "data_long_error");
			json.addProperty("reason", "input data too long!!!");
		}
		else{
			json.addProperty("ack", "reg_error");
		}
		
		out.println(json.toString());
	}
	
	public void chat(JsonObject jobject){
		//完成向目标用户聊天消息的转发
		String userfrom = jobject.get("userfrom").getAsString();
		String sendto = jobject.get("sendto").getAsString();
		String msg = jobject.get("msg").getAsString();
		
		JsonObject json = new JsonObject();  //回复给发送发的json字符串
		json.addProperty("msgtype", 18);   //18表示服务器响应用户发送聊天消息的发送方
		
		boolean userexist = Server.checkExist(sendto);  //访问数据库检查用户是否存在
		if (!userexist){
			json.addProperty("ack", "exist_error");
			json.addProperty("reason", "your send user not exist!!!");
			out.println(json.toString());
			return;
		}
		
		Socket sendToClient = Server.checkOnline(sendto);   //访问ConcurrentHashMap,检查用户是否在线
		if (sendToClient == null){
			json.addProperty("ack", "online_error");
			json.addProperty("reason", "your send user offline,but " + sendto + " will receive the message when " + sendto + " online next time!");
			out.println(json.toString());
			//将离线消息存储进数据库
			DbConnector db = DbConnector.getInstance();
			db.saveMsg(userfrom,sendto, msg);
			return;
		}
		
		//回复给发送方,表示用户在线,并且成功发送
		json.addProperty("ack", "send_ok");
		out.println(json.toString());
		
		JsonObject sendjson = new JsonObject();  //回复给接收方的json字符串
		
		Date date = new Date(System.currentTimeMillis());
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = sdf.format(date);
		
		sendjson.addProperty("msgtype", 17);  //17表示服务器转发聊天消息给接收方
		sendjson.addProperty("ack", "usermsg");  //usermsg  servermsg
		sendjson.addProperty("userfrom", userfrom);
		sendjson.addProperty("sendtime", time);
		sendjson.addProperty("msg", msg);
		
		try {
			PrintWriter out2 = new PrintWriter(sendToClient.getOutputStream(), true);
			out2.println(sendjson.toString());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//用户下线提醒
	public void userExitCall(String name){
		String time = getCurTime();
		
		Set<Entry<String, Socket>> users = Server.map.entrySet();
		for (Entry<String, Socket> user : users){
			JsonObject offlineCall = new JsonObject();
			offlineCall.addProperty("msgtype", 17);  //用户接收其他用户发送的消息
			offlineCall.addProperty("ack", "servermsg");
			offlineCall.addProperty("sendtime", time);
			offlineCall.addProperty("message", "user " + name + " has quit out!");
			try {
//				String recvUserName = user.getKey();
//				if (recvUserName.compareTo(name) != 0){
					PrintWriter oncall = new PrintWriter(user.getValue().getOutputStream(), true);
					oncall.println(offlineCall.toString());
//				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	@Override
	public void run() {
		// TODO Auto-generated method stub
		/
		
	try{
		while (!Thread.currentThread().isInterrupted()){
			String recvMsg = scanner.nextLine();   //解析从客户端发送过来的信息
			System.out.println(recvMsg);
			JsonParser parser = new JsonParser();
			JsonElement element = parser.parse(recvMsg);
			JsonObject jobject = element.getAsJsonObject();
			
			int msgtype = jobject.get("msgtype").getAsInt();
			switch(msgtype){
			
			case 1:  //处理登陆消息
				login(jobject);
				break;
				
			case 2:  //处理注册消息
				register(jobject);
				break;
				
			case 3:  //处理聊天消息
				chat(jobject);
				break;
				
			case 0:  //处理客户正常退出
				
				//1.删除map中对应socket
				System.out.println("client exit:" + client.getRemoteSocketAddress());	
				String username = jobject.get("username").getAsString();
				Server.map.remove(username);
				
				//2.下线提醒
				userExitCall(username);
				
				//3.关闭socket,当前线程	
				try {
					client.close();
					out.close();
					Thread.currentThread().interrupt();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				break;
			}
		}
	}catch(NoSuchElementException e){  //处理客户端异常退出的情况
		
		if(username != null){   //用户已经登陆
			Server.map.remove(username);  //删除map表中该用户对应socket
			System.out.println("remove " + username);
		}
	} finally{
		Set<Entry<String, Socket>> set = Server.map.entrySet();
		System.out.println("map contain:");
		for (Entry<String, Socket> entry : set){
			System.out.print(entry.getKey());
		}
		System.out.println();
	}
		
		///
	}
	
}

class WorkMenu implements Runnable{
	private Scanner scan;
	
	public WorkMenu(){
		scan = new Scanner(System.in);
	}
	
	public void menu(){
		System.out.println("--------------");
		System.out.println("1.广播消息");
		System.out.println("0.关闭服务器");
		System.out.println("--------------");
	}
	
	//给所有在线或者不在线用户发送广播消息
	public void broadcastMsg(String broadMsg){
		DbConnector db = DbConnector.getInstance();
		String sql = "select * from chatuser";
		ResultSet user = db.select(sql);
		try {
			while (user.next()){
				JsonObject jobject = new JsonObject();
				Date date = new Date(System.currentTimeMillis());
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String time = sdf.format(date);
				
				String sendto = user.getString("username");
				Socket client = Server.checkOnline(sendto); //如果该用户在线,socket非空
				if (client != null){  //将该消息发送给在线用户
					jobject.addProperty("msgtype", 17);  //17表示客户接收在线消息
					jobject.addProperty("ack", "servermsg");
					jobject.addProperty("sendtime", time);
					jobject.addProperty("message", broadMsg);
					
					try {
						PrintWriter out = new PrintWriter(client.getOutputStream(), true);
						out.println(jobject.toString());
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				else{  //将该消息储存进数据库,离线客户下次上线自动接收
					db.saveMsg("SuperUser", sendto, broadMsg);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void run() {
		// TODO Auto-generated method stub
		menu();
		int select = 0;
		
		while ((select = Integer.parseInt(scan.nextLine())) != 0){
			
			switch(select){
			
			case 1:  //发送广播消息
				System.out.print("input broadcast message: ");
				String broadMsg = scan.nextLine();
				broadcastMsg(broadMsg);
				break;
				
			case 0:
				break;
			}
			menu();
		}
	}
	
}

public class Server {
	private static ExecutorService threadpool;
	public static ConcurrentHashMap<String, Socket> map;   //储存在线客户的<name, socket>
	
	static{
		threadpool = Executors.newCachedThreadPool();
		map = new ConcurrentHashMap<String, Socket>();
	}
	
	//检查该用户是否在线
	public static Socket checkOnline(String sendto){
		Set<Entry<String, Socket>> entryset = Server.map.entrySet();
		for (Entry<String, Socket> entry : entryset){
			if (entry.getKey().compareTo(sendto) == 0){
				return entry.getValue();
			}
		}
		return null;
	}
	
	//检查该用户是否存在
	public static boolean checkExist(String sendto){
		DbConnector db = DbConnector.getInstance();
		ResultSet rset = db.exist(sendto);
		
		try {
			while (rset.next()){
				if (rset.getString("username").compareTo(sendto) == 0){
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return false;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			ServerSocket server = new ServerSocket(6000);
			System.out.println("server supply service on 6000...");
			
			threadpool.submit(new WorkMenu());
			
			while (!Thread.currentThread().isInterrupted()){
				Socket client = server.accept();
				System.out.println("accept client:" + client.getRemoteSocketAddress());
				threadpool.submit(new HandleClientTask(client));
			}
			
			System.out.println("server out service...");
			server.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

客户端:
package client;

import java.io.IOException;
import java.io.PrintWriter;
import java.net.Socket;
import java.net.UnknownHostException;
import java.util.Scanner;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

class ReaderTask implements Runnable{
	private Socket client;
	private Scanner scanner;
	private PrintWriter out;
	private String username;
	private boolean blogin;  //判断用户是否登陆
	
	public ReaderTask(Socket client){
		this.client = client;
		try {
			blogin = false;
			scanner = new Scanner(client.getInputStream());
			out = new PrintWriter(client.getOutputStream(), true);//必须加true或者每次发送都需要flush
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public boolean getBlogin(){
		return blogin;
	}
	
	public String getUsername(){
		return username;
	}
	
	public void login(){
		Scanner scan = new Scanner(System.in);
		System.out.print("input username: ");
		String name = scan.nextLine();
		System.out.print("input password: ");
		String pwd = scan.nextLine();
		
		this.username = name;
		
		JsonObject jobject = new JsonObject();
		jobject.addProperty("msgtype", 1);  //1.登陆
		jobject.addProperty("name", name);
		jobject.addProperty("pwd", pwd);
		
		out.println(jobject.toString());   //向服务器发送登陆信息
	}
	
	public void register(){
		Scanner scan = new Scanner(System.in);
		System.out.print("input username: ");
		String name = scan.nextLine();
		System.out.print("input password: ");
		String pwd = scan.nextLine();
		System.out.print("input phonenumber: ");
		String pnumber = scan.nextLine();
		System.out.print("input address: ");
		String addr = scan.nextLine();
		
		JsonObject jobject = new JsonObject();
		jobject.addProperty("msgtype", 2);  //2.注册
		jobject.addProperty("name", name);
		jobject.addProperty("pwd", pwd);
		jobject.addProperty("pnumber", pnumber);
		jobject.addProperty("addr", addr);
		
		out.println(jobject.toString());   //向服务器发送注册信息
	}
	
	public void sendMsg(){
		Scanner scan = new Scanner(System.in);
		System.out.print("send to: ");
		String sendto = scan.nextLine();
		System.out.print("message: ");
		String msg = scan.nextLine();
		
		JsonObject jobject = new JsonObject();
		jobject.addProperty("msgtype", 3);
		jobject.addProperty("userfrom", this.username);
		jobject.addProperty("sendto", sendto);
		jobject.addProperty("msg", msg);
		
		out.println(jobject.toString());   //向服务器发送聊天消息
	}
	
	@Override
	public void run() {
		// TODO Auto-generated method stub
		while (!Thread.currentThread().isInterrupted()){
			String recvMsg = scanner.nextLine();//从服务器接收到的json字符串解析
			JsonParser parser = new JsonParser();
			JsonElement element = parser.parse(recvMsg);
			JsonObject object = element.getAsJsonObject();
			
			int msgtype = object.get("msgtype").getAsInt();
			switch(msgtype){  //接收服务器ack响应消息
			
			case 20:  //处理登陆消息
				String acklog = object.get("ack").getAsString();
				if (acklog.compareTo("loginfail") == 0){
					System.out.println("login fail!");
					System.out.println(object.get("reason").getAsString());
				}
				else{
					blogin = true;
					System.out.println("login success!");
				}
				
				break;
				
			case 19:   //处理注册消息
				String ackreg = object.get("ack").getAsString();
				if (ackreg.compareTo("reg_error") == 0){
					System.out.println("register fail!");
					System.out.println("please regist again!");
				}
				else if (ackreg.compareTo("pri_error") == 0){
					System.out.println("register fail!");
					System.out.println(object.get("reason").getAsString());
				}
				else if (ackreg.compareTo("data_long_error") == 0){
					System.out.println("register fail!");
					System.out.println(object.get("reason").getAsString());
				}
				else{
					System.out.println("register success!");
				}
				break;
				
			case 18:  //处理发送聊天消息之后服务器的响应消息
				String acksend = object.get("ack").getAsString();
				if (acksend.compareTo("exist_error") == 0){
					System.out.println(object.get("reason").getAsString());
				}
				else if (acksend.compareTo("online_error") == 0){
					System.out.println(object.get("reason").getAsString());
				}
				else{
					System.out.println("message send success!");
				}
				break;
				
			case 17:  //接收其他用户发送的聊天消息
				String ackrecv = object.get("ack").getAsString();
				if (ackrecv.compareTo("servermsg") == 0){  //表示接收到服务器发送过来的消息
					System.out.println("*****系统消息*****");
					System.out.println("发送时间:"+object.get("sendtime").getAsString());
					System.out.println("内容:"+object.get("message").getAsString());
				}
				else{  //表示接收到普通用户发送过来的消息
					System.out.println(object.get("userfrom").getAsString()+" "+object.get("sendtime").getAsString()+":");
					System.out.println(object.get("msg").getAsString());
				}
				break;
				
			case 16:  //接收其他用户发送的离线消息
				String ackoffmsg = object.get("ack").getAsString();
				if (ackoffmsg.compareTo("servermsg") == 0){
					System.out.println("*****系统消息*****");
					System.out.println("发送时间:"+object.get("sendtime").getAsString());
					System.out.println("内容:"+object.get("message").getAsString());
				}
				else{
					System.out.println(object.get("userfrom").getAsString()+" "+object.get("sendtime").getAsString()+":");
					System.out.println(object.get("message").getAsString());
				}
				break;
				
			}
		
		}
	}
}

public class Client {
	
	public static void mainMenu(){
		System.out.println("--------------");
		System.out.println("1.登陆");
		System.out.println("2.注册");
		System.out.println("3.聊天");
		System.out.println("0.退出");
		System.out.println("--------------");
	}

	/**
	 * 是否out = new PrintWriter(client.getOutputStream(), true);
	 * out.close()
	 * 使得client也关闭???
	 * 
	 * scan.close()
	 * 不能随便close()
	 * */
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			Scanner scan = new Scanner(System.in);
			ExecutorService threadpool = Executors.newSingleThreadExecutor();
			Socket client = new Socket("127.0.0.1", 6000);
			ReaderTask readtask = new ReaderTask(client);
			threadpool.submit(readtask);
			
			mainMenu();
			int select = 0;
			
			while ((select = Integer.parseInt(scan.nextLine())) != 0){
				switch(select){
				
				case 1:  //登陆
					if (!readtask.getBlogin())
						readtask.login();
					else
						System.out.println("you have been logged in!");
					
					break;
					
				case 2:  //注册
					readtask.register();
					break;
					
				case 3:  //发送消息
					if (!readtask.getBlogin())
						System.out.println("please login first!");
					else
						readtask.sendMsg();
					break;
				}
				
				mainMenu();	
			}
			
			//选择0,正常退出
			JsonObject exit = new JsonObject();
			exit.addProperty("msgtype", 0);
			exit.addProperty("username", readtask.getUsername());
			PrintWriter out = new PrintWriter(client.getOutputStream(), true);
			out.println(exit.toString());
			
			client.close();
			threadpool.shutdown();
			
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

数据库部分:
package db;

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

public class DbConnector {
	private Connection connection;
	private PreparedStatement prestatement;
	private ResultSet resultset;
	
	private DbConnector(){
		try {
			//加载jdbc的驱动
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/school";
			
			//创建和mysql server 的一个连接
			connection = DriverManager.getConnection(url);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	static class InnerDbConnector{
		public static final DbConnector db = new DbConnector();
	}
	
	public static DbConnector getInstance(){
		return InnerDbConnector.db;
	}
	
	//返回查询结果
	public ResultSet select(String sql){
		try {
			Statement statement = connection.createStatement();
			return statement.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//检查用户是否存在
	public ResultSet exist(String sendto){
		String sql = "select username from chatuser where username = ?";
		try {
			prestatement = connection.prepareStatement(sql);
			prestatement.setString(1, sendto);
			
			return prestatement.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//用户注册
	public String register(String name, String pwd, String pnumber, String addr){
		String sql = "insert into chatuser values(?, ?, ?, ?)";
		try {
			prestatement = connection.prepareStatement(sql);
			prestatement.setString(1, name);
			prestatement.setString(2, pwd);
			prestatement.setString(3, pnumber);
			prestatement.setString(4, addr);
			
			int result = prestatement.executeUpdate();
			if (result > 0){
				return "reg_ok";
			}
			else{
				return "reg_error";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if (e.getMessage().contains("for key 'PRIMARY'")){
				return "pri_error";
			}
			else if (e.getMessage().contains("Data too long")){
				return "data_long_error";
			}
			else{
				e.printStackTrace();
			}
		}
		
		return null;
		
	}
	
	//储存离线消息
	public void saveMsg(String userfrom, String sendto, String msg){
		Date date = new Date(System.currentTimeMillis());
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = sdf.format(date);
		
		String sql = "insert into offmsg values(?, ?, ?, ?);";
		try {
			prestatement = connection.prepareStatement(sql);
			prestatement.setString(1, sendto);
			prestatement.setString(2, userfrom);
			prestatement.setString(3, time);
			prestatement.setString(4, msg);
			
			prestatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	//检查该用户是否存在离线消息
	public ResultSet checkOffMsg(String username){
		String sql = "select * from offmsg where username = ?";
		try {
			prestatement = connection.prepareStatement(sql);
			prestatement.setString(1, username);
			
			return prestatement.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//删除该用户的离线消息
	public void delOffMsg(String name){
		String sql = "delete from offmsg where username = ?";
		
		try {
			prestatement = connection.prepareStatement(sql);
			prestatement.setString(1, name);
			
			prestatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}