通过自动回复机器人学Mybatis——基础版——慕课网

最新推荐文章于 2018-12-04 23:33:25 发布

开发转测试

最新推荐文章于 2018-12-04 23:33:25 发布

阅读量924

点赞数

本文链接：https://blog.csdn.net/fulq1234/article/details/55261559

版权

**3-1.Mybatis的下载并搭建核心架构**
SqlSession:
1.向sql语句传入参数
2.执行sql语句
3.获取执行sql语句的结果
4.事务的控制
如何得到SqlSession:
1.通过配置文件获取数据库连接的相关信息
2.通过配置信息构建SqlSessionFactory
3.通过SqlSessionFactory得到数据库会话

核心配置文件Configuration.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>
  <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://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
 <mappers>
    <mapper resource="com/imooc/config/sqlxml/Message.xml"/>
  </mappers> 
</configuration>

访问数据库文件

 public class DBAccess {
	public SqlSession getSqlSession() throws IOException{
		//通过配置文件获取数据库连接信息
		Reader reader = Resources.getResourceAsReader("com/imooc/config/Configuration.xml");
		//通过配置信息构建一个SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		//通过SqlSessionFactory打开一个数据库会话
		SqlSession sqlSession=sqlSessionFactory.openSession();
		return sqlSession;
	}
  }

**3-2. SQL基本配置与执行**
java.sql.Types.VARCHAR
java.sql.Types.后面自动出现所有类型

Message.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="Message">
	<resultMap type="com.imooc.bean.Message" id="MessageResult">
		<id column="id" jdbcType="INTEGER" property="id"/>
		<result column="command" jdbcType="VARCHAR" property="command"/>
		<result column="description" jdbcType="VARCHAR" property="description"/>
		<result column="content" jdbcType="VARCHAR" property="content"/>
	</resultMap>
  <select id="queryMessageList" resultMap="MessageResult">
    select id,command,description,content from message
  </select>
</mapper>

**4-1. 动态SQL拼接(上)**
OGNL(Object-Graph Navigation Language)

**4-2. 动态SQL拼接(下)**

1.ognl能直接调用java的方法
2.双引号转义成 "
&转义成&
3.&&或者and都可以
4.mybatis解析#{command},当碰到这个#就替换成?，最后填充这个问号
5.mybatis自动处理空格，

<select id="queryMessageList" parameterType="com.imooc.bean.Message" resultMap="MessageResult">
    select id,command,description,content from message where 1=1
    <if test="command!= null and !&quot;&quot;.equals(command.trim())">
    and command=#{command}
    </if>
    <if test="description!= null and !&quot;&quot;.equals(description.trim())">
    and description like '%' #{description} '%'
    </if>
  </select>

**4-3.应用log4j调试动态SQL**
如果想看到mybatis的sql语句，需要配置log4j

#日志输出级别，位置
#mybatis通过log4j输出sql语句，需要用DEBUG
log4j.rootLogger=DEBUG,Console
#控制台输出
log4j.appender.Console=org.apache.log4j.ConsoleAppender
#布局：PatternLayout:自定义方式
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
#%d:产生日志的时间; 
#%t:线程名称; 
#%p:日志级别，DEBUG; 
#-5:至少占5位字符，不够的话用空格补齐，放在右边
#%c:输出日志的类的全名，包括包名
#%m:附加信息
#%n:换行
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n

log4j.logger.org.apache=INFO

**4-4.实现单条信息删除**

Message.xml

<delete id="deleteOne" parameterType="int">
  	delete from message where id=#{_parameter}
</delete>

MessageDao.xml

package com.imooc.dao;
public class MessageDao {
	
	/**
	 * 单条删除
	 * @param id
	 */
	public void deleteOne(int id){
		DBAccess db=new DBAccess();
		SqlSession sqlSession=null;
		try {
			sqlSession=db.getSqlSession();
			sqlSession.delete("Message.deleteOne",id);
			sqlSession.commit();//提交
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(sqlSession!=null){
				sqlSession.close();
			}
		}
		
	}
}

MaintainService.java

package com.imooc.service;

import com.imooc.dao.MessageDao;

/**
 * 维护相关的业务功能
 * @author Administrator
 *
 */
public class MaintainService {
	public void deleteOne(String id){
		if(id != null && !"".equals(id.trim())){
			MessageDao messageDao = new MessageDao();
			messageDao.deleteOne(Integer.valueOf(id));
			
		}
		
	}
}

DeleteOneServlet.java

package com.imooc.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.service.MaintainService;

public class DeleteOneServlet extends HttpServlet{
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String id=req.getParameter("id");
		MaintainService maintainService = new MaintainService();
		maintainService.deleteOne(id);
		req.getRequestDispatcher("/List.action").forward(req, resp);
	}
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doGet(req, resp);
	}
}

web.xml

<?xml version="1.0" encoding="UTF-8" ?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_5.xsd">
<servlet>
	<servlet-name>DeleteOneServlet</servlet-name>
	<servlet-class>com.imooc.servlet.DeleteOneServlet</servlet-class>
</servlet>
<servlet-mapping>
	<servlet-name>DeleteOneServlet</servlet-name>
	<url-pattern>/DeleteOne.action</url-pattern>
</servlet-mapping>
</web-app>

list.jsp

<a href="<%=basepath %>/DeleteOne.action?id=${temp.id}">删除</a>

**4-5.实现信息批量删除**

Message.xml

<mapper>
<!-- 批量删除 ,separator可以拼接参数-->
  <delete id="deleteBatch" parameterType="java.util.List">
  	delete from message where id in(
  		<foreach collection="list" item="item" separator=",">
  			#{item}
  		</foreach>
  	)
  </delete>
</mapper>

MessageDao.java

public class MessageDao {
/*
	 * 批量删除
	 */
	public void deleteBatch(List<Integer> ids){
		DBAccess db=new DBAccess();
		SqlSession sqlSession=null;
		try {
			sqlSession=db.getSqlSession();
			sqlSession.delete("Message.deleteBatch",ids);
			sqlSession.commit();//提交
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(sqlSession!=null){
				sqlSession.close();
			}
		}
		
	}
}

MaintainService.java

public class MaintainService {
/**
	 * 批量删除
	 * @param id
	 */
	public void deleteBatch(String[] ids){	
		List<Integer> list = new ArrayList<Integer>();
		for(String id:ids){
			list.add(Integer.valueOf(id));
		}
		MessageDao messageDao = new MessageDao();
		messageDao.deleteBatch(list);			
			
	}
}

public class DeleteBatchServlet extends HttpServlet{
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String[] ids=req.getParameterValues("id");
		MaintainService maintainService = new MaintainService();
		maintainService.deleteBatch(ids);
		req.getRequestDispatcher("/List.action").forward(req, resp);
	}
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doGet(req, resp);
	}
}

web.xml

<web-app>
<!-- 批量删除 -->
	<servlet>
		<servlet-name>DeleteBatchServlet</servlet-name>
		<servlet-class>com.imooc.servlet.DeleteBatchServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>DeleteBatchServlet</servlet-name>
		<url-pattern>/DeleteBatch.action</url-pattern>
	</servlet-mapping>
</web-app>

list.jsp

<html>
<script type="text/javascript">
function deleteBatch(basePath){
	$("#mainForm").attr("action",basePath + "/DeleteBatch.action")
	$("#mainForm").submit();
}
</script>

<a class="btn03" href="javascript:deleteBatch('<%=basepath%>')">删 除</a>

<form name="mainForm" type="post">
<c:forEach var="temp" items="${messageList}" varStatus="status">
	<input type="checkbox" name="id" value="${temp.id }"/>
</c:forEach>
</form>
</html>

**5-1 到 5-3 一对多关系的配置**

表结构,主表:command,附表:command_content

表bean类，,set,get方法

command.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="Command">

	<resultMap type="com.imooc.bean.Command" id="Command">
		<!-- column:jdbc的列名,不是数据库的表列名 -->
		<id column="C_ID" jdbcType="INTEGER" property="id"/>
		<result column="name" jdbcType="VARCHAR" property="name"/>
		<result column="description" jdbcType="VARCHAR" property="description"/>
		<collection property="contentList" resultMap="CommandContent.content"/>
 	</resultMap>
	
	<select id="queryCommandList" parameterType="com.imooc.bean.Command" resultMap="Command">
		select a.id C_ID,a.name,a.description,b.id,b.content,b.command_id 
		from command a left join command_content b on a.id=b.command_id
		<where>
			<if test="name!=null and !name.equals("")">
				and name = #{name}
			</if>
			<if test="description!=null and !"".equals(description.trim())">
				and description like '%'#{description}'%'
			</if>
		</where>
	</select>
</mapper>

CommandContext.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="CommandContent">
	<resultMap type="com.imooc.bean.CommandContent" id="content">
		<id column="id" jdbcType="INTEGER" property="id"/>
		<result column="content" jdbcType="VARCHAR" property="content"/>
		<result column="command_id" jdbcType="VARCHAR" property="command_id"/>
	</resultMap>
</mapper>

command.java

package com.imooc.dao;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.imooc.bean.Command;
import com.imooc.db.DBAccess;

public class CommandDao {
	public  List<Command> queryCommandList(String name,String description){
		DBAccess dbAccess=new DBAccess();
		List<Command> commandList=new ArrayList<Command>();
		SqlSession sqlSession = null;
		try {
			sqlSession = dbAccess.getSqlSession();
			Command command = new Command();			
			command.setName(name);
			command.setDescription(description);
			commandList = sqlSession.selectList("Command.queryCommandList", command);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return commandList;
	}
}

**5-4. 常用标签**

1. include refid

<mapper>
<select id="queryMessageList" parameterType="com.imooc.bean.Message" resultMap="MessageResult">
    select <include refid="columns"></include> from message where 1=1
    <if test="command!= null and !"".equals(command.trim())">
    and command like '%' #{command} '%'
    </if>
    <if test="description!= null and !"".equals(description.trim())">
    and description like '%' #{description} '%'
    </if>
  </select>
  <sql id="columns">id,command,description,content</sql>
</mapper>

2.set

<mapper>
<update id="">
  	update message 
  	<set>
	  	<if test="name!=null">
	  		name=#{name}
	  	</if>
  	</set>
  </update>
</mapper>

3.trim

<mapper>
<!-- 前缀prefix，如果有内容，前面加where,
  如果有内容，就在后面加suffix
 如果前面有and或或者or，就去掉， -->
  <trim prefix="set" suffix="test" prefixOverrids="and|or">
  	
  </trim>
</mapper>

4.choose

<mapper>
  <choose>
  	<when test="">
  	</when>
  	<when test="">
  	</when>
  	<otherwise></otherwise>
  </choose>
</mapper>

5.association

<mapper>
 <!-- 字表中能看到主表的内容 
  字表实体(CommandContent.java)有个主表的引用(private Command command) -->
  <association property = "command" result="Command.Command"/>
</mapper>

**6-2. 常见问题解析**
1.获取自增主键值
useGeneratedKeys:设置是否使用JDBC的getGeneratedKeys方法获取主键并赋值到keyProperty设置的领域的模型属性中。MySQL和SQLServer执行auto-generated key field，因此当数据库设置好自增长主键后，可通过JDBC的getGeneratedKeys方法获取。
keyProperty:将主键存在参数(parameterType)的哪一个属性中

<mapper>
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.imooc.bean.Command">
  	insert into COMMAND(name,description) values(#{name},#{description})
  </insert>
</mapper>

开发转测试

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
通过自动回复机器人学Mybatis——基础版——慕课网

**3-1.Mybatis的下载并搭建核心架构**SqlSession: 1.向sql语句传入参数 2.执行sql语句 3.获取执行sql语句的结果 4.事务的控制如何得到SqlSession: 1.通过配置文件获取数据库连接的相关信息 2.通过配置信息构建SqlSessionFactory 3.通过SqlSessionFactory得到数据库会话
复制链接

扫一扫