1.创建用户表和留言表
2.编写数据库连接工具类
public class SqlserverDBConn { private Statement stmt; private Connection conn; public ResultSet rs; public SqlserverDBConn(){ stmt=null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=TEST","sa","5loveme."); //if(!conn.isClosed()){ // System.out.println("success!"); //} } catch (Exception e) { e.printStackTrace(); } rs=null; }
public Connection getConnection(){
//if(conn.isClosed()){
return conn;
//}
}
public ResultSet executeQuery(String sql){ try { stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); rs=stmt.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); } return rs; } public void closeStmt(){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } public void closeConn(){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
2.测试之
package org.test;
import org.jdbc.SqlserverDBConn;
public class Test {
public static void main(String[] args) {
SqlserverDBConn dbConn=new SqlserverDBConn();
}
}
3.编写数据表对应的pojo类
public class UserTable {
private Integer id;
private String username;
private String password;
public UserTable(){
}
public UserTable(Integer id, String username, String password) {
super();
this.id = id;
this.username = username;
this.password = password;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "UserTable [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
}
public class LyTable {
private Integer id;
private Integer userId;
private Date date;
private String title;
private String content;
public LyTable(){
}
public LyTable(Integer id, Integer userId, Date date, String title,
String content) {
super();
this.id = id;
this.userId = userId;
this.date = date;
this.title = title;
this.content = content;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
@Override
public String toString() {
return "LyTable [id=" + id + ", userId=" + userId + ", date=" + date
+ ", title=" + title + ", content=" + content + "]";
}
}
***记得编写无参数构造函数
4.编写login.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>登录页面</title>
</head>
<body>
<form action="MainServlet" method="post">
<caption>用户登录</caption>
<table>
<tr>
<td>
用户名
</td>
<td>
<input type="text" name="username" size=20 />
</td>
</tr>
<tr>
<td>
密码
</td>
<td>
<input type="password" name="password" size=20 />
</td>
</tr>
</table>
<input type="submit" value="提交" />
<input type="reset" value="重置" />
<a href="registerServlet"><input type="button" value="注册" /></a>
</form>
</body>
</html>
5.添加对应的servlet——MainServlet
public class MainServlet extends HttpServlet {
/**
* Constructor of the object.
*/
public MainServlet() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String username=request.getParameter("username");
String password=request.getParameter("password");
boolean validated=false;
SqlserverDBConn DBConn=new SqlserverDBConn();
HttpSession session=request.getSession();
UserTable user=null;
user=(UserTable)session.getAttribute("user");
if(user==null){
String sql="select * from TEST..userTable";
ResultSet rs=DBConn.executeQuery(sql);
try {
while(rs.next()){
if(rs.getString("username").equals(username)&&rs.getString("password").equals(password)){
user=new UserTable(rs.getInt(1),rs.getString(2),rs.getString(3));
//user.setId(rs.getInt(1));
//user.setUsername(rs.getString(2));
//user.setPassword(rs.getString(3));
session.setAttribute("user", user);
validated=true;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConn.closeStmt();
}
else{
validated=true;
}
if(validated){
ArrayList al=new ArrayList();
String sql="select * from TEST..lyTable";
ResultSet rs=DBConn.executeQuery(sql);
try {
while(rs.next()){
LyTable ly=new LyTable();
ly.setId(rs.getInt(1));
ly.setUserId(rs.getInt(2));
ly.setDate(rs.getDate(3));
ly.setTitle(rs.getString(4));
ly.setContent(rs.getString(5));
al.add(ly);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
session.setAttribute("al", al);
DBConn.closeStmt();
DBConn.closeConn();
response.sendRedirect("main.jsp");
}
else{
response.sendRedirect("error.jsp");
}
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request,response);
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
6.web.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<servlet>
<servlet-name>MainServlet</servlet-name>
<display-name>This is the display name of my J2EE component</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>org.servlet.MainServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>RegisterServlet</servlet-name>
<display-name>This is the display name of my J2EE component</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>org.servlet.RegisterServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>MainServlet</servlet-name>
<url-pattern>/page/MainServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>RegisterServlet</servlet-name>
<url-pattern>/page/RegisterServlet</url-pattern>
</servlet-mapping>
</web-app>
***<url-pattern>映射路径 “/”代表WEBROOT</url-pattern>
7.编写留言板界面main.jsp
<%@ page language="java" import="java.util.*,java.sql.*,org.jdbc.*,org.pojo.*" pageEncoding="utf-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>留言板信息</title>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<form action="add.jsp" method="post">
<table border="1">
<caption>留言信息</caption>
<tr>
<th>留言人姓名</th>
<th>留言时间</th>
<th>留言标题</th>
<th>留言内容</th>
</tr>
<%
SqlserverDBConn DBConn=new SqlserverDBConn();
Connection conn=DBConn.getConnection();
PreparedStatement pstmt=null;
ArrayList al=(ArrayList)session.getAttribute("al");
Iterator it=al.iterator();
while(it.hasNext()){
LyTable ly=(LyTable)it.next();
String username=null;
String sql="select username from TEST..userTable where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, ly.getUserId());
ResultSet rs=pstmt.executeQuery();
//String username=rs.getString(1);
while(rs.next()){
username=rs.getString(1);
}
%>
<tr>
<td><%=username%></td>
<td><%=ly.getDate() %></td>
<td><%=ly.getTitle() %></td>
<td><%=ly.getContent() %></td>
</tr>
<%
}
%>
</table>
<input type="submit" value="添加留言" />
</form>
</body>
</html>
8.编写添加留言页面add.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>添加留言</title>
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="AddServlet" method="post">
<table border="1">
<caption>添加留言</caption>
<tr>
<td>留言标题</td>
<td>
<input type="text" name="title" />
</td>
</tr>
<tr>
<td>留言内容</td>
<td>
<textarea name="content" rows="10" cols="20"></textarea>
</td>
</tr>
</table>
<input type="submit" value="添加" />
<input type="reset" value="重置" />
</form>
</body>
</html>
9.编写AddServlet
public class AddServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AddServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
HttpSession session=request.getSession();
String title=request.getParameter("title");
String content=request.getParameter("content");
UserTable user=(UserTable)session.getAttribute("user");
LyTable ly=new LyTable();
ly.setUserId(user.getId());
ly.setDate(new Date(System.currentTimeMillis()));
ly.setTitle(title);
ly.setContent(content);
ArrayList al=(ArrayList)session.getAttribute("al");
al.add(ly);
SqlserverDBConn DBConn=new SqlserverDBConn();
String sql="insert into TEST..lyTable (userId,date,title,content) values(?,?,?,?)";
PreparedStatement pstmt=null;
Connection conn=DBConn.getConnection();
try {
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, ly.getUserId());
pstmt.setDate(2, ly.getDate());
pstmt.setString(3, ly.getTitle());
pstmt.setString(4, ly.getContent());
pstmt.executeUpdate();
response.sendRedirect("main.jsp");
} catch (SQLException e) {
e.printStackTrace();
response.sendRedirect("ad.jsp");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
10.实现注册功能，编写注册页面
<html>
<head>
<title>注册</title>
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="RegisterServlet" method="post">
<table>
<caption>用户注册</caption>
<tr>
<td>用户名</td>
<td>
<input type="text" name="regName" />
</td>
</tr>
<tr>
<td>密码</td>
<td><input type="password" name="regPass" /></td>
</tr>
</table>
<input type="submit" value="确认" />
<input type="reset" value="重置" />
</form>
</body>
</html>
11.编写RegisterServlet
public class RegisterServlet extends HttpServlet {
/**
* Constructor of the object.
*/
public RegisterServlet() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
HttpSession session=request.getSession();
String regName=request.getParameter("regName");
String regPass=request.getParameter("regPass");
SqlserverDBConn DBConn=new SqlserverDBConn();
Connection conn=DBConn.getConnection();
PreparedStatement pstmt=null;
String sql="insert into TEST..userTable (username,password) values(?,?)";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, regName);
pstmt.setString(2, regPass);
pstmt.executeUpdate();
response.sendRedirect("login.jsp");
pstmt.close();
DBConn.closeConn();
} catch (SQLException e) {
e.printStackTrace();
response.sendRedirect("error.jsp");
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request,response);
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
