mabits实现简单的增删改查

最新推荐文章于 2022-12-12 11:35:58 发布

原创最新推荐文章于 2022-12-12 11:35:58 发布 · 4.7k 阅读

5 ·

CC 4.0 BY-SA版权

本文介绍如何利用Mabits框架实现数据库的增删改查功能，包括创建模型、定义数据操作和执行SQL语句，帮助开发者快速进行数据库交互。

1.创建Maven工程

2.在pom.xml中配置所需要的jar包

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.6</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<dependency>
			<groupId>commons-logging</groupId>
			<artifactId>commons-logging</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.38</version>
		</dependency>

3.在resources中创建配置文件

目录如图所示：

sqlMapConfig中的配置：

<?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>
	<properties>
		<property name="driver" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost:3306/wang"/>
		<property name="username" value="root"/>
		<property name="password" value="root"/>
	</properties>


	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	
	
	
	<mappers>
		<mapper resource="mybatis/mapper/UserMapper.xml" />
	</mappers>
</configuration>

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">
<mapper namespace="userInfo">

	<select id="selectUserInfoById" resultType="com.zx.mybatis.sayHello.entity.UserInfo">
			select id, user_name userName, passwd, birthday, dept_id deptId from user_info where id = 2
	</select>

	<insert id="insertUserInfo" parameterType="com.zx.mybatis.sayHello.entity.UserInfo">
		insert into stuInfo(stuNo,stuName,gender,age) values(#{stuNo},#{stuName},#{gender},#{age})
	</insert>
	
	<delete id="deleteUserInfo" parameterType="com.zx.mybatis.sayHello.entity.UserInfo">
		delete from stuInfo where stuNo = #{stuNo}
	</delete>
	
	<update id="updateUserInfo" parameterType="com.zx.mybatis.sayHello.entity.UserInfo">
		update stuInfo set stuName=#{stuName},gender=#{gender},age=#{age} where stuNo=#{stuNo}
	</update>
</mapper>

4.编写java代码

创建UserInfo类

package com.zx.mybatis.sayHello.entity;

public class UserInfo {
	
	private String stuNo;
	
	private String stuName;
	
	private String gender;
	
	private int age;

	public String getStuNo() {
		return stuNo;
	}

	public void setStuNo(String stuNo) {
		this.stuNo = stuNo;
	}

	public String getStuName() {
		return stuName;
	}

	public void setStuName(String stuName) {
		this.stuName = stuName;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "UserInfo [stuNo=" + stuNo + ", stuName=" + stuName + ", gender=" + gender + ", age=" + age + "]";
	}
	
	
}

创建单元测试类

package com.zx.mybatis.sayHello.hello.first;

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

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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.Before;
import org.junit.Test;

import com.zx.mybatis.sayHello.entity.UserInfo;

public class MybatisTest {
	//构建session工厂
	private SqlSessionFactory ssf;
	
	private Log log = LogFactory.getLog(MybatisTest.class);
	
	/*
	    加载mybatis配置文件
	*/
	@Before
	public void init() {
		try {
			ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis/sqlMapConfig.xml"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/*
	 * 添加信息
	 * 
	*/
	@Test
	public void insertStu() {
		//创建能执行映射文件中sql的sqlsession
		SqlSession ss = ssf.openSession();
		
		UserInfo ui = new UserInfo();
		ui.setStuNo("2016010");
		ui.setStuName("詹姆斯");
		ui.setGender("男");
		ui.setAge(33);
		
		int affectedRows = ss.insert("userInfo.insertUserInfo", ui);
		
		log.info("--------"+affectedRows);
		ss.commit();
		ss.close();
	}
	/*
	 * 删除学生信息
	*/
	@Test
	public void deleteStu() {
		SqlSession ss = ssf.openSession();
		UserInfo ui = new UserInfo();
		ui.setStuNo("2017002");
		int affectedRows = ss.delete("userInfo.deleteUserInfo", ui);
		
		log.info("--------"+affectedRows);
		ss.commit();
		ss.close();
	}
	/*
	 * 修改学生信息
	*/
	@Test
	public void updateStu() {
		SqlSession ss = ssf.openSession();
		UserInfo ui = new UserInfo();
		ui.setStuName("刘达");
		ui.setGender("女");
		ui.setAge(10);
		ui.setStuNo("2016003");
		int affectedRows = ss.update("userInfo.updateUserInfo", ui);
		
		log.info("----------"+affectedRows);
		ss.commit();
		ss.close();
	}
	/*
	 * 查询学生信息，动态查询 ，sql分页
	*/
	@Test
	public void selectTest() {
		SqlSession ss = ssf.openSession();
		UserInfo ui = new UserInfo();
		ui.setStuName("张");
		List<UserInfo> stuList = ss.selectList("userInfo.selectUserInfoByAttr",ui);
		log.info("------------"+stuList);
		ss.close();
	}
	
	
	
	
	
}

注意动态查询，配置文件中为

<!-- 
	
		动态sql，分页查询
	 -->
	 <select id="selectUserInfoByAttrCount" parameterType="com.zx.mybatis.sayHello.entity.UserInfo" resultType="long">
	 	select count(stuNo) from userInfo
	 	<include refid="selectUserInfoByAttrSql"></include>
	 </select>
	 
	 <select id="selectUserInfoByAttr" parameterType="com.zx.mybatis.sayHello.entity.UserInfo" resultType="com.zx.mybatis.sayHello.entity.UserInfo">
	 	select*from stuInfo
	 	<include refid="selectUserInfoByAttrSql"></include>
	 	limit 0 , 5
	 </select>
	 
	 <sql id="selectUserInfoByAttrSql">
	 	<where>
	 		<if test="stuName != null and stuName !=''">
	 			and stuName like '%${stuName}%'
	 		</if>	
	 	</where>
	 </sql>

注意：namespace表示命名空间，对数据库的SQL信息进行分类话管理；

  id:表示sql语句的唯一身份，也称为statement ID

  resultType:表示sql执行输出结果

  parameterType:表示执行sql输入参数

  #{id} 表示一个占位符， id 表示接受输入参数的名称。

  ${} 与 #{}：${}表示sql 拼接字符串，如果参数类型为基本数值类型，则必须为${value},如果为引用类型，则写属性名称。