CTRL+F：getBytes中文亂碼處理、講義、關(guān)于分頁查詢到的總條數(shù)的準(zhǔn)確顯現(xiàn)的實(shí)現(xiàn)、視頻和筆記、通過循環(huán)設(shè)置參數(shù)、if判斷非空。

提前說明：

因?yàn)榫W(wǎng)頁上是有時(shí)保存不了href，src的，所以我用CTRL+F，統(tǒng)一替換href為hreff等，如果是常3連我的視頻和專欄者，就可以用CTRL+F，替換hreff為href。（這個(gè)是我個(gè)人的獨(dú)門高效秘籍誒！還不常3連？只要常3連，我的個(gè)人的獨(dú)門高效秘籍就是“大大”的有！(＠￣ー￣＠)）

關(guān)于分頁查詢到的總條數(shù)的準(zhǔn)確顯現(xiàn)的實(shí)現(xiàn)? START

分頁組合查詢部分：

分頁組合查詢后的總條數(shù)的獲得：

關(guān)于分頁查詢到的總條數(shù)的準(zhǔn)確顯現(xiàn)的實(shí)現(xiàn)? END

講義 START

組合分頁查詢

根據(jù)gname進(jìn)行模糊查詢like ?

根據(jù)gtype下拉框進(jìn)行精確的匹配=?

根據(jù)Gcomp進(jìn)行模糊查詢like ?

根據(jù)gyear進(jìn)行范圍的匹配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","游戲發(fā)行時(shí)間1");

insert into? game(gname,

gtype,

Gcomp,

gyear ) values("游戲名2","游戲類型2","游戲公司2","游戲發(fā)行時(shí)間2");

insert into? game(gname,

gtype,

Gcomp,

gyear ) values("游戲名3","游戲類型3","游戲公司3","游戲發(fā)行時(shí)間3");

insert into? game(gname,

gtype,

Gcomp,

gyear ) values("游戲名4","游戲類型4","游戲公司4","游戲發(fā)行時(shí)間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;//每頁記錄條數(shù)

? ? private Integer page;//頁碼數(shù)

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;

}

//加載游戲類型下拉框中的數(shù)據(jù)

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文件讀取到內(nèi)存中去

? ? InputStream is = DbUtil.class.getClassLoader()

? ? .getResourceAsStream("db.properties");

? ? //加載內(nèi)容

? ? try {

prop.load(is);

//讀取內(nèi)容

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();

}

? ? }

? ??

? ? //獲取數(shù)據(jù)庫連接對(duì)象的方法

? ? 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返回的字符串的內(nèi)容取自GameAction的哪個(gè)屬性 -->

? ? ? ? ? ? ? ? ?<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中文亂碼處理：

因?yàn)橐婚_始打開界面時(shí)g為null，不加if判斷非空就g.setXXX等，就會(huì)報(bào)錯(cuò)，所以下面的代碼是這么寫的：

通過循環(huán)設(shè)置參數(shù)：

視頻和筆記 END