mybatis连接数据库实现双表查询

创建maven工程包

 再点击下一步

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

设置自己本地仓库位置

最后点击完成。

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

  <dependencies>
    <dewww.devze.compendency>
      <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-js8" ?>
<!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>
<!--  android  连接数据库配置-->
    <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"/>
                <properphpty 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();
    }
}

结果如图所示

到此这篇关于mybatis连接数据库实现双表查询的文章就介绍到这了,更多相关mybatis 双表查询内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！