为什么 mysql 存储过程比 mybatis 插入数据快 50 多倍

[inter18099]

我任务是将一个表中的数据处理后插入另外一个表。

用 mybatis 花了 7 个小时，用存储过程 7 分钟就插完了。谁知道是为什么呀？

附我的程序：

Mybatis：

public class CreateStation extends Thread{

public void run() {

System.out.println("abc");
//System.out.println("UUID One: " + uniqueID);
//System.out.println("UUID Two: " + uniqueID);
System.out.println("abc");
SqlSessionFactory sqlSessionFactory = SqlSessionFactorySingleton.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
CreateStationMapper mapper = sqlSession.getMapper(CreateStationMapper.class);

List<Line> list2 = mapper.selectAllLines();


for(Line line : list2) {
sleep(1000);

// 顺程的站点
if(line != null && line.getShunLine() != null && line.getShunLine().trim().length() > 0) {
String[] stationArray = line.getShunLine().split(",");
for (int i = 0; i < stationArray.length; i++) {
// 检查该站点是否已经在 station 表中存在
// 如果已经存在，更新该站点的 lineNoList 字段
List<Station> stationWithSpecificNameAndCity = mapper.selectStationByNameAndCity(stationArray[i], line.getCity());
if (stationWithSpecificNameAndCity != null && stationWithSpecificNameAndCity.size() > 0) {
updateStationLineNoList(mapper, stationArray, line, sqlSession, i);
System.out.println("update one: " + i);
} else {
insertIntoStation(stationArray, mapper, line, i, sqlSession);
System.out.println("insert one: " + i);
}
}
}
// 逆程的站点
if(line != null && line.getNiLine() != null && line.getNiLine().trim().length() > 0) {
String[] stationArray2 = line.getNiLine().split(",");
for (int i = 0; i < stationArray2.length; i++) {
// 检查该站点是否已经在 station 表中存在
// 如果已经存在，更新该站点的 lineNoList 字段
List<Station> stationWithSpecificNameAndCity2 = mapper.selectStationByNameAndCity(stationArray2[i], line.getCity());
if (stationWithSpecificNameAndCity2 != null && stationWithSpecificNameAndCity2.size() > 0) {
updateStationLineNoList(mapper, stationArray2, line, sqlSession, i);
System.out.println("update one: " + i);
} else {
insertIntoStation(stationArray2, mapper, line, i, sqlSession);
System.out.println("insert one: " + i);
}
}
}
}
} catch(Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}

public static void main(String[] args) {
new CreateStation().start();
}

private static void updateStationLineNoList(CreateStationMapper mapper, String[] stationArray, Line line, SqlSession sqlSession, int i) {
String oldStationLineNoList = mapper.getStationLineNoList(stationArray[i], line.getCity());
boolean isNewLineNo = true;
if(oldStationLineNoList != null && oldStationLineNoList.trim().length() > 0) {
String[] oldStationLineNoListArray = oldStationLineNoList.split(",");
// 假设该 lineNo 在旧 lineNoList 里找不到，是新的 lineNo
for (int j = 0; j < oldStationLineNoListArray.length; j++) {
if (oldStationLineNoListArray[j].equals(line.getBusNo())) {
// 在旧 lineNoList 中已找到该 lineNo
isNewLineNo = false;
break;
}
}
}
if(isNewLineNo && stationArray[i] != null && stationArray[i].trim().length() > 0) {
mapper.updateStationLineNoList(stationArray[i], line.getCity(), oldStationLineNoList + "," + line.getBusNo());
sqlSession.commit();
}
}

private static void insertIntoStation(String[] stationArray, CreateStationMapper mapper, Line line, int i, SqlSession sqlSession) {

String name = stationArray[i];

String city = line.getCity();
String cityEn = line.getCityEn();
if(name != null && name.trim().length() > 0 && city != null && city.trim().length() > 0 && cityEn != null && cityEn.trim().length() > 0) {
mapper.insertIntoStation(name, line.getBusNo(), city, cityEn);
sqlSession.commit();
}
}
}




存储过程：
DELIMITER //
DROP PROCEDURE IF EXISTS createStation //
CREATE PROCEDURE createStation(cityParam varchar(255))
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE foundLine BOOLEAN DEFAULT 0;
DECLARE shunLineVar VARCHAR(2048);
DECLARE niLineVar VARCHAR(2048);
DECLARE cityEnVar VARCHAR(255);
DECLARE cityVar VARCHAR(255);
DECLARE busNoVar VARCHAR(255);
DECLARE lineNoListVar VARCHAR(2048);
DECLARE countOfStation INT DEFAULT NULL;
DECLARE oldLineNoListVar VARCHAR(2048);
-- for loop2
DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;
-- for loop2
-- for lineNoList loop
DECLARE _nextLine TEXT DEFAULT NULL;
DECLARE _nextlenLine INT DEFAULT NULL;
DECLARE _valueLine TEXT DEFAULT NULL;
-- for listNoList loop
DECLARE cur_line CURSOR FOR SELECT shunLine,niLine,city,cityEn,busNo FROM bus_line WHERE cityEn = cityParam;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur_line;
line_loop:LOOP
FETCH cur_line INTO shunLineVar, niLineVar, cityVar, cityEnVar, busNoVar;

IF(done = 1) THEN
LEAVE line_loop;
END IF;

INSERT INTO t1(c1) VALUES (shunLineVar);
stationShun:LOOP
IF LENGTH(TRIM(shunLineVar)) = 0 OR shunLineVar IS NULL THEN
LEAVE stationShun;
END IF;
SET _next = SUBSTRING_INDEX(shunLineVar,',',1);
-- SET _nextlen = LENGTH(_next);
SET _nextlen = CHAR_LENGTH(_next);
SET _value = TRIM(_next);
SET countOfStation = (SELECT count(*) FROM test_station WHERE name=_next AND cityEn=cityEnVar);
IF countOfStation = 0 THEN
INSERT INTO test_station(name,lineNoList,city,cityEn) VALUES(_next, busNoVar, cityVar, cityEnVar);
ELSE
SET lineNoListVar = (SELECT lineNoList FROM test_station WHERE name=_next AND cityEn=cityEnVar);
getLineNoList:LOOP
IF LENGTH(TRIM(lineNoListVar)) = 0 OR lineNoListVar IS NULL THEN
LEAVE getLineNoList;
END IF;
SET _nextLine = SUBSTRING_INDEX(lineNoListVar,',',1);
IF _nextLine = busNoVar THEN
SET foundLine = 1;
LEAVE getLineNoList;
END IF;
SET _nextlenLine = CHAR_LENGTH(_nextLine);
SET lineNoListVar = INSERT(lineNoListVar,1,_nextlenLine + 1,'');
END LOOP getLineNoList;
IF foundLine = 0 THEN
SET oldLineNoListVar = (SELECT lineNoList FROM test_station WHERE name=_next AND cityEn=cityEnVar);
UPDATE test_station SET lineNoList = CONCAT(oldLineNoListVar, ',', busNoVar) WHERE name=_next AND cityEn=cityEnVar;
END IF;
END IF;
INSERT INTO t1(c1) VALUES (_next);
INSERT INTO t1(c1) VALUES (_nextlen);
SET shunLineVar = INSERT(shunLineVar,1,_nextlen + 1,'');
END LOOP stationShun;
INSERT INTO t1(c1) VALUES("exit loop2");
END LOOP line_loop;
CLOSE cur_line;

END//

DELIMITER ;