超市订单管理系统（mybatis）

超市订单管理系统（mybatis）

准备阶段——————数据库建立（基于mysql）

• 建表命令

create table smbms_role(
	id bigint(20) not null auto_increment ,
	roleCode varchar(15),
	roleName varchar(15),
	createdBy bigint(15),
	creationDate datetime,
	modifyBy bigint(20),
	modifyDate datetime,
	constraint pk_role primary key(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

create table smbms_user(
	id bigint(20) not null auto_increment,
	userCode varchar(15),
	userName varchar(15),
	userPassword varchar(15),
	gender int(10),
	birthday date,
	phone varchar(15),
	address varchar(40),
	userRole bigint(20),
	createdBy bigint(20),
	creationDate datetime,
	modifyBy bigint(20),
	modifyDate datetime,
	constraint pk_user primary key(id),
	constraint fk_role foreign key(userRole) references smbms_role(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

create table smbms_provider(
	id bigint(20) not null auto_increment,
	proCode varchar(20),
	proName varchar(20),
	proDesc varchar(50),
	proContact varchar(20),
	proPhone varchar(20),
	proAddress varchar(50),
	proFax varchar(20),
	createdBy bigint(20),
	creationDate datetime,
	modifyBy bigint(20),
	modifyDate datetime,
	constraint pk_ primary key(id)
	
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

create table smbms_bill(
	id bigint(20) not null auto_increment,
	billCode varchar(20),
	productName varchar(20),
	productDesc varchar(50),
	productUnit varchar(20),
	productCount decimal(20,2),
	totalPrice decimal(20,2),
	isPayment int(10),
	providerId bigint(20),
	createdBy bigint(20),
	creationDate datetime,
	modifyBy bigint(20),
	modifyDate datetime,
	constraint pk_bill primary key(id),
	constraint fk_provider foreign key(providerId) references smbms_provider(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

create table smbms_address(
	id bigint(20) not null auto_increment,
	contact varchar(15),
	addressDesc varchar(50),
	postCode varchar(15),
	tel varchar(20),
	createdBy bigint(20),
	creationDate datetime,
	modifyBy bigint(20),
	modifyDate datetime,
	userId bigint(20),
	constraint pk_address primary key(id),
	constraint fk_user foreign key(userId) references smbms_user(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

• 所遇问题

  由于长时间没有使用sql和mysql命令导致一些基本问题的产生。

	   1.创建数据库过程中，出现权限问题。
	   解决：使用root账号登录，为用户创建相应的数据库，授予用户相应的权限。
	   命令：create database name;
		   grant all privileges on dbname.* to test@localhost;
		   revoke 用于取消
		   flush privileges;
		   show grants for username;
		

mybatis的入门记录

• 导入相关的jar包后
• mybatis主要的包含的文件
  mybatis-config.xml、pojo和sql映射文件。
• mybatis的核心对象
  SqlSessionFactoryBuilder—build()生成SqlSessionFactory-----openSession()生成SqlSession
• 核心对象的作用范围
  SqlSessionFactoryBuilder一旦创建了SqlSessionFactory就不需要了。
  SqlSessionFactory一旦创建就与应用共存亡。
  SqlSession对应的是与数据库的一次对话，如果关闭了则需要重新生成。
• 两种操作数据的方式
  1.利用select操作相关xml文件中的id
  2.利用getMapper操作接口的方式（接口中的方法名必须与xml中的id名相同）

mybatis初步小结

什么是持久化：数据的相关操作就是持久化。
orm：对象和关系之间的映射（突然想到了对象型数据库）
jdbc：加载驱动—建立连接—执行sql的预编译—执行sql
mybatis：创建factory–生成sqlSession（即与数据库的连接）—执行sql

小规模实战

• sql代码

create table equipment(
	id bigint(20) auto_increment primary key,
	type varchar(20) not null,
	price decimal(20,2),
	productDate date 
)ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
insert into equipment(type,price,productDate) values('cdma-1','650.78','2016-1-22');
insert into equipment(type,price,productDate) values('cdma-2','2356.23','2013-1-22');
insert into equipment(type,price,productDate) values('cdma-3','2200.76','2010-1-22');

• POJO

package equipment;

import java.sql.Date;

public class Equipment {
	private Integer id;
	private String type;
	private Float price;
	private Date productDate;
	..........
	..........
}

• Mapper

<?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="equipment.EquipmentMapper">
	<select id="count" resultType="int">
		select count(1) as count from equipment
	</select>
	<select id="getEquipmentList" resultType="equipment.Equipment">
		select * from equipment
	</select>
</mapper>

• Config

<?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="database.properties"/>
	<settings>
		 <setting name='logImpl' value='LOG4J'/>
	</settings>
	<environments default="development">
		<environment id="development">
		<transactionManager type="JDBC"></transactionManager>
		<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>
	<!-- <typeAliases>
		<package name = "smbms.dao">
	</typeAliases> -->
	<mappers>
		<mapper resource="equipment/EquipmentMapper.xml"/>
		
	</mappers>
</configuration>

• MyBatisUtil

package smbms.utils;

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 {
	private static SqlSessionFactory factory;
	static {
		try {
			InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
			factory = new  SqlSessionFactoryBuilder().build(is);
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	public static SqlSession createSqlSession() {
		return factory.openSession();
	}
	public static void closeSqlSession(SqlSession sqlSession) {
		if(sqlSession!=null)
			sqlSession.close();
	}
}

• 接口

package equipment;

import java.util.List;

public interface EquipmentMapper {
	public List<Equipment> getEquipmentList();
}