3、基于传统dao模式下的数据操作
3.1、创建dao层接口
package com.luke.dao;
import com.luke.pojo.User;
import java.util.List;
public interface UserDao {
public int insertUser(User vo) throws Exception;
public int updateUser(User user) throws Exception;
public int deleteUserById(Long id) throws Exception;
public Integer selectCount() throws Exception;
public User queryUserById(Long id) throws Exception;
public List<User> queryAllUser() throws Exception;
}
3.2、UserMapper.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">
<!--
namespace:命名空間 (保证唯一)
-->
<mapper namespace="com.luke.pojo.User">
<insert id="insertUser" parameterType="com.luke.pojo.User">
insert into tb_user (userid,user_name,pwd,age,sex,birthday)
values(seq_user.nextval,#{userName},#{pwd},#{age},#{sex},#{birthday})
</insert>
<update id="updateUser" parameterType="com.luke.pojo.User">
update tb_user set user_name=#{userName},pwd=#{pwd},age=#{age},sex=#{sex},birthday=#{birthday}
where userid=#{userid}
</update>
<delete id="deleteUserById" parameterType="long">
delete from tb_user where userid=#{userid}
</delete>
<select id="selectCount" resultType="int">
select count(*) from tb_user
</select>
<select id="queryAllUser" resultType="com.luke.pojo.User">
select USERID,
USER_NAME as userName,
PWD,
AGE,
SEX,
BIRTHDAY
from tb_user
</select>
<select id="queryUserById" resultType="com.luke.pojo.User">
select USERID,
USER_NAME as userName,
PWD,
AGE,
SEX,
BIRTHDAY
from tb_user where userid = #{userid}
</select>
</mapper>
3.3、实现类UserDaoImpl
package com.luke.dao.impl;
import com.luke.dao.UserDao;
import com.luke.pojo.User;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class UserDaoImpl implements UserDao {
private SqlSession sqlSession;
public UserDaoImpl(SqlSession sqlSession) {
this.sqlSession=sqlSession;
}
@Override
public int insertUser(User vo) throws Exception {
return sqlSession.insert("com.luke.pojo.User.insertUser",vo);
}
@Override
public int updateUser(User user) throws Exception {
return sqlSession.update("com.luke.pojo.User.updateUser",user);
}
@Override
public int deleteUserById(Long id) throws Exception {
return sqlSession.delete("com.luke.pojo.User.deleteUserById",id);
}
@Override
public Integer selectCount() throws Exception {
return sqlSession.selectOne("com.luke.pojo.User.selectCount");
}
@Override
public User queryUserById(Long id) throws Exception {
return sqlSession.selectOne("com.luke.pojo.User.queryUserById",id);
}
@Override
public List<User> queryAllUser() throws Exception {
return sqlSession.selectList("com.luke.pojo.User.queryAllUser");
}
}
3.4、编写测试类
package com.luke.test;
import com.luke.dao.UserDao;
import com.luke.dao.impl.UserDaoImpl;
import com.luke.pojo.User;
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.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class TestMyBatis {
private UserDao dao ;
private SqlSession sqlsession;
@Before
public void setUp() throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlsession = sqlSessionFactory.openSession();
dao = new UserDaoImpl(sqlsession);
}
@After
public void tearDown() throws Exception {
sqlsession.close();
}
}
3.5、添加用户
运行结果ok，但是到数据库中查询发现数据没有进去,肯定是事务的问题，接下来加入事务操作，再次修改代码
@Test
public void testInsertUser() throws Exception {
Date birthday = new Date();
User user = new User( "关羽", "123456", 15, "男", birthday);
int result=-1;
try {
result = dao.insertUser(user);
sqlsession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlsession.rollback();
}
System.out.println(result);
}
运行结果如下
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 1380976928.
DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - ==> Preparing: insert into tb_user (userid,user_name,pwd,age,sex,birthday) values(seq_user.nextval,?,?,?,?,?)
DEBUG - ==> Parameters: 关羽(String), 123456(String), 15(Integer), 男(String), 2021-04-20 15:00:45.589(Timestamp)
DEBUG - <== Updates: 1
DEBUG - Committing JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
1
DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - Returned connection 1380976928 to pool.
Process finished with exit code 0
从日志的结果中，我们发现，事务已经发生提交，而且mybatis默认也使用了连接池机制
好了，那么接下来的操作就方便很多了
3.6、修改用户
@Test
public void testUpdateUser() throws Exception {
Date birthday = new Date();
User user = new User( 5,"关羽2", "123456", 15, "男", birthday);
int result=-1;
try {
result = dao.updateUser(user);
sqlsession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlsession.rollback();
}
System.out.println(result);
}
测试结果
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 1380976928.
DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - ==> Preparing: update tb_user set user_name=?,pwd=?,age=?,sex=?,birthday=? where userid=?
DEBUG - ==> Parameters: 关羽2(String), 123456(String), 15(Integer), 男(String), 2021-04-20 15:04:22.37(Timestamp), 6(Integer)
DEBUG - <== Updates: 1
DEBUG - Committing JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
1
DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - Returned connection 1380976928 to pool.
Process finished with exit code 0
3.7、删除用户
测试方法
@Test
public void testDeleteUserById() throws Exception {
int result=-1;
try {
result = dao.deleteUserById(5l);
sqlsession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlsession.rollback();
}
System.out.println(result);
}
运行结果
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 1380976928.
DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - ==> Preparing: delete from tb_user where userid=?
DEBUG - ==> Parameters: 6(Long)
DEBUG - <== Updates: 1
DEBUG - Committing JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
1
DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@52500920]
DEBUG - Returned connection 1380976928 to pool.
3.8、查询数据
@Test
public void testQueryAll() throws Exception {
List<User> list = null ;
try {
list = dao.queryAllUser();
} catch (Exception e) {
e.printStackTrace();
}finally{
sqlsession.close();
}
for (User u:list){
System.out.println(u);
}
}
运行结果
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 1160487387.
DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - ==> Preparing: select USERID, USER_NAME as userName, PWD, AGE, SEX, BIRTHDAY from tb_user
DEBUG - ==> Parameters:
DEBUG - <== Total: 4
DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - Returned connection 1160487387 to pool.
User [userid=1, userName=张三, pwd=123456, age=10, sex=男, birthday=Tue Apr 20 09:43:59 CST 2021]
User [userid=2, userName=李四, pwd=123456, age=10, sex=男, birthday=Tue Apr 20 09:43:59 CST 2021]
User [userid=3, userName=王五, pwd=123456, age=10, sex=男, birthday=Tue Apr 20 09:43:59 CST 2021]
User [userid=4, userName=赵六, pwd=123456, age=10, sex=男, birthday=Tue Apr 20 09:43:59 CST 2021]
Process finished with exit code 0
3.9、查询总记录数
public void testSelectCount() throws Exception {
int result=-1;
try {
result = dao.selectCount();
} catch (Exception e) {
e.printStackTrace();
}finally{
sqlsession.close();
}
System.out.println(result);
}
运行结果
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 1160487387.
DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - ==> Preparing: select count(*) from tb_user
DEBUG - ==> Parameters:
DEBUG - <== Total: 1
DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - Returned connection 1160487387 to pool.
4
3.10、查询单条数据
@Test
public void testQueryUserById() throws Exception {
User user = null ;
try {
user = dao.queryUserById(3l);
} catch (Exception e) {
e.printStackTrace();
}finally{
sqlsession.close();
}
System.out.println(user);
}
运行结果
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 1160487387.
DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - ==> Preparing: select USERID, USER_NAME as userName, PWD, AGE, SEX, BIRTHDAY from tb_user where userid = ?
DEBUG - ==> Parameters: 3(Long)
DEBUG - <== Total: 1
DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@452ba1db]
DEBUG - Returned connection 1160487387 to pool.
User [userid=3, userName=王五, pwd=123456, age=10, sex=男, birthday=Tue Apr 20 09:43:59 CST 2021]
