精华内容
下载资源
问答
  • 简单Java 多表联查分页显示(初)

    千次阅读 2016-04-16 18:00:29
    Java 多表联查分页显示 操作步骤: 建立数据库 使用Servlet 创建Java项目 编写代码 1、数据库 选用 Oracle代码附上:drop table Score; drop table Student; drop table Subject; drop sequence seq_Student; drop ...

                Java 多表联查分页显示



    操作步骤:

    1. 建立数据库
    2. 使用Servlet 创建Java项目
    3. 编写代码

    1、数据库 选用 Oracle

    代码附上:

    drop table Score;
    drop table Student;
    drop table Subject;
    drop sequence seq_Student;
    drop sequence seq_Score;
    drop sequence seq_Subject;
    
    
    create table Student
    (
        id int primary key,
        name nvarchar2(50) not null,
        age int not null,
        hobby nvarchar2(50) not null
    );
    
    
    
    create table subject
    (
        id int primary key,
        name nvarchar2(20) not null
    );
    
    create table Score
    (
        id int primary key,
        score number(11,2) not null,
        stuId int references Student(id) not null,
        subId int references Subject not null
    );
    
    
    create sequence seq_Student;
    create sequence seq_Score;
    create sequence seq_Subject;
    
    
    insert into Student values(seq_student.nextval,'张三',20,'篮球');
    insert into Student values(seq_student.nextval,'李四',30,'篮球');
    insert into Student values(seq_student.nextval,'王五',40,'篮球');
    insert into Student values(seq_student.nextval,'赵六',50,'篮球');
    commit;
    
    insert into Subject values(seq_Subject.nextval,'语文');
    insert into Subject values(seq_Subject.nextval,'数学');
    insert into Subject values(seq_Subject.nextval,'英语');
    insert into Subject values(seq_Subject.nextval,'java');
    commit;
    
    
    insert into Score values(seq_score.nextval,96.5,1,1);
    insert into Score values(seq_score.nextval,96.5,2,1);
    insert into Score values(seq_score.nextval,96.5,3,4);
    insert into Score values(seq_score.nextval,96.5,4,4);
    commit;
    
    
    
    
    
    
    select * from Student;
    select * from Score;
    select * from Subject;
    
    
    
    
    
    select stu.*,sco.score,sub.name from (select rownum as rn,s.* from (select * from Student order by id desc) s ) stu 
    left join Score sco on stu.id = sco.id 
    left join Subject sub on sub.id = sco.subid where stu.rn>0 and stu.rn<=10

    2、页面展示:index.jsp页面

    <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
    <html>
      <head>
        <title>My JSP 'index.jsp' starting page</title>
      </head>
    
      <body>
        <form action="">
            <table align="center" border="1" style="width: 400px; border-collapse: collapse; text-align: center;">
                <tr>
                    <th>编号</th>
                    <th>姓名</th>
                    <th>年龄</th>
                    <th>爱好</th>
                    <th>成绩</th>
                    <th>科目</th>
                </tr>
                <c:if test="${empty list}">
                    <c:redirect url="StudentInfoServlet.do"></c:redirect>
                </c:if>
                <c:forEach var="list" items="${sessionScope.list}">
                    <tr>
                        <td>${list.id }</td>
                        <td>${list.name }</td>
                        <td>${list.age }</td>
                        <td>${list.hobby }</td>
                        <td>${list.score }</td>
                        <td>${list.subject }</td>
                    </tr>
                </c:forEach>
                <tr>
                    <td colspan="6">
                        <a href="StudentInfoServlet.do?page=1">首页</a>
                        <a href="StudentInfoServlet.do?page=${sessionScope.top }">上一页</a>
                        <a href="StudentInfoServlet.do?page=${sessionScope.bottom }">下一页</a>
                        <a href="StudentInfoServlet.do?page=${sessionScope.count }">末页</a>
                    </td>
                </tr>
            </table>
    
        </form>
      </body>
    </html>
    

    3、建立Servlet    StudentInfoServlet.java类

    package servlet;
    
    import java.io.IOException;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import dao.StudentInfoDao;
    import dao.impl.StudentInfoDaoImpl;
    import entity.StudentInfo;
    
    public class StudentInfoServlet extends HttpServlet {
    
        private static final long serialVersionUID = 1L;
    
        public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            this.doPost(request, response);
        }
    
        public void doPost(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
    
            response.setContentType("text/html;charset= utf-8");
            request.setCharacterEncoding("utf-8");
    
            StudentInfoDao stu = new StudentInfoDaoImpl();
            int count = stu.count_ye();
    
            Integer dang_ye = null;
            if (request.getParameter("page") != null) {
                dang_ye = Integer.parseInt(request.getParameter("page"));
            } else {
                dang_ye = 1;
            }
            List<StudentInfo> list = stu.getAll(dang_ye);
    
            int top = 1;
            int bottom = count;
            if (dang_ye != 1) {
                top = dang_ye - 1;
            }
            if (dang_ye != count) {
                bottom = dang_ye + 1;
            }
            request.getSession().setAttribute("top", top);
            request.getSession().setAttribute("bottom", bottom);
            request.getSession().setAttribute("count", count);
            request.getSession().setAttribute("list", list);
            response.sendRedirect("index.jsp");
        }
    
    }
    

    4、建立Dao层区

    StudentInfoDao.java接口

    package dao;
    
    import java.util.List;
    
    import entity.StudentInfo;
    
    public interface StudentInfoDao {
        public List<StudentInfo> getAll(int page);
    
        public int count_ye();
    }
    

    实现类 StudentInfoDaoImpl.java接口实现类

    package dao.impl;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import dao.BaseDao;
    import dao.StudentInfoDao;
    import entity.StudentInfo;
    
    public class StudentInfoDaoImpl extends BaseDao implements StudentInfoDao {
    
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
    
        public int count_ye() {
            int count = 0;
            int ye = 0;
            String sql = "select count(*) from Student";
            try {
                conn = this.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                if (rs.next()) {
                    count = rs.getInt(1);
                }
                ye = count / 10;
                if (count % 10 != 0) {
                    ye++;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return ye;
        }
    
        public List<StudentInfo> getAll(int page) {
            List<StudentInfo> list = new ArrayList<StudentInfo>();
            String sql = "select stu.*,sco.score as scoid,sub.name as subid from (select rownum as rn,s.* from (select * from Student order by id desc) s ) stu left join Score sco on stu.id = sco.id left join Subject sub on sub.id = sco.subid where stu.rn>? and stu.rn<=?";
            int top = (page - 1) * 10;
            int bottom = page * 10;
            try {
                conn = this.getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, top);
                ps.setInt(2, bottom);
                rs = ps.executeQuery();
                while (rs.next()) {
                    StudentInfo stu = new StudentInfo(rs.getInt("id"),
                            rs.getString("name"), rs.getInt("age"), rs.getString("hobby"), rs
                                    .getInt("scoid"), rs.getString("subid"));
                    list.add(stu);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                this.getClose(conn, ps, rs);
            }
            return list;
        }
    
    }
    

    BaseDao.java

    package dao;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class BaseDao {
        private static final String driver = "oracle.jdbc.driver.OracleDriver";
        private static final String url = "jdbc:oracle:thin:@localhost:1521:accp11g";
        private static final String name = "system";
        private static final String pwd = "baiyu";
    
        public Connection getConnection() {
            Connection conn = null;
    
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, name, pwd);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
    
        public void getClose(Connection conn, Statement ps, ResultSet rs) {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    

    实体类 StudentInfo.java

    package entity;
    
    import java.io.Serializable;
    
    public class StudentInfo implements Serializable {
        private static final long serialVersionUID = 1L;
        private int id;
        private String name;
        private int age;
        private String hobby;
        private int score;
        private String subject;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public int getAge() {
            return age;
        }
    
        public void setAge(int age) {
            this.age = age;
        }
    
        public String getHobby() {
            return hobby;
        }
    
        public void setHobby(String hobby) {
            this.hobby = hobby;
        }
    
        public int getScore() {
            return score;
        }
    
        public void setScore(int score) {
            this.score = score;
        }
    
        public String getSubject() {
            return subject;
        }
    
        public void setSubject(String subject) {
            this.subject = subject;
        }
    
        public StudentInfo(int id, String name, int age, String hobby, int score,
                String subject) {
            super();
            this.id = id;
            this.name = name;
            this.age = age;
            this.hobby = hobby;
            this.score = score;
            this.subject = subject;
        }
    
        public StudentInfo(String name, int age, String hobby, int score,
                String subject) {
            super();
            this.name = name;
            this.age = age;
            this.hobby = hobby;
            this.score = score;
            this.subject = subject;
        }
    
        public StudentInfo() {
            super();
        }
    
    }
    
    展开全文
  • 第一个是必须写一个关于你的多表联查的实体类,剩下的就是普通的写法,就是把你的表写成单标查询。 第二个是你首先得知道你查的是那些数据,要放到哪里按什么位置摆放,其实表查询就是把你需要的数据放道你自己...

    大家好我是曜耀。

    今天将一下关于多表联查的技巧。

    首先你要命白多表联查有俩个解决的方案一个是最容易但是代码量很大,一个是难的但是代码量是很少的。第一个是必须写一个关于你的多表联查的实体类,剩下的就是普通的写法,就是把你的多表写成单标查询。

    第二个是你首先得知道你查的是那些数据,要放到哪里按什么位置摆放,其实多表查询就是把你需要的数据放道你自己创建的一个复合你的要求的表,因此思路就明白了。剩下的就是追备好SQL语句和你放在什么的里面,其他的就按照以前的写就行。注意的是拿到的数据放到map里因为map的类型是Object父类,不管你的数据是什么类型都属于她的子类,所以在这不会出现一些数据类型不符合的错误。其次在把map放进map类型的list里,这样你就可以很快的遍历你拿到的数据。

    其中代码曜耀之前发过,今天主要是讲一下其中的思路。对于我们程序员来说,代码是小事,编程思维是大事,你只有懂得了编程思维,剩下的一步一步就能完成,即使出错了,你还可以在网上找解决的方法,不然你都不知道那出错了,就不会有目的的去找解决的方法。

    我是曜耀,下次见。

    展开全文
  • JAVA erp项目 多表联查

    2018-01-22 09:55:16
    ![图片说明](https://img-ask.csdn.net/upload/201801/22/1516614804_895354.png) 一张销售(销售),两张副,分别为普通销售和剪板销售,有主外键关联,怎么获得如图所示效果
  • java通用dao方法(优化多表联查

    千次阅读 2018-08-03 13:47:33
    本方法只介绍一个通用方法,本方法可应用在多表联查,可以不用ji建javaBean也不需要把外键变成对象 1.准备一个pet实体类和DBlist工具类 package com.pet.entity; import java.io.Serializable; public ...

    本方法只介绍一个通用方法,本方法可应用在多表联查,可以不用多ji建javaBean也不需要把外键变成对象

    1.准备一个pet实体类和DBlist工具类

    
    package com.pet.entity;
     
    import java.io.Serializable;
     
    public class Pet implements Serializable {
     
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 502943061295576054L;
    	
    	private Integer pet_id;
    	private String pet_name;
    	private Integer sort_id;
    	private String pet_sex;
    	private String pet_introduce;
    	private Integer user_id;
    	private Integer pet_informationPhoto_power;
    	private Integer pet_informationPhoto_ability;
    	private Integer pet_informationPhoto_height;
    	private Integer pet_informationPhoto_integral;
    	public Pet(Integer pet_id, String pet_name, Integer sort_id, String pet_sex, String pet_introduce, Integer user_id,
    			Integer pet_informationPhoto_power, Integer pet_informationPhoto_ability,
    			Integer pet_informationPhoto_height, Integer pet_informationPhoto_integral) {
    		super();
    		this.pet_id = pet_id;
    		this.pet_name = pet_name;
    		this.sort_id = sort_id;
    		this.pet_sex = pet_sex;
    		this.pet_introduce = pet_introduce;
    		this.user_id = user_id;
    		this.pet_informationPhoto_power = pet_informationPhoto_power;
    		this.pet_informationPhoto_ability = pet_informationPhoto_ability;
    		this.pet_informationPhoto_height = pet_informationPhoto_height;
    		this.pet_informationPhoto_integral = pet_informationPhoto_integral;
    	}
    	public Pet(String pet_name, Integer sort_id, String pet_sex, String pet_introduce, Integer user_id,
    			Integer pet_informationPhoto_power, Integer pet_informationPhoto_ability,
    			Integer pet_informationPhoto_height, Integer pet_informationPhoto_integral) {
    		super();
    		this.pet_name = pet_name;
    		this.sort_id = sort_id;
    		this.pet_sex = pet_sex;
    		this.pet_introduce = pet_introduce;
    		this.user_id = user_id;
    		this.pet_informationPhoto_power = pet_informationPhoto_power;
    		this.pet_informationPhoto_ability = pet_informationPhoto_ability;
    		this.pet_informationPhoto_height = pet_informationPhoto_height;
    		this.pet_informationPhoto_integral = pet_informationPhoto_integral;
    	}
    	public Pet() {
    		super();
    	}
    	public Integer getPet_id() {
    		return pet_id;
    	}
    	public void setPet_id(Integer pet_id) {
    		this.pet_id = pet_id;
    	}
    	public String getPet_name() {
    		return pet_name;
    	}
    	public void setPet_name(String pet_name) {
    		this.pet_name = pet_name;
    	}
    	public Integer getSort_id() {
    		return sort_id;
    	}
    	public void setSort_id(Integer sort_id) {
    		this.sort_id = sort_id;
    	}
    	public String getPet_sex() {
    		return pet_sex;
    	}
    	public void setPet_sex(String pet_sex) {
    		this.pet_sex = pet_sex;
    	}
    	public String getPet_introduce() {
    		return pet_introduce;
    	}
    	public void setPet_introduce(String pet_introduce) {
    		this.pet_introduce = pet_introduce;
    	}
    	public Integer getUser_id() {
    		return user_id;
    	}
    	public void setUser_id(Integer user_id) {
    		this.user_id = user_id;
    	}
    	public Integer getPet_informationPhoto_power() {
    		return pet_informationPhoto_power;
    	}
    	public void setPet_informationPhoto_power(Integer pet_informationPhoto_power) {
    		this.pet_informationPhoto_power = pet_informationPhoto_power;
    	}
    	public Integer getPet_informationPhoto_ability() {
    		return pet_informationPhoto_ability;
    	}
    	public void setPet_informationPhoto_ability(Integer pet_informationPhoto_ability) {
    		this.pet_informationPhoto_ability = pet_informationPhoto_ability;
    	}
    	public Integer getPet_informationPhoto_height() {
    		return pet_informationPhoto_height;
    	}
    	public void setPet_informationPhoto_height(Integer pet_informationPhoto_height) {
    		this.pet_informationPhoto_height = pet_informationPhoto_height;
    	}
    	public Integer getPet_informationPhoto_integral() {
    		return pet_informationPhoto_integral;
    	}
    	public void setPet_informationPhoto_integral(Integer pet_informationPhoto_integral) {
    		this.pet_informationPhoto_integral = pet_informationPhoto_integral;
    	}
    	@Override
    	public String toString() {
    		return "Pet [pet_id=" + pet_id + ", pet_name=" + pet_name + ", sort_id=" + sort_id + ", pet_sex=" + pet_sex
    				+ ", pet_introduce=" + pet_introduce + ", user_id=" + user_id + ", pet_informationPhoto_power="
    				+ pet_informationPhoto_power + ", pet_informationPhoto_ability=" + pet_informationPhoto_ability
    				+ ", pet_informationPhoto_height=" + pet_informationPhoto_height + ", pet_informationPhoto_integral="
    				+ pet_informationPhoto_integral + "]";
    	}
    	
    	
     
    }

     

    package com.pet.util;
     
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
     
     
    public class DBlist {
     
    	
    	
    	public List<Map<String, Object>> convertList(ResultSet rs){
    		
    		//新建一个map list集合用于存放多条查询记录
    		List<Map<String, Object>> li = new ArrayList();
    		try {
    			//结果集(rs)的结构信息,比如字段数和字段名等
    			ResultSetMetaData rsmd = rs.getMetaData();
    			// 取得查询出来的字段个数
    			int columnCount = rsmd.getColumnCount();
    			//迭代rs
    			while(rs.next()) {
    				//新建一个Map集合,将查询出内容按照字段名:值得键值对形式存储在map集合中
    				Map<String, Object> rowDate = new HashMap<String,Object>();
    				//循环所有查询出的字段
    				for (int i = 1; i <=columnCount; i++) {
    					rowDate.put(rsmd.getColumnName(i), rs.getObject(i));
    					//获取第i个列名
    					//getColumnName(i)
    					//获取第i个对象的值
    					//getObject(i)
    				}
    				//存放到list集合中
    				li.add(rowDate);
    			}
    		} catch (Exception e) {
    			// TODO: handle exception
    			e.printStackTrace();
    		}finally {
    			
            try {
            	if (rs != null) {
                    rs.close();
    			}
    		} catch (Exception e2) {
    			// TODO: handle exception
    			e2.printStackTrace();
    		}
    		}
     
     
    		
    		return li;
    	}
    	
    	
    }

    2.写一个pageBean,这里面主要是分页操作

    package com.pet.util;
     
    import java.util.Map;
     
    import javax.servlet.http.HttpServletRequest;
     
    /**
     * 分页工具类
     * @author Administrator
     *2018年5月24日下午6:54:33
     */
    public class PageBean {
    	
    	private int page=1;					//当前页
    	private int rows=5;					//每页记录数
    	private int total=0;				//总记录数
    	
    	private boolean pagination=true ;	//是否分页
    	
    	private String url; 				//根目录+请求地址
    	
    	private Map<String, String[]> map;	//请求参数
    	
    	public PageBean() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	public PageBean(int page, boolean pagination) {
    		super();
    		this.page = page;
    		this.pagination = pagination;
    	}
    	
    	public PageBean(int page, int rows, int total, boolean pagination) {
    		super();
    		this.page = page;
    		this.rows = rows;
    		this.total = total;
    		this.pagination = pagination;
    	}
     
    	public int getPage() {
    		return page;
    	}
     
    	public void setPage(int page) {
    		this.page = page;
    	}
    	
    	//重载setPage
    	public void setPage(String page) {
    		if(page!=null&& !"".equals(page)) {
    			this.page = Integer.parseInt(page);
    		}	
    	}
     
    	public int getRows() {
    		return rows;
    	}
     
    	public void setRows(int rows) {
    		this.rows = rows;
    	}
    	
    	//重载setRows
    	public void setRows(String rows) {
    		if(rows!=null&& !rows.equals("")) {
    			this.rows = Integer.parseInt(rows);
    		}
    	}
     
    	public int getTotal() {
    		return total;
    	}
     
    	public void setTotal(int total) {
    		this.total = total;
    	}
    	//重载setTotal
    	public void setTotal(String total) {
    		if(total!=null&& !total.equals("")) {
    			this.total = Integer.parseInt(total);
    		}		
    	}
     
    	public boolean isPagination() {
    		return pagination;
    	}
     
    	public void setPagination(boolean pagination) {
    		this.pagination = pagination;
    	}
    	
    	//重载setPagination
    	public void setPagination(String pagination) {
    		if("false".equals(pagination) && pagination !=null) {
    			this.pagination = false;
    		}
    	}
    	
    	public String getUrl() {
    		return url;
    	}
    	public void setUrl(String url) {
    		this.url = url;
    	}
    	public Map<String, String[]> getMap() {
    		return map;
    	}
    	public void setMap(Map<String, String[]> map) {
    		this.map = map;
    	}
     
    	
    	
    	@Override
    	public String toString() {
    		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination
    				+ ", url=" + url + ", map=" + map + "]";
    	}
    	/**
    	 * 计算起始记录的下标
    	 * @return int
    	 */
    	public int getStartIndex() {
    		return (this.page-1)*this.rows;
    	}
    	
    	/**
    	 * 计算总页数
    	 * @return int
    	 */
    	public int getTotalPage() {
    		int totalPage=this.total/this.rows;
    		if(this.total%this.rows!=0) {
    			totalPage++;
    		}
    		return totalPage;
    	}
    	
    	/**
    	 * 计算上一页
    	 * @return int
    	 */
    	public int getPreviousPage() {
    		int PreviousPage=this.page-1;
    		if(PreviousPage<1) {
    			PreviousPage=1;
    		}
    		return PreviousPage;
    	}
    	
    	/**
    	 * 计算下一页
    	 * @return int
    	 */ 
    	public int getNextPage() {
    		int NextPage=this.page+1;
    		if(NextPage>this.getTotalPage()) {
    			NextPage=this.getTotalPage();
    		}
    		return NextPage;
    	}
    	
    	/**
    	 * 初始化PageBean
    	 * 实例化PageBean时,调用该方法重新为page,rows,pagination赋值和提交路径及参数(将上一次查询请求再发一次,只不过页码变了)
    	 * @param request
    	 */
    	public void initPageBean(HttpServletRequest request) {
    		//重新为page,rows,pagination赋值
    		this.setPage(request.getParameter("page"));
    		this.setRows(request.getParameter("rows"));
    		this.setPagination(request.getParameter("pagination"));
    		//提交路径及参数(将上一次查询请求再发一次,只不过页码变了)
    		this.url=request.getContextPath()+request.getServletPath();
    		this.map=request.getParameterMap();
    	}
    	
     
    }

    3.写一个Basedao类,CRUD的通用方法

    
    package com.pet.dao;
     
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;
     
    import com.pet.util.DBHelper;
    import com.pet.util.PageBean;
     
     
     
    /**
     * CURD基础类
     * @author Administrator
     *2018年5月24日下午5:55:31
     */
    public class BaseDao<E> {
     
     
    /**
    	 * 查询通用方法,支持模糊查询
    	 * @param sql
    	 * @param params
    	 * @param callBack2
    	 * @return List<Map<String, Object>>
    	 */
    	public List<Map<String, Object>> executeQuery3(String sql,PageBean pageBean,CallBack2<E> callBack){
    		Connection con=null;
    		PreparedStatement ps=null;
    		ResultSet rs=null;
    		try {
    			con=DBHelper.getcon();
    			//第一次查满足条件的总记录数
    			if(pageBean!=null && pageBean.isPagination()) {
    				//将普通的sql转换成查总记录数的getCountSql()
    				String CountSql=this.getCountSql(sql);
    				ps=con.prepareStatement(CountSql);
    				rs=ps.executeQuery();
    				if(rs.next()) {
    					Object obj = rs.getObject(1);
    					//取出结果集中的总记录数,赋给pageBean对象的总记录数属性
    					pageBean.setTotal(obj.toString());
    				}
    				//关闭资源
    				DBHelper.close(null, ps, rs);
    			}
    			//第二次查指定页码并满足条件的记录
    			if (null != pageBean && pageBean.isPagination()) {
    				//求总页数的方法getPageSql()
    				sql = this.getPageSql(sql, pageBean);
    			}
    			ps=con.prepareStatement(sql);
    			rs=ps.executeQuery();
    			return callBack.forEach(rs);
    		} catch (Exception e) {
    			// TODO: handle exception
    			e.printStackTrace();
    		}finally {
    			DBHelper.close(con, ps,rs);
    		}
    		
    		return null;
    	}
     
     
    /**
    	 * 将普通的sql转换成查总记录数的getCountSql()
    	 * @param sql
    	 * @return String
    	 */
    	private String getCountSql(String sql) {
    		String CountSql="select count(*) from ("+sql+") a";
    		return CountSql;
    	}
    	
    	/**
    	 * 求总页数的方法getPageSql()
    	 * @param sql
    	 * @param pageBean
    	 * @return String
    	 */
    	private String getPageSql(String sql,PageBean pageBean) {
    		String PageSql=sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows();
    		return PageSql;
    	}
    }

    然后就是使用了,我们先继承Basedao类

    public class PetDaoImpl extends BaseDao<Pet> implements IPetDao {
     
    @SuppressWarnings({ "unchecked", "rawtypes" })
    	public List<Map<String, Object>> getPetById2(Pet p,PageBean pageBean) {
    		// TODO Auto-generated method stub
    		String sql = "select a.*,b.user_name,b.user_pwd,b.user_email from t_pet a,t_user b where a.user_id=b.user_id";
    		return new BaseDao().executeQuery3(sql, pageBean, new BaseDao.CallBack2<Object>() {
     
    			public List<Map<String, Object>> forEach(ResultSet rs) throws SQLException {
    				// TODO Auto-generated method stub
    				List<Map<String, Object>> li = null;
    				DBlist db = new DBlist();
    				li = db.convertList(rs);
    				return li;
    			}
    		});
    	}
    }

     测试

    public class Dao {
     
    	@Test
    	public void test() {
    		IPetDao ipd = new PetDaoImpl();
    		Pet p = new Pet();
    		PageBean pageBean = new PageBean();
    		List<Map<String, Object>> petById2 = ipd.getPetById2(p,pageBean);
    		System.out.println(petById2);
    	}
    }

     注意:因为使用的是Map集合,所以你数据库查询的字段名不能有重复的的,所以多表联查时要把字段名给打出来

    原文地址:https://blog.csdn.net/qq_40132161/article/details/81383783

     

    展开全文
  • java操作mongodb之多表联查($lookup)

    千次阅读 2019-05-20 16:28:56
    最近在开发的过程中,一个列表的查询,涉及到了表的关联查询,由于持久层使用的是mongodb,对这个非关系型数据使用的不是很,所以在实现此功能的过程中出现了不少问题,现在此做记录,一...多表联查的查询语句:...

    最近在开发的过程中,一个列表的查询,涉及到了多表的关联查询,由于持久层使用的是mongodb,对这个非关系型数据使用的不是很多,所以在实现此功能的过程中出现了不少问题,现在此做记录,一为加深自己的理解,以后遇到此类问题可以快速的解决,二为遇到同样问题的小伙伴提供一点小小的帮助。

    全文分为两部分:

    1. 使用robo3t编写多表关系的查询语句
    2. 将编写的查询语句整合到java项目

    多表联查的查询语句:

    此处使用的为mongodb的robo3t可视化工具,先说下需求:从A(假如说是日志表)表中查询出符合条件的数据,根据A表中符合条件数据查询B(假如说是信息表)表中的数据,此处也可以将B表的查询条件加入进来(类型于关系型数据库中的临时表)

    mongo查询语句:

    db.getCollection('A').aggregate([
            {
            $lookup:{
                        from:'B',
                        localField:'userid',
                        foreignField:'userid',
                        as:'userinfo'
                    }
            }, 
            {
             $unwind:'$userrole'//把一个数组展成多个,就比如说按多表连查的userrole数组中有10数据,那么用$unwind将把一条带数组的数据分成10条,这10条数据除了userrole不同之外,其它数据都是相同的,就类似于一个展开操作
            },
            {
             $match:{'username':'zhangsan'}
            },
            {
              $group:{
                        _id:{
                                userid:'$userid',//这个属性必须是要A表中有的
                                userrole:'$userrole.roleid',//A表中有一个集合,里面存放的对象有一个名为roleid的属性
                            },
                        operateTime:{
                                $last:'$operateTime'//取A表操作时间最后一条件数
                            }
                        info:{
                                $first:'$userinfo'//因为数组的扩展,造成了大量的重复数据(只有userrole不同),$first是只取最新的一条
                            }
                    }
            },
            {
                $sort:{'operateTime':-1}//操作时间倒序,-1:倒序,1:升序
            },
            {
                $skip:0//跳过几条数据,也就是从第几条数据开始取
            },
            {
                $limit:5//每页显示几条数据
            }
    ]);

    java代码整合查询语句

    //定义分组字段
    String[] groupIds = new String[] {"$userid","$userrole.roleid"};
    //定义查询条件
    Criteria criteria = new Criteria();
    //相当于where username = "zhangsan"
    criteria.and("username").is("zhangsan");
    //相当于 where age not in("15","20")
    criteria.and("age").nin("15","20");
    //in操作对应的语句
    //criteria.and("").in();
    //定义排序条件
    Sort sort = new Sort(Direction.DESC,"operateTime");
    //联合查询总条数,分页用
    Aggregation aggregationCount = Aggregation.newAggregation(
        Aggregation.match(criteria);//查询条件
        Aggregation.group(groupIds);//分组字段
    );
    //联合查询条件
    Aggregation newAggregation = Aggregation.newAggregation(
        Aggregation.lookup('B','userid','userid','userinfo'),//从表名,主表联接字段,从表联接字段,别名
        Aggregation.unwind("$userrole"),
        Aggregation.match(criteria),
        Aggregation.group(groupIds)
            .last("$operateTime").as("operateTime")//取值,起别名
            .first("$userinfo").as("info"),
        Aggregation.sort(sort),
        Aggregation.skip(pageSize*(pageNumber-1L)),//Long类型的参数
        Aggregation.limit(pageSize)
    );
    //查询
    AggregationResults<BasicDBObject> aggregate = mongoTemplate.aggregate(
        newAggregation ,"A",BasicDBObject.class//A表,是查询的主表
    );
    int count = mongoTemplate.aggregate(aggregationCount ,"A",BasicDBObject.class).getMappedResults().size();
    //组装分页对象
    Page<BasicDBObject> pager = new Page<>(aggregate.getMappedResults(),count,pageSize,pageNumber,page*(pageNumber-1));
    //对象转换
    将BasicDBObject转换成前面需要的类型.....
    
    
    

    行文时间短促,疏漏之处在所难免,欢迎大家批评指定。

    展开全文
  • 如题,*Mapper.xml 里面我是这么写的, ``` ...-- ...还有两个实体类就是Product和Brand,不说了,现在死活就是不好使,求各位大神帮忙看看
  • mybatis多表联查

    2018-11-20 09:45:04
    这里是修真院后端小课堂,每篇分享文从 ...本篇分享的是:【mybatis多表联查】 (1)背景介绍 在客观世界中,对象很少是孤独存在的,如班级与学生之间的关系,学生与课程之间的关系,它们的实例之间可以互...
  • 版权声明:本文为博主原创文章,未经博主同意不得转载 以Oracle数据库为例 有 表名:AAA 字段: id name tag ...select 列名 from 主 inner join 从 on 主和从关系 注意: (1)必须要有on语句 (2)关系必...
  • Mybatis多表联查

    2021-03-29 09:24:47
    Mybatis多表联查1、表间关系:对一的查询第一种方式:在sql语句中联表查询第二种方式:两次查询2、一对第一种方式:sql语句多表联查第二种方式:分开查询 1、表间关系:对一的查询 创建 班级表和教师表 ...
  • spring data jpa 实现条件复杂查询及多表联查

    万次阅读 多人点赞 2018-06-19 16:32:29
    今天写一下端午这两天琢磨的条件查询,还有多表联查。maven依赖啊,配置,继承写法等知识点不展开说了,之前写过一篇文章: spring boot 配置及使用 spring data jpa这里说一下更新的地方:JPA的配置#############...
  • SQL 会创建多表多表的关系 1.多表之间的关系如何来维护 添加外键约束: foreign key alter table product add foreign key(cno) references category(cid); 从分类中,删除分类为5信息, delete from category...
  • 如何避免多表联查

    2021-04-25 23:37:33
    跨入21世纪,数据呈现了暴增的阶段,以往数据结果的获取方式都是以多表联查为主而获得的结果,可是在大数据的时代,数据暴增的情况下,再如此往复的使用多表联查,势必会对性能造成一定的影响,那么如何才能够避免...
  • 【SQL从一点一滴分析系列文章】为实际开发中的点点滴滴的总结,从最最简单的SQL 查询 到 综合分析查询在...要交来自的数据组织到一起,就像是一个结果集叠加到另一个上页面一样,例如有两个表表一 用户 t_us...
  • 多表联查分页显示

    2016-04-16 18:11:16
    简单的servlet多变联查
  • I have several tables with different numbers and types of columns, and a single column in common.+--------+---------+------------+-------------+| person | beardID | beardStyle | beardLength |+--------...
  • Mybatis实现多表联查

    万次阅读 多人点赞 2019-05-09 17:53:25
    一、Mybatis实现多表联查询 1、Mybatis实现多表联查询方式 业务装配对两个写单独的sql语句,在业务(service)把查询结果进行联合。 使用Auto Mapping特性,在实现两个联合查询时通过别名完成自动映射。 ...
  • Mybatis中实现了对数据库中的数据进行封装,那么进行表查询时就会遇到查询结果不只是一个对象的数据,有可能是个对象的混合,可以使用<resultMap>...多表联查sql语句 实体类entity S...
  • 一个命令多表联查,利用sql可以实现一条语句可以多表联查的特点,创建两个表的对应类型,使用private UserExtra userExtra;与注解@Autowired使得UserExtra与UserInfo两张表的绑定。对象的类型采用包括了userExtra的...
  • SpringBoot+MyBatisPlus多表联查 文章目录SpringBoot+MyBatisPlus多表联查一、前言二、数据库表设计1.表结构2.数据库语句:三、代码实现1.实体类2.Mapper3.Service4.ServiceImpl5.Controller四、测试接口总结 一、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,869
精华内容 1,547
关键字:

java多表联查

java 订阅