|
|
1.数据库脚本:
drop table users
create table users
(
id int primary key identity(1,1),
username varchar(50)
)
drop proc adduser
create proc adduser
as
declare @i int
set @i = 1
begin
while(@i <= 100)
begin
insert into users values('name' + convert(varchar(10),@i))
set @i = @i + 1
end
end
go
exec adduser
select count(*) as rows from users
2.数据库连接类:
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
  /** *//**
 * 数据库连接类
*
* @author zdw
*
 */
public class DB
 {
private static final String DBDRIVER = "net.sourceforge.jtds.jdbc.Driver";
private static final String DBURL = "jdbc:jtds:sqlserver://localhost:1433/pubs;user=sa;pwd=";
private DB()
  {
 }
public static DB getInstance()
{
return new DB();
}
public Connection getConn()
{
Connection conn = null;
try
{
Class.forName(DBDRIVER);
conn = DriverManager.getConnection(DBURL);
} catch (ClassNotFoundException e)
{
e.printStackTrace();
} catch (SQLException e)
{
e.printStackTrace();
}
return conn;
}
}
3.UserDAO:
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.db.DB;
import com.vo.User;
/** *//*******************************************************************************
* userdao 对用户实现增删改查
*
* @author zdw
*
*/
public class UserDAO
{
public List<User> queryAll()
{
List<User> list = new ArrayList<User>();
String sql = "select * from users";
Connection conn = DB.getInstance().getConn();
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
list.add(user);
}
} catch (SQLException e)
{
e.printStackTrace();
} finally
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
return list;
}
public User queryById(Integer id)
{
User user = null;
String sql = "select * from users where id = ?";
Connection conn = DB.getInstance().getConn();
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
}
} catch (SQLException e)
{
e.printStackTrace();
} finally
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
return user;
}
public void save(User user)
{
String sql = "insert into users values(?)";
Connection conn = DB.getInstance().getConn();
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
} finally
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
public void update(User user)
{
String sql = "update users set name = ? where id = ?";
Connection conn = DB.getInstance().getConn();
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setInt(2, user.getId());
pstmt.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
} finally
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
public void delete(Integer id)
{
String sql = "delete from users where id = ?";
Connection conn = DB.getInstance().getConn();
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
} finally
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
4.UserServlet:
package com.web;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.UserDAO;
import com.vo.User;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
@SuppressWarnings("serial")
public class UserServlet extends HttpServlet
{
private Configuration cfg = null;
@Override
public void init() throws ServletException
{
cfg = new Configuration();
cfg.setServletContextForTemplateLoading(this.getServletContext(), null);
}
@SuppressWarnings("unchecked")
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
// 网站全路径
String basePath = req.getScheme() + "://" + req.getServerName() + ":"
+ req.getServerPort() + req.getContextPath() + "/";
// 得到要操作的方法名
String method = req.getParameter("method");
Map map = new HashMap();
map.put("path", basePath);
// 设置编码
res.setCharacterEncoding("gbk");
// 得到输出流
PrintWriter out = res.getWriter();
Template t = null;
// 查询所有用户
if (method.equals("queryAll"))
{
List list = this.doQueryAll(req, res);
map.put("list", list);
t = cfg.getTemplate("WEB-INF/templates/index.ftl");
System.out.println("ssdfs");
}
// 添加用户前的跳转
if (method.equals("forwardSave"))
{
t = cfg.getTemplate("WEB-INF/templates/form.ftl");
}
// 保存用户
if (method.equals("save"))
{
System.out.println("save");
this.doSave(req, res);
req.getRequestDispatcher("/UserServlet?method=queryAll").forward(
req, res);
return;
}
// 删除用户
if (method.equals("delete"))
{
this.doDel(req, res);
req.getRequestDispatcher("/UserServlet?method=queryAll").forward(
req, res);
return;
}
// 更新用户前的跳转
if (method.equals("forwardUpdate"))
{
Integer id = Integer.parseInt(req.getParameter("id"));
UserDAO userDAO = new UserDAO();
User user = userDAO.queryById(id);
map.put("user", user);
t = cfg.getTemplate("WEB-INF/templates/update.ftl");
}
// 更新用户
if (method.equals("update"))
{
this.doUpdate(req, res);
req.getRequestDispatcher("/UserServlet?method=queryAll").forward(
req, res);
return;
}
try
{
// 处理map和输出流
t.process(map, out);
out.flush();
out.close();
} catch (TemplateException e)
{
e.printStackTrace();
}
}
private void doSave(HttpServletRequest req, HttpServletResponse res)
{
String username = req.getParameter("username");
System.out.println("username:" + username);
UserDAO userDAO = new UserDAO();
User user = new User();
user.setName(username);
userDAO.save(user);
}
private void doUpdate(HttpServletRequest req, HttpServletResponse res)
{
Integer id = Integer.parseInt(req.getParameter("id"));
String username = req.getParameter("username");
UserDAO userDAO = new UserDAO();
User user = new User();
user.setId(id);
user.setName(username);
userDAO.update(user);
}
private List<User> doQueryAll(HttpServletRequest req,
HttpServletResponse res)
{
UserDAO userDAO = new UserDAO();
List<User> users = userDAO.queryAll();
return users;
}
private void doDel(HttpServletRequest req, HttpServletResponse res)
{
Integer id = Integer.parseInt(req.getParameter("id"));
UserDAO userDAO = new UserDAO();
userDAO.delete(id);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException
{
this.doGet(req, resp);
}
}
5.web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<servlet>
<servlet-name>UserServlet</servlet-name>
<servlet-class>com.web.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>UserServlet</servlet-name>
<url-pattern>/UserServlet</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
6.模板文件index.ftl:
<html>
<head><title>用户管理首页</title></head>
<a href="${path}UserServlet?method=forwardSave">添加用户</a>
<table border=1>
<tr>
<td>用户id</td>
<td>用户名</td>
<td>操作</td>
<td>操作</td>
</tr>
<#list list as user>
<tr>
<td>${user.id}</td>
<td>${user.name}</td>
<td><a href="${path}UserServlet?method=forwardUpdate&id=${user.id}">修改</a></td>
<td><a href="${path}UserServlet?method=delete&id=${user.id}">删除</a></td>
</tr>
</#list>
</table>
</body>
</html>
update.ftl:
<html>
<head><title>修改用户</title></head>
<body>
修改用户
<form method="post" action="${path}UserServlet?method=update&id=${user.id}">
username: <input type="text" name="username" value="${user.name}"/><br />
<input type="submit" value="修改" />
<input type="reset" value="重置" />
</form>
</body>
</html>
form.ftl:
<html>
<head><title>增加用户</title></head>
<body>
添加用户
<form method="post" action="${path}UserServlet?method=save">
username: <input type="text" name="username" /><br />
<input type="submit" value="添加" />
<input type="reset" value="重置" />
</form>
</body>
</html>
源码下载
|