输入参数:parameterType
1.类型为 简单类型(8个基本类型+String)
#{任意值}
${value},其中的标识符只能是value
#{}自动给String类型加上单引号(自动类型转换)
${}原样输出,但是适合于动态排序(动态字段)
select * from Person where name = #{id}
select * from Person where name = '${value}'
//动态排序,根据传入的参数进行降序desc
//如果使用#{column},根据字符串排序
//因为#{}自动给String类型加上单引号
<select id="queryAllPersonOrderByColumn" parameterType="String" resultType="Person">
select * from Person order by ${value} desc
</select>
#{}可以防止SQL注入
${}不防止
2.类型为 对象类型
#{属性名}
${属性名}
3.类型为 嵌套对象类型
#{属性名.属性名}
${属性名.属性名}
//xxx.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8
username=root
password=123456
//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>
<properties resource="db.properties" />
<!--
<settings>
<setting name="cacheEnabled" value="false"/>
<setting name="lazyLoadingEnabled" value="false"/>
</settings>
-->
<!-- 设置单个/多个别名(别名忽略大小写) -->
<typeAliases>
<!-- 设置单个别名 -->
<!-- <typeAlias type="org.lanqiao.entity.Person" alias="Person"/> -->
<!-- 批量设置别名,以下会自动将该包中的所有类批量定义别名:别名就是类名 -->
<package name="org.lanqiao.entity"/>
</typeAliases>
<typeHandlers>
<typeHandler handler="org.lanqiao.converter.BooleanAndNumberConverter" javaType="Boolean" jdbcType="INTEGER" />
</typeHandlers>
<!-- default和id值相同时才能连接成功,测试类中也可以修改默认值 -->
<environments default="development">
<!-- 开发环境(自己的计算机) -->
<environment id="development">
<!-- 事务提交方式:
JDBC:利用JDBC方式处理事务(手工)
MANAGED:将事务交由其他组件去托管(spring,jobss)默认会关闭连接
-->
<transactionManager type="JDBC" />
<!-- 取消MANAGED的默认关闭连接 -->
<!-- <property name="closeConnection" value="false" /> -->
<!-- 数据源类型:
POOLED:使用数据库连接池(三方)
UNPOOLED:传统的JDBC模式,每次访问数据库,均需要打开、关闭等数据库操作,比较消耗性能
JNDI:从tomcat中获取一个内置的数据库连接池
-->
<dataSource type="POOLED">
<!-- 配置数据库信息 -->
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
<!-- 真正的项目应该在 发布的那台计算机上运行 -->
<environment id="shishi">
<!-- 数据库事务处理 -->
<transactionManager type="JDBC" />
<!-- 配置数据库信息 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载映射文件 -->
<mapper resource="org/lanqiao/mapper/personMapper.xml" />
</mappers>
</configuration>
//personMapper.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">
<!-- namespace:该mapper.xml映射文件的唯一标识 -->
<mapper namespace="org.lanqiao.mapper.PersonMapper">
<!-- 后续通过namespace+"."+id唯一定位sql语句 -->
<!--
parameterType:输入参数的类型
resultType:查询结果的返回值类型
resultType值如果没有别名需要写全包名+"."+类名
parameterType和resultType在形式上都只能有一个,但多个参数可以使用对象和集合来代替
parameterType和resultType如果是简单类型(8个基本类型和String),可以使用任何占位符:#{xxx};如果是对象类型,则必须是对象的属性:#{属性名}
resultType如果是一个对象,查询结果无论是一个还是多个,resultType都写对象:Person
-->
<!-- 查询:使用了类型转换器
1、如果类的属性和表的字段类型能够合理识别(String-varchar),则可以使用resultType;否则(boolean-number)使用resultMap
2、如果类的属性名和表的字段名能够合理识别(id-id),则可以使用resultType;否则(id-perno)使用resultMap
-->
<select id="queryPersonByIdWithConverter" resultMap="personResult" parameterType="int">
select * from Person where id = #{id}
</select>
<resultMap type="Person" id="personResult">
<!-- 分为主键id和非主键result -->
<id property="id" column="id" />
<result property="name" column="name" />
<result property="age" column="age" />
<!-- jdbcType的值integer要大写 -->
<result property="sex" column="sex" javaType="boolean" jdbcType="INTEGER" />
</resultMap>
<insert id="addPersonWithConverter" parameterType="Person">
insert into person values(#{id},#{name},#{age},#{sex,javaType=boolean,jdbcType=INTEGER})
</insert>
<update id="updatePersonById" parameterType="Person">
update person set id=#{id},name=#{name},age=#{age},sex=#{sex,javaType=boolean,jdbcType=INTEGER} where id= #{id}
</update>
<delete id="deletePersonById" parameterType="int">
delete from person where id= #{id}
</delete>
<select id="queryAllPerson" resultType="Person">
select * from Person
</select>
<select id="queryAllPersonOrderByColumn" parameterType="String" resultType="Person">
select * from Person order by ${value} desc
</select>
<select id="queryPersonByNameOrAge" parameterType="Person" resultType="Person">
select * from Person where name like #{name} or age=#{age}
</select>
<!-- parameterType为嵌套对象类型 -->
<select id="queryPersonByAddress" parameterType="Person" resultType="Person">
select * from Person where homeAddress = #{address.homeAddress} or schoolAddress = '${address.schoolAddress}'
</select>
</mapper>
//BooleanAndNumberConverter.java
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
//BaseTypeHandler<java类型>
public class BooleanAndNumberConverter extends BaseTypeHandler<Boolean> {
/*
* ps:PreparedStatement对象
* i:PreparedStatement对象操作参数的位置
* parameter:java值
* jdbcType:jdbc操作的数据库类型
*/
// java(boolean) --> DB(number)
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Boolean parameter, JdbcType jdbcType)
throws SQLException {
if(parameter) {
ps.setInt(i,1);
}else {
ps.setInt(i,0);
}
}
// DB --> java
@Override
public Boolean getNullableResult(ResultSet rs, String columnName) throws SQLException {
int sexNum=rs.getInt(columnName);//rs.getInt("id");列名
return sexNum==1;
}
// DB --> java
@Override
public Boolean getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
int sexNum=rs.getInt(columnIndex);//rs.getInt(1);下标
return sexNum==1;
}
// DB --> java
@Override
public Boolean getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
int sexNum=cs.getInt(columnIndex);//cs.getInt(1);存储过程
return sexNum==1;
}
}
//Person.java
public class Person {
private int id;
private String name;
private int age;
private boolean sex;
private Address address;
public Person() {
}
public Person(int id, String name, int age ,boolean sex) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = sex;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public String toString() {
return this.id+"-"+this.name+"-"+this.age+"-"+this.sex+"-"+this.address;
}
}
//Address.java
public class Address {
private String homeAddress;
private String schoolAddress;
public Address() {
}
public Address(String homeAddress, String schoolAddress) {
this.homeAddress = homeAddress;
this.schoolAddress = schoolAddress;
}
public String getHomeAddress() {
return homeAddress;
}
public void setHomeAddress(String homeAddress) {
this.homeAddress = homeAddress;
}
public String getSchoolAddress() {
return schoolAddress;
}
public void setSchoolAddress(String schoolAddress) {
this.schoolAddress = schoolAddress;
}
}
PersonMapper.java
import java.util.List;
import org.lanqiao.entity.Person;
//操作mybatis的接口
public interface PersonMapper {
/* 1、方法名和mapper.xml文件中标签的id相同
* 2、方法的输入参数和mapper.xml文件中标签的parameterType一致
* 3、方法的返回值和mapper.xml文件中标签的resultType一致
* 4、接口里的方法默认public abstract,所以可以省略
*/
Person queryPersonByIdWithConverter(int id);
List<Person> queryPersonByNameOrAge(Person person);
List<Person> queryPersonByAddress(Person person);
List<Person> queryAllPerson();
List<Person> queryAllPersonOrderByColumn(String column);
void addPersonWithConverter(Person person);
void deletePersonById(int id);
void updatePersonById(Person person);
}
//测试类
import java.io.IOException;
import java.io.Reader;
import java.util.List;
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.lanqiao.entity.Address;
import org.lanqiao.entity.Person;
import org.lanqiao.mapper.PersonMapper;
public class TestMybatis {
//使用转换器
public static void queryPersonByIdWithConverter(int id) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
Person person=personMapper.queryPersonByIdWithConverter(id);
System.out.println(person);
session.close();
}
//parameterType为对象类型
public static void queryPersonByNameOrAge(String name,int age) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
Person person=new Person();
//模糊查询
person.setName("%"+name+"%");
person.setAge(age);
List<Person> persons=personMapper.queryPersonByNameOrAge(person);
System.out.println(persons);
session.close();
}
//parameterType为嵌套对象类型
public static void queryPersonByAddress(String homeAddress,String schoolAddress) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
Person person=new Person();
Address address=new Address(homeAddress,schoolAddress);
person.setAddress(address);
List<Person> persons=personMapper.queryPersonByAddress(person);
System.out.println(persons);
session.close();
}
public static void queryAllPerson() throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
List<Person> persons=personMapper.queryAllPerson();
System.out.println(persons);
session.close();
}
public static void queryAllPersonOrderByColumn(String column) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
List<Person> persons=personMapper.queryAllPersonOrderByColumn(column);
System.out.println(persons);
session.close();
}
public static void addPersonWithConverter(Person person) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
personMapper.addPersonWithConverter(person);
//提交事务,不然数据进不去数据库中
session.commit();
session.close();
}
public static void deletePersonById(int id) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
personMapper.deletePersonById(id);
//提交事务,不然数据进不去数据库中
session.commit();
session.close();
}
public static void updatePersonById(Person person) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
personMapper.updatePersonById(person);
//提交事务,不然数据进不去数据库中
session.commit();
session.close();
}
public static void main(String[] args) throws IOException {
queryAllPersonOrderByColumn("name");
queryPersonByNameOrAge("czw",11);
queryPersonByAddress("ccc","xxxx");
}
}
4.类型为 HashMap
HashMap可以代替上面的方式
用map中key的值匹配占位符#{属性名},如果匹配成功就会用map中key对应的value替换占位符
//personMapper.xml中
<select id="queryPersonByNameOrAgeWithHashMap" parameterType="HashMap" resultType="Person">
select * from Person where name like #{name} or age=#{age}
</select>
//测试类中
public static void queryPersonByNameOrAgeWithHashMap(String name,int age) throws IOException {
//加载Mybatis配置文件(为了访问数据库)
Reader reader = Resources.getResourceAsReader("config.xml");
//可以在build中修改config.xml中developments的默认值
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
//session - connection
SqlSession session = sessionFactory.openSession();
PersonMapper personMapper=session.getMapper(PersonMapper.class);
//接口中的方法--》SQL语句
Map<String,Object> map = new HashMap<>();
map.put("name",name);
map.put("age",age);
List<Person> persons=personMapper.queryPersonByNameOrAgeWithHashMap(map);
System.out.println(persons);
session.close();
}