目录

一、数据库创建、CRUD

二、MyBatis集成

三、ViewObject：方便传递任何数据到Velocity

四、DataTool：Velocity自带工具类导入

---

一、数据库创建、CRUD

 推荐使用GUI转SQL的方式生成SQL脚本。

常用数据类型

int

varchar(n)

datetime

float(m,d)

text，65535

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

SELECT */列名称 FROM 表名称

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

DELETE FROM 表名称 WHERE 列名称 = 值

SELECT {COLA,COLB,*} FROM {TABLE} WHERE {CONDITION}

ORDER BY {COL} DESC

LIMIT {OFF},{COUNT}

二、MyBatis集成

spring.datasource.url=jdbc:mysql://localhost:3306/toutiao?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=nowcoder
#添加mybatis配置文件
mybatis.config-location=classpath:mybatis-config.xml
#logging.level.root=DEBUG
spring.velocity.suffix=.html
spring.velocity.cache=false
spring.velocity.toolbox-config-location=toolbox.xml

 注解配置：

@Mapper
public interface UserDAO {
    String TABLE_NAME = "user";
    String INSET_FIELDS = " name, password, salt, head_url ";
    String SELECT_FIELDS = " id, name, password, salt, head_url";

    @Insert({"insert into ", TABLE_NAME, "(", INSET_FIELDS,
            ") values (#{name},#{password},#{salt},#{headUrl})"})
    int addUser(User user);

    @Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME, " where id=#{id}"})
    User selectById(int id);

    @Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME, " where name=#{name}"})
    User selectByName(String name);

    @Update({"update ", TABLE_NAME, " set password=#{password} where id=#{id}"})
    void updatePassword(User user);
    
    //一般是通过update ... set statue = 0 where ...来进行删除
    @Delete({"delete from ", TABLE_NAME, " where id=#{id}"})
    void deleteById(int id);
}

XML配置：在相同的包目录下定义的同名XML

文件名：NewDAO.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.nowcoder.dao.NewsDAO">
    <sql id="table">news</sql>
    <sql id="selectFields">id,title, link, image, like_count, comment_count,created_date,user_id
    </sql>
    <select id="selectByUserIdAndOffset" resultType="com.nowcoder.model.News">
        SELECT
        <include refid="selectFields"/>
        FROM
        <include refid="table"/>

        <if test="userId != 0">
            WHERE user_id = #{userId}
        </if>
        ORDER BY id DESC
        LIMIT #{offset},#{limit}
    </select>
</mapper>

NewDAO.java

@Mapper
public interface NewsDAO {
    String TABLE_NAME = "news";
    String INSERT_FIELDS = " title, link, image, like_count, comment_count, created_date, user_id ";
    String SELECT_FIELDS = " id, " + INSERT_FIELDS;

    @Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
            ") values (#{title},#{link},#{image},#{likeCount},#{commentCount},#{createdDate},#{userId})"})
    int addNews(News news);

    @Select({"select ", SELECT_FIELDS , " from ", TABLE_NAME, " where id=#{id}"})
    News getById(int id);

    @Update({"update ", TABLE_NAME, " set comment_count = #{commentCount} where id=#{id}"})
    int updateCommentCount(@Param("id") int id, @Param("commentCount") int commentCount);

    @Update({"update ", TABLE_NAME, " set like_count = #{likeCount} where id=#{id}"})
    int updateLikeCount(@Param("id") int id, @Param("likeCount") int likeCount);

    //这里用到了@Param的注解，因为NewDAO.xml中会使用到
    List<News> selectByUserIdAndOffset(@Param("userId") int userId, @Param("offset") int offset,
                                       @Param("limit") int limit);
}

三、ViewObject：方便传递任何数据到Velocity

ViewObject定义在Model包下：

public class ViewObject {
    private Map<String, Object> objs = new HashMap<String, Object>();
    public void set(String key, Object value) {
        objs.put(key, value);
    }

    public Object get(String key) {
        return objs.get(key);
    }
}

例如，获取News的方法：

    private List<ViewObject> getNews(int userId, int offset, int limit) {
        List<News> newsList = newsService.getLatestNews(userId, offset, limit);
        int localUserId = hostHolder.getUser() != null ? hostHolder.getUser().getId() : 0;
        List<ViewObject> vos = new ArrayList<>();
        for (News news : newsList) {
            ViewObject vo = new ViewObject();
            vo.set("news", news);
            vo.set("user", userService.getUser(news.getUserId()));
            if (localUserId != 0) {
                vo.set("like", likeService.getLikeStatus(localUserId, EntityType.ENTITY_NEWS, news.getId()));
            } else {
                vo.set("like", 0);
            }
            vos.add(vo);
        }
        return vos;
    }

    @RequestMapping(path = {"/", "/index"}, method = {RequestMethod.GET, RequestMethod.POST})
    public String index(Model model,
                        @RequestParam(value = "pop", defaultValue = "0") int pop) {
        model.addAttribute("vos", getNews(0, 0, 10));
        if (hostHolder.getUser() != null) {
            pop = 0;
        }
        model.addAttribute("pop", pop);
        return "home";
    }

做成一个List，然后就可以在Velocity中写for语句把News展示出来了！

#foreach ($vo in $vos)

......

四、DataTool：Velocity自带工具类导入

实现功能：将News按照时间分开

编写toolbox.xml，里面定义DataTool，作用范围是整个spring

<toolbox>
    <tool>
    <key>date</key>
    <scope>application</scope>
    <class>org.apache.velocity.tools.generic.DateTool</class>
</tool>
</toolbox>

然后在application.properties中添加：

spring.velocity.toolbox-config-location=toolbox.xml

home.html中：

#set($cur_date = '')
#foreach($vo in $vos)
#if ($cur_date != $date.format('yyyy-MM-dd', $vo.news.createdDate))
    #if ($foreach.index > 0)
        </div> ## 上一个要收尾
    #end
    #set($cur_date = $date.format('yyyy-MM-dd', $vo.news.createdDate))
                    <h3 class="date">
                        <i class="fa icon-calendar"></i>
                        <span>头条资讯 &nbsp; $date.format('yyyy-MM-dd', $vo.news.createdDate)</span>
                    </h3>

                    <div class="posts">
                    #end
.......
##后面还有