Json和Ajax
LogDao接口
package com.log.dao;
import java.util.List;
import com.log.Vo.LogInfoVo;
import com.log.bean.LogInfo;
public interface LogDao {
//添加信息
boolean insertLog(LogInfo log);
//删除
boolean deleteLog(int id);
//更改
boolean updateLog(LogInfo log);
//查询列表
List<LogInfo> getLogList();
//根据id查询
LogInfo getLogById(int id);
//获取总条数
int getLogCount();
//根据分页信息查询列表
List<LogInfoVo> getLogVoListByPage(int pageIndex,int pageSize);
//
List<LogInfoVo> getLogvoList();
}
接口实现类LogDaoImpl
package com.log.dao.impl;
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.log.Vo.LogInfoVo;
import com.log.bean.LogInfo;
import com.log.dao.LogDao;
import com.util.DBUtil;
public class LogDaoImpl extends DBUtil implements LogDao {
public boolean deleteLog(int id) {
boolean flag=false;
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn=getConn();
String sql="delete from logInfo where ID=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
int rowNum=pstmt.executeUpdate();
if(rowNum==1){
flag=true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, pstmt, null);
}
return flag;
}
public LogInfo getLogById(int id) {
LogInfo log=null;
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
conn=getConn();
pstmt=conn.prepareStatement("select * from logInfo where ID=?");
pstmt.setInt(1, id);
rs=pstmt.executeQuery();
if(rs.next()){
String title=rs.getString("logTitle");
String content=rs.getString("logContent");
String createTime=rs.getString("CreateTime");
int logId=rs.getInt("ID");
log=new LogInfo(logId,title,content,createTime);
log.setUserId(rs.getInt("userId"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, pstmt, rs);
}
return log;
}
public List<LogInfo> getLogList() {
List<LogInfo> logList=new ArrayList<LogInfo>() ;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
try {
conn=getConn();
statement=conn.createStatement();
rs=statement.executeQuery("select * from logInfo");
while(rs.next()){
String title=rs.getString("logTitle");
String content=rs.getString("logContent");
String createTime=rs.getString("CreateTime");
int logId=rs.getInt("ID");
LogInfo log=new LogInfo(logId,title,content,createTime);
log.setUserId(rs.getInt("userId"));
logList.add(log);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, statement, rs);
}
return logList;
}
public boolean insertLog(LogInfo log) {
boolean flag=false;
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn=getConn();
String sql="insert into logInfo(logTitle,logContent,createTime,userId,state) values(?,?,?,?,?)";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, log.getLogTitle());
pstmt.setString(2, log.getLogContent());
pstmt.setString(3, log.getCreateTime());
pstmt.setInt(4, log.getUserId());
pstmt.setInt(5, log.getState());
int rowNum=pstmt.executeUpdate();
if(rowNum==1){
flag=true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, pstmt, null);
}
return flag;
}
public boolean updateLog(LogInfo log) {
boolean flag=false;
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn=getConn();
String sql="update logInfo set logTitle=?,logContent=?,userId=? where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, log.getLogTitle());
pstmt.setString(2, log.getLogContent());
pstmt.setInt(3, log.getUserId());
pstmt.setInt(4, log.getID());
int rowNum=pstmt.executeUpdate();
if(rowNum==1){
flag=true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, pstmt, null);
}
return flag;
}
public int getLogCount() {
int count=0;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
try {
conn=getConn();
statement=conn.createStatement();
rs=statement.executeQuery("select count(ID) as num from logInfo");
if(rs.next()){
count=rs.getInt("num");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, statement, rs);
}
return count;
}
public List<LogInfoVo> getLogVoListByPage(int pageIndex,int pageSize) {
List<LogInfoVo> logList=new ArrayList<LogInfoVo>() ;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
try {
conn=getConn();
statement=conn.createStatement();
rs=statement.executeQuery("select l.*,u.U_Name,t.num from (select top "+pageSize+" * from logInfo where " +
"ID not in (select top "+(pageIndex-1)*pageSize+" ID from logInfo)) l left join userInfo u on l.userId=u.U_ID"+
" left join (select logid,COUNT(ID) num from comment group by logId) t on l.ID=t.logId");
while(rs.next()){
String title=rs.getString("logTitle");
String content=rs.getString("logContent");
String createTime=rs.getString("CreateTime");
int logId=rs.getInt("ID");
String uname=rs.getString("U_Name");
int commentcount=rs.getInt("num");
LogInfoVo log=new LogInfoVo(logId,title,content,createTime,uname,commentcount);
log.setUserId(rs.getInt("userId"));
logList.add(log);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, statement, rs);
}
return logList;
}
public List<LogInfoVo> getLogvoList(){
List<LogInfoVo> logList=new ArrayList<LogInfoVo>() ;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
try {
conn=getConn();
statement=conn.createStatement();
rs=statement.executeQuery("select l.*,u.U_Name,t.num from logInfo l left join userInfo u on l.userId=u.U_ID" +
" left join (select logid,COUNT(ID) num from comment group by logId) t on l.ID=t.logId");
while(rs.next()){
String title=rs.getString("logTitle");
String content=rs.getString("logContent");
String createTime=rs.getString("CreateTime");
int logId=rs.getInt("ID");
String uname=rs.getString("U_Name");
int commentcount=rs.getInt("num");
LogInfoVo log=new LogInfoVo(logId,title,content,createTime,uname,commentcount);
log.setUserId(rs.getInt("userId"));
logList.add(log);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, statement, rs);
}
return logList;
}
}
json和ajax实现日志表的获取并用分页实现
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@page import="com.log.dao.LogDao"%>
<%@page import="com.log.dao.impl.LogDaoImpl"%>
<%@page import="com.log.Vo.LogInfoVo"%>
<%@page import="net.sf.json.JSONArray"%>
<%
LogDao logDao=new LogDaoImpl();
String str=request.getParameter("pageIndex");
int newIndex=0;
if(str!=null&&!str.equals("")){
newIndex=Integer.parseInt(str);
}
int count=logDao.getLogCount(); //获得数据的总条数
int pageSize=3; //每一页的数据条数
int pangCount=count%pageSize==0?(count/pageSize):(count/pageSize+1); //判断总共有多少条
if(newIndex<1){
newIndex=1;
}else if(newIndex>pangCount){
newIndex=pangCount;
}
List<LogInfoVo> logInfos=logDao.getLogVoListByPage(newIndex,pageSize); //调用logDao的getLogVoListByPage进行分页
JSONArray jsonarray=new JSONArray(); //创建json数组对象jsonarray
jsonarray.add(0,JSONArray.fromObject(logInfos)); // 把logInfos集合对象转换为json数组对象并把其加入到jsonarray数组对象中
jsonarray.add(1,newIndex); //把newIndex加入到jsonarray数组对象中
out.print(jsonarray); //输出jsonarray数组对象。
%>
<%@ 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>
<base href="<%=basePath%>">
<title>Ajax获取用户列表</title>
<script type="text/javascript" src="js/jquery-1.7.2.js"></script>
<script type="text/javascript">
var index=1;
function shouList(vars){
//$("#listLog #newTr").remove();
$("#listLog tr").remove("#newTr");
index=index+vars;
if(index<1){
index=1;
}
$.post("Ajax/doList.jsp",{"pageIndex":index},function(date){
$.each(date[0],function(index,val){ //date[0]得到日志列表，类型为json数组
$("#listLog").append("<tr id='newTr'><td>"+index+"</td><td>"+val.ID+"</td><td>"+val.logTitle+"</td></tr>");
});
index=date[1]; //得到newIndex
},"json");
}
</script>
</head>
<body>
<input type="button" value="显示列表" onclick="shouList(0)">
<table id="listLog">
<tr>
<td>编号</td>
<td>ID</td>
<td>标题</td>
</tr>
</table>
<input type="button" value="上一页" onclick="shouList(-1)">
<input type="button" value="下一页" onclick="shouList(1)">
</body>
</html>
得到的Json数组对象
[
[{"ID":1,"UName":"admin","commentCount":2,"createTime":"2016-06-03 08:24:42.973","logContent":"日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容","logTitle":"日志标题","userId":1},
{"ID":2,"UName":"aaa","commentCount":0,"createTime":"2016-06-03 08:31:35.41","logContent":"日志内容222222222222","logTitle":"日志标题2","userId":2},
{"ID":3,"UName":"admin","commentCount":0,"createTime":"2016-06-03 15:35:40.047","logContent":"日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容日志内容3333","logTitle":"日志标题3","userId":1}],
1]
