MyBatis的数据库操作入门（六）

你的名字 2021-09-27 12:50 318阅读 0赞

增加

UserMapper.java新增一个方法

package cn.bdqn.dao;
    
    import java.util.List;
    import java.util.Map;
    
    import cn.bdqn.pojo.User;
    
    public interface UserMapper {
    
    	List<User> getUserList();
    	List<User> getUserLikeUserName(String userName);     //模糊查询
    	List<User> getUserLikeUserNameAndUserRole(User user);
    	List<User> getUserByMap(Map<String,Object> map);
    	
    	int add(User user);
    }

UserMapper.xml新增一个select

<?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="cn.bdqn.dao.UserMapper">
    
    	<select id="getUserList" resultType="cn.bdqn.pojo.User">
    		Select * from smbms_user
    	</select>
    	
    	<select id="getUserLikeUserName" parameterType="String" resultType="user">
      		select * from smbms_user where userName like concat('%',#{userName},'%')
      	</select>
      	
      	<select id="getUserLikeUserNameAndUserRole" parameterType="user" resultMap="userList">
      		select u.*,r.roleName from smbms_user u,smbms_role r 
      			where 
      			r.id=u.userRole 
      			and userRole=#{userRole} 
      			and userName like concat('%',#{userName},'%')
      	
      	</select>
      	
      	<resultMap type="user" id="userList">
      		<id column="id" property="id"/>
      		<result column="userName" property="userName" />
      		<result column="userCode" property="userCode" />
      		<result column="roleName" property="userRoleName" />
      	</resultMap>
      	
      	<select id="getUserByMap" parameterType="map" resultType="user">
      		select * from smbms_user where userRole=#{userRole} and userName like concat('%',#{userName},'%')
      	</select>
      	
      	<insert id="add" parameterType="user">
      		INSERT INTO smbms_user (
      		`userCode`,
      		`userName`,
      		`userPassword`,
      		`gender`,
      		`birthday`,
      		`phone`,
      		`address`,
      		`userRole`,
      		`createdBy`,
      		`creationDate`) 
      		VALUES(
      		#{userCode},
      		#{userName},
      		#{userPassword},
      		#{gender},
      		#{birthday},
      		#{phone},
      		#{address},
      		#{userRole},
      		#{createdBy},
      		#{creationDate})
      	</insert>
    </mapper>

实体类代码：

package cn.bdqn.test;
    
    
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    import org.apache.log4j.Logger;
    
    import cn.bdqn.dao.UserMapper;
    import cn.bdqn.pojo.User;
    import cn.bdqn.util.MyBatisUtil;
    
    public class Test {
    
    	/*private static Logger logger = Logger.getLogger("Test");*/
    
    	public static void main(String[] args) {
    
    		//getUserCount();
    		//getUserList();
    		//getUserByName(sqlSession);
    		//getUserLikeNameAndRole(sqlSession);
    		//getUserByMap(sqlSession);
    		SqlSession sqlSession = MyBatisUtil.getSqlSession();
    		User user=new User(17L, "andy", "刘德华", "1234567", 1, "13422369777", "HK", new Date(), 2, null, null, 1, new Date());
    		int count = sqlSession.getMapper(UserMapper.class).add(user);
    		sqlSession.commit();
    		System.out.println("增加了"+count+"条数据！");
    		MyBatisUtil.closeSession(sqlSession);
    	}
    
    	
    /*	private static void getUserByMap(SqlSession sqlSession) {
    		Map<String,Object> map=new HashMap<String,Object>();
    		map.put("userRole", 3);
    		map.put("userName", "孙");
    		List<User> users = sqlSession.getMapper(UserMapper.class).getUserByMap(map);
    		for (User user1 : users) {
    			System.out.println(user1);
    		}
    	}*/
    	
    	
    	
    /*	private static void getUserLikeNameAndRole(SqlSession sqlSession) {
    		User user=new User();
    		user.setUserName("孙");
    		user.setUserRole(3);
    		List<User> users = sqlSession.getMapper(UserMapper.class).getUserLikeUserNameAndUserRole(user);
    		
    		for (User user1 : users) {
    			System.out.println(user1);
    		}
    		MyBatisUtil.closeSession(sqlSession);
    	}*/
    	
    	
    	
    /*	private static void getUserByName(SqlSession sqlSession) {
    		List<User> users = sqlSession.getMapper(UserMapper.class).getUserLikeUserName("孙");
    		for (User user : users) {
    			System.out.println(user);
    		}
    		MyBatisUtil.closeSession(sqlSession);
    	}*/
    	
    	
    	
    	
    /*	public static void getUserList(){
    		SqlSession session = MyBatisUtil.getSqlSession();
    		//綁定Mapper接口
    		List<User> userList = session.getMapper(UserMapper.class).getUserList();
    		for (User user : userList) {
    			logger.debug("用户：" + user);
    		}
    		MyBatisUtil.closeSession(session);
    	}*/
    	
    
    /*	private static void getUserCount() {
    		SqlSession session = MyBatisUtil.getSqlSession();
    		// 调用mapper文件进行数据操作
    		int count = session.selectOne("cn.bdqn.dao.UserMapper.count");
    
    		logger.debug("用户数量是：" + count);
    		MyBatisUtil.closeSession(session);
    	}*/
    }

注意，如果没有sqlSession.commit();这一段代码

运行结果：

\[DEBUG\] 2018-09-29 23:51:58,023 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection  
\[DEBUG\] 2018-09-29 23:51:58,225 org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1211076369.  
\[DEBUG\] 2018-09-29 23:51:58,241 cn.bdqn.dao.UserMapper.add - ooo Using Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:51:58,241 cn.bdqn.dao.UserMapper.add - ==>  Preparing: INSERT INTO smbms\_user ( \`userCode\`, \`userName\`, \`userPassword\`, \`gender\`, \`birthday\`, \`phone\`, \`address\`, \`userRole\`, \`createdBy\`, \`creationDate\`) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)   
\[DEBUG\] 2018-09-29 23:51:58,256 cn.bdqn.dao.UserMapper.add - ==> Parameters: andy(String), 刘德华(String), 1234567(String), 1(Integer), 2018-09-29 23:51:57.982(Timestamp), 13422369777(String), HK(String), 2(Integer), null, null  
增加了1条数据！  
\[DEBUG\] 2018-09-29 23:51:58,600 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Rolling back JDBC Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:51:58,868 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:51:58,870 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:51:58,870 org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1211076369 to pool.

好像增加成功了，实际上没有，具体如何有时间补回来

有了那段代码后运行结果如下：

\[DEBUG\] 2018-09-29 23:55:38,081 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection  
\[DEBUG\] 2018-09-29 23:55:38,308 org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1211076369.  
\[DEBUG\] 2018-09-29 23:55:38,308 cn.bdqn.dao.UserMapper.add - ooo Using Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:55:38,308 cn.bdqn.dao.UserMapper.add - ==>  Preparing: INSERT INTO smbms\_user ( \`userCode\`, \`userName\`, \`userPassword\`, \`gender\`, \`birthday\`, \`phone\`, \`address\`, \`userRole\`, \`createdBy\`, \`creationDate\`) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)   
\[DEBUG\] 2018-09-29 23:55:38,339 cn.bdqn.dao.UserMapper.add - ==> Parameters: andy(String), 刘德华(String), 1234567(String), 1(Integer), 2018-09-29 23:55:38.034(Timestamp), 13422369777(String), HK(String), 2(Integer), null, null  
\[DEBUG\] 2018-09-29 23:55:38,355 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Committing JDBC Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
增加了1条数据！  
\[DEBUG\] 2018-09-29 23:55:38,417 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:55:38,417 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection \[com.mysql.jdbc.JDBC4Connection@482f8f11\]  
\[DEBUG\] 2018-09-29 23:55:38,417 org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1211076369 to pool.

数据库：

![70][]

[70]: /images/20210923/fcbfb446af4a4611be728de58e0eb229.png