创建数据库
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
创建Config文件夹，放置三个配置文件。
image.png
配置如下：
log4j.properties
# Global logging configuration
#在开发环境日志级别要设置为DEBUG、生产环境要设置为INFO或者ERROR
log4j.rootLogger=DEBUG, stdout
# 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
Mybatis默认使用log4j作为输出日志信息。
db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///scott?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=root
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>
<properties resource="db.properties"></properties>
<typeAliases>
<package name="com.company.bean"></package>
</typeAliases>
<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>
<mappers>
<package name="com.company.mapper"></package>
</mappers>
</configuration>
创建bean类
package com.company.bean;
/**
* Created by ttc on 2018/8/2.
*/
import java.util.Date;
/**
* Created by Administrator on 2017/10/21.
*/
public class User {
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
Mybatis 的 mapper 接口
开发规范：
1.在 mapper.xml 中 namespace 等于 mapper 接口地址（所在包名的全路径）
<mapper namespace="com.mapper.UserMapper"></mapper>
2.在 xxxmapper.java 接口中的方法名要与 xxxMapper.xml 中 statement 的 id 一致。
3.在 xxxmapper.java 接口中的输入参数类型要与 xxxMapper.xml 中 statement 的 parameterType 指定的参数类型一致。
4.在 xxxmapper.java 接口中的返回值类型要与 xxxMapper.xml 中 statement 的 resultType 指定的类型一致。
5.接口文件名要与xml映射文件名一致（UserMapper.java和UserMapper.xml）
根据用户 id（主键）查询用户信息
定义Mapper接口
package com.company.mapper;
import com.company.bean.PageInfo;
import com.company.bean.User;
import java.util.List;
import java.util.Map;
/**
* Created by ttc on 2018/8/2.
*/
public interface UserMapper {
User findUserByID(int id);
List<User> findAllUsers();
int delUserByID(int id);
int addUser(User user);
int updateUser(User user);
List<User> findUserList(User user);
int getCount();
List<User> getPagedUser(PageInfo pageInfo);
List<Map<String,Object>> findOrdersInfo();
}
定义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">
<mapper namespace="com.company.mapper.UserMapper">
<sql id="query_condition">
<where>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="username != null and username != ''">
and username like #{username}
</if>
</where>
</sql>
<select id="findUserByID" parameterType="int" resultType="User">
select * from user where id = #{value}
</select>
<select id="findAllUsers" resultType="User">
select * from user
</select>
<delete id="delUserByID" parameterType="int">
delete from user where id = #{value}
</delete>
<insert id="addUser" parameterType="User">
<selectKey keyProperty="id" order="AFTER" resultType="int">
select last_insert_id()
</selectKey>
insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
</insert>
<update id="updateUser" parameterType="User">
update user set username = #{username},birthday = #{birthday},sex = #{sex},address=#{address} where id = #{id}
</update>
<select id="findUserList" resultType="User" parameterType="User">
select * from user
<include refid="query_condition"/>
</select>
<select id="getCount" resultType="int">
select count(*) from user
</select>
<select id="getPagedUser" parameterType="PageInfo" resultType="User">
select * from user limit #{pagestart}, #{pagesize}
</select>
<select id="findOrdersInfo" resultType="map">
select orders.number,orders.createtime,user.username,user.address from orders
join user on orders.user_id = user.id
</select>
</mapper>
主方法测试
package com.company.test;
import com.company.bean.PageInfo;
import com.company.bean.User;
import com.company.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 org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* Created by ttc on 2018/8/2.
*/
public class TestCrud {
SqlSession sqlSession;
@Before
public void setup() throws IOException {
//创建sqlSessionFactory
//Mybatis 配置文件
String resource = "SqlMapConfig.xml";
//得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建会话工厂,传入Mybatis的配置文件信息
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
}
@Test
public void testFindUserByID(){
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserByID(16);
System.out.println(user.getUsername());
}
@Test
public void testFindAllUser() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> list = userMapper.findAllUsers();
for(User user : list)
{
System.out.println(user);
}
}
@Test
public void testDelUser() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.delUserByID(10);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testAddUser() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("lisi333");
user.setSex("1");
user.setAddress("dalian");
user.setBirthday(new Date());
int num = userMapper.addUser(user);
System.out.println(user.getId());
sqlSession.commit();
sqlSession.close();
}
@Test
public void testUpdateUser() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("李四");
user.setSex("2");
user.setAddress("fushun");
user.setBirthday(new Date());
user.setId(26);
userMapper.updateUser(user);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindUserList() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
String username = "%张%";
user.setUsername(username);//select * from user WHERE username like ?
// user.setSex("1");select * from user WHERE sex = ? and username like ?
List<User> userList = userMapper.findUserList(user);
for(User user1 : userList)
{
System.out.println(user1);
}
sqlSession.close();
}
@Test
public void testGetCount() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int count = userMapper.getCount();
System.out.println(count);
sqlSession.close();
}
@Test
public void testGetPagedUser() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
PageInfo pageInfo = new PageInfo();
pageInfo.setPageindex(2);
pageInfo.setPagesize(4);
List<User> userList = userMapper.getPagedUser(pageInfo);
for(User user1 : userList)
{
System.out.println(user1);
}
sqlSession.close();
}
@Test
public void testGetOrders() {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Map<String,Object>> ordersInfoList = userMapper.findOrdersInfo();
// request.setAttribute("orderinfo",ordersInfoList);
for(Map<String,Object> map : ordersInfoList)
{
for(String string : map.keySet())
{
System.out.print(map.get(string)+" ");
}
System.out.println();
}
}
}
image.png
网友评论