这个类即实现了进行数据库操作的Servlet类，又实现了Websocket的功能
package action;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.websocket.*;
import javax.websocket.server.ServerEndpoint;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.CopyOnWriteArraySet;
/**
* 这个类即实现了进行数据库操作的Servlet类，又实现了Websocket的功能.
*/
//该注解用来指定一个URI，客户端可以通过这个URI来连接到WebSocket，类似Servlet的注解mapping；
// servlet的注册放在了web.xml中。
@ServerEndpoint(value = "/websocket")
public class ManagerServlet extends HttpServlet {
//concurrent包的线程安全Set，用来存放每个客户端对应的MyWebSocket对象。若要实现服务端与单一客户端通信的话，可以使用Map来存放，其中Key可以为用户标识
private static CopyOnWriteArraySet<ManagerServlet> webSocketSet = new CopyOnWriteArraySet<ManagerServlet>();
//这个session不是Httpsession，相当于用户的唯一标识，用它进行与指定用户通讯
private javax.websocket.Session session=null;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException {
System.out.println("进入doget方法>>>>>>>>>>>>>");
String msg;
String name=request.getParameter("name");
//这里submit是数据库操作的方法，如果插入数据成功，则发送更新信号
if(submit("1",name)){
//发送更新信号
sendMessage();
msg="ok!";
}else {
msg="error!";
}
response.sendRedirect("manager.jsp?msg="+msg);
}
public void doPost(HttpServletRequest request,HttpServletResponse reponse) throws ServletException, IOException {
doGet(request,reponse);
}
/**
* 向数据库插入一个name
* @param name
* @return
*/
public boolean submit(String id,String name){
System.out.println("进入写入sql》》》》》》》》》》》》》》》》");
DB db=new DB();
String sql="insert into users(id,name) values(?,?)";
try{
PreparedStatement pstmt=db.getConnection().prepareStatement(sql);
pstmt.setString(1,id);
pstmt.setString(2, name);
pstmt.executeUpdate();
return true;
}catch (Exception e){
e.printStackTrace();
return false;
}finally {
try {
db.getConnection().close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @OnOpen allows us to intercept the creation of a new session.
* The session class allows us to send data to the user.
* In the method onOpen, we'll let the user know that the handshake was
* successful.
* 建立websocket连接时调用
*/
@OnOpen
public void onOpen(Session session){
int i=1;
submit(String.valueOf(i),"name");
System.out.println("建立websocket连接时调用>>>>>>>>>>>>>>>>");
System.out.println("Session " + session.getId() + " has opened a connection");
try {
this.session=session;
webSocketSet.add(this); //加入set中
session.getBasicRemote().sendText("Connection Established");
} catch (IOException ex) {
ex.printStackTrace();
}
}
/**
* When a user sends a message to the server, this method will intercept the message
* and allow us to react to it. For now the message is read as a String.
* 接收到客户端消息时使用，这个例子里没用
*/
@OnMessage
public void onMessage(String message, Session session){
System.out.println("Message from " + session.getId() + ": " + message);
}
/**
* The user closes the connection.
*
* Note: you can't send messages to the client from this method
* 关闭连接时调用
*/
@OnClose
public void onClose(Session session){
webSocketSet.remove(this); //从set中删除
System.out.println("Session " +session.getId()+" has closed!");
}
/**
* 注意: OnError() 只能出现一次. 其中的参数都是可选的。
* @param session
* @param t
*/
@OnError
public void onError(Session session, Throwable t) {
t.printStackTrace();
}
/**
* 这个方法与上面几个方法不一样。没有用注解，是根据自己需要添加的方法。
* @throws IOException
* 发送自定义信号，“1”表示告诉前台，数据库发生改变了，需要刷新
*/
public void sendMessage() throws IOException{
//群发消息
for(ManagerServlet item: webSocketSet){
try {
item.session.getBasicRemote().sendText("1");
} catch (IOException e) {
e.printStackTrace();
continue;
}
}
}
}
//数据库连接方法类
package action;
import java.sql.Connection;
import java.sql.DriverManager;
import constant.ConsumptionConstant;
public class DB {
//一开始必须填一个已经存在的数据库
private Connection conn=null;
public Connection getConnection() {
try{
System.out.println("进入》》》》》》》》》》》》》");
Class.forName(ConsumptionConstant.drivers);
String url = ConsumptionConstant.url;
conn = DriverManager.getConnection(url, ConsumptionConstant.userName, ConsumptionConstant.password);// 连接数据库
System.out.println("conn============"+conn);
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
}
//常量类
package constant;
/**
*
*/
public interface ConsumptionConstant {
String url="jdbc:mysql://localhost:3306/pei_test?serverTimezone=UTC&characterEncoding=utf-8";
String userName="root";
String password="123456";
String drivers ="com.mysql.jdbc.Driver";
}
//Client类信息查询
package fun;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import action.DB;
import model.UserBean;
public class Client {
public List<UserBean> list(){
String sql = "select * from users";
//3.ResultSet类，用来存放获取的结果集！！
List<UserBean> list = new ArrayList<UserBean>();
try {
DB db =new DB();
ResultSet rs = db.getConnection().createStatement().executeQuery(sql);
System.out.println("rs==========="+rs);
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount(); //Map rowData;
while (rs.next()) { //rowData = new HashMap(columnCount);
UserBean bean=new UserBean();
for (int i = 1; i <= columnCount; i++) {
if(md.getColumnName(i).equals("id")) {
bean.setId(String.valueOf(rs.getObject(i)));
}
if(md.getColumnName(i).equals("name")) {
bean.setName(String.valueOf(rs.getObject(i)));
}
}
list.add(bean);
}
System.out.println("list==========="+list);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}
//实体类
package model;
public class UserBean {
private String id;
private String name;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "UserBean [id=" + id + ", name=" + name + "]";
}
public UserBean(String id, String name) {
super();
this.id = id;
this.name = name;
}
public UserBean() {
super();
}
}
//pom.xml文件
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com</groupId> <artifactId>websocktAndSql</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <dependencies> <dependency> <groupId>javax.websocket</groupId> <artifactId>javax.websocket-api</artifactId> <version>1.1</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <!-- 数据库--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <dependency> <groupId>c3p0</groupId> <artifactId>c3p0</artifactId> <version>0.9.1.2</version> </dependency> </dependencies> </project>
//jsp文件
<%@ page import="model.UserBean" %>
<%@ page import="java.util.List" %>
<%@ page import="fun.Client" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
</head>
<body>
<table id="table" border="1">
<tr>
<th >id</th>
<th >name</th>
</tr>
<%
//的到数据库信息，放在list中
Client client=new Client();
List<UserBean> list= client.list();
if(list != null){
for(UserBean user : list){
%>
<tr >
<td ><%=user.getId()%></td>
<td ><%=user.getName()%></td>
</tr>
<%
}
}
%>
</table>
<div id="message"></div>
<script>
var websocket = null;
//判断当前浏览器是否支持WebSocket
if ('WebSocket' in window) {
//建立连接，这里的/websocket ，是ManagerServlet中开头注解中的那个值
websocket = new WebSocket("ws://localhost:8080/websocktAndSql/websocket");
}
else {
alert('当前浏览器 Not support websocket')
}
//连接发生错误的回调方法
websocket.onerror = function () {
setMessageInnerHTML("WebSocket连接发生错误");
};
//连接成功建立的回调方法
websocket.onopen = function () {
setMessageInnerHTML("WebSocket连接成功");
}
//接收到消息的回调方法
websocket.onmessage = function (event) {
setMessageInnerHTML(event.data);
if(event.data=="1"){
location.reload();
}
}
//连接关闭的回调方法
websocket.onclose = function () {
setMessageInnerHTML("WebSocket连接关闭");
}
//监听窗口关闭事件，当窗口关闭时，主动去关闭websocket连接，防止连接还没断开就关闭窗口，server端会抛异常。
window.onbeforeunload = function () {
closeWebSocket();
}
//将消息显示在网页上
function setMessageInnerHTML(innerHTML) {
document.getElementById('message').innerHTML += innerHTML + '<br/>';
}
//关闭WebSocket连接
function closeWebSocket() {
websocket.close();
}
</script>
</body>
</html>
//数据库
//项目结构
//tomcat启动后访问路径
http://localhost:8080/websocktAndSql/user.jsp