第一部分------常用动态sql语句(分页,模糊查询 用框架)
1.UserMapper.xml (映射文件)
<!--用户列表 分页查询-->
<select id="findUserPage" resultType="cn.kgc.pojo.User">
SELECT * FROM smbms_user
<trim prefix="where" prefixOverrides="and">
<if test="userName!=null and userName!=''">
and userName like concat('%',#{userName},'%')
</if>
<if test="userRole!=null and userRole!=''">
and userRole=#{userRole}
</if>
</trim>
LIMIT #{pageNo},#{pageSize}
</select>
<!--总数-->
<select id="count" resultType="java.lang.Integer">
SELECT COUNT(*)FROM smbms_user
<trim prefix="where" prefixOverrides="and">
<if test="userName!=null and userName!=''">
and userName like concat('%',#{userName},'%')
</if>
<if test="userRole!=null and userRole!=''">
and userRole=#{userRole}
</if>
</trim>
</select>
2.UserServiceImpl.java (实现类)
动态sql语句的分页,service实现方法
//查询 分页 用户列表
public Map findUserPage(int pageNo,String userName,Long userRole) {
int count = userMapper.count(userName,userRole);//总数
List<User> userPageList = userMapper.findUserPage((pageNo - 1) * 3, 3,userName,userRole);//分页查询
int countPage=count%3==0 ? count/3 : (count/3)+1; //三目运算 计算总页数
//放进Map
Map<String,Object> userMap =new HashMap<>();
userMap.put("count",count);
userMap.put("userPageList",userPageList);
userMap.put("countPage",countPage);
userMap.put("pageNo",pageNo);
return userMap;
}
第二部分------三层架构 分页 (未用框架)
1.ServiceImpl.java(实现类)
//根据title 模糊查询 分页展示
public Page findNewsDetaiBytitle(String title, int pageNo) throws SQLException {
Page page=new Page();
page.setPageNo(pageNo);//当前页
page.setCountNo(newsDetailDao.NewsDetaiCount(title));//信息总数
page.setListNewsDetail(newsDetailDao.findNewsDetaiBytitle(title,pageNo));//信息集合
//总页数在page页面计算的
return page;
}
2.DaoImpl(实现类)
//根据title 模糊查询 分页展示
public List<NewsDetail> findNewsDetaiBytitle(String title, int pageNo) throws SQLException {
List<NewsDetail>list=new ArrayList<>();
Page page=new Page();
int pageSize = page.getPageSize();
String sql;
ResultSet rs;
if(title.equals("")){
sql="SELECT id,title,summary,author,createdate FROM news_detail LIMIT ?,?";
Object[]params={(pageNo-1)*pageSize,pageSize};
rs = executeQuery(sql, params);
}else {
sql="SELECT id,title,summary,author,createdate FROM news_detail WHERE title LIKE ? LIMIT ?,?";
Object[]params={"%"+title+"%",(pageNo-1)*pageSize,pageSize};
rs = executeQuery(sql, params);
}
while(rs.next()){
String id = rs.getString("id");
String title2 = rs.getString("title");
String summary = rs.getString("summary");
String author = rs.getString("author");
String createdate = rs.getString("createdate");
NewsDetail newsDetail=new NewsDetail();
newsDetail.setId(Integer.parseInt(id));
newsDetail.setTitle(title2);
newsDetail.setSummary(summary);
newsDetail.setAuthor(author);
newsDetail.setCreatedate(Timestamp.valueOf(createdate));
list.add(newsDetail);
}
return list;
}
//总数
public int NewsDetaiCount(String title) throws SQLException {
String sql;
ResultSet rs;
if(title.equals("")){
sql="SELECT COUNT(1) FROM news_detail";
Object[]params={};
rs = executeQuery(sql, params);
}else {
sql="SELECT COUNT(1) FROM news_detail WHERE title LIKE ?";
Object[]params={"%"+title+"%"};
rs = executeQuery(sql, params);
}
int totle=0;
while(rs.next()){
totle = rs.getInt(1);
}
return totle;
}
第三部分 多字段相等,联表查询
SELECT *,
(SELECT `cityName` FROM city WHERE id = #{departureCity})as cityName,
(SELECT `cityName` FROM city WHERE id = #{arrivalCity}) as cityName2
FROM flight
WHERE departureCity =#{departureCity} AND arrivalCity =#{arrivalCity}
LIMIT #{pageNo},#{pageSize}