[MyBatis]完整MyBatis CRUD工程

下载地址：https://files.cnblogs.com/files/xiandedanteng/Person191005.rar

pom.xml:这个文件主要是引入依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.hy</groupId>
  <artifactId>fillMillionDatum</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>fillMillionDatum</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.3.0</version>
    </dependency>
    
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
    
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.12</version>
    </dependency>
    
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.12</version>
    </dependency>
    
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
  </dependencies>
</project>

mybatis-config.xml:这个文件主要配了DB连接信息和需要引入的Mapper

<?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>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://192.168.161.128:3306/test" />
                <property name="username" value="root" />
                <property name="password" value="12345678" />
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="PersonMapper.xml"></mapper>
    </mappers>
</configuration>

PersonMapper.xml:这里定义进行CRUD的多条sql语句

<?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.hy.PersonMapper">

    <select id="selectAll" resultType="com.hy.Person">
        select id,name,enrollTime from person
    </select>
    
    <select id="insertPerson">
        insert into person(id,name,enrollTime) values (#{id},#{name},#{enrolltime})
    </select>
    
    <select id="selectOneById" resultType="com.hy.Person">
        select id,name,enrollTime from person where id=#{id}
    </select>
    
    <update id="updatePersonNameById"  parameterType="java.util.HashMap">
        update person set name=#{name} where id=#{id}
    </update>
    
    <delete id="deletePersonsMonthsAgo"  parameterType="java.util.HashMap">
        delete from person where enrolltime&lt;#{date}
    </delete>
</mapper>

PersonMapper接口 这个和PersonMapper文件是对应的，可以把调DB的函数写在这里，也可以在代码里用id来调用。

package com.hy;

public interface PersonMapper {
    Person selectOneById(long id);
    int updatePersonNameById(String name,long id);
    int deletePersonsMonthsAgo(String date);
}

Person类：实体类

package com.hy;

import java.text.MessageFormat;

public class Person {
    private long id;
    private String name;
    private String enrolltime;
    
    public String toString() {
        Object[] arr={id,name,enrolltime};
        String retval=MessageFormat.format("Person id={0},name={1},enrolltime={2}", arr);
        return retval;
    }
    
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEnrolltime() {
        return enrolltime;
    }
    public void setEnrolltime(String enrolltime) {
        this.enrolltime = enrolltime;
    }
}

SelectAllPerson：全选方案

package com.hy;

import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;

public class SelectAllPerson {
    private static Logger logger = Logger.getLogger(SelectAllPerson.class);
    
    public static void main(String[] args) throws Exception{
        long startTime = System.currentTimeMillis();
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        try {
            List<Person> list=session.selectList("selectAll");
            
            for(Person emp:list) {
                System.out.println(emp);
            }
            
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
        }finally {
            session.close();
        }
    }
    
    // format seconds to day hour minute seconds style
    // Example 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

SelectOnePerson：按ID单找一个的方案

package com.hy;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;

public class SelectOnePerson {
    private static Logger logger = Logger.getLogger(SelectOnePerson.class);
    
    public static void main(String[] args) throws Exception{
        long startTime = System.currentTimeMillis();
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        try {
            PersonMapper pm=session.getMapper(PersonMapper.class);
            Person p=pm.selectOneById(9);
            logger.info("Selected person="+p);
            
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
        }finally {
            session.close();
        }
    }
    
    // format seconds to day hour minute seconds style
    // Example 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

UpdatePersonName：按ID更新一个人名字的方案

package com.hy;

import java.io.Reader;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;

public class UpdatePersonName {
    private static Logger logger = Logger.getLogger(UpdatePersonName.class);
    
    public static void main(String[] args) throws Exception{
        long startTime = System.currentTimeMillis();
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        try {
            Map<String, String> para=new HashMap<String, String>();
            para.put("id","1");
            para.put("name","Andy");
            
            int changed=session.update("updatePersonNameById", para);
            session.commit();
            
            logger.info(changed+" record was changed.");
            
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
        }catch(Exception ex) {
            session.rollback();
        }finally {
            session.close();
        }
    }
    
    // format seconds to day hour minute seconds style
    // Example 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

DeletePersonMonthAgo：按日期删除一批人的方案

package com.hy;

import java.io.Reader;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;

public class DeletePersonMonthAgo {
    private static Logger logger = Logger.getLogger(DeletePersonMonthAgo.class);
    
    public static void main(String[] args) throws Exception{
        long startTime = System.currentTimeMillis();
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        try {
            Map<String, String> para=new HashMap<String, String>();
            para.put("date","2020-01-01 00:01:00");
            
            int changed=session.delete("deletePersonsMonthsAgo", para);
            session.commit();
            
            logger.info(changed+" record was changed.");
            
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
        }catch(Exception ex) {
            session.rollback();
        }finally {
            session.close();
        }
    }
    
    // format seconds to day hour minute seconds style
    // Example 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

InsertMillionPersons：插入六百四十万条Person记录（测试用，在我的T440p的虚拟机上的CentOS6.5上的MySql数据库耗时约一小时）

package com.hy;

import java.io.Reader;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;


public class InsertMillionPersons {
    private static Logger logger = Logger.getLogger(InsertMillionPersons.class);
    
    public static void main(String[] args)  throws Exception{
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        long startTime = System.currentTimeMillis();
        
        try {
                String currTime="2018-01-01 00:00:00";
            
            for(long i=0;i<6400000;i++) {
                currTime=timePastTenSecond(currTime);
                
                   Person emp=new Person();
                 emp.setId(i);
                 emp.setName("Person:"+String.valueOf(i));
                 emp.setEnrolltime(currTime);
                 
                 int changed=session.insert("insertPerson", emp);
                 logger.info("No."+i+" changed="+changed+" time:"+currTime);
            }
                        
            session.commit();
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
        }catch(Exception ex) {
            session.rollback();
        }finally {
            session.close();
        }
    }
    
    // 得到十秒后的时间
    public static String timePastTenSecond(String otime) {
        try {
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date dt=sdf.parse(otime);
            
            Calendar newTime = Calendar.getInstance();
            newTime.setTime(dt);
            newTime.add(Calendar.SECOND,10);//日期加10秒
            
            Date dt1=newTime.getTime();
            String retval = sdf.format(dt1);
            
            return retval;
        }
        catch(Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    // format seconds to day hour minute seconds style
    // Exmplae 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

InsertPersons：插入数条Person记录

package com.hy;

import java.io.Reader;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;


public class InsertPersons {
    private static Logger logger = Logger.getLogger(InsertPersons.class);
    
    public static void main(String[] args)  throws Exception{
        Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
        
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(reader);
        reader.close();
        
        SqlSession session=ssf.openSession();
        
        long startTime = System.currentTimeMillis();
        
        try {
                String currTime="2018-01-01 00:00:00";
            
            for(long i=0;i<10;i++) {
                currTime=timePastTenSecond(currTime);
                
                   Person emp=new Person();
                 emp.setId(i);
                 emp.setName("Person:"+String.valueOf(i));
                 emp.setEnrolltime(currTime);
                 
                 int changed=session.insert("insertPerson", emp);
                 logger.info("No."+i+" changed="+changed+" time:"+currTime);
                 
            }
                        
            session.commit();
            long endTime = System.currentTimeMillis();
            logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");
            logger.info("Insert 10 persons completed.");
        }catch(Exception ex) {
            session.rollback();
        }finally {
            session.close();
        }
    }
    
    public static String timePastTenSecond(String otime) {
        try {
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date dt=sdf.parse(otime);
            
            Calendar newTime = Calendar.getInstance();
            newTime.setTime(dt);
            newTime.add(Calendar.SECOND,10);//日期加10秒
            
            Date dt1=newTime.getTime();
            String retval = sdf.format(dt1);
            
            return retval;
        }
        catch(Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    // format seconds to day hour minute seconds style
    // Exmplae 5000s will be formatted to 1h23m20s
    public static String toDhmsStyle(long allSeconds) {
        String DateTimes = null;
        
        long days = allSeconds / (60 * 60 * 24);
        long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);
        long minutes = (allSeconds % (60 * 60)) / 60;
        long seconds = allSeconds % 60;
        
        if (days > 0) {
            DateTimes = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            DateTimes = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            DateTimes = minutes + "m" + seconds + "s";
        } else {
            DateTimes = seconds + "s";
        }

        return DateTimes;
    }
}

 

使用这个程序，往一张Person表中插6,,400,000条记录花费约59分，删除3，153，605条记录（2019年1月1日之前）约用41秒，删除3153600条记录(2020年1月1日之前)约用49秒。

为什么在远程Oracle数据库上删花费时间要长的多？

--END-- 2019年10月5日20:39:39