通过自动回复机器人学Mybatis——基础版——慕课网
**3-1.Mybatis的下载并搭建核心架构**SqlSession: 1.向sql语句传入参数 2.执行sql语句 3.获取执行sql语句的结果 4.事务的控制如何得到SqlSession: 1.通过配置文件获取数据库连接的相关信息 2.通过配置信息构建SqlSessionFactory 3.通过SqlSessionFactory得到数据库会话
·
**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 !"".equals(command.trim())">
and command=#{command}
</if>
<if test="description!= null and !"".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
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);
}
}
<?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><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>
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>
CSDN联合极客时间，共同打造面向开发者的精品内容学习社区，助力成长！
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)