MyBatis基础入门《十七》动态SQL
描述：
>> 完成多条件查询等逻辑实现
>> 用于实现动态SQL的元素主要有：
> if
> trim
> where
> set
> choose( when , otherwise )
> foreach
动态SQL为Mybatis重要部分，项目也重新新建了一个：mybatis-dynamic-sql
项目结构：
TblClient.java
package com.charles.entity; import java.io.Serializable;
import java.util.Date; public class TblClient implements Serializable { private static final long serialVersionUID = -5993993584624176849L; private Integer cid;
private String cname;
private String caddress;
private Date cbirthday; public TblClient() { } public Integer getCid() {
return cid;
} public void setCid(Integer cid) {
this.cid = cid;
} public String getCname() {
return cname;
} public void setCname(String cname) {
this.cname = cname;
} public String getCaddress() {
return caddress;
} public void setCaddress(String caddress) {
this.caddress = caddress;
} public Date getCbirthday() {
return cbirthday;
} public void setCbirthday(Date cbirthday) {
this.cbirthday = cbirthday;
}
}
ClientMapper.java
package com.charles.mapper;
import java.util.List;
import com.charles.entity.TblClient;
public interface ClientMapper {
/***
* 注意这个名字，必须要和ClientMapper.xml文件中的select标签id属性值一样。
* @return List<TblClient> 集合
*/
public List<TblClient> getClientAll();
}
ClientMapper.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="com.charles.mapper.ClientMapper"> <resultMap type="com.charles.entity.TblClient" id="tblClientID">
<id property="cid" column="id" />
<result property="cname" column="client_name"/>
<result property="caddress" column="client_address"/>
<result property="cbirthday" column="client_birthday"/>
</resultMap> <select id="getClientAll" resultMap="tblClientID">
SELECT
id ,
client_name ,
client_address ,
client_birthday
FROM tbl_client
</select> </mapper>
MyBatisUtil.java
package com.charles.util; import java.io.IOException;
import java.io.InputStream; import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisUtil { private static SqlSessionFactory factory = null; /** 在静态代码块下，factory只会被创建一次 **/
static {
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis/mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
} // 获取factory
public static SqlSessionFactory getSessionFactory() { return factory;
} /**
* 获取SQLSession方法
*
* @return SQLSession
**/
public static SqlSession getSqlSession() { // 开启事物
return factory.openSession(false);
} /**
* 关闭SQLSession方法
*
* @param SQLSession对象
*/
public static void closeSqlSession(SqlSession sqlSession) { if (sqlSession != null) {
sqlSession.close();
}
}
}
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标签就必须放在properties标签的后面。
--> <!-- 引入database.properties文件 -->
<properties resource="properties/database.properties"></properties> <!-- 配置mybatis的log实现log4j -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings> <!-- 配置别名 -->
<typeAliases>
<typeAlias type="com.charles.entity.TblClient" alias="baitang" />
</typeAliases> <!-- 配置Mybatis的环境 -->
<environments default="development">
<environment id="development">
<!-- 配置事物管理 -->
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments> <!-- 将Mapper文件加入到mybatis的配置文件中 -->
<mappers>
<mapper resource="com/charles/mapper/ClientMapper.xml" />
</mappers> </configuration>
database.properties
jdbc.driver=com.mysql.jdbc.Driver
#jdbc.url=jdbc:mysql://IP地址:3306/test
jdbc.url=jdbc:mysql://IP地址:3306/test
jdbc.username=charles
jdbc.password=charles
lo4j.properties
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.charles=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
JunitSelect.java
package com.charles.junit; import java.util.List; import org.apache.ibatis.session.SqlSession;
import org.junit.Test; import com.charles.entity.TblClient;
import com.charles.mapper.ClientMapper;
import com.charles.util.MyBatisUtil; public class JunitSelect { @Test
public void selectif() { /** 1. 获取SQLSession **/
SqlSession session = MyBatisUtil.getSqlSession(); /** 2. 调度方法,从数据库中获取数据 **/
List<TblClient> list = session.getMapper(ClientMapper.class).getClientAll(); /** 3. 关闭SQLSession **/
MyBatisUtil.closeSqlSession(session); for (TblClient client : list) {
System.out.println(client.getCid() + "\t" + client.getCname() + "\t" + client.getCaddress() + "\t"
+ client.getCbirthday());
}
}
}
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.charles.mybatis</groupId>
<artifactId>mybatis-dynamic-sql</artifactId>
<version>0.0.1-SNAPSHOT</version> <dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.29</version>
</dependency>
</dependencies> </project>
运行测试代码：JunitSelect.java 的测试结果：
如有问题，欢迎纠正！！！
如有转载，请标明源处：https://www.cnblogs.com/Charles-Yuan/p/9902779.html