今日完成

8: 使用navicat备份数据库到sql文件

我选择了备份结构和数据，保存sql文件到本地. Database -> Dump SQL File -> Structure + Data

然后删除数据 使用了sql语句 DELETE FROM enroll_application WHERE id=0;

最后使用SQL file恢复数据 Data -> Execute SQL File

昨天把数据库建立起来了。今天首先要测试下有无index情况下数据CRUD的效率。

第一次执行时，中文输入出现乱码，解决方案是在jdbc连接string上加了characterEncoding=utf8

因为我SELECT使用的是where student_name = "00706b38-bdf8-4b1c-a5a0-f3ccb1284ffa" (student_name 索引了）student_name是unique的，所以hash可能比btree更高效一点。无论如何，没有index的效率非常低。

感觉MySQL优化还要进一步学习，插10000条使用90多秒有点不能接受。

使用maven创建项目然后在pom.xml中引入dependencies

<dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

<version>4.12</version>

</dependency>

<dependency>

    <groupId>log4j</groupId>

    <artifactId>log4j</artifactId>

    <version>1.2.17</version>

</dependency>

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>5.1.6</version>

</dependency>

<dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-context</artifactId>

<version>4.3.8.RELEASE</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->

<dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-jdbc</artifactId>

<version>4.3.8.RELEASE</version>

</dependency>

<dependency>

<groupId>org.mybatis</groupId>

<artifactId>mybatis</artifactId>

<version>3.4.4</version>

</dependency>

<dependency>

<groupId>org.mybatis</groupId>

<artifactId>mybatis-spring</artifactId>

<version>1.3.1</version>

</dependency>

我把默认的junit3.8换成了4.12，使用maven测试的时候总是run：0测试。。最后发现，应该是第一个测试就没过，所以run0测试。一个个测试过之后，显示run7测试。

写了DAO的interface如下

package com.xiuzhen.enrollment.dal.enrolldata;

import java.util.List;

import com.xiuzhen.enrollment.pojo.EnrollData;

public interface EnrollDataDAO {

    EnrollData selectById(int enrollDataId);

    EnrollData selectByOnlineId(String onlineId);

    List<EnrollData> selectAll();

    List<EnrollData> selectByRealName(String realname);

    boolean insert(EnrollData ed);

    boolean update(EnrollData ed);

    boolean delete(EnrollData ed);}

｝

使用MyBatis，使用的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.xiuzhen.enrollment.dal.enrolldata.EnrollDataMapper'>

    <resultMap type="com.xiuzhen.enrollment.pojo.EnrollData" id="EnrollDataResult">

        <id property="enrollDataId" column="id"/>

        <result property="created_at" column="created_at"/>

        <result property="updated_at" column="updated_at"/>

        <result property="realname" column="realname"/>

        <result property="qq" column="qq"/>

        <result property="school" column="school"/>

        <result property="tutor" column="tutor"/>

        <result property="online_id" column="online_id"/>

        <result property="study_note_link" column="study_note_link"/>

        <result property="expected_start_date" column="expected_start_date"/>

        <result property="study_aoth" column="study_aoth"/>

        <result property="heard_us_from" column="heard_us_from"/>

    </resultMap>

    <select id='selectById' parameterType='int' resultMap="EnrollDataResult">

        select * from enroll where id=#{enrollDataId}

    </select>

    <select id='selectByOnlineId' parameterType='string' resultMap="EnrollDataResult">

        select * from enroll where online_id=#{onlineId}

    </select>

    <select id='selectByRealname' parameterType='string' resultMap="EnrollDataResult">

        select * from enroll where realname=#{realname}

    </select>

    <select id='selectAll'  resultMap="EnrollDataResult">

        select * from enroll

    </select>

    <insert id='insert' parameterType='com.xiuzhen.enrollment.pojo.EnrollData'>

        insert into enroll (created_at, updated_at, realname, qq, school, 

        tutor, online_id, study_note_link, study_aoth, 

        expected_start_date, heard_us_from)

        values  

        (#{created_at}, #{updated_at}, #{realname}, #{qq}, #{school}, 

        #{tutor}, #{online_id}, #{study_note_link}, #{study_aoth}, 

        #{expected_start_date}, #{heard_us_from})

    </insert>

    <update id="update" parameterType="com.xiuzhen.enrollment.pojo.EnrollData">

        update enroll set

         created_at=#{created_at}, 

         updated_at=#{updated_at}, 

         realname=#{realname}, 

         qq=#{qq}, 

         school=#{school}, 

         tutor=#{tutor}, 

         online_id=#{online_id}, 

         study_note_link=#{study_note_link}, 

         study_aoth=#{study_aoth}, 

         expected_start_date=#{expected_start_date}, 

         heard_us_from=#{heard_us_from}

         where id=#{enrollDataId}

    </update>

    <delete id="delete" parameterType="com.xiuzhen.enrollment.pojo.EnrollData">

        delete from enroll where id=#{enrollDataId}

    </delete>

</mapper>

使用Spring用setter injection的方式给DAOImpl注入了MyBatis的mapper。如下：

package com.xiuzhen.enrollment.dal.enrolldata;

import java.util.List;

import com.xiuzhen.enrollment.pojo.EnrollData;

public class EnrollDataDAOImpl implements EnrollDataDAO{

private EnrollDataMapper enrollDataMapper = null;

public void setEnrollDataMapper(EnrollDataMapper enrollDataMapper) {

this.enrollDataMapper = enrollDataMapper;

}

public EnrollData selectById(int enrollDataId) {

return enrollDataMapper.selectById(enrollDataId);

}

public EnrollData selectByOnlineId(String onlineId) {

if (onlineId == null || onlineId.length() == 0)

return null;

return enrollDataMapper.selectByOnlineId(onlineId);

}

public List<EnrollData> selectAll() {

return enrollDataMapper.selectAll();

}

public List<EnrollData> selectByRealName(String realname) {

if (realname == null || realname.length() == 0)

return null;

return enrollDataMapper.selectByRealname(realname);

}

public boolean insert(EnrollData ed) {

if (ed == null)

return true;

try { 

int result = enrollDataMapper.insert(ed);

if (result > 0)

return true;

else 

return false;

} catch (Exception e) {

// e.printStackTrace();

return false;

}

}

public boolean update(EnrollData ed) {

if (ed == null)

return true;

try { 

int result = enrollDataMapper.update(ed);

if (result > 0)

return true;

else 

return false;

} catch (Exception e) {

// e.printStackTrace();

return false;

}

}

public boolean delete(EnrollData ed) {

if (ed == null)

return true;

if (enrollDataMapper.delete(ed) > 0)

return true;

else

return false;

}

}

然后写测试class。

今天遇到的几个小坑，一个是连接mysql需要使用characterEncoding=utf8,这样存在mysql中的中文不会发生乱麻

Mybatis的mapper如果出错会直接抛异常，所以在DAO class中使用try catch来catch异常，要不然runtime出错就麻烦了。

明天的任务。

还需要学习下log4j如何写log，加入到code 中。

把maven项目deploy到网上的

基本这个项目就完成了，争取明天做完。