首页
   /       /   
MyBatis动态SQL实现
5月
13
MyBatis动态SQL实现
作者:大彭Sir    分类: 教学教程

创建数据库

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); 
}
本文标签:标签: Java SSM Mybatis SQL
责任声明:本页信息由网友自行发布或来源于网络,真实性、合法性由发布人负责,请仔细甄别!本站只为传递信息,我们不做任何双方证明,也不承担任何法律责任。文章内容若侵犯你的权益,请联系本站删除!
转载声明:本文作者大彭Sir,如需转载请保留文章出处!原文链接请自行复制!

Theme By Brief 鄂ICP备19010459号

站长统计 sitemap

首页

分类

友链

登录