d. 本次使用的是 Oracle 11g 数据库,建表SQL 脚本:
-------Table : users-----------------------------------------------------------------------------------------------------------------------------------
drop table users;
create table users(
userId number(10) not null,
username varchar2(30) not null,
birthday date default null,
sex char(1) default 1 check (sex in (0, 1)),
address varchar2(200) not null,
detail varchar2(1000) default 'no detail',
score number(4,2) default null,
primary key (userId));
comment on column users.sex is '0: female 1: male';
/*
--添加注释
-- comment on table users is '用户表';
-- comment on column users.userId is '用户编号';
-- comment on column users.sex is '0: female 1: male';
--修改表中字段的默认值
alter table users modify (detail varchar2(1000) default ('no detail'));
alter table users modify (sex char(1) default 1 check (sex in (0, 1)));
--
*/
/*
--创建序列Sequence
create sequence seq_userId
minvalue 100100 --最小值
nomaxvalue --不设置最大值(由机器决定),或 根据表字段的值范围设置 maxvalue
start with 100100 --从1开始计数,数值可变
increment by 2 --每次加1,数值可变
nocycle --一直累加,不循环
nocache --不建缓冲区,如果建立cache那么系统将自动读取cache值 个seq,这样会加快运行速度;如果当机或oracle死了,那么下次读取的seq值将不连贯
*/
create sequence seq_userId minvalue 100100 maxvalue 9999999999 start with 100100 increment by 1;
--创建触发器
create or replace trigger tg_insertUser
before insert on users for each row when (new.userId is null)
begin
select seq_userId.Nextval into:new.userId from dual;
end;
--测试:
insert into users (username, birthday, sex, address, socre) values ('Oracle', to_date('1991-08-25 19:55:45', 'yyyy-mm-dd hh24:mi:ss'), 1, 'ShangHai', 99.50);
-------Table : orders-----------------------------------------------------------------------------------------------------------------------------------
drop table orders;
create table orders(
orderId number(11) not null,
user_id number(10) not null,
orderNumber varchar2(20) not null,
primary key (orderId),
constraint fk_orders_users foreign key (user_id) references users (userId) on delete cascade
);
create sequence seq_orderId minvalue 1000000 maxvalue 99999999999 start with 1000000 increment by 1;
create or replace trigger tg_insertOrders
before insert on orders for each row when (new.orderId is null)
begin
select seq_orderId.Nextval into:new.orderId from dual;
end;
insert into orders values(seq_orderId.Nextval, 100102, 'ord100101');
insert into orders values(seq_orderId.Nextval, 100106, 'ord100102');
insert into orders values(seq_orderId.Nextval, 100108, 'ord100103');
insert into orders values(seq_orderId.Nextval, 100108, 'ord100104');
-------Table : items-----------------------------------------------------------------------------------------------------------------------------------
drop table items;
create table items(
itemId number(11) not null,
itemName varchar2(30) not null,
itemPrice number(11,2) not null,
itemDetail varchar2(200) not null,
primary key (itemId)
);
create sequence seq_itemId minvalue 1000000 maxvalue 99999999999 start with 1000000 increment by 1;
create or replace trigger tg_insertItem
before insert on items for each row when (new.itemId is null)
begin
select seq_itemId.Nextval into:new.itemId from dual;
end;
insert into items values(seq_itemId.Nextval, 'milk', 12.50, 'good milk');
insert into items values(seq_itemId.Nextval, 'car', 65000.50, 'fast car');
insert into items values(seq_itemId.Nextval, 'Australia Steak', 188.50, 'delicious');
insert into items values(seq_itemId.Nextval, 'UA Bag', 350, 'cool');
-------Table : orderDetail-----------------------------------------------------------------------------------------------------------------------------------
drop table orderDetail;
create table orderDetail(
orderDetailId number(11) not null,
order_id number(11) not null,
item_id number(11) not null,
item_number number(8) not null,
item_price number(11,2) not null,
primary key (orderDetailId),
constraint fk_orderDetail_orders foreign key (order_id) references orders (orderId),
constraint fk_orderDetail_items foreign key (item_id) references items (itemId)
);
create sequence seq_orderDetailId minvalue 1000000 maxvalue 99999999999 start with 1000000 increment by 1;
create or replace trigger tg_insertOrderDetail
before insert on orderDetail for each row when (new.orderDetailId is null)
begin
select seq_orderDetailId.Nextval into:new.orderDetailId from dual;
end;
insert into orderDetail values(seq_orderDetailId.Nextval, 1000001, 1000002, 1, 65000.50);
insert into orderDetail values(seq_orderDetailId.Nextval, 1000003, 1000001, 3, 12.50);
insert into orderDetail values(seq_orderDetailId.Nextval, 1000002, 1000003, 1, 188.50);
insert into orderDetail values(seq_orderDetailId.Nextval, 1000004, 1000004, 2, 350);
insert into orderDetail values(seq_orderDetailId.Nextval, 1000004, 1000001, 1, 12.50);
e. 配置 mapper.xml 和 Mapper 接口,本次只配置一个简单的查询 mapper.xml,更多的mapper.xml 配置请参考我前面的 MyBatis blog。
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="ssm.mapper.UserMapper">
<!-- namespace:本 mapper.xml 对应的 Mapper 接口的类路径
这样在调用接口的时候,MyBatis 会通过 mapper 自动找到要执行的 SQL 语句
-->
<!-- 定义SQL 片段,可重复使用 -->
<sql id="query_user_ByUserId">
<!-- 注意:要做不为null 和 '' 校验 -->
<if test="value != null and value != ''">
and users.userId = #{value}
</if>
</sql>
<!-- 动态 sql 查询用户信息 -->
<select id="findUserById" parameterType="int" resultType="user">
select * from users
<!-- where 可以自动处理第一个 and -->
<where>
<include refid="query_user_ByUserId"></include>
</where>
</select>
</mapper>
UserMapper.java 接口
package sam.mapper;
import sam.model.User;
public interface UserMapper {
//根据用户Id 查询用户
public User findUserById(int userId) throws Exception;
}
f. MyBatis 的配置基本都完成了,下面写一个测试类 测试一下mybatis 是否好用。右键 UserMapper.java new 一个 Junit Test Case,将 package 修改成 test package。然后下一步。勾选 UserMapper 接口中需要测试的方法。如下:
UserMapperTest.java
package ssm.test;
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;
import org.junit.Before;
import org.junit.Test;
import ssm.mapper.UserMapper;
import ssm.model.User;
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
String resource = "mybatis/SqlMapConfig.xml";
@Before
public void setUp() throws Exception {
// 通过输入流读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
// 通过SqlSessionFactoryBuilder,获取SqlSessionFactory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserById() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserById(100101);
System.out.println(user);
sqlSession.commit();
sqlSession.close();
}
}
运行 Test case,应该可以查询出 User。至此,mybatis 部分算是搞定了。下面整合 spring。