SpringMVC第七阶段：SpringMVC的增删改查（01）

SpringMVC的增删改查

1、准备单表的数据库

drop database if exists springmvc;create database springmvc;use springmvc; ##创建图书表
create table t_book(`id` int(11) primary key auto_increment, 	## 主键`name` varchar(50) not null,				## 书名 `author` varchar(50) not null,				## 作者`price` decimal(11,2) not null,				## 价格`sales` int(11) not null,					## 销量`stock` int(11) not null					## 库存
);## 插入初始化测试数据
insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '怎样拐跑别人的媳妇' , '龙伍' , 68, 99999 , 52);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '木虚肉盖饭' , '小胖' , 16, 1000 , 50);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , 'C++编程思想' , '刚哥' , 45.5 , 14 , 95);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '蛋炒饭' , '周星星' , 9.9, 12 , 53);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '赌神' , '龙伍' , 66.5, 125 , 535);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '西游记' , '罗贯中' , 12, 19 , 9999);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '水浒传' , '华仔' , 33.05 , 22 , 88);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '操作系统原理' , '刘优' , 133.05 , 122 , 188);insert into t_book(`id` , `name` , `author` , `price` , `sales` , `stock`) 
values(null , '数据结构 java版' , '封大神' , 173.15 , 21 , 81);## 查看表内容
select id,name,author,price,sales,stock from t_book;

2、SpringMVC的增，删，改，查

需要导入的jar包有:

druid-1.1.9.jar
junit_4.12.jar
mysql-connector-java-5.1.37-bin.jar
org.hamcrest.core_1.3.0.jar
spring-aop-5.2.5.RELEASE.jar
spring-beans-5.2.5.RELEASE.jar
spring-context-5.2.5.RELEASE.jar
spring-core-5.2.5.RELEASE.jar
spring-expression-5.2.5.RELEASE.jar
spring-jcl-5.2.5.RELEASE.jar
spring-jdbc-5.2.5.RELEASE.jar
spring-orm-5.2.5.RELEASE.jar
spring-test-5.2.5.RELEASE.jar
spring-tx-5.2.5.RELEASE.jar
spring-web-5.2.5.RELEASE.jar
spring-webmvc-5.2.5.RELEASE.jar

在src目录下记得配置 jdbc.properties属性配置文件:

SpringMVC的配置文件内容如下:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"><context:component-scan base-package="com"></context:component-scan><!--配置视图解析器--><bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"><property name="prefix" value="/book/" /><property name="suffix" value=".jsp" /></bean><!-- 加载jdbc.properties属性配置文件 --><context:property-placeholder location="classpath:jdbc.properties" /><!-- 配置数据库连接池 --><bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"><property name="username" value="${user}" /><property name="password" value="${password}" /><property name="url" value="${url}" /><property name="driverClassName" value="${driverClassName}" /><property name="initialSize" value="${initialSize}" /><property name="maxActive" value="${maxActive}" /></bean><!-- 配置用于执行sql语句的jdbcTemplate工具类 --><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource" /></bean></beans>

web.xml中的配置内容如下:

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"version="4.0"><!-- 配置前端控制器 --><servlet><servlet-name>dispatcher</servlet-name><servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class><init-param><param-name>contextConfigLocation</param-name><param-value>classpath:springmvc.xml</param-value></init-param><load-on-startup>1</load-on-startup></servlet><servlet-mapping><servlet-name>dispatcher</servlet-name><url-pattern>/</url-pattern></servlet-mapping></web-app>

3、创建实体bean对象

public class Book {private Integer id;private String name;private String author;private BigDecimal price;private Integer sales;private Integer stock;
}

4、创建BookDao以及测试

@Repository
public class BookDao {@AutowiredJdbcTemplate jdbcTemplate;public int saveBook(Book book) {String sql = "insert into t_book( `name`,`author`,`price`,`sales`,`stock`) values(?,?,?,?,?)";return jdbcTemplate.update(sql, book.getName(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock());}public int deleteBookById(Integer id) {String sql = "delete from t_book where id = ?";return jdbcTemplate.update(sql, id);}public int updateBook(Book book) {String sql = "update t_book set `name`=?,`author`=?,`price`=?,`sales`=?,`stock`=? where id = ?";return jdbcTemplate.update(sql, book.getName(), book.getAuthor(),book.getPrice(), book.getSales(), book.getStock(), book.getId());}public Book queryBookById(Integer id){String sql = "select `name`,`author`,`price`,`sales`,`stock`,`id` from t_book where id = ?";return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class), id );}public List<Book> queryBooks(){String sql = "select `name`,`author`,`price`,`sales`,`stock`,`id` from t_book";return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));}}

5、创建BookService以及测试

@Service
public class BookService {@AutowiredBookDao bookDao;public void saveBook(Book book){bookDao.saveBook(book);}public void deleteBookById(Integer id){bookDao.deleteBookById(id);}public void updateBook(Book book){bookDao.updateBook(book);}public Book queryBookById(Integer id){return bookDao.queryBookById(id);}public List<Book> queryBooks(){return bookDao.queryBooks();}}

6、拷贝CRUD页面到WebContent目录下

从SpringMVC第二个的目录里,笔记目录中的CRUD目录

7、配置springMVC的视图解析器到springmvc.xml配置文件中

<!--配置视图解析器
-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"><property name="prefix" value="/book/" /><property name="suffix" value=".jsp" />
</bean>