MyBatis动态SQL实现
MyBatis动态SQL实现
创建数据库 t_user库的名称 手动创建库 user 表名 /*SQLyog Ultimate v12.09 (64 bit) MySQL - 5.5.62 : Database - t_user ********************************************************************* */ /* !40101 SET NAMES utf8 */; /* !40101 SET SQL_MODE=''*/; /* !40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /* !40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /* !40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /* !40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /* !32312 IF NOT EXISTS*/`t_user` /* !40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */; USE `t_user`; /*Table structure for table `user` */ DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(20) COLLATE utf8_bin DEFAULT NULL, `password` varchar(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*Data for the table `user` */ insert into `user`(`id`,`username`,`password`) values (1,'tom1','123'), (2,'tom2','123'),(3,'tom3','123'),(4,'tom4','123'),(5,'tom5','123'), (6,'tom6','123'),(7,'tom7','123'),(8,'tom8','123'),(9,'tom9','123'), (10,'tom10','123'),(11,'tom11','123'),(12,'tom12','123'),(13,'tom13','123'); /* !40101 SET SQL_MODE=@OLD_SQL_MODE */; /* !40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /* !40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /* !40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 根据用户名或密码查询 * 一个条件都没有 * 只有用户名,没有密码 * 用户名 和 密码 都有 编写接口Usermapper //条件 public List<User> findUserByUserNameOrPws(User user); 编写sql语句 usermapper.xml中 <!--条件查询--> <select id="findUserByUserNameOrPws" resultType="user" parameterType="user"> /*username 参数对象user 中的属性 */ select * from user <where> <if test="username!=null"> and username=#{username} </if> <if test="password !=null"> and password=#{password} </if> </where> </select> 测试代码 findUser()只看这个方法 package com.haha.controller; import com.haha.domian.User; import com.haha.mapper.UserMapper; 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 java.io.File; import java.io.IOException; import java.io.InputStream; import java.util.List; public class TestMain { public static void main(String[] args) throws IOException { //1.查询所有数据 //list(); //2.查询数据 findUser(); } //条件查询用户 public static void findUser()throws IOException{ SqlSession sqlSession = getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user=new User(); user.setUsername("tom10"); List<User> list = mapper.findUserByUserNameOrPws(user); System.out.println(list); } //查询所有数据 public static void list()throws IOException{ SqlSession sqlSession = getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> all = mapper.findAll(); System.out.println(all); } //获取到sqlsession对象 public static SqlSession getSqlSession() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); return sqlSession; } } 批量查询 例子: select * from user where id in(1,3,5,6) 编写接口 public List<User> findByIds(List<Integer> list); 编写sql语句 <!--批量查询 根据id--> <select id="findByIds" resultType="user" parameterType="list"> /*select * from user where id in (xx,xxx)*/ select * from user <where> <foreach collection="list" open="id in (" close=")" item="id" separator=","> #{id} </foreach> </where> </select> 测试代码 //批量查询id的值 public static void findUserByids()throws IOException{ SqlSession sqlSession = getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<Integer> list=new ArrayList<>(); list.add(1); list.add(3); list.add(5); List<User> users = mapper.findByIds(list); System.out.println(users); }
 2022-5-13
     跳转到第 页,共 1 页

Theme By Brief 鄂ICP备19010459号

站长统计 sitemap

首页

分类

友链

登录