四. DAO层和model
2018-08-14 本文已影响0人
stoneyang94
结构
5. DAO
DAO
5.1 CommentDAO
@Mapper
public interface CommentDAO {
String TABLE_NAME = " comment ";
String INSERT_FIELDS = " user_id, content, created_date, entity_id, entity_type, status ";
String SELECT_FIELDS = " id, " + INSERT_FIELDS;
@Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
") values (#{userId},#{content},#{createdDate},#{entityId},#{entityType},#{status})"})
int addComment(Comment comment);
@Update({"update ", TABLE_NAME, " set status=#{status} where entity_id=#{entityId} and entity_type=#{entityType}"})
void updateStatus(@Param("entityId") int entityId, @Param("entityType") int entityType, @Param("status") int status);
@Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME,
" where entity_id=#{entityId} and entity_type=#{entityType} order by id desc"})
List<Comment> selectByEntity(@Param("entityId") int entityId, @Param("entityType") int entityType);
@Select({"select count(id) from ", TABLE_NAME, " where entity_id=#{entityId} and entity_type=#{entityType} "})
int getCommentCount(@Param("entityId") int entityId, @Param("entityType") int entityType);
}
1)DAO在JavaWeb开发中的定位
一个典型的DAO实现有下列几个组件：
1.一个DAO接口(CRUD)
2.一个实现DAO接口的具体类
3.数据传递对象(DTO)(POJO)：有些时候叫做值对象(VO)或领域模型（domain）
2)@Mapper注解
作用：持久化
- 一种持久化方式：
在一个包中写一个DAO的接口，在另一个包里面写DAO的实现，使用sqlMapClient来从***-sql.xml中读取相应的sql。 - spring+Mybatis的项目，一种新的持久化方式：
只写一个dao的接口，在接口的方法中直接注解上用到的sql语句，接口上方多了一个@Mapper注解。
3）@Param
mybatis中使用@param和不使用区别
1，使用@Param注解
当以下面的方式进行写SQL语句时：
@Select("select column from table where userid = #{userid} ")
public int selectColumn(int userid);
当你使用了使用@Param注解来声明参数时，如果使用 #{} 或 ${} 的方式都可以。
@Select("select column from table where userid = ${userid} ")
public int selectColumn(@Param("userid") int userid);
当你不使用@Param注解来声明参数时，必须使用使用 #{}方式。如果使用 ${} 的方式，会报错。
@Select("select column from table where userid = ${userid} ")
public int selectColumn(@Param("userid") int userid);
2，不使用@Param注解
不使用@Param注解时，参数只能有一个，并且是Javabean。在SQL语句里可以引用JavaBean的属性，而且只能引用JavaBean的属性。
// 这里id是user的属性
@Select("SELECT * from Table where id = ${id}")
Enchashment selectUserById(User user);
5.2 LoginTicketDAO
@Mapper
public interface LoginTicketDAO {
String TABLE_NAME = "login_ticket";
String INSERT_FIELDS = " user_id, expired, status, ticket ";
String SELECT_FIELDS = " id, " + INSERT_FIELDS;
@Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
") values (#{userId},#{expired},#{status},#{ticket})"})
int addTicket(LoginTicket ticket);
@Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME, " where ticket=#{ticket}"})
LoginTicket selectByTicket(String ticket);
@Update({"update ", TABLE_NAME, " set status=#{status} where ticket=#{ticket}"})
void updateStatus(@Param("ticket") String ticket, @Param("status") int status);
}
5.3 MessageDAO
@Mapper
public interface MessageDAO {
String TABLE_NAME = " message ";
String INSERT_FIELDS = " from_id, to_id, content, has_read, conversation_id, created_date ";
String SELECT_FIELDS = " id, " + INSERT_FIELDS;
@Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
") values (#{fromId},#{toId},#{content},#{hasRead},#{conversationId},#{createdDate})"})
int addMessage(Message message);
@Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME, " where conversation_id=#{conversationId} order by id desc limit #{offset}, #{limit}"})
List<Message> getConversationDetail(@Param("conversationId") String conversationId,
@Param("offset") int offset, @Param("limit") int limit);
@Select({"select count(id) from ", TABLE_NAME, " where has_read=0 and to_id=#{userId} and conversation_id=#{conversationId}"})
int getConvesationUnreadCount(@Param("userId") int userId, @Param("conversationId") String conversationId);
@Select({"select ", INSERT_FIELDS, " ,count(id) as id from ( select * from ", TABLE_NAME, " where from_id=#{userId} or to_id=#{userId} order by id desc) tt group by conversation_id order by created_date desc limit #{offset}, #{limit}"})
List<Message> getConversationList(@Param("userId") int userId,
@Param("offset") int offset, @Param("limit") int limit);
}
5.4 NewsDAO
@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);
List<News> selectByUserIdAndOffset(@Param("userId") int userId, @Param("offset") int offset,
@Param("limit") int limit);
}
int addNews(News news); 发布资讯
News selectById(int id); 点击详情页时根据newsId查询资讯
int updateCommentCount(@Param("id") int id, @Param("commentCount") int commentCount); 资讯详情页时改评论数量
List<News> selectByUserIdAndOffset(@Param("userId") int userId, @Param("offset") int offset, @Param("limit") int limit); 点击具体用户头像可查询该用户发布的所有资讯。
5.5 UserDAO
@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);
@Delete({"delete from ", TABLE_NAME, " where id=#{id}"})
void deleteById(int id);
}
7. model
model
7.1 Comment
通过entityId（如对news进行评论entityId就是newsId ）和entityType的实时变化组合，可以代表对不同的东西进行评论。
public class Comment {
private int id;
private int userId;
private int entityId;
private int entityType;
private String content;
private Date createdDate;
private int status;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public int getEntityId() {
return entityId;
}
public void setEntityId(int entityId) {
this.entityId = entityId;
}
public int getEntityType() {
return entityType;
}
public void setEntityType(int entityType) {
this.entityType = entityType;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getCreatedDate() {
return createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
}
7.2 EntityType
public class EntityType {
public static int ENTITY_NEWS = 1;
public static int ENTITY_COMMENT = 2;
}
7.3 HostHolder
结合PassportInterceptor
用来持有用户，表示当前的用户是谁。
@Component
public class HostHolder {
//多线程访问的，要注意多线程安全
private static ThreadLocal<User> users = new ThreadLocal<User>();
public User getUser() {
return users.get();
}
public void setUser(User user) {
users.set(user);
}
public void clear() {
users.remove();
}
}
7.4 LoginTicket
public class LoginTicket {
private int id;
private int userId;
private Date expired;
private int status;// 0有效，1无效
private String ticket;
public String getTicket() {
return ticket;
}
public void setTicket(String ticket) {
this.ticket = ticket;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public Date getExpired() {
return expired;
}
public void setExpired(Date expired) {
this.expired = expired;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
}
7.5 Message
public class Message {
private int id;
private int fromId;
private int toId;
private String content;
private Date createdDate;
private int hasRead;
private String conversationId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getFromId() {
return fromId;
}
public void setFromId(int fromId) {
this.fromId = fromId;
}
public int getToId() {
return toId;
}
public void setToId(int toId) {
this.toId = toId;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getCreatedDate() {
return createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
public int getHasRead() {
return hasRead;
}
public void setHasRead(int hasRead) {
this.hasRead = hasRead;
}
public String getConversationId() {
if (fromId < toId) {
return String.format("%d_%d", fromId, toId);
}
return String.format("%d_%d", toId, fromId);
}
}
7.6 News
public class News {
private int id;
private String title;
private String link;
private String image;
private int likeCount;
private int commentCount;
private Date createdDate;
private int userId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getLink() {
return link;
}
public void setLink(String link) {
this.link = link;
}
public String getImage() {
return image;
}
public void setImage(String image) {
this.image = image;
}
public int getLikeCount() {
return likeCount;
}
public void setLikeCount(int likeCount) {
this.likeCount = likeCount;
}
public int getCommentCount() {
return commentCount;
}
public void setCommentCount(int commentCount) {
this.commentCount = commentCount;
}
public Date getCreatedDate() {
return createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
}
7.7 User
public class User {
private int id;
private String name;
private String password;
private String salt;
private String headUrl;
public User() {
}
public User(String name) {
this.name = name;
this.password = "";
this.salt = "";
this.headUrl = "";
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public String getHeadUrl() {
return headUrl;
}
public void setHeadUrl(String headUrl) {
this.headUrl = headUrl;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
}
7.8 ViewObject
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);
}
}
sql
数据库
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT '',
`password` varchar(128) NOT NULL DEFAULT '',
`salt` varchar(32) NOT NULL DEFAULT '',
`head_url` varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(128) NOT NULL DEFAULT '',
`link` varchar(256) NOT NULL DEFAULT '',
`image` varchar(256) NOT NULL DEFAULT '',
`like_count` int NOT NULL,
`comment_count` int NOT NULL,
`created_date` datetime NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `login_ticket`;
CREATE TABLE `login_ticket` (
`id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT NOT NULL,
`ticket` VARCHAR(45) NOT NULL,
`expired` DATETIME NOT NULL,
`status` INT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE INDEX `ticket_UNIQUE` (`ticket` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`id` INT NOT NULL AUTO_INCREMENT,
`content` TEXT NOT NULL,
`user_id` INT NOT NULL,
`entity_id` INT NOT NULL,
`entity_type` INT NOT NULL,
`created_date` DATETIME NOT NULL,
`status` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
INDEX `entity_index` (`entity_id` ASC, `entity_type` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
`id` INT NOT NULL AUTO_INCREMENT,
`from_id` INT NULL,
`to_id` INT NULL,
`content` TEXT NULL,
`created_date` DATETIME NULL,
`has_read` INT NULL,
`conversation_id` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
INDEX `conversation_index` (`conversation_id` ASC),
INDEX `created_date` (`created_date` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
