Spring和MyBatis整合


阅读数: 次   

文章导航

Spring和MyBatis整合

🍉🍉🍉

实现Spring和MyBatis整合…

思路

可以发现 ，MyBatis最终是通过SqlSessionFactory来操作数据库，Spring整合MyBatis 其实就是 将MyBatis的SqlSessionFactory 交给Spring。

图书管理项目

一. 需要的jar包

jar包

二. 源文件

1). 实体类

Account.java

1
2
3
4
5
6
7
public class Account {
	private Long aid;
	private String aname;
	private String aloc;

    //省略get/set和构造方法
}

BankBalance.java

1
2
3
4
5
6
public class BankBalance {
	private Long aid;
	private Double bbmoney;
	
    //省略get/set和构造方法
}

Book.java

1
2
3
4
5
6
7
8
public class Book {
	private Integer bid;
	private String bname;
	private Double bprice;
	private String bpublisher;
        
    //省略get/set和构造方法
}

BookStock.java

1
2
3
4
5
6
public class BookStock {
	private Integer bid;
	private Integer bsstock;
    
    //省略get/set和构造方法
}

2). Dao数据访问层

AccountDao.java

1
2
3
4
5
6
7
8
9
10
public interface AccountDao{
	//添加用户
	void insertAccount(Account account);
	//修改用户
	void updateAccount(Account account);
	//删除用户
	void delAccount(Long aid);
	//查询用户
	List<Account> queryAccount(Long aid);
}

BankBalanceDao.java

1
2
3
4
5
6
7
8
public interface BankBalanceDao {
	//修改账户
	void updateBalance(BankBalance bank);
	//添加账户
	void insertBalance(BankBalance bank);
	//查询账户
	Double queryBalance(Long aid);
}

BookDao.java

1
2
3
4
5
6
7
8
9
10
public interface BookDao {
	//添加图书
	void insertBook(Book book);
	//删除图书
	void delBook(Integer bid);	
	//修改图书
	void updateBook(Book book);
	//图书查询
	List<Book> queryBook(Integer bid);
}

BookStockDao.java

1
2
3
4
5
6
7
8
public interface BookStockDao {
	//查询库存
	Integer queryStock(Integer bid);
	//修改库存
	void updateStock(BookStock bookStock);
	//添加图书
	void insertStock(BookStock bookStock);
}

3). Service业务层

  • 接口

AccountService.java

1
2
3
4
5
6
7
8
9
10
public interface AccountService {
	//添加账户
	void insertAccount(Account account);
	//账户修改
	void updateAccount(Account account);
	//删除账户
	void delAccount(Long aid);
	//账户查询
	List<Account> queryAccount(Long aid);
}

BankBalanceService.java

1
2
3
4
5
6
7
8
public interface BankBalanceService {
	//账户充值
	void updateBalance(BankBalance bank);
	//添加账户
	void insertBalance(BankBalance bank);
	//余额查询
	Double queryBalance(Long aid);
}

BookService.java

1
2
3
4
5
6
7
8
9
10
public interface BookService {
	//新增图书
	void insertBook(Book book);
	//删除图书
	void delBook(Integer bid);	
	//修改图书
	void updateBook(Book book);
	//图书查询
	List<Book> queryBook(Integer bid);
}

BookStockService.java

1
2
3
4
5
6
7
public interface BookStockService {
	//查询库存
	Integer queryStock(Integer bid);
	void insertStock(BookStock bookStock);
	//买卖图书
	void updateStock(BookStock bookStock);
}
  • 实现

AccountImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@Service
public class AccountImpl implements AccountService {

	@Autowired
	private AccountDao accountDao;

	@Override
	public void insertAccount(Account account) {
		// TODO Auto-generated method stub
		accountDao.insertAccount(account);
	}

	@Override
	public void updateAccount(Account account) {
		// TODO Auto-generated method stub
		accountDao.updateAccount(account);
	}

	@Override
	public void delAccount(Long aid) {
		// TODO Auto-generated method stub
		accountDao.delAccount(aid);
	}

	@Override
	public List<Account> queryAccount(Long aid) {
		// TODO Auto-generated method stub
		return accountDao.queryAccount(aid);
	}

}

BankBalanceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Service
public class BankBalanceImpl implements BankBalanceService {

	@Autowired
	private BankBalanceDao bankBalanceDao;
	@Override
	public void updateBalance(BankBalance bank) {
		// TODO Auto-generated method stub
		bankBalanceDao.updateBalance(bank);
	}

	@Override
	public Double queryBalance(Long aid) {
		// TODO Auto-generated method stub
		return bankBalanceDao.queryBalance(aid);
	}

	@Override
	public void insertBalance(BankBalance bank) {
		// TODO Auto-generated method stub
		bankBalanceDao.insertBalance(bank);		
	}

}

BookImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Service
public class BookImpl implements BookService {

	@Autowired
	private BookDao bookDao;
	@Override
	public void insertBook(Book book) {
		// TODO Auto-generated method stub
		bookDao.insertBook(book);
	}

	@Override
	public void delBook(Integer bid) {
		// TODO Auto-generated method stub
		bookDao.delBook(bid);
	}

	@Override
	public void updateBook(Book book) {
		// TODO Auto-generated method stub
		bookDao.updateBook(book);
	}

	@Override
	public List<Book> queryBook(Integer bid) {
		// TODO Auto-generated method stub
		return bookDao.queryBook(bid);
	}

}

BookStockImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Service
public class BookStockImpl implements BookStockService {

	@Autowired
	private BookStockDao bookstockDao;
	@Override
	public void updateStock(BookStock bookStock) {
		// TODO Auto-generated method stub
		bookstockDao.updateStock(bookStock);
	}

	@Override
	public Integer queryStock(Integer bid) {
		// TODO Auto-generated method stub
		return bookstockDao.queryStock(bid);
	}

	@Override
	public void insertStock(BookStock bookStock) {
		// TODO Auto-generated method stub
		bookstockDao.insertStock(bookStock);
	}

}

4). Controller控制层

AccountController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Component
public class AccountController {

	@Autowired
	private AccountImpl accountImpl;

	public void insertAccount(Account account) {
		accountImpl.insertAccount(account);
		System.out.println("---------添加用户成功！---------");
	}

	public void updateAccount(Account account) {
		accountImpl.updateAccount(account);
		System.out.println("---------修改用户信息成功！---------");
	}

	public void delAccount(Long aid) {
		accountImpl.delAccount(aid);
		System.out.println("---------删除["+aid+"用户]成功！---------");
	}

	public void queryAccount(Long aid) {
		List<Account> queryAccount = accountImpl.queryAccount(aid);
		System.out.println("---------查询["+aid+"用户信息]---------");
		for(Account account : queryAccount){
			System.out.println(account);
		}
	}

}

BankBalanceController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Component
public class BankBalanceController {
	@Autowired
	private BankBalanceImpl bankBalanceImpl;
	
	public void updateBalance(BankBalance bank) {
		if(bankBalanceImpl.queryBalance(bank.getAid())!=null){
			bankBalanceImpl.updateBalance(bank);
		}else{
			bankBalanceImpl.insertBalance(bank);			
		}
		System.out.println("---------["+bank.getAid()+"用户充值:"+bank.getBbmoney()+"元]---------");			
	}

	public void queryBalance(Long aid) {
		Double Balance = bankBalanceImpl.queryBalance(aid);
		System.out.println("---------查询["+aid+"用户余额为:"+Balance+"元]---------");
	}

}

BookController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
@Component
public class BookController {

	@Autowired
	private BookImpl bookImpl;
	
	public void insertBook(Book book) {
		bookImpl.insertBook(book);
		 System.out.println("---------添加图书成功！---------");		
	}

	public void delBook(Integer bid) {
		bookImpl.delBook(bid);
		 System.out.println("---------删除["+bid+"图书]成功！---------");		
	}

	public void updateBook(Book book) {
		bookImpl.updateBook(book);
		 System.out.println("---------修改图书信息成功！---------");
	}

	public void queryBook(Integer bid) {
		 List<Book> books = bookImpl.queryBook(bid);
		 System.out.println("---------查询["+bid+"图书信息]---------");
		 for(Book book : books){
			 System.out.println(book);
		 }		 
	}
}

BookStockController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@Component
public class BookStockController {

	@Autowired
	private BookStockImpl bookstockImpl;
	@Autowired
	private BookImpl bookImpl;
	@Autowired
	private BankBalanceImpl bankbalanceImpl;
	
	public void updateStock(BookStock bookStock) {
		if(bookstockImpl.queryStock(bookStock.getBid())!=null){
			bookstockImpl.updateStock(bookStock);
		}else{
			bookstockImpl.insertStock(bookStock);
		}
		System.out.println("---------["+bookStock.getBid()+"图书]进货"+bookStock.getBsstock()+"本成功！---------");
	}

	//事务注解
	@Transactional
	public void delStock(Long Aid,BookStock bookStock) {
		//图书单价
		Double bookprice = bookImpl.queryBook(bookStock.getBid()).get(0).getBprice();
		if(bankbalanceImpl.queryBalance(Aid)>=bookprice*bookStock.getBsstock()){
			//剪出图书库存
			bookstockImpl.updateStock(new BookStock(bookStock.getBid(),-bookStock.getBsstock()));
			//扣除顾客买书金额
			bankbalanceImpl.updateBalance(new BankBalance(Aid,-bookprice*bookStock.getBsstock()));
			System.out.println("---------["+Aid+"用户]购买["+bookStock.getBid()+"图书]"+bookStock.getBsstock()+"本成功！---------");
		}else{
			System.out.println("---------["+Aid+"用户]余额不足，请进行充值！---------");
		}
		System.out.println("---------["+bookStock.getBid()+"图书]出售"+bookStock.getBsstock()+"本成功！---------");
	}

}

5). 测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public class BookStoreTest {
	
	public static void main(String[] args) {
		ApplicationContext Ac = new ClassPathXmlApplicationContext("applicationContext.xml");
		AccountController acc = Ac.getBean("accountController",AccountController.class);
//		//id查询顾客信息
//		acc.queryAccount(478899199910013369L);
//		//添加顾客信息
//		acc.insertAccount(new Account(478899054910299899L,"赵云","梵蒂冈"));
//		//修改顾客信息
//		acc.updateAccount(new Account(478899054910299899L,"赵子龙","梵蒂冈"));
//		//删除顾客信息
//		acc.delAccount(478899054910299899L);
		BankBalanceController bank = Ac.getBean("bankBalanceController",BankBalanceController.class);
//		//顾客充值
//		bank.updateBalance(new BankBalance(478899054910299899L,10000.0));
//		//查询顾客余额
//		bank.queryBalance(478899054910299899L);
		BookController book = Ac.getBean("bookController",BookController.class);
//		//添加图书信息
//		book.insertBook(new Book(1007,"《狂人日记》",55.8,"鲁迅"));
//		//修改图书信息
//		book.updateBook(new Book(1007,"《狂人日记》",50.8,"鲁迅"));
//		//查询图书信息
//		book.queryBook(1007);
//		//删除图书
//		book.delBook(1007);
		BookStockController bookstore = Ac.getBean("bookStockController",BookStockController.class);
//		//进购图书
//		bookstore.updateStock(new BookStock(1007,30));
		//买卖图书
		bookstore.delStock(478899054910299899L,new BookStock(1001,2));
	}
}

三. 项目所需要的配置文件

1). applicationContext.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<!-- 自动扫包 -->
	<context:component-scan base-package="com.AAA.teng"/>
	<!-- 引入数据库信息 -->
 	<context:property-placeholder location="classpath:db.properties"/>
 	<!-- 配置数据源 -->
	<bean id="ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}"/>
		<property name="jdbcUrl" value="${jdbc.url}"/>
		<property name="user" value="${jdbc.user}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>
	
	<!-- spring使用Mybatis配置映射文件 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- Mybatis配置文件位置 -->
		<property name="configLocation" value="classpath:mybatis/MybatisConfig.xml"></property>
		<!-- 数据源 -->
		<property name="dataSource" ref="ds"></property>
		<!-- 别名 -->
		<property name="typeAliasesPackage" value="com.AAA.teng.modle"></property>
		<!-- sql映射文件 -->
		<property name="mapperLocations" value="classpath:mybatis/mapper/*.xml"></property>
	</bean>
	
	
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<!-- 把dao接口的实现加入到ioc容器中 -->
		<property name="basePackage" value="com.AAA.teng.dao"/>
		<!-- 指定sqlSessionFactory -->
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
	</bean>
	
	<!-- 1.配置一个事务管理器 -->
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="ds"/>
	</bean>

<!-- 使用注解方式 -->
	<tx:annotation-driven transaction-manager="txManager"/>

<!-- 使用配置方式 -->	
<!--
 	2.配置事务通知 
	<tx:advice id="txAdvice" transaction-manager="txManager">
		<tx:attributes>
			<tx:method name="delStock" timeout="250"/>
		</tx:attributes>
	</tx:advice>
	
	3.配置事务与切点的关联
	<aop:config>
		<aop:pointcut expression="execution(* com.AAA.teng.Controller.*.*(..))" id="p"/>
		<aop:advisor advice-ref="txAdvice" pointcut-ref="p"/>
	</aop:config> -->
</beans>

2). 数据库配置

1
2
3
4
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:BOOKSHOP
jdbc.user=scott
jdbc.password=123456

3). 日志文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
 <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
   <param name="Encoding" value="UTF-8" />
   <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
   </layout>
 </appender>
 <logger name="java.sql">
   <level value="debug" />
 </logger>
 <logger name="org.apache.ibatis">
   <level value="info" />
 </logger>
 <root>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

4). 数据库文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
create table book(
	bid number(4) primary key,
	bname varchar2(20),
	bprice number(4,2),
	bpublisher varchar2(40)
);
insert into book values(1001,'《童话故事》',15.8,'安徒生');
insert into book values(1002,'《古兰经》',20.05,'耶稣');
insert into book values(1003,'《西天取经》',27.9,'释迦摩尼');
insert into book values(1004,'《道德经》',30,'老子');
insert into book values(1005,'《对象面向Java开发》',35.67,'Get');
insert into book values(1006,'《聊斋志异》',25.0,'蒲松龄');
create table book_stock(
	bid number(4) primary key,
	bsstock number(3) check(bsstock >= 0),
	constraint fk_book_bid foreign key(bid) references book(bid) on delete cascade
);
insert into book_stock values(1001,10);
insert into book_stock values(1002,10);
insert into book_stock values(1003,10);
insert into book_stock values(1004,10);
insert into book_stock values(1005,10);
insert into book_stock values(1006,10);
create table account(
	aid char(18) primary key,
	aname varchar2(20),
	aloc varchar2(50)
);
insert into account values(478899199910013369,'张三','圣蒂兰岛');
insert into account values(476666202005250000,'李四','南极洲');
create table bank_balance(
	aid char(18) primary key,
	bbmoney number(10,2) check(bbmoney >= 0),
	constraint fk_account_aid foreign key(aid) references account(aid) on delete cascade
);
insert into bank_balance values(478899199910013369,10000000.00);
insert into bank_balance values(476666202005250000,9000);

5). MyBatis的sql映射文件

AccountMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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.AAA.teng.dao.AccountDao">
	<insert id="insertAccount" parameterType="Account">
		insert into account values(#{aid},#{aname},#{aloc})
	</insert>
	<update id="updateAccount" parameterType="Account">
		update account set aname=#{aname},aloc=#{aloc} where aid=#{aid}
	</update>
	<delete id="delAccount">
		delete account where aid=#{aid}
	</delete>
	<select id="queryAccount" resultType="Account">
		select aid,aname,aloc from account where aid=#{aid}
	</select>
</mapper>

BankBalanceMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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.AAA.teng.dao.BankBalanceDao">
	<update id="updateBalance" parameterType="BankBalance">
		update bank_balance set bbmoney=bbmoney+#{bbmoney} where aid=#{aid}
	</update>
	<insert id="insertBalance" parameterType="BankBalance">
		insert into bank_balance values(#{aid},#{bbmoney})
	</insert>
	<select id="queryBalance" resultType="Double" parameterType="Long">
		select bbmoney from bank_balance where aid=#{aid}
	</select>
</mapper>

BookMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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.AAA.teng.dao.BookDao">
	<insert id="insertBook" parameterType="Book">
		insert into Book values(#{bid},#{bname},#{bprice},#{bpublisher})
	</insert>
	<update id="updateBook" parameterType="Book">
		update Book set bname=#{bname},bprice=#{bprice},bpublisher=#{bpublisher} where bid=#{bid}
	</update>
	<delete id="delBook">
		delete from Book where bid=#{bid}
	</delete>
	<select id="queryBook" resultType="Book">
		select bid,bname,bprice,bpublisher from Book where bid=#{bid}
	</select>
</mapper>

BookStockMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?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.AAA.teng.dao.BookStockDao">
	<select id="queryStock" resultType="Integer">
		select bsstock from book_stock where bid=#{bid}
	</select>
	<update id="updateStock" parameterType="BookStock">
		update book_stock set bsstock=bsstock+#{bsstock} where bid=#{bid}
	</update>
	<insert id="insertStock" parameterType="BookStock">
		insert into book_stock values(#{bid},#{bsstock})
	</insert>
</mapper>

日志项目(一对多，一对一)

一. 项目结构

项目结构

二. 源文件

1). 实体类

Dept.java

1
2
3
4
5
6
public class Dept {
    private Integer did;
    private String dname;

    //省略get/set和构造方法
}

Emp.java

1
2
3
4
5
6
7
public class Emp {
    private Integer eid;
    private String ename;
    private Dept dept;
	
    //省略get/set和构造方法
}

Level.java

1
2
3
4
5
6
public class Level {
    private Integer lid;
    private String lname;
        
    //省略get/set和构造方法
}

Type.java

1
2
3
4
5
6
7
public class Type {
    private Integer tid;
    private String tname;
    private List<Level> levels;
        
    //省略get/set和构造方法
}

Log.java

1
2
3
4
5
6
7
8
9
10
public class Log {
    private Integer lgid;//日志id
    private String sn;//设备唯一标识
    private String ename;//员工姓名
    private String tname;//校准类型
    private String res;//校准结果
    private String info;//校准信息
    
    //省略get/set和构造方法
}

2). Dao数据访问层

DeptDao.java

1
2
3
4
public interface DeptDao {
    //根据Id查询部门
    Dept queryIdDept(Integer did);
}

EmpDao.java

1
2
3
4
public interface EmpDao {
    //查询所有员工
    List<Emp> queryAllEmp();
}

LevelDao.java

1
2
3
4
public interface LevelDao {
    //根据Id查询挡位信息
    Level queryIdLevel(Integer tid);
}

xxxxxxxxxx — 查看系统下表空间视图select distinct(tablespace_name) from dba_free_space;—去除重复—计算表空间大小select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;—数据文件select from dba_data_files—临时文件select from dba_temp_files— 创建表空间create tablespace tsp_test01datafile ‘D:/a.dbf’—存放路径size 10m—文件大小autoextend no next 5m maxsize 30m—超出10m每次递增5m，最大为30m— 创建分布式表空间(数据文件分开放)create tablespace tsp_test02datafile ‘D:/b.dbf’size 10mautoextend no next 5m maxsize 30m,’D:/c.dbf’size 10mautoextend no next 5m maxsize 30m— 临时表空间create temporary tablespace temp_test03—temporary临时tempfile ‘D:/temp_d.dbf’—tempfile临时文件size 10m— 创建指定表空间用户create user uname identified by 123default tablespace tsp_test01—表空间Temporary tablespace temp_test03—临时表空间— 修改表空间(表空间可以有多个文件)alter tablespace tsp_test02add datafile ‘D:\APP\TENG\ORADATA\TEST\test_02.dbf’ size 100m— 查看默认表空间select * from database_properties where property_name like ‘%TABLESPACE%’;— 删除表空间drop tablespace tsp_test02including contents and datafiles;—删除表空间下文件​sql

1
2
3
4
public interface TypeDao {
    //查询所有校准类型信息
    List<Type> queryAllType();
}

LogDao.java

1
2
3
4
5
6
public interface LogDao {
    //插入日志
    void insertLog(Log log);
    //根据条件查询日志
    List<Log> queryByCondition(@Param("sn") String sn,@Param("ename") String ename,@Param("tname") String tname);
}

3). Service业务层

  • 接口

DeptService.java

1
2
3
4
public interface DeptService {
    //根据id查询部门
    Dept queryIdDept(Integer did);
}

EmpService.java

1
2
3
4
public interface EmpService {
    //查询所有员工
    List<Emp> queryAllEmp();
}

LevelService.java

1
2
3
4
public interface LevelService {
    //根据Id查询挡位
    Level queryIdLevel(Integer tid);
}

TypeService.java

1
2
3
4
public interface TypeService {
    //查询所有校准类型
    List<Type> queryAllType();
}

LogService.java

1
2
3
4
5
6
public interface LogService {
    //插入日志
    void insertLog(Log log);
    //根据条件查询日志
    List<Log> queryByCondition(String sn,String ename,String tname);
}
  • 实现

DeptServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
@Service
public class DeptServiceImpl implements DeptService {

    @Autowired
    private DeptDao deptDao;

    @Override
    public Dept queryIdDept(Integer did) {
        return deptDao.queryIdDept(did);
    }
}

EmpServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpDao empDao;

    @Override
    public List<Emp> queryAllEmp() {
        return empDao.queryAllEmp();
    }
}

LevelServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
@Service
public class LevelServiceImpl implements LevelService {

    @Autowired
    private LevelDao levelDao;

    @Override
    public Level queryIdLevel(Integer lid) {
        return levelDao.queryIdLevel(lid);
    }
}

TypeServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
@Service
public class TypeServiceImpl implements TypeService {

    @Autowired
    private TypeDao typeDao;

    @Override
    public List<Type> queryAllType() {
        return typeDao.queryAllType();
    }
}

LogServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Service
public class LogServiceImpl implements LogService {

    @Autowired
    private LogDao logDao;


    @Override
    public void insertLog(Log log) {
        logDao.insertLog(log);
    }

    @Override
    public List<Log> queryByCondition(String sn, String ename, String tname) {
        return logDao.queryByCondition(sn,ename,tname);
    }
}

4). 测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public class Test {
    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        System.out.println("-----查询校准种类匹配-----");
        TypeService TypeService = applicationContext.getBean(TypeService.class);
        List<Type> Types = TypeService.queryAllType();
        for(Type Type: Types){
            System.out.println(Type);
        }
        System.out.println("-----查询校准种类匹配-----");
        EmpService empService = applicationContext.getBean(EmpService.class);
        List<Emp> emps = empService.queryAllEmp();
        for(Emp emp: emps){
            System.out.println(emp);
        }
        System.out.println("-----查询日志-----");
        LogService logService = applicationContext.getBean(LogService.class);
        List<Log> logs = logService.queryByCondition(null, null, "方波校准");
        for(Log log: logs){
            System.out.println(log);
        }
    }
}

三. 项目所需要的配置文件

1). applicationContext.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<!-- 自动扫包 -->
	<context:component-scan base-package="com.AAA.teng"/>
	<!-- 引入数据库信息 -->
 	<context:property-placeholder location="classpath:db.properties"/>
 	<!-- 配置数据源 -->
	<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${jdbc.driver}"/>
		<property name="Url" value="${jdbc.url}"/>
		<property name="username" value="${jdbc.user}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>
	
	<!-- spring使用Mybatis配置映射文件 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- Mybatis配置文件位置 -->
		<property name="configLocation" value="classpath:mybatis/MybatisConfig.xml"></property>
		<!-- 数据源 -->
		<property name="dataSource" ref="ds"></property>
		<!-- 别名 -->
		<property name="typeAliasesPackage" value="com.AAA.teng.entity"></property>
		<!-- sql映射文件 -->
		<property name="mapperLocations" value="classpath:mybatis/mapper/*.xml"></property>
	</bean>
	
	
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<!-- 把dao接口的实现加入到ioc容器中 -->
		<property name="basePackage" value="com.AAA.teng.dao"/>
		<!-- 指定sqlSessionFactory -->
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
	</bean>
	
	<!-- 1.配置一个事务管理器 -->
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="ds"/>
	</bean>

<!-- 使用注解方式配置事务 -->
	<tx:annotation-driven transaction-manager="txManager"/>

</beans>

2). 数据库配置

1
2
3
4
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:BOOKSHOP
jdbc.user=scott
jdbc.password=123456

3). 日志文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
 <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
   <param name="Encoding" value="UTF-8" />
   <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
   </layout>
 </appender>
 <logger name="java.sql">
   <level value="debug" />
 </logger>
 <logger name="org.apache.ibatis">
   <level value="info" />
 </logger>
 <root>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

4). 数据库文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
--部门表
create table qkp0609_dept(
       did number(4) primary key,
       dname varchar2(20)
);

insert into qkp0609_dept values(1,'研发');
insert into qkp0609_dept values(2,'测试');
insert into qkp0609_dept values(3,'行政');
insert into qkp0609_dept values(4,'财务');
insert into qkp0609_dept values(5,'后勤');
insert into qkp0609_dept values(6,'生产');

--员工表
create table qkp0609_emp(
       eid number(4) primary key,
       ename varchar2(20),
       did number(4),
       constraint fk_did foreign key(did) references qkp0609_dept(did) on delete cascade
);

insert into qkp0609_emp values(1001,'赵云',1);
insert into qkp0609_emp values(1002,'张飞',2);
insert into qkp0609_emp values(1003,'刘备',3);
insert into qkp0609_emp values(1004,'曹操',4);
insert into qkp0609_emp values(1005,'关羽',6);

--校准种类表
create table qkp0609_type(
       tid number(4) primary key,
       tname varchar2(20)
);

insert into qkp0609_type values(1,'方波校准');
insert into qkp0609_type values(2,'正弦波校准');

--档位表
create table qkp0609_level(
       lid number(2) primary key,
       lname varchar2(10)
);
insert into qkp0609_level values(1,'1mv');
insert into qkp0609_level values(2,'2mv');
insert into qkp0609_level values(3,'5mv');
insert into qkp0609_level values(4,'10mv');
insert into qkp0609_level values(5,'20mv');
insert into qkp0609_level values(6,'50mv');
insert into qkp0609_level values(7,'1v');
insert into qkp0609_level values(8,'5v');
insert into qkp0609_level values(9,'10v');
insert into qkp0609_level values(10,'50v');
insert into qkp0609_level values(11,'100v');
insert into qkp0609_level values(12,'1kv');
insert into qkp0609_level values(13,'10kv');


--种类档位表
create table qkp0609_type_level(
       tid number(4),
       lid number(2),
       constraint fk_tid foreign key(tid) references qkp0609_type(tid) on delete cascade,
       constraint fk_lid foreign key(lid) references qkp0609_level(lid) on delete cascade
);
insert into qkp0609_type_level values(1,1);
insert into qkp0609_type_level values(1,3);
insert into qkp0609_type_level values(1,5);
insert into qkp0609_type_level values(1,8);
insert into qkp0609_type_level values(1,11);
insert into qkp0609_type_level values(2,1);
insert into qkp0609_type_level values(2,2);
insert into qkp0609_type_level values(2,4);
insert into qkp0609_type_level values(2,8);
insert into qkp0609_type_level values(2,10);
insert into qkp0609_type_level values(2,12);


--日志表
create table qkp0609_log(
       lgid number(4) primary key,
       sn char(15),
       eid number(4),
       tid number(4),
       res varchar2(50),
       info varchar2(200),
       constraint fk_log_eid foreign key(eid) references qkp0609_emp(eid) on delete cascade,
       constraint fk_log_tid foreign key(tid) references qkp0609_type(tid) on delete cascade
)segment creation immediate;
create sequence seq_qkp0609_log increment by 1 start with 1;

insert into qkp0609_log values(seq_qkp0609_log.nextval,'100100100100001',1001,1,'成功','一切顺利');
insert into qkp0609_log values(seq_qkp0609_log.nextval,'100100100100001',1002,2,'成功','一切顺利');
insert into qkp0609_log values(seq_qkp0609_log.nextval,'100100100100002',1003,1,'失败','波形信号有误');

5). MyBatis的sql映射文件

DeptMapper.xml

1
2
3
4
5
6
7
<?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.AAA.teng.dao.DeptDao">
	<select id="queryIdDept" resultType="Dept">
		select did,dname from ztf0609_dept where did=#{did}
	</select>
</mapper>

EmpMapper.xml

  • 一对一
1
2
3
4
5
6
7
8
9
10
11
12
<?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.AAA.teng.dao.EmpDao">
	<resultMap id="empDept" type="emp">
		<id property="eid" column="eid"></id>
		<association property="dept" javaType="Dept" column="did" select="com.AAA.teng.dao.DeptDao.queryIdDept">
		</association>
	</resultMap>
    <select id="queryAllEmp" resultMap="empDept">
		select * from ztf0609_Emp
	</select>
</mapper>

LevelMapper.xml

1
2
3
4
5
6
7
<?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.AAA.teng.dao.LevelDao">
    <select id="queryIdLevel" resultType="Level">
		select * from ztf0609_Level l,ztf0609_type_level tl where tl.lid=l.lid and tl.tid=#{tid}
	</select>
</mapper>

TypeMapper.xml

  • 一对多
1
2
3
4
5
6
7
8
9
10
11
12
<?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.AAA.teng.dao.TypeDao">
	<resultMap id="Type_level" type="Type">
		<id property="tid" column="tid"></id>
		<collection property="levels" ofType="Level" column="tid" select="com.AAA.teng.dao.LevelDao.queryIdLevel">
		</collection>
	</resultMap>
    <select id="queryAllType" resultMap="Type_level">
		select * from ztf0609_Type
	</select>
</mapper>

LogMapper.xml

  • 动态SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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.AAA.teng.dao.LogDao">
	<sql id="selectEmp">
		(select eid from ztf0609_emp where ename=#{ename})
	</sql>
	<sql id="selectType">
		(select tid from ztf0609_type where tname=#{tname})
	</sql>
    <insert id="insertLog" parameterType="Log">
		insert into ztf0609_log values(seq_ztf0609_log.nextval,#{sn},
		<include refid="selectEmp"/>,
		<include refid="selectType"/>,
		#{res},#{info})
	</insert>
	<select id="queryByCondition" resultType="Log">
		select * from ztf0609_log lg,ztf0609_type t,ztf0609_emp e where lg.eid=e.eid and lg.tid=t.tid
		<trim prefix="and" prefixOverrides="and">
			<if test="sn!=null and sn!=''">
				and lg.sn=#{sn}
			</if>
			<if test="ename!=null and ename!=''">
				and lg.eid=<include refid="selectEmp"/>
			</if>
			<if test="tname!=null and tname!=''">
				and lg.tid=<include refid="selectType"/>
			</if>
		</trim>
	</select>
</mapper>
赏

感谢支持!

微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

正因为没有翅膀，<br>人们才会寻找飞翔的方法。