分类：数据库

一种批量插入数据saveIgnore并返回主键的方法

在mysql中,提供了save ignore语法,用于在插入数据如果出现冲突时忽略信息的处理方式.在这种情况下,一般主键id是通过自动生成,ignore通过一些惟一索引进行控制.在程序中的期望即如果惟一索引不冲突就插入新的数据,如果冲突则不再插入. 但都希望能够在处理成功之后拿到这些数据的主键(不管是之前的还是新插入的),以便于后续进行处理.相应的简单业务逻辑如下所示.

        List<T> personList = xxxList;
        mysql.saveIgnore(personList);
        
        //进行后续逻辑,如转账
        personList.forEach(t-> {
            mysql.addMoney(t.getId(), 100);
        });

在标准的jdbc中,如果是save,或者是mysql的save values(value1) (value2) 这种语法,是能够通过 statement.getGeneratedKeys()返回自动生成的主键.但对于save ignore无效,mysql并不是返回由于冲突处理的之前的主键信息.

常规的作法就是在save ignore之后,再通过相应的惟一索引来进行查询. 类似如下的sql方式

select id from t where t.name in(name1,name2);

这种方式,对于如果惟一索引仅有一列是没有问题的.但如果是多列组成的,则生成如下的sql

select id from t where (t.name,t.code) in ((name1,code1),(name2,code2));

理论上,这种方式也没有问题,但是在mysql中,这种语法并不能命中相应的索引,会造成全局扫描(验证版本5.5,5.6)
修改in版本为如下sql

select id from t where (t.name = name1 and t.code = code1) or (t.name = name2 and t.code = code2);

这种方式可以命中索引,但是整个sql会变得很长,可以看出,相应的惟一索引列会出现多次.如果是5000个数据处理(批量处理肯定数据会很多才有意义),则整个sql会很长.

本文的方式是通过临时表,提前插入索引数据,再通过表关联来获取相应的数据.这样可以避免上面sql过长的问题,并且利用mysql内存表快速处理数据.整个原理可以理解为以下几个步骤

  1. 创建临时表,表数据仅为惟一索引要求列,并建立相应的惟一索引
  2. 插入待处理数据中指定属性到临时表当中
  3. 使用save ignore语法插入数据到实际表中
  4. 两表关联,使用临时表关联实际表,指定相应的惟一列进行关联条件,查询出主键信息
  5. 程序中将主键处理到相应的对象中

继续阅读“一种批量插入数据saveIgnore并返回主键的方法”

在mybatis条件查询中进行类关联查询(类Hibernate关联查询)

在上一篇中，已经解决了如何使用构建条件进行数据查询。但在常规的查询中，并不总是单独查询某一个表信息，某些条件还需要关联其它数据表才能得出。我们希望在进行查询时，能够根据条件中所指定的关联表进行关联化查询，并在条件中自动处理关联化条件。如下的查询语句所示:

select a.* from tableA a inner join tableB b on b.a_id = a.id where b.c_id = ? and b.d = ? and a.e != ?

那么在相应的条件中，即要处理模型之间的关联关系，同时处理在条件中的前缀别名信息，以保证所在表的正确性。那么相应的Criteria表达应该如下所示:

List<Criteria> criteriaList = Lists.newArrayList();
criteriaList.add(Criterias.link(B.class, "b", "aId", LinkMode.INNER));
criteriaList.add(Criterias.eq("b.c_id", cId));
criteriaList.add(Criterias.eq("b.d", d));

即在原来的基础之上，增加一个用于描述关联关系的LinkCriteria，同时在相应的条件上增加属性前缀，用于描述指定的条件主体(当然如果是当前主体不需要前缀)。在具体的生成Mql的过程当中，增加用于处理关联关系的逻辑，同时处理条件的前缀即可。

继续阅读“在mybatis条件查询中进行类关联查询(类Hibernate关联查询)”

在mybatis中使用Criteria式条件查询

在使用常规的mybatis时，我们经常碰到的问题就是条件式查询。在一个查询界面，查询条件较多，并且运算符并不总是=时，在后台就需要拼装sql语句。这种处理方式肯定不是使用mybatis的初衷，对于使用了hibernate的我来说，如果mybatis也有一套criteria查询就好了。在具体实现中，我们只需要按照hibernate的处理方式定义好相应的criteria，最后传递给mybatis，其自身处理相应的条件和参数信息，最终返回相应的数据即可。如下一个示例代码所示:

List<Criteria> criteriaList = Lists.newArrayList();
criteriaList.add(Criterias.eq("aaa",111));//等于某个值
criteriaList.add(Criterias.ge("date",new Date()));//大于或等于某个时间
criteriaList.add(Criterias.in("code", new String[]{"a","b","c"}));//代码值在一个集合当中

如果使用这种方式，无疑会大大降低编写表单式查询的代码复杂度。同时，在内部处理中也不需要作任何判断，而直接将生成的sql交给mybatis去执行即可。当然，我们不希望生成的sql连我们自己都看不懂(想一想hibernate生成的sql),最终生成的sql像下面这样即可。

select * from table where aaa = #{aaa1,jdbcType=NUMERIC} and date >= #{date2,jdbcType=TIMESTAMP} and code in (#{code1,jdbcType=VARCHAR},#{code2,jdbcType=VARCHAR},#{code3,jdbcType=VARCHAR})

这是标准的mybatis语句，在进行代码调试和处理时也方便进行查看并处理。那么整个处理逻辑即变成如何处理参数信息，即如下所示的语句

字段名 运算符 #{参数名,jdbcType=字段类型} //filed = #{param1,jdbcType=VARCHAR}

参考 数据库表与java域模型之间的mapping和自动生成(基于mybatis)。我们可以很容易地就完成这个处理。分别处理 字段名 运算符 参数名 字段类型 参数映射即可。

继续阅读“在mybatis中使用Criteria式条件查询”

数据库表与java域模型之间的mapping和自动生成(基于mybatis)

最近有幸读到《企业架构模式》这本书，需要写作于2003年，已经是十年前，但仔细读过，有些东西现在只知道是这样用，但并不知道为什么要这样做。在看过此书之后，很多东西都能够有一条线进行贯穿，在使用到一些框架时，也知道背后的原因了。
这里面有一篇讲到对象-关系元数据映射的，实际上就是指在一个数据库中一个数据表与一个java中的domain对象之间的映射，在文中提到几种操作，也提到了为什么要这样做。其中，重要的当然是为什么要这样做了，但本篇主要讲期间在mybatis中笔者之前做的一个简单的映射，最终的效果与文中的结果基本上是一致的（因此在进行code时，还是没看过此书，结果发现自己又发明了一个新轮子）。
由于使用到mybatis，所以对模型之间的关系这里并没有涉及，只简单对应于一个数据表一个模型的概念。

通常情况下，我们在数据表中一个数据表user，有2个字段分别为user_name和password.那么在java中,我们会有一个对应的domain文件,如下代码所示:

public class User {
private String userName;
private String password;
}

这里只是一个简单的对应,同时字段user_name对应于userName,这里并不是完全相同的字符串.因此,在mybatis相对应的xml中,我们需要显示的对待mapping操作.如下xml所示:

<insert>
insert into user(user_name,password) values(#{userName},#{password});
</insert>

<select columnMap=tMap> <!-- tMap中需要定义mapping关系 >
select user_name,password from user
</select>

这里涉及到一个东西,就是我们需要手动地编写相应的mapping语句,而且涉及到多个地方.比如在insert脚本中,需要编写user_name和userName不同的语句;在select中,还需要手动进行columnMap工作.对于一般的开发人员,使用copy&paste时,这里就会出错.而且一旦涉及到模型属性的变更,比如增加一个属性,表中加一个字段,这里的修改量就较大了,而且一旦涉及到代码还不集中,那就更麻烦了.
本篇即是引入一种特殊的columnMapping对象,并通过自动生成+动态SQL构建,来完成这种操作.参考如下一个insert语句:

	<insert>
		insert into ${table.schema()}.${table.name()}(
		<foreach collection="columnMappingList" separator="," item="cm">
			${cm.jdbcField}
		</foreach>
		) values(
		<foreach collection="columnMappingList" separator="," item="cm">
			#{e.${cm.javaProperty},jdbcType=${cm.jdbcType}}
		</foreach>
		)
	</insert>

继续阅读“数据库表与java域模型之间的mapping和自动生成(基于mybatis)”

Oracle中关于rownum和分页的详细描述(翻译)

原文为:http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Limiting Result Sets
rownum是一个很让人疑惑的oracle概念，这会导致很多人不会使用它，一般情况下，在以下的两个场合，我们会使用到rownum伪列:

  • 执行一个TOP-N查询，如查询最大或最多，最靠前的某些记录，与其它数据库(如mysql)的limit很相似.
  • 对查询进行分页，这个很常见，就不说了。

How ROWNUM Works
rownum是一个伪列，在一个查询中，它并不是真正的列，但在查询结果中就会存在这个列数据。在查询的结果中，rownum会依次从1开始赋值给查询的每一条记录。但这个值并不是固定的，会随着不同的查询发生变化，也就是说在一个数据表中，并不存在rownum这个列，即你不能在一个表中查询rownum为5的数据，没有这种数据。

那么rownum是什么时候被赋值的呢，了解这个时机会让你在一些查询中了解一个sql为什么要这样写。
rownum是在当数据查询一定的条件被查询出来但并没有被排序或者聚合的时候赋值的
也就是说，rownum是在有数据的时候才会递增，如果没有数据被查询出来，则不会递增。如下面的sql语句，则永远不会返回任何数据:

select * 
  from t 
 where ROWNUM > 1;

因为对于第一条数据来说,rowum > 1并不为真,因此rownum也不会递增到2，所以不会有数据的rownum大于1。
我们可以通过下面的一个标准的数据查询，来了解rownum是何时被赋值的。如下所示：

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

那么，一般来说，它会按照以下的顺序来工作:

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

继续阅读“Oracle中关于rownum和分页的详细描述(翻译)”

oracle中row_number和rownum的区别和联系(翻译)

附问题:有以下一个SQL语句:

SELECT  *
FROM    (
        SELECT  t.*, row_number() OVER (ORDER BY ID) rn
        FROM    mytable t
        )
WHERE   rn BETWEEN :start and :end

sql中的order by语句大大降低了处理的速度，如果把order by去掉，相应的执行计划会大大地提高。如果换成下面的sql:

SELECT  t.*, row_number() OVER (ORDER BY ID) rn
FROM    mytable t
WHERE   rownum BETWEEN :start and :end

很明显，这个sql是错的，根本查询不了正确的数据信息。是否有其它的方法可以提高查询速度？
针对以上问题，就必须要了解一下关于row_number和rownum的区别，以及如何来运用这些信息。

首先了解一下rownum是如何进行工作的，根据oracle的官方文档：
如果对rownum进行大于比较，这个比较将直接返回false。如，下列sql语句将不能返回任何数据信息:

继续阅读“oracle中row_number和rownum的区别和联系(翻译)”