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 {
@Autowired
JdbcTemplate 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 {
@Autowired
BookDao 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>