mybatis基础&动态SQL&log4j简单使用

 工程目录结构查看:

 执行流程:

 1.数据库文件mybatisdb.sql

避免报错1406 - Data too long for column

根据不同的客户端工具使用不同的字符集编码

(1)cmd执行(本身GBK):

mysql -uroot -prootswliu

SET NAMES GBK;

(2)mysql客户端工具Navicat等(本身UTF8):

SET NAMES UTF8;-- 可以省略

CREATE DATABASE IF NOT EXISTS `mybatisdb` DEFAULT CHARACTER SET utf8;

USE `mybatisdb`;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) DEFAULT NULL,
  `BIRTHDAY` datetime DEFAULT NULL,
  `ADDRESS` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

insert  into `user`(`ID`,`NAME`,`BIRTHDAY`,`ADDRESS`) values (1,'夏言','1573-01-01 00:00:00','桂州村'),(2,'严嵩','1587-01-01 00:00:00','分宜县城介桥村');

select * from `user`;

2. 测试类TestMybatis.java

package mybatis;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.Before;
import org.junit.Test;

import com.swliu.mybatis.pojo.User;

public class TestMybatis {
	private SqlSessionFactory factory;//SqlSessionFactory线程安全类,可以作为成员变量(高并发场景)
	
	@Before
	public void init() throws IOException{
		//1.创建SqlSessionFactory,加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream is = Resources.getResourceAsStream(resource);
		factory = new SqlSessionFactoryBuilder().build(is);
	}
	
	@Test
	public void findAll() throws IOException{
		//2.利用工厂创建SqlSession对象,读取某个映射文件来执行
		SqlSession session = factory.openSession();SqlSession非线程安全类,不能作为成员变量(高并发场景)
		//查询所有。namespace.id,桶namespace定位唯一映射。映射文件有多个访问,通过id找到唯一标签
		String statement = "com.swliu.mybatis.pojo.UserMapper.findAll";	//指向映射文件,映射文件中需要pojo
		//ORM 对象和数据库表映射工具,持久化工具Mybatis
		List<User> userList = session.selectList(statement);
		//打印数据
		for(User u: userList){
			System.out.println(u);
		}
	}
	
	@Test
	public void findOne() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.findOne";	
		User u = session.selectOne(statement,2 );
		System.out.println(u);
	}
	
	@Test
	public void findCount() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.findCount";	
		int count = session.selectOne(statement);
		System.out.println(count);
	}
	
	@Test
	public void insert() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.insert";	
		User u = new User();
		u.setName("刘德华");
		u.setBirthday(new Date());
		u.setAddress("中国香港");
		session.insert(statement,u);
		
		//如何证明mybatis默认事务是手动提交?
		//如果mybatis默认事务是自动提交,则下面i=1/0执行之前,就已经将数据插入到数据库;
		//如果数据库中没有该条数据提交的内容,则说明事务已经回滚,则证明mybatis为事务手动提交
		//int i=1/0;
		
		//mybatis默认事务手动提交
		session.commit();
		
	}
	
	@Test
	public void update() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.update";	
		String name="Jack";
		session.update(statement,name);
		
		//如何证明mybatis默认事务是手动提交?
		//如果mybatis默认事务是自动提交,则下面i=1/0执行之前,就已经将数据插入到数据库;
		//如果数据库中没有该条数据提交的内容,则说明事务已经回滚,则证明mybatis为事务手动提交
		//int i=1/0;
		
		//mybatis默认事务手动提交
		session.commit();
	}
	
	@Test
	public void delete() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.delete";	
		int pid=12;
		session.delete(statement,pid);
		
		//如何证明mybatis默认事务是手动提交?
		//如果mybatis默认事务是自动提交,则下面i=1/0执行之前,就已经将数据插入到数据库;
		//如果数据库中没有该条数据提交的内容,则说明事务已经回滚,则证明mybatis为事务手动提交
		//int i=1/0;
		
		//mybatis默认事务手动提交
		session.commit();
	}
	
	@Test
	public void findYear() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.findYear";	
		List<User> userList = session.selectList(statement);
		for(User u: userList){
			System.out.println(u);
		}
	}
	
	@Test
	public void order() throws IOException{
		SqlSession session = factory.openSession();
		String statement = "com.swliu.mybatis.pojo.UserMapper.order";	
		//不能使用string传值,不符合业务需要
		//List<User> userList = session.selectList(statement,"name");
		//可以使用对象传值的方式
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("findObj", "name");
		List<User> userList = session.selectList(statement,map);
		for(User u: userList){
			System.out.println(u);
		}
	}
	
}

 selectList返回的数据:可以是多条/一条/没有

 selectOne返回的数据:只能有一个值

SqlSessionFactory线程安全类,可以作为成员变量(高并发场景)

SqlSession非线程安全类,不能作为成员变量(高并发场景) 

mybatis默认事务开启,需要手动提交

begin;
start transaction;

commit;

 3.配置事务、数据源、映射文件、全局配置文件 sqlMapConfig.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>

	<!-- 简化类,起别名 -->
	<typeAliases>
		<typeAlias type="com.swliu.mybatis.pojo.User" alias="User"/>
	</typeAliases>

	<!-- 配置事务、数据源、映射文件、全局配置 -->
	<environments default="deploy">
		<environment id="deploy">
			<!-- 事务:类似java锁,防止并发;JDBC/MANAGE -->
			<transactionManager type="JDBC"/>
			
			<!-- 数据源:
			POOLED池化
			/UNPOOLED非池化
			/JNDI命名和目录的管理方式:类似于DNS域名解析,需要一个单独的服务器来解析
			 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/mybatisdb?charactorEncoding=utf8"/>
				<property name="username" value="root"/>
				<property name="password" value="rootswliu"/>
			</dataSource>
		</environment>
		<!-- <environment id="test"></environment> -->
	</environments>
	
	<!-- 映射 -->
	<mappers>
		<mapper resource="com/swliu/mybatis/pojo/UserMapper.xml"/>
	</mappers>
	
</configuration>   



 

4.配置映射文件的命名空间(java包): 包的路径.映射文件的名称 UserMapper.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">
	
<!-- 配置映射文件的命名空间(java包): 包的路径.映射文件的名称 -->
<mapper namespace="com.swliu.mybatis.pojo.UserMapper">

	<!-- 列出所有的字段,可以共享-->
	<sql id="cols">id,name,birthday,address</sql>

	<!-- 需求:查询user表的所有数据 -->
	<select id="findAll" resultType="User">
		select <include refid="cols"/> from user
	</select>
	
	<!-- 需求:获取某个用户信息 -->
	<select id="findOne" parameterType="int" resultType="User">
		select <include refid="cols"/> from user where id=#{pid}
	</select>
	
	<!-- 需求:查询记录总数 SQL优化:count(id)比count(*) 执行效率高 -->
	<select id="findCount" resultType="int">
		select count(id) from user
	</select>
	
	<!-- 需求:插入一条数据-->
	<!-- mybatis非常智能,直接可以获取对象中的参数-->
	<select id="insert" parameterType="User">
		insert into user(name,birthday,address) values(#{name},#{birthday},#{address})
	</select>
	
	<!-- 需求:修改一条数据-->
	<select id="update" parameterType="string">
		update user set name=#{name} where name='刘德华'
	</select>
	
	<!-- 需求:删除一条数据-->
	<select id="delete" parameterType="int">
		delete from user where id=#{pid}
	</select>
	
	<!-- 
	如果有特殊符号,在xml中不用去解析,可以用下面的方式括起来即可(CDATA规范:原样输出)
	select * from user where year(birthday) <![CDATA[>]]>1580 and year(birthday) <![CDATA[<]]>1590 
	-->
	<!-- 需求:按年龄段查询-->
	<select id="findYear" resultType="User">
		select <include refid="cols"/> from user where year(birthday) &#62;1580 and year(birthday) &#60;1590 
	</select>
	
	<!-- order测试:
	Mybatis不能直接支持特殊的string,需要使用对象get方法获取(解决方法:改成user对象或者map结构)
	#{}表示占位符形式,防止SQL注入;  
		select * from user order by 'name'; (不符合业务需要,'name'没有按占位符使用)
	${}表示直接字符串拼接,存在隐患,不能防止SQL注入,慎用.order by/group by
		select * from user order by name;  (业务需要,使用对象传值)
	-->
	<select id="order" parameterType="map" resultType="User">
		select <include refid="cols"/> from user order by ${findObj}
	</select>
	
</mapper>


 参数parameterType/parameterMap(后者废除,可以用parameterType="map"替代)

select * from user where year(birthday) >1580 and year(birthday) <1590 ;
select * from user where extract(year from birthday)>1580 and extract(year from birthday) <1590 ;
select * from user where birthday between '1580-1-1' and '1590-12-31'; 

如果有特殊符号>或者<:

方式一:在xml中不用去解析,可以用下面的方式括起来即可(CDATA规范:原样输出)
select <include refid="cols"/> from user where year(birthday) <![CDATA[>]]>1580 and year(birthday) <![CDATA[<]]>1590  

或:

select <include refid="cols"/> from user where 
<![CDATA[
year(birthday)>1580 and year(birthday)<1590 
]]>

方式二:

select <include refid="cols"/> from user where year(birthday) &gt;1580 and year(birthday) &lt;1590 

方式三:

select <include refid="cols"/> from user where year(birthday) &#62;1580 and year(birthday) &#60;1590 

Mybatis不能直接支持特殊的string,需要使用对象get方法获取

(如order by/group by某个字段场景)

(解决方法:改成user对象或者map结构)
    #{}表示占位符形式,防止SQL注入;  
        select * from user order by 'name'; (不符合业务需要,'name'没有按占位符使用)
    ${}表示直接字符串拼接,存在隐患,不能防止SQL注入,慎用.order by/group by
        select * from user order by name;  (业务需要,使用对象传值)

 5.javaBean类User.java

package com.swliu.mybatis.pojo;

import java.util.Date;

public class User {
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", birthday=" + birthday + ", address=" + address + "]";
	}
	private Integer id;
	private String name;
	private Date birthday;
	private String address;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
}

6.log4j.properties日志打印 

log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

7.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.swliu</groupId>
	<artifactId>mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>  
	    <argLine>-Dfile.encoding=UTF-8</argLine>  
	</properties>
	
	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.40</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.2.8</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
		</dependency>
	</dependencies>
	
	<!-- 
	<build>
	    <plugins>  
	        <plugin>  
	            <groupId>org.apache.maven.plugins</groupId>  
	            <artifactId>maven-surefire-plugin</artifactId>  
	            <version>2.16</version>  
	            <configuration>  
	                <forkMode>once</forkMode>  
	                <argLine>-Dfile.encoding=UTF-8</argLine>  
	            </configuration>  
	        </plugin>  
	    </plugins>
	</build>  
     -->
     
</project>


打印信息: 略

xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findAll] - ==>  Preparing: select id,name,birthday,address from user 
xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findAll] - ==> Parameters: 

xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findCount] - ==>  Preparing: select count(id) from user 
xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findCount] - ==> Parameters: 

xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findYear] - ==>  Preparing: select id,name,birthday,address from user where year(birthday) >1580 and year(birthday) <1590 
xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findYear] - ==> Parameters: 

xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findOne] - ==>  Preparing: select id,name,birthday,address from user where id=? 
xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.findOne] - ==> Parameters: 2(Integer)

xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.order] - ==>  Preparing: select id,name,birthday,address from user order by name  (name非占位符)
xxxxxx [main] DEBUG [com.swliu.mybatis.pojo.UserMapper.order] - ==> Parameters: 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值