使用mybatis连接数据库双表查询

创建maven工程包

 

 再点击下一步

 自己定义项目名称，在下一步

 设置自己本地仓库位置

最后点击完成。

建好后在pom.xml文件中添加mybatis以及相关架包

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis </groupId>
      <artifactId> mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
    </dependency>
  </dependencies>

这些架包可以从Maven Repository: Search/Browse/Explore (mvnrepository.com)获取.

接着连接数据库表的接口

import java.util.List;

public interface OrdersDAO {
    public List<Orders> findOrderByUserid(long userid);

    public  void batchInsertOrder(List<Orders> ord);
}

public interface UserinfosDAO {

public List<Userinfos>findTop10();
public List<Userinfos>findUser(Userinfos user);

}

以及实用类

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Userinfos {
    private  long userid;
    private  String username;
    private Date birthday;
    private  String pwd;
    private List<Orders> ords;
}

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Userinfos {
    private  long userid;
    private  String username;
    private Date birthday;
    private  String pwd;
    private List<Orders> ords;
}

以及配置文件order.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.kgc.mymb.dao.OrdersDAO">

    <!--   resulMap-->
    <!--   动态sql-->
    <select id="findOrderByUserid" parameterType="long" resultType="order">
        select * from orders where userid=#{userid}

    </select>
    <insert id="batchInsertOrder">
        insert  into orders values
    <foreach collection="list" item="order" separator=",">
    (#{order.ordid},#{order.userid},#{order.shopid},#{order.buynum})
    </foreach>
    </insert>
</mapper>

和userinfos.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.kgc.mymb.dao.UserinfosDAO">
    <!--    动态sql-->
    <select id="findUser" parameterType="user" resultType="user">
        select* from userinfos
      <where>
          <if test="username != null">
               username=#{username}
          </if>
          <if test="birthday != null">
               and date_format(birthday,'%Y-%m-%d')=date_format(#{birthday},'%Y-%m-%d')

          </if>
      </where>
    </select>

    <!--   resulMap  关联性查询-->
    <resultMap id="user_order" type="user">
        <!--      collection  一对多
          property 查询出订单的结果放在哪个属性中
          column 用什么列传递给订单做参数
          select 订单查询方法
          -->
        <result column="userid" property="userid"></result>
        <collection property="ords"
                    column="userid"
                    select="com.kgc.mymb.dao.OrdersDAO.findOrderByUserid">

        </collection>


    </resultMap>
    <select id="findTop10" resultMap="user_order">
        select * from userinfos limit 10
    </select>
</mapper>

数据库链接文件mybatis.cfg.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC
        "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--     给自己的实体类起别名-->
    <typeAliases>
        <typeAlias type="com.kgc.mymb.domain.Userinfos" alias="user"></typeAlias>
    <typeAlias type="com.kgc.mymb.domain.Orders" alias="order"></typeAlias>
    </typeAliases>
<!--    连接数据库配置-->
    <environments default="cm">
        <environment id="cm">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.47.3:3306/mydemo"/>
                <property name="username" value="root"/>
                <property name="password" value="ok"/>
            </dataSource>
        </environment>
    </environments>
<!--    加载用户的sql xml 文件-->
    <mappers>
        <mapper resource="mapper/userinfos.xml"></mapper>
        <mapper resource="mapper/orders.xml"></mapper>
    </mappers>
</configuration>

最后测试类

public class Demo {
    public static void main(String[] args) throws IOException {
        //读取配置文件
        Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");
        // 开启SqlSessionFactory工厂
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
        //将你的dao接口注入到会话工厂配置对象中
        // factory.getConfiguration().addMapper(UserinfosDAO.class);
        //开启SqlSession
        SqlSession session =factory.openSession();
        //获取接口对象


        // UserinfosDAO udao= session.getMapper(UserinfosDAO.class);
        // Calendar cal=Calendar.getInstance();
        // cal.set(1995,3,22,0,0,0);
        // Userinfos us =  Userinfos.builder().username("dpjbms").birthday(cal.getTime()).build();
        // System.out.println(udao.findUser(us));

        UserinfosDAO udao= session.getMapper(UserinfosDAO.class);
        System.out.println( udao.findTop10());
        // OrdersDAO ordersDAO = session.getMapper(OrdersDAO.class);
        // Orders ord1=Orders.builder().ordid(10).userid(1).shopid(100).buynum(5).build();
        // Orders ord2=Orders.builder().ordid(11).userid(2).shopid(1).buynum(1).build();
        // List<Orders> ords=new ArrayList<>();
        // ords.add(ord1);
        // ords.add(ord2);
        // ordersDAO.batchInsertOrder(ords);
        // session.commit();


        // OrdersDAO ordersDAO= session.getMapper(OrdersDAO.class);
        // System.out.println(ordersDAO.findOrderByUserid(1));

        //关闭会话
        session.close();
    }
}

结果如图所示