1.做一个图书类Book id,name,price ,get,set访问器,构造方法2个,1个无参,1个有参
做一个测试类,在main中创建3个图书对象,放到list集合中。做一个菜单,可以添加,删除,修改,查询
2.上题的类,在一个JSP页面中,创建一个集合,里面放3个图书,集合循环遍历显示在页面上。
3.在MySQL中创建Book表,里面id,name,price,
用命令实现,
添加一个图书,
根据名称删除图书,
把所有名称是“我”开头的图书删除,
删除全部图书,
把20元以上的图书价格都修改为18.8,
查看全部图书,
查看价格高于10块钱的全部图书
——————————————————
_____大概完成了以上sql命令的持久层实现
——通过maven+mybatis+JSP的方式
____虽然目前的controller层只有一个getAll数据遍历的实现
——————————————————
首先是pom文件:
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.cotomi</groupId> <artifactId>test0427</artifactId> <version>1.0</version> <packaging>war</packaging> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.1.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>jsp-api</artifactId> <version>2.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> <scope>compile</scope> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.6</version> <scope>compile</scope> </dependency> <!-- https://mvnrepository.com/artifact/javax.servlet/jstl --> <dependency> <groupId>javax.servlet</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.taglibs/taglibs-standard-impl --> <dependency> <groupId>org.apache.taglibs</groupId> <artifactId>taglibs-standard-impl</artifactId> <version>1.2.5</version> <scope>runtime</scope> </dependency> </dependencies> <build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> <include>**/*.properties</include> </includes> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.xml</include> <include>**/*.properties</include> </includes> </resource> </resources> </build> </project>
然后是Mybatis的SQLMapConfig
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="jdbc.properties"></properties> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <typeAliases> <package name="com.cotomi.entity"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <package name="com.cotomi.mapper"/> </mappers> </configuration>
jdbc的properties文件:
jdbc.driverClassName=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai jdbc.username=root jdbc.password=root
Mapper层映射的BookMapper接口:
package com.cotomi.mapper; import com.cotomi.entity.Book; import java.math.BigDecimal; import java.util.List; /** * @ClassName BookMapper * @Date 2022/4/27 17:50 * @Author 音无彩名 * @Description 按照要求实现的应该的功能 * 添加一个图书, * 根据名称删除图书, * 把所有名称是“我”开头的图书删除, * 删除全部图书, * 把20元以上的图书价格都修改为18.8, * 查看全部图书, * 查看价格高于10块钱的全部图书、 * ..... * 这里只是建立持久层的通用CURD操作 */ public interface BookMapper { //批量添加图书(在这里顺便实现了添加一个图书的功能) int addBatch(List<Book> bookList); //批量添加图书(包括自增的主键) int addBatchAll(List<Book> bookList); //完整书名删除图书 int deleteByName(Book book); //按照开头的模糊匹配删除图书 int deleteByNameBegin(String beginName); //删除全部图书 int deleteAll(); //将高于money1价格的图书全部修改为money2价格 int updateHighMoneyToOtherMoney(BigDecimal money1, BigDecimal money2); //查看全部图书 List<Book> getAll(); //查看价格高于money的全部图书 List<Book> getUpMoneyBooks(BigDecimal money); }
Mapper层映射的BookMapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.cotomi.mapper.BookMapper"> <sql id="allBookNoId"> bookname,bookprice </sql> <sql id="allBook"> id, bookname, bookprice </sql> <!-- //批量添加图书(在这里顺便实现了添加一个图书的功能) int addBatch(List<Book> bookList); --> <insert id="addBatch"> insert into book(<include refid="allBookNoId"></include>) values <foreach collection="list" item="book" separator=",">(#{book.bookname},#{book.bookprice})</foreach> </insert> <!-- int addBatchAll(List<Book> bookList);--> <insert id="addBatchAll"> insert into book(<include refid="allBook"></include>) values <foreach collection="list" item="bookAll" separator=","> (#{bookAll.id},#{bookAll.bookname},#{bookAll.bookprice}) </foreach> </insert> <!-- //完整书名删除图书 int deleteByName(Book book); --> <delete id="deleteByName" parameterType="book"> delete from book where bookname = #{bookname} </delete> <!--//按照开头的模糊匹配删除图书 int deleteByNameBegin(String beginName);--> <delete id="deleteByNameBegin" parameterType="string"> delete from book where bookname like concat(#{beginName},'%') </delete> <!-- //删除全部图书 int deleteAll();--> <delete id="deleteAll"> delete from book </delete> <!-- //将高于money1价格的图书全部修改为money2价格 int updateHighMoneyToOtherMoney(BigDecimal money1, BigDecimal money2); --> <update id="updateHighMoneyToOtherMoney"> update book set bookprice = #{arg1} where bookprice > #{arg0} </update> <!-- //查看全部图书 List<Book> getAll();--> <select id="getAll" resultType="book"> select <include refid="allBookNoId"></include> from book </select> <!-- //查看价格高于money的全部图书 List<Book> getUpMoneyBooks(BigDecimal money);--> <select id="getUpMoneyBooks" resultType="book"> select <include refid="allBookNoId"></include> from book where bookprice > #{arg0} </select> </mapper>
Book实体类:
package com.cotomi.entity; import java.math.BigDecimal; import java.util.Objects; /** * @ClassName Book * @Date 2022/4/27 17:45 * @Author 音无彩名 * @Description 对应数据库中book表的实体类 */ public class Book { private Integer id; private String bookname; private BigDecimal bookprice; public Book() { } public Book(String bookname, BigDecimal bookprice) { this.bookname = bookname; this.bookprice = bookprice; } public Book(Integer id, String bookname, BigDecimal bookprice) { this.id = id; this.bookname = bookname; this.bookprice = bookprice; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getBookname() { return bookname; } public void setBookname(String bookname) { this.bookname = bookname; } public BigDecimal getBookprice() { return bookprice; } public void setBookprice(BigDecimal bookprice) { this.bookprice = bookprice; } @Override public String toString() { return "Book{" + "id=" + id + ", bookname='" + bookname + '\'' + ", bookprice=" + bookprice + '}'; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; Book book = (Book) o; return Objects.equals(id, book.id) && Objects.equals(bookname, book.bookname) && Objects.equals(bookprice, book.bookprice); } @Override public int hashCode() { return Objects.hash(id, bookname, bookprice); } }
数据库表创建语句:
CREATE TABLE `book` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `bookname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名', `bookprice` decimal(10,2) NOT NULL COMMENT '价格', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;
对于持久层代码功能的Junit测试:
----这里只是测试功能的代码 并无业务功能上的意义
package com.cotomi.test; import com.cotomi.entity.Book; import com.cotomi.mapper.BookMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.util.ArrayList; import java.util.List; /** * @ClassName MyTest * @Date 2022/4/27 18:13 * @Author 音无彩名 * @Description TODO */ public class MyTest { SqlSession sqlSession; BookMapper bookMapper; @Before public void openSqlSession() throws IOException { InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); bookMapper = sqlSession.getMapper(BookMapper.class); } @After public void closeSqlSession(){ sqlSession.close(); } @Test public void testAddBatch(){ List<Book> bookList = new ArrayList<>(); bookList.add(new Book("你的图书5",new BigDecimal("23.5"))); bookList.add(new Book("你的图书6",new BigDecimal("2.5"))); System.out.println(bookMapper.addBatch(bookList)); sqlSession.commit(); } @Test public void testAddBatchAll(){ BigDecimal bigDecimal = new BigDecimal("12.11"); Book book1 = new Book(9,"我的图书11",bigDecimal); Book book2 = new Book(11,"我的图书12",bigDecimal); List<Book> bookList = new ArrayList<>(); bookList.add(book1); bookList.add(book2); int i = bookMapper.addBatchAll(bookList); sqlSession.commit(); System.out.println(i); } @Test public void testDeleteByName(){ Book book = new Book(); book.setBookname("我的图书11"); System.out.println(bookMapper.deleteByName(book)); sqlSession.commit(); } @Test public void testDeleteByNameBegin(){ String begin = "我"; System.out.println(bookMapper.deleteByNameBegin(begin)); sqlSession.commit(); } @Test public void testDeleteAll(){ System.out.println(bookMapper.deleteAll()); sqlSession.commit(); } @Test public void testUpdateHighMoneyToOtherMoney(){ System.out.println(bookMapper.updateHighMoneyToOtherMoney(new BigDecimal("20"), new BigDecimal("18.8"))); sqlSession.commit(); } @Test public void testGetAll(){ List<Book> bookList = bookMapper.getAll(); bookList.forEach(book -> System.out.println(book)); } @Test public void testGetUpMoneyBooks(){ List<Book> bookList = bookMapper.getUpMoneyBooks(new BigDecimal("10")); bookList.forEach(book -> System.out.println(book)); } }
因为jsp界面中只要求了输出数据库中文件 所以service层代码只实现了getAll这一个接口,同理controller层中同样只实现了这一个功能
package com.cotomi.service; import com.cotomi.entity.Book; import java.util.List; /** * @ClassName BookService * @Date 2022/4/27 22:03 * @Author 音无彩名 * @Description TODO */ public interface BookService { //获取所有图书 List<Book> getAll(); }
package com.cotomi.service.impl; import com.cotomi.entity.Book; import com.cotomi.mapper.BookMapper; import com.cotomi.service.BookService; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; /** * @ClassName BookService * @Date 2022/4/27 22:03 * @Author 音无彩名 * @Description TODO */ public class BookServiceImpl implements BookService{ SqlSession sqlSession; BookMapper bookMapper; @Override public List<Book> getAll() { List<Book> bookList = null; try { this.openSqlSession(); bookList = bookMapper.getAll(); } catch (IOException e) { e.printStackTrace(); } finally { this.closeSqlSession(); } return bookList; } private void openSqlSession() throws IOException { InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); bookMapper = sqlSession.getMapper(BookMapper.class); } private void closeSqlSession(){ sqlSession.close(); } }
控制层:
package com.cotomi.controller; import com.cotomi.entity.Book; import com.cotomi.service.BookService; import com.cotomi.service.impl.BookServiceImpl; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; /** * @ClassName Page * @Date 2022/4/27 21:55 * @Author 音无彩名 * @Description TODO */ @WebServlet("/show") public class Page extends HttpServlet { @Override protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String servletPath = request.getServletPath(); switch (servletPath){ case "/show": doShowAll(request,response); break; default: System.out.println("哒咩"); break; } } private void doShowAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { BookService bookService = new BookServiceImpl(); List<Book> bookList = bookService.getAll(); request.setAttribute("bookList",bookList); request.getRequestDispatcher("/view/show.jsp").forward(request,response); return; } }
对应的展示ShowJSP文件:
<%@ page import="java.util.List" %> <%@ page import="com.cotomi.entity.Book" %><%-- Created by IntelliJ IDEA. User: 音无彩名 Date: 2022/4/27 Time: 21:58 To change this template use File | Settings | File Templates. --%> <%@page language="java" contentType="text/html;charset=utf-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@page isELIgnored="false"%> <% List<Book> bookList = (List<Book>)request.getAttribute("bookList"); %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>JSP0427homework</title> </head> <body> <table border="1px" cellspacing="0px" cellpadding="20px"> <tr> <th>书籍名</th> <th>价格</th> </tr> <c:forEach items="${requestScope.bookList}" var="book" varStatus="count"> <tr> <td>${book.bookname}</td> <td>${book.bookprice}</td> </tr> </c:forEach> </table> </body> </html>
<%@page language="java" contentType="text/html;charset=utf-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%@page isELIgnored="false"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>JSP0427homework</title> </head> <body> <a href="${pageContext.request.contextPath}/show">一切从这里开始</a> </body> </html>
界面截图:
——————————————————————————————————————————————————
————————————————————————————————————
...
安