MyBatis基本配置和简单的增删改查
1.MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架。
2.MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。
3.MyBatis 可以对配置和原生Map使用简单的 XML 或注解,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
4.切入正题,这里不对配置中的标签做详细解析,有一部分在代码中有说明。后续会补充个标签的具体意义,这里只说简单的用法。下面插入代码片段:
a.配置MyBatis的xml 文件,命名为(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>
<!-- 配置别名 如果配置了别名 在sql映射文件中就不用写对应的包名了~ 就可以直接对应的别名即可 -->
<typeAliases>
<typeAlias alias="user" type="com.test.bean.UserInfoBean"/>
</typeAliases>
<!-- 配置数据源信息 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED PooledDataSourceFactory 使用连接池的数据源 -->
<!-- UNPOOLED UnpooledDataSourceFactory 不使用连接池的数据源 -->
<!-- JNDI JndiDataSourceFactory 使用JNDI实现的数据源 -->
<!-- type=”POOLED” MyBatis会创建PooledDataSource实例 type=”UNPOOLED” MyBatis会创建UnpooledDataSource实例type=”JNDI” MyBatis会从JNDI服务上查找DataSource实例 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/spring_test?useUnicode=true&characterEncoding=UTF-8"/>
<!-- 配置userName -->
<property name="username" value="root"></property>
<!-- 配置密码 -->
<property name="password" value="root"></property>
</dataSource>
</environment>
</environments>
<!-- 配置数据库厂商标识 可省略 -->
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
</databaseIdProvider>
<!-- 配置sql映射 -->
<mappers>
<mapper resource="UserInfoMapping.xml"/>
<mapper resource="ScoreMapping.xml"/>
<!-- 如果用注解的方式需要配置上calss。因为mybatis底层是通过动态代理的方式来实现的 -->
<mapper class="com.test.service.interfaces.ScoreServiceInterface"/>
</mappers>
</configuration>
b.创建UserInfoBean
package com.test.bean;
public class UserInfoBean {
private int id;
private String account;
private String name;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
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;
}
}
c.创建ScoreBean
package com.test.bean;
public class ScoreBean {
private int id;
private double score;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
}
package com.test.service.interfaces;
import com.test.bean.UserInfoBean;
public interface UserInfoServiceInterface {
void queryUserInfo(UserInfoBean userInfoBean);
void addUserInfo(UserInfoBean userInfoBean);
void updateUserInfo(UserInfoBean userInfoBean);
void deleteUserInfo(UserInfoBean userInfoBean);
int getCountAll();
}
package com.test.service.interfaces;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.test.bean.ScoreBean;
public interface ScoreServiceInterface {
@Select(value="select count(*) from score")
int getCountAll();
@Select(value="select * from score where id=#{id}")
ScoreBean getScoreById(ScoreBean scoreBean);
@Insert(value="insert into score (score)values(#{score})")
void insertScore(ScoreBean scoreBean);
@Delete(value="delete from score where id =#{id}")
void deleteScore(ScoreBean scoreBean);
@Update(value="update score set score =#{score} where id=#{id}")
void updateScore(ScoreBean scoreBean);
}
package com.test.service.imp;
import org.apache.ibatis.session.SqlSession;
import com.test.bean.UserInfoBean;
import com.test.service.interfaces.UserInfoServiceInterface;
import com.test.util.SqlSessionFactoryUtil;
public class UserInfoServiceImp implements UserInfoServiceInterface {
/* (non-Javadoc)
* @see com.test.service.imp.UserInfoServiceinterface#queryUserInfo(com.test.bean.UserInfoBean)
*/
@Override
public void queryUserInfo(UserInfoBean userInfoBean){
SqlSession session =SqlSessionFactoryUtil.getOpenSession();
try {
// 通过对象的方式查询一个对象
UserInfoBean user = session.selectOne("getUserInfoByUser", userInfoBean);
System.out.println("通过对象的方式账号:"+user.getAccount());
//通过sql 动态语句的方式查询
UserInfoBean user1 = session.selectOne("getUserInfoDynamic", userInfoBean);
System.out.println("通过动态语句和对象的方式查询,账号:"+user1.getAccount());
} catch (Exception e) {
e.printStackTrace();
}finally{
session.close();
}
}
/* (non-Javadoc)
* @see com.test.service.imp.UserInfoServiceinterface#addUserInfo(com.test.bean.UserInfoBean)
*/
@Override
public void addUserInfo(UserInfoBean userInfoBean){
// 构建sqlSession工厂
SqlSession session =SqlSessionFactoryUtil.getOpenSession();
try {
session.insert("addUserInfo", userInfoBean);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
session.close();
}
}
/* (non-Javadoc)
* @see com.test.service.imp.UserInfoServiceinterface#updateUserInfo(com.test.bean.UserInfoBean)
*/
@Override
public void updateUserInfo(UserInfoBean userInfoBean){
SqlSession session =SqlSessionFactoryUtil.getOpenSession();
try {
session.update("updateUserInfo", userInfoBean);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
session.close();
}
}
/* (non-Javadoc)
* @see com.test.service.imp.UserInfoServiceinterface#deleteUserInfo(com.test.bean.UserInfoBean)
*/
@Override
public void deleteUserInfo(UserInfoBean userInfoBean){
SqlSession session =SqlSessionFactoryUtil.getOpenSession();
try {
session.delete("deleteUserInfo", userInfoBean);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
session.close();
}
}
/* (non-Javadoc)
* @see com.test.service.imp.UserInfoServiceInterface#getCountAll()
*/
@Override
public int getCountAll() {
// 通过对象的方式查询一个对象
SqlSession session = SqlSessionFactoryUtil.getOpenSession();
int reslut = 0;
try {
reslut=session.selectOne("getCount");
System.out.println("UserInfo表数量"+reslut);
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
return reslut;
}
}
g.创建SqlSessionFactoryUtil。用来获取sessionFactory
package com.test.util;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.SQL;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SqlSessionFactoryUtil {
private static SqlSessionFactory sessionFactory =null;
/**
* 类似与线程锁,做同步用的
* 因为大量创建SqlSessionFactory是非常浪费的
* 我们只需要取Session即可
*/
private static final Class CLASS_LOCK =SqlSessionFactoryUtil.class;
/**
*空参数构造方法
*/
private SqlSessionFactoryUtil() {};
/**
*初始化
*/
public static SqlSessionFactory initSqlSessionFactory() {
// mybatis的配置文件
String resource = "mybatis-config.xml";
Reader re = null; //字符流
try {
re = Resources.getResourceAsReader(resource);
} catch (IOException e) {
e.printStackTrace();
}
// 同步
synchronized (CLASS_LOCK) {
if(null==sessionFactory){
sessionFactory = new SqlSessionFactoryBuilder().build(re);
}
}
return sessionFactory;
}
/**
*获取openSession
*/
public static SqlSession getOpenSession() {
if(null==sessionFactory){
// 初始化sessionFactory
initSqlSessionFactory();
}
System.out.println("数据库类型:"+sessionFactory.getConfiguration().getDatabaseId());
return sessionFactory.openSession();
}
}
h.创建ScoreMapping.xml 如果是用注解的方式 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">
<!-- 配置score的映射 如果是用注解的方式 这里就可以不用写sql语句了! -->
<mapper namespace="com.test.service.imp.ScoreBean">
</mapper>
i.创建UserInfoMapping.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.test.service.imp.UserInfoServiceImp">
<!-- 通过id查询 resultType="user" 因为之前已经命名过别名了 -->
<select id="getUserInfoById" parameterType="int" resultType="user">
select * from userInfo where id =#{id}
</select>
<!-- 通过对象的方式查询 -->
<select id="getUserInfoByUser" parameterType="user" resultType="user">
select * from userInfo where id = #{id} and name=#{name}
</select>
<!-- 查询所有的数量 -->
<select id="getCount" resultType="int">
select count(*) from userInfo
</select>
<!-- 通过对象和动态语句的方式 -->
<select id="getUserInfoDynamic" parameterType="user" resultType="user">
select * from userInfo
<where>
<if test="id!=null and id!=''" >
and id=#{id}
</if>
<if test="name!=null">
and name=#{name}
</if>
<if test="account!=null">
and account=#{account}
</if>
</where>
</select>
<!-- update 通过动态语句的方式 返回的值为更新的数量-->
<update id="updateUserInfo" parameterType="user">
update userinfo set
<if test="name!=null">
name=#{name}
</if>
<if test="account!=null">
account=#{account}
</if>
where id=#{id}
</update>
<!-- 通过对象的方式删除 -->
<delete id="deleteUserInfo" parameterType="user">
delete from userinfo
<where>
<if test="id!=null and id!=''">
id=#{id}
</if>
<if test="account!=null and account!=''">
account=#{account}
</if>
</where>
</delete>
<!-- 通过对象的方式添加数据 -->
<insert id="addUserInfo" parameterType="user">
insert into userinfo
(account, name, password)
values
(#{account}, #{name}, #{password})
</insert>
</mapper>
j.创建对应的测试类即可
j1.创建ScoreTest
package com.test.junit;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.test.bean.ScoreBean;
import com.test.service.interfaces.ScoreServiceInterface;
import com.test.util.SqlSessionFactoryUtil;
public class ScoreTest {
@Test
public void test() {
SqlSession sqlSession = SqlSessionFactoryUtil.getOpenSession();
try {
ScoreServiceInterface service = sqlSession.getMapper(ScoreServiceInterface.class);
System.out.println("通过接口@select注解的方式查询数量:" + service.getCountAll());
ScoreBean bean = new ScoreBean();
// 通过对象的方式查询
bean.setId(7);
ScoreBean scoreBean = service.getScoreById(bean);
System.out.println("通过接口@select注解的方式查询一个对象"+scoreBean.getScore());
// 插入数据
bean.setScore(212.0);
service.insertScore(bean);
//删除
bean.setId(3);
bean.setScore(664);
service.deleteScore(bean);
//更新数据
bean.setId(7);
bean.setScore(12.11);
service.updateScore(bean);
// 提交事务
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
sqlSession.close();
}
}
}
package com.test.junit;
import java.io.Reader;
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.Test;
import com.test.bean.UserInfoBean;
import com.test.service.imp.UserInfoServiceImp;
import com.test.service.interfaces.UserInfoServiceInterface;
public class UserInfoTest {
@Test
public void test() {
// 通过id的方式查询
String resource = "mybatis-config.xml";
SqlSession session = null;
try {
Reader re = Resources.getResourceAsReader(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(re);
session = sqlSessionFactory.openSession();
//selectOne 只返回一条数据
UserInfoBean user = session.selectOne("getUserInfoById", 10);
System.out.println(user.getName());
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
UserInfoServiceInterface service = new UserInfoServiceImp();
//通过对象的方式查询
UserInfoBean userInfoBean =new UserInfoBean();
userInfoBean.setId(10);
userInfoBean.setName("测试3");
service.queryUserInfo(userInfoBean);
//通过对象的方式进行更新数据
userInfoBean.setId(10);
userInfoBean.setName("测试3");
service.updateUserInfo(userInfoBean);
//通过对象的 方式进行删除数据
// service.deleteUserInfo(userInfoBean);
//通过对象的方式插入数据
userInfoBean.setAccount("1231234");
userInfoBean.setPassword("1231234");
service.addUserInfo(userInfoBean);
// 查询行数
service.getCountAll();
}
}