ibatis编程实例-实现基本数据库操作

实例实现了6个基本功能：

1.向数据库student表插入一条数据
2.删除student表的所有数据
3.删除student表指定ID的数据
4. 更新student表指定ID的数据
5.查询student表的所有数据
6.查询student表的指定ID数据

使用的是oracle数据库

你也可以去下载我上传的资源“ibatis实例程序”，里面包含了3个ibatis的jar包、1个oracle的驱动包和所有的程序代码。。

我的编程实例从创建SqlMapConfig.xml文件开始

第一步：编写SqlMapConfig.xml文件

1. <?xml version="1.0" encoding="UTF-8" ?>
2. <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
3. <sqlMapConfig>
4.
5. <properties resource="SqlMapConfig.properties" />
6.
7. <transactionManager type="JDBC">
8. <dataSource type="SIMPLE">
9. <property name="JDBC.Driver" value="${driver}" />
10. <property name="JDBC.ConnectionURL" value="${url}" />
11. <property name="JDBC.Username" value="${username}" />
12. <property name="JDBC.Password" value="${password}" />
13.
14. </dataSource>
15. </transactionManager>
16.
17. <sqlMap resource="sqlMap_student.xml" />
18.
19. </sqlMapConfig>


1. <?xml version="1.0" encoding="UTF-8" ?>
2. <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
3. <sqlMapConfig>
4.
5. <properties resource="SqlMapConfig.properties" />
6.
7. <transactionManager type="JDBC">
8. <dataSource type="SIMPLE">
9. <property name="JDBC.Driver" value="${driver}" />
10. <property name="JDBC.ConnectionURL" value="${url}" />
11. <property name="JDBC.Username" value="${username}" />
12. <property name="JDBC.Password" value="${password}" />
13.
14. </dataSource>
15. </transactionManager>
16.
17. <sqlMap resource="sqlMap_student.xml" />
18.
19. </sqlMapConfig>

SqlMapConfig.xml文件包含另外2个

资源文件SqlMapConfig.properties(SqlMapConfig.xml文件中引用这里定义的4个值，一般编程会把各种数据库的连接方法都写于此，以便下次调用各种不同数据库之间的使用)

1. ##### Oracle #####
2.
3. driver=oracle.jdbc.driver.OracleDriver
4. url=jdbc:oracle:thin:@localhost:1521:name
5. usernameusername=username
6. passwordpassword=password
7. ##### Oracle #####
8.
9. driver=oracle.jdbc.driver.OracleDriver
10. url=jdbc:oracle:thin:@localhost:1521:name
11. username=username
12. password=password

资源文件sqlMap_student.xml(具体的sql文执行语句)

1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
3.
4. <sqlMap namespace="Test">
5.
6. <statement id="insert_student"
7. parameterClass="zh2208.dto.StudentDto">
8. insert into student(
9. id,name,age,sex,address) values(
10. #id#,#name#,#age#,#sex#,#address# )
11. </statement>
12.
13. <statement id="delete_all_student">
14. delete from student
15. </statement>
16.
17. <statement id="deleteByID_student"
18. parameterClass="zh2208.dto.StudentDto">
19. delete from student where
20. id = #id#
21. </statement>
22.
23. <statement id="updataStudent_test"
24. parameterClass="zh2208.dto.StudentDto">
25. update student set
26. name=#name#,sex=#sex#,age=#age#,address=#address#
27. where id = #id#
28. </statement>
29.
30. <statement id="select_all_student"
31. resultClass="zh2208.dto.StudentDto">
32. select * from student order by id
33. </statement>
34.
35. <statement id="selectByID_student"
36. parameterClass="zh2208.dto.StudentDto"
37. resultClass="zh2208.dto.StudentDto">
38. select * from student
39. where id = #id#
40. order by id
41. </statement>
42. </sqlMap>


1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
3.
4. <sqlMap namespace="Test">
5.
6. <statement id="insert_student"
7. parameterClass="zh2208.dto.StudentDto">
8. insert into student(
9. id,name,age,sex,address) values(
10. #id#,#name#,#age#,#sex#,#address# )
11. </statement>
12.
13. <statement id="delete_all_student">
14. delete from student
15. </statement>
16.
17. <statement id="deleteByID_student"
18. parameterClass="zh2208.dto.StudentDto">
19. delete from student where
20. id = #id#
21. </statement>
22.
23. <statement id="updataStudent_test"
24. parameterClass="zh2208.dto.StudentDto">
25. update student set
26. name=#name#,sex=#sex#,age=#age#,address=#address#
27. where id = #id#
28. </statement>
29.
30. <statement id="select_all_student"
31. resultClass="zh2208.dto.StudentDto">
32. select * from student order by id
33. </statement>
34.
35. <statement id="selectByID_student"
36. parameterClass="zh2208.dto.StudentDto"
37. resultClass="zh2208.dto.StudentDto">
38. select * from student
39. where id = #id#
40. order by id
41. </statement>
42. </sqlMap>

以上步骤完成了所有的xml文件的编写。



第二步编写dto文件

我用到了student表，里面包含了id，name，sex，age，address 5个字段

2.1 dto文件的内容为

1. package zh2208.dto;
2.
3. /**
4. * @author zh2208
5. *
6. */
7. public class StudentDto {
8.
9. //学生ID
10. private int id = 0;
11. //学生姓名
12. private String name = "";
13. //学生性别
14. private String sex = "";
15. //学生年龄
16. private int age = 0;
17. //学生地址
18. private String address = "";
19.
20. public String getAddress() {
21. return address;
22. }
23. public void setAddress(String address) {
24. this.address = address;
25. }
26. public int getAge() {
27. return age;
28. }
29. public void setAge(int age) {
30. this.age = age;
31. }
32. public int getId() {
33. return id;
34. }
35. public void setId(int id) {
36. this.id = id;
37. }
38. public String getName() {
39. return name;
40. }
41. public void setName(String name) {
42. this.name = name;
43. }
44. public String getSex() {
45. return sex;
46. }
47. public void setSex(String sex) {
48. this.sex = sex;
49. }
50.
51. }
52. package zh2208.dto;
53.
54. /**
55. * @author zh2208
56. *
57. */
58. public class StudentDto {
59.
60. //学生ID
61. private int id = 0;
62. //学生姓名
63. private String name = "";
64. //学生性别
65. private String sex = "";
66. //学生年龄
67. private int age = 0;
68. //学生地址
69. private String address = "";
70.
71. public String getAddress() {
72. return address;
73. }
74. public void setAddress(String address) {
75. this.address = address;
76. }
77. public int getAge() {
78. return age;
79. }
80. public void setAge(int age) {
81. this.age = age;
82. }
83. public int getId() {
84. return id;
85. }
86. public void setId(int id) {
87. this.id = id;
88. }
89. public String getName() {
90. return name;
91. }
92. public void setName(String name) {
93. this.name = name;
94. }
95. public String getSex() {
96. return sex;
97. }
98. public void setSex(String sex) {
99. this.sex = sex;
100. }
101.
102. }




2.2 接口文件的内容为

1. package zh2208.dao;
2.
3. import java.util.ArrayList;
4. import com.ibatis.sqlmap.client.SqlMapClient;
5.
6. import zh2208.dto.*;
7. /**
8. * @author zh2208
9. *
10. */
11. public interface StudentDao {
12.
13. //添加student表的数据
14. public void addStudent(SqlMapClient sqlMap,StudentDto studentdto);
15. //删除student表的数据
16. public void delStudent(SqlMapClient sqlMap);
17. //删除student表的指定ID数据
18. public void delStudentByID(SqlMapClient sqlMap,StudentDto studentdto);
19. //更新student表的数据
20. public void updataStudent(SqlMapClient sqlMap,StudentDto studentdto);
21. //查询student表的所有数据
22. public ArrayList selectStudent(SqlMapClient sqlMap);
23. //查询student表的指定ID数据
24. public StudentDto selectStudentByID(SqlMapClient sqlMap,StudentDto studentdto);
25.
26. }


1. package zh2208.dao;
2.
3. import java.util.ArrayList;
4. import com.ibatis.sqlmap.client.SqlMapClient;
5.
6. import zh2208.dto.*;
7. /**
8. * @author zh2208
9. *
10. */
11. public interface StudentDao {
12.
13. //添加student表的数据
14. public void addStudent(SqlMapClient sqlMap,StudentDto studentdto);
15. //删除student表的数据
16. public void delStudent(SqlMapClient sqlMap);
17. //删除student表的指定 ID数据
18. public void delStudentByID(SqlMapClient sqlMap,StudentDto studentdto);
19. //更新student表的数据
20. public void updataStudent(SqlMapClient sqlMap,StudentDto studentdto);
21. //查询student表的所有数据
22. public ArrayList selectStudent(SqlMapClient sqlMap);
23. //查询student表的指定 ID数据
24. public StudentDto selectStudentByID(SqlMapClient sqlMap,StudentDto studentdto);
25.
26. }




2.3 实现接口的内容为

1. package zh2208.impl;
2.
3. import java.sql.SQLException;
4. import java.util.ArrayList;
5.
6. import com.ibatis.sqlmap.client.SqlMapClient;
7.
8. import zh2208.dao.StudentDao;
9. import zh2208.dto.StudentDto;
10.
11. /**
12. * @author zh2208
13. *
14. */
15. public class StudentImpl implements StudentDao {
16.
17. //添加student表的数据
18. public void addStudent(SqlMapClient sqlMap, StudentDto studentdto) {
19.
20. try {
21. sqlMap.insert("insert_student", studentdto);
22. } catch (SQLException e) {
23.
24. e.printStackTrace();
25. }
26. }
27.
28. //删除student表的数据
29. public void delStudent(SqlMapClient sqlMap) {
30.
31. try {
32. sqlMap.delete("delete_all_student", null);
33. } catch (SQLException e) {
34.
35. e.printStackTrace();
36. }
37. }
38.
39. //删除student表的指定ID数据
40. public void delStudentByID(SqlMapClient sqlMap, StudentDto studentdto) {
41.
42. try {
43. sqlMap.delete("deleteByID_student",studentdto );
44. } catch (SQLException e) {
45.
46. e.printStackTrace();
47. }
48. }
49.
50. //更新student表的数据
51. public void updataStudent(SqlMapClient sqlMap, StudentDto studentdto) {
52.
53. try {
54. sqlMap.update("updataStudent_test",studentdto );
55. } catch (SQLException e) {
56.
57. e.printStackTrace();
58. }
59. }
60.
61. //查询student表的所有数据
62. public ArrayList selectStudent(SqlMapClient sqlMap) {
63.
64. //保存查询结果
65. ArrayList rsList = new ArrayList();
66.
67. try {
68. rsList = (ArrayList)sqlMap.queryForList("select_all_student","");
69. } catch (SQLException e) {
70.
71. e.printStackTrace();
72. }
73. return rsList;
74. }
75.
76. //查询student表的指定 ID数据
77. public StudentDto selectStudentByID(SqlMapClient sqlMap, StudentDto studentdto) {
78.
79. //返回后保存在 info中
80. StudentDto info = new StudentDto();
81. try {
82. info = (StudentDto)sqlMap.queryForObject("selectByID_student", studentdto);
83. } catch (SQLException e) {
84.
85. e.printStackTrace();
86. }
87. return info;
88. }
89.
90.
91.
92. }



1. package zh2208.impl;
2.
3. import java.sql.SQLException;
4. import java.util.ArrayList;
5.
6. import com.ibatis.sqlmap.client.SqlMapClient;
7.
8. import zh2208.dao.StudentDao;
9. import zh2208.dto.StudentDto;
10.
11. /**
12. * @author zh2208
13. *
14. */
15. public class StudentImpl implements StudentDao {
16.
17. //添加student表的数据
18. public void addStudent(SqlMapClient sqlMap, StudentDto studentdto) {
19.
20. try {
21. sqlMap.insert("insert_student", studentdto);
22. } catch (SQLException e) {
23.
24. e.printStackTrace();
25. }
26. }
27.
28. //删除student表的数据
29. public void delStudent(SqlMapClient sqlMap) {
30.
31. try {
32. sqlMap.delete("delete_all_student", null);
33. } catch (SQLException e) {
34.
35. e.printStackTrace();
36. }
37. }
38.
39. //删除student表的指定 ID数据
40. public void delStudentByID(SqlMapClient sqlMap, StudentDto studentdto) {
41.
42. try {
43. sqlMap.delete("deleteByID_student",studentdto );
44. } catch (SQLException e) {
45.
46. e.printStackTrace();
47. }
48. }
49.
50. //更新student表的数据
51. public void updataStudent(SqlMapClient sqlMap, StudentDto studentdto) {
52.
53. try {
54. sqlMap.update("updataStudent_test",studentdto );
55. } catch (SQLException e) {
56.
57. e.printStackTrace();
58. }
59. }
60.
61. //查询student表的所有数据
62. public ArrayList selectStudent(SqlMapClient sqlMap) {
63.
64. //保存查询结果
65. ArrayList rsList = new ArrayList();
66.
67. try {
68. rsList = (ArrayList)sqlMap.queryForList("select_all_student","");
69. } catch (SQLException e) {
70.
71. e.printStackTrace();
72. }
73. return rsList;
74. }
75.
76. //查询student表的指定ID数据
77. public StudentDto selectStudentByID(SqlMapClient sqlMap, StudentDto studentdto) {
78.
79. //返回后保存在info中
80. StudentDto info = new StudentDto();
81. try {
82. info = (StudentDto)sqlMap.queryForObject("selectByID_student", studentdto);
83. } catch (SQLException e) {
84.
85. e.printStackTrace();
86. }
87. return info;
88. }
89.
90. }




2.4 测试类的内容为

1. package zh2208.com;
2.
3. import java.io.IOException;
4. import java.io.Reader;
5. import java.util.ArrayList;
6.
7. import zh2208.dto.StudentDto;
8. import zh2208.impl.StudentImpl;
9.
10. import com.ibatis.common.resources.Resources;
11. import com.ibatis.sqlmap.client.SqlMapClient;
12. import com.ibatis.sqlmap.client.SqlMapClientBuilder;
13.
14. /**
15. * @author zh2208
16. *
17. */
18. public class MainTest {
19.
20.
21. public StudentImpl impl = new StudentImpl();
22. public StudentDto info = new StudentDto();
23. public static SqlMapClient sqlmapclient = null;
24. static {
25. try {
26. //读取xml文件
27. Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
28. sqlmapclient = SqlMapClientBuilder.buildSqlMapClient(reader);
29. reader.close();
30. } catch (IOException e) {
31.
32. e.printStackTrace();
33. }
34. }
35.
36. public static void main(String []args){
37. MainTest stu = new MainTest();
38.
39. System.out.println("------------------------------- start ------------------------------");
40.
41. //以下为各种方法测试
42.
43. //添加 student表的数据
44. //stu.addStudent_test();
45.
46. //删除student表的数据
47. //stu.delStudent_test();
48.
49. //删除 student表的指定ID数据
50. //stu.delStudentByID_test();
51.
52. //更新student表的数据
53. //stu.updataStudent_test();
54.
55. //查询 student表的所有数据
56. //stu.selectStudent_test();
57.
58. //查询student表的所有数据
59. //stu.selectStudentByID_test();
60.
61. System.out.println("------------------------------- end ------------------------------");
62.
63. }
64.
65. //添加student表的数据
66. public void addStudent_test(){
67.
68. //把要插入的数据填入 info对象中
69. info.setId(5);
70. info.setName("zh2208");
71. info.setSex("男");
72. info.setAge(24);
73. info.setAddress("上海");
74.
75. impl.addStudent(sqlmapclient, info);
76. }
77.
78. //删除student表的数据
79. public void delStudent_test(){
80. impl.delStudent(sqlmapclient);
81. }
82.
83. //删除student表的指定ID数据
84. public void delStudentByID_test(){
85. //指定ID
86. info.setId(1);
87. impl.delStudentByID(sqlmapclient,info);
88.
89. }
90.
91. //更新student表的数据
92. public void updataStudent_test(){
93.
94. //把要更新的数据填入 info对象中
95. info.setId(6);
96. info.setName("zh2208up");
97. info.setSex("男");
98. info.setAge(20);
99. info.setAddress("上海up");
100. impl.updataStudent(sqlmapclient, info);
101. }
102.
103. //查询student表的所有数据
104. public void selectStudent_test(){
105.
106. StudentDto stu_dto = new StudentDto();
107. //检索结果保存到 list中
108. ArrayList resultList = impl.selectStudent(sqlmapclient);
109. for(int i = 0; i < resultList.size();i++){
110. stu_dto = (StudentDto) resultList.get(i);
111. //打印对象中的信息
112. show(stu_dto);
113. }
114.
115. }
116.
117. //查询student表的指定ID数据
118. public void selectStudentByID_test(){
119. StudentDto stu_dto = new StudentDto();
120. info.setId(1);
121. stu_dto = impl.selectStudentByID(sqlmapclient,info);
122.
123. if(stu_dto != null){
124. show(stu_dto);
125. }else{
126. System.out.println("no data!!!!");
127. }
128. }
129.
130. //打印查询结果
131. public void show(StudentDto stu_dto){
132.
133. System.out.print("学生ID :" + stu_dto.getId() + " ; ");
134. System.out.print("学生姓名 :" + stu_dto.getName() + " ; ");
135. System.out.print("学生性别 :" + stu_dto.getSex() + " ; ");
136. System.out.print("学生年龄 :" + stu_dto.getAge() + " ; ");
137. System.out.print("学生地址 :" + stu_dto.getAddress());
138. System.out.println();
139.
140. }
141.
142. }