搭建流程:
1:创建mybatis的配置文件(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/jdbc" />
<property name="username" value="root" />
<property name="password" value="1234" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="cn/sxt/entity/user-mapper.xml" />
</mappers>
</configuration>
- mapper标签中的resource是SQL映射文件的“包名+类名”
2:创建工具类(MybatisUtil.java)获得SqlSessionFactory与SqlSession类
package cn.sxt.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 {
public static SqlSessionFactory getSqlSessionFactory() throws IOException{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory;
}
public static SqlSession getSqlSession() throws IOException{
SqlSession sqlsession = getSqlSessionFactory().openSession();
return sqlsession;
}
}
- 创建SqlSessionFactory时,Resources.getResourceAsStream(resource)的参数为“包名+mybatis的配置文件的文件名”,此次我直接将mybatis-config.xml写在src文件下,所以省略了包名
3:创建实体类(User)用于接收数据
package cn.sxt.entity;
public class User {
private int id;
private String name;
private String password;
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
- 实体类的字段命名最好与数据库中的列名保持一致,否则会导致数据无法查询到。若两者命名不一致有两种解决方法:1.在SQL映射文件(User-mapper.xml)的SQL语句中使用别名,2.在SQL映射文件(User-mapper.xml)中的操作标签中使用resultMap属性
4:创建SQL映射文件(User-mapper.xml)用于写入SQL语句
<?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="cn.sxt.entity.user-mapper">
<select id="selectUser" resultType="cn.sxt.entity.User">
select * from user where id = #{id}
</select>
</mapper>
- 命名空间可自定义,通常使用“包名+文件名”,select的id自定义,resultType为“包名+类名”
- mybatis中的#{id}是占位符的意思,其效果等同于JDBC中的“?”,当标签没有使用parameterType属性时,花括号中的命名不作要求
最后,书写测试类,获得数据库数据
package cn.sxt.test;
import java.io.IOException;
import org.apache.ibatis.session.SqlSession;
import cn.sxt.entity.User;
import cn.sxt.util.MybatisUtil;
public class Test {
public static void main(String[] args) throws IOException {
SqlSession sqlsession = MybatisUtil.getSqlSession();
User user = (User) sqlsession.selectOne("cn.sxt.entity.user-mapper.selectUser", "2");
System.out.println(user.getId()+" "+user.getName());
}
}
- 测试类中只需获得SqlSession对象,通过SqlSession对象的相关方法便可以得到对数据库的操作结果
- sqlsession.selectOne()的第一个参数应为“SQL映射文件的命名空间+相关SQL语句的id”
- PS:建议将对数据库的操作封装到dao类中,在使用时,在测试类中调用dao对象
查询结果
2 李四
用mybatis完成增、删、改、查
在上面的基础上创建dao包,并创建dao类,将具体对数据库的操作封装到dao中,测试类用于获取dao对象
增
1:在映射文件(User-mapper.xml)中写入相关的SQL语句
<insert id="addUser" parameterType="cn.sxt.entity.User">
insert into user(id,name,password) values(#{id},#{name},#{password})
</insert>
- mybatis中的“#{id}”等符号是占位符的意思,其效果等同于JDBC中的“?”,当标签使用parameterType属性时,花括号中的命名必须与参数中的字段名相同
2:在dao(UserDao.java)中获取SqlSession对象
public int insert(User user) throws IOException{
SqlSession sqlsession = MybatisUtil.getSqlSession();
int result = sqlsession.insert("cn.sxt.entity.user-mapper.addUser", user);
sqlsession.commit();
sqlsession.close();
return result;
}
- 在dao中执行insert操作时,必须对事物进行提交:sqlsession.commit();
在测试类中获取dao对象
UserDao userdao = new UserDao();
User user = new User();
user.setId(5);
user.setName("張三");
user.setPassword("1256");
int i = userdao.insert(user);
System.out.println(i);
- 若不想添加user的id,则需在数据库中设置id为自增
测试结果
1
改
1:在映射文件(User-mapper.xml)中写入相关的SQL语句
<update id="updateUser" parameterType="cn.sxt.entity.User">
update user set name=#{name},password=#{password} where id=#{id}
</update>
2:在dao(UserDao.java)中获取SqlSession对象
public int update(User user) throws IOException{
SqlSession sqlsession = MybatisUtil.getSqlSession();
int result = sqlsession.update("cn.sxt.entity.user-mapper.updateUser", user);
sqlsession.commit();
sqlsession.close();
return result;
}
在测试类中获取dao对象
UserDao userdao = new UserDao();
User user = userdao.getById(5);
user.setPassword("2222");
System.out.println(userdao.update(user));
测试结果
1
删
1:在映射文件(User-mapper.xml)中写入相关的SQL语句
<delete id="deleteUser">
delete from user where id=#{id}
</delete>
2:在dao(UserDao.java)中获取SqlSession对象
public int delete(int id) throws IOException{
SqlSession sqlsession = MybatisUtil.getSqlSession();
int result = sqlsession.delete("cn.sxt.entity.user-mapper.deleteUser", id);
sqlsession.commit();
sqlsession.close();
return result;
}
在测试类中获取dao对象
UserDao userdao = new UserDao();
System.out.println(userdao.delete(3));
测试结果
1
查询所有
1:在映射文件(User-mapper.xml)中写入相关的SQL语句
<select id="selectUserAll" resultType="cn.sxt.entity.User">
select * from user
</select>
2:在dao(UserDao.java)中获取SqlSession对象
public List<User> getUserAll() throws IOException{
SqlSession sqlsession = MybatisUtil.getSqlSession();
List<User> list = sqlsession.selectList("cn.sxt.entity.user-mapper.selectUserAll");
sqlsession.close();
return list;
}
- 查询所有时,dao中的SqlSession对象调用的是selectList()方法,返回list集合在
测试类中获取dao对象
UserDao userdao = new UserDao();
List<User> list = userdao.getUserAll();
for(User u:list){
System.out.println(u);
}
测试结果
User [id=1, name=张三, password=1234]
User [id=2, name=李四, password=1111]
User [id=4, name=張三, password=1256]
User [id=5, name=張三, password=2222]
User [id=6, name=張三, password=1256]
User [id=7, name=lishi, password=1256]
配置文件的优化
使用properties保存需要连接的数据库的信息
1:编写properties(db.properties)文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=1234
2:在mybatis的配置文件(mybatis-config.xml)中引入properties文件
<?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="cn/sxt/dao/db.properties"/>
<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="cn/sxt/entity/user-mapper.xml" />
</mappers>
</configuration>
- properties标签的resource属性为“包名+文件名”,若直接在src文件下可省略包名
解决实体字段名与数据库列名不一致的问题
1:在SQL语句中使用别名
select id,name,password paw from user where id = #{id}
2:在mapper映射文件(User-mapper.xml)中使用resultMap标签
<select id="selectUserAll" resultMap="selectUserAllMap">
select * from user
</select>
<resultMap type="cn.sxt.entity.User" id="selectUserAllMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="password" property="userPassword"/>
</resultMap>
- 操作标签的resultMap属性与resultMap标签的id属性命名相同
- resultMap标签中的id标签非必须,可全由result标签替代
- column属性为数据库中的列名,property属性为实体类字段名
分页查询
通过使用mapper映射文件中select标签的parameterType属性,利用Map类完成分页查询
1:mapper映射文件(User-mapper.xml)中的select标签使用属性parameterType="Map"
<select id="selectUserAllThroughPage" parameterType="Map" resultMap="selectUserAllMap">
select * from user limit #{startIndex},#{pageSize}
</select>
2:dao的查询方法中增添map对象
public List<User> getUserAllThroughPage(int currentPage, int pageSize) throws IOException{
SqlSession sqlsession = MybatisUtil.getSqlSession();
Map<String, Integer> map = new HashMap<String, Integer>();
map.put("startIndex", (currentPage-1)*pageSize);
map.put("pageSize", pageSize);
List<User> list = sqlsession.selectList("cn.sxt.entity.user-mapper.selectUserAllThroughPage",map);
sqlsession.close();
return list;
}
测试类中获取dao对象
UserDao userdao = new UserDao();
List<User> list = userdao.getUserAllThroughPage(1, 2);
for(User u:list){
System.out.println(u);
}
测试结果
User [id=1, name=张三, userPassword=1234]
User [id=2, name=李四, userPassword=1111]
使用RowBounds完成分页查询
1:映射文件无需改变
2:dao中的查询方法使用RowBounds类
public List<User> getUserAllThroughPage2(int currentPage, int pageSize) throws IOException{
SqlSession sqlsession = MybatisUtil.getSqlSession();
RowBounds rowbounds = new RowBounds((currentPage-1)*pageSize, pageSize);
List<User> list = sqlsession.selectList("cn.sxt.entity.user-mapper.selectUserAll",null,rowbounds);
sqlsession.close();
return list;
}
- RowBounds rowbounds =newRowBounds((currentPage-1)*pageSize, pageSize);
- List<User> list = sqlsession.selectList("cn.sxt.entity.user-mapper.selectUserAll",null,rowbounds);
测试类中获取dao对象
UserDao userdao = new UserDao();
List<User> list = userdao.getUserAllThroughPage2(1, 2);
for(User u:list){
System.out.println(u);
}
测试结果
User [id=1, name=张三, userPassword=1234]
User [id=2, name=李四, userPassword=1111]
使用注解进行开发
使用注解开发不需要配置mapper映射文件(User-mapper.xml)。SQL语句直接写在dao接口中,具体的dao操作在dao接口的实现类中。与不使用注解开发相比,使用注解开发少了一个mapper映射文件,多了一个dao接口。
1:创建mybatis的配置文件(mybatis-config.xml)用以连接数据库(可使用properties保存需要连接的数据库的信息)
2:创建工具类(MybatisUtil.java)获得SqlSessionFactory与SqlSession类
3:创建实体类(User)用于接收数据
4:创建dao接口(UserDao.java)用于书写SQL语句与抽象方法
public interface UserDao {
@Select("select id,name,password userPassword from user")
public List<User> getAllUser();
@Insert("insert into user(id,name,password) values(#{id},#{name},#{userPassword})")
public int insert(User user);
}
- 在dao接口中的抽象方法上使用注解
5:创建dao类实现dao接口(UserDaoImp.java)
public class UserDaoImp implements UserDao{
public List<User> getAllUser(){
SqlSession sqlsession;
try {
sqlsession = MybatisUtil.getSqlSession();
UserDao userdao = sqlsession.getMapper(UserDao.class);
List<User> list = userdao.getAllUser();
return list;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
public int insert(User user) {
SqlSession sqlsession;
try {
sqlsession = MybatisUtil.getSqlSession();
UserDao userdao = sqlsession.getMapper(UserDao.class);
userdao.insert(user);
sqlsession.commit();
sqlsession.close();
return 1;
} catch (IOException e) {
e.printStackTrace();
}
return 0;
}
}
- 通过sqlsession.getMapper(UserDao.class)获得一个实现类对象赋值给UserDao接口
- 在增、删、该是应注意需要进行事件的提交:sqlsession.commit();
测试类中获取dao对象
public class Test {
public static void main(String[] args) throws IOException {
/*UserDaoImp userdaoimp = new UserDaoImp();
List<User> list = userdaoimp.getAllUser();
for(User u:list){
System.out.println(u);
}*/
UserDaoImp userdaoimp = new UserDaoImp();
User user = new User();
user.setId(14);
user.setName("黄明");
user.setuserPassword("9999");
System.out.println(userdaoimp.insert(user));
}
}
测试结果
1