ssh（struts2+spring+hibernate）+mybatis，用mybatis做多表查询

上篇说的是集成和用mybatis做的简单查询，那么这篇说的是多表查询。

试了好几个方法，说下最终成功的方法。

1.数据库新建2个表.

　　新建表Test和表Test1。表的结构一样，如下所示。

2.在src/com/A/B/persistence 新建文件Test.java（对应数据表Test）和ABPO.java（对应2表连接查询结果）,对应数据表Test1的文件，在本例没有使用到，可不建。

Test.java

public class Test {
 	private String id;
	private String username;
	private String password;
	
	public Test(){}
	
	public String getId() {
	    return id;
	}
	public void setId(String id) {
	    this.id = id;
	}
	
	public String getUsername() {
	    return username;
	}
	public void setUsername(String username) {
	    this.username = username;
	}
	
	public String getPassword() {
	    return password;
	}
	public void setPassword(String password) {
	    this.password = password;
	}
	
	public String toString(){
	    return "Test[id="+id+" , username="+username+" , password="+password+"]";
	}

}

 ABPO.java

public class ABPO {
 	private String aid;
	private String ausername;
	private String apassword;
	private String busername;
	private String bpassword;
	
	public ABPO(){}

	public String getAid() {
		return aid;
	}


	public void setAid(String aid) {
		this.aid = aid;
	}


	public String getAusername() {
		return ausername;
	}


	public void setAusername(String ausername) {
		this.ausername = ausername;
	}


	public String getApassword() {
		return apassword;
	}


	public void setApassword(String apassword) {
		this.apassword = apassword;
	}


	public String getBusername() {
		return busername;
	}


	public void setBusername(String busername) {
		this.busername = busername;
	}


	public String getBpassword() {
		return bpassword;
	}


	public void setBpassword(String bpassword) {
		this.bpassword = bpassword;
	}

}

 3.在src/com/A/B/dao  建接口和xml文件

TestDAO.java

public interface  TestDAO  {
	 public Test getTest(Test test);
	 public void addTest(Test test);
	 public void updateTest(Test test);
	 public void deleteTest(int id);
	 List<ABPO> selectUnion(String id,String name);  
}

 TestDAO.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.A.B.dao.TestDAO">  
	<select id="getTest" parameterType="com.A.B.persistence.Test" resultType="com.A.B.persistence.Test">  
	    SELECT * FROM test WHERE username=#{username} AND password=#{password}  
	</select>  
	
	<insert id="addTest" parameterType="com.A.B.persistence.Test" flushCache="true">  
	   INSERT INTO test (id,username,password) VALUES (#{id},#{username},#{password})  
	</insert>  
	
	<update id="updateTest" parameterType="com.A.B.persistence.Test">  
	    UPDATE test SET password=#{password} WHERE id=#{id}  
	</update>  
	
	<delete id="deleteTest" parameterType="int">  
	    DELETE FROM test WHERE id=#{id}  
	</delete>  
    
    <resultMap type="com.A.B.persistence.ABPO" id="lt">
        <result property="aid" column="id"/>
        <result property="ausername" column="ausername"/>
        <result property="apassword" column="apassword"/>
        <result property="busername" column="busername"/>
        <result property="bpassword" column="bpassword"/>
	</resultMap>

	<select id="selectUnion"  resultMap="lt">    
		select  a.id as aid,a.username as ausername,a.password apassword,b.username as busername,b.password bpassword
		from test a     
		LEFT JOIN test1 b on a.id=b.id     
		where a.id=#{0} and  a.username=#{1}
	</select>     
    
</mapper>

 4. 最后一步，测试

        ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
        TestDAO testDAO=(TestDAO)ctx.getBean("testDAO");
        List<ABPO> obj=testDAO.selectUnion("1","Jessica1");

 如果Test表里有id为“1”的数据，那么使用testDAO.selectUnion("1","Jessica1")会查询到一条信息，如果Test表里没有,则查询不到。

当然也可以将TestDAO.java里的selectUnion方法的参数去掉，同时去掉TestDAO.xml里的selectUnion 的where语句，那么查询的是test表里所有的信息。