增删改查
增删改查
数据库中数据
新建springboot工程，导入依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.28</version>
<scope>runtime</scope>
</dependency>
配置数据库
spring.datasource.url=jdbc:mysql:///test?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
展示所有数据
实体类
public class Admin {
Integer id;
String username;
String password;
String name;
String email;
String icon;
//get set
}
AdminMapper
public interface AdminMapper {
List<Admin> getAllAdmin();
}
AdminMapper.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.example.test.mapper.AdminMapper">
<select id="getAllAdmin" resultType="com.example.test.entity.Admin">
select * from t_admin;
</select>
</mapper>
因为AdminMapper和AdminMapper.xml放在一起
在pom中加入
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>
**/*.xml
</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
AdminService
@Service
public class AdminService {
@Autowired
private AdminMapper adminMapper;
public List<Admin> getAllAdmin() {
return adminMapper.getAllAdmin();
}
}
AdminController
@Controller
public class AdminController {
@Autowired
private AdminService adminService;
@RequestMapping("/index")
public String getAllAdmin(Model model) {
model.addAttribute("admins", adminService.getAllAdmin());
return "index";
}
}
在前端展示
导入freemarker依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
在配置文件中加入
spring.freemarker.suffix=.ftl
index.ftl
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>admin</title>
</head>
<body>
<table border="1" align="center">
<#list admins as item>
<tr>
<#if item.username??>
<td>${item.username}</td>
</#if>
<#if item.password??>
<td>${item.password}</td>
</#if>
<#if item.name??>
<td>${item.name}</td>
</#if>
<#if item.email??>
<td>${item.email}</td>
</#if>
</tr>
</#list>
</table>
</body>
</html>
效果图
增加数据
AdminMapper
Integer addAdmin(Admin admin);
AdminMapper.xml
<insert id="addAdmin" parameterType="com.example.test.entity.Admin">
insert into t_admin (username,password,name,email) values (#{username},#{password},#{name},#{email});
</insert>
AdminService
public void addAdmin(Admin admin) {
adminMapper.addAdmin(admin);
}
AdminController
@RequestMapping("/add")
public String add() {
return "add";
}
@RequestMapping("/addadmin")
public String addadmin(Admin admin) {
adminService.addAdmin(admin);
return "redirect:/index";
}
前端
在index中添加
<a href="/add"><input type="button" value="添加"></a><br>
新建add.html
<body>
<div style="width:100%;text-align:center">
<form action="/addadmin" method="post">
username：<input type="text" name="username"><br>
password：<input type="text" name="password"><br>
name:<input type="text" name="name"><br>
email:<input type="text" name="email"><br>
<input type="submit" value="提交">
</form>
</div>
</body>
</body>
效果图
删除操作
Integer deleteAdminById(Integer id);
<delete id="deleteAdminById">
delete from t_admin where id=#{id}
</delete>
AdminService
public void deleteById(Integer id) {
adminMapper.deleteAdminById(id);
}
AdminController
@RequestMapping("/delete")
public String deleteAdmin(Integer id) {
adminService.deleteById(id);
return "redirect:/index";
}
在index.html加入删除按钮
<td><a href="/delete?id=${item.id}"><input type="button" value="删除"></a></td>
修改操作
Admin getAdminById(Integer id);
Integer updateAdminById(Admin admin);
<select id="getAdminById" resultType="com.example.test.entity.Admin">
select * from t_admin where id = #{id}
</select>
<update id="updateAdminById" parameterType="com.example.test.entity.Admin">
update t_admin set username=#{username},password=#{password},name=#{name},email=#{email},icon=#{icon} where id=#{id}
</update>
public void updateAdmin(Admin admin) {
adminMapper.updateAdminById(admin);
}
public Admin getAdminById(Integer id) {
return adminMapper.getAdminById(id);
}
@RequestMapping("/update")
public String update(Model model, Integer id) {
model.addAttribute("admin", adminService.getAdminById(id));
return "update";
}
@RequestMapping("/updateadmin")
public String updateAdmin(Admin admin) {
adminService.updateAdmin(admin);
return "redirect:/index";
}
在index中加入修改按钮
<td><a href="/update?id=${item.id}"><input type="button" value="修改"></a></td>
update.ftl
<div>修改信息</div>
<div style="width:100%;text-align:center">
<form action="/updateadmin" method="post" >
username：<input type="text" name="username" value="${admin.username}"><br>
password：<input type="text" name="password"><br>
name:<input type="text" name="name" value="${admin.name}"><br>
email:<input type="text" name="email" value="${admin.email}"><br>
<input type="hidden" name="id" value="${admin.id}">
<input type="submit" value="提交">
</form>
</div>
模糊查询，和分页操作
在index中加入
<div align="center">
<form action="/getbycondtion?page=1" method="post">
<input type="text" name="word"><input type="submit" value="确定">
</form>
</div>
新建condtion.html
<body>
<a href="/add"><input type="button" value="添加"></a><br>
<div align="center">
<form action="/getbycondtion" method="post">
<input type="text" name="word">
<input type="submit" value="确定">
</form>
</div>
<table border="1" align="center">
<#list admins as item>
<tr>
<#if item.username??>
<td>${item.username}</td>
</#if>
<#if item.password??>
<td>${item.password}</td>
</#if>
<#if item.name??>
<td>${item.name}</td>
</#if>
<#if item.email??>
<td>${item.email}</td>
</#if>
<td><a href="/update?id=${item.id}"><input type="button" value="修改"></a></td>
<td><a href="/delete?id=${item.id}"><input type="button" value="删除"></a></td>
</tr>
</#list>
</table>
<div align="center">
<a href="/getbycondtion?word=${word}&page=${page-1}">上一页</a>
<a>总数据量${total}，当前<span style="color: red;">${page}</span>/${pagesize}</a>页
<a href="/getbycondtion?word=${word}&page=${page+1}">下一页</a>
</div>
</body>
AdminController
public final static int size = 4;//每页展示4条
@RequestMapping("/getbycondtion")
public String getByCondtion(Model model, String word, Integer page) {
if (page<=0||page == null||page.equals("")) {
page=1;
}
List<Admin> admins = adminService.getByController(word, page, size);
Integer total = adminService.getAdminByConditionTotal(word);
//总页数
Integer pageSize = (total - 1) / size + 1;
//如果page>总页数,那么page使他在pageSize最大一页
if(page>pageSize){
page=pageSize;
admins=adminService.getByController(word, pageSize, size);
}
model.addAttribute("admins", admins);
model.addAttribute("total", total);
model.addAttribute("page", page);
model.addAttribute("pagesize", pageSize);
model.addAttribute("word",word);
return "condtion";
}
AdminService
public List<Admin> getByController(String word, int page, int size) {
int row = (page-1)*size;
return adminMapper.getAdminByCondition(word,row,size);
}
//按条件查询的总页数
public Integer getAdminByConditionTotal(String word) {
return adminMapper.getAdminByConditionTotal(word);
}
AdminMapper
List<Admin> getAdminByCondition(@Param("word") String word, @Param("row") int row, @Param("size") int size);
//得到total
Integer getAdminByConditionTotal(String word);
AdminMapper.xml
<select id="getAdminByCondition" resultType="com.example.test.entity.Admin">
select * from t_admin where username like concat('%',#{word},'%') limit #{row},#{size}
</select>
<select id="getAdminByConditionTotal" parameterType="String" resultType="Integer">
select count(*) from t_admin where username like concat('%',#{word},'%');
</select>
效果图
将数据导入到excel中
导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
ExcelDownload
@Controller
public class ExcelDownload {
@Autowired
private AdminMapper adminMapper;
@RequestMapping("/exceldownload")
public void exceldownload(HttpServletResponse response)throws IOException {
//表头数据：
String[] header={"id","username","name","email"};
//声明工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个表格
HSSFSheet sheet = workbook.createSheet("管理员表");
//设置表格列宽度为10个字节
sheet.setDefaultColumnWidth(10);
//创建标题的显示样式
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//创建第一行表头
HSSFRow headrow = sheet.createRow(0);
//遍历添加表头
for (int i = 0; i < header.length; i++) {
//创建一个单元格
HSSFCell cell = headrow.createCell(i);
//创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(header[i]);
//将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
cell.setCellStyle(headerStyle);
}
//获取所有的admin
List<Admin> admins=adminMapper.getAllAdmin();
for (int i = 0; i < admins.size(); i++) {
//创建第一行
HSSFRow row1 = sheet.createRow(i+1);
//第一列创建并赋值
row1.createCell(0).setCellValue(new HSSFRichTextString(admins.get(i).getId().toString()));
row1.createCell(1).setCellValue(new HSSFRichTextString(admins.get(i).getUsername()));
row1.createCell(2).setCellValue(new HSSFRichTextString(admins.get(i).getName()));
row1.createCell(3).setCellValue(new HSSFRichTextString(admins.get(i).getEmail()));
}
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
response.setContentType("application/octet-stream");
//这后面可以设置导出Excel的名称，此例中名为student.xls
response.setHeader("Content-disposition", "attachment;filename=admin.xls");
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中，供页面下载
workbook.write(response.getOutputStream());
}
}
