1、Mybatis中SqlSession的作用;
向SQL语句出入参数;
执行SQL语句;
获取SQL语句执行的结果;
事务的控制;
2、如何得到SqlSession?
通过配置文件获取数据库连接的相关信息;
通过配置信息构建SqlSessionFactory;
通过SqlSessionFactory打开数据库会话;
1.添加mybatis.jar包
下载地址https://github.com/mybatis/mybatis-3/releases
2.配置文件
mybatis\mybatis-3-mybatis-3.3.0\src\test\java\org\apache\ibatis\submitted\complex_property\Configuration.xml
修改:
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/micro_message"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
3.得到SqlSession
//通过配置文件获取数据库连接信息
Reader reader=Resources.getResourceAsReader("com/imooc/config/Configuration.xml");
//通过配置信息构建一个SqlSessionFactory
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
//通过SqlSessionFactory打开一个数据库对话
SqlSession sqlSession=sqlSessionFactory.openSession();
4.核心配置文件
mybatis\mybatis-3-mybatis-3.3.0\src\test\java\org\apache\ibatis\submitted\complex_property\User.xml
修改:
<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" parameterType="long" resultMap="MessageResult">
select ID,COMMAND,DESCRIPTION,CONTENT from MESSAGE where 1=1
</select>
5.查询
public List<Message> queryMessageList(String command,String description){
List<Message> messageList=new ArrayList<Message>();
DBAccess dbAcess=new DBAccess();
SqlSession sqlSession=null;
try {
sqlSession=dbAcess.getSqlSession();
messageList=sqlSession.selectList("Message.queryMessageList");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(sqlSession!=null)
sqlSession.close();
}
return messageList;
}
6.传递查询参数
sqlSession.selectList(参数1,参数2)只能传递两个参数
ONGL表达式(不是mybatis专有,独立)
<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>
7.调试日志 log4j.jar 下载地址http://www.apache.org/dyn/closer.cgi/logging/log4j/1.2.17/log4j-1.2.17.zip
配置文件mybatis\mybatis-3-mybatis-3.3.0\src\test\java\log4j.properties
直接放在src下
修改: log4j.rootLogger=DEBUG,Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache=INFO
1). debug:输出的级别,console输出端的名称
2). 输出到控制台
3).布局
4).输出格式:%D产生时间,%t线程,%p日志级别,%c日志打印的类,%m输出内容,%n换行
5).org.apache:该包下的日志级别,第一行是针对所有的日志定义的级别
8.单表删除
sqlSession.delete("Message.deleteOne",id);
sqlSession.commit();
配置文件:
<delete id="deleteOne" parameterType="int">
delete from MESSAGE where ID = #{_parameter}
</delete>
9.批量删除
配置文件:
<delete id="deleteBatch" parameterType="java.util.List">
delete from MESSAGE where ID in(
<foreach collection="list" item="item" separator=",">
#{item}
</foreach>
)
</delete>
10.前端回复功能
talk.jsp
$.ajax({
url : $("#basePath").val() + "AutoReplyServlet.action",
type : "POST",
dataType : "text",
timeout : 10000,
success : function (data) {
appendDialog("talk_recordboxme","My账号",content);
appendDialog("talk_recordbox","公众号",data);
$("#content").val("");
render();
},
data : {"content":content}
});
AutoReplyServlet.java
//设置编码
resp.setContentType("text/html;charset=utf-8");
PrintWriter out = resp.getWriter();
QueryService queryService = new QueryService();
out.write(queryService.queryByCommand(req.getParameter("content")));
out.flush();
out.close();
11.一对多
在实体类中添加一个集合,例如:list
在配置文件中配置:
<collection property = "commandContentList" resultMap = "commandContent.content"/>配置一对多
两个表中都存在ID,执行后前缀会去掉,不能分清,所以给一个ID起别名,一般是主表.
12.标签
where标签:select <where> (and内容)</where>
sql标签 <sql id="columns">ID,COMMAND,CONTENT</sql>
引用:select <include refid="columns"/> from MESSAGE
set标签 : update MESSAGE set (内容) </set>
trim标签:代替where标签<trim prefix="where" prefixOverrides="and/or" > (内容)</trim>
代替set标签<trim prefix="set" suffixOverrides=",">(内容) </trim>
choose标签:相当于if..else
<choose>
<when test=""></when>
<when test=""></when>
association标签:子表关联主表
<association property="command" resultMap="Command.Command"></association>
<otherwise></otherwise>
</choose>
list标签: <foreach collection="list" item="item" separator=",">
#{item}
</foreach>
12.容易混淆的概念
resultMap(映射id) resultType(类型)结果集中的列名必须和实体类中相同
parameterMap(不推荐使用) parameterType
#{}(?,preparement,有预编译效果) ${}(直接替换,场景:order by $())
向SQL语句出入参数;
执行SQL语句;
获取SQL语句执行的结果;
事务的控制;
2、如何得到SqlSession?
通过配置文件获取数据库连接的相关信息;
通过配置信息构建SqlSessionFactory;
通过SqlSessionFactory打开数据库会话;
1.添加mybatis.jar包
下载地址https://github.com/mybatis/mybatis-3/releases
2.配置文件
mybatis\mybatis-3-mybatis-3.3.0\src\test\java\org\apache\ibatis\submitted\complex_property\Configuration.xml
修改:
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/micro_message"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
3.得到SqlSession
//通过配置文件获取数据库连接信息
Reader reader=Resources.getResourceAsReader("com/imooc/config/Configuration.xml");
//通过配置信息构建一个SqlSessionFactory
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
//通过SqlSessionFactory打开一个数据库对话
SqlSession sqlSession=sqlSessionFactory.openSession();
4.核心配置文件
mybatis\mybatis-3-mybatis-3.3.0\src\test\java\org\apache\ibatis\submitted\complex_property\User.xml
修改:
<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" parameterType="long" resultMap="MessageResult">
select ID,COMMAND,DESCRIPTION,CONTENT from MESSAGE where 1=1
</select>
5.查询
public List<Message> queryMessageList(String command,String description){
List<Message> messageList=new ArrayList<Message>();
DBAccess dbAcess=new DBAccess();
SqlSession sqlSession=null;
try {
sqlSession=dbAcess.getSqlSession();
messageList=sqlSession.selectList("Message.queryMessageList");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(sqlSession!=null)
sqlSession.close();
}
return messageList;
}
6.传递查询参数
sqlSession.selectList(参数1,参数2)只能传递两个参数
ONGL表达式(不是mybatis专有,独立)
<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>
7.调试日志 log4j.jar 下载地址http://www.apache.org/dyn/closer.cgi/logging/log4j/1.2.17/log4j-1.2.17.zip
配置文件mybatis\mybatis-3-mybatis-3.3.0\src\test\java\log4j.properties
直接放在src下
修改: log4j.rootLogger=DEBUG,Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache=INFO
1). debug:输出的级别,console输出端的名称
2). 输出到控制台
3).布局
4).输出格式:%D产生时间,%t线程,%p日志级别,%c日志打印的类,%m输出内容,%n换行
5).org.apache:该包下的日志级别,第一行是针对所有的日志定义的级别
8.单表删除
sqlSession.delete("Message.deleteOne",id);
sqlSession.commit();
配置文件:
<delete id="deleteOne" parameterType="int">
delete from MESSAGE where ID = #{_parameter}
</delete>
9.批量删除
配置文件:
<delete id="deleteBatch" parameterType="java.util.List">
delete from MESSAGE where ID in(
<foreach collection="list" item="item" separator=",">
#{item}
</foreach>
)
</delete>
10.前端回复功能
talk.jsp
$.ajax({
url : $("#basePath").val() + "AutoReplyServlet.action",
type : "POST",
dataType : "text",
timeout : 10000,
success : function (data) {
appendDialog("talk_recordboxme","My账号",content);
appendDialog("talk_recordbox","公众号",data);
$("#content").val("");
render();
},
data : {"content":content}
});
AutoReplyServlet.java
//设置编码
resp.setContentType("text/html;charset=utf-8");
PrintWriter out = resp.getWriter();
QueryService queryService = new QueryService();
out.write(queryService.queryByCommand(req.getParameter("content")));
out.flush();
out.close();
11.一对多
在实体类中添加一个集合,例如:list
在配置文件中配置:
<collection property = "commandContentList" resultMap = "commandContent.content"/>配置一对多
两个表中都存在ID,执行后前缀会去掉,不能分清,所以给一个ID起别名,一般是主表.
12.标签
where标签:select <where> (and内容)</where>
sql标签 <sql id="columns">ID,COMMAND,CONTENT</sql>
引用:select <include refid="columns"/> from MESSAGE
set标签 : update MESSAGE set (内容) </set>
trim标签:代替where标签<trim prefix="where" prefixOverrides="and/or" > (内容)</trim>
代替set标签<trim prefix="set" suffixOverrides=",">(内容) </trim>
choose标签:相当于if..else
<choose>
<when test=""></when>
<when test=""></when>
association标签:子表关联主表
<association property="command" resultMap="Command.Command"></association>
<otherwise></otherwise>
</choose>
list标签: <foreach collection="list" item="item" separator=",">
#{item}
</foreach>
12.容易混淆的概念
resultMap(映射id) resultType(类型)结果集中的列名必须和实体类中相同
parameterMap(不推荐使用) parameterType
#{}(?,preparement,有预编译效果) ${}(直接替换,场景:order by $())
#{} String或基本数据类型#{_parameter}
代码上传 我的资源