mybatis學(xué)習(xí)筆記

1、第十六章 回顧SQL99中的連接查詢(xún) 1）內(nèi)連接 2）外連接 3）自連接 第十七章 回顧hibernate多表開(kāi)發(fā)1）一對(duì)一2）一對(duì)多3）多對(duì)多第十八章 mybatis一對(duì)一映射【學(xué)生與身份證】1）參見(jiàn)2）創(chuàng)建students.sql和cards.sql drop table students;drop table cards;create table cards(idint(5)primary key,num varchar(20);create table students(idint(5)primary key,name varchar(10),cid int(5),constraint

2、 cid_fk foreign key(cid) references cards(id);insert into cards(id,num) values(1,111);insert into students(id,name,cid) values(1,哈哈,1);3）創(chuàng)建Students.java和Card.java public class Card private Integer id;private String num;private Student student;public Card()public Integer getId() return id;public void

3、 setId(Integer id) this.id = id;public String getNum() return num;public void setNum(String num) this.num = num;public Student getStudent() return student;public void setStudent(Student student) this.student = student;public class Student private Integer id;private String name;private Card card;publ

4、ic Student()public Integer getId() return id;public void setId(Integer id) this.id = id;public String getName() return name;public void setName(String name) = name;public Card getCard() return card;public void setCard(Card card) this.card = card;4）創(chuàng)建StudentMapper.xml和CardMapper.xml CardMap

5、per.xml StudentMapper.xmlselect s.id,,c.id,c.numfrom students s inner join cards con s.cid = c.id and s.id = #id5）創(chuàng)建StudentCardDao.java public class StudentCardDao /* * 查詢(xún)1號(hào)【學(xué)生】 */public Student findById(int id) throws ExceptionSqlSession sqlSession = null;trysqlSession = MybatisUtil.getSqlSes

6、sion();return sqlSession.selectOne(studentNamespace.findById,id);catch(Exception e)e.printStackTrace();throw e;finallyMybatisUtil.closeSqlSession();public static void main(String args) throws ExceptionStudentCardDao dao = new StudentCardDao();Student student = dao.findById(1);System.out.println(stud

7、ent.getId()+:+student.getName();System.out.println(student.getCard().getId()+:+student.getCard().getNum();第十九章 mybatis一對(duì)多映射【班級(jí)與學(xué)生】1）參見(jiàn)2）創(chuàng)建grades.sql和students.sqldrop table students;drop table grades;create table grades(gidint(5)primary key,gname varchar(10) );create table students(sidint(5)primary k

8、ey,sname varchar(10), sgid int(5), constraint sgid_fk foreign key(sgid) references grades(gid);insert into grades(gid,gname) values(1,java);insert into students(sid,sname,sgid) values(1,哈哈,1);insert into students(sid,sname,sgid) values(2,呵呵,1);3）創(chuàng)建Grade.java和Student.sql/* * 班級(jí)（單方） * author AdminTC *

9、/public class Grade private Integer id;private String name;private List studentList = new ArrayList();public Grade()public Integer getId() return id;public void setId(Integer id) this.id = id;public String getName() return name;public void setName(String name) = name;public List getStudent

10、List() return studentList;public void setStudentList(List studentList) this.studentList = studentList;/* * 學(xué)生（多方） * author AdminTC */public class Student private Integer id;private String name;private Grade grade;public Student()public Integer getId() return id;public void setId(Integer id) this.id

11、= id;public String getName() return name;public void setName(String name) = name;public Grade getGrade() return grade;public void setGrade(Grade grade) this.grade = grade;4）創(chuàng)建GradeMapper.xml和StudentMapper.xml GradeMapper.xmlselect g.gid,g.gname,s.sid,s.snamefrom grades g,students swhere g.

12、gid = s.sgidand s.sname = #name StudentMapper.xmlselect s.sid,s.sname,g.gid,g.gnamefrom grades g,students swhere g.gid = s.sgidand g.gname = #name5）創(chuàng)建GradeStudentDao.javapublic class GradeStudentDao /* * 查詢(xún)java班級(jí)有哪些【學(xué)生】 */public List findAllByName(String name) throws ExceptionSqlSession sqlSession =

13、 null;trysqlSession = MybatisUtil.getSqlSession();return sqlSession.selectList(studentNamespace.findAllByName,name);catch(Exception e)e.printStackTrace();throw e;finallyMybatisUtil.closeSqlSession();/* * 查詢(xún)哈哈屬于哪個(gè)【班級(jí)】 */public Grade findGradeByName(String name) throws ExceptionSqlSession sqlSession =

14、 null;trysqlSession = MybatisUtil.getSqlSession();return sqlSession.selectOne(gradeNamespace.findGradeByName,name);catch(Exception e)e.printStackTrace();throw e;finallyMybatisUtil.closeSqlSession();public static void main(String args) throws ExceptionGradeStudentDao dao = new GradeStudentDao();List

15、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多對(duì)多映射【學(xué)生與課程】1）參見(jiàn)2）創(chuàng)建students.sql和courses.s

16、ql和middles.sqldrop table middles;drop table students;drop table courses;create table students(sid int(5) primary key,sname varchar(10);create table courses(cid int(5) primary key,cname varchar(10);create table middles(sid int(5),cid int(5),primary key(sid,cid);insert into students(sid,sname) values(

17、1,哈哈);insert into students(sid,sname) values(2,呵呵);insert into courses(cid,cname) values(1,java);insert into courses(cid,cname) values(2,net);insert into middles(sid,cid) values(1,1);insert into middles(sid,cid) values(1,2);insert into middles(sid,cid) values(2,1);insert into middles(sid,cid) values

18、(2,2);select * from students;select * from courses;select * from middles;3）創(chuàng)建Student.java和Course.java/* * 學(xué)生(多方) * author AdminTC */public class Student private Integer id;private String name;private List courseList = new ArrayList();public Student()public Integer getId() return id;public void setId

19、(Integer id) this.id = id;public String getName() return name;public void setName(String name) = name;public List getCourseList() return courseList;public void setCourseList(List courseList) this.courseList = courseList;/* * 課程(多方) * author AdminTC */public class Course private Integer id;

20、private String name;private List studentList = new ArrayList();public Course()public Integer getId() return id;public void setId(Integer id) this.id = id;public String getName() return name;public void setName(String name) = name;public List getStudentList() return studentList;public void

21、setStudentList(List studentList) this.studentList = studentList;4）創(chuàng)建StudentMapper.xml和CourseMapper.xml StudentMapper.xmlselect s.sid,s.snamefrom students s,middles m,courses cwhere s.sid = m.sid and m.cid = c.cidand ame = #name CourseMapper.xmlselect c.cid,amefrom students s,middles m,courses cwhere

22、 s.sid = m.sid and m.cid = c.cidand s.sname = #name5）創(chuàng)建students.sql和courses.sql和middles.sqlpublic class StudentCourseDao /* * 查詢(xún)哈哈選學(xué)的【課程】 */public List findCourseByName(String name) throws ExceptionSqlSession sqlSession = null;trysqlSession = MybatisUtil.getSqlSession();return sqlSession.selectList(

23、courseNamespace.findCourseByName,name);catch(Exception e)e.printStackTrace();throw e;finallyMybatisUtil.closeSqlSession();/* * 查詢(xún)java課程有哪些【學(xué)生】 */public List findStudentByName(String name) throws ExceptionSqlSession sqlSession = null;trysqlSession = MybatisUtil.getSqlSession();return sqlSession.selec

24、tList(studentNamespace.findStudentByName,name);catch(Exception e)e.printStackTrace();throw e;finallyMybatisUtil.closeSqlSession();public static void main(String args) throws ExceptionStudentCourseDao dao = new StudentCourseDao();/List courseList = dao.findCourseByName(哈哈);/for(Course c : courseList)

25、/System.out.println(c.getId()+:+c.getName();/List studentList = dao.findStudentByName(java);for(Student s : studentList)System.out.println(s.getId()+:+s.getName(); 第二十一章 spring + mybatis + mysql/oracle開(kāi)發(fā)1）創(chuàng)建一個(gè)spring-mybatis-mysql這么一個(gè)javaweb或java工程2）導(dǎo)入spring-ioc，spring-aop，spring-transaction，mybatis，

26、c3p0，mysql/oracle相關(guān)的jar包和spring整合mybatis的jar包3）創(chuàng)建students.sql-mysqlcreate table students( sid int(5) primary key, sname varchar(10), ssal double(8,2);4）創(chuàng)建Student.java/* * 學(xué)生 * author AdminTC */public class Student private Integer id;/編號(hào)private String name;/姓名private Double sal;/薪水public Student()pub

27、lic Student(Integer id, String name, Double sal) this.id = id; = name;this.sal = sal;public Integer getId() return id;public void setId(Integer id) this.id = id;public String getName() return name;public void setName(String name) = name;public Double getSal() return sal;public voi

28、d setSal(Double sal) this.sal = sal;5）創(chuàng)建StudentMapper.xmlinsert into students(sid,sname,ssal) values(#id,#name,#sal)6）創(chuàng)建StudentDao.javapublic class StudentDao private SqlSessionFactory sqlSessionFactory;public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) this.sqlSessionFactory = sq

29、lSessionFactory;public void insert(Student student)SqlSession sqlSession = sqlSessionFactory.openSession();sqlSession.insert(studentNamespace.insert,student);/int i = 10/0;public static void main(String args) ApplicationContext ac = new ClassPathXmlApplicationContext(new Stringspring.xml);StudentDao

30、 studentDao = (StudentDao) ac.getBean(studentDaoID);studentDao.insert(new Student(1,哈哈,7000D);7）在src目錄下創(chuàng)建mybatis.xml8）在src目錄下創(chuàng)建spring.xml 第二十二章 jsp/js/jquery/easyui/json + springmvc + spring + mybatis + mysql/oracle開(kāi)發(fā)1） 員工管理系統(tǒng)-增加員工第二十三章 jdbc訪問(wèn)oracle存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)1）寫(xiě)一個(gè)計(jì)算個(gè)人所得稅的應(yīng)用-定義過(guò)程create or replace proc

31、edure get_rax(salary in number,rax out number)as -需要交稅的錢(qián) 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 := b

32、al * 0.3 - 2755; elsif bal=80000 then rax := bal * 0.35 - 5505; else rax := bal * 0.45 - 13505; end if;end;/-調(diào)用過(guò)程declare -交稅 rax number; salary number := &salary;begin get_rax(salary,rax); dbms_output.put_line(salary|元工資需要交|rax|元稅);end;/ /Java調(diào)用過(guò)程public class TestCallOracleProc public static void main(String args) throws ExceptionString 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(10