Idea+Mybatis+Maven+单表增删改查

步骤1：在mysql中创建表

create table `account` (  `id` int(11) not null auto_increment,  `username` varchar(32) not null,  `pwd` varchar(64),  `email` varchar(64),  primary key (`id`),  unique key `unique_username` (`username`),  key `username_index` (`username`) USING BTree) engine=InnoDB default charset=utf8

步骤2：在IDEA下创建Maven项目并创建与表对应的实体类

往Maven项目添加Mybatis依赖的包，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/maven-v4_0_0.xsd">  <modelVersion>4.0.0</modelVersion>  <groupId>cn.hurrican</groupId>  <artifactId>conference</artifactId>  <packaging>war</packaging>  <version>1.0-SNAPSHOT</version>  <name>conference Maven Webapp</name>  <url>http://maven.apache.org</url>  <dependencies>    <dependency>      <groupId>junit</groupId>      <artifactId>junit</artifactId>      <version>4.9</version>      <scope>test</scope>    </dependency>    <dependency>      <groupId>org.mybatis</groupId>      <artifactId>mybatis</artifactId>      <version>3.4.1</version>    </dependency>    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->    <dependency>      <groupId>mysql</groupId>      <artifactId>mysql-connector-java</artifactId>      <version>5.1.42</version>    </dependency>    <!-- https://mvnrepository.com/artifact/org.ow2.asm/asm -->    <dependency>      <groupId>org.ow2.asm</groupId>      <artifactId>asm</artifactId>      <version>4.2</version>    </dependency>    <!-- https://mvnrepository.com/artifact/cglib/cglib -->    <dependency>      <groupId>cglib</groupId>      <artifactId>cglib</artifactId>      <version>3.1</version>    </dependency>    <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->    <dependency>      <groupId>commons-logging</groupId>      <artifactId>commons-logging</artifactId>      <version>1.2</version>    </dependency>    <!-- https://mvnrepository.com/artifact/log4j/log4j -->    <dependency>      <groupId>log4j</groupId>      <artifactId>log4j</artifactId>      <version>1.2.17</version>    </dependency>    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->    <dependency>      <groupId>org.apache.logging.log4j</groupId>      <artifactId>log4j-api</artifactId>      <version>2.7</version>    </dependency>    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->    <dependency>      <groupId>org.apache.logging.log4j</groupId>      <artifactId>log4j-core</artifactId>      <version>2.7</version>    </dependency>    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->    <dependency>      <groupId>org.slf4j</groupId>      <artifactId>slf4j-api</artifactId>      <version>1.7.21</version>    </dependency>    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->    <dependency>      <groupId>org.slf4j</groupId>      <artifactId>slf4j-log4j12</artifactId>      <version>1.7.21</version>      <scope>test</scope>    </dependency>  </dependencies>  <build>    <finalName>conference</finalName>    <plugins>      <plugin>        <artifactId>maven-compiler-plugin</artifactId>        <version>2.3.2</version>        <configuration>          <source>1.8</source>          <target>1.8</target>        </configuration>      </plugin>    </plugins>  </build></project>

项目mybatis.xml配置内容如下：

<?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>        <!--当返回行的所有列都是空时，MyBatis默认返回null-->        <setting name="jdbcTypeForNull" value="NULL"/>        <!--cacheEnabled - 使全局的映射器启用或禁用缓存-->        <setting name="cacheEnabled" value="true"/>    </settings>    <typeAliases>        <typeAlias type="beans.UserInfo" alias="UserInfo"></typeAlias>        <package name="beans"></package>    </typeAliases>    <!-- 配置运行环境-->    <environments default="mysql">        <environment id="mysql">            <!-- MyBatis 中有两种事务管理器类型，分别是：-->            <!--1. type = "JDBC" (依赖于数据源得到的连接来管理事务范围)-->            <!--2. type = "MANAGED" (不提交或回滚连接，让容器来管理事务的整个周期)-->            <transactionManager type="JDBC"/>            <dataSource type="POOLED">                <property name="driver" value="com.mysql.jdbc.Driver"/>                <property name="url" value="jdbc:mysql://localhost:3306/conference"/>                <property name="username" value="root"/>                <property name="password" value="qwer123456"/>                <!--poolMaximumActiveConnections – 正在使用连接的数量。默认值:10 -->                <!--poolMaximumIdleConnections – 任意时间存在的空闲连接数-->            </dataSource>        </environment>    </environments>    <mappers>        <mapper resource="userinfoMapper.xml"/>        <mapper resource="basicinfoMapper.xml"/>        <mapper resource="workinfoMapper.xml"/>    </mappers></configuration>

项目userinfoMapper.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="userdao">    <insert id="addUser" parameterType="UserInfo" useGeneratedKeys="true" keyProperty="id">        insert into account (username, pwd, email)        values (#{username}, #{pwd}, #{email})    </insert>    <insert id="addUserAndQuery" useGeneratedKeys="true" keyProperty="id">        insert into account (username, pwd, email)        values (#{username}, #{pwd}, #{email})        <selectKey resultType="int" keyProperty="id">            select @@identity;        </selectKey>    </insert>    <!-- 单表删除语句-->    <delete id="deleteUser" parameterType="long">        delete from account where id = #{id}    </delete>    <!-- 单表更新语句-->    <update id="updateUser">        update account set username = #{username}, email = #{email} where id = #{id}    </update>    <!-- 单表查询语句-->    <select id="queryById" resultType="UserInfo" parameterType="long">        select id, username, pwd, email from account where id = #{id}    </select>    <select id="queryAll" resultType="UserInfo">        select id, username, pwd, email from account    </select>    <!-- 模糊查询-->    <select id="queryByLike" resultType="UserInfo">        select id,username,email from account where email like concat('%', #{qq},'%')    </select>    <select id="queryNyLike1" resultType="UserInfo">        select id,username,email from account where email like '%${value}%'    </select></mapper>

创建实体类beans.UserInfo.java

package beans;public class UserInfo {    private long id;    private String username;    private String email;    private String pwd;    public UserInfo(String username, String email, String pwd) {        this.username = username;        this.email = email;        this.pwd = pwd;    }    public UserInfo()    {        this.id = -1;    }  //此处省略所有成员的getter方法和setter方法    @Override    public String toString() {        return new StringBuffer().append("id:\t").append(this.id)                .append("\nusername:\t").append(this.username).toString();    }}

创建DAO接口 dao.IUserinfoDao.java

package dao;import beans.UserInfo;import java.util.List;public interface IUserinfoDao {    void add(UserInfo user);    void remove(UserInfo user);    UserInfo queryById(long id);    List<UserInfo> queryAll();    void update(UserInfo user);    List<UserInfo> queryByField(String fieldVal);}

创建工具类MybatisSessionManage，主要用于获取SqlSession对象

utils.MybatisSessionManage.java

package utils;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 java.io.IOException;import java.io.InputStream;public class MybatisSessionManage {    private static InputStream stream = null;    private static SqlSessionFactory factory = null;    static {        try {            stream = Resources.getResourceAsStream("mybatis.xml");            if (stream != null) {                factory = new SqlSessionFactoryBuilder().build(stream);            }        } catch (IOException e) {            e.printStackTrace();        }    }    public static SqlSession getSqlSession(){        return factory.openSession();    }    public static SqlSession getSqlSessionAutoCommit(){        return factory.openSession(true);    }}

DAO接口实现类 dao.UserinfoDaoImp.java

package dao;import beans.UserInfo;import org.apache.ibatis.session.SqlSession;import utils.MybatisSessionManage;import java.util.ArrayList;import java.util.List;public class UserinfoDaoImp implements IUserinfoDao {    public void add(UserInfo user) {        SqlSession session = MybatisSessionManage.getSqlSession();        session.insert("addUser", user);        session.commit();        session.close();    }    public void getIdAfterAdd(UserInfo user){        SqlSession session = MybatisSessionManage.getSqlSessionAutoCommit();        session.insert("addUserAndQuery", user);        session.close();    }    public void remove(UserInfo user) {        SqlSession session = MybatisSessionManage.getSqlSessionAutoCommit();        session.delete("deleteUser", user);        session.close();    }    @Override    public void update(UserInfo user) {        SqlSession session = MybatisSessionManage.getSqlSessionAutoCommit();        session.update("updateUser", user);        session.close();    }    @Override    public List<UserInfo> queryByField(String fieldVal) {        List<UserInfo> list = new ArrayList<>();        SqlSession session = MybatisSessionManage.getSqlSessionAutoCommit();        list = session.selectList("queryByLike", fieldVal);        return list;    }    @Override    public UserInfo queryById(long id) {        SqlSession session = MybatisSessionManage.getSqlSessionAutoCommit();        UserInfo user = session.selectOne("userdao.queryById", id);        return user;    }    @Override    public List<UserInfo> queryAll() {        SqlSession session = MybatisSessionManage.getSqlSessionAutoCommit();        List<UserInfo> list = new ArrayList<>();        list = session.selectList("queryAll");        session.close();        return list;    }}

---

单表插入一条记录

<insert id="addUser" parameterType="UserInfo" useGeneratedKeys="true" keyProperty="id">        insert into account (username, pwd, email)        values (#{username}, #{pwd}, #{email})</insert>

 public void add(UserInfo user) {        SqlSession session = MybatisSessionManage.getSqlSession();        session.insert("addUser", user);        session.commit();        session.close();    }

    @Test    public void add() {        UserInfo user = new UserInfo("ZhangFei", "[e-mail]", "3235168831");        new UserinfoDaoImp().add(user);    }

主键自增长插入1条记录后查询出刚插入的主键



单表简单删除



单表简单更新



根据ID查询（查询时最好带上命名空间，防止与id相同命名空间不同的查询冲突）

单表1个条件模糊查询

使用Mapper动态代理查询

要求：

• DAO层定义了数据访问接口
• 每个数据库访问的接口方法名与mapper.xml文件里的select,delete,update,insert元素的id名需要保持一致
• <mapper></mapper>标签里的 namespace 的值需要与DAO接口的全限定类名一致
  
  
  
  
  
  

---

设有beans.WorkInfo.java实体类，类定义如下：

package beans;import java.text.SimpleDateFormat;import java.util.Date;public class WorkInfo {    private Integer work_id;    private String company;  //公司名称    private String position; //职位    private String duty;     //岗位职责    private Date departure;  //离职时间    private Integer user_id; //关联account表外键    @Override    public String toString() {        StringBuilder builder = new StringBuilder();        builder.append("work_id:\t").append(this.work_id)                .append("\ncompany:\t").append(this.company)                .append("\nposition:\t").append(this.position)                .append("\ndeparture:\t").append(this.formatDate(this.departure))                .append("\nduty:\t").append(this.duty)                .append("\nuser_id:\t").append(this.user_id).append("\n");        return builder.toString();    }    private String formatDate(Date date){        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");        return dateFormat.format(date);    }    public WorkInfo(String company, String position,                    String duty, Date departure, Integer user_id) {        this.company = company;        this.position = position;        this.duty = duty;        this.departure = departure;        this.user_id = user_id;    }    public WorkInfo() {        this.work_id = -1;    }   //此处省略getter和setter方法}

实体类访问数据库的dao.IWorkInfoDao接口定义如下：

package dao;import beans.WorkInfo;import java.util.List;import java.util.Map;public interface IWorkInfoDao {    void addWorkInfo(WorkInfo info);    List<WorkInfo> queryWorkInfoByManyConditions(Map<String,Object> map);    List<WorkInfo> queryWorkInfoByNumberIndex(Integer userIdRange, String prefixPosition);}

数据库里有对应的表work_info，表定义如下：

 create table `work_info` (  `work_id` int(11) not null auto_increment,  `company` varchar(64),  `position` varchar(32),  `duty` varchar(128),  `departure` date ,  `user_id` int(11) ,  primary key (`work_id`),  key `fk_workinfo_account` (`user_id`),  constraint `fk_workinfo_account` foreign key (`user_id`) references `account` (`id`)) engine=InnoDB default charset=utf8

Mapper动态代理单表插入



Mapper动态代理单表多条件查询

1. 方法1
   
2. 方法2