Java 获取 clickhouse 读取表所有元信息

文章目录

• 一、MyBatis连接池与事务的深入

• 1.1 MyBatis的连接池技术

• 1.1.1 连接池的分类
• 1.1.2 数据源的配置
• 1.1.3 MyBatis中DataSource的存取
• 1.1.4 MyBatis中连接的获取过程分析

• 1.2 MyBatis的事务控制

• 二、MyBatis的动态sql语句

• 2.1 if标签

• 2.1.1 持久层DAO接口
• 2.1.2 持久层DAO映射配置

• 2.2 where标签

• 2.2.1 持久层DAO接口
• 2.2.2 持久层DAO映射配置

• 2.3 foreach标签

• 2.3.1 条件实体类QueryVo
• 2.3.2 持久层DAO接口
• 2.3.3 持久层DAO映射配置

• 2.4 简化编写的sql片段

• 三、MyBatis多表查询（一对一）

• 3.1 方式一：新建全属性实体类

• 3.1.1 实体类
• 3.1.2 sql语句_等值连接（关键）
• 3.1.3 AccountUser类（含全属性）
• 3.1.4 IAccountDao接口
• 3.1.5 IAccountDao.xml
• 3.1.6 测试

• 3.2 方式二：添加从属关系属性

• 3.2.1 修改Account类
• 3.2.2 修改Account接口方法
• 3.2.3 重新定义AccountDao.xml
• 3.2.4 测试

• 四、MyBatis多表查询（一对多）

• 4.1 编写SQL语句（左外连接）
• 4.2 User类中添加List\
• 4.3 IUserDao接口
• 4.4 IUserDao.xml
• 4.5 测试

• 五、MyBatis多表查询（多对多）

• 5.1 Role -> User 的多对多

• 5.1.1 编写sql语句（role为驱动表）
• 5.1.2 Role实体类
• 5.1.3 IRoleDao接口
• 5.1.4 IRoleDao.xml
• 5.1.5 测试

• 5.2 User -> Role 的多对多

欢迎访问笔者个人技术博客：http://rukihuang.xyz/

一、MyBatis连接池与事务的深入

1.1 MyBatis的连接池技术

1.1.1 连接池的分类

1. UNPOOLED：不适用连接池的数据源
2. POOLED：使用连接池的数据源
3. JNDI：使用JNDI实现的数据源

• MyBatis内部分别定义了实现java.sql.DataSource接口的UnPooledDataSource和PooledDataSource，来表示UNPOOLED、POOLED类型的数据源。
• 一般采用POOLED数据源

1.1.2 数据源的配置

• 在SqlMapConfig.xml文件中配置数据源

<dataSource type="POOLED">
    <property name="driver" value="${jdbc.driver}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</dataSource>

1.1.3 MyBatis中DataSource的存取

• MyBatis是通过工厂模式来创建数据源DataSource对象的，org.apache.ibatis.datasource.DataSourceFactory，通过其getDataSource()方法返回数据源DataSource。

1.1.4 MyBatis中连接的获取过程分析

1. 先找空闲池中有没有空闲的连接

1. 有。直接返回
2. 无。看活动池是否已经达到最大数量

1. 否。在活动池创建一个新的连接来用
2. 是。等待最老的那个连接被归还后，重置后返回

1.2 MyBatis的事务控制

• MyBatis框架是对JDBC的封装，所以其本身也是用JDBC的setAutoCommit()方式来设置事务的提交方式。

session = sessionFactory.openSession(true);//设置自动提交

二、MyBatis的动态sql语句

2.1 if标签

2.1.1 持久层DAO接口

/**
     * 根据条件查用户
     * @param user
     * @return
     */
    List<User> findUserByCondition(User user);

2.1.2 持久层DAO映射配置

• where 1=1的作用

• 为了加 and 拼接 sql 语句。因为不能保证拼接的sql语句是否带and，索性加一个永真条件，是的后面添加的条件，都带有and，那就不用判断了。

• 不是sql语句中的内容，严格区分大小写
• test：判断条件字段是否为空

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
        select * from user where 1=1
        <if test="username != null">
            and username = #{username}
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
    </select>

2.2 where标签

• 简化where 1=1的拼装，可以使用where标签

2.2.1 持久层DAO接口

/**
     * 根据条件查用户
     * @param user
     * @return
     */
    List<User> findUserByCondition(User user);

2.2.2 持久层DAO映射配置

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
        select * from user
        <where>
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </select>

2.3 foreach标签

• 进行范围查询时，对参数进行传递。如：

SELECT * FROM USERS WHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16)

2.3.1 条件实体类QueryVo

• 在条件实体类QueryVo中加入List集合用于封装参数

package com.ruki.domain;

import java.util.List;

public class QueryVo {
    private User user;
    private List<Integer> ids;

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

2.3.2 持久层DAO接口

/**
     * 子查询foreach标签
     * @param vo
     * @return
     */
    List<User> findUserInIds(QueryVo vo);

2.3.3 持久层DAO映射配置

• collection：集合属性名
• open：开始
• close：结束
• item：遍历出来的元素
• separator：分隔符
• #{id}：将值取出，与item的值对应

<!-- foreach标签 -->
    <select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
        select * from user
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in (" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>

        </where>
    </select>

2.4 简化编写的sql片段

• 在持久层DAO映射配置中，有许多重复的查询语句select * from user，可将其提取出来。
• sql：

• id：提取出来的sql片段名

• include：

• refid：引用sql片段的id

<!-- sql的抽取 -->
    <sql id="defaultUser">
        select * from user
    </sql>
    
    <!-- 查询所有用户 -->
    <select id="findAll" resultMap="userMap">
        <include refid="defaultUser"></include>
        <!-- select * from user -->
    </select>

三、MyBatis多表查询（一对一）

3.1 方式一：新建全属性实体类

• 定义专门的pojo类作为输出类型，定义sql查询结果集所有的字段，并将字段封装到含有两部分属性的AccountUser实体类中，然后进行输出。

3.1.1 实体类

• Account实体类

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
	//getter setter toString ...
}

• User实体类

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
	//getter setter toString ...
}

3.1.2 sql语句_等值连接（关键）

SELECT 
	account.*, 
	user.username, 
	user.address 
FROM 
	account, 
	user 
WHERE account.uid = user.id

3.1.3 AccountUser类（含全属性）

• 继承Account类，并且包含账户信息的同时，还要包含用户信息

public class AccountUser extends Account {
    private String username;
    private String address;
	//getter setter toString ...
}

3.1.4 IAccountDao接口

/**
     * 查询所有账户，以及对应的用户信息
     * @return
     */
    List<AccountUser> findAllAccountUser();

3.1.5 IAccountDao.xml

<!-- 查询所有AccountUser -->
    <select id="findAllAccountUser" resultType="accountuser">
        select a.*, u.username, u.address from account a, user u where a.uid = u.id;
    </select>

3.1.6 测试

@Test
    public void testFindAllAccountUser(){
        List<AccountUser> aus = accountDao.findAllAccountUser();
        for(AccountUser au : aus){
            System.out.println(au);
        }
    }

3.2 方式二：添加从属关系属性

• 使用resultMap，定义专门的resultMap用于映射一对一查询结果。
• 通过面向对象的(has a)关系可以得知，我们可以在Account类中加入一个User类的对象来代表这个账户是哪个用户的

3.2.1 修改Account类

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;

    //从表应包含主表的引用属性
    private User user;
	
	//getter setter toString
}

3.2.2 修改Account接口方法

/**
     * 查询所有账户
     * @return
     */
    List<Account> findAll();

3.2.3 重新定义AccountDao.xml

• 一对一关系映射采用association标签

• association

• property：属性名
• column：外键字段名
• javaType：包装类型。（一般为全限定类名，但这里已经在SqlMapConfig.xml文件中设置了别名）

<!-- 定义封装account和user的resultMap -->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>

        <!-- 一对一关系的映射：配置封装user的内容 -->
        <association property="user" column="uid" javaType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </association>
    </resultMap>

    <!-- 查询所有用户 -->
    <select id="findAll" resultMap="accountUserMap">
        select u.*, a.id aid, a.uid, a.money from account a, user u where u.id = a.uid
    </select>

3.2.4 测试

/**
     * 测试查询所有账户
     */
    @Test
    public void testFindAll() throws IOException {
        List<Account> accounts = accountDao.findAll();
        for(Account account : accounts){
            System.out.println("---每一个account的信息");
            System.out.println(account);
            System.out.println(account.getUser());
        }
    }

四、MyBatis多表查询（一对多）

4.1 编写SQL语句（左外连接）

select 
	u.*,
	a.id,
	a.uid,
	a.money
from 
	user u 
LEFT JOIN account a ON a.UID = u.id;

4.2 User类中添加List<account>

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
	//主表包含从表的属性
    private List<Account> accounts;
    
	//getter setter toString
}

4.3 IUserDao接口

/**
     * 查找所有
     * @return
     */
    List<User> findAll();

4.4 IUserDao.xml

• 一对多采用collection标签

• collection

• property：属性名
• ofType：泛型（一般为全限定类名，但这里已经在SqlMapConfig.xml文件中设置了别名）

<resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
		
        <!-- collection是用于建立一对多中集合属性的对应关系 
			ofType用于指定集合元素的数据类型 -->
        <collection property="accounts" ofType="account">
            <id property="id" column="aid"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>

    <!-- 查询所有用户 -->
    <select id="findAll" resultMap="userAccountMap">
        select * from user u LEFT JOIN account a ON a.UID = u.id;
    </select>

4.5 测试

@Test
    public void testFindAll() throws IOException {
        List<User> users = userDao.findAll();
        for(User user : users){
            System.out.println(user);
            System.out.println(user.getAccounts());

        }
    }

五、MyBatis多表查询（多对多）

• 通过中间表形成多对多关联

5.1 Role -> User 的多对多

5.1.1 编写sql语句（role为驱动表）

select 
	u.*, 
	r.ID as rid, 
	r.ROLE_NAME, 
	r.ROLE_DESC 
from 
	role r
left join 
	user_role ur on r.ID = ur.RID
left join 
	user u on ur.UID = u.id;

5.1.2 Role实体类

public class Role implements Serializable {

    private Integer roleId;
    private String roleName;
    private String roleDesc;
	//多对多的关系映射：一个角色可以赋予多个用户
    private List<User> users;
    
    //getter setter toString
}

5.1.3 IRoleDao接口

/**
     * 查询所有role
     * @return
     */
    List<Role> findAll();

5.1.4 IRoleDao.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.ruki.dao.IRoleDao">
    <resultMap id="roleMap" type="role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>

        <collection property="users" ofType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>
    
    <select id="findAll" resultMap="roleMap">
        select u.*, r.ID as rid, r.ROLE_NAME, r.ROLE_DESC from role r
         left join user_role ur
         on r.ID = ur.RID
         left join user u
         on ur.UID = u.id;
    </select>
</mapper>

5.1.5 测试

@Test
    public void testFindAll() throws IOException {
        List<Role> roles = roleDao.findAll();
        for(Role role : roles){
            System.out.println("------------------------");
            System.out.println(role);
            System.out.println(role.getUsers());
        }
    }

5.2 User -> Role 的多对多

• 原理同上，sql顺序改变一下，User类中添加roles属性