Mybatis基本应用实现增删改查

1.概述

Mybatis在以数据库为中心的项目中很常用，最近本的操作就是对数据表的增删改查。本文直接以Mybatis+MySql数据库为例，实现增删查。尽量不去讲太多概念，力求简单易懂。

2.搭建环境工具

首先安装MySQl；安装Eclipse；这些不是本文重点，相信大家都会。

3.建立工程结构如下：

1）需要的jar包

mybatis-3.0.5.jar

Mybatis访问数据库

mysql-connector-java-5.1.25-bin.jar

连接mysql数据库用

log4j-1.2.13.jar

slf4j-api-1.5.8.jar

slf4j-log4j12-1.5.8.jar

日志记录用

2）Mybatis配置

<?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><!-- 配置别名 --><typeAliases><typeAlias type="com.iMybatis.use.dao.UserDao" alias="UserDao" /><typeAlias type="com.iMybatis.model.UserDto" alias="UserDto" /></typeAliases><!-- 配置环境变量 --><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url"value="jdbc:mysql://127.0.0.1:3306/iMybatis" /><property name="username" value="root" /><property name="password" value="1" /></dataSource></environment></environments><!-- 配置mappers --><mappers><mapper resource="com/iMybatis/use/dao/UserDao.xml" /></mappers></configuration>   

3）日志配置

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"><log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"><appender name="console" class="org.apache.log4j.ConsoleAppender"><!-- <param name="Target" value="System.out" /> --><layout class="org.apache.log4j.PatternLayout"><param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss,SSS} %-5p %c - %m%n" /></layout></appender><appender name="file" class="org.apache.log4j.RollingFileAppender" ><param name="File" value="iMybatis.log" /><param name="MaxFileSize" value="100KB" /><param name="MaxBackupIndex" value="2" /><layout class="org.apache.log4j.PatternLayout"><param name="ConversionPattern" value="=%d %-5p [%t] %-17c{2} (%13F:%L) %3x - %m%n" /></layout></appender><category name="org.apache.ibatis" additivity="false"><priority value="DEBUG" /><appender-ref ref="console" /></category><category name="java.sql" additivity="false"><priority value="ERROR" /><appender-ref ref="console" /></category><logger name="org.apache.ibatis.common.jdbc.SimpleDataSource"><level value="debug" /></logger><logger name="org.apache.ibatis.common.jdbc.SimpleDataSource"><level value="debug" /></logger><logger name="org.apache.ibatis.common.jdbc.ScriptRunner"><level value="debug" /></logger><logger name="org.apache.ibatis.common.jdbc.SqlMapClientDelegate"><level value="debug" /></logger><logger name="java.sql.Connection"><level value="debug" /></logger><logger name="java.sql.Statement"><level value="debug" /></logger><logger name="java.sql.PreparedStatement"><level value="debug" /></logger><logger name="java.sql.ResultSet"><level value="debug" /></logger><root><priority value="ERROR" /><appender-ref ref="console" /></root></log4j:configuration>

4）数据库访问DAO

package com.iMybatis.use.dao;import java.util.List;import com.iMybatis.model.UserDto;public interface UserDao {    public List<UserDto> queryUsers(UserDto user) throws Exception;    public int addUser(UserDto user) throws Exception;     public int deleteUser(UserDto user) throws Exception;    public int updateUser(UserDto user) throws Exception;    public  List<UserDto> getAllUsers(UserDto user) throws Exception;}

sqlmap配置代码

<?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.iMybatis.use.dao.UserDao"><select id="queryUsers" parameterType="UserDto" resultType="UserDto"useCache="false">    <![CDATA[select * from t_user t where t.username = #{username}]]></select><!-- 创建用户(Create) --><insert id="addUser" parameterType="UserDto">         insert into t_user(username,password,address) values(#{username},#{password},#{address})</insert><!-- 删除用户(Remove) --><delete id="deleteUser" parameterType="int">         delete from t_user where userid=#{userid}</delete><!-- 修改用户(Update) --><update id="updateUser" parameterType="UserDto">         update t_user set username=#{username},password=#{password},address=#{address} where userid=#{userid}</update><!-- 查询全部用户--><select id="getAllUsers" resultType="UserDto">         select * from t_user</select></mapper>

5）实体类

package com.iMybatis.model;public class UserDto {    private Integer userid;    private String username;    private String password;    private String address;    private String createtime;    public Integer getUserid() {        return userid;    }    public void setUserid(Integer userid) {        this.userid = userid;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public String getCreatetime() {        return createtime;    }    public void setCreatetime(String createtime) {        this.createtime = createtime;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }}

6）测试类

package com.iMybatis.use;import java.io.IOException;import java.io.Reader;import java.util.List;import org.apache.ibatis.io.Resources;import org.apache.ibatis.logging.Log;import org.apache.ibatis.logging.LogFactory;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.iMybatis.model.UserDto;import com.iMybatis.use.dao.UserDao;public class useMybatis{private static final Log logger = LogFactory.getLog(useMybatis.class);private static SqlSessionFactoryBuilder sqlSessionFactoryBuilder;private static SqlSessionFactory sqlSessionFactory;public static void main(String[] args) {// TODO Auto-generated method stubtry{System.out.println("测试");init();}catch (IOException e){logger.error("", e);}testQueryList();//查询//testAddUser(); // 增加//testUpdateUser();// 修改//testDeleteUser();// 删除}private static void init() throws IOException {String resource = "mybatis-config.xml";// System.out.println(resource);Reader reader = Resources.getResourceAsReader(resource);System.out.println(reader);sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();sqlSessionFactory = sqlSessionFactoryBuilder.build(reader);}private static void testQueryList() {SqlSession session = null;try{session = sqlSessionFactory.openSession();UserDao userDao = session.getMapper(UserDao.class);UserDto user = new UserDto();user.setUsername("iMbatis");List<UserDto> users = userDao.queryUsers(user);if (null != users){logger.error("Find " + users.size() + " users named iMbatis.");}session.commit(true);}catch (Exception e){logger.error("", e);session.rollback(true);}finally{session.close();}}private static void testAddUser() {SqlSession session = null;try{session = sqlSessionFactory.openSession();UserDao userDao = session.getMapper(UserDao.class);for (int k = 0; k < 10; k++){UserDto user = new UserDto();user.setUsername("zhang" + k);user.setAddress("bei" + k);userDao.addUser(user);logger.error("添加" + String.valueOf(k));}session.commit(true);}catch (Exception e){logger.error("", e);session.rollback(true);}finally{session.close();}}private static void testUpdateUser() {SqlSession session = null;try{session = sqlSessionFactory.openSession();UserDao userDao = session.getMapper(UserDao.class);UserDto user = new UserDto();user.setUserid(6);user.setUsername("zhang6");user.setPassword("ps");user.setAddress("beijing");userDao.updateUser(user);int retResult = userDao.updateUser(user);logger.error("修改测试"+retResult);session.commit(true);}catch (Exception e){logger.error("", e);session.rollback(true);}finally{session.close();}}private static void testDeleteUser() {SqlSession session = null;try{session = sqlSessionFactory.openSession();UserDao userDao = session.getMapper(UserDao.class);UserDto user = new UserDto();user.setUserid(7);int retResult =userDao.deleteUser(user);logger.error("删除测试"+retResult);session.commit(true);}catch (Exception e){logger.error("", e);session.rollback(true);}finally{session.close();}}}

4总结

访问数据库本质都是：1打开数据库连接；2执行SQL；3关闭数据库连接；

5.代码

下载代码