【功能】对person数据库实现以下功能
- 增(insertPerson,insertPersons)
- 删(deletePersonByPkId,deletePersonsById)
- 改(updatePersonByPkId,updatePersonById)
- 查(countPersons,getPersonByName,listPersons,listPersonsByNameOrPhone,listPersonsBySex)
【步骤】
【项目创建步骤】创建一个javaweb项目
输入项目名称,选择路径存储项目,点击finish。 如下所示为项目结构图:
【数据库】
【代码】
1.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>
<!-- 配置开发环境,可以配置多个,在具体用时再做切换 -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="test">
<environment id="test">
<transactionManager type="JDBC"></transactionManager> <!-- 事务管理类型:JDBC、MANAGED -->
<dataSource type="POOLED"> <!-- 数据源类型:POOLED、UNPOOLED、JNDI -->
<property name="driver" value="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://localhost:5432/people?characterEncoding=UTF-8" />
<property name="username" value="postgres" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 加载映射文件 mapper -->
<mappers>
<mapper class="main.java.com.mybatisstudy.mapper.PersonMapper"/>
</mappers>
</configuration>
2.实体类
package main.java.com.mybatisstudy.entity;
import java.util.UUID;
public class PersonDO {
private Long id;
private String personName;
private String sex;
private String phone;
public PersonDO() {
}
public PersonDO(Long id, String personName, String sex, String phone) {
this.id = id;
this.personName = personName;
this.sex = sex;
this.phone = phone;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getPersonName() {
return personName;
}
public void setPersonName(String personName) {
this.personName = personName;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "PersonDO{" +
"id=" + id +
", personName='" + personName + '\'' +
", sex=" + sex +
", phone='" + phone + '\'' +
'}';
}
}
3.创建PeopleMapper.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="main.java.com.mybatisstudy.mapper.PersonMapper">
<resultMap id="PersonDO" type="main.java.com.mybatisstudy.entity.PersonDO">
<result column="id" property="id"/>
<result column="person_name" property="personName"></result>
<result column="sex" property="sex"></result>
<result column="phone" property="phone"></result>
</resultMap>
<insert id="insertPerson" parameterType="main.java.com.mybatisstudy.entity.PersonDO">
insert into person (id,person_name,sex,phone) values (#{id},#{personName},#{sex},#{phone});
</insert>
<sql id="property">
id,person_name,sex,phone
</sql>
<insert id="insertPersons" parameterType="main.java.com.mybatisstudy.entity.PersonDO">
insert into person(<include refid="property"/>)
values
<foreach collection="list" item="personDO" index="index" separator=",">
(#{personDO.id},#{personDO.personName},#{personDO.sex},#{personDO.phone})
</foreach>
</insert>
<delete id="deletePersonByPkId" parameterType="Long">
delete from person where id=#{id}
</delete>
<update id="updatePersonByPkId">
update person set phone=#{phone} where id=#{id}
</update>
<select id="countPersons" resultType="Map">
select count(*) from person;
</select>
<select id="getPersonByName" parameterType="String" resultMap="PersonDO">
select * from person where person_name=#{personName}
</select>
<select id="listPersonsBySex" parameterType="String" resultMap="PersonDO">
select * from person where sex=#{sex}
</select>
<select id="listPersons" resultMap="PersonDO">
select * from person
</select>
<select id="listPersonsByNameOrPhone" resultMap="PersonDO">
select * from person
<where>
<if test="personName!=null and personName!=''">
person_name=#{personName}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</where>
</select>
<update id="updatePersonById" parameterType="main.java.com.mybatisstudy.entity.PersonDO">
update person
<set>
<if test="personName!=null and personName!=''">
person_name=#{personName},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone}
</if>
</set>
where id=#{id}
</update>
<delete id="deletePersonsById" parameterType="Long">
delete from person where id in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>
4.创建PeopleMapper接口
package main.java.com.mybatisstudy.mapper;
import main.java.com.mybatisstudy.entity.PersonDO;
import org.apache.ibatis.annotations.Param;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
/**
* 添加人员
* @param personDO
* @return 结果
*/
int insertPerson(PersonDO personDO);
/**
* 批量添加人员
* @param personDOs
* @return 结果
*/
int insertPersons(List<PersonDO> personDOs);
/**
* 通过pkId删除人员
* @param id
* @return 结果
*/
int deletePersonByPkId(Long id);
/**
* 通过pkId修改人员信息
* @param id
* @param phone
* @return 结果
*/
int updatePersonByPkId(@Param("phone") String phone, @Param("id") Long id);
/**
* 统计人员总数
* @return 结果
*/
Map<String,Integer> countPersons();
/**
* 通过人员姓名获取人员信息(暂不考虑同名人)
* @param personName
* @return 人员信息
*/
PersonDO getPersonByName(String personName);
/**
* 通过性别获取所有人员
* @param sex
* @return 某性别的人员信息列表
*/
List<PersonDO> listPersonsBySex(String sex);
/**
* 获取所有人员
* @return 所有人员信息
*/
List<PersonDO> listPersons();
/**
* 动态查询
* 通过人员姓名或人员电话获取人员信息(考虑同名人员)
* @param personName
* @param phone
* @return 满足条件人员信息
*/
List<PersonDO> listPersonsByNameOrPhone(@Param("personName") String personName,@Param("phone") String phone);
/**
* 根据id动态修改人员信息
* @param personDO
* @return
*/
int updatePersonById(PersonDO personDO);
/**
* 批量删除人员信息表
* @param ids
* @return
*/
int deletePersonsById(List<Long> ids);
}
5.编写单元测试类一
package test.java.com.mybatisstudy;
import main.java.com.mybatisstudy.entity.PersonDO;
import main.java.com.mybatisstudy.mapper.PersonMapper;
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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
public class MybatisStudy {
@Test
public void listPersonsTest(){
SqlSession sqlSession=null;
try {
sqlSession=getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
List<PersonDO> persons = personMapper.listPersons();
if(persons!=null){
for(PersonDO personDO:persons){
System.out.println(personDO.toString());
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void listPersonsBySexTest(){
SqlSession sqlSession= null;
try {
sqlSession = getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
List<PersonDO> persons = personMapper.listPersonsBySex("男");
sqlSession.commit();
if(persons!=null){
for(PersonDO personDo:persons){
System.out.println(personDo.toString());
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void getPersonByNameTest(){
SqlSession sqlSession= null;
try {
sqlSession = getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
PersonDO person = personMapper.getPersonByName("HAHA");
sqlSession.commit();
System.out.println(person.toString());
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void countPersonsTest(){
try {
SqlSession sqlSession=getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
Map<String,Integer> result = personMapper.countPersons();
System.out.println("共有"+result.get("count")+"条人员信息");
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void updatePersonByPkIdTest(){
try {
SqlSession sqlSession=getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
int result=personMapper.updatePersonByPkId("12345",1L);
sqlSession.commit();
System.out.println("修改的结果为:"+result);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void deletePersonByPkIdTest(){
try {
SqlSession sqlSession=getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
int result=personMapper.deletePersonByPkId(3L);
sqlSession.commit();
System.out.println("删除的结果为:"+result);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void insertPersonsTest(){
List<PersonDO> personDOS=new ArrayList<>();
personDOS.add(new PersonDO(13L,"王二小","男","12309787"));
personDOS.add(new PersonDO(14L,"王小","女","12323209787"));
personDOS.add(new PersonDO(15L,"钱钱","男","129999787"));
personDOS.add(new PersonDO(163L,"吼吼","男","17777787"));
try {
SqlSession sqlSession=getSqlSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
int result=personMapper.insertPersons(personDOS);
sqlSession.commit();
if(result>0){
System.out.println("添加人员信息成功");
}
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void insertPersonTest(){
PersonDO personDO=new PersonDO();
personDO.setId(4L);
personDO.setPersonName("王五");
//personDO.setSex("男");
personDO.setPhone("12345678901");
try {
SqlSession sqlSession = getSqlSession();
PersonMapper mapper=sqlSession.getMapper(PersonMapper.class);
int result = mapper.insertPerson(personDO);
sqlSession.commit();
if(result==1){
System.out.println("添加人员信息成功");
}
} catch (IOException e) {
e.printStackTrace();
}
}
public SqlSession getSqlSession() throws IOException {
// 定位核心配置文件
String resource = "main/java/com/mybatisstudy/sqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建 SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取到 SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
}
6.编写单元测试类二(动态)
package test.java.com.mybatisstudy;
import main.java.com.mybatisstudy.entity.PersonDO;
import main.java.com.mybatisstudy.mapper.PersonMapper;
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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class MybatisStudy2 {
@Test
public void deletePersonsByIdTest(){
InputStream inputStream=null;
try {
String resource="main/java/com/mybatisstudy/sqlMapConfig.xml";
inputStream= Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
List<Long> list=new ArrayList<>();
list.add(23L);
list.add(12L);
list.add(163L);
list.add(13L);
int result = personMapper.deletePersonsById(list);
System.out.println("数据库共删除数据:"+result+"条");
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void updatePersonByPkIdTest(){
InputStream inputStream=null;
try {
String resource="main/java/com/mybatisstudy/sqlMapConfig.xml";
inputStream=Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
PersonDO personDO=new PersonDO(4L,"ceshi","男","");
int result=personMapper.updatePersonById(personDO);
System.out.println("修改记录结果为:"+result);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void listPersonsByNameOrPhoneTest() throws IOException {
String resource = "main/java/com/mybatisstudy/sqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建 SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取到 SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
List<PersonDO> persons = personMapper.listPersonsByNameOrPhone("张三", "");
System.out.println(persons);
if(persons!=null){
for(PersonDO personDO:persons){
System.out.println(personDO.toString());
}
}
}
}
完结。。。