<?xml version="1.0" encoding="UTF-8" ?>
//User实体类中的属性public class User { private Integer id; private String userName; private Date birthday; private String sex; private String address;}
insert into users(username, birthday, sex, address) values(#{userName}, #{birthday}, #{sex}, #{address})
用于字符串的拼接和字符串的替换
需求:在users表中,根据地址或者用户名模糊查询用户信息
sql语句:
select * from users where username like '%模糊查询条件%'
select * from users where address like '%模糊查询条件%'
存在的问题:两条sql语句的结构在本质上是相同的,写两条语句十分冗余,可以采用替换列名的方式进行优化
package com.example.mapper;import com.example.pojo.User;import org.apache.ibatis.annotations.Param;import java.util.List;/** * 数据访问层的接口,定义对数据库完成的CRUD的操作 */public interface UsersMapper { //根据用户名或者地址模糊查询 List getByNameOrAddress( @Param("colName") String colName, @Param("userName") String userName );}
<?xml version="1.0" encoding="UTF-8" ?>
package com.example.mapper;import com.example.pojo.User;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 java.io.IOException;import java.io.InputStream;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;public class TestUsersMapper { //时间刷 SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd"); //SqlSession对象 SqlSession sqlSession; //mybatis动态代理对象 UsersMapper usersMapper; //获取SqlSession @Before public void getSqlSession() throws IOException { //读取核心配置文件 InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml"); //创建SqlSessionFactory对象 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //获取SqlSession sqlSession = factory.openSession(); //获取mybatis动态代理对象 usersMapper = sqlSession.getMapper(UsersMapper.class); } //归还SqlSession @After public void closeSession(){ sqlSession.close(); } @Test public void testGetByNameOrAddress(){ List users = usersMapper.getByNameOrAddress("username", "小"); //List users = usersMapper.getByNameOrAddress("address", "市"); users.forEach(System.out::println); }}
Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]Opening JDBC ConnectionCreated connection 1293462056.Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28] ==> Preparing: select id, username, birthday, sex, address from users where username like concat('%', ?, '%')==> Parameters: 小(String) <== Columns: id, username, birthday, sex, address<== Row: 2, 小王, 2001-07-12, 1, 芜湖市<== Row: 3, 小张, 1999-02-22, 1, 长沙<== Row: 29, 小昕, 2001-03-14, 女, 忻州<== Total: 3Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}Users{id=3, userName='小张', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='长沙'}Users{id=29, userName='小昕', birthday=Wed Mar 14 00:00:00 CST 2001, sex='女', address='忻州'}Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]Returned connection 1293462056 to pool.Process finished with exit code 0
Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]Opening JDBC ConnectionCreated connection 1293462056.Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28] ==> Preparing: select id, username, birthday, sex, address from users where address like concat('%', ?, '%')==> Parameters: 市(String) <== Columns: id, username, birthday, sex, address<== Row: 2, 小王, 2001-07-12, 1, 芜湖市<== Row: 7, 学委, 2001-05-13, 2, 平顶山市<== Total: 2Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}Users{id=7, userName='学委', birthday=Sun May 13 00:00:00 CST 2001, sex='2', address='平顶山市'}Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d18aa28]Returned connection 1293462056 to pool.Process finished with exit code 0
==> Preparing: select id, username, birthday, sex, address from users where username like concat('%', ?, '%')==> Parameters: 小(String)
==> Preparing: select id, username, birthday, sex, address from users where address like concat('%', ?, '%')==> Parameters: 市(String)
留言与评论(共有 0 条评论) “” |