mybatis連接數(shù)據(jù)庫實現(xiàn)雙表查詢

更新時間：2024年09月19日 09:36:59 作者：『初橙ζ 』

本文主要介紹了mybatis連接數(shù)據(jù)庫實現(xiàn)雙表查詢,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

創(chuàng)建maven工程包

再點擊下一步

自己定義項目名稱，在下一步

設(shè)置自己本地倉庫位置

最后點擊完成。

建好后在pom.xml文件中添加mybatis以及相關(guān)架包

  <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)獲取.

接著連接數(shù)據(jù)庫表的接口

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-->
    <!--   動態(tài)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">
    <!--    動態(tài)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  關(guān)聯(lián)性查詢-->
    <resultMap id="user_order" type="user">
        <!--      collection  一對多
          property 查詢出訂單的結(jié)果放在哪個屬性中
          column 用什么列傳遞給訂單做參數(shù)
          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>

數(shù)據(jù)庫鏈接文件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>
<!--    連接數(shù)據(jù)庫配置-->
    <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));

        //關(guān)閉會話
        session.close();
    }
}

結(jié)果如圖所示