Mybatis
需要mybatis-3.jar
1、配置xxx.properties文件,配置数据源,可以配置多个数据源信息
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
username=root
password=root
driver1=com.mysql.jdbc.Driver
url1=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8
username1=root
password1=root
2、配置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>
<!-- 配置properties文件的位置 -->
<properties resource="database.properties"></properties>
<!-- 取别名, 每一个在包 entity 中的 Java Bean,在没有注解的情况下,会使用 Bean 的首字母小写的非限定类名来作为它的别名。 比如 entity.Author 的别名为author;若有注解,则别名为其注解值,@Alias("author")-->
<typeAliases>
<!-- <typeAlias type="entity.User" alias="user"/> -->
<package name="entity"/>
</typeAliases>
<!-- 配置连接信息 -->
<environments default="mybatis">
<environment id="mybatis">
<transactionManager type="JDBC"></transactionManager>
<!-- <dataSource type="POOLED"> -->
<!-- <property name="driver" value="com.mysql.jdbc.Driver"/> -->
<!-- <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/> -->
<!-- <property name="username" value="root"/> -->
<!-- <property name="password" value="123"/> -->
<!-- </dataSource> -->
<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="mapper/UserMapper.xml"/>
</mappers>
</configuration>
3、编写工具类,获取session
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtil {
private static SqlSessionFactory factory;
static{
try {
//build方法有多个重载方法,此处使用mybatis-config.xml文件中的默认数据源,build(InputStream inputStream, String environment) 可以获取指定的数据源
factory = new SqlSessionFactoryBuilder().build(MyBatisUtil
.class
.getClassLoader()
.getResourceAsStream("mybatis-config.xml"));
} catch (Exception e) {
e.printStackTrace();
//静态代码块中出现异常抛出
throw new ExceptionInInitializerError("mybatis初始化异常");
}
}
public static SqlSession getSession(){
SqlSession session = null;
try {
//默认autocommit(false);openSession(true)开启自动提交
session = factory.openSession();
} catch (Exception e) {
e.printStackTrace();
}
return session;
}
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
public class MybatisUtil {
private static final String CONFIGURATION_PATH = "mybatis.xml";
private static final Map<DataSourceEnvironment, SqlSessionFactory> dcit =
new HashMap<DataSourceEnvironment, SqlSessionFactory>();
public static SqlSessionFactory getSqlSessionFactory(DataSourceEnvironment environment ){
SqlSessionFactory sqlSessionFactory = dcit.get(environment);
if(sqlSessionFactory != null){
return sqlSessionFactory;
}
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(CONFIGURATION_PATH);
// inputStream = MybatisUtil
// .class
// .getClassLoader()
// .getResourceAsStream(CONFIGURATION_PATH);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, environment.name());
dcit.put(environment, sqlSessionFactory);
return sqlSessionFactory;
} catch (IOException e) {
e.printStackTrace();
} finally{
if(inputStream != null){
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
public static SqlSession getSession(DataSourceEnvironment environment){
SqlSession session = null;
try {
//默认autocommit(false);openSession(true)开启自动提交
session = getSqlSessionFactory(environment).openSession();
} catch (Exception e) {
e.printStackTrace();
}
return session;
}
public static void closeSession(SqlSession session) {
if (session != null) {
session.close();
}
}
public static Object getObject(Class cla,DataSourceEnvironment environment){
SqlSession session = MybatisUtil.getSession(environment);
Object obj = session.getMapper(cla);
return obj;
}
}
4、编写mapper.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="dao.ManagerDao">
<sql id="subscriptionColumn">
s.id "s.id",
s.mid "s.mid",
s.no "s.no",
s.linkman "s.linkman",
s.email "s.email",
s.phone "s.phone",
s.status "s.status",
s.cretime "s.cretime",
s.remark "s.remark",
m.username "m.username"
</sql>
<resultMap type="entity.Subscription" id="subscriptionMap">
<id property="id" column="s.id" />
<result property="no" column="s.no"/>
<result property="linkman" column="s.linkman"/>
<result property="email" column="s.email"/>
<result property="phone" column="s.phone"/>
<result property="status" column="s.status"/>
<result property="cretime" column="s.cretime"/>
<result property="remark" column="s.remark"/>
<association property="member" column="mid" javaType="entity.Member">
<id property="id" column="s.mid"/>
<result property="username" column="m.username"/>
</association>
</resultMap>
<select id="selectManagerByUsernameAndPwd" resultType="entity.Manager">
select id,username,pwd
from manager where username=#{0} and pwd=#{1}
</select>
<select id="selectByNo" parameterType="string" resultMap="subscriptionMap" >
select <include refid="subscriptionColumn"></include>
from subscription s left join member m on s.mid=m.id
where s.no=#{no}
</select>
<select id="selectBySid" parameterType="int" resultType="vo.SubscriptionDtl">
select dtl.id id, c.name categoryName ,r.no roomNo ,dtl.residetype resideType, dtl.price price, dtl.sdate sdate, dtl.edate edate
from subscription_dtl dtl ,room r,category c
where dtl.rid=r.id and r.cid=c.id
and dtl.sid=#{sid}
</select>
</mapper>
Insert 返回主键属性设置:useGeneratedKeys="true" keyProperty="roleId" keyColumn="role_id"
5、获取实例方法
SqlSession session = MyBatisUtil.getSession();
IUserDao userDao = session.getMapper(IUserDao.class);
注意:
MyBatis中常用的类就要数SqlSessionFactoryBuilder、SqlSessionFactory、SqlSession、SqlMapper了。那么下面对他们的应用范围和生命周期进行一下说明:
1、SqlSessionFactoryBuilder:在应用中该类实例的主要作用是创建SqlSessionFactory实例,故任务完成后就可以消失了。因此该类实例的最佳应用范围和生命周期应为“方法范围”。
2、SqlSessionFactory:在应用的整个周期中会有众多地方需要利用其实例打开某个SqlSession,因此最佳范围是“应用生命周期范围”。故此,可以使用单例与工厂模式,在官方文档中最佳建议是IoC容器,如Spring来生成该实例。
3、SqlSession:该类是非线程安全的,其实例是不能共享的,所以应该每个线程具有自己的SqlSession实例。因此最佳建议是“请求或方法范围”。例如:收到一个Http请求后,创建一个实例,进行某些操作,之后关闭。确保将关闭放于finally中。
4、SqlMapper:是创建绑定映射语句的接口。其实例从SqlSession获得,所以其最宽生命周期与SqlSession相同,因此其实例的执行范围也是“方法范围”,而且其不需要明确的关闭。
Mapper.xml 中 #和$ 符号的区别:
1.#将传入的数据都当成一个字符串,会对自动传入的数据加一个双引号。如:order by #user_id#,如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id,则解析成的sql为order by "id".
2. $将传入的数据直接显示生成在sql中。如:order by $user_id$,如果传入的值是111,那么解析成sql时的值为order by user_id, 如果传入的值是id,则解析成的sql为order by id.
3. #方式能够很大程度防止sql注入。
4.$方式无法防止Sql注入。
5.$方式一般用于传入数据库对象,例如传入表名.
6.一般能用#的就别用$.
MyBatis排序时使用order by 动态参数时需要注意,用$而不是#
mybatis批量插入 2种方式:
1、mybatis BATCH模式插入
@Test
public void testInsertBatch2() throws Exception {
long start = System.currentTimeMillis();
User user;
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
UserDao mapper = sqlSession.getMapper(UserDao.class);
for (int i = 0; i < 500; i++) {
user = new User();
user.setId("test" + i);
user.setName("name" + i);
user.setDelFlag("0");
mapper.insert(user);
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
<insert id="insert">
INSERT INTO t_user (id, name, del_flag)
VALUES(#{id}, #{name}, #{delFlag})
</insert>
2、foreach方式插入
@Test
public void testInsertBatch() throws Exception {
long start = System.currentTimeMillis();
List<User> list = new ArrayList<>();
User user;
for (int i = 0; i < 10000; i++) {
user = new User();
user.setId("test" + i);
user.setName("name" + i);
user.setDelFlag("0");
list.add(user);
}
userService.insertBatch(list);
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
<insert id="insertBatch">
INSERT INTO t_user
(id, name, del_flag)
VALUES
<foreach collection ="list" item="user" separator =",">
(#{user.id}, #{user.name}, #{user.delFlag})
</foreach >
</insert>
特别注意:mysql默认接受sql的大小是1048576(1M),即第三种方式若数据量超过1M会报如下异常:(可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的"max_allowed_packet = 1M")
nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable. |
第二种方式执行效率高一点。