mysql导入CSV文件及JAVA读取CSV文件和MYSQL批量插入和更新及ibatis常用SQL语句

mysql 的load data local infile命令

和insert into 用途类似，LOAD DATA INFILE也是将数据插入表中，

不过它的插入速度要快很多，适合于大数据插入。

基本语法组成
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS[TERMINATED BY '\t'][OPTIONALLY] ENCLOSED BY]

[ESCAPED BY ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
 

1.0
LOW_PRIORITY：设置最低优先级，LOAD DATA语句的执行被推迟

到没有其他用户读取表后

1.1
LOCAL：用LOCAL关键字，则用客户端的文件中执行读取插入，如果

没有指定，则文件必须位于服务器上，若在服务器上操作LOAD

DATA INFILE，则需要有该文件的操作权限
 LOAD DATA INFILE “./data.txt” INTO TABLE db2.my_table;从服务

器目录下读取文件 `data.txt”
LOAD DATA LOCAL INFILE “user/data.txt” INTO TABLE

db2.my_table;从本地的User目录下读取文件 `data.txt”

1.2 'file_name.txt'
需要写入数据库表的文件

1.3 [REPLACE | IGNORE]
若写入表中的数据包含原表中已存在的唯一标示或ID的记录，用
REPLACE或IGNORE来控制操作，REPLACE使用文件中的新记录来代

替原表中唯一键值的旧记录，而IGNORE则忽略新的记录任然保留旧

的唯一键值记录,
若表my_tale中已有记录
id name
111 aaa
id为唯一标示
若要插入文件中包含
id name
111 bbb
的记录
则用 REPLACE关键字的话，数据库中会插入111 bbb来代替111 aaa

若用IGNORTE关键字的话，数据库中任然会保留111 aaa而 111 bbb

不会被插入

1.4 INTO TABLE tbl_name
需要写入数据库的表名

1.5 FIELDS
指定字段之间TERMINATED BY, [OPTIONALLY] ENCLOSED BY和

ESCAPED BY 字段分隔，特殊字符转义等等的相关操作

1.51
TERMINATED BY
指定字段之间的分隔符，默认为\t,

1.52 [OPTIONALLY] [ENCLOSED BY ''][ESCAPED BY ]
OPTIONALLY
ENCLOSED BY：指定每个记录中需要特殊处理写入的字符，比如引

号，
若要读入
name
"aaa"
需要对ENCLOSED BY 做指定
FIELDS  ENCLOSED BY ”"” ESCAPED BY ”\\”

而ESCAPED BY则用来转义ENCLOSED BY中指定的特殊字符
如果省略OPTIONALLY，所有的字段都将被ENCLOSED BY影响
比如
“1”,a string”,”100.20“
如果指定OPTIONALLY，则只有字符型字段会被影响
 

1.6 [IGNORE number LINES]
设置每行记录之间的区隔，默认以\n作为换行符

1.7 [IGNORE number LINES]
IGNORE number LINES选项可被用来忽略在文件开始的一个列名字

的头

1.8 [(col_name,...)]
可选择需要的列来插入

下面示例格式
mysql 用load data infile 把CSV文件到如到表里
mysql>load data infile '/tmp/UserEmail .csv' 
into table user_info1_shinnseityuu 
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\r\n';

//文件中有中文字符的话，设置编码
mysql>load data infile '/tmp/UserEmail .csv' 
into table user_info1_shinnseityuu character set gb2312  --包含控

制导入后字符的正确语句  
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\r\n';

mysql使用 load data infile将excel文件导入数据库
步骤很简单
1、先将excel另存为csv格式的文本，csv是以逗号分隔各个字段数据

的

2、在mysql中输入sql语句
load data local infile "F:/test.csv"
into table tableName
fields terminated by ','   (dir_1,dir_2,dir_3,keyword,buy,click,ppc)  ;

然后执行SQL即可~
注意，字段必须放到fields terminated by ',' 的后面，否则会报错。
上面的fields terminated by ',' 的意思是根据文本中的逗号来分隔每

个字段的数据。
如果文本里面的分隔符是\t制表位，则不需要写这个字段分隔SQL语

句，直接写录入的字段即可。

 

Java不写文件，使用LOAD DATA LOCAL INFILE大批量导入数据到MySQL的实现

MySQL使用load data local infile 从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。
但是这个方法有个缺点，就是导入数据之前，必须要有文件，也就是说从文件中导入。这样就需要去写文件，
以及文件删除等维护。某些情况下，比如数据源并发的话，还会出现写文件并发问题，很难处理。
那么有没有什么办法，可以达到同样的效率，直接从内存（IO流中）中导入数据，而不需要写文件呢？
前段时间，去MySQL社区的时候发现了这样一个方法：setLocalInfileInputStream（），此方法位于com.mysql.jdbc.PreparedStatement 类中
下面是具体实现：

通过使用 MySQL JDBC 的setLocalInfileInputStream 方法实现从java InputStream中load data local infile 到MySQL数据库中。
准备测试表
SQL如下：
use test; 
CREATE TABLE `test` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `a` int(11) NOT NULL, 
  `b` bigint(20) unsigned NOT NULL, 
  `c` bigint(20) unsigned NOT NULL, 
  `d` int(10) unsigned NOT NULL, 
  `e` int(10) unsigned NOT NULL, 
  `f` int(10) unsigned NOT NULL, 
  PRIMARY KEY (`id`), 
  KEY `a_b` (`a`,`b`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Java代码如下：
package com.seven.dbTools.DBTools; 
 
import java.io.ByteArrayInputStream; 
import java.io.InputStream; 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
import org.springframework.jdbc.core.JdbcTemplate;  
import javax.sql.DataSource; 
import org.apache.log4j.Logger; 
 
/**
 * @author seven
 * @since 07.03.2013
 */ 
public class BulkLoadData2MySQL { 
 
    private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class); 
    private JdbcTemplate jdbcTemplate; 
    private Connection conn = null; 
 
    public void setDataSource(DataSource dataSource) { 
        this.jdbcTemplate = new JdbcTemplate(dataSource); 
    } 
 
    public static InputStream getTestDataInputStream() { 
        StringBuilder builder = new StringBuilder(); 
        for (int i = 1; i <= 10; i++) { 
            for (int j = 0; j <= 10000; j++) { 
 
                builder.append(4); 
                builder.append("\t"); 
                builder.append(4 + 1); 
                builder.append("\t"); 
                builder.append(4 + 2); 
                builder.append("\t"); 
                builder.append(4 + 3); 
                builder.append("\t"); 
                builder.append(4 + 4); 
                builder.append("\t"); 
                builder.append(4 + 5); 
                builder.append("\n"); 
            } 
        } 
        byte[] bytes = builder.toString().getBytes(); 
        InputStream is = new ByteArrayInputStream(bytes); 
        return is; 
    } 
 
    /**
     * 
     * load bulk data from InputStream to MySQL
     */ 
    public int bulkLoadFromInputStream(String loadDataSql, 
            InputStream dataStream) throws SQLException { 
        if(dataStream==null){ 
            logger.info("InputStream is null ,No data is imported"); 
            return 0; 
        } 
        conn = jdbcTemplate.getDataSource().getConnection(); 
        PreparedStatement statement = conn.prepareStatement(loadDataSql); 
 
        int result = 0; 
 
        if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) { 
 
            com.mysql.jdbc.PreparedStatement mysqlStatement = statement 
                    .unwrap(com.mysql.jdbc.PreparedStatement.class); 
 
            mysqlStatement.setLocalInfileInputStream(dataStream); 
            result = mysqlStatement.executeUpdate(); 
        } 
        return result; 
    } 
 
    public static void main(String[] args) { 
        String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)"; 
        InputStream dataStream = getTestDataInputStream(); 
        BulkLoadData2MySQL dao = new BulkLoadData2MySQL(); //DAO实现类
        try { 
            long beginTime=System.currentTimeMillis(); 
            int rows=dao.bulkLoadFromInputStream(testSql, dataStream); 
            long endTime=System.currentTimeMillis(); 
            logger.info("importing "+rows+" rows data into mysql and cost "+(endTime-beginTime)+" ms!"); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        System.exit(1); 
    }   
} 
提示：
 String testSql ="LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";
使用setLocalInfileInputStream方法，会直接忽略掉文件名称，而直接将IO流导入到数据库中。

 

java读取csv文件

CSV其实就是COMMA SEPARATED VALUE的缩写。
在开发中用Java操作csv文件有专门的的API叫javacsv.jar
javacsv.jar下载地址：
http://sourceforge.net/project/showfiles.php?group_id=33066

下面演示一段操作代码仅供参考：
package com.syc.test.bean;  
public class ReslutBean {  
    String help_keyword_id;  
    String name;    
    public String getHelp_keyword_id() {  
        return help_keyword_id;  
    }  
 
    public void setHelp_keyword_id(String help_keyword_id) {  
        this.help_keyword_id = help_keyword_id;  
    }  
 
    public String getName() {  
        return name;  
    }  
 
    public void setName(String name) {  
        this.name = name;  
    }   
} 

package com.syc.test.javaCSV;  
 
import java.io.IOException;  
import java.nio.charset.Charset;  
import java.util.ArrayList;  
import java.util.List;  
 
import com.csvreader.CsvReader;  
import com.csvreader.CsvWriter;  
import com.syc.test.DAO.ConnectionDB;  
import com.syc.test.bean.ReslutBean;  
 
public class Java2CSV {  
    /** 
     * @param args 
     * @throws Exception 
     */ 
    public static void main(String[] args) throws Exception {  
        // 从获取将要写入csv文件的结果集  
        List<ReslutBean> list = new ArrayList<ReslutBean>();  
        list = ConnectionDB.querySQL();  
 
        // 预组装csv文件内容标题行  
        String[][] data = new String[list.size() + 1][2];  
        data[0][0] = "Help_keyword_id";  
        data[0][1] = "Name";  
 
        // 预组装csv文件内容  
        int len = list.size();  
        for (int i = 0; i < len; i++) {  
            data[i + 1][0] = list.get(i).getHelp_keyword_id();  
            data[i + 1][1] = list.get(i).getName();  
        }  
 
        writerCsv("e://c测试.csv", data);  
        readerCsv("e://c测试.csv");  
    }  
 
    /** 
     * 读取csv 
     *  
     * @param csvFilePath 
     * @throws Exception 
     */ 
    public static void readerCsv(String csvFilePath) throws Exception {  
 
        CsvReader reader = new CsvReader(csvFilePath, ',',  
                Charset.forName("GBK"));// shift_jis日语字体,utf-8 
        reader.readHeaders();  
        String[] headers = reader.getHeaders();  
 
        List<Object[]> list = new ArrayList<Object[]>();  
        while (reader.readRecord()) {  
            list.add(reader.getValues());  
        }  
        Object[][] datas = new String[list.size()][];  
        for (int i = 0; i < list.size(); i++) {  
            datas[i] = list.get(i);  
        }  
 
        /* 
         * 以下输出 
         */ 
 
        for (int i = 0; i < headers.length; i++) {  
            System.out.print(headers[i] + "\t");  
        }  
        System.out.println("");  
 
        for (int i = 0; i < datas.length; i++) {  
            Object[] data = datas[i]; // 取出一组数据 
            for (int j = 0; j < data.length; j++) {  
                Object cell = data[j];  
                System.out.print(cell + "\t");  
            }  
            System.out.println("");  
        }  
    }  
 
    /** 
     * 写入csv 
     *  
     * @param csvFilePath文件名路径 
     *            +文件名字 
     * @param data数据项 
     */ 
    public static void writerCsv(String csvFilePath, String[][] data) {  
 
        CsvWriter writer = null;  
        try {  
            writer = new CsvWriter(csvFilePath, ',', Charset.forName("GBK"));// shift_jis日语字体,utf-8 
 
            for (int i = 0; i < data.length; i++) {  
                writer.writeRecord(data[i]);  
            }  
        } catch (IOException e) {  
            e.printStackTrace();  
        } finally {  
            writer.close();  
        }  
    }  
} 

当然你还可以用supecsv 或者 opencsv啦。
先下载javacsv2.0.zip的文件，解压后，把javacsv.jar 添加到项目中。
官方下载地址：
http://sourceforge.net/project/showfiles.php?group_id=33066
API地址：http://javacsv.sourceforge.net/
简单的操作代码：import java.io.IOException;  
import java.nio.charset.Charset;  
import java.util.ArrayList;  
import com.csvreader.CsvReader;  
import com.csvreader.CsvWriter;  
   
public class DB2ExportCsv  
{  
    /** 
    * 读取CSV文件 
    */ 
    public void  readCsv(){  
        try {      
                ArrayList<String[]> csvList = new ArrayList<String[]>(); //用来保存数据 
                String csvFilePath = "D:/log/Alarm20101125.csv";  
                CsvReader reader = new CsvReader(csvFilePath,',',Charset.forName("SJIS"));    //一般用这编码读就可以了     
                   
                reader.readHeaders(); // 跳过表头   如果需要表头的话，不要写这句。 
                   
                while(reader.readRecord()){ //逐行读入除表头的数据     
                    csvList.add(reader.getValues());  
                }              
                reader.close();  
                   
                for(int row=0;row<csvList.size();row++){  
                     String  cell = csvList.get(row)[0]; //取得第row行第0列的数据 
                     System.out.println(cell);  
                }       
            } catch (Exception ex) {  
                    System.out.println(ex);  
                }  
    }  
      
    /** 
     * 写入CSV文件 
     */ 
    public static void WriteCsv(){  
        try {  
                String csvFilePath = "D:/log/Alarm20101125.csv";  
                CsvWriter wr =new CsvWriter(csvFilePath,',',Charset.forName("SJIS"));  
                String[] contents = {"告警信息","非法操作","没有权限","操作失败"};                      
                wr.writeRecord(contents);  
                wr.close();  
         } catch (IOException e) {  
            e.printStackTrace();  
         }  
    }  
} 

 想了解更多的函数请查看javacsv2.0/doc/index.html说明。我觉得javacsv2.0/src/AllTests.java看看也很有用。大家可以去试试
此代码可以解决字段中出现分隔符，双引号等等。。。
/** 
     * 对于文件中字段包含逗号的文件的特殊处理 （同时可以去除掉双引号）处理完以后会在相同的路径下输出相同文件名的TXT文件 
     *  
     * @throws Exception 
     */ 
    public static void specialChar(String filePath,int starRow) throws Exception {  
 
        BufferedReader br = null;  
        File f = new File(filePath);  
        String fileName = f.getName();  
 
        if (!fileName.substring(fileName.indexOf(".") + 1).equals("csv")) {  
            throw new Exception(filePath + "不是一个CSV文件");  
        }  
        File file = new File(StringUtil.replace(f.getPath(), "csv", "txt"));  
        FileWriter filewriter = null;  
        try {  
            br = new BufferedReader(new InputStreamReader(  
                    new FileInputStream(f), "utf-8"));  
            filewriter = new FileWriter(file, false);  
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
 
            System.out.println(sd.format(new Date()));  
            String tempString = null;  
            int i = 0;  
            while ((tempString = br.readLine()) != null) {  
                if (i < starRow-1) {  
                    i++;  
                    continue;  
                }  
                if(tempString.trim().equals(""))  
                    break;  
                if (StringUtil.contains(tempString, "\"")) {  
                    tempString = deepParser(tempString,filePath);  
                } else 
                    tempString = StringUtil.replace(tempString, ",", "|");  
//              System.out.println(tempString);  
                filewriter.write(stringTrim(tempString, "\\|") + "\r\n");  
                i++;  
            }  
            System.out.println(sd.format(new Date()));  
        } catch (Throwable e) {  
            log.warn("解析文件：【" + filePath + "】出错", e);  
            e.printStackTrace();  
        } finally {  
            try {  
                br.close();  
                filewriter.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
 
    }  
 
    public static String deepParser(String str,String filePath) {  
        System.out.println(str);  
        String temp = str;  
                          str = str+",";  
        StringBuffer sb = new StringBuffer();  
        try {  
            int from = 0;  
            int end = str.length();  
            int i = 0;  
            while (StringUtil.contains((str = str.substring(from)), "\"")) {  
                from = str.indexOf("\"");  
                end = str.indexOf("\"", from + 1);  
                sb.append(StringUtil.replace(str.substring(0, from), ",", "|"));  
                sb.append(str.substring(from + 1, end));  
                from = end + 1;  
                i++;  
            }  
            sb.append(StringUtil.replace(str, ",", "|"));  
        } catch (Throwable e) {  
            log.warn("解析文件：【" + filePath + "】出错,一下数据有问题："+temp, e);  
            e.printStackTrace();  
        }  
                       String s = sb.toString();  
             s = s.substring(0, s.lastIndexOf("|"));  
             return s;  
    }  
 
 
    //去除字段2边空格，可以指定分隔符  
    public static String stringTrim(String str, String regex) {  
        str = str+" ";  
        String[] strs = str.split(regex);  
        StringBuffer sb = new StringBuffer();  
 
        for (int i = 0; i < strs.length; i++) {  
            sb.append(strs[i].trim() + "|");  
        }  
 
        return sb.toString().substring(0, sb.toString().lastIndexOf("|"));  
    } 

 

MYSQL批量插入

 

插入：

MySql提供了可以一次插入多条数据的用法：

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12)...; 

在程序中可以通过循环，添加Values对应的列表，最后使用一次executeUpdate完成插入操作

。
但是Mysql语句并不是越长越好，MYsql语句长度有限制，可以查看mysql的配置文件my.in中

max_allowed_packet属性，并进行相应设置。
在程序中，使用循环拼接sql语句，然后一次性丢给myql_query()处理即可。需要注意的是，

sql语句不是越长越好，太长了可能会超出mysql限制（通过show varables查看like

max_allowed_packet）。
我在实测中，我限制sql长度在10M,例如:
for(...)
{
    m_strSQL += ...;
    if (m_strSQL.length() > 10485760) //10M
    {
         m_pMysqlStatement->execute(m_strSQL.substr(0, m_strSQL.length() - 1));
         m_strSQL= "INSERT INTO location (locationid, name, alternatename) VALUES ";
    }
}

在table1中执行insert后，立即执行另一条语句：“select @@IDENTITY;”；该语句会返回最后

插入的那条记录的ID，这样问题就解决了。后来发现mysql提供了内部函数LAST_INSERT_ID

()，我认为比“select @@IDENTITY;”更合理。

更新：

方法1:
Mysql中没有提供像Insert一样一次更新多条记录，需要逐条语句拼接。
update weibo set userName = 'xyw' where id = '22';update weibo set userID = '143' where

id = '35'; 
你可以使用addBatch语句，将拼接起来的SQL语句然后一次性处理即可。，但是效率并不高。
还可以在调用批处理使用线程(注意线程调用的方法要使用同步关键字),即每个线程执行部分

批处理语句

方法2:
replace into 或者insert into ...on duplicate key update

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

或者使用
insert into test_tbl (id,dr) values  (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values

(dr);

方法3:
创建临时表，先更新临时表，然后从临时表中update
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。
replace into  和insert into on duplicate key update的不同在于：
replace into　操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字

段置为缺省值

insert into 则是只update重复记录，不会改变其它字段。

方法3不建临时表:
可以使用另一种方法执行批量更新。针对更新记录，仍然使用insert语句，不过限制主键重复

时，更新字段。如下：
INSERT INTO tbl_name [col_name1, col_name2,...)]
VALUES(col_value1,col_value2,...), (col_value1,col_value2,...)
ON DUPLICATE KEY UPDATE userName=VALUES(userName) 

使用这种方法必须满足条件：col_name1, col_name2,...中必须有主键(可以是联合主键)或者

唯一键。

userName是要更新的列。
如果想一次更新多列，可以在userName=VALUES(userName)后面继续添加，例如：
INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES(col_value1,col_value2,...),

(col_value1,col_value2,...)ON DUPLICATE KEY UPDATE userName=VALUES(userName),

userID = VALUES(userID) 
这样就可以同时更新userName和userID两个字段。

它的实现原理是，首先Mysql根据表名后面列出的主键，查找表（因为是主键，所以在表中唯

一存在）。如果存在该行数据，则按照最后的col_name = values(col_name)列表对相应的字

段，按照values列表中给出的值进行更新。建议：表名后面的字段列表，除了主键之外，列出

来的最好都作为更新的对象，即在语句最后都要有相应的col_name = values(col_name)，否

则，你在表名后罗列出来字段，在values中赋值了，但是不是更新的对象，显然是浪费。

如果不存在该行数据，则进行插入操作，没有作为更新对象的列按照默认值填充（前提是

Mysql运行在非严格模式下。如果在严格模式下，没列都需要有默认值，否则运行出错）。

注意：
主键可以作为更新的对象 ，但是只是在表中不存在该记录时起作用，即执行了插入操作，如

果表中已经存在了该主键对应的行数据，下次更新时不会再插入该行，而是执行除了主键之

外的其他列的更新操作。所以最好不要将主键设置为更新的对象。
实例：

INSERT INTO keywordtable(id, keyword, userName, userID)
VALUES(1, '你好', 'Eliot', 22), (2, 'hello', 'Jhon', 23),(3, '嘻嘻', 'Jim', 24)
ON DUPLICATE KEY UPDATE
keyword=VALUES(keyword),
userName=VALUES(userName),
userID=VALUES(userID); 

除了id外，字段有keyword, userName, userID，他们是要更新的字段。

问题1:可以用代码手动删除更改主键,完成后再还原
mysql表字段添加主键：
alter table user add primary key(id);
alter table user change id id int(10) not null auto_increment;

mysql删除表字段主键：
alter table user change id id int(10); //先删除自增长
alter table user drop primary key; //再删除主键

有时候，为了代码灵活，便于修改，要直接向IBatis传入完整的SQL语句。ibatis map 文件如

下(注意这儿$不能用#替代 否则会出现异常 个人理解这儿没有能完整预编绎的SQL固定语句 )

：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"

"http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="bbs_define">
    <!– selectBySql –>
    <select id="selectBySql" resultClass="java.util.HashMap"
        remapResults="true"
        parameterClass="java.util.HashMap">
        <isNotEmpty property="sql">$sql$</isNotEmpty>
    </select>   
    <!– updateBySql –>
    <update id="updateBySql" parameterClass="java.util.HashMap">
        <isNotEmpty property="sql">$sql$</isNotEmpty>
    </update>   
</sqlMap>

其中最重要的设置是“remapResults="true"”。remapResults设置成true，表示结果字段可以

是不定的。也就是说，这次可返回“ID,NAME”两个字段，下次何返回“ID,NAME,TYPE_ID”三个

字段，也可以返回“*”

 

问题2：在一条sql语句中只能更新某些指定的字段，而程序中可能会有不同的更新条件。比如

：上一条记录只更新languages字段，而下一条记录则只更新postalcode字段，这样是没办法

蟹岛一条sql语句中的。

解决办法是：针对这两个不同的字段，使用不同的sql语句，保证每个sql语句只更新某个字段

：
m_strSQL_Language = "INSERT INTO location (locationid, languages) VALUES ";
m_strSQL_Postcode = "INSERT INTO location (locationid, postalcode) VALUES ";
for(...)
{
    m_strSQL_Language +=...
    m_strSQL_Postcode +=...
    if (m_strSQL_Language.length() > 10485760) //10M
    {       
        m_strSQL_Language += " ON DUPLICATE KEY UPDATE languages=VALUES

(languages)";
       
       execute(m_strSQL_Language);//执行SQL

        m_strSQL_Language = "INSERT INTO location (locationid, languages) VALUES ";
    }

    if (m_strSQL_Postcode.length() > 10485760) //10M
    {
        m_strSQL_Postcode += " ON DUPLICATE KEY UPDATE postalcode= VALUES

(postalcode)";
       
        execute(m_strSQL_Postcode);//执行SQL

        m_strSQL_Postcode = "INSERT INTO location (locationid, postalcode) VALUES ";
    }
}

 

ibatis常用16条SQL语句

 

(1) 输入参数为单个值

Xml代码  <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"  
parameterClass="long">  
delete from  
MemberAccessLog  
where  
accessTimestamp = #value#  
</delete>  

 
(2) 输入参数为一个对象
Xml代码 
<insert id="com.fashionfree.stat.accesslog.MemberAccessLog.insert"  
parameterClass="com.fashionfree.stat.accesslog.model.MemberAccessLog>  
insert into MemberAccessLog  
(  
accessLogId, memberId, clientIP,  
httpMethod, actionId, requestURL,  
accessTimestamp, extend1, extend2,  
extend3  
)  
values  
(  
#accessLogId#, #memberId#,  
#clientIP#, #httpMethod#,  
#actionId#, #requestURL#,  
#accessTimestamp#, #extend1#,  
#extend2#, #extend3#  
)  
</insert>  

 

(3) 输入参数为一个java.util.HashMap
Xml代码 
<select id="com.fashionfree.stat.accesslog.selectActionIdAndActionNumber"  
parameterClass="hashMap"  
resultMap="getActionIdAndActionNumber">  
select  
actionId, count(*) as count  
from  
MemberAccessLog  
where  
memberId = #memberId#  
and accessTimestamp &gt; #start#  
and accessTimestamp &lt;= #end#  
group by actionId  
</select> 
 

  (4) 输入参数中含有数组
Xml代码 
<insert id="updateStatusBatch" parameterClass="hashMap">  
update  
Question  
set  
status = #status#  
<dynamic prepend="where questionId in">  
<isNotNull property="actionIds">  
<iterate property="actionIds" open="(" close=")" conjunction=",">  
#actionIds[]#  
</iterate>  
</isNotNull>  
</dynamic>  
</insert>  

   说明：actionIds为传入的数组的名字；
   使用dynamic标签避免数组为空时导致sql语句语法出错；
   使用isNotNull标签避免数组为null时ibatis解析出错
 

   (5)传递参数只含有一个数组
Xml代码 
<select

id="com.fashionfree.stat.accesslog.model.StatMemberAction.selectActionIdsOfModule"  
resultClass="hashMap">  
select  
moduleId, actionId  
from  
StatMemberAction  
<dynamic prepend="where moduleId in">  
<iterate open="(" close=")" conjunction=",">  
#[]#  
</iterate>  
</dynamic>  
order by  
moduleId  
</select>  

    说明：注意select的标签中没有parameterClass一项
       另：这里也可以把数组放进一个hashMap中，但增加额外开销，不建议使用

 

   (6)让ibatis把参数直接解析成字符串
Xml代码 
<select id="com.fashionfree.stat.accesslog.selectSumDistinctCountOfAccessMemberNum"  
parameterClass="hashMap" resultClass="int">  
select  
count(distinct memberId)  
from  
MemberAccessLog  
where  
accessTimestamp &gt;= #start#  
and accessTimestamp &lt; #end#  
and actionId in $actionIdString$  
</select> 
    说明：使用这种方法存在sql注入的风险，不推荐使用
 

    (7)分页查询 (pagedQuery)
Java代码 
<select id="com.fashionfree.stat.accesslog.selectMemberAccessLogBy"  
parameterClass="hashMap" resultMap="MemberAccessLogMap">  
<include refid="selectAllSql"/>  
<include refid="whereSql"/>  
<include refid="pageSql"/>  
</select>  
<select id="com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count"  
parameterClass="hashMap" resultClass="int">  
<include refid="countSql"/>  
<include refid="whereSql"/>  
</select>  
<sql id="selectAllSql">  
select  
accessLogId, memberId, clientIP,  
httpMethod, actionId, requestURL,  
accessTimestamp, extend1, extend2,  
extend3  
from  
MemberAccessLog  
</sql>  
<sql id="whereSql">  
accessTimestamp &lt;= #accessTimestamp#  
</sql>  
<sql id="countSql">  
select  
count(*)  
from  
MemberAccessLog  
</sql>  
<sql id="pageSql">  
<dynamic>  
<isNotNull property="startIndex">  
<isNotNull property="pageSize">  
limit #startIndex# , #pageSize#  
</isNotNull>  
</isNotNull>  
</dynamic>  
</sql>  

   说明：本例中，代码应为：
   HashMap hashMap = new HashMap();
   hashMap.put(“accessTimestamp”, someValue);
   pagedQuery(“com.fashionfree.stat.accesslog.selectMemberAccessLogBy”, hashMap);
   pagedQuery方法首先去查找名为

com.fashionfree.stat.accesslog.selectMemberAccessLogBy.Count 的mapped statement来进

行sql查询，从而得到com.fashionfree.stat.accesslog.selectMemberAccessLogBy查询的记录个

数，
再进行所需的paged sql查询(com.fashionfree.stat.accesslog.selectMemberAccessLogBy)，具

体过程参见utils类中的相关代码

 

(8)sql语句中含有大于号>、小于号<
    1. 将大于号、小于号写为： &gt; &lt; 如：
Xml代码 
<delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"

parameterClass="long">  
delete from  
MemberAccessLog  
where  
accessTimestamp &lt;= #value#  
</delete>  
 

    2. 将特殊字符放在xml的CDATA区内：
Xml代码 
<delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLogsBefore"

parameterClass="long">  
<![CDATA[ 
delete from 
MemberAccessLog 
where 
accessTimestamp <= #value# 
]]>  
</delete>  

   推荐使用第一种方式，写为&lt; 和 &gt; (XML不对CDATA里的内容进行解析，因此如果

CDATA中含有dynamic标签，将不起作用)

(9)include和sql标签
   将常用的sql语句整理在一起，便于共用：
Xml代码 
<sql id="selectBasicSql">  
select  
samplingTimestamp,onlineNum,year,  
month,week,day,hour  
from  
OnlineMemberNum  
</sql>  
<sql id="whereSqlBefore">  
where samplingTimestamp &lt;= #samplingTimestamp#  
</sql>  
<select

id="com.fashionfree.accesslog.selectOnlineMemberNumsBeforeSamplingTimestamp"

parameterClass="hashmap" resultClass="OnlineMemberNum">  
<include refid="selectBasicSql" />  
<include refid="whereSqlBefore" />  
</select>  

    注意：sql标签只能用于被引用，不能当作mapped statement。如上例中有名为

selectBasicSql的sql元素，试图使用其作为sql语句执行是错误的:
    sqlMapClient.queryForList(“selectBasicSql”); ×

(10)随机选取记录
Xml代码 
<sql id=”randomSql”>  
ORDER BY rand() LIMIT #number#  
</sql>  

    从数据库中随机选取number条记录（只适用于MySQL）
 

(11)将SQL GROUP BY分组中的字段拼接
Xml代码 
<sql id=”selectGroupBy>  
SELECT  
a.answererCategoryId, a.answererId, a.answererName,  
a.questionCategoryId, a.score, a.answeredNum,  
a.correctNum, a.answerSeconds, a.createdTimestamp,  
a.lastQuestionApprovedTimestamp, a.lastModified, GROUP_CONCAT(q.categoryName) as

categoryName  
FROM  
AnswererCategory a, QuestionCategory q  
WHERE a.questionCategoryId = q.questionCategoryId  
GROUP BY a.answererId  
ORDER BY a.answererCategoryId  
</sql> 

    注：SQL中使用了MySQL的GROUP_CONCAT函数

(12) 按照IN里面的顺序进行排序

    ①MySQL:
Xml代码 
<sql id=”groupByInArea”>  
select  
moduleId, moduleName,  
status, lastModifierId, lastModifiedName,  
lastModified  
from  
StatModule  
where  
moduleId in (3, 5, 1)  
order by  
instr(',3,5,1,' , ','+ltrim(moduleId)+',')  
</sql>      

②SQLSERVER:
Xml代码 
<sql id=”groupByInArea”>  
select  
moduleId, moduleName,  
status, lastModifierId, lastModifiedName,  
lastModified  
from  
StatModule  
where  
moduleId in (3, 5, 1)  
order by  
charindex(','+ltrim(moduleId)+',' , ',3,5,1,')  
</sql> 

    说明：查询结果将按照moduleId在in列表中的顺序(3, 5, 1)来返回
    MySQL : instr(str, substr)
    SQLSERVER: charindex(substr, str)
    返回字符串str 中子字符串的第一个出现位置
    ltrim(str)
    返回字符串str, 其引导(左面的)空格字符被删除

(13) resultMap
    resultMap负责将SQL查询结果集的列值映射成Java Bean的属性值。

Xml代码  <resultMap class="java.util.HashMap" id="getActionIdAndActionNumber">  
<result column="actionId" property="actionId" jdbcType="BIGINT" javaType="long"/>  
<result column="count" property="count" jdbcType="INT" javaType="int"/>  
</resultMap>  

   使用resultMap称为显式结果映射，与之对应的是resultClass(内联结果映射)，使用

resultClass的最大好处便是简单、方便，不需显示指定结果，由iBATIS根据反射来确定自行决

定。而resultMap则可以通过指定jdbcType和javaType，提供更严格的配置认证。

(14) typeAlias
Xml代码 
<typeAlias alias="MemberOnlineDuration"

type="com.fashionfree.stat.accesslog.model.MemberOnlineDuration" />  
<typeAlias>允许你定义别名，避免重复输入过长的名字。 

 

(15) remap
Xml代码 
<select id="testForRemap" parameterClass="hashMap" resultClass="hashMap"

remapResults="true">  
select  
userId  
<isEqual property="tag" compareValue="1">  
, userName  
</isEqual>  
<isEqual property="tag" compareValue="2">  
, userPassword  
</isEqual>  
from  
UserInfo  
</select>  

   此例中，根据参数tag值的不同，会获得不同的结果集，如果没有remapResults="true"属性

，iBatis会将第一次查询时的结果集缓存，下次再执行时（必须还是该进程中）不会再执行结

果集映射，而是会使用缓存的结果集。
因此，如果上面的例子中remapResult为默认的false属性，而有一段程序这样书写：

Java代码 
HashMap<String, Integer> hashMap = new HashMap<String, Integer>();  
hashMap.put("tag", 1);  
sqlClient.queryForList("testForRemap", hashMap);  
hashMap.put("tag", 2);  
sqlClient.queryForList("testForRemap", hashMap);  

 则程序会在执行最后一句的query查询时报错，原因就是iBATIS使用了第一次查询时的结果集

，而前后两次的结果集是不同的:(userId, userName)和(userId, userPassword)，所以导致出

错。如果使用了remapResults="true"这一属性，iBATIS会在每次执行查询时都执行结果集映

射，从而避免错误的发生（此时会有较大的开销）。

(16) dynamic标签的prepend
  dynamic标签的prepend属性作为前缀添加到结果内容前面，当标签的结果内容为空时，

prepend属性将不起作用。
当dynamic标签中存在prepend属性时，将会把其嵌套子标签的第一个prepend属性忽略。例如

：
<sql id="whereSql">  
<dynamic prepend="where ">  
<isNotNull property="userId" prepend="BOGUS">  
userId = #userId#  
</isNotNull>  
<isNotEmpty property="userName" prepend="and ">  
userName = #userName#  
</isNotEmpty>  
</dynamic>  
</sql> 

此例中，dynamic标签中含有两个子标签<isNotNull>和<isNotEmpty>。根据前面叙述的原则

，如果<isNotNull>标签中没有prepend="BOGUS" 这一假的属性来让dynamic去掉的话，

<isNotEmpty>标签中的and就会被忽略，会造成sql语法错误。
   注意：当dynamic标签没有prepend属性时，不会自动忽略其子标签的第一个prepend属性。