1框架struts2个人高效秘籍：分页组合查询easyui实现模块管理，增删改查【诗书画唱】
CTRL+F：getBytes中文乱码处理、讲义、关于分页查询到的总条数的准确显现的实现、视频和笔记、通过循环设置参数、if判断非空。
提前说明：
因为网页上是有时保存不了href，src的，所以我用CTRL+F，统一替换href为hreff等，如果是常3连我的视频和专栏者，就可以用CTRL+F，替换hreff为href。（这个是我个人的独门高效秘籍诶！还不常3连？只要常3连，我的个人的独门高效秘籍就是“大大”的有！(＠￣ー￣＠)）
关于分页查询到的总条数的准确显现的实现 START
分页组合查询部分：
分页组合查询后的总条数的获得：
关于分页查询到的总条数的准确显现的实现 END
讲义 START
组合分页查询
根据gname进行模糊查询like ?
根据gtype下拉框进行精确的匹配=?
根据Gcomp进行模糊查询like ?
根据gyear进行范围的匹配between ? and ?
讲义 END
例子 START
select* from game
create table game(
id int primary key auto_increment,
gname varchar(100),
gtype varchar(100),
Gcomp varchar(100),
gyear varchar(100)
);
drop table game
insert into game(gname,
gtype,
Gcomp,
gyear ) values("游戏名1","游戏类型1","游戏公司1","游戏发行时间1");
insert into game(gname,
gtype,
Gcomp,
gyear ) values("游戏名2","游戏类型2","游戏公司2","游戏发行时间2");
insert into game(gname,
gtype,
Gcomp,
gyear ) values("游戏名3","游戏类型3","游戏公司3","游戏发行时间3");
insert into game(gname,
gtype,
Gcomp,
gyear ) values("游戏名4","游戏类型4","游戏公司4","游戏发行时间4");
package com.SSHC.action;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.struts2.ServletActionContext;
import com.SSHC.bean.Game;
import com.SSHC.dao.GameDao;
public class GameAction {
private Map<String,Object>map = new HashMap<String,Object>();
private GameDao gameDao = new GameDao();
private Game g;
private InputStream ins;
//添加分页属性
private Integer rows;//每页记录条数
private Integer page;//页码数
public Map<String, Object> getMap() {
return map;
}
public void setMap(Map<String, Object> map) {
this.map = map;
}
public GameDao geTgameDao() {
return gameDao;
}
public void seTgameDao(GameDao gameDao) {
this.gameDao = gameDao;
}
public Integer getRows() {
return rows;
}
public void setRows(Integer rows) {
this.rows = rows;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Game geTg() {
return g;
}
public void seTg(Game g) {
this.g = g;
}
public InputStream getIns() {
return ins;
}
public void setIns(InputStream ins) {
this.ins = ins;
}
public String loadAll() throws UnsupportedEncodingException{
if(g == null) {
g = new Game();
}
g.setPage(page);
g.setRows(rows);
List<Game>list = gameDao.selectByPageAndCond(g);
List<Game> selectByPageAndCondTotal = gameDao.selectByPageAndCondTotal(g);
Integer total = gameDao.total(g);
map.put("rows", list);
map.put("total", selectByPageAndCondTotal.size());
return "success";
}
public String addGame(){
try {
Integer count = gameDao.add(g);
//{ct:1}
ServletActionContext.getResponse()
.getWriter().write("{\"ct\":" + count + "}");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public String deleteGame(){
try {
Integer count = gameDao.delete(g.getId());
//{ct:1}
ServletActionContext.getResponse()
.getWriter().write("{\"ct\":" + count + "}");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//加载游戏类型下拉框中的数据
public String loadSel() throws UnsupportedEncodingException{
//String jsonStr = "[{\"id\":\"1\",\"gtype\":\"RPG\"}]";
StringBuilder jsonStr = new StringBuilder("[");
List<String>list = gameDao.getAllType();
String dot = "";
for(String s : list) {
jsonStr.append(dot);
jsonStr.append("{\"id\":\"" + s + "\",\"gtype\":\""
+ s + "\"}");
dot = ",";
}
jsonStr.append("]");
ins = new ByteArrayInputStream(jsonStr.toString().getBytes("utf-8"));
return "success";
}
public String toEdit(){
g = gameDao.selectById(g.getId());
return "success";
}
public String ediTgame(){
try {
Integer count = gameDao.update(g);
//{ct:1}
ServletActionContext.getResponse()
.getWriter().write("{\"ct\":" + count + "}");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
package com.SSHC.bean;
public class Game {
private Integer id;
private String gname;
private String gtype;
private String Gcomp;
private String gyear;
//分页属性
private Integer page;
private Integer rows;
//查询属性
private String startYear;//开始年份
private String endYear;//截止年份
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String geTgname() {
return gname;
}
public void seTgname(String gname) {
this.gname = gname;
}
public String geTgtype() {
return gtype;
}
public void seTgtype(String gtype) {
this.gtype = gtype;
}
public String geTgcomp() {
return Gcomp;
}
public void seTgcomp(String Gcomp) {
this.Gcomp = Gcomp;
}
public String geTgyear() {
return gyear;
}
public void seTgyear(String gyear) {
this.gyear = gyear;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getRows() {
return rows;
}
public void setRows(Integer rows) {
this.rows = rows;
}
public String getStartYear() {
return startYear;
}
public void setStartYear(String startYear) {
this.startYear = startYear;
}
public String getEndYear() {
return endYear;
}
public void setEndYear(String endYear) {
this.endYear = endYear;
}
}
package com.SSHC.dao;
import java.io.UnsupportedEncodingException;
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.SSHC.bean.Game;
import com.SSHC.util.DbUtil;
public class GameDao {
public List<Game>selectByPageAndCond(Game game)
throws UnsupportedEncodingException{
StringBuilder sql =
new StringBuilder("select * from game where 1 = 1 ");
List<Object>params = new ArrayList<Object>();
String gname = game.geTgname();
if(gname != null && gname.length() > 0) {
gname = new String(gname.getBytes("iso8859-1"),"utf-8");
params.add("%" + gname + "%");
sql.append("and gname like ? ");
}
String gtype = game.geTgtype();
if(gtype != null && gtype.length() > 0) {
gtype = new String(gtype.getBytes("iso8859-1"),"utf-8");
params.add(gtype);
sql.append("and gtype = ? ");
}
String Gcomp = game.geTgcomp();
if(Gcomp != null && Gcomp.length() > 0) {
Gcomp = new String(Gcomp.getBytes("iso8859-1"),"utf-8");
params.add("%" + Gcomp + "%");
sql.append("and Gcomp like ? ");
}
String startYear = game.getStartYear();
String endYear = game.getEndYear();
if(startYear != null && startYear.length() > 0) {
params.add(startYear);
sql.append("and gyear > ? ");
}
if(endYear != null && endYear.length() > 0) {
params.add(endYear);
sql.append("and gyear < ? ");
}
Integer page = game.getPage();
Integer rows = game.getRows();
params.add((page - 1) * rows);
params.add(rows);
sql.append("limit ?,? ");
List<Game>list = new ArrayList<Game>();
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql.toString());
for(int i = 0;i < params.size();i ++) {
pstm.setObject((i + 1), params.get(i));
}
rs = pstm .executeQuery();
while(rs.next()){
Game g = new Game();
g.setId(rs.getInt("id"));
g.seTgname(rs.getString("gname"));
g.seTgtype(rs.getString("gtype"));
g.seTgcomp(rs.getString("Gcomp"));
g.seTgyear(rs.getString("gyear"));
list.add(g);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DbUtil.close(rs, pstm, conn);
}
return list;
}
public List<Game>selectByPageAndCondTotal(Game game)
throws UnsupportedEncodingException{
StringBuilder sql =
new StringBuilder("select * from game where 1 = 1 ");
List<Object>params = new ArrayList<Object>();
String gname = game.geTgname();
if(gname != null && gname.length() > 0) {
gname = new String(gname.getBytes("iso8859-1"),"utf-8");
params.add("%" + gname + "%");
sql.append("and gname like ? ");
}
String gtype = game.geTgtype();
if(gtype != null && gtype.length() > 0) {
gtype = new String(gtype.getBytes("iso8859-1"),"utf-8");
params.add(gtype);
sql.append("and gtype = ? ");
}
String Gcomp = game.geTgcomp();
if(Gcomp != null && Gcomp.length() > 0) {
Gcomp = new String(Gcomp.getBytes("iso8859-1"),"utf-8");
params.add("%" + Gcomp + "%");
sql.append("and Gcomp like ? ");
}
String startYear = game.getStartYear();
String endYear = game.getEndYear();
if(startYear != null && startYear.length() > 0) {
params.add(startYear);
sql.append("and gyear > ? ");
}
if(endYear != null && endYear.length() > 0) {
params.add(endYear);
sql.append("and gyear < ? ");
}
/* Integer page = game.getPage();
Integer rows = game.getRows();
params.add((page - 1) * rows);
params.add(rows);
sql.append("limit ?,? ");*/
List<Game>list = new ArrayList<Game>();
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql.toString());
for(int i = 0;i < params.size();i ++) {
pstm.setObject((i + 1), params.get(i));
}
rs = pstm .executeQuery();
while(rs.next()){
Game g = new Game();
g.setId(rs.getInt("id"));
g.seTgname(rs.getString("gname"));
g.seTgtype(rs.getString("gtype"));
g.seTgcomp(rs.getString("Gcomp"));
g.seTgyear(rs.getString("gyear"));
list.add(g);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DbUtil.close(rs, pstm, conn);
}
return list;
}
public Integer total(Game game) throws UnsupportedEncodingException{
// String sql = "select count(*) ct from game";
//组合查询 START
StringBuilder sql =
new StringBuilder( "select count(*) ct from game where 1=1");
Integer count = 0;
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql.toString());
// for(int i = 0;i < params.size();i ++) {
// pstm.setObject((i + 1), params.get(i));
// }
rs = pstm.executeQuery();
// List<Game>list = new ArrayList<Game>();
if(rs.next()) {
count = rs.getInt("ct");
// Game g = new Game();
// g.setId(rs.getInt("id"));
// g.seTgname(rs.getString("gname"));
// g.seTgtype(rs.getString("gtype"));
// g.seTgcomp(rs.getString("Gcomp"));
// g.seTgyear(rs.getString("gyear"));
// list.add(g);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DbUtil.close(rs, pstm, conn);
}
return count;
}
public Integer add(Game g){
String sql = "insert into game (gname,gtype,Gcomp,gyear) values(?,?,?,?)";
Connection conn = null;
PreparedStatement pstm = null;
Integer count = 0;
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql);
pstm.setString(1, g.geTgname());
pstm.setString(2, g.geTgtype());
pstm.setString(3, g.geTgcomp());
pstm.setString(4, g.geTgyear());
count = pstm.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
public Integer delete(Integer id){
String sql = "delete from game where id = ?";
Connection conn = null;
PreparedStatement pstm = null;
Integer count = 0;
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql);
pstm.setInt(1, id);
count = pstm.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
public List<String> getAllType(){
String sql = "select distinct gtype from game";
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
List<String>list = new ArrayList<String>();
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()) {
list.add(rs.getString("gtype"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public Game selectById(Integer id){
String sql = "select * from game where id = ?";
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
Game g = new Game();
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql);
pstm.setInt(1, id);
rs = pstm.executeQuery();
if(rs.next()) {
g.setId(id);
g.seTgname(rs.getString("gname"));
g.seTgtype(rs.getString("gtype"));
g.seTgcomp(rs.getString("Gcomp"));
g.seTgyear(rs.getString("gyear"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return g;
}
public Integer update(Game g){
String sql = "update game set gname = ?,gtype = ?,Gcomp = ?,gyear = ? where id = ?";
Connection conn = null;
PreparedStatement pstm = null;
Integer count = 0;
try {
conn = DbUtil.getConn();
pstm = conn.prepareStatement(sql);
pstm.setString(1, g.geTgname());
pstm.setString(2, g.geTgtype());
pstm.setString(3, g.geTgcomp());
pstm.setString(4, g.geTgyear());
pstm.setInt(5, g.getId());
count = pstm.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
}
package com.SSHC.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class DbUtil {
private static String driverName;
private static String url;
private static String user;
private static String pwd;
static {
//读取properties文件
Properties prop = new Properties();
//将db.properties文件读取到内存中去
InputStream is = DbUtil.class.getClassLoader()
.getResourceAsStream("db.properties");
//加载内容
try {
prop.load(is);
//读取内容
driverName = prop.getProperty("drivername");
url = prop.getProperty("url");
user = prop.getProperty("username");
pwd = prop.getProperty("password");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取数据库连接对象的方法
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,pwd);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet rs,PreparedStatement pstm
,Connection conn){
try {
if(rs != null) {
rs.close();
}
if(pstm != null) {
pstm.close();
}
if(conn != null) {
conn.close();
}
} catch(Exception e) {
e.printStackTrace();
}
}
}
drivername=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/firstjsp?useUnicode=true&characterEncoding=UTF-8
username=root
password=root
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<package name="my" namespace="/gm" extends="json-default">
<action name="ldAc" class="com.SSHC.action.GameAction"
method="loadData">
<result type="stream">
<param name="contentType">text/plain</param>
<!-- action返回的字符串的内容取自GameAction的哪个属性 -->
<param name="inputName">ins</param>
</result>
</action>
<action name="ldAllAc" class="com.SSHC.action.GameAction"
method="loadAll">
<result type="json">
<param name="root">map</param>
<param name="contentType">text/html</param>
</result>
</action>
<action name="addAc" class="com.SSHC.action.GameAction"
method="addGame">
</action>
<action name="deleteAc" class="com.SSHC.action.GameAction"
method="deleteGame">
</action>
<action name="loadTypeAc" class="com.SSHC.action.GameAction"
method="loadSel">
<result type="stream">
<param name="contentType">text/plain</param>
<param name="inputName">ins</param>
</result>
</action>
<action name="toEditAc" class="com.SSHC.action.GameAction"
method="toEdit">
<result>/edit.jsp</result>
</action>
<action name="editAc" class="com.SSHC.action.GameAction"
method="ediTgame">
</action>
</package>
</struts>
视频和笔记 START
getBytes中文乱码处理：
因为一开始打开界面时g为null，不加if判断非空就g.setXXX等，就会报错，所以下面的代码是这么写的：
通过循环设置参数：
