mybatis入门到精通02

mybatis入门到精通02

第十六章 回顾SQL99中的连接查询

1）内连接

2）外连接

3）自连接

 

 

第十七章 回顾hibernate多表开发

1）一对一

2）一对多

3）多对多

 

 

第十八章 mybatis一对一映射【学生与身份证】

1）参见<<一对一图示.JPG>>

2）创建students.sql和cards.sql

droptable students;

droptable cards;

createtable cards(

       id    int(5)     primarykey,

       numvarchar(20)

);

createtable students(

       id    int(5)     primarykey,

       namevarchar(10),

       cid int(5),

       constraint cid_fkforeignkey(cid)references cards(id)

);

insertinto cards(id,num)values(1,'111');

insertinto students(id,name,cid)values(1,'哈哈',1);

3）创建Students.java和Card.java

publicclass Card {

    private Integerid;

    private Stringnum;

    private Studentstudent;

    public Card(){}

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getNum() {

       returnnum;

    }

    publicvoid setNum(String num) {

       this.num = num;

    }

    public Student getStudent() {

       returnstudent;

    }

    publicvoid setStudent(Student student) {

       this.student = student;

    }

}

publicclass Student {

    private Integerid;

    private Stringname;

    private Cardcard;

    public Student(){}

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getName() {

       returnname;

    }

    publicvoid setName(String name) {

       this.name = name;

    }

    public Card getCard() {

       returncard;

    }

    publicvoid setCard(Card card) {

       this.card = card;

    }

}

4）创建StudentMapper.xml和CardMapper.xml

   CardMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="cardNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.one2one.Card"id="cardMap">

       <idproperty="id"column="id"/>

       <resultproperty="num"column="num"/>

    </resultMap>

</mapper>

   StudentMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="studentNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.one2one.Student"id="studentMap">

       <idproperty="id"column="id"/>

       <resultproperty="name"column="name"/>

       <associationproperty="card"resultMap="cardNamespace.cardMap"/>

    </resultMap>

    <selectid="findById"parameterType="int"resultMap="studentMap">

       select s.id,s.name,c.id,c.num

       from students s inner join cards c

       on s.cid = c.id

       and s.id = #{id}

    </select>

</mapper>

5）创建StudentCardDao.java

publicclass StudentCardDao {

    /**

     *查询1号【学生】

     */

    public Student findById(int id)throws Exception{

       SqlSession sqlSession = null;

       try{

           sqlSession = MybatisUtil.getSqlSession();

           return sqlSession.selectOne("studentNamespace.findById",id);

       }catch(Exception e){

           e.printStackTrace();

           throw e;

       }finally{

           MybatisUtil.closeSqlSession();

       }

    }

    publicstaticvoid main(String[] args)throws Exception{

       StudentCardDao dao = new StudentCardDao();

       Student student = dao.findById(1);

       System.out.println(student.getId()+":"+student.getName());

        System.out.println(student.getCard().getId()+":"+student.getCard().getNum());

    }

}

 

 

第十九章 mybatis一对多映射【班级与学生】

1）参见<<一对多图示.JPG>>

2）创建grades.sql和students.sql

droptable students;

droptable grades;

createtable grades(

       gid  int(5)       primarykey,

       gname varchar(10)

 );

createtable students(

       sid   int(5)       primarykey,

       sname varchar(10),

    sgid int(5),

    constraint sgid_fkforeignkey(sgid)references grades(gid)

);

insertinto grades(gid,gname)values(1,'java');

insertinto students(sid,sname,sgid)values(1,'哈哈',1);

insertinto students(sid,sname,sgid)values(2,'呵呵',1);

3）创建Grade.java和Student.sql

/**

 *班级（单方）

 *@authorAdminTC

 */

publicclass Grade {

    private Integerid;

    private Stringname;

    private List<Student>studentList =new ArrayList<Student>();

    public Grade(){}

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getName() {

       returnname;

    }

    publicvoid setName(String name) {

       this.name = name;

    }

    public List<Student> getStudentList() {

       returnstudentList;

    }

    publicvoid setStudentList(List<Student> studentList) {

       this.studentList = studentList;

    }

}

/**

 *学生（多方）

 *@authorAdminTC

 */

publicclass Student {

    private Integerid;

    private Stringname;

    private Gradegrade;

    public Student(){}

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getName() {

       returnname;

    }

    publicvoid setName(String name) {

       this.name = name;

    }

    public Grade getGrade() {

       returngrade;

    }

    publicvoid setGrade(Grade grade) {

       this.grade = grade;

    }

}

4）创建GradeMapper.xml和StudentMapper.xml

  GradeMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="gradeNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.one2many.Grade"id="gradeMap">

       <idproperty="id"column="gid"/>

       <resultproperty="name"column="gname"/>

       <collectionproperty="studentList"resultMap="studentNamespace.studentMap"/>

    </resultMap>

    <selectid="findGradeByName"parameterType="string"resultMap="gradeMap">

       select g.gid,g.gname,s.sid,s.sname

       from grades g,students s

       where g.gid = s.sgid

       and s.sname = #{name}

    </select>

</mapper>

   StudentMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="studentNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.one2many.Student"id="studentMap">

       <idproperty="id"column="sid"/>

       <resultproperty="name"column="sname"/>

       <associationproperty="grade"resultMap="gradeNamespace.gradeMap"/>

    </resultMap>

    <selectid="findAllByName"parameterType="string"resultMap="studentMap">

       select s.sid,s.sname,g.gid,g.gname

       from grades g,students s

       where g.gid = s.sgid

       and g.gname = #{name}

    </select>

</mapper>

5）创建GradeStudentDao.java

publicclass GradeStudentDao {

    /**

     *查询java班级有哪些【学生】

     */

    public List<Student> findAllByName(String name)throws Exception{

       SqlSession sqlSession = null;

       try{

           sqlSession = MybatisUtil.getSqlSession();

           return sqlSession.selectList("studentNamespace.findAllByName",name);

       }catch(Exception e){

           e.printStackTrace();

           throw e;

       }finally{

           MybatisUtil.closeSqlSession();

       }

    }

    /**

     *查询哈哈属于哪个【班级】

     */

    public Grade findGradeByName(String name)throws Exception{

       SqlSession sqlSession = null;

       try{

           sqlSession = MybatisUtil.getSqlSession();

           return sqlSession.selectOne("gradeNamespace.findGradeByName",name);

       }catch(Exception e){

           e.printStackTrace();

           throw e;

       }finally{

           MybatisUtil.closeSqlSession();

       }

    }

    publicstaticvoid main(String[] args)throws Exception{

       GradeStudentDao dao = new GradeStudentDao();

       List<Student> studentList = dao.findAllByName("java");

       for(Student s : studentList){

    System.out.println(s.getId()+":"+s.getName()+":"+s.getGrade().getId()+":"+s.getGrade().getName());

       }

       Grade grade = dao.findGradeByName("哈哈");

       System.out.println(grade.getId()+":"+grade.getName());

    }

}

 

 

第二十章 mybatis多对多映射【学生与课程】

1）参见<<多对多图示.JPG>>

2）创建students.sql和courses.sql和middles.sql

droptable middles;

droptable students;

droptable courses;

createtable students(

       sid int(5) primarykey,

       sname varchar(10)

);

createtable courses(

       cid int(5) primarykey,

       cname varchar(10)

);

createtable middles(

       sid int(5),

       cid int(5),

       primarykey(sid,cid)      

);

insertinto students(sid,sname)values(1,'哈哈');

insertinto students(sid,sname)values(2,'呵呵');

insertinto courses(cid,cname)values(1,'java');

insertinto courses(cid,cname)values(2,'net');

insertinto middles(sid,cid)values(1,1);

insertinto middles(sid,cid)values(1,2);

insertinto middles(sid,cid)values(2,1);

insertinto middles(sid,cid)values(2,2);

select *from students;

select *from courses;

select *from middles;

 

3）创建Student.java和Course.java

/**

 *学生(多方)

 *@authorAdminTC

 */

publicclass Student {

    private Integerid;

    private Stringname;

    private List<Course>courseList = new ArrayList<Course>();

    public Student(){}

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getName() {

        returnname;

    }

    publicvoid setName(String name) {

       this.name = name;

    }

    public List<Course> getCourseList() {

       returncourseList;

    }

    publicvoid setCourseList(List<Course> courseList) {

       this.courseList = courseList;

    }

}

/**

 *课程(多方)

 *@authorAdminTC

 */

publicclass Course {

    private Integerid;

    private Stringname;

    private List<Student>studentList =new ArrayList<Student>();

    public Course(){}

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getName() {

       returnname;

    }

    publicvoid setName(String name) {

       this.name = name;

    }

    public List<Student> getStudentList() {

       returnstudentList;

    }

    publicvoid setStudentList(List<Student> studentList) {

       this.studentList = studentList;

    }

}

4）创建StudentMapper.xml和CourseMapper.xml

  StudentMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="studentNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.many2many.Student"id="studentMap">

       <idproperty="id"column="sid"/>

       <resultproperty="name"column="sname"/>

    </resultMap>

    <selectid="findStudentByName"parameterType="string"resultMap="studentMap">

       select s.sid,s.sname

       from students s,middles m,courses c

       where s.sid = m.sid

       and m.cid = c.cid

       and c.cname = #{name}

    </select>

</mapper>

  CourseMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="courseNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.many2many.Course"id="courseMap">

       <idproperty="id"column="cid"/>

       <resultproperty="name"column="cname"/>

    </resultMap>

    <selectid="findCourseByName"parameterType="string"resultMap="courseMap">

       select c.cid,c.cname

       from students s,middles m,courses c

       where s.sid = m.sid

       and m.cid = c.cid

       and s.sname = #{name}

    </select>

</mapper>

5）创建students.sql和courses.sql和middles.sql

publicclass StudentCourseDao {

    /**

     *查询哈哈选学的【课程】

     */

    public List<Course> findCourseByName(String name)throws Exception{

       SqlSession sqlSession = null;

       try{

           sqlSession = MybatisUtil.getSqlSession();

           return sqlSession.selectList("courseNamespace.findCourseByName",name);

       }catch(Exception e){

           e.printStackTrace();

           throw e;

       }finally{

           MybatisUtil.closeSqlSession();

       }

    }

    /**

     *查询java课程有哪些【学生】

     */

    public List<Student> findStudentByName(String name)throws Exception{

       SqlSession sqlSession = null;

       try{

           sqlSession = MybatisUtil.getSqlSession();

           return sqlSession.selectList("studentNamespace.findStudentByName",name);

       }catch(Exception e){

           e.printStackTrace();

           throw e;

       }finally{

           MybatisUtil.closeSqlSession();

       }

    }

    publicstaticvoid main(String[] args)throws Exception{

       StudentCourseDao dao = new StudentCourseDao();

       //List<Course> courseList = dao.findCourseByName("哈哈");

       //for(Course c : courseList){

       // System.out.println(c.getId()+":"+c.getName());

       //}

       List<Student> studentList = dao.findStudentByName("java");

       for(Student s : studentList){

           System.out.println(s.getId()+":"+s.getName());

       }

    }  

}

 

 

第二十一章 spring + mybatis + mysql/oracle开发

1）创建一个spring-mybatis-mysql这么一个javaweb或java工程

2）导入spring-ioc，spring-aop，spring-transaction，mybatis，c3p0，mysql/oracle相关的jar包和spring整合mybatis的jar包

3）创建students.sql

--mysql

createtable students(

   sid  int(5) primarykey,

   sname varchar(10),

   ssal double(8,2)

);

 

4）创建Student.java

/**

 *学生

 *@authorAdminTC

 */

publicclass Student {

    private Integerid;//编号

    private Stringname;//姓名

    private Doublesal;//薪水

    public Student(){}

    public Student(Integer id, String name, Double sal) {

       this.id = id;

       this.name = name;

       this.sal = sal;

    }

    public Integer getId() {

       returnid;

    }

    publicvoid setId(Integer id) {

       this.id = id;

    }

    public String getName() {

       returnname;

    }

    publicvoid setName(String name) {

       this.name = name;

    }

    public Double getSal() {

       returnsal;

    }

    publicvoid setSal(Double sal) {

       this.sal = sal;

    }

}

5）创建StudentMapper.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mappernamespace="studentNamespace">

    <resultMaptype="cn.itcast.javaee.mybatis.entity.Student"id="studentMap">

       <idproperty="id"column="sid"/>

       <resultproperty="name"column="sname"/>

       <resultproperty="sal"column="ssal"/>

    </resultMap>

    <insertid="insert"parameterType="cn.itcast.javaee.mybatis.entity.Student">

       insert into students(sid,sname,ssal) values(#{id},#{name},#{sal})

    </insert>

</mapper>

6）创建StudentDao.java

publicclass StudentDao {

    private SqlSessionFactorysqlSessionFactory;

    publicvoid setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {

       this.sqlSessionFactory = sqlSessionFactory;

    }

    publicvoid insert(Student student){

       SqlSession sqlSession = sqlSessionFactory.openSession();

       sqlSession.insert("studentNamespace.insert",student);

       //int i = 10/0;

    }

    publicstaticvoid main(String[] args) {

       ApplicationContext ac = new ClassPathXmlApplicationContext(new String[]{"spring.xml"});

       StudentDao studentDao = (StudentDao) ac.getBean("studentDaoID");

       studentDao.insert(new Student(1,"哈哈",7000D));

    }

}

7）在src目录下创建mybatis.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEconfiguration PUBLIC"-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <mappers>

       <mapperresource="cn/itcast/javaee/mybatis/entity/StudentMapper.xml"/>

    </mappers>

</configuration>

8）在src目录下创建spring.xml

<?xmlversion="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"

      xmlns:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"

      xmlns:mvc="http://www.springframework.org/schema/mvc"

      

      xsi:schemaLocation="

   

      http://www.springframework.org/schema/beans

      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd

     

      http://www.springframework.org/schema/context

      http://www.springframework.org/schema/context/spring-context-3.0.xsd

     

      http://www.springframework.org/schema/aop

      http://www.springframework.org/schema/aop/spring-aop-3.0.xsd

     

      http://www.springframework.org/schema/tx

      http://www.springframework.org/schema/tx/spring-tx-3.0.xsd

   

      http://www.springframework.org/schema/mvc

      http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd

       

      ">

 

      <!--配置C3P0连接池(即管理数据库连接) -->

      <beanid="comboPooledDataSourceID"class="com.mchange.v2.c3p0.ComboPooledDataSource">

           <property name="driverClass"value="com.mysql.jdbc.Driver"/>

           <property name="jdbcUrl"value="jdbc:mysql://127.0.0.1:3306/mybatis"/>

           <property name="user"value="root"/>

           <property name="password"value="root"/>

      </bean>

     

     

      <!--配置SqlSessionFactoryBean(即替代MyBatisUtil工具类的作用) -->

      <beanid="sqlSessionFactoryBeanID"class="org.mybatis.spring.SqlSessionFactoryBean">

           <property name="dataSource"ref="comboPooledDataSourceID"/>

           <property name="configLocation"value="classpath:mybatis.xml"/>

      </bean>

     

     

      <!--配置事务管理器(即使用JDBC事务管理器) -->

      <beanid="dataSourceTransactionManagerID"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

           <property name="dataSource"ref="comboPooledDataSourceID"/>

      </bean>

     

     

      <!--配置事务通知(即哪些方法需要事务) -->

      <tx:adviceid="tx"transaction-manager="dataSourceTransactionManagerID">

           <tx:attributes>

               <tx:method name="*"propagation="REQUIRED"/>

           </tx:attributes>

      </tx:advice>    

 

     

      <!--配置事务切面(即哪些包中的类需要事务通知) -->

      <aop:config>

           <aop:pointcut id="pointcut"expression="execution(* cn.itcast.javaee.mybatis.dao.*.*(..))"/>

           <aop:advisor advice-ref="tx"pointcut-ref="pointcut"/>

      </aop:config>

     

     

      <!--配置StudentDao类 -->

      <beanid="studentDaoID"class="cn.itcast.javaee.mybatis.dao.StudentDao">

           <property name="sqlSessionFactory"ref="sqlSessionFactoryBeanID"/>

      </bean>

     

</beans>

 

 

第二十二章 jsp/js/jquery/easyui/json +@springmvc + spring + mybatis   + mysql/oracle开发

1）  员工管理系统--增加员工

 

 

第二十三章 jdbc访问oracle存储过程和存储函数

1）写一个计算个人所得税的应用

--定义过程

create or replace procedure get_rax(salary in number,rax out number)

as

    --需要交税的钱

    bal number;

begin

    bal := salary - 3500;

    if bal<=1500 then

       rax := bal * 0.03 - 0;

    elsif bal<=4500 then

       rax := bal * 0.1 - 105;

    elsif bal<=9000 then

       rax := bal * 0.2 - 555;

    elsif bal<=35000 then

       rax := bal * 0.25 - 1005;

    elsif bal<=55000 then

       rax := bal * 0.3 - 2755;

    elsif bal<=80000 then

       rax := bal * 0.35 - 5505;

    else

       rax := bal * 0.45 - 13505;

    end if;

end;

/

 

--调用过程

declare

   --交税

   rax number;

   salary number := &salary;

begin

   get_rax(salary,rax);

   dbms_output.put_line(salary||'元工资需要交'||rax||'元税');

end;

/

    

//Java调用过程

publicclass TestCallOracleProc {

    publicstaticvoid main(String[] args)throws Exception{

       String sql = "{call get_rax(?,?)}";

       Connection conn = JdbcUtil.getConnection();

       CallableStatement cstmt = conn.prepareCall(sql);

       cstmt.setInt(1,10000);

       cstmt.registerOutParameter(2,Types.INTEGER);

       cstmt.execute();

       Integer rax = cstmt.getInt(2);

       System.out.println("10000元需要交" + rax + "元税");

       JdbcUtil.close(cstmt);

       JdbcUtil.close(conn);

    }

}

 

 

      查询7788号员工的的姓名，职位，月薪

--定义函数

create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2,psal out number)

return varchar2

as

    pename emp.ename%type;

begin

    select ename,job,sal into pename,pjob,psal from emp where empno = pempno;

    return pename;

end;

/

 

--调用函数

declare

    pename emp.ename%type;

    pjob   emp.job%type;

    psal   emp.sal%type;

begin

    pename := findEmpNameAndJobAndSal(7788,pjob,psal);

    dbms_output.put_line('7788'||'--'||pename||'--'||pjob||'--'||psal);

end;

/

    

//Java调用函数

publicclass TestCallOracleFunc {

    publicstaticvoid main(String[] args)throws Exception{

        String sql = "{?=call findEmpNameAndJobAndSal(?,?,?)}";

        Connection conn = JdbcUtil.getConnection();

        CallableStatement cstmt = conn.prepareCall(sql);

        cstmt.registerOutParameter(1,Types.VARCHAR);

        cstmt.setInt(2,7788);

        cstmt.registerOutParameter(3,Types.VARCHAR);

        cstmt.registerOutParameter(4,Types.INTEGER);

        cstmt.execute();

        String ename = cstmt.getString(1);

        String job = cstmt.getString(3);

        Integer sal = cstmt.getInt(4);

        System.out.println(ename+":"+job+":"+sal);

        JdbcUtil.close(cstmt);

        JdbcUtil.close(conn);

    }

}