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

布满荆棘的人生 2021-09-27 12:52 263阅读 0赞

修改密码

UserMapper.java新增一个方法

package cn.bdqn.dao;
    
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Param;
    
    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);
    	int update(User user);
    	int updatePassword(@Param("id")Long id,@Param("newPassword")String newPassword);
    }

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>
      	
      	<update id="update" parameterType="user">
      	
      		UPDATE `smbms_user` SET 
      		`userCode`=#{userCode},
      		`userName`=#{userName},
      		`userPassword`=#{userPassword},
      		`gender`=#{gender},
      		`birthday`=#{birthday},
      		`phone`=#{phone},
      		`address`=#{address},
      		`userRole`=#{userRole},
      		`modifyBy`=#{modifyBy},
      		`modifyDate`=#{modifyDate}
      		WHERE id=#{id}
      	</update>
      	
      	<update id="updatePassword">
      		UPDATE `smbms_user` SET 
      		`userPassword`=#{newPassword}
      		WHERE id=#{id}
      	</update>
    </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);
    		
    		//修改
    		//User user=new User(17L, "andy", "陈大锤", "1234567", 1, "13422369777", "HK", new Date(), 2, null, null, 1, new Date());
    		//int count = sqlSession.getMapper(UserMapper.class).update(user);
    		
    		int count = sqlSession.getMapper(UserMapper.class).updatePassword(17L, "0000000");
    		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);
    	}*/
    }

运行结果如下：

\[DEBUG\] 2018-09-30 00:10:09,618 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection  
\[DEBUG\] 2018-09-30 00:10:09,824 org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1268650975.  
\[DEBUG\] 2018-09-30 00:10:09,824 cn.bdqn.dao.UserMapper.updatePassword - ooo Using Connection \[com.mysql.jdbc.JDBC4Connection@4b9e13df\]  
\[DEBUG\] 2018-09-30 00:10:09,824 cn.bdqn.dao.UserMapper.updatePassword - ==>  Preparing: UPDATE \`smbms\_user\` SET \`userPassword\`=? WHERE id=?   
\[DEBUG\] 2018-09-30 00:10:09,861 cn.bdqn.dao.UserMapper.updatePassword - ==> Parameters: 0000000(String), 17(Long)  
\[DEBUG\] 2018-09-30 00:10:09,892 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Committing JDBC Connection \[com.mysql.jdbc.JDBC4Connection@4b9e13df\]  
修改了1条数据！  
\[DEBUG\] 2018-09-30 00:10:09,939 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection \[com.mysql.jdbc.JDBC4Connection@4b9e13df\]  
\[DEBUG\] 2018-09-30 00:10:09,939 org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection \[com.mysql.jdbc.JDBC4Connection@4b9e13df\]  
\[DEBUG\] 2018-09-30 00:10:09,939 org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1268650975 to pool.

数据库（123456修改为000000）：

![70][]

[70]: /images/20210923/9c07738a009c45c9a93fcdbd97202103.png