Mybatis(二)—实现数据库的增删改查

原创 于 2019-01-05 17:20:17 发布 · 206 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#Mybatis

一 创建数据库

在mysql中创建mybatis数据库，以便以后的学习
/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50521
Source Host           : localhost:3306
Source Database       : mybatis

Target Server Type    : MYSQL
Target Server Version : 50521
File Encoding         : 65001

Date: 2018-12-09 16:03:53
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '下单用户id',
  `number` varchar(32) NOT NULL COMMENT '订单号',
  `createtime` datetime NOT NULL COMMENT '创建订单时间',
  `note` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `order` VALUES ('3', '1', '1000010', '2018-12-09 13:22:35', null);
INSERT INTO `order` VALUES ('4', '1', '1000011', '2018-12-11 13:22:41', null);
INSERT INTO `order` VALUES ('5', '10', '1000012', '2018-12-20 16:13:23', null);

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2018-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);

二 创建User实体类

package cn.tz.pojo;

import java.util.Date;

public class User {
	private Integer id;
	private String username;
	private Date birthday;
	private Integer sex; 
	private String  address;
	private String  uuuid;
	public String getUuuid() {
		return uuuid;
	}
	public void setUuuid(String uuuid) {
		this.uuuid = uuuid;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public Integer getSex() {
		return sex;
	}
	public void setSex(Integer sex) {
		this.sex = sex;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
				+ address + ", uuuid=" + uuuid + "]";
	} 
}

三 创建MyBatis全局配置文件

新建config包来保存SqlMapConfig.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">
<configuration>
	<!--后期跟spring整合的时候是会废除的  -->
	<environments default="test">
		<environment id="test">
		<!--使用的是jdbc的事物管理  -->
			<transactionManager type="JDBC"/>
			<!-- 配置连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
				<property name="username" value="账号"/>
				<property name="password" value="密码"/>
			</dataSource>
		</environment>
	</environments>
	<!-- 加载配置文件 -->
	<mappers>
		<mapper resource="mybatis/user.xml"/>
	</mappers>
</configuration>

四 创建SQL映射文件

<?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 命名空间    用于隔离sql语句     -->
<mapper namespace="user">
<!-- id   语句的唯一表示
	parameterType  入参的类型
	resultType    返回结果的数据类型
	#{}       相当于jdbc的？号
	   -->
	<select id="getUserById" parameterType="int" resultType="cn.tz.pojo.User" >
		select
			`id`,
			`username`,
			`birthday`,
			`sex`,
			`address`
		from `user`
		where id =#{id}
	</select>
	  
	<!-- ${}   相当于字符串的拼接    入参为普通类型的话       只能写value
			如果入参为pojo的话   就写pojo的属性
	 -->
	<select id="getUserByUserName" parameterType="string" resultType="cn.tz.pojo.User" >
		select
			`id`,
			`username`,
			`birthday`,
			`sex`,
			`address`
		from `user`
		where username like '%${value}%'
		
	</select>
	
	<!--
	useGeneratedKeys  参数为ture的时候使用自增  
	keyProperty 他们是配套使用的  这个 是user的主键    
	-->
	<insert id="insertUser" parameterType="cn.tz.pojo.User"  useGeneratedKeys="true" keyProperty="id">
	<!-- 
		keyProperty 主键返回  
		resultType     user中的主键的属性
		order      指定 selectKey  何时执行     AFTER  是之前执行
	 -->
		<!-- <selectKey keyProperty="id" resultType="int" order="AFTER">
			select last_insert_id()
		</selectKey> -->
		
		<selectKey  keyProperty="uuuid" resultType="string"  order="BEFORE">
			select uuid()
		</selectKey>
		insert into `user`(
			`username`,
			`birthday`,
			`sex`,
			`address`,
			`uuuid`)
		values (
			#{username},
			#{birthday},
			#{sex},
			#{address},
			#{uuuid}
		)
	</insert>
	
	<update id="updateUser" parameterType="cn.tz.pojo.User">
		update `user`
		set `username` = #{username}
		where `id`= #{id}
	</update>
	
	<delete id="deleteUser" parameterType="int">
		delete 
		from `user`
		where `id` =#{id}
	</delete>
	
	
</mapper>

五 测试

package cn.tz.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

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.Test;

import cn.tz.pojo.User;
import cn.tz.utils.SqlSessionFactoryUtils;

public class test {
	
	@Test
	public void test1() throws IOException{
		//编写sql语句
		//配置user映射文件
		//编写测试程序
		//写代码
		//加载配置文件
		//第一步   创建SqlSessionFactoryBuilder对象
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		//第二部    加载核心配置文件
		InputStream inputStream = Resources.getResourceAsStream("config/SqlMapConfig.xml");
		//第三部      创建SqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
		//第四部   SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//第五步  得到user对象
		User user = sqlSession.selectOne("getUserById", 1);
		// 第六步  打印
		System.out.println(user);
		//第七部    关闭资源
		sqlSession.close();
	}
	
	
	//根据用户的username查询用户信息
	@Test
	public void test2() throws IOException{
		SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession();
		List<User> listuser = sqlSession.selectList("getUserByUserName", "张");
		for (User user : listuser) {
			System.out.println(user);
		}
		sqlSession.close();
	}
	//添加用户的操作
	@Test
	public void test3() throws IOException{
		SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		User user = new User();
		user.setUsername("红枣");
		user.setSex(0);
		user.setBirthday(new Date());
		user.setAddress("火星");
		
		sqlSession.insert("insertUser", user);
		//提交事务   
		//sqlSession.commit();
		System.out.println(user);
		sqlSession.close();
		
	}
	
	//修改数据
	@Test
	public void test4() throws IOException{
		SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		User user = new User();
		user.setId(26);
		user.setUsername("火星222");
		sqlSession.update("updateUser", user);
		sqlSession.close();
	}
	
	//删除
	@Test
	public void test5() throws IOException{
		SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		sqlSession.delete("deleteUser", 27);
		sqlSession.close();
	}
}

六 SqlSessionFactory的封装

package cn.tz.utils;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SqlSessionFactoryUtils {
	private static SqlSessionFactory sqlSessionFactory;
	
	static {
		try {
			//创建SqlSessionFactoryBuilder工厂
			SqlSessionFactoryBuilder ssfd = new SqlSessionFactoryBuilder();
			//创建核心配置文件的输入流
			InputStream inputStream = Resources.getResourceAsStream("config/SqlMapConfig.xml");
			//通过输入流创建SqlSessionFactory对象
			sqlSessionFactory = ssfd.build(inputStream);
			
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取SqlSessionFactory
	 * @return
	 */
	public static SqlSessionFactory getSqlSessionFactory() {
		return sqlSessionFactory;
	}
	
	
}
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值