美文网首页Spring技术Java技术mysql 知识库
我的Mysql&Mybatis积累

我的Mysql&Mybatis积累

作者: 一字马胡 | 来源:发表于2018-06-24 18:02 被阅读35次

    怎么建表

    
DROP DATABASE IF EXISTS `traversal`;
CREATE DATABASE `traversal`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
USE `traversal`;

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`(
  `dept_id` int auto_increment primary key comment '学院id',
  `dept_name` varchar(30) not null comment '学院名字',
  `dept_desc` VARCHAR(100) comment '描述一下这个学院是什么',
  `dept_code` int comment '学院编码'
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT='学院表';

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
  `teacher_id` int AUTO_INCREMENT PRIMARY KEY COMMENT '老师id',
  `teacher_name` VARCHAR(20) NOT NULL DEFAULT '老师' COMMENT '老师名字',
  `teacher_office_add` VARCHAR(30) NOT NULL DEFAULT '教师办公楼' COMMENT '在哪里办公'
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='学院表';

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`(
  `course_id` int AUTO_INCREMENT PRIMARY KEY COMMENT '课程id',
  `dept_id` int COMMENT '属于哪个学院',
  `teacher_id` int COMMENT '属于哪个老师的课程',
  `course_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
  `course_desc` VARCHAR(100) DEFAULT '' COMMENT '描述一下这个课程',
  `course_type_code` int DEFAULT 0 COMMENT '课程类型，默认为0，代表公共课',
  `course_status` int DEFAULT 1 COMMENT '课程状态，默认为1，表示正常',

  FOREIGN KEY (dept_id) REFERENCES `department`(dept_id) ON DELETE CASCADE,
  FOREIGN KEY (teacher_id) REFERENCES `teacher`(teacher_id) ON DELETE CASCADE
)ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='课程信息';

DROP TABLE IF EXISTS `learner`;
CREATE TABLE `learner`(
  `learner_id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生id',
  `dept_id` INT COMMENT '属于哪个学院',
  `learner_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `learner_age` int NOT NULL DEFAULT 0 COMMENT '学生年级',

  FOREIGN KEY (dept_id) REFERENCES `department`(dept_id) ON DELETE CASCADE
)ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8 COMMENT='学生信息';

DROP TABLE IF EXISTS `elective`;
CREATE TABLE `elective`(
  `learner_id` int COMMENT '学生id',
  `course_id` int COMMENT '课程id',
  `create_time` TIMESTAMP NOT NULL DEFAULT now() COMMENT '选课时间',

  FOREIGN KEY (learner_id) REFERENCES `learner`(learner_id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES `course`(course_id) ON DELETE CASCADE,
  PRIMARY KEY (`learner_id`, `course_id`) COMMENT '联合主键，一个学生不能重复选择一门课程'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='选课信息';

/**
    准备一些数据
 */
INSERT INTO
  `department`(`dept_name`, `dept_desc`, `dept_code`)
VALUES
      ('文学院', '汉语言文化学院', 1993001),
      ('物理学院', '研究物理科学和工程的学院', 1993002),
      ('数学科学学院', '专注于攻克数学难题的学院', 1993003),
      ('信息技术科学学院', '现代信息科学技术相关专业学院', 1993004),
      ('历史学院', '研究历史以明鉴是非', 1993005);

INSERT INTO
  `teacher`(`teacher_name`, `teacher_office_add`)
VALUES
  ('胡建', '教师办公楼-F2-C-101'),
  ('李白', '教师办公楼-F1-D-502'),
  ('王安石', '教师办公楼-F7-V-808');

INSERT INTO
  `course`(`dept_id`, `teacher_id`, `course_name`, `course_desc`, `course_type_code` , `course_status`)
VALUES
  (105, 1001, '唐诗宋词', '解读唐诗和宋词', 1, 1),
  (106, 1000, '高能物理', '关于高能物理的相关内容', 2, 1),
  (106, 1000, '化学物理', '使用化学的方法来研究物理', 2, 1),
  (107, 1002, '高等数学（一）', '高等数学一', 0, 1),
  (107, 1000, '高等数学（二）', '高等数学二', 0, 1),
  (108, 1000, 'java语言程序设计', 'java语言基础知识讲解', 3, 1),
  (108, 1000, '计算机体系结构', '非常有价值的计算机专业课程', 3, 0),
  (109, 1002, '近代史', '研究近代史', 0, 1),
  (107, 1000, '古代史', '研究古代史', 0, 1);


INSERT INTO
  `learner`(`dept_id`, `learner_name`, `learner_age`)
VALUES
  (105, '胡小一', 20),
  (106, '胡大意', 25),
  (106, '胡无', 19),
  (107, '大二个', 21),
  (108, '伟人胡健', 23),
  (109, '王小十', 24);

INSERT INTO
  `elective`(`learner_id`, `course_id`)
VALUES
  (100000, 10000),
  (100000, 10003),
  (100000, 10004),
  (100001, 10001),
  (100001, 10003),
  (100001, 10004),
  (100001, 10002),
  (100002, 10002),
  (100002, 10003),
  (100002, 10004),
  (100003, 10003),
  (100003, 10004),
  (100004, 10001),
  (100004, 10003),
  (100004, 10004),
  (100004, 10005),
  (100004, 10006),
  (100005, 10003),
  (100005, 10004),
  (100005, 10007),
  (100005, 10008);

    怎么写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>
    <settings>
        <!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
        <setting name="useGeneratedKeys" value="true" />

        <!-- 使用列别名替换列名 默认:true -->
        <setting name="useColumnLabel" value="true" />

        <!-- 开启驼峰命名转换:a_bc -> aBc -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
</configuration>

    怎么写spring-dao.xml

    
<?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:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
    <!-- 1.配置数据库相关参数properties的属性 -->
    <context:property-placeholder location="classpath:jdbc.properties" />

    <!-- 2.数据库连接池 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!-- 配置连接池属性 -->
        <property name="driverClass" value="${jdbc.driver}" />
        <property name="jdbcUrl" value="${jdbc.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        <!-- c3p0连接池的私有属性 -->
        <property name="maxPoolSize" value="30" />
        <property name="minPoolSize" value="10" />
        <!-- 关闭连接后不自动commit -->
        <property name="autoCommitOnClose" value="false" />
        <!-- 获取连接超时时间 -->
        <property name="checkoutTimeout" value="10000" />
        <!-- 当获取连接失败重试次数 -->
        <property name="acquireRetryAttempts" value="2" />
    </bean>

    <!-- 3.配置SqlSessionFactory对象 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 注入数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
        <!-- 配置MyBaties配置文件:mybatis-config.xml -->
        <property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" />
        <!-- 扫描entity包 使用别名 -->
        <property name="typeAliasesPackage" value="com.hujian.entity" />
        <!-- 扫描sql配置文件:mapper需要的xml文件 -->
        <property name="mapperLocations" value="classpath:mapper/*.xml" />
    </bean>

    <!-- 4.配置扫描Dao接口包，动态实现Dao接口，注入到soring容器中 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 注入sqlSessionFactory -->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        <!-- 给出需要扫描Dao接口包 -->
        <property name="basePackage" value="com.hujian.dao" />
    </bean>

</beans>

    怎么写mapper类

    
public interface CourseMapper {

    public Course queryCourseById1(@Param("courseId") int courseId);

    public Course queryCourseById2(@Param("courseId") int courseId);

    public int insertCourse(Course course);

    public int updateCourse(Course course);

    public int deleteCourseIfExistsById(@Param("courseId") int courseId);

}

    怎么写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="com.hujian.dao.CourseMapper">
    <!-- 做一个ResultMap

    private int courseId;
    private int depId;
    private int teacherId;
    private String courseName;
    private String courseDesc;
    private int courseTypeCode;
    private int courseStatus;

    //多对一属性
    private Department department;
    private Teacher teacher;

    -->
    <resultMap id="QueryCourseResultMap" type="Course">
        <result column="course_id" property="courseId" jdbcType="INTEGER"/>
        <result column="dept_id" property="depId" jdbcType="INTEGER"/>
        <result column="teacher_id" property="teacherId" jdbcType="INTEGER"/>
        <result column="course_name" property="courseName" jdbcType="VARCHAR"/>
        <result column="course_desc" property="courseDesc" jdbcType="VARCHAR"/>
        <result column="course_type_code" property="courseTypeCode" jdbcType="INTEGER"/>
        <result column="course_status" property="courseStatus" jdbcType="INTEGER"/>
        <!-- association:  department

    private int depId;
    private String depName;
    private String depDesc;
    private int depCode;

        -->
        <association property="department" javaType="Department">
            <id column="dept_id" property="depId" jdbcType="INTEGER"/>
            <result column="dept_name" property="depName" jdbcType="VARCHAR"/>
            <result column="dept_desc" property="depDesc" jdbcType="VARCHAR"/>
            <result column="dept_code" property="depCode" jdbcType="INTEGER"/>
        </association>
        <!-- association:  teacher

    private int teacherId;
    private String teacherName;
    private String teacherOfficeAdd;


        -->
        <association property="teacher" javaType="Teacher">
            <id column="teacher_id" property="teacherId" jdbcType="INTEGER"/>
            <result column="teacher_name" property="teacherName" jdbcType="VARCHAR"/>
            <result column="teacher_office_add" property="teacherOfficeAdd" jdbcType="VARCHAR"/>
        </association>
    </resultMap>

    <sql id="Course_Column">
        course_name, course_desc, course_type_code, course_status, dept_id, teacher_id
    </sql>

    <select id="queryCourseById1" resultMap="QueryCourseResultMap" parameterType="map">
        SELECT
        *
        FROM
        course as c
        INNER JOIN teacher as t ON c.teacher_id = t.teacher_id
        INNER JOIN department as d ON c.dept_id = d.dept_id
        WHERE
        c.course_id = #{courseId}
    </select>

    <select id="queryCourseById2" resultType="Course" parameterType="map">
        SELECT
        c.course_id,
        c.dept_id "dep_id",
        c.teacher_id,
        c.course_name,
        c.course_desc,
        c.course_type_code,
        c.course_status,
        t.teacher_id "teacher.teacher_id",
        t.teacher_name "teacher.teacher_name",
        t.teacher_office_add "teacher.teacher_office_add",
        d.dept_id "department.dep_id",
        d.dept_name "department.dep_name",
        d.dept_desc "department.dep_desc",
        d.dept_code "department.dep_code"

        FROM
        course as c
        INNER JOIN teacher as t ON c.teacher_id = t.teacher_id
        INNER JOIN department as d ON c.dept_id = d.dept_id
        WHERE
        c.course_id = #{courseId}
    </select>

    <insert id="insertCourse" parameterType="Course">
        INSERT INTO
        course
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <include refid="Course_Column" />
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{courseName, jdbcType=VARCHAR},
            #{courseDesc, jdbcType=VARCHAR},
            #{courseTypeCode, jdbcType=INTEGER},
            #{courseStatus, jdbcType=INTEGER},
            #{depId, jdbcType=INTEGER},
            #{teacherId, jdbcType=INTEGER},
        </trim>
    </insert>

    <update id="updateCourse" parameterType="Course">
        UPDATE
        course as c
        SET
        course_name = #{courseName},
        course_desc = #{courseDesc},
        course_type_code = #{courseTypeCode},
        course_status = #{courseStatus},
        dept_id = #{depId},
        teacher_id = #{teacherId}
        WHERE c.course_id = #{courseId}
    </update>

    <delete id="deleteCourseIfExistsById" parameterType="map">
        DELETE FROM
        course
        WHERE course.course_id = #{courseId}
    </delete>

</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="com.hujian.dao.LearnerWithCourseMapper">

    <resultMap id="QueryCourseResultMap" type="Course">
        <result column="course_id" property="courseId" jdbcType="INTEGER"/>
        <result column="dept_id" property="depId" jdbcType="INTEGER"/>
        <result column="teacher_id" property="teacherId" jdbcType="INTEGER"/>
        <result column="course_name" property="courseName" jdbcType="VARCHAR"/>
        <result column="course_desc" property="courseDesc" jdbcType="VARCHAR"/>
        <result column="course_type_code" property="courseTypeCode" jdbcType="INTEGER"/>
        <result column="course_status" property="courseStatus" jdbcType="INTEGER"/>
        <association property="department" javaType="Department">
            <id column="dept_id" property="depId" jdbcType="INTEGER"/>
            <result column="dept_name" property="depName" jdbcType="VARCHAR"/>
            <result column="dept_desc" property="depDesc" jdbcType="VARCHAR"/>
            <result column="dept_code" property="depCode" jdbcType="INTEGER"/>
        </association>
        <association property="teacher" javaType="Teacher">
            <id column="teacher_id" property="teacherId" jdbcType="INTEGER"/>
            <result column="teacher_name" property="teacherName" jdbcType="VARCHAR"/>
            <result column="teacher_office_add" property="teacherOfficeAdd" jdbcType="VARCHAR"/>
        </association>
    </resultMap>

    <resultMap id="LearnerWithCourseResult" type="LearnerWithCourse">
        <id column="learner_id" property="learnerId" jdbcType="INTEGER"/>
        <collection property="courseList" ofType="Course" resultMap="QueryCourseResultMap"/>
    </resultMap>

    <resultMap id="LearnerWithCourseResultV2" type="LearnerWithCourse">
        <id column="learner_id" property="learnerId" jdbcType="INTEGER"/>
        <collection property="courseList" select="GetAllCoursesForLearner" column="learner_id"/>
    </resultMap>

    <!--
        ====================================================
        SQL:
        ====================================================
       select * from course as c
       inner join department as d on c.dept_id = d.dept_id
       inner join teacher as t on c.teacher_id = t.teacher_id
       where c.course_id in
       (select course_id from elective as e where e.learner_id = 100005);
        ====================================================
        Result:
        ====================================================

     -->
    <select id="GetAllCoursesForLearner" resultMap="QueryCourseResultMap" parameterType="java.lang.Integer">
        SELECT *
        FROM `course` as cs
        INNER JOIN `department` as d ON cs.dept_id = d.dept_id
        INNER JOIN `teacher` as t ON cs.teacher_id = t.teacher_id
        WHERE
        cs.course_id
        IN
        (SELECT course_id FROM `elective` as e WHERE e.learner_id = #{learner_id})
    </select>

    <!--
        ====================================================
        SQL:
        ====================================================
        select * from learner,
        course as c
        inner join department as d on c.dept_id = d.dept_id
        inner join teacher as t on c.teacher_id = t.teacher_id
        where learner.learner_id = 100004
        and
         c.cd
         in
         (select course_id from elective where elective.learner_id = 100004);
         ========================================================================================================
         Result:
         ========================================================================================================
         LearnerWithCourse(learnerId=100004,
         courseList=[
         Course(courseId=10001, depId=108, teacherId=1000, courseName=高能物理, courseDesc=关于高能物理的相关内容, courseTypeCode=2, courseStatus=1,
             department=Department(depId=108, depName=物理学院, depDesc=研究物理科学和工程的学院, depCode=1993002),
            teacher=Teacher(teacherId=1000, teacherName=胡建, teacherOfficeAdd=教师办公楼-F2-C-101)),
         Course(courseId=10004, depId=108, teacherId=1000, courseName=高等数学（二）, courseDesc=高等数学二, courseTypeCode=0, courseStatus=1,
            department=Department(depId=108, depName=数学科学学院, depDesc=专注于攻克数学难题的学院, depCode=1993003),
            teacher=Teacher(teacherId=1000, teacherName=胡建, teacherOfficeAdd=教师办公楼-F2-C-101)),
         Course(courseId=10005, depId=108, teacherId=1000, courseName=java语言程序设计, courseDesc=java语言基础知识讲解, courseTypeCode=3, courseStatus=1,
          department=Department(depId=108, depName=信息技术科学学院, depDesc=现代信息科学技术相关专业学院, depCode=1993004),
           teacher=Teacher(teacherId=1000, teacherName=胡建, teacherOfficeAdd=教师办公楼-F2-C-101)),
         Course(courseId=10006, depId=108, teacherId=1000, courseName=计算机体系结构, courseDesc=非常有价值的计算机专业课程, courseTypeCode=3, courseStatus=0,
          department=Department(depId=108, depName=信息技术科学学院, depDesc=现代信息科学技术相关专业学院, depCode=1993004),
          teacher=Teacher(teacherId=1000, teacherName=胡建, teacherOfficeAdd=教师办公楼-F2-C-101)),
         Course(courseId=10003, depId=108, teacherId=1002, courseName=高等数学（一）, courseDesc=高等数学一, courseTypeCode=0, courseStatus=1,
          department=Department(depId=108, depName=数学科学学院, depDesc=专注于攻克数学难题的学院, depCode=1993003),
           teacher=Teacher(teacherId=1002, teacherName=王安石, teacherOfficeAdd=教师办公楼-F7-V-808))])
           ========================================================================================================
    -->
    <select id="queryLearnersCourseByLearnerId1" resultMap="LearnerWithCourseResult" parameterType="map">
        SELECT
        *
        FROM
        `learner` as l,
         `course` as c
           INNER JOIN `department` as d ON c.dept_id = d.dept_id
           INNER JOIN `teacher` as t ON c.teacher_id = t.teacher_id
        WHERE
        l.`learner_id` = #{learnerId}
        AND
         c.`course_id` IN
         (SELECT course_id FROM `elective` as e WHERE e.`learner_id` = #{learnerId})
    </select>

    <select id="queryLearnersCourseByLearnerId2" resultMap="LearnerWithCourseResultV2" parameterType="map">
        SELECT
        learner_id
        FROM
        `learner` as l
        WHERE
        l.learner_id = #{learnerId}
    </select>

</mapper>

    相关文章

      网友评论

        本文标题：我的Mysql&Mybatis积累

        本文链接：https://www.haomeiwen.com/subject/pptneftx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        Spring技术

        Java技术

        mysql 知识库

        关于我们|服务条款|联系我们|我的Mysql&Mybatis积累|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！