mysql动态查询
mapper接口文件:
package qin.com.mapper;
import org.apache.ibatis.annotations.Param;
import qin.com.entity.Users;
import javax.swing.event.ListDataEvent;
import java.util.Date;
import java.util.List;
public interface UsersMapper {
public List<Users> getAll();
int update(Users users);
Users getUsersById(int id);
Users getNameLike(String userName);
List<Users> getByNameGood(String name);
int addUsers(Users users);
int deleteUserById(int id);
List<Users> getByNameAndAddress(@Param("columnName") String columnName,@Param("columnValue") String columnValue);
List<Users> selectUserByCondition(Users users);
int updateBySet(Users users);
List<Users> getByIds(Integer []arr);
int delByIds(Integer []arr);
int addByIds(List<Users> users);
int updateByIds(List<Users> users);
List<Users> selectByIDS(Integer []arr);
int addUserList(List<Users> users);
List<Users> getByBirthday(Date begin,Date end);
}
mapper.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="qin.com.mapper.UsersMapper"> <!-- 一般为数据表在持久层对应接口的完整路径 -->
<resultMap id="userBean" type="qin.com.entity.Users"> <!-- 一般为数据表对应实体类所在数据包 -->
<id property="id" column="id"></id>
<result property="userName" column="userName"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</resultMap>
<!--定义SQL代码片段-->
<sql id="allColumns">
id,username,birthday,sex,address
</sql>
<!-- 查询数据表中所有数据,这相标签的id值要与数据表对应接口中的查询方法的名称要一样 -->
<select id="getAll" resultType="qin.com.entity.Users">
select <include refid="allColumns"/>
from users
</select>
<!-- private int id;
private String userName;
private Date birth;
private String sex;
private String address;
#{}写的是实体类的属性名,注意大小写
-->
<update id="update" parameterType="Users">
update users set username=#{userName},birthday=#{birthday},sex=#{sex},address=#{address}
where id=#{id}
</update>
<select id="getUsersById" resultType="qin.com.entity.Users">
select * from users where id=#{id}
</select>
<insert id="addUsers" parameterType="Users">
<selectKey keyProperty="id" resultType="int" order="AFTER">
select last_insert_id()
</selectKey>
insert into users(username, birthday, sex, address)
values(#{userName},#{birthday},#{sex},#{address})
</insert>
<delete id="deleteUserById" parameterType="int">
delete from users where id=#{id}
</delete>
<select id="getNameLike" resultType="qin.com.entity.Users">
select * from users where username like '%${userName}%'
</select>
<!--
<select id="getByNameGood" parameterType="string" resultType="qin.com.entity.Users">
select <include refid="allColumns"/>
from users where username like concat('%',#{name},'%')
</select>
<!--
如果参数超过一个,则parameterType不写
List<Users> getByNameAndAddress(String colum,String columValue);-->
<select id="getByNameAndAddress" resultType="qin.com.entity.Users">
select <include refid="allColumns"/>
from users
where ${columnName} like concat('%',#{columnValue},'%')
</select>
<!--
int updateBySet(Users users);-->
<update id="updateBySet" parameterType="Users">
update users
<set>
<if test="userName != null and userName != ''">
userName = #{userName},
</if>
<if test="birthday != null">
brithday =#{brithday},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="address != null and address != ''">
address #{address},
</if>
</set>
where id = #{id}
</update>
<!--
根据实体类中的成员变量是否有值来决定是否添加条件
List<Users> getByCondition(Users users);-->
<select id="selectUserByCondition" resultType="Users" parameterType="Users">
select <include refid="allColumns"></include>
from users
<where>
<if test="userName != null and userName != ''" >
and username like concat('%',#{userName},'%')
</if>
<if test="birthday != null" >
and birthday = #{birthday}
</if>
<if test="sex != null and sex != ''" >
and sex = #{sex}
</if>
<if test="address != null and address != ''" >
and address like concat('%',#{address},'%')
</if>
</where>
</select>
<!--
List<Users> delByIds(Integer []arr);-->
<delete id="delByIds">
delete from users
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--
int addByIds(List<Users> users);-->
<insert id="addByIds">
insert into users(username, birthday, sex, address)
values
<foreach collection="list" item="user" separator=",">
(#{user.userName},#{user.birthday},#{user.sex},#{user.address})
</foreach>
</insert>
<!--
int updateByIds(List<Users> users);-->
<update id="updateByIds">
<foreach collection="list" item="user" separator=";">
update users
<set>
<if test="user.userName != null and user.userName != ''">
username = #{user.userName},
</if>
<if test="user.birthday != null">
birthday = #{user.birthday},
</if>
<if test="user.sex != null and user.sex != ''">
sex = #{user.sex},
</if>
<if test="user.address != null and user.address != ''">
address = #{user.address}
</if>
</set>
where id = #{user.id}
</foreach>
</update>
<!--
List<Users> getByIds(Integer []arr);
-->
<select id="getByIds" resultType="qin.com.entity.Users">
select <include refid="allColumns"></include>
from users
where id in
<!-- (
<foreach collection="array" item="id" separator=",">
#{id}
</foreach>
)-->
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<!--
List<Users> selectByIDS(Integer[] arr);-->
<select id="selectByIDS" resultType="qin.com.entity.Users">
select <include refid="allColumns"></include>
from users
where id in
<foreach collection="array" item="id" separator="," open="(" close=")" >
#{id}
</foreach>
</select>
<!--
int addUserList(List<Users> users);-->
<insert id="addUserList">
insert into users(username, birthday, sex, address) values
<foreach collection="list" item="user" separator=",">
(#{user.userName},#{user.birthday},#{user.sex},#{user.address})
</foreach>
</insert>
<!--
List<Users> getByBirthday(Date begin,Date end);
对应between #{arg0} in #{arg1}
-->
<select id="getByBirthday" resultType="qin.com.entity.Users">
select <include refid="allColumns"></include>
from users
where birthday between #{arg0} and #{arg1}
</select>
</mapper>
mybatis-config.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>
<typeAliases>
<package name="qin.com.entity"></package>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost/mybatis"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</dataSource>
</environment>
</environments>
<mappers>
<package name="qin.com.mapper"/>
</mappers>
</configuration>
Test测试类
package qin.com.service;
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.After;
import org.junit.Before;
import org.junit.Test;
import qin.com.entity.Users;
import qin.com.mapper.UsersMapper;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
public class MyTest {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
SqlSession sqlSession;
UsersMapper usersMapper;
@Before
public void openSqlSession() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
usersMapper = sqlSession.getMapper(UsersMapper.class);
}
@After
public void closeSqlsSession(){
sqlSession.close ();
}
@Test
public void testgetAll() throws IOException {
System.out.println(usersMapper.getClass());
List<Users> usersList = usersMapper.getAll();
}
@Test
public void testUpdate() throws ParseException {
Users users = new Users(31,"大白奶子", sdf.parse("2022-12-20"), "2", "美国");
int count = usersMapper.update(users);
System.out.println(count);
sqlSession.commit();
}
@Test
public void testgetUsersById(){
Users usersById = usersMapper.getUsersById(2);
usersById.toString();
}
@Test
public void testgetNameLike(){
Users nameLike = usersMapper.getNameLike("虎");
nameLike.toString();
}
@Test
public void testAddUser() throws ParseException {
Users users = new Users("大屁股大白奶子", sdf.parse("2022-02-02"), "2", "日本");
int i = usersMapper.addUsers(users);
System.out.println(i);
sqlSession.commit();
System.out.println(users);
}
@Test
public void testdeleteUserById(){
int i = usersMapper.deleteUserById(33);
System.out.println(i);
sqlSession.commit();
}
@Test
public void testgetByNameGood(){
List<Users> nameLike = usersMapper.getByNameGood("虎");
for(Users users:nameLike){
System.out.println(users);
}
}
@Test
public void testgetByNameAndAddress(){
List<Users> nameLike = usersMapper.getByNameAndAddress("address","市");
for(Users users:nameLike){
System.out.println(users);
}
}
@Test
public void testUUID(){
UUID uuid = UUID.randomUUID();
System.out.println(uuid);
}
@Test
public void testUpdateByCondition(){
Users users = new Users();
users.setId(35);
users.setUserName("小奶子");
int num = usersMapper.updateBySet(users);
System.out.println(num);
sqlSession.commit();
}
@Test
public void testSelectByIDS(){
Integer[] array = {1,3,5,31,32,36};
for(int i=0;i<array.length;i++){
System.out.println(array[i]);
}
List<Users> list = usersMapper.selectByIDS(array);
list.forEach(users -> System.out.println(users));
}
@Test
public void testaddUserList() throws ParseException {
Users users1 = new Users("翘臀大美女1",sdf.parse("2022-02-02"),"2","日本1");
Users users2 = new Users("翘臀大美女2",sdf.parse("2022-02-02"),"2","日本2");
Users users3 = new Users("翘臀大美女3",sdf.parse("2022-02-02"),"2","日本3");
Users users4 = new Users("翘臀大美女4",sdf.parse("2022-02-02"),"2","日本4");
List list = new ArrayList();
list.add(users1);
list.add(users2);
list.add(users3);
list.add(users4);
int i = usersMapper.addUserList(list);
System.out.println(i);
sqlSession.commit();
}
@Test
public void testgetBirthday() throws ParseException {
Date begin = sdf.parse("2018-02-02");
Date end = sdf.parse("2021-08-08");
List<Users> byBirthday = usersMapper.getByBirthday(begin, end);
byBirthday.forEach(users -> System.out.println(users));
}
}