大数据正式24
数据库中的锁机制
- 共享锁
- 共享锁和共享锁可以共存
- 共享锁和排他锁不可以共存
- 非Serializable级别的任何查询都不加锁，在Serializable级别的查询加锁
- 排它锁
- 排它锁和任何锁都不可以共存
- 任意隔离级别的增删改都加
- 数据库中的锁的设计
- 两个查询：没必要加锁
- 两个增删改：必须互斥
- 一个查询，一个修改：Serializable级别的需要加锁，其他级别的不需要加锁
具体分析【锁对象--操作--是否可以共同操作】
| 操作对象【对象一：对象二】 | 对象一操作 | 对象二操作 | 分析 | 结果 |
|---|---|---|---|---|
| 【非Serializable：非Serializable】 | 查 | 查 | 【不加锁：不加锁】 | 可以共同操作 |
| 【非Serializable：Serializable】 | 查 | 查 | 【不加锁：共享锁】 | 可以共同操作 |
| 【Serializable：Serializable】 | 查 | 查 | 【共享锁：共享锁】 | 可以共同操作 |
| 【非Serializable：非Serializable】 | 查 | 增删改 | 【不加锁：排它锁】 | 可以共同操作 |
| 【Serializable：非Serializable】 | 查 | 增删改 | 【共享锁：排它锁】 | 不可以共同操作 |
| 【非Serializable：Serializable】 | 查 | 增删改 | 【不加锁：排它锁】 | 可以共同操作 |
| 【Serializable：Serializable】 | 查 | 增删改 | 【共享锁：排它锁】 | 不可以共同操作 |
| 【非Serializable：非Serializable】 | 增删改 | 增删改 | 【排它锁：排它锁】 | 不可以共同操作 |
| 【Serializable：非Serializable】 | 增删改 | 增删改 | 【排它锁：排它锁】 | 不可以共同操作 |
| 【非Serializable：Serializable】 | 增删改 | 增删改 | 【排它锁：排它锁】 | 不可以共同操作 |
| 【Serializable：Serializable】 | 增删改 | 增删改 | 【排它锁：排它锁】 | 不可以共同操作 |
其他锁
- 死锁：如果有两个以上的资源，互相等待
- 例：当看客户端都是Serializable隔离级别时，两边先查询【共享锁】，再修改【想升级为排它锁】，都无法执行---资源互等，产生死锁
- 解决
- 避免死锁
- 解决死锁【数据库没有避免死锁，只能是遇到错的时候，关闭错误的请求，进行正确的操作】
EasyMall中的事务控制
- 遇到的问题：Service层控制事务需要用到同一个Connection，而Connection是Dao层的特有对象，那该怎么办呢？
- 图解问题：
-
- 解决：解决耦合--》管理事务--》专门的事务管理【将耦合统一管理，方便未来开发和维护】
- Connection管理
- 如果所有的用到的都使用同一个Static的Connection，则会出现多线程不安全的问题
- 方案一：类内部加锁--效率太低
- 方案二：ThreadLocal，线程流的传递同时将数据也进行传递
- 如果所有的用到的都使用同一个Static的Connection，则会出现多线程不安全的问题
- Connection管理
-
ThreadLocal
- 本地线程变量
- 本质：利用程序的执行机制，在上游存放数据，下游利用该数据
- 图解：
-
- 方法
-
-
例子
package com.easymall.utils; public class T { static ThreadLocal<String> tl = new ThreadLocal<String>(); public static void main(String[] args) { tl.set("abc"); a(); } public static void a() { b(); } public static void b() { c(); } public static void c() { System.out.println(tl.get()); } }- 执行结果
abc
- 执行结果
-
EasyMall中添加产品模块
- 流程
-
-
ProdService
-
ProdServiceImpl
package com.easymall.service; import com.easymall.dao.ProdDao; import com.easymall.domain.Prod; import com.easymall.domain.ProdCategory; import com.easymall.factory.BasicFactory; import com.easymall.utils.TransactionManager; public class ProdServiceImpl implements ProdService { private ProdDao prodDao = BasicFactory.getFactory().getObj(ProdDao.class); public void addProd(Prod prod) { try { // 开启事务 TransactionManager.StartTransaction(); String category_id = "-666"; // 检查商品种类是否存在 ProdCategory category = prodDao.findProdCategoryByCategoryName(prod .getProdCategory().getName()); if (category == null) { // 商品种类不存在--则先添加商品种类，然后添加商品 category = prodDao .addCategory(prod.getProdCategory().getName());// 返回值的作用是为了后期容易得到商品种类的ID值 } // 获取商品种类的ID category_id = category.getId(); // 商品种类存在，添加商品 prodDao.addProd(prod, category_id); // 提交事务 TransactionManager.CommitTransaction(); // 关闭连接 TransactionManager.realse(); } catch (Exception e) { e.printStackTrace(); // 事务回滚 TransactionManager.RollbackTransaction(); System.out.println("back"); throw new RuntimeException(e); } } }
-
-
ProdDao
-
ProdDaoImpl
package com.easymall.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import com.easymall.domain.Prod; import com.easymall.domain.ProdCategory; import com.easymall.utils.MySqlUtils; import com.easymall.utils.TransactionManager; public class ProdDaoImpl implements ProdDao { Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; public ProdCategory findProdCategoryByCategoryName(String name) { try { ProdCategory pc = null; conn = TransactionManager.getConn(); stat = conn .prepareStatement("select * from prod_category where name=?"); stat.setString(1, name); rs = stat.executeQuery(); while (rs.next()) {// 一个对象这里才对----------嘻嘻 pc = new ProdCategory(); pc.setId(rs.getString("id")); pc.setName(rs.getString("name")); } return pc; } catch (Exception e) { throw new RuntimeException(e); } finally { MySqlUtils.close(null, stat, rs); } } public ProdCategory addCategory(String category_name) { try { conn = TransactionManager.getConn(); conn.setAutoCommit(false);// 设置不自动提交 stat = conn .prepareStatement("insert into prod_category(name) values(?)"); stat.setString(1, category_name); stat.executeUpdate(); // 通过得到刚存入的数据得到相应的id来返回ProdCategory对象 ProdCategory pc = null; stat = conn .prepareStatement("select * from prod_category where name=? "); stat.setString(1, category_name); rs = stat.executeQuery(); while (rs.next()) { pc = new ProdCategory(); pc.setId(rs.getString("id")); pc.setName(rs.getString("name")); } return pc; } catch (Exception e) { throw new RuntimeException(e); } finally { MySqlUtils.close(null, stat, rs); } } public void addProd(Prod prod, String category_id) { try { conn = TransactionManager.getConn(); stat = conn .prepareStatement("insert into prod(name,price,pnum,img_url,description,prodCategory_id) values(?,?,?,?,?,?)"); stat.setString(1, prod.getName()); stat.setDouble(2, prod.getPrice()); stat.setInt(3, prod.getPnum()); stat.setString(4, prod.getImgurl()); stat.setString(5, prod.getDescription()); stat.setInt(6, Integer.parseInt(category_id)); stat.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } finally { MySqlUtils.close(null, stat, rs); } } }
-
-
utils
-
TransactionManager
package com.easymall.utils; import java.sql.Connection; import java.sql.SQLException; /** * 注意：使用了ThreadLocal类-------通过线程的【传递性】来传递【互不影响】的数据 * * @author Administrator * */ public class TransactionManager { private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); // 构造函数私有化 private TransactionManager() { super(); } // 开启事务 public static void StartTransaction() { // 创建连接 Connection conn = MySqlUtils.getConn(); tl.set(conn); // 开启事务 try { if (null != tl.get()) { tl.get().setAutoCommit(false); } } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } // 提交事务 public static void CommitTransaction() { if (null != tl.get()) { try { tl.get().commit(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } // 回滚事务 public static void RollbackTransaction() { if (null != tl.get()) { try { tl.get().rollback(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } // 获取Conn public static Connection getConn() { return tl.get(); } // 释放连接 public static void realse() { Connection conn = tl.get(); MySqlUtils.close(conn, null, null); tl.remove(); } }
-
- 流程
Easymall商品模块展示
- 流程
- index.jsp中点击“所有商品”
- ProdListServlet调用Service查询所有商品信息，存入Request域，带到页面展示
- ProdDao查询所有商品
- 相关技术
- 数据库查询
- 笛卡尔查询
- select * from t1,t2;
- 内连接查询
- 在笛卡尔积的基础上选
- select * from t1,t2 where ti.id = td.t1_id;
- select * from t1 inner join t2 on t1.id = t2.t1_id;
- 外链接查询
- 在内连接的基础上增加左表剩下的数据
- 左外连接查询
- select * from t1 left join t2 on t1.id = t2.t1_id;
- 右外连接查询
- select * from t1 right join t2 on t1.id = t2.t1_id;
- 全外连接
- select * from t1 full join t2 on t1.id = t2.t1_id;//mysql中没有实现
- select * from t1 left join t2 on t1.id = t2.t1_id union select * from t1 right join t2 on t1.id = t2.t1_id;
- 笛卡尔查询
- 数据库查询
-
商品列表（前台）
- 效果展示
-
-
代码
-
展示页面
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" buffer="0kb"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; %> <!DOCTYPE HTML> <html> <head> <meta http-equiv="Content-type" content="text/html; charset=UTF-8" /> <link href="css/prodList.css" rel="stylesheet" type="text/css"> </head> <body> <%@include file="head.jsp"%> <div id="content"> <div id="search_div"> <form method="post" action="#"> <span class="input_span">商品名：<input type="text" name="name" /> </span> <span class="input_span">商品种类：<input type="text" name="category" /> </span> <span class="input_span">商品价格区间：<input type="text" name="minprice" /> - <input type="text" name="maxprice" /> </span> <input type="submit" value="查询"> </form> </div> <div id="prod_content"> <c:forEach items="${requestScope.prod_lists}" var="prod"> <div id="prod_div"> <img src="../ProdImgServlet?imgurl=${prod.imgurl}"></img> <div id="prod_name_div">${prod.name}</div> <div id="prod_price_div">${prod.price}</div> <div> <div id="gotocart_div"> <a href="#">加入购物车</a> </div> <div id="say_div">133人评价</div> </div> </div> </c:forEach> </div> <div style="clear: both"></div> </div> <%@include file="foot.jsp"%> </body> </html> -
web层
-
ProdImgServlet（获取二进制图片）
package com.easymall.web; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class ProdImgServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 获取产品图片地址 String imgurl = request.getParameter("imgurl"); // 将图片的二进制发往页面 // 读取并写入输出 File file = new File(this.getServletContext().getRealPath(imgurl)); InputStream in = new FileInputStream(file); OutputStream os = response.getOutputStream(); int len = -1; byte[] data = new byte[1024]; while ((len = in.read(data)) != -1) { os.write(data, 0, len); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } } -
ProdListServlet
package com.easymall.web; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.easymall.domain.Prod; import com.easymall.factory.BasicFactory; import com.easymall.service.ProdService; @SuppressWarnings("serial") public class ProdListServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 调用Service查询所有商品 ProdService service = BasicFactory.getFactory().getObj( ProdService.class); List<Prod> list = service.findProdList(); // 将数据放入Request域 request.setAttribute("prod_lists", list); // 转发到prodList.jsp页面 request.getRequestDispatcher("prodList.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } }
-
-
service层
-
ProdService
package com.easymall.service; import java.util.List; import com.easymall.domain.Prod; public interface ProdService { /** * 添加一个产品 * * @param prod */ public void addProd(Prod prod); /** * 查询所有的商品 * * @return 查询所有的商品 */ public List<Prod> findProdList(); /** * 通过id更新商品的pnum * * @param id * @param pnum */ public void upDatePunm(int id, int pnum); } -
ProdServiceImpl
package com.easymall.service; import java.util.List; import com.easymall.dao.ProdDao; import com.easymall.domain.Prod; import com.easymall.domain.ProdCategory; import com.easymall.factory.BasicFactory; import com.easymall.utils.TransactionManager; public class ProdServiceImpl implements ProdService { private ProdDao prodDao = BasicFactory.getFactory().getObj(ProdDao.class); public void addProd(Prod prod) { try { // 开启事务 TransactionManager.StartTransaction(); String category_id = "-666"; // 检查商品种类是否存在 ProdCategory category = prodDao.findProdCategoryByCategoryName(prod .getProdCategory().getName()); if (category == null) { // 商品种类不存在--则先添加商品种类，然后添加商品 category = prodDao .addCategory(prod.getProdCategory().getName());// 返回值的作用是为了后期容易得到商品种类的ID值 } // 获取商品种类的ID category_id = category.getId(); // 商品种类存在，添加商品 prodDao.addProd(prod, category_id); // 提交事务 TransactionManager.CommitTransaction(); // 关闭连接 TransactionManager.realse(); } catch (Exception e) { e.printStackTrace(); // 事务回滚 TransactionManager.RollbackTransaction(); System.out.println("back"); throw new RuntimeException(e); } } public List<Prod> findProdList() { return prodDao.findProdList(); } public void upDatePunm(int id, int pnum) { prodDao.upDatePnum(id, pnum); } }
-
-
dao层
-
ProdDao
package com.easymall.dao; import java.util.List; import com.easymall.domain.Prod; import com.easymall.domain.ProdCategory; public interface ProdDao { /** * 通过商品种类名称查询数据库是否有此种类 * * @param string * @return 找到了？ 商品种类的对象：null； */ ProdCategory findProdCategoryByCategoryName(String string); /** * 添加一个商品种类 * * @param category * @return 添加成功对应的商品种类 */ ProdCategory addCategory(String category_name); /** * 添加商品 * * @param prod * 商品 * @param category_id * 商品种类的ID * @return void */ void addProd(Prod prod, String category_id); /** * 查询所有商品 * * @return 商品列表 */ List<Prod> findProdList(); /** * 通过id更新产品的pnum数据 * * @param id * @param pnum */ void upDatePnum(int id, int pnum); } -
ProdDaoImpl
package com.easymall.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Collections; import java.util.List; import com.easymall.domain.Prod; import com.easymall.domain.ProdCategory; import com.easymall.utils.MySqlUtils; import com.easymall.utils.TransactionManager; public class ProdDaoImpl implements ProdDao { Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; public ProdCategory findProdCategoryByCategoryName(String name) { try { ProdCategory pc = null; conn = TransactionManager.getConn(); stat = conn .prepareStatement("select * from prod_category where name=?"); stat.setString(1, name); rs = stat.executeQuery(); while (rs.next()) {// 一个对象这里才对----------嘻嘻 pc = new ProdCategory(); pc.setId(rs.getString("id")); pc.setName(rs.getString("name")); } return pc; } catch (Exception e) { throw new RuntimeException(e); } finally { MySqlUtils.close(null, stat, rs); } } public ProdCategory addCategory(String category_name) { try { conn = TransactionManager.getConn(); conn.setAutoCommit(false);// 设置不自动提交 stat = conn .prepareStatement("insert into prod_category(name) values(?)"); stat.setString(1, category_name); stat.executeUpdate(); // 通过得到刚存入的数据得到相应的id来返回ProdCategory对象 ProdCategory pc = null; stat = conn .prepareStatement("select * from prod_category where name=? "); stat.setString(1, category_name); rs = stat.executeQuery(); while (rs.next()) { pc = new ProdCategory(); pc.setId(rs.getString("id")); pc.setName(rs.getString("name")); } return pc; } catch (Exception e) { throw new RuntimeException(e); } finally { MySqlUtils.close(null, stat, rs); } } public void addProd(Prod prod, String category_id) { try { conn = TransactionManager.getConn(); stat = conn .prepareStatement("insert into prod(name,price,pnum,img_url,description,prodCategory_id) values(?,?,?,?,?,?)"); stat.setString(1, prod.getName()); stat.setDouble(2, prod.getPrice()); stat.setInt(3, prod.getPnum()); stat.setString(4, prod.getImgurl()); stat.setString(5, prod.getDescription()); stat.setInt(6, Integer.parseInt(category_id)); stat.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } finally { MySqlUtils.close(null, stat, rs); } } public List<Prod> findProdList() { List<Prod> prod_list = new ArrayList<Prod>(); try { // 查询数据库，并把每一条记录包装成Prod对象存入prod_list列表中 conn = MySqlUtils.getConn(); stat = conn .prepareStatement("select * from prod inner join prod_category where prod.prodCategory_id=prod_category.id"); rs = stat.executeQuery(); while (rs.next()) { Prod prod = new Prod(); prod.setId(rs.getInt(1)); prod.setName(rs.getString(2)); prod.setPnum(rs.getInt(4)); prod.setDescription(rs.getString(7)); prod.setImgurl(rs.getString(5)); prod.setPrice(rs.getDouble(3)); prod.setProdCategory_id(rs.getInt(6)); prod.setProdCategory(new ProdCategory(rs.getString(8), rs .getString(9))); prod_list.add(prod); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { MySqlUtils.close(conn, stat, rs); } return prod_list; } public void upDatePnum(int id, int pnum) { try { // 通过id更新产品的pnum conn = MySqlUtils.getConn(); stat = conn.prepareStatement("update prod set pnum=? where id=? "); stat.setInt(1, pnum); stat.setInt(2, id); stat.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } }
-
-
- 效果展示
-
商品修改（后台）
- 效果展示
- ![] (https://i.imgur.com/1x9wHPB.png)
-
-
代码
-
展示页面
-
web层
-
ManagerProdListServlet
package com.easymall.web; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.easymall.domain.Prod; import com.easymall.factory.BasicFactory; import com.easymall.service.ProdService; @SuppressWarnings("serial") public class ManagerProdListServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 调用Service查询所有商品 ProdService service = BasicFactory.getFactory().getObj( ProdService.class); List<Prod> list = service.findProdList(); // 将数据放入Request域 request.setAttribute("prod_lists", list); // 转发到prodList.jsp页面 request.getRequestDispatcher( request.getContextPath() + "/backend/manageProdList.jsp") .forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doGet(request, response); } } -
ManageUpdatePnumServlet(更新pnum)
package com.easymall.web; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.easymall.factory.BasicFactory; import com.easymall.service.ProdService; public class ManageUpdatePnumServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 获取参数 int id = Integer.parseInt(request.getParameter("id")); int pnum = Integer.parseInt(request.getParameter("pnum")); System.out.println(id + "~~" + pnum); // 调用Service来更新数据 ProdService service = BasicFactory.getFactory().getObj( ProdService.class); service.upDatePunm(id, pnum); // 直接输出结果----ajax response.getWriter().write(pnum + ""); } } -
ProdImgServlet（获取二进制图片）
package com.easymall.web; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class ProdImgServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 获取产品图片地址 String imgurl = request.getParameter("imgurl"); // 将图片的二进制发往页面 // 读取并写入输出 File file = new File(this.getServletContext().getRealPath(imgurl)); InputStream in = new FileInputStream(file); OutputStream os = response.getOutputStream(); int len = -1; byte[] data = new byte[1024]; while ((len = in.read(data)) != -1) { os.write(data, 0, len); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } }
-
-
service层（见上）
- dao层（见上）
-
- 效果展示
EasyMall遗留的问题
- 注册的JavaScript处理-----逻辑不完善
- 异常的具体处理---------很不完善（~ 。~）
- 商品添加的JavaScript判断和后台判断处理-----这个记得参考【注册】
- 大致大体有这些，具体的细节遇到记得完善。。。
1045
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
