一、工作(jobs)的CRUD
-
1.三层架构
dao层
package cn.itsource.cms.dao;
import java.util.List;
import cn.itsource.cms.domain.Jobs;
import cn.itsource.cms.page.PageList;
import cn.itsource.cms.query.SqlCondition;
public interface IJobsDao {
void save(Jobs jobs);
void update(Jobs jobs);
void delete(Integer id);
Jobs finalId(Integer id);
List<Jobs> finalAll();
//分页查询的方法,query代表的是有条件查询
PageList<Jobs> queryAll(SqlCondition condition);
}
impl
package cn.itsource.cms.dao.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import cn.itsource.cms.dao.IJobsDao;
import cn.itsource.cms.domain.Jobs;
import cn.itsource.cms.page.PageList;
import cn.itsource.cms.query.SqlCondition;
@Repository
public class JobsDaoImpl implements IJobsDao{
@Autowired
private JdbcTemplate jdbc;
@Override
public void save(Jobs jobs) {
jdbc.update("insert into t_jobs (title,address_id,jobnum,treatment,describes,requires,htmlurl,positiontype,isenabled,inputdate) "
+ "values(?,?,?,?,?,?,?,?,?,?);",jobs.getTitle(),jobs.getAddress_id(),jobs.getJobnum(),jobs.getTreatment(),jobs.getDescribes(),jobs.getRequires()
,jobs.getHtmlurl(),jobs.getPositiontype(),jobs.getIsenabled(),jobs.getInputdate()
);
}
@Override
public void update(Jobs jobs) {
//修改要求:只修改简介和是否启用
jdbc.update("update t_jobs set title=?,address_id=?,jobnum=?,treatment=?,describes=?,requires=?,htmlurl=?,positiontype=?,isenabled=?,inputdate=? where id=?;",
jobs.getTitle(),jobs.getAddress_id(),jobs.getJobnum(),jobs.getTreatment(),jobs.getDescribes(),jobs.getRequires()
,jobs.getHtmlurl(),jobs.getPositiontype(),jobs.getIsenabled(),jobs.getInputdate(),jobs.getId() );
}
@Override
public void delete(Integer id) {
jdbc.update("delete from t_jobs where id=?;",id);
}
@Override
public Jobs finalId(Integer id) {
try {
return jdbc.queryForObject("select * from t_jobs where id =?", new BeanPropertyRowMapper<Jobs>(Jobs.class),id);
} catch (Exception e) {
// TODO: handle exception
return null;
}
}
@Override
public List<Jobs> finalAll() {
try {
return jdbc.query("select * from v_jobs ;", new BeanPropertyRowMapper<Jobs>(Jobs.class));
} catch (Exception e) {
return null;
}
}
@Override
public PageList<Jobs> queryAll(SqlCondition condition) {
//拿到当前页和每页条数
int currentPage = condition.getCurrentPage();
int pageSize = condition.getPageSize();
//获取当前查询的sql
String whereSql = condition.createWhereSql();
//查询总条数
//1.准备查询总条数的sql语句
String sql="select count(*) from v_jobs "+whereSql;
//执行语句,拿到总条数
Integer totalCount = jdbc.queryForObject(sql, Integer.class);
//查询当前页的数据
//1.计算当前也是从第几条数据开始的
int beginIndex = (currentPage-1) * pageSize;
//sql语句
String datasql ="select * from v_jobs "+whereSql+" limit "+beginIndex+","+pageSize;
//执行查询功能
List<Jobs> data = jdbc.query(datasql, new BeanPropertyRowMapper<Jobs>(Jobs.class));
//创建Pagelist,并返回
PageList pageList = new PageList<>(currentPage, pageSize, totalCount, data);
return pageList;
}
}
service
package cn.itsource.cms.service;
import java.util.List;
import cn.itsource.cms.domain.Jobs;
import cn.itsource.cms.page.PageList;
import cn.itsource.cms.query.SqlCondition;
public interface IJobsService {
void save(Jobs jobs);
void update(Jobs jobs);
void delete(Integer id);
Jobs finalId(Integer id);
List<Jobs> finalAll();
//分页查询的方法,query代表的是有条件查询
PageList<Jobs> queryAll(SqlCondition condition);
}
domain
package cn.itsource.cms.domain;
import java.util.Date;
public class Jobs {
private Integer id;
private String title;//职位标题
private String address;//招聘地址
//工作地址对应的id
private Integer address_id;
private Integer jobnum;//招聘人数
private Integer treatment;//待遇(工资)
private String describes;//职位描述
private String requires;//职业要求
private String htmlurl;//静态网站地址
private Integer positiontype;//职位类型
private Boolean isenabled;//是否录入
private Date inputdate = new Date();//录入时间
/**
* @return the id
*/
public Integer getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(Integer id) {
this.id = id;
}
/**
* @return the title
*/
public String getTitle() {
return title;
}
/**
* @param title the title to set
*/
public void setTitle(String title) {
this.title = title;
}
/**
* @return the address
*/
public String getAddress() {
return address;
}
/**
* @param address the address to set
*/
public void setAddress(String address) {
this.address = address;
}
/**
* @return the jobnum
*/
public Integer getJobnum() {
return jobnum;
}
/**
* @param jobnum the jobnum to set
*/
public void setJobnum(Integer jobnum) {
this.jobnum = jobnum;
}
/**
* @return the treatment
*/
public Integer getTreatment() {
return treatment;
}
/**
* @param treatment the treatment to set
*/
public void setTreatment(Integer treatment) {
this.treatment = treatment;
}
/**
* @return the describes
*/
public String getDescribes() {
return describes;
}
/**
* @param describes the describes to set
*/
public void setDescribes(String describes) {
this.describes = describes;
}
/**
* @return the requires
*/
public String getRequires() {
return requires;
}
/**
* @param requires the requires to set
*/
public void setRequires(String requires) {
this.requires = requires;
}
/**
* @return the htmlurl
*/
public String getHtmlurl() {
return htmlurl;
}
/**
* @param htmlurl the htmlurl to set
*/
public void setHtmlurl(String htmlurl) {
this.htmlurl = htmlurl;
}
/**
* @return the positiontype
*/
public Integer getPositiontype() {
return positiontype;
}
/**
* @param positiontype the positiontype to set
*/
public void setPositiontype(Integer positiontype) {
this.positiontype = positiontype;
}
/**
* @return the isenabled
*/
public Boolean getIsenabled() {
return isenabled;
}
/**
* @param isenabled the isenabled to set
*/
public void setIsenabled(Boolean isenabled) {
this.isenabled = isenabled;
}
/**
* @return the inputdate
*/
public Date getInputdate() {
return inputdate;
}
/**
* @param inputdate the inputdate to set
*/
public void setInputdate(Date inputdate) {
this.inputdate = inputdate;
}
/**
* @return the address_id
*/
public Integer getAddress_id() {
return address_id;
}
/**
* @param address_id the address_id to set
*/
public void setAddress_id(Integer address_id) {
this.address_id = address_id;
}
/* (non-Javadoc)
* @see java.lang.Object#toString()
*/
@Override
public String toString() {
return id + ", " + title + ", " + address + ", " + jobnum + ", " + treatment + ", " + describes + ", "
+ requires + ", " + htmlurl + ", " + positiontype + ", " + isenabled + ", " + inputdate + "]";
}
}
Controller
package cn.itsource.cms.web.controller;
import java.io.File;
import java.io.FileWriter;
import java.util.HashMap;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import com.sun.org.apache.xalan.internal.xsltc.compiler.sym;
import cn.itsource.cms.domain.Jobs;
import cn.itsource.cms.query.SqlCondition;
import cn.itsource.cms.service.IAddressService;
import cn.itsource.cms.service.IJobsService;
import freemarker.template.Configuration;
import freemarker.template.Template;
@Controller
@RequestMapping("/jobs")
public class JobsController {
@Autowired
private IJobsService jobsService;
@Autowired
private IAddressService addressService;
@RequestMapping("/query")
public String query(SqlCondition condition, Model model){
//把所有数据传到前台(支持分页的)
model.addAttribute("pageList", jobsService.queryAll(condition));
return "jobs";
}
/**修改或添加的跳转
* @return
*/
@RequestMapping("/input")
public String input(Integer id,Model model){
model.addAttribute("addressList",addressService.fandll());
if (id != null) {
//把当前的工作对象传到前端
model.addAttribute("job", jobsService.finalId(id));
return "jobs_edit";//ID不为空,就携带当前的工作对象传递到前台,跳转到修改界面
}
return "jobs_add";//没有ID就跳转到添加页面
}
@RequestMapping("/save")
public String save(Jobs jobs,HttpServletRequest req) throws Exception{
//如果这个静态页面已经存在,我就要把它干掉,避免创建多个静态页面
String realPath = req.getServletContext().getRealPath("/statichtml");
String htmlurl = jobs.getHtmlurl();//获取静态网站地址
if (htmlurl != null) {
File file2 = new File(realPath, htmlurl);
// 判断这个文件是否存在
if (file2.exists()) {
System.gc();//召唤垃圾回收
file2.delete();
}
}
//拿到配置对象
Configuration configuration = new Configuration(Configuration.VERSION_2_3_28);
//加载路径
String path = "F:/c/javaee/cmstest/src/main/webapp/statichtml";
configuration.setDirectoryForTemplateLoading(new File(path));
//设置字符集
configuration.setDefaultEncoding("utf-8");
//获取模板
Template template = configuration.getTemplate("details.ftl");
//准备数据(数据就是jobs)
HashMap<Object,Object> map = new HashMap<>();
map.put("job", jobs);
//确保HTML名称唯一性,准备一个html的名称
String uuid = UUID.randomUUID().toString()+".html";
File file = new File(path, uuid);
FileWriter fileWriter = new FileWriter(file);
//把数据和模板输出,数据+模板=输出
template.process(map, fileWriter);
fileWriter.close();
//把相应的名称放到jobs中去
jobs.setHtmlurl(uuid);
if (jobs.getId() !=null) {
jobsService.update(jobs);
}else{
jobsService.save(jobs);
}
return "redirect:/jobs/query";
}
@RequestMapping("/delete/{id}")
public String delete(@PathVariable("id") Integer id,HttpServletRequest req){
//拿到相应的地址
String realPath = req.getServletContext().getRealPath("//statichtml");
Jobs jobs = jobsService.finalId(id);
String htmlurl = jobs.getHtmlurl();
//创建相应的文件
File file = new File(realPath, htmlurl);
if (file.exists()) {
System.gc();
file.delete();
}
jobsService.delete(id);
return "redirect:/jobs/query";
}
}
-
- 注意:以后开发要注意,一个domain的属性(一张表中的列)在开发的时候一定要先把它搞清楚
二、回显功能
<select id="address" name="address_id" class="form-control">
<c:forEach items="${addressList}" var="address">
<option value="${address.id}"
<c:if test="${address.id==job.address_id}">
selected
</c:if>
>${address.name}</option>
</c:forEach>
</select>
三、创建视图
-
- 视图:相当于是一个假表
- 视图本质就是对查询的一个封装
-
- 视图创建:create view 视图名称 as select…
create view stuscore as select students.*,scores.score from scores inner join students on scores.stuid=students.id;
-
- 视图查询:select * from 视图名称
五、模板技术
-
- 动态网页(数据可变)与静态网页(访问快,SEO排名高)
- 动态网页静态化(伪静态[只改后缀],真静态)
用到模板技术:数据+模板=输入文本
freemarker【ftl】,velocity【vm】
- 动态网页(数据可变)与静态网页(访问快,SEO排名高)
-
-查错方法:
-
F12->看js有没有错,再看网络传参有没有问题
如果前端没有问题,问题就在后端(有没有接收参数,如果接收到了,有没有调用方法,SQL有没有写对,数据有没有返回)
没有找到 -> 比较法,打印法,删除法