本章主要记录MyBatis单表增删改查 + log4j + 手动事务控制,涉及到的技术点有:
- SqlSession
:MyBatis数据库连接会话,用来进行数据库操作。
- SqlSessionFactory
:MyBatis数据库连接会话工厂,用来创建SqlSession
连接。
- mybatis-config.xml
:MyBatis总配置文件(名字随意),用来配置实体类别名、数据源、XML
映射文件等。
- XML
映射文件:实体对应的SQL
配置文件,主要用来配置SQL
方法、参数类型、返回类型和SQL
语句。
- IDAO
:DAO
层接口,与XML
映射文件对应,优化SqlSession
的数据库操作方式。
- log4j
:日志
- 手动事务控制:利用SqlSession
完成对事务的提交commit()
、回滚rollback()
、关闭close()
等.
1.业务场景与SQL
业务场景很简单,如下:
- 根据话题id查询一个话题信息。
- 查询所有的话题信息。
- 新增一个话题信息。
- 修改一个话题信息。
- 删除一个话题信息。
drop table topic;
create table `topic`(
`id` int(5) unsigned not null auto_increment comment '话题',
`title` varchar(20) not null comment '题目',
`score` int(3) not null comment '分数',
`answer` varchar(100) comment '答案',
primary key(id)
)engine=InnoDB comment='话题' auto_increment=1 default charset=utf8;
insert into topic values(99999,'题目1',100,'你好吗?');
2.目录结构
src
\---main
\---java
| \---pers
| \---hanchao
| \---himybatis
| \---curd
| \---Topic.java
| \---ITopicDAO.java
| \---TopicApp.java
\---resources
\---mybatis-mappers
\---Topic.xml
\---log4j.properties
\---mybatis-config.xml
3.MyBatis配置文件mybatis-config.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">
<!--MyBatis的总配置文件-->
<configuration>
<!--类型依赖:配置Java类对应的别名,与Question.xml中的resultType相互对应-->
<typeAliases>
<!--通过XML+IDAO的配置方式,实现单表的增删改查-->
<typeAlias type="pers.hanchao.himybatis.curd.Topic" alias="Topic"/>
</typeAliases>
<!--mybatis的数据库连接-->
<environments default="dev">
<environment id="dev">
<!--事务管理-->
<transactionManager type="JDBC"></transactionManager>
<!--数据源信息-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/exam?useSSL=false"/>
<property name="username" value="****"/>
<property name="password" value="****"/>
</dataSource>
</environment>
</environments>
<!--映射xml的路径配置-->
<mappers>
<mapper resource="mybatis-mappers/Topic.xml"/>
</mappers>
</configuration>
4.实体类+IDAO+XML
4.1.Topic.java
package pers.hanchao.himybatis.curd;
/**
* <p>话题</p>
* @author hanchao 2018/1/26 23:56
**/
public class Topic {
/** 话题id */
private Integer id;
/** 题目 */
private String title;
/** 分数 */
private Integer score;
/** 答案 */
private String answer;
//constructor/toString/setter/getter
}
4.2.ITopicDAO.java
package pers.hanchao.himybatis.curd;
import java.util.List;
/**
* <p>Topic的DAO层接口</p>
* @author hanchao 2018/1/27 0:01
**/
public interface ITopicDAO {
/** 新增一个题目 */
void insertTopic(Topic topic);
/** 修改一个题目 */
void updateTopic(Topic topic);
/** 删除一个题目 */
void deleteTopic(Integer id);
/** 查询一个题目 */
Topic queryTopicById(Integer id);
/** 查询所有题目 */
List<Topic> queryTopicList();
}
4.3.Topic.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">
<!--MyBatis的分配置文件,分别对应每个实体,用来配置SQL操作及SQL语句-->
<!--namespace,定义这个映射的命名域,这里指向Dao层接口-->
<!--先开发ITopicDAO,在开发Topic.xml-->
<mapper namespace="pers.hanchao.himybatis.curd.ITopicDAO">
<!--新增一个话题-->
<insert id="insertTopic" parameterType="Topic">
INSERT INTO `topic`(id,title,score,answer) VALUE(#{id},#{title},#{score},#{answer})
</insert>
<!--修改一个话题-->
<update id="updateTopic" parameterType="Topic">
UPDATE `topic` SET title = #{title},score = #{score},answer = #{answer} WHERE id = #{id}
</update>
<!--删除一个话题-->
<delete id="deleteTopic" parameterType="Integer">
DELETE FROM `topic` WHERE id = #{id}
</delete>
<!--查询一个话题-->
<select id="queryTopicById" parameterType="Integer" resultType="Topic">
SELECT * FROM `topic` WHERE id = #{id}
</select>
<!--查询全部话题-->
<select id="queryTopicList" resultType="Topic">
SELECT * FROM `topic`
</select>
</mapper>
说明:
XML
的namespace(作用域)
要与IDAO
的类路径相对应XML
的id
要与IDAO
的方法名相对应XML
的parameterType(参数类型)
要与IDAO
的参数相对应XML
的resultType(返回类型)
要与IDAO
的返回类型相对应XML
的parameterType(参数类型)="Topic"
中的"Topic"
是实体Topic
的别名,在总配置文件mybatis-config.xml
的typeAlias
配置的。可以不使用别名,直接使用全类名,如"pers.hanchao.himybatis.curd.Topic"
。XML
的SQL
语句中,#{}
表征的是参数字段
5.测试类TopicApp.java
注意MyBatis执行过程和事务控制方式。
IDAO+XML
配置的MyBatis执行过程简述:
- 通过
Reader
读取总配置文件 - 通过配置文件的数据源信息创建
SqlSessionFactory(会话工厂)
- 通过
SqlSessionFactory(会话工厂)
打开SqlSession(会话)
SqlSession(会话)
通过IDAO.class
创建DAO
层的实例化对象,如problemDAO
等等- 通过调用
DAO
层的实例化对象(如problemDAO
)的方法,实现SQL
语句的查询。 - 如果
SQL
执行无问题,则进行SqlSession(会话)
提交(commit
) - 如果
SQL
执行有异常,则进行SqlSession(会话)
回滚(rollback
) SqlSession(会话)
关闭(close
)- 结束
MyBatis的JDBC
事务控制方式:
//创建数据库会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
//增删改查
...
//事务提交
sqlSession.commit();
}catch (Exception e){
//事务回滚
sqlSession.rollback();
}finally {
//关闭连接
sqlSession.close();
}
TopicApp.java完整代码
package pers.hanchao.himybatis.curd;
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 org.apache.log4j.Logger;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
/**
* <p>通过XML+IDAO配置MyBatis,实现单表的增删改查</p>
* @author hanchao 2018/1/27 0:35
**/
public class TopicApp {
/** SqlSessionFactory用于创建SqlS */
private static SqlSessionFactory sqlSessionFactory;
/** Reader用于读取配置文件 */
private static Reader reader;
private static final Logger LOGGER = Logger.getLogger(TopicApp.class);
static{
try{
//读取MyBatis总配置文件
reader = Resources.getResourceAsReader("mybatis-config.xml");
//根据配置文件创建SqlSessionFactory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
LOGGER.error("加载MyBatis配置文件出错!");
}
}
/**
* <p>简单的增删改查</p>
* @author hanchao 2018/1/27 0:37
**/
public static void main(String[] args) {
//创建数据库会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
//调用IDAO
ITopicDAO topicDAO = sqlSession.getMapper(ITopicDAO.class);
//查询所有的话题
showTopicList(topicDAO.queryTopicList());
//新增一个话题
Topic topic = new Topic(1,"題目1",100,"答案1");
topicDAO.insertTopic(topic);
LOGGER.info("新增一个话题:" + topic.toString());
//查询所有的话题
showTopicList(topicDAO.queryTopicList());
//修改一個话题
topic = new Topic(1,"题目1",88,"答案1111");
topicDAO.updateTopic(topic);
LOGGER.info("修改一个话题:" + topic.toString());
//查询所有的话题
showTopicList(topicDAO.queryTopicList());
//删除一个话题
topicDAO.deleteTopic(1);
LOGGER.info("删除一个话题:id = 1");
//查询所有的话题
showTopicList(topicDAO.queryTopicList());
//新增5个话题
for (int i = 0; i < 5; i++) {
int index = 5 + i;
topic = new Topic(index,"题目" + index,60 + index,"答案" + index);
topicDAO.insertTopic(topic);
LOGGER.info("新增一个话题:" + topic.toString());
}
//查询所有的话题
showTopicList(topicDAO.queryTopicList());
//查询一个题目
Topic topic1 = topicDAO.queryTopicById(9);
LOGGER.info("查询一个话题,id = 9,result:" + topic1.toString());
//事务提交
sqlSession.commit();
}catch (Exception e){
//事务回滚
sqlSession.rollback();
}finally {
//关闭连接
sqlSession.close();
}
}
/**
* <p>打印所有话题</p>
* @author hanchao 2018/1/27 0:21
**/
private static void showTopicList(List<Topic> topicList) {
LOGGER.info("===================================显示当前所有话题===================================");
//如果List无值,则打印无信息
if (null == topicList || topicList.size() == 0){
LOGGER.info("暂无话题.");
}else {
for (Topic topic : topicList){
LOGGER.info(topic.toString());
}
}
LOGGER.info("====================================================================================\n");
}
}
6.result
日志级别=INFO的控制台信息
2018-01-28 14:45:09 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:45:09 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:45:09 INFO TopicApp:53 - 新增一个话题:Topic{id=1, title='題目1', score=100, answer='答案1'}
2018-01-28 14:45:09 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=1, title='題目1', score=100, answer='答案1'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:45:09 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:45:09 INFO TopicApp:60 - 修改一个话题:Topic{id=1, title='题目1', score=88, answer='答案1111'}
2018-01-28 14:45:09 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=1, title='题目1', score=88, answer='答案1111'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:45:09 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:45:09 INFO TopicApp:66 - 删除一个话题:id = 1
2018-01-28 14:45:09 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:45:09 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:45:09 INFO TopicApp:75 - 新增一个话题:Topic{id=5, title='题目5', score=65, answer='答案5'}
2018-01-28 14:45:09 INFO TopicApp:75 - 新增一个话题:Topic{id=6, title='题目6', score=66, answer='答案6'}
2018-01-28 14:45:09 INFO TopicApp:75 - 新增一个话题:Topic{id=7, title='题目7', score=67, answer='答案7'}
2018-01-28 14:45:09 INFO TopicApp:75 - 新增一个话题:Topic{id=8, title='题目8', score=68, answer='答案8'}
2018-01-28 14:45:09 INFO TopicApp:75 - 新增一个话题:Topic{id=9, title='题目9', score=69, answer='答案9'}
2018-01-28 14:45:09 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=5, title='题目5', score=65, answer='答案5'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=6, title='题目6', score=66, answer='答案6'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=7, title='题目7', score=67, answer='答案7'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=8, title='题目8', score=68, answer='答案8'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=9, title='题目9', score=69, answer='答案9'}
2018-01-28 14:45:09 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:45:09 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:45:09 INFO TopicApp:82 - 查询一个话题,id = 9,result:Topic{id=9, title='题目9', score=69, answer='答案9'}
日志级别=DEBUG的控制台信息
2018-01-28 14:46:14 DEBUG LogFactory:54 - Logging initialized using 'class org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl' adapter.
2018-01-28 14:46:14 DEBUG PooledDataSource:54 - PooledDataSource forcefully closed/removed all connections.
2018-01-28 14:46:14 DEBUG PooledDataSource:54 - PooledDataSource forcefully closed/removed all connections.
2018-01-28 14:46:14 DEBUG PooledDataSource:54 - PooledDataSource forcefully closed/removed all connections.
2018-01-28 14:46:14 DEBUG PooledDataSource:54 - PooledDataSource forcefully closed/removed all connections.
2018-01-28 14:46:15 DEBUG JdbcTransaction:54 - Opening JDBC Connection
2018-01-28 14:46:15 DEBUG PooledDataSource:54 - Created connection 133250414.
2018-01-28 14:46:15 DEBUG JdbcTransaction:54 - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7f13d6e]
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Preparing: SELECT * FROM `topic`
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Parameters:
2018-01-28 14:46:15 DEBUG queryTopicList:54 - <== Total: 1
2018-01-28 14:46:15 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:46:15 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Preparing: INSERT INTO `topic`(id,title,score,answer) VALUE(?,?,?,?)
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Parameters: 1(Integer), 題目1(String), 100(Integer), 答案1(String)
2018-01-28 14:46:15 DEBUG insertTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:53 - 新增一个话题:Topic{id=1, title='題目1', score=100, answer='答案1'}
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Preparing: SELECT * FROM `topic`
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Parameters:
2018-01-28 14:46:15 DEBUG queryTopicList:54 - <== Total: 2
2018-01-28 14:46:15 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=1, title='題目1', score=100, answer='答案1'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:46:15 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:46:15 DEBUG updateTopic:54 - ==> Preparing: UPDATE `topic` SET title = ?,score = ?,answer = ? WHERE id = ?
2018-01-28 14:46:15 DEBUG updateTopic:54 - ==> Parameters: 题目1(String), 88(Integer), 答案1111(String), 1(Integer)
2018-01-28 14:46:15 DEBUG updateTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:60 - 修改一个话题:Topic{id=1, title='题目1', score=88, answer='答案1111'}
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Preparing: SELECT * FROM `topic`
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Parameters:
2018-01-28 14:46:15 DEBUG queryTopicList:54 - <== Total: 2
2018-01-28 14:46:15 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=1, title='题目1', score=88, answer='答案1111'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:46:15 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:46:15 DEBUG deleteTopic:54 - ==> Preparing: DELETE FROM `topic` WHERE id = ?
2018-01-28 14:46:15 DEBUG deleteTopic:54 - ==> Parameters: 1(Integer)
2018-01-28 14:46:15 DEBUG deleteTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:66 - 删除一个话题:id = 1
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Preparing: SELECT * FROM `topic`
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Parameters:
2018-01-28 14:46:15 DEBUG queryTopicList:54 - <== Total: 1
2018-01-28 14:46:15 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:46:15 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Preparing: INSERT INTO `topic`(id,title,score,answer) VALUE(?,?,?,?)
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Parameters: 5(Integer), 题目5(String), 65(Integer), 答案5(String)
2018-01-28 14:46:15 DEBUG insertTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:75 - 新增一个话题:Topic{id=5, title='题目5', score=65, answer='答案5'}
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Preparing: INSERT INTO `topic`(id,title,score,answer) VALUE(?,?,?,?)
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Parameters: 6(Integer), 题目6(String), 66(Integer), 答案6(String)
2018-01-28 14:46:15 DEBUG insertTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:75 - 新增一个话题:Topic{id=6, title='题目6', score=66, answer='答案6'}
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Preparing: INSERT INTO `topic`(id,title,score,answer) VALUE(?,?,?,?)
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Parameters: 7(Integer), 题目7(String), 67(Integer), 答案7(String)
2018-01-28 14:46:15 DEBUG insertTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:75 - 新增一个话题:Topic{id=7, title='题目7', score=67, answer='答案7'}
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Preparing: INSERT INTO `topic`(id,title,score,answer) VALUE(?,?,?,?)
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Parameters: 8(Integer), 题目8(String), 68(Integer), 答案8(String)
2018-01-28 14:46:15 DEBUG insertTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:75 - 新增一个话题:Topic{id=8, title='题目8', score=68, answer='答案8'}
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Preparing: INSERT INTO `topic`(id,title,score,answer) VALUE(?,?,?,?)
2018-01-28 14:46:15 DEBUG insertTopic:54 - ==> Parameters: 9(Integer), 题目9(String), 69(Integer), 答案9(String)
2018-01-28 14:46:15 DEBUG insertTopic:54 - <== Updates: 1
2018-01-28 14:46:15 INFO TopicApp:75 - 新增一个话题:Topic{id=9, title='题目9', score=69, answer='答案9'}
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Preparing: SELECT * FROM `topic`
2018-01-28 14:46:15 DEBUG queryTopicList:54 - ==> Parameters:
2018-01-28 14:46:15 DEBUG queryTopicList:54 - <== Total: 6
2018-01-28 14:46:15 INFO TopicApp:99 - ===================================显示当前所有话题===================================
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=5, title='题目5', score=65, answer='答案5'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=6, title='题目6', score=66, answer='答案6'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=7, title='题目7', score=67, answer='答案7'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=8, title='题目8', score=68, answer='答案8'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=9, title='题目9', score=69, answer='答案9'}
2018-01-28 14:46:15 INFO TopicApp:105 - Topic{id=99999, title='题目1', score=100, answer='你好吗?'}
2018-01-28 14:46:15 INFO TopicApp:108 - ====================================================================================
2018-01-28 14:46:15 DEBUG queryTopicById:54 - ==> Preparing: SELECT * FROM `topic` WHERE id = ?
2018-01-28 14:46:15 DEBUG queryTopicById:54 - ==> Parameters: 9(Integer)
2018-01-28 14:46:15 DEBUG queryTopicById:54 - <== Total: 1
2018-01-28 14:46:15 INFO TopicApp:82 - 查询一个话题,id = 9,result:Topic{id=9, title='题目9', score=69, answer='答案9'}
2018-01-28 14:46:15 DEBUG JdbcTransaction:54 - Rolling back JDBC Connection [com.mysql.jdbc.JDBC4Connection@7f13d6e]
2018-01-28 14:46:15 DEBUG JdbcTransaction:54 - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7f13d6e]
2018-01-28 14:46:15 DEBUG JdbcTransaction:54 - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7f13d6e]
2018-01-28 14:46:15 DEBUG PooledDataSource:54 - Returned connection 133250414 to pool.