导入所需jar包
下面是包
建包util 放入写好的辅助类
首先是第一个config.properties 这个是放连接数据库工具类
里面可连接Oracle sqlserver mysql 的不同版本 这里我连接的是mysql 所以其他的先注释 打开mysql
mysql://localhost:3306/xx? localhost是连接名字 3306是端口号 xx是表的名字
我mysql 密码为空所以什么都没写
#oracle9i
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#user=scott
#pwd=123
#sql2005
#driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#url=jdbc:sqlserver://localhost:1433;DatabaseName=test1
#user=sa
#pwd=123
#sql2000
#driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
#url=jdbc:microsoft:sqlserver://localhost:1433;databaseName=unit6DB
#user=sa
#pwd=888888
#mysql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/xx?useUnicode=true&characterEncoding=UTF-8
user=root
pwd=
接下来放入连接工具DBAccess
package com.xx.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 提供了一组获得或关闭数据库对象的方法
* @author 小曦
*
* http://www.javaxl.com
*/
public class DBAccess {
private static String driver;
private static String url;
private static String user;
private static String password;
static {// 静态块执行一次，加载 驱动一次
try {
InputStream is = DBAccess.class
.getResourceAsStream("config.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("pwd");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 获得数据连接对象
*
* @return
*/
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void close(ResultSet rs) {
if (null != rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
public static void close(Statement stmt) {
if (null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
public static void close(Connection conn) {
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
close(rs);
close(stmt);
close(conn);
}
public static boolean isOracle() {
return "oracle.jdbc.driver.OracleDriver".equals(driver);
}
public static boolean isSQLServer() {
return "com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driver);
}
public static boolean isMysql() {
return "com.mysql.jdbc.Driver".equals(driver);
}
public static void main(String[] args) {
Connection conn = DBAccess.getConnection();
DBAccess.close(conn);
System.out.println("isOracle：" + isOracle());
System.out.println("isSQLServer：" + isSQLServer());
System.out.println("isMysql：" + isMysql());
System.out.println("数据库连接(关闭)成功");
}
}
还有中文乱码处理类EncodingFiter
package com.xx.util;
import java.io.IOException;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
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;
/**
* 中文乱码处理
*
*/
public class EncodingFiter implements Filter {
private String encoding = "UTF-8";// 默认字符集
public EncodingFiter() {
super();
}
public void destroy() {
}
public void doFilter(ServletRequest request, ServletResponse response,
FilterChain chain) throws IOException, ServletException {
HttpServletRequest req = (HttpServletRequest) request;
HttpServletResponse res = (HttpServletResponse) response;
// 中文处理必须放到 chain.doFilter(request, response)方法前面
res.setContentType("text/html;charset=" + this.encoding);
if (req.getMethod().equalsIgnoreCase("post")) {
req.setCharacterEncoding(this.encoding);
} else {
Map map = req.getParameterMap();// 保存所有参数名=参数值(数组)的Map集合
Set set = map.keySet();// 取出所有参数名
Iterator it = set.iterator();
while (it.hasNext()) {
String name = (String) it.next();
String[] values = (String[]) map.get(name);// 取出参数值[注：参数值为一个数组]
for (int i = 0; i < values.length; i++) {
values[i] = new String(values[i].getBytes("ISO-8859-1"),
this.encoding);
}
}
}
chain.doFilter(request, response);
}
public void init(FilterConfig filterConfig) throws ServletException {
String s = filterConfig.getInitParameter("encoding");// 读取web.xml文件中配置的字符集
if (null != s && !s.trim().equals("")) {
this.encoding = s.trim();
}
}
}
工具类StringUtils
package com.xx.util;
public class StringUtils {
// 私有的构造方法，保护此类不能在外部实例化
private StringUtils() {
}
/**
* 如果字符串等于null或去空格后等于""，则返回true，否则返回false
*
* @param s
* @return
*/
public static boolean isBlank(String s) {
boolean b = false;
if (null == s || s.trim().equals("")) {
b = true;
}
return b;
}
/**
* 如果字符串不等于null或去空格后不等于""，则返回true，否则返回false
*
* @param s
* @return
*/
public static boolean isNotBlank(String s) {
return !isBlank(s);
}
}
拼音工具类，能将汉字转换成拼音的首字母 PinYinUtil 可有可无把看需求
package com.xx.util;
import java.util.regex.Pattern;
import net.sourceforge.pinyin4j.PinyinHelper;
/**
* 拼音工具类，能将汉字转换成拼音的首字母
*/
public class PinYinUtil {
/* 用于中文判断的正则表达式 */
private static final String regexStr = "[\u4e00-\u9fa5]";
/**
* 将一个字符串中的汉字转换成拼音首字母、非汉字则不变
*
* @param cn
* String
* @return String
*/
public static String toPinyin(String cn) {
String pinyin = null;
if (null == cn || 0 == cn.trim().length()) {
return pinyin;
}
/* 去掉字符串前后的空格 */
cn = cn.trim();
char[] chineseCharacterArr = cn.toCharArray(); // 转换成汉字字符数组
char[] letteCharacterArr = new char[chineseCharacterArr.length]; // 字母字符数组
for (int i = 0; i < chineseCharacterArr.length; i++) {
// 得到汉字拼音的首字母
letteCharacterArr[i] = getFirstLetterFromPinyin(chineseCharacterArr[i]);
}
if (0 != letteCharacterArr.length) {
pinyin = new String(letteCharacterArr);
pinyin = pinyin.toUpperCase();
}
return pinyin;
}
/* 得到一个汉字的拼音的首字母 */
private static char getFirstLetterFromPinyin(char cn) {
// 判断cn是否为一个合法的汉字,不是则直接返回cn
if (!isChineseCharacters(cn)) {
return cn;
}
String[] pyArr = PinyinHelper.toHanyuPinyinStringArray(cn);
char py = pyArr[0].charAt(0);
return py;
}
/**
* 判断字符是否为一个汉字
*
* @param cn
* char
* @return boolean
*/
public static boolean isChineseCharacters(char cn) {
boolean b = false;
if (Pattern.matches(regexStr, String.valueOf(cn))) {
b = true;
}
return b;
}
public static void main(String[] args) {
String s = "保存并加入A题库";
System.out.println(PinYinUtil.toPinyin(s).toLowerCase());
}
}
写实体类 dao方法 和 PageBean BaseDao
实体类 字段名 要和数据库的一样
package com.xx.entity;
import java.io.Serializable;
public class Student implements Serializable{
private static final long serialVersionUID = 1L;
private String id;
private String job;
private String company;
private String address;
private String salary;
private String url;
private String limit;
private String time;
private String desc;
private String jobHandle;
private String addressHandle;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getLimit() {
return limit;
}
public void setLimit(String limit) {
this.limit = limit;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
public String getJobHandle() {
return jobHandle;
}
public void setJobHandle(String jobHandle) {
this.jobHandle = jobHandle;
}
public String getAddressHandle() {
return addressHandle;
}
public void setAddressHandle(String addressHandle) {
this.addressHandle = addressHandle;
}
public Student() {
}
public Student(String id, String job, String company, String address, String salary, String url, String limit,
String time, String desc, String jobHandle, String addressHandle) {
this.id = id;
this.job = job;
this.company = company;
this.address = address;
this.salary = salary;
this.url = url;
this.limit = limit;
this.time = time;
this.desc = desc;
this.jobHandle = jobHandle;
this.addressHandle = addressHandle;
}
}
dao方法 增加 删除 修改 模糊查询
package com.xx.dao;
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.xx.Callback;
import com.xx.entity.Student;
import com.xx.util.DBAccess;
import com.xx.util.PageBean;
import com.xx.util.StringUtils;
public class StudentDao extends BaseDao<Student>{
/*Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
public void add(Student stu) {
}*/
/*public List<Student> getAll(String name){
List<Student> ls=new ArrayList<Student>();
String sql="select * from t_solr_job";
if(name!=null) {
sql="select * from t_solr_job where company like '%"+name+"%'";
}
try {
con=DBAccess.getConnection();
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
Student stu=new Student();
stu.setId(rs.getString("id"));
stu.setJob(rs.getString("job"));
stu.setCompany(rs.getString("company"));
stu.setAddress(rs.getString("address"));
stu.setSalary(rs.getString("salary"));
stu.setUrl(rs.getString("url"));
stu.setLimit(rs.getString("limit"));
stu.setTime(rs.getString("time"));
stu.setDesc(rs.getString("desc"));
stu.setJobHandle(rs.getString("jobHandle"));
stu.setAddressHandle(rs.getString("addressHandle"));
ls.add(stu);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
DBAccess.close(con, ps, rs);
}
return ls;
}*/
public List<Student> list(String sname,PageBean pageBean){
List<Student> list=new ArrayList<Student>();
//模糊查询的条件
String sql="select * from t_solr_job";
if(sname!=null) {
sql="select * from t_solr_job where company like '%"+sname+"%'";
}
return this.query(sql, pageBean, new Callback<Student>() {
@Override
public List<Student> foreach(ResultSet rs) throws SQLException {
// TODO Auto-generated method stub
Student stu=null;
while(rs.next()) {
stu=new Student();
stu.setId(rs.getString("id"));
stu.setJob(rs.getString("job"));
stu.setCompany(rs.getString("company"));
stu.setAddress(rs.getString("address"));
stu.setSalary(rs.getString("salary"));
stu.setUrl(rs.getString("url"));
stu.setLimit(rs.getString("limitt"));
stu.setTime(rs.getString("time"));
stu.setDesc(rs.getString("descc"));
stu.setJobHandle(rs.getString("jobHandle"));
stu.setAddressHandle(rs.getString("addressHandle"));
list.add(stu);
}
return list;
}
});
}
public int delete(String id){
int n=0;
Connection con=null;
PreparedStatement ps=null;
try {
con=DBAccess.getConnection();
String sql="delete from t_solr_job where id="+id;
ps=con.prepareStatement(sql);
n=ps.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
DBAccess.close(con, ps, null);
}
return n;
}
public List<Student> list(String id){
List<Student> ls=new ArrayList<Student>();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
con=DBAccess.getConnection();
String sql="select * from t_solr_job where id="+id;
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
if(rs.next()) {
Student stu=new Student();
stu.setId(rs.getString("id"));
stu.setJob(rs.getString("job"));
stu.setCompany(rs.getString("company"));
stu.setAddress(rs.getString("address"));
stu.setSalary(rs.getString("salary"));
stu.setUrl(rs.getString("url"));
stu.setLimit(rs.getString("limitt"));
stu.setTime(rs.getString("time"));
stu.setDesc(rs.getString("descc"));
stu.setJobHandle(rs.getString("jobHandle"));
stu.setAddressHandle(rs.getString("addressHandle"));
ls.add(stu);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
DBAccess.close(con, ps, rs);
}
return ls;
}
public int update(String id,Student stu){
int n=0;
Connection con=null;
PreparedStatement ps=null;
try {
con=DBAccess.getConnection();
String sql="update t_solr_job set job=?,company=?,address=?,salary=?,url=?,limitt=? where id="+id;
ps=con.prepareStatement(sql);
ps.setString(1, stu.getJob());
ps.setString(2, stu.getCompany());
ps.setString(3, stu.getAddress());
ps.setString(4, stu.getSalary());
ps.setString(5, stu.getUrl());
ps.setString(6, stu.getLimit());
n=ps.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
DBAccess.close(con, ps, null);
}
return n;
}
public int insert(Student stu){
int n=0;
Connection con=null;
PreparedStatement ps=null;
try {
con=DBAccess.getConnection();
String sql="insert into t_solr_job(id,job,company,address,salary,url,limitt,time,descc,jobHandle,addressHandle)values(?,?,?,?,?,?,?,?,?,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1, stu.getId());
ps.setString(2, stu.getJob());
ps.setString(3, stu.getCompany());
ps.setString(4, stu.getAddress());
ps.setString(5, stu.getSalary());
ps.setString(6, stu.getUrl());
ps.setString(7, stu.getLimit());
ps.setString(8, stu.getTime());
ps.setString(9, stu.getDesc());
ps.setString(10, stu.getJobHandle());
ps.setString(11, stu.getAddressHandle());
n=ps.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
DBAccess.close(con, ps, null);
}
return n;
}
}
接下来就是分页了
PageBean
分页三要素
page 页码 视图层传递过来
rows 页大小 视图层传递过来
total 总记录数 后台查出来
pagination 是否分页 视图层传递过来
package com.xx.util;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
public class PageBean {
private int page = 1;
private int rows = 10;
private int total = 0;
private boolean pagination = true;
//请求的地址
private String url;
//请求的参数
private Map<String, String[]> parameterMap;
public Map<String, String[]> getParameterMap() {
return parameterMap;
}
public void setParameterMap(Map<String, String[]> parameterMap) {
this.parameterMap = parameterMap;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public PageBean() {
super();
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public void setPage(String page) {
if(null!=page&&!"".equals(page.trim())) {
this.page = Integer.parseInt(page);
}
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
this.rows = rows;
}
public void setRows(String rows) {
if(null!=rows&&"".equals(rows.trim())) {
this.rows = Integer.parseInt(rows);
}
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public boolean isPagination() {
return pagination;
}
public void setPagination(boolean pagination) {
this.pagination = pagination;
}
public void setPagination(String pagination) {
if("false".equals(pagination)) {
this.pagination=false;
}
}
@Override
public String toString() {
return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
}
/**
* 获取起始记录的下标
* @return
*/
public int getStartIndex() {
int start=(this.page-1)*this.rows;
return start;
}
public void initPageBean(HttpServletRequest request,PageBean pageBean) {
pageBean.setPage(request.getParameter("page"));
pageBean.setRows(request.getParameter("rows"));
pageBean.setPagination(request.getParameter("pagination"));
pageBean.setUrl(request.getContextPath()+request.getServletPath());
pageBean.setParameterMap(request.getParameterMap());
}
/**
* 获取最大页码数
* @return
*/
public int getMaxPage() {
int maxPage=this.total/this.rows;
if(0!=this.total%this.rows) {
maxPage=maxPage+1;
}
return maxPage;
}
/**
* 获取下一页
* @return
*/
public int getNextPage() {
int nextPage=this.page+1;
if(nextPage>this.getMaxPage()) {
nextPage=this.getMaxPage();
}
return nextPage;
}
/**
* 获取上一页
* @return
*/
public int getFrontPage() {
int frontPage=this.page-1;
if(frontPage<1) {
frontPage=1;
}
return frontPage;
}
}
有了PageBean以后可以写dao方法了
重点 ： 泛型类调用分页类 sql语句的拼接模糊查询分页 计算符合条件的总记录数
package com.xx.dao;
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.xx.Callback;
import com.xx.entity.Student;
import com.xx.util.DBAccess;
import com.xx.util.PageBean;
public class BaseDao<E> {
public List<E> query(String sql,PageBean pageBean,Callback<E> callback){
List<E> ls=new ArrayList<E>();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
//第一次查询 查总记录数
if(null!=pageBean&&pageBean.isPagination()) {
try {
con=DBAccess.getConnection();
String countsql=this.getCountSql(sql);
ps=con.prepareStatement(countsql);
rs=ps.executeQuery();
if(rs.next()) {
pageBean.setTotal(rs.getInt(1));
}
} catch (SQLException e) {
// TODO: handle exception
throw new RuntimeException();
}finally {
DBAccess.close(null, ps, rs);
}
}
//第二次查询 查询满足条件的记录数
if(null!=pageBean&&pageBean.isPagination()) {
sql=this.getPageSql(sql, pageBean);
}
try {
con=DBAccess.getConnection();
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
return callback.foreach(rs);
} catch (SQLException e) {
// TODO: handle exception
throw new RuntimeException();
}finally {
DBAccess.close(null, ps, rs);
}
}
/**
* 把普通sql转成查总记录数sql
* @param sql
* @return
*/
public String getCountSql(String sql) {
String countSql="select count(*) from ("+sql+") t";
return countSql;
}
/**
* 查满足分页的sql
* @param sql
* @return
*/
public String getPageSql(String sql,PageBean pageBean) {
String pageSql=sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows()+"";
return pageSql;
}
}
写一个jsp测试下
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<c:if test="${empty list }">
<jsp:forward page="StudentServlet"></jsp:forward>
</c:if>
<form action="StudentServlet" method="post">
公司名称：<input type="text" name="name"/>
<input type="submit" value-"查询"/>
</form>
<table border="1px" width="100%">
<tr>
<td>编号</td>
<td>类型</td>
<td>公司名称</td>
<td>公司地址</td>
<td>工资</td>
<td>网址</td>
<td>学历要求</td>
<td>时间</td>
<td>操作</td>
</tr>
<c:forEach items="${list }" var="i">
<tr>
<td>${i.id }</td>
<td>${i.job }</td>
<td>${i.company }</td>
<td>${i.address }</td>
<td>${i.salary }</td>
<td>${i.url }</td>
<td>${i.limit }</td>
<td>${i.time }</td>
<td>
<a href="StudentServletDelete?id=${i.id }" onclick="return confirm('您确定要删除吗？')">删除</a>
<a href="StudentServletUpcx?id=${i.id }">修改</a>
<a href="StudentServletDetails?id=${i.id }">查看详情</a>
</td>
</tr>
</c:forEach>
</table>
当前第${pageBean.page }页 共${pageBean.maxPage }页 共${pageBean.total }
<a href='javascript:doForward(1)'>首页</a>
<a href='javascript:doForward("${pageBean.frontPage }")'>上一页</a>
<a href='javascript:doForward("${pageBean.nextPage }")'>下一页<a>
<a href='javascript:doForward("${pageBean.maxPage }")'>尾页<a>
<form id="form" action="${pageBean.url }" method="post">
<input type="text" name="page" id="page"/>
</form>
<script type="text/javascript">
function doForward(page){
console.log(page);
document.getElementById("page").value=page;
document.getElementById("form").submit();
}
</script>
<br/>
<button><a href="add.jsp">增加</a></button>
</body>
</html>
效果图:
592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
