Javaweb06-JDBC - ToB企服应用市场:ToB评测及商务社交产业平台

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/animedb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8
user=root
password=root

复制代码

package com.kgc.jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
* JDBC数据库操作工具类-都是公共的静态方法（不需要实例化），减少代码冗余
* @author zhukang
*
*/
public class JDBCUtil {
/**
* 读取外部的数据库连接信息配置文件，获取数据库连接对象
*/
public static Connection getConnection() throws Exception {
// 创建Properties属性对象
Properties properties = new Properties();
// 使用反射机制，读取外部配置文件
InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
// 加载输入流对象，获取配置文件内容
properties.load(inputStream);
// 读取数据库连接信息
String driverClass = properties.getProperty("driverClass");
String jdbcUrl = properties.getProperty("jdbcUrl");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
// 加载驱动
Class.forName(driverClass);
// 获取数据库连接对象
return DriverManager.getConnection(jdbcUrl, user, password);
}
/**
* 增删改的通用方法：只需要提供执行的SQL语句和SQL语句需要的参数，使用预处理对象
*/
public static int update(String executeSql, Object ... params){
// 定义数据库连接和预处理操作对象
Connection conn = null;
PreparedStatement pstmt = null;
// 定义SQL语句执行的影响行数
int row = 0;
// 公共执行增删改的处理代码
try {
// 获取数据库连接
conn = getConnection();
// 创建预处理操作对象
pstmt = conn.prepareStatement(executeSql);
// 实现动态传参，注意: 传入的预编译SQL的?和传入的参数个数和顺序要一致，即：要保证一一对应
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
// 执行增删改操作，并获取影响行数
row = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
releaseResource(conn, pstmt, null);
}
// 返回影响行数
return row;
}
/**
* 查询的通用方法：只需要提供执行的SQL语句和SQL语句需要的参数，使用预处理对象
*/
public static ResultSet select(String executeSql, Object ... params){
// 定义数据库连接,预处理操作对象,结果集对象
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 公共执行查询的处理代码
try {
// 获取数据库连接
conn = getConnection();
// 创建预处理操作对象
pstmt = conn.prepareStatement(executeSql);
// 实现动态传参，注意: 传入的预编译SQL的?和传入的参数个数和顺序要一致，即：要保证一一对应
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
// 执行查询操作，并获取结果集
rs = pstmt.executeQuery();
} catch (Exception e) {
e.printStackTrace();
} finally {
//最好不释放资源，因为rs要返回，关闭后，直接外层不可以使用
releaseResource(conn, pstmt, rs);
}
// 返回查询结果集
return rs;
}
/**
* 释放数据库操作对象资源
*/
public static void releaseResource(Connection conn, Statement stmt, ResultSet rs){
try {
// 手动释放
if (null != rs) {
rs.close();
}
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

复制代码

<c:forEach items="${animesList}" var ="anime" varStatus="status">
<c:forEach items="${categoriesList}" var ="category" varStatus="status">
<c:if test="${ anime.cid eq category.id}" var="flag">
${category.name}
</c:if>
</c:forEach>
</c:forEach>

复制代码

<select name="cid" id="categories">
<c:forEach items="${categoriesList}" var ="category" varStatus="status">
<option value="${category.id }"
<c:if test="${ upAnimes.cid eq category.id}" var="flag">
selected="selected"
</c:if>
>${category.name }</option>
</c:forEach>
</select>

复制代码

resp.getWriter().print("");

复制代码

getPartment(str); //如果属性，没有参数就是空字符串“” ,如果没有属性，获取的就是null
getPartment(str).trim(); //防止不小心多输入空格，去掉前后的空格,但是 trim()必须保证参数值不为 null

复制代码

//注意这种参数判断的思想
//条件查询动漫
public List<Animes> searchAnimes(Search search) throws SQLException{
List<Animes> animesList = new ArrayList<>();
String executeSql = "select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from animes";
//参数
Object[] params = {};
//如果有参数
if(!"".equals(String.valueOf(search.getAnimeName())) || !"".equals(String.valueOf(search.getAnimeAuthor())) || !"0".equals(String.valueOf(search.getAnimeCategory()))) {
executeSql = executeSql.concat(" where");
}
//如果有动漫名
if(!"".equals(String.valueOf(search.getAnimeName()))) {
executeSql = executeSql.concat(" name like concat('%',?,'%')");
Object[] params1 = {search.getAnimeName()};
params = params1;
}
//如果有动漫名和作者
if(!"".equals(String.valueOf(search.getAnimeName())) && !"".equals(String.valueOf(search.getAnimeAuthor()))) {
executeSql = executeSql.concat(" and author like concat('%',?,'%')");
Object[] params2 = {search.getAnimeName(),search.getAnimeAuthor()};
params = params2;
}else if(!"".equals(String.valueOf(search.getAnimeAuthor()))){
//如果作者
executeSql = executeSql.concat(" author like concat('%',?,'%')");
Object[] params3 = {search.getAnimeAuthor()};
params = params3;
}
//如果有作者名和动漫名和类型
if(!"".equals(String.valueOf(search.getAnimeName())) && !"".equals(String.valueOf(search.getAnimeAuthor())) && !"0".equals(String.valueOf(search.getAnimeCategory()))) {
executeSql = executeSql.concat(" and cid = ?");
Object[] params4 = {search.getAnimeName(),search.getAnimeAuthor(),search.getAnimeCategory()};
params = params4;
}else if(!"".equals(String.valueOf(search.getAnimeName())) && !"0".equals(String.valueOf(search.getAnimeCategory()))) {
//动漫名和类型
executeSql = executeSql.concat(" and cid = ?");
Object[] params5 = {search.getAnimeName(),search.getAnimeCategory()};
params = params5;
}else if(!"".equals(String.valueOf(search.getAnimeAuthor())) && !"0".equals(String.valueOf(search.getAnimeCategory()))) {
//作者和类型
executeSql = executeSql.concat(" and cid = ?");
Object[] params6 = {search.getAnimeAuthor(),search.getAnimeCategory()};
params = params6;
}else if(!"0".equals(String.valueOf(search.getAnimeCategory()))){
//类型
executeSql = executeSql.concat(" cid = ?");
Object[] params7 = {search.getAnimeCategory()};
params = params7;
}
System.out.println("searchAnimes 搜索的SQL==>"+executeSql);
ResultSet rs = JDBCUtil.select(executeSql, params);
while(rs.next()) {
Animes anime = new Animes();
anime.setId(rs.getInt("id"));
anime.setCid(rs.getInt("cid"));
anime.setName(rs.getString("name"));
anime.setAuthor(rs.getString("author"));
anime.setActor(rs.getString("actor"));
anime.setProduce(rs.getString("produce"));
anime.setCreate_date(rs.getDate("create_date"));
animesList.add(anime);
}
System.out.println("----- AnimesImpl 遍历数据库中查询的数据 ----");
for (int i = 0; i < animesList.size(); i++) {
System.out.println(animesList.get(i).toString());
}
System.out.println("----- AnimesImpl 遍历数据库中查询的数据结束 ---");
return animesList;
}

复制代码

//标记关键字 name
String markName = "replace(`name`,'"+search.getAnimeName() +"',""+search.getAnimeName()+"") as 'name'";
//标记关键字 author
String markAuthor = "replace(`author`,'"+search.getAnimeAuthor() +"',""+search.getAnimeAuthor()+"") as 'author'";
//执行的sql
String executeSql = "select `id`,`cid`,"+markName+","+markAuthor+",`actor`,`produce`,`create_date` from animes";

复制代码

//分页查询所有的动漫数据
public List<Animes> pageSelectAllAnimes(PageUtil pageUtil) throws SQLException{
List<Animes> animesList = new ArrayList<>();
String executeSql = "select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from animes limit ?,?";
//参数
Object[] params = {(pageUtil.getPageNum()-1)*pageUtil.getPageSize(),pageUtil.getPageSize()};
ResultSet rs = JDBCUtil.select(executeSql, params);
while(rs.next()) {
Animes anime = new Animes();
anime.setId(rs.getInt("id"));
anime.setCid(rs.getInt("cid"));
anime.setName(rs.getString("name"));
anime.setAuthor(rs.getString("author"));
anime.setActor(rs.getString("actor"));
anime.setProduce(rs.getString("produce"));
anime.setCreate_date(rs.getDate("create_date"));
animesList.add(anime);
}
System.out.println("----- AnimesImpl 打印测试当前页=》"+pageUtil.getPageNum()+",页面大小=》"+pageUtil.getPageSize());
for (int i = 0; i < animesList.size(); i++) {
System.out.println(animesList.get(i).toString());
}
System.out.println("-----------------------------------");
return animesList;
}

复制代码

//分页工具类
public class PageUtil {
//一共多少条数据
private int dateCount;
//每页显示的数据条数
private int pageSize = 5;
//当前页
private int pageNum = 1;
//最大页数
private int maxPageNum;
//构造方法,需要数据量和页面大小
public PageUtil(int dateCount) {
this.dateCount = dateCount;
this.maxPageNum = (this.dateCount%pageSize) == 0 ? (this.dateCount/pageSize) : (this.dateCount/pageSize) +1 ;
}
//获取当前页面
public int getPageNum() {
return pageNum;
}
//设置当前页面
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
//获取最大页面数
public int getMaxPageNum() {
return this.maxPageNum;
}
//获取页面大小
public int getPageSize() {
return pageSize;
}
//设置页面大小
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
//页面大小切换后应该重新切换页面数量
this.maxPageNum = (this.dateCount%pageSize) == 0 ? (this.dateCount/pageSize) : (this.dateCount/pageSize) +1 ;
}
//获取数据总条数
public int getDateCount() {
return dateCount;
}
}

复制代码

[/code]跳转页面的链接 [b]首页，上一页，下一页，尾页[/b]
[code]<a target="_blank" href="https://www.cnblogs.com/pageSearch?pageNum=1&pageSize=" >首页</a> |
<c:if test="${pageUtil.pageNum ne 1}" var="sexFlag">
<a target="_blank" href="https://www.cnblogs.com/pageSearch?pageNum=${pageUtil.pageNum -1 }&pageSize=" ><<上一页</a> |
</c:if>
<c:if test="${pageUtil.pageNum ne pageUtil.maxPageNum}" var="sexFlag">
<a target="_blank" href="https://www.cnblogs.com/pageSearch?pageNum=${pageUtil.pageNum +1 }&pageSize=" >下一页>></a> |
</c:if>
<a target="_blank" href="https://www.cnblogs.com/pageSearch?pageNum=${pageUtil.maxPageNum }&pageSize=" >尾页</a> |

复制代码

<select name="pageSize" id="pageSize">
<c:forEach items="${pageSizeList}" var ="pageSize" varStatus="status">
<option value="${pageSize.pageSize}"
<c:if test="${pageUtil.pageSize eq pageSize.pageSize}">
selected = "selected"
</c:if>
>${pageSize.pageSize}</option>
</c:forEach>
</select>

复制代码

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//======从数据库获取动漫数据不走条件查询======
List<Animes> animesList = new ArrayList<>();
AnimesImpl animesImpl = new AnimesImpl();
// try {
// animesList = animesImpl.selectAllAnimes();
// } catch (SQLException e) {
// e.printStackTrace();
// }
//======从数据库获取动漫数据走条件查询======
//获取动漫的总条数
int dateCount = 0;
try {
dateCount = animesImpl.selectAllAnimesCounts();
} catch (SQLException e) {
e.printStackTrace();
}
PageUtil pageUtil = new PageUtil(dateCount);
//将分页对象放进session域中，初始化页面对象
req.getSession().setAttribute("pageUtil", pageUtil);
try {
animesList = animesImpl.pageSelectAllAnimes(pageUtil);
} catch (SQLException e) {
e.printStackTrace();
}
//将动漫集合放入request中
req.setAttribute("animesList", animesList);
//======动漫类型======
List<Category> categoriesList = new ArrayList<>();
CategoryImpl categoryImpl = new CategoryImpl();
try {
categoriesList = categoryImpl.selectAllCategories();
} catch (SQLException e) {
e.printStackTrace();
}
//将类型数据放进session中
req.getSession().setAttribute("categoriesList", categoriesList);
//页面大小
List<PageSize> pageSizeList = new ArrayList<PageSize>();
pageSizeList.add(new PageSize("3"));
pageSizeList.add(new PageSize("5"));
pageSizeList.add(new PageSize("10"));
pageSizeList.add(new PageSize("15"));
//将pageSize放进session中
req.getSession().setAttribute("pageSizeList", pageSizeList);
System.out.println("==AnimeServlet 动漫数据条数"+dateCount+"当前页"+pageUtil.getPageNum()+"最大页"+pageUtil.getMaxPageNum()+"===");
//转发到动漫列表页面
req.getRequestDispatcher("web6Jdbc/animeListJson.jsp").forward(req, resp);
}

复制代码

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//第几页
int pageNum = Integer.parseInt(req.getParameter("pageNum")==null ? "1" :req.getParameter("pageNum"));
//页面大小
int pageSize = Integer.parseInt(req.getParameter("pageSize")==null ? "5" :req.getParameter("pageSize"));
System.out.println("PageSelectAnimeServlet 当前页=》"+pageNum+",页面大小=》"+pageSize);
AnimesImpl animesImpl = new AnimesImpl();
PageUtil pageUtil = null;
try {
//现在每次实例化都先获取数据总数,后面考虑更好的方法 ??小问题
pageUtil = new PageUtil(animesImpl.selectAllAnimesCounts());
} catch (SQLException e1) {
e1.printStackTrace();
}
pageUtil.setPageNum(pageNum);
pageUtil.setPageSize(pageSize);
//每次分页查询要将分页工具类重新更新
req.getSession().setAttribute("pageUtil", pageUtil);
List<Animes> animesList = new ArrayList<Animes>();
try {
animesList = animesImpl.pageSelectAllAnimes(pageUtil);
//将动漫集合放入request中
req.setAttribute("animesList", animesList);
} catch (SQLException e) {
e.printStackTrace();
}
//转发到动漫列表页面
req.getRequestDispatcher("web6Jdbc/animeListJson.jsp").forward(req, resp);
}

复制代码