1. 框架
半成品:我们需要调用框架要实现增删查改,极大得到减少开发的代码量。
反编译工具jdu
注:增删查改用重定向 查询用转发
现在开始代码操作
先把之前写的mvc语言变成jar导出
选中要导出的项目,右键选择Export…
然后
再然后把我们需要的包导入到项目中,包括我们自己写的mvc.jar
BookAction
public class BookAction extends DispatcherServlet implements ModelDriven<Book>{
private Book book = new Book();
private BookDao bookdao = new BookDao();
@Override
public Book getModel() {
// TODO Auto-generated method stub
return book;
}
/**
* 查询单个书本信息
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String querySingleBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
Book b = bookdao.querySingleBook(book);
req.setAttribute("book", b);
//定义类型参数,告知去哪里,修改页面,详情页面
String type = req.getParameter("type");
if("edit".equals(type)) {
return "edit";
}else {
return "detail";
}
}
/**
* 书本新增
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String addBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
bookdao.addBook(book);
//bookAction.action?methodName=queryBookPager
req.getSession().setAttribute("message", "新增书本信息成功");
return "success";
}
/**
* 书本删除
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String delBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
bookdao.delBook(book);
return "success";
}
/**
* 书本修改
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String editBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
bookdao.editBook(book);
req.getSession().setAttribute("message", "新增书本信息成功");
return "success";
}
/**
* 书本分页查询
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String queryBookPager(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
PageBean pageBean = new PageBean();
pageBean.setRequest(req);
List<Book> books = bookdao.queryBookPager(book, pageBean);
req.setAttribute("books", books);
req.setAttribute("pageBean", pageBean);
return "list";
}
}
BaseDao
public class BaseDao<T> {
public static interface CallBack<E>{
//遍历ResultSet结果集
public List<E> forEach(ResultSet rs) throws SQLException;
}
/**
* 分页查询
* @param sql 普通的sql
* @param pageBean
* @return
*/
public List<T> executeQuery(String sql,PageBean pageBean,CallBack<T> callBack){
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
conn=DBHelper.getConnection();
//判断是否分页
if(null!=pageBean&&pageBean.isPagination()) {
//第一次查询总记录数
String countSql=this.getCountSql(sql);
stmt=conn.prepareStatement(countSql);
rs=stmt.executeQuery();
if(rs.next()) {
Object obj = rs.getObject(1);
pageBean.setTotal(Integer.parseInt(obj.toString()));
}
//DBHelper.close(conn, stmt, rs);
//第二次满足条件的分页数据集
sql=this.getPagerSql(sql, pageBean);
}
stmt=conn.prepareStatement(sql);
rs=stmt.executeQuery();
//处理结果集
return callBack.forEach(rs);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.close(conn, stmt, rs);
}
return null;
}
/**
* 将普通的Sql语句转换成查总记录数的Sql语句
* 例如:sql=="select * from t_book where bookname='123'"
* @param sql
* @return
*/
private String getCountSql(String sql) {
return "select count(1) from ("+sql+") t1";
}
/**
* 将普通Sql语句转换成查询分页的Sql语句
* 例如:sql=="select * from t_book where bookname='123'"
* @param sql
* @param pageBean
* @return
*/
private String getPagerSql(String sql,PageBean pageBean) {
return sql+" Limit "+pageBean.getStartIndex()+","+
pageBean.getRows();
}
}
BookDao
/**
* 书本新增
* @param book
*/
public void addBook(Book book) {
String sql = "insert into t_mvc_book(bname,price) values(?,?)";
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBHelper.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, book.getBname());
ps.setFloat(2, book.getPrice());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(conn,ps,null);
}
}
/**
* 书本修改
* @param book
*/
public void editBook(Book book) {
String sql = "update t_mvc_book set bname = ?,price = ? where bid = ?";
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBHelper.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, book.getBname());
ps.setFloat(2, book.getPrice());
ps.setInt(3, book.getBid());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(conn,ps,null);
}
}
/**
* 书本删除
* @param book
*/
public void delBook(Book book) {
String sql = "delete t_mvc_book where bid = ?";
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBHelper.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, book.getBid());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(conn,ps,null);
}
}
/**
* 根据书本编号查询书本信息
* @param book
* @return
*/
public Book querySingleBook(Book book) {
String sql = "select bid,bname,price from t_mvc_book where bid ="+book.getBid();
List<Book> lst=super.executeQuery(sql,null, new CallBack<Book>() {
@Override
public List<Book> forEach(ResultSet rs) throws SQLException {
return CommonUtils.toList(rs,Book.class);
}
});
if(null!=lst||0!=lst.size()) {
return lst.get(0);
}
return null;
}
/**
* 分页查询书本
* @param book
* @return
*/
public List<Book> queryBookPager(Book book,PageBean pageBean){
String sql = "select bid,bname,price from t_mvc_book where 1=1";
if(StringUtils.isNotBlank(book.getBname())) {
sql += " and bname like '%"+book.getBname()+"%'";
sql +="order by bid desc";
}
return super.executeQuery(sql,pageBean, new CallBack<Book>() {
@Override
public List<Book> forEach(ResultSet rs) throws SQLException {
return CommonUtils.toList(rs,Book.class);
}
});
}
}
PaginationTag
public class PaginationTag extends BodyTagSupport {
private PageBean pageBean;
public PageBean getPageBean() {
return pageBean;
}
public void setPageBean(PageBean pageBean) {
this.pageBean = pageBean;
}
@Override
public int doEndTag() throws JspException {
return EVAL_PAGE;
}
@Override
public int doStartTag() throws JspException {
JspWriter out = pageContext.getOut();
try {
out.write(toHtml());
} catch (Exception e) {
e.printStackTrace();
}
return SKIP_BODY;
}
private String toHtml() {
//判断是否分页
if(null==pageBean||!pageBean.isPagination())
return "";
else {
StringBuilder sb=new StringBuilder();
//拼接form表单
sb.append("<form id=\"pageBeanForm\" method=\"post\" action=\""+pageBean.getUrl()+"\">");
//定义page的隐藏域
sb.append("<input type=\"hidden\" name=\"page\"/>");
//循环遍历请求参数集合
Map<String, String[]> map = pageBean.getMap();
//获取请求参数的键值对
Set<Entry<String, String[]>> entrySet = map.entrySet();
//遍历键值对
for (Entry<String, String[]> entry : entrySet) {
String name=entry.getKey();
if("page".equals(name))
continue;
String[] values = entry.getValue();
for (String value : values) {
//checkbox
sb.append("<input type=\"hidden\" name=\""+name+"\" value=\""+value+"\"/>");
}
}
sb.append("</form>");
//拼接分页的基本属性信息
sb.append("<div style=\"width:100%;text-align:right;\">");
sb.append("第"+pageBean.getPage()+"页/共"+pageBean.getMaxPager()
+"页,总共"+pageBean.getTotal()+"条记录");
//首页、上一页
if(pageBean.getPage()!=1) {
sb.append("<a href=\"javascript:gotoPage(1);\">首页</a> ");
sb.append("<a href=\"javascript:gotoPage("+pageBean.getPreviousPager()+");\">上一页</a> ");
}else {
sb.append("首页 上一页 ");
}
//下一页、末页
if(pageBean.getPage()!=pageBean.getMaxPager()) {
sb.append("<a href=\"javascript:gotoPage("+pageBean.getNextPager()+");\">下一页</a> ");
sb.append("<a href=\"javascript:gotoPage("+pageBean.getMaxPager()+");\">末页</a> ");
}else {
sb.append("下一页 末页 ");
}
//GO
sb.append("<input type=\"text\" id=\"skipPage\" value=\"\" style=\"width:30px\"/>\r\n" +
" <a href=\"javascript:skipPage();\">GO</a>");
//封装javascript方法
//1)gotoPage跳转页面的方法
sb.append("<script type=\"text/javascript\">\r\n" +
"function gotoPage(page){\r\n" +
" //设置当前页码\r\n" +
" document.getElementById('pageBeanForm').page.value=page;\r\n" +
" //触发表单提交\r\n" +
" document.getElementById('pageBeanForm').submit();\r\n" +
"}");
//点击GO按钮跳转页面的方法
sb.append("function skipPage(){\r\n" +
" var page=document.getElementById('skipPage').value;\r\n" +
" if(isNaN(page)||page<1||page>"+pageBean.getMaxPager()+"){\r\n" +
" alert('请输入1~"+pageBean.getMaxPager()+"的数字!');\r\n" +
" return false;\r\n" +
" }\r\n" +
" gotoPage(page);\r\n" +
"}\r\n" +
"</script>");
sb.append("</div>");
return sb.toString();
}
}
}
CommonUtils
public class CommonUtils {
/**
* 根据ResultSet数据集,利用反射机制动态赋值并返回List<T>
* @param rs ResultSet数据集
* @param clazz 实体类对象
* @return 返回List实体集合
* @throws Exception
*/
public static <T> List<T> toList(ResultSet rs,Class<T> clazz){
//定义实体集合
List<T> lst=new ArrayList<T>();
try {
//获取ResultSet的metadata列信息
ResultSetMetaData metaData = rs.getMetaData();
//获取对象属性集合
Field[] fields=clazz.getDeclaredFields();
//循环ResultSet
while(rs.next()) {
//反射机制实例化
T obj = clazz.newInstance();
for (int i = 0; i < metaData.getColumnCount(); i++) {
//获取列名
String columnName=metaData.getColumnLabel(i+1).toUpperCase();
for (Field field : fields) {
//判断属性名与列名是否相同
if(field.getName().toUpperCase().equals(columnName)) {
//获取属性对应的set方法名,方法名首字母大写
String methodName="set"+field.getName().substring(0, 1).toUpperCase()+field.getName().substring(1);
//获取属性对应的set方法
Method method = obj.getClass().getDeclaredMethod(methodName, field.getType());
//设置访问权限
method.setAccessible(true);
//执行set方法,将数据存储到对象中的相应属性中
method.invoke(obj, rs.getObject(columnName));
break;
}
}
}
lst.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return lst;
}
/**
* 多表查询时返回结果集,利用反射机制赋值
* @param rs
* @return 返回List<Map<String,Object>>
* @throws Exception
*/
public static List<Map<String,Object>> toList(ResultSet rs) throws Exception{
//定义实体集合
List<Map<String,Object>> lst=new ArrayList<Map<String,Object>>();
//获取ResultSet的metadata列信息
ResultSetMetaData metaData = rs.getMetaData();
Map<String,Object> set=null;
while(rs.next()) {
set=new HashMap<String,Object>();
for (int i = 0; i < metaData.getColumnCount(); i++) {
String columnName=metaData.getColumnLabel(i+1);
set.put(columnName, rs.getObject(columnName));
}
lst.add(set);
}
return lst;
}
}
DBHepler
/**
* 提供了一组获得或关闭数据库对象的方法
*
*/
public class DBHelper {
private static String driver;
private static String url;
private static String user;
private static String password;
static {// 静态块执行一次,加载 驱动一次
try {
InputStream is = DBHelper.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, 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 = DBHelper.getConnection();
DBHelper.close(conn);
System.out.println("isOracle:" + isOracle());
System.out.println("isSQLServer:" + isSQLServer());
System.out.println("isMysql:" + isMysql());
System.out.println("数据库连接(关闭)成功");
}
}
小资料:config.properties
#oracle9i
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:ora9
#user=test
#pwd=test
#sql2005
#driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#url=jdbc:sqlserver://localhost:1423;DatabaseName=test
#user=sa
#pwd=sa
#sql2000
#driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
#url=jdbc:microsoft:sqlserver://localhost:1433;databaseName=unit6DB
#user=sa
#pwd=888888
#mysql5
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/tt?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
user=root
pwd=123
mvc.xml
<config>
<action path="/bookAction" type="com.tt.action.BookAction">
<forward name="success" path="/bookAction.action?methodName=queryBookPager" redirect="true" />
<forward name="list" path="/bookList.jsp" redirect="false" />
<forward name="edit" path="/bookEdit.jsp"/>
<forward name="detail" path="/bookDetail.jsp"/>
</action>
</config>
bookList.jsp
//导入
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="z" uri="/zking" %>
<%@ include file="showMessage.jsp" %>
<body>
<form action="bookAction.action" method="post">
<label>书本名称:</label><input type="text" name="bname"/>
<input type="submit" value="查 询" name="methodName:queryBookPager">
<a href="addBook.jsp">书本新增</a>
</form>
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<th>书本编号</th>
<th>书本名称</th>
<th>书本价格</th>
<th>操作</th>
<tbody>
<c:forEach items="${books }" var="book">
<tr>
<td>${book.bid }</td>
<td>${book.bname }</td>
<td>${book.price }</td>
<td>
<a href="bookAction.action?methodName=delBook&bid=${book.bid }">删除</a>
<a href="bookAction.action?methodName=querySingleBook&type=edit&bid=${book.bid }">修改</a>
<a href="bookAction.action?methodName=querySingleBook&type&type=detail&bid=${book.bid }">详情</a>
</td>
</tr>
</c:forEach>
</tbody>
</table>
${pageBean }
<z:page pageBean="${pageBean }"/>
</body>
bookEdit.jsp
<body>
<h1>书本修改页面</h1>
<form action="bookAction.action" method="post">
<label>书本编号:</label><input type="text" name="bid" value="${book.bid }" readonly="readonly"/><br/>
<label>书本名称:</label><input type="text" name="bname"value="${book.bname }" /><br/>
<label>书本价格:</label><input type="text" name="price"value="${book.price }" /><br/>
<input type="submit" value="提交" name="methodName:editBook">
</form>
</body>
showMessage.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<script>
var msg = '${mesage}';
if(msg){
window.onload = function(){
alert(msg);
};
}
</script>
<!-- 把提示信息移除 -->
<c:remove var="message" scope="session"/>