创建数据库mybatis
在数据库中创建user表
CREATE TABLE `user` (
`id` int(20) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`pwd` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `user`(`id`,`name`,`pwd`)
values (1,'小明','123456'),(2,'小红','111111'),(3,'小黄','222222');
新建一个maven项目
在pom.xml中添加:
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
在resoerces目录下新建配置文件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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
</configuration>
创建实体类:
package it.ytu.pojo;
public class User {
private int id;
private String name;
private String pwd;
public User() {
super();
}
public User(int id, String name, String pwd) {
this.id = id;
this.name = name;
this.pwd = pwd;
}
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 String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]";
}
}
创建UserDao.java:
package it.ytu.dao;
import java.util.List;
import it.ytu.pojo.User;
public interface UserDao {
List<User> getUserList();
}
在UserDao.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">
<!-- namespace绑定一个指定的Dao/Mapper接口 -->
<mapper namespace="it.ytu.dao.UserDao">
<select id="getUserList" resultType="it.ytu.pojo.User">
select * from mybatis.user
</select>
</mapper>
在mybatis-config.xml中注册UserMapper.xml:
<mappers>
<mapper resource="it/ytu/dao/UserMapper.xml"/>
</mappers>
此时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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="it/ytu/dao/UserMapper.xml"/>
</mappers>
</configuration>
在src/test/java目录下编写测试类
UserDaoTest.java:
package it.ytu.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import it.ytu.pojo.User;
import it.ytu.utils.MybatisUtils;
public class UserDaoTest {
@Test
public void test() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
List<User> userList=userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
} finally {
//关闭SqlSession
sqlSession.close();
}
}
}
运行结果如下:
增加增删改查功能:
UserDao.java:
package it.ytu.dao;
import java.util.List;
import it.ytu.pojo.User;
public interface UserDao {
//查询全部用户
List<User> getUserList();
//根据id查询用户
User getUserById(int id);
//增加用户
int addUser(User user);
//修改用户
int updateUser(User user);
//删除用户
int deleteUser(int id);
}
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">
<!-- namespace绑定一个指定的Dao/Mapper接口 -->
<mapper namespace="it.ytu.dao.UserDao">
<select id="getUserList" resultType="it.ytu.pojo.User">
select * from mybatis.user
</select>
<select id="getUserById" resultType="it.ytu.pojo.User" parameterType="int">
select * from mybatis.user where id=#{id}
</select>
<insert id="addUser" parameterType="it.ytu.pojo.User">
insert into mybatis.user (id,name,pwd)
values(#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="it.ytu.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd}
where id=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id=#{id}
</delete>
</mapper>
UserDaoTest.java:
package it.ytu.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import it.ytu.pojo.User;
import it.ytu.utils.MybatisUtils;
public class UserDaoTest {
@Test
public void test() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
List<User> userList=userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
} finally {
//关闭SqlSession
sqlSession.close();
}
}
@Test
public void test1() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
User user=userDao.getUserById(1);
System.out.println(user);
} finally {
//关闭SqlSession
sqlSession.close();
}
}
@Test
public void test2() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
int flag=userDao.addUser(new User(4,"小绿","333333"));
if(flag==1) {
System.out.println("插入成功");
}
} finally {
sqlSession.commit();
//关闭SqlSession
sqlSession.close();
}
}
@Test
public void test3() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
int flag=userDao.updateUser(new User(4,"小黄","333333"));
if(flag==1) {
System.out.println("修改成功");
}
} finally {
sqlSession.commit();
//关闭SqlSession
sqlSession.close();
}
}
@Test
public void test4() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
int flag=userDao.deleteUser(4);
if(flag==1) {
System.out.println("删除成功");
}
} finally {
sqlSession.commit();
//关闭SqlSession
sqlSession.close();
}
}
}
万能Map:
UserDao.java
User getUser(Map<String,Object> map);
UserMapper.xml
<select id="getUser" resultType="it.ytu.pojo.User" parameterType="map">
select * from mybatis.user where id=#{id} and name=#{name}
</select>
UserDaoTest.java
@Test
public void test5() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
Map<String, Object> map=new HashMap<String, Object>();
map.put("id", 2);
map.put("name", "小红");
User user=userDao.getUser(map);
System.out.println(user);
} finally {
//关闭SqlSession
sqlSession.close();
}
}
模糊查询(顺序同上)
//模糊查询
List<User> getUserLike(String value);
<select id="getUserLike" resultType="it.ytu.pojo.User" >
select * from mybatis.user where name like #{value}
</select>
@Test
public void test6() {
//获取sqlSession对象
SqlSession sqlSession=null;
try {
sqlSession=MybatisUtils.getSqlSession();
//getMapper
UserDao userDao=sqlSession.getMapper(UserDao.class);
List<User> userList=userDao.getUserLike("%小%");
for (User user : userList) {
System.out.println(user);
}
} finally {
//关闭SqlSession
sqlSession.close();
}
}
配置优化:
在resource目录下新建db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8
username=root
password=123456
此时的mubatis-config.xml可以进行修改:
<environments default="development">
<environment id="development">
<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>
同时mapper也有多种方法:
<mappers>
<mapper resource="it/ytu/dao/UserMapper.xml"/>
<!-- <mapper class="it.ytu.dao.UserMapper"/> -->
<!-- <package name="it.ytu.dao"/> -->
</mappers>
然后可以起别名:
<typeAliases>
<!-- typeAlias type="it.ytu.pojo.User" alias="user" -->
<package name="it.ytu.pojo"/>
</typeAliases>
此时的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>
<!-- typeAlias type="it.ytu.pojo.User" alias="user" -->
<package name="it.ytu.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<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="it/ytu/dao/UserMapper.xml"/>
<!-- <mapper class="it.ytu.dao.UserMapper"/> -->
<!-- <package name="it.ytu.dao"/> -->
</mappers>
</configuration>
如果数据库中的字段与实体类不一致可以采取映射:
UserMapper.xml示例
<!-- 结果集映射 -->
<resultMap type="user" id="UserMap">
<!-- column数据库中的字段 property实体类中的字段-->
<!-- <result column="id" property="id"/> -->
<result column="name" property="name"/>
<result column="pwd" property="pwd"/>
</resultMap>
<select id="getUserById" resultMap="UserMap" parameterType="int">
select * from mybatis.user where id=#{id}
</select>