核心提示：Web数据库的增加、删除、修改、查找package com.jredu.web.dao;import java.sql.Connection;import java.sql.PreparedState...
Web——数据库的增加、删除、修改、查找
package com.jredu.web.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.jredu.web.db.DBConnection;
import com.jredu.web.entity.User;
public class UserDao {
public List<User> selectAll(){
Connection con=DBConnection.getConnection();
Statement stmt;
List<User> list=new ArrayList<User>();
try {
stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * FROM users");
while(rs.next()){
User user=new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setPwd(rs.getString("pwd"));
user.setDisplayName(rs.getString("display_name"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnection.closeConnection();
}
return list;
}
public User selectwhere(String whereOption){
Connection con=DBConnection.getConnection();
Statement stmt;
User user=null;
try {
stmt=con.createStatement();
String sql=("SELECT * FROM users");
if(!whereOption.equals("")){
sql+=whereOption;
}
ResultSet rs=stmt.executeQuery(sql);
if(rs.next()){
user=new User();
user.setUserName(rs.getString("user_name"));
user.setPwd(rs.getString("pwd"));
user.setDisplayName(rs.getString("display_name"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnection.closeConnection();
}
return user;
}
public boolean selectwhere2(String whereOption2){
Connection con=DBConnection.getConnection();
Statement stmt;
User user=null;
try {
stmt=con.createStatement();
String sql=("SELECT * FROM users");
if(!whereOption2.equals("")){
sql+=whereOption2;
}
ResultSet rs=stmt.executeQuery(sql);
if(rs.next()){
user=new User();
user.setUserName(rs.getString("user_name"));
user.setPwd(rs.getString("pwd"));
user.setDisplayName(rs.getString("display_name"));
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnection.closeConnection();
}
return false;
}
public int insert(User user){
Connection con=DBConnection.getConnection();
PreparedStatement pstmt=null;
int count=0;
String sql=" insert into users(user_name,pwd,display_name) values(?,?,?)";
try {
pstmt=con.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPwd());
pstmt.setString(3, user.getDisplayName());
count=pstmt.executeUpdate();
if(count==0){
//throw new DataAlreadyExistException();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
return count;
}
public int update(User user){
Connection con=DBConnection.getConnection();
PreparedStatement pstmt=null;
String sql = " update users " +
" set user_name = ? ," +
" pwd = ? ," +
" display_name = ? " +
" where id = ? ";
int affCount=0;
try {
pstmt=con.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPwd());
pstmt.setString(3, user.getDisplayName());
pstmt.setInt(4, user.getId());
affCount=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
return affCount;
}
public int delete(int id) {
Connection con = DBConnection.getConnection();
PreparedStatement pstmt = null;
int affCount=0;
String sql = " delete from users where id = ? ";
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
affCount=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
return affCount;
}
/*public void delete(int id){
Connection con=DBConnection.getConnection();
PreparedStatement pstmt=null;
String sql=" delete from users where is= ? ";
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
}*/
public List<User> selectPage(int from,int rows){
Connection con=DBConnection.getConnection();
Statement stmt;
List<User> list=new ArrayList<User>();
try {
stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * FROM users LIMIT "+from+","+rows);
while(rs.next()){
User user=new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setPwd(rs.getString("pwd"));
user.setDisplayName(rs.getString("display_name"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnection.closeConnection();
}
return list;
}
public int selectCount(){
Connection con=DBConnection.getConnection();
Statement stmt;
int count=0;
try {
stmt=con.createStatement();
String sql="SELECT count(1) as count FROM users ";
ResultSet rs=stmt.executeQuery(sql);
if(rs.next()){
count=rs.getInt("count");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnection.closeConnection();
}
return count;
}
}
package com.jredu.web.db;
import java.sql.*;
public class DBConnection {
private static Connection con=null;
//mysql驱动mingz
private static String driverName="com.mysql.jdbc.Driver";
//数据库用户名
private static String userName="root";
//密码
private static String userPasswd = "ffffff";
//数据库名
private static String dbName = "shcoolapp";
//联结字符串
private static String url = "jdbc:mysql://localhost/" + dbName
+ "?user="+ userName
+ "&password=" + userPasswd
+ "&useUnicode=true&characterEncoding=gbk";
public static Connection getConnection(){
try {
Class.forName(driverName);
con =DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
// TODO: handle exception
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
return con;
}
public static void closeConnection(){
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
[java] view plain copy
package com.jredu.web.entity;
public class User {
private int id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
private String userName;
private String pwd;
private String displayName;
public User(){}
public User(String userName, String pwd, String displayName) {
super();
this.userName = userName;
this.pwd = pwd;
this.displayName = displayName;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getDisplayName() {
return displayName;
}
public void setDisplayName(String displayName) {
this.displayName = displayName;
}
}
package com.jredu.web.entity;
public class User {
private int id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
private String userName;
private String pwd;
private String displayName;
public User(){}
public User(String userName, String pwd, String displayName) {
super();
this.userName = userName;
this.pwd = pwd;
this.displayName = displayName;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getDisplayName() {
return displayName;
}
public void setDisplayName(String displayName) {
this.displayName = displayName;
}
}
package com.jredu.web.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.collections.map.HashedMap;
import com.jredu.web.dao.UserDao;
import com.jredu.web.entity.User;
public class UserServlet extends HttpServlet {
/**
* Constructor of the object.
*/
public UserServlet() {
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 {
this.doPost(request, response);
}
/**
* 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 {
response.setCharacterEncoding("utf-8");
request.setCharacterEncoding("utf-8");
response.setContentType("textml;charset=utf-8");
String action=request.getParameter("action");
if(null==action||action.equals("select")){
select(request,response);
}else if(action.equals("update")){
update(request,response);
}else if(action.equals("add")){
add(request,response);
}else if(action.equals("delect")){
delect(request,response);
}
}
//添加
public void add(HttpServletRequest request, HttpServletResponse response) throws IOException{
String userName=request.getParameter("userName");
String pwd=request.getParameter("pwd");
String displayName=request.getParameter("displayName");
User user=new User();
user.setUserName(userName);
user.setPwd(pwd);
user.setDisplayName(displayName);
UserDao userDao = new UserDao();
int affCount=userDao.insert(user);
PrintWriter out = response.getWriter();
out.print(affCount);
}
//删除
public void delect(HttpServletRequest request, HttpServletResponse response) throws IOException{
String ids[]=request.getParameterValues("uid[]");
UserDao userDao = new UserDao();
int affCount=0;
for(int i=0;i<ids.length;i++){
affCount +=userDao.delete(Integer.parseInt(ids[i]));
}
PrintWriter out = response.getWriter();
out.print(affCount);
}
//修改
public void update(HttpServletRequest request, HttpServletResponse response)
throws IOException{
String id=request.getParameter("id");
String userName=request.getParameter("userName");
String pwd=request.getParameter("pwd");
String displayName=request.getParameter("displayName");
User user=new User();
user.setId(Integer.parseInt(id));
user.setUserName(userName);
user.setPwd(pwd);
user.setDisplayName(displayName);
UserDao userDao = new UserDao();
int affCount=userDao.update(user);
PrintWriter out = response.getWriter();
out.print(affCount);
}
//查询
public void select(HttpServletRequest request, HttpServletResponse response) throws IOException{
String page=request.getParameter("page");
String row=request.getParameter("rows");
System.out.print(page+"##"+row);
int from=0;
int rows= Integer.parseInt(row);
int pages= Integer.parseInt(page);
if(pages>0){
from=rows*(pages-1);
}
UserDao userDao = new UserDao();
List<User> list = userDao.selectPage(from,rows);
HashMap<String,Object> map=new HashMap<String,Object>();
map.put("total", userDao.selectCount());
map.put("rows", list);
PrintWriter out = response.getWriter();
JSONObject ja=JSONObject.fromObject(map);
//List<User> list = userDao.selectAll();
//变成单个对象
//JSONObject jo=JSONObject.fromObject(user);
//把list变成JSONArray
//JSONArray ja = JSONArray.fromObject(list);
//PrintWriter out = response.getWriter();
System.out.println(ja.toString());
out.print(ja.toString());
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
<%@page import="java.net.URLDecoder"%>
<%@ page language="java" import="java.util.*"
pageEncoding="utf-8"%>
<%
request.setCharacterEncoding("utf-8");
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
if(request.getParameter("zyf")!=null){
String zyf= URLDecoder.decode(request.getParameter("zyf"),"utf-8");
out.print(zyf);
}
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'login.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="servlet/LoginServlet" method="post">
用户名：<input type="text" name="name"><br>
密码： <input type="text" name="pwd"><br>
<input type="submit" value="提交登录" ><br>
</body>
</html>
<%@page import="java.net.URLDecoder"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
request.setCharacterEncoding("utf-8");
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
String zyf = "11";
if (request.getParameter("zyf") != null) {
zyf = URLDecoder.decode(request.getParameter("zyf"), "utf-8");
}
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'main.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<link rel="stylesheet" href="css/icon.css"></link>
<link rel="stylesheet" href="css/easyui.css"></link>
<script type="text/javascript" src="js/jquery-1.7.2.min.js"></script>
<script type="text/javascript" src="js/jquery.easyui.min.js"></script>
<script type="text/javascript">
/* $(function() {
$('#dg').datagrid({
title:'用户列表',
method:'GET',
url:'servlet/UserServlet',
iconCls:'icon-ok',
fitColumns:true,
pagination:true,
striped:true,
nowrap:true,
singleSelect:true, //单行
rownumbers:true,
collapsible:true,//是否可折叠的
// pageSize: 1,//每页显示的记录条数，默认为10
pageList: [1,2,3],//可以设置每页记录条数的列表
fitColumns : true,
autoRowHeight:true,
striped:true,
nowrap:true,
pagination:true,
toolbar : [ {
text:'查询',
iconCls : 'icon-search',
handler : function() {
$('#dg').datagrid('reload');
}
}, '-', {
text:'修改',
iconCls : 'icon-edit',
handler : function() {
alert('帮助按钮')
}
}, '-', {
text:'增加',
iconCls : 'icon-add',
handler : function() {
alert('帮助按钮')
}
}, '-', {
text:'删除',
iconCls : 'icon-remove',
handler : function() {
alert('帮助按钮')
}
} ],
columns : [ [
{
field : 'cc',
checkbox:true,
width : 100,
}, {
field : 'userName',
title : '用户名',
width : 100,
editor:'text',
align:'center'
}, {
field : 'pwd',
title : '密码',
editor:'text',
align:'center',
width : 100
}, {
field : 'displayName',
title : '级别',
width : 100,
editor:'text',
align:'center'
},{
field:'option',
title:'操作',
width : 100,
formatter:function(value,row,index){
if(row.editing){
var s='<a href="javascript：void(0);"onclick="saverow('+index+')">save</a>';
return s;
}else{
var e='<a href="javascript：void(0);"onclick="editrow('+index+')">editrow</a>';
return e;
}
}
}
] ],
onBeforeEdit:function(index,row){
row.editing=true;
$("#dg").datagrid("refreshRow",index);
},
onAfterEdit:function(index,row){
row.editing=false;
$("#dg").datagrid("refreshRow",index);
}
});
//设置分页控件
var p = $('#dg').datagrid('getPager');
$(p).pagination(
{
beforePageText : '第',//页数文本框前显示的汉字
afterPageText : '页 共 {pages} 页',
displayMsg : '当前显示 {from} - {to} 条记录 共 {total} 条记录',
onSelectPage : function(pageNo, pageSize) {
var start = (pageNo - 1) * pageSize;
var end = start + pageSize;
alert(pageNo);
$("#dg").datagrid('options').url = "servlet/UserServlet?page="
+ pageNo+"&rows="+end; //设置表格数据的来源URL
$("#dg").datagrid('reload'); //重新加载表格
}
});
});
function editrow(index){
var row=$("#dg").datagrid("getSelected");
if(row==null){
alert("请选择你要编辑的行");
return;
}
$("#dg").datagrid("beginEdit",index);
}
function saverow(index){
$("#dg").datagrid("endEdit",index);
var row=$("#dg").datagrid("getSelected");
dbSave(row);
}
function dbSave(row){
alert(row.userName+"-"+row.pwd+"-"+row.displayName);
} */
var addFlag="0";
$(function() {
$('#dg').datagrid({
title : '用户列表',
method : 'GET',
url : 'servlet/UserServlet?action=select',
//fit : true,
fitColumns : true,
autoRowHeight : true,
checkOnSelect : false,
pagination : true,
striped : true,
nowrap : true,
multiSort : true,
ctrlSelect : true,
rownumbers : true,
collapsible : true,//是否可折叠的
pageSize:4,
pageList : [ 1, 2, 3 ,4],//可以设置每页记录条数的列表
frozenColumns : [ [ {
field : 'checkbox',
checkbox : true
} ] ],
toolbar : [ {
text : '查询',
iconCls : 'icon-search',
handler : function() {
$('#dg').datagrid('reload');
}
}, '-', {
text : '修改',
iconCls : 'icon-edit',
handler : function() {
alert('帮助按钮');
}
}, '-', {
text : '添加',
iconCls : 'icon-add',
handler : function() {
if(addFlag=="0"){
$('#dg').datagrid('insertRow',{
index:0,//索引从0开始
row:{}
});
var editIndex=0;
$('#dg').datagrid('selectRow',editIndex)
.datagrid('beginEdit',editIndex);
addFlag="1";
}
}
}, '-', {
text : '删除',
iconCls : 'icon-remove',
handler : function() {
$.messager.confirm("信息确认","确定删除吗?",function(ret){
if(ret){
var row =$("#dg").datagrid("getSelections");
if(row==null){
$.messager.alert("提示","请选择要删除的数据");
return;
}
var ids =[];
for(var i=0;i<row.length;i++){
ids.push(row[i].id);
}
$.post("servlet/UserServlet?action=delect",{uid:ids},
function(data){
if(data>0){
$('#dg').datagrid('reload');
alert("删除成功");
}else{
alert("删除失败");
}
});
}
});
}
} ],
columns : [ [ {
field : 'id',
hidden:true,
align : 'center'
},
{
field : 'userName',
title : '用户名',
width : 100,
sortable : true,
editor : 'text',
align : 'center'
}, {
field : 'pwd',
title : '密码',
width : 100,
editor : 'text',
align : 'center'
}, {
field : 'displayName',
title : '级别',
width : 100,
height : 100,
editor : 'text',
align : 'center'
},{
field : 'option',
title : '操作',
width : 100,
formatter: function(value,row,index){
if(row.editing){
var s ='<a href="javascript：void(0);" onclick="saverow('+index+')">save</a>'+' '
+'<a href="javascript：void(0);" onclick="cancleEdit('+index+')">cancle</a>';
return s;
}else{
var e ='<a href="javascript：void(0);" onclick="editrow('+index+')">edit</a>';
return e;
}
}
} ] ],
onBeforeEdit : function(index,row){
row.editing = true;
$("#dg").datagrid("refreshRow",index);
},
onAfterEdit: function(index,row){
row.editing = false;
$("#dg").datagrid("refreshRow",index);
}
});
//设置分页控件
var p = $('#dg').datagrid('getPager');
$(p).pagination(
{
beforePageText : '第',//页数文本框前显示的汉字
afterPageText : '页 共 {pages} 页',
displayMsg : '当前显示 {from} - {to} 条记录 共 {total} 条记录',
/* onSelectPage : function(pageNo, pageSize) {
var start = (pageNo - 1) * pageSize;
var end = start + pageSize;
alert(pageNo);
$("#dg").datagrid('options').url = "servlet/UserServlet?page="
+ pageNo+"&rows="+end; //设置表格数据的来源URL
$("#dg").datagrid('reload'); //重新加载表格
} */
});
});
function editrow(index){
var row = $("#dg").datagrid("getSelected");
if(row == null){
alert("请选择您要编辑的行");
return;
}
$("#dg").datagrid("beginEdit",index);
}
function saverow(index){
$("#dg").datagrid("endEdit",index);
var row = $("#dg").datagrid("getSelected");
dbSave(row);
}
function cancleEdit(index){
$("#dg").datagrid("rejectChanges");
if(addFlag=="1"){
addFlag="0";
}
}
function dbSave(row){
var id=row.id;
var name=row.userName;
var pwd=row.pwd;
var dName=row.displayName;
if(addFlag=="1"){
$.post("servlet/UserServlet?action=add",
{userName:name,pwd:pwd,displayName:dName},
function(data){
if(data=="1"){
alert("添加成功");
}else{
alert("添加失败");
}
});
addFlag="0";
}else{
$.post("servlet/UserServlet?action=update",
{id:id,userName:name,pwd:pwd,displayName:dName},
function(data){
if(data=="1"){
alert("修改成功");
}else{
alert("修改失败");
}
});
}
}
</script>
</head>
<body>
<%=zyf%>
This is my main.JSP page.
<br> ${sessionScope.user.displayName}
<table class="easyui-datagrid" id="dg">
</table>
</body>
</html>
package com.jredu.web.filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
public class URLfilter implements Filter {
public URLfilter(){
}
@Override
public void destroy() {
// TODO Auto-generated method stub
}
@Override
public void doFilter(ServletRequest arg0, ServletResponse arg1,
FilterChain arg2) throws IOException, ServletException {
// TODO Auto-generated method stub
HttpServletResponse response=(HttpServletResponse)arg1;
HttpServletRequest request=(HttpServletRequest )arg0;
HttpSession session=request.getSession();
System.out.println("过滤器运行");
if(session.getAttribute("user")==null){
if(request.getRequestURI().indexOf("login/login.jsp")>0){
arg2.doFilter(request, response);
}else{
System.out.println("非法用户");
response.sendRedirect("login.jsp");
}
}
else{
arg2.doFilter(request, response);
}
//login/main.jsp *.jsp /login.jsp
// if(session.getAttribute("user")==null){
// if(request.getRequestURI().indexOf("login/login.jsp")>0){
// arg2.doFilter(request, response);
// }else{
//
// System.out.println("非法用户");
// response.sendRedirect("login.jsp");
// }
// }
}
@Override
public void init(FilterConfig arg0) throws ServletException {
// TODO Auto-generated method stub
}
}
