目录
在static 下面创建页面index,add, update
在启动项目包下建立controller, entity, utils包
环境搭建
创建spring web项目
导入依赖
<!--*****************************************************-->
<!-- 连接MySQL数据库的依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<!--*****************************************************-->
在static 下面创建页面index,add, update
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>商品管理系统</h1>
<a href="/add.html">添加商品</a>
<a href="/select">商品列表</a>
<a href="/update.html">修改商品</a>
</body>
</html>
add.html
<body>
<h1>添加商品页面</h1>
<form action="/insert">
<input type="text" name="title" placeholder="商品标题">
<input type="text" name="price" placeholder="商品价格">
<input type="text" name="num" placeholder="商品库存">
<input type="submit" value="添加商品">
</form>
</body>
update.html
<h1>修改商品</h1>
<form action="/update">
<input type="text" name="id" placeholder="请输入修改商品的id">
<input type="text" name="title" placeholder="标题">
<input type="text" name="price" placeholder="价格">
<input type="text" name="num" placeholder="库存">
<input type="submit" value="修改商品">
</form>
</body>
在启动项目包下建立controller, entity, utils包
DBUtils.java
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DBUtils {
private static DruidDataSource dds;
static {
//创建数据库连接池
dds = new DruidDataSource();
//设置连接数据库的信息
dds.setDriverClassName("com.mysql.cj.jdbc.Driver");
dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");
dds.setUsername("root");
dds.setPassword("root");
//设置初始连接数量
dds.setInitialSize(3);
//设置最大连接数量
dds.setMaxActive(5);
}
public static Connection getConn() throws SQLException {
//获取连接对象 异常抛出
Connection conn = dds.getConnection();
System.out.println("连接:"+conn);
return conn;
}
}
Product.java
public class Product {
//包装类型有一个null值, 在未赋值的时候是null
//而int类型未赋值为0, 这样的话使用int类型无法判断出0是未赋值 还是赋值的就是0
private Integer id;
private String title;
private Integer price;
private Integer num;
//生成get和set方法 tostring方法
@Override
public String toString() {
return "Product{" +
"id=" + id +
", title='" + title + '\'' +
", price=" + price +
", num=" + num +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
}
ProductController.java
import cn.te.boot03.entity.Product;
import cn.te.boot03.utils.DBUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
@Controller
public class ProductController {
@RequestMapping("/insert")
@ResponseBody
public String insert(Product product){
System.out.println("product = " + product);
//获取数据库连接
try (Connection conn = DBUtils.getConn()){
//准备执行插入的SQL语句
String sql = "insert into product values(null,?,?,?)";
//创建执行SQL语句的对象
PreparedStatement ps = conn.prepareStatement(sql);
//替换SQL语句中的三个?
ps.setString(1,product.getTitle());
ps.setInt(2,product.getPrice());
ps.setInt(3,product.getNum());
ps.executeUpdate();//执行插入数据的SQL语句
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return "添加完成!<a href='/'>返回首页</a>";
}
@RequestMapping("/select")
@ResponseBody
public String select(){
//创建一个ArrayList集合对象 把查询到的多个商品对象保存
ArrayList<Product> list = new ArrayList<>();
//获取数据库连接
try (Connection conn = DBUtils.getConn()){
//准备执行查询的SQL语句
String sql = "select id,title,price,num from product";
//创建执行SQL语句的对象
PreparedStatement ps = conn.prepareStatement(sql);
//执行查询 , 查询到的结果在rs里面
ResultSet rs = ps.executeQuery();
//遍历结果集对象
while(rs.next()){
int id = rs.getInt(1);
String title = rs.getString(2);
int price = rs.getInt(3);
int num = rs.getInt(4);
//创建商品对象并把查询到的数据封装到对象中
Product p = new Product();
p.setId(id);
p.setTitle(title);
p.setPrice(price);
p.setNum(num);
//把对象装进list集合
list.add(p);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//把集合中的数据装进table表格中
String html = "<table border='1'>";
html+="<caption>商品列表</caption>";
html+="<tr><th>id</th><th>标题</th><th>价格</th><th>库存</th><th>操作</th></tr>";
//遍历集合 添加tr和td
for (Product p:list) {
html+="<tr>";
html+="<td>"+p.getId()+"</td>";
html+="<td>"+p.getTitle()+"</td>";
html+="<td>"+p.getPrice()+"</td>";
html+="<td>"+p.getNum()+"</td>";
//添加删除超链接的一列, 请求地址为 /delete?id=xxx ?是请求地址和参数的分隔符
html+="<td><a href='/delete?id="+p.getId()+"'>删除</a></td>";
html+="</tr>";
}
html+="</table>";
return html;//把页面和数据一起返回给客户端
}
@RequestMapping("/delete")
@ResponseBody
public String delete(int id){
System.out.println("id = " + id);
//获取数据库连接
try (Connection conn = DBUtils.getConn()){
String sql = "delete from product where id=?";
//创建执行SQL语句对象
PreparedStatement ps = conn.prepareStatement(sql);
//替换?
ps.setInt(1,id);
//执行插入SQL语句
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// http://localhost:8080/
return "删除完成!<a href='/select'>返回列表页面</a>";
}
@RequestMapping("/update")
@ResponseBody
public String update(Product product){
System.out.println("product = " + product);
//获取数据库连接
try (Connection conn = DBUtils.getConn()){
String sql = "update product set title=?,price=?,num=? where id=?";
//创建执行SQL语句对象
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,product.getTitle());
ps.setInt(2,product.getPrice());
ps.setInt(3,product.getNum());
ps.setInt(4,product.getId());
ps.executeUpdate();//执行修改SQL语句
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return "修改完成!<a href='/select'>返回列表页面</a>";
}
}
数据库搭建
show databases ;
CREATE DATABASE empdb charset=utf8;
use empdb;
create table user(
id int primary key auto_increment,
username varchar(50),
password varchar(50),
nickname varchar(50)
);
create table product(
id int primary key auto_increment,
title varchar(50),
price int,
num int
);