MyBatis_CURD
一、项目结构
二、代码实现
1 package com.jmu.bean; 2 3 public class Student { 4 private Integer id; 5 private String name; 6 private double score; 7 private int age; 8 public Student() { 9 super(); 10 } 11 12 public Student(String name, int age ,double score) { 13 super(); 14 this.name = name; 15 this.score = score; 16 this.age = age; 17 } 18 19 @Override 20 public String toString() { 21 return "Student [id=" + id + ", name=" + name + ", score=" + score + ", age=" + age + "]"; 22 } 23 24 public Integer getId() { 25 return id; 26 } 27 public void setId(Integer id) { 28 this.id = id; 29 } 30 public String getName() { 31 return name; 32 } 33 public void setName(String name) { 34 this.name = name; 35 } 36 public double getScore() { 37 return score; 38 } 39 public void setScore(double score) { 40 this.score = score; 41 } 42 public int getAge() { 43 return age; 44 } 45 public void setAge(int age) { 46 this.age = age; 47 } 48 49 }
1 package com.jmu.dao; 2 3 4 5 import java.util.List; 6 import java.util.Map; 7 8 import com.jmu.bean.Student; 9 10 public interface IStudentDao { 11 void insertStudent (Student student); 12 void insertStudentCacheId(Student student);//插入后获取 13 14 void deleteStudentById(int id); 15 void updateStudent(Student student); 16 17 18 List<Student> selectAllStudents();//查询所有 19 Map<String,Object> selectAllStudentsMap(); 20 Student selectStudentById(int id); //根据id查询 21 List<Student> selectStudentsByName(String name);//模糊查询 22 } 23
1 package com.jmu.dao; 2 3 import static org.hamcrest.CoreMatchers.nullValue; 4 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.util.ArrayList; 8 import java.util.HashMap; 9 import java.util.List; 10 import java.util.Map; 11 12 import javax.persistence.MapKey; 13 14 import org.apache.ibatis.io.Resources; 15 import org.apache.ibatis.session.SqlSession; 16 import org.apache.ibatis.session.SqlSessionFactory; 17 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 18 19 import com.jmu.bean.Student; 20 import com.jmu.utils.MybatisUtils; 21 22 public class StudentDaoImpl implements IStudentDao { 23 24 private InputStream inputStream; 25 private SqlSession sqlSession; 26 27 @Override 28 public void insertStudent(Student student) { 29 // TODO Auto-generated method stub 30 try { 31 /* 32 * // 1、加载主配置文件 inputStream = 33 * Resources.getResourceAsStream("mybatis-config.xml"); // 34 * 2、创建SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new 35 * SqlSessionFactoryBuilder().build(inputStream); sqlSession = 36 * sqlSessionFactory.openSession(); // 4、相关操作 37 */ 38 sqlSession = MybatisUtils.getSqlSession(); 39 sqlSession.insert("insertStudent", student); 40 sqlSession.commit(); 41 } finally { 42 43 if (sqlSession != null) { 44 sqlSession.close(); 45 } 46 } 47 } 48 49 @Override 50 public void insertStudentCacheId(Student student) { 51 // TODO Auto-generated method stub 52 try { 53 54 sqlSession = MybatisUtils.getSqlSession(); 55 sqlSession.insert("insertStudentCacheId", student); 56 sqlSession.commit(); 57 } finally { 58 59 if (sqlSession != null) { 60 sqlSession.close(); 61 } 62 } 63 } 64 65 @Override 66 public void deleteStudentById(int id) { 67 // TODO Auto-generated method stub 68 try { 69 70 sqlSession = MybatisUtils.getSqlSession(); 71 sqlSession.update("deleteStudentById", id); 72 sqlSession.commit(); 73 } finally { 74 75 if (sqlSession != null) { 76 sqlSession.close(); 77 } 78 } 79 } 80 81 @Override 82 public void updateStudent(Student student) { 83 // TODO Auto-generated method stub 84 try { 85 86 sqlSession = MybatisUtils.getSqlSession(); 87 sqlSession.update("updateStudent", student); 88 sqlSession.commit(); 89 } finally { 90 91 if (sqlSession != null) { 92 sqlSession.close(); 93 } 94 } 95 } 96 97 @Override 98 public List<Student> selectAllStudents() { 99 // TODO Auto-generated method stub 100 List<Student> students =new ArrayList<Student>(); 101 try { 102 sqlSession=MybatisUtils.getSqlSession(); 103 students=sqlSession.selectList("selectAllStudents"); 104 105 } finally { 106 if (sqlSession!=null) { 107 sqlSession.close(); 108 109 } 110 } 111 112 return students; 113 } 114 115 @Override 116 public Map<String, Object> selectAllStudentsMap() { 117 // TODO Auto-generated method stub 118 Map<String, Object> map=new HashMap<>(); 119 try { 120 sqlSession=MybatisUtils.getSqlSession(); 121 map=sqlSession.selectMap("selectAllStudents", "name"); 122 } finally { 123 // TODO: handle finally clause 124 if (sqlSession!=null) { 125 sqlSession.close(); 126 } 127 } 128 return map; 129 } 130 131 @Override 132 public Student selectStudentById(int id) { 133 // TODO Auto-generated method stub 134 Student student=null; 135 try { 136 sqlSession=MybatisUtils.getSqlSession(); 137 student=sqlSession.selectOne("selectStudentById", id); 138 139 } finally { 140 // TODO: handle finally clause 141 if (sqlSession!=null) { 142 sqlSession.close(); 143 144 } 145 } 146 return student; 147 } 148 149 @Override 150 public List<Student> selectStudentsByName(String name) { 151 // TODO Auto-generated method stub 152 List<Student> students =new ArrayList<Student>(); 153 try { 154 sqlSession=MybatisUtils.getSqlSession(); 155 students=sqlSession.selectList("selectStudentByName",name); 156 157 } finally { 158 if (sqlSession!=null) { 159 sqlSession.close(); 160 161 } 162 } 163 return students; 164 } 165 166 }
<?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.jmu.dao">
<insert id="insertStudent" parameterType="Student">
insert into
student(name,age,score) values(#{name},#{age},#{score})
</insert>
<insert id="insertStudentCacheId">
insert into student(name,age,score) values(#{name},#{age},#{score})
<selectKey resultType="int" keyProperty="id" order="AFTER">
select @@identity
</selectKey>
</insert>
<delete id="deleteStudentById">
delete from student where id=#{XXX}<!--这里的#{} 仅仅是个占位符，里边放什么都行 -->
</delete>
<update id="updateStudent">
update student set name=#{name},age=#{age},score=#{score} where id=#{id}
</update>
<select id="selectAllStudents" resultType="Student">
select id,name,age,score from student
</select>
<select id="selectStudentById" resultType="Student">
select id,name,age,score from student where id=#{JJJ}
</select>
<select id="selectStudentByName" resultType="Student">
<!-- select id,name,age,score from student where name like CONCAT('%',#{XXX},'%') -->
<!-- select id,name,age,score from student where name like '%王二%' -->
<!-- select id,name,age,score from student where name like '%${value}%' --><!-- 不存在sql注入 风险，但是效率低 -->
select id,name,age,score from student where name like '%' #{XXX} '%'<!--常用 -->
</select>
</mapper>
1 package com.jmu.utils; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 6 import org.apache.ibatis.io.Resources; 7 import org.apache.ibatis.session.SqlSession; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 10 11 public class MybatisUtils { 12 private static SqlSessionFactory sqlSessionFactory; 13 14 public static SqlSession getSqlSession() { 15 16 try { 17 InputStream is = Resources.getResourceAsStream("mybatis-config.xml"); 18 if (sqlSessionFactory == null) { 19 sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); 20 21 } 22 return sqlSessionFactory.openSession(); 23 } catch (IOException e) { 24 // TODO Auto-generated catch block 25 e.printStackTrace(); 26 } 27 28 return null; 29 30 } 31 }
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://127.0.0.1:3306/test 3 jdbc.username=root 4 jdbc.password=123456
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6 <properties resource="jdbc_mysql.properties"> </properties> 7 <typeAliases > 8 <package name="com.jmu.bean" /> 9 </typeAliases> 10 <!--配置运行环境 --> 11 <environments default="development"> 12 <environment id="development"> 13 <transactionManager type="JDBC" /> 14 <dataSource type="POOLED"> 15 <property name="driver" value="${jdbc.driver}" /> 16 <property name="url" 17 value="${jdbc.url}" /> 18 <property name="username" value="${jdbc.username}" /> 19 <property name="password" value="${jdbc.password}" /> 20 </dataSource> 21 </environment> 22 </environments> 23 <!--注册映射文件 --> 24 <mappers> 25 <mapper resource="com/jmu/dao/mapper.xml" /> 26 </mappers> 27 </configuration>
1 package com.jmu.test; 2 3 import static org.junit.Assert.*; 4 5 import java.util.List; 6 import java.util.Map; 7 8 import org.junit.Before; 9 import org.junit.Test; 10 11 import com.jmu.bean.Student; 12 import com.jmu.dao.IStudentDao; 13 import com.jmu.dao.StudentDaoImpl; 14 15 public class MyTest { 16 private IStudentDao dao; 17 18 @Before 19 public void Before() { 20 dao = new StudentDaoImpl(); 21 } 22 23 24 25 @Test 26 public void test01() { 27 Student student = new Student("明明",19,87.9); 28 System.out.println("插入前：student="+student); 29 dao.insertStudent(student); 30 System.out.println("插入后：student="+student); 31 } 32 //插入后获取 33 @Test 34 public void test02(){ 35 Student student =new Student("明明",23,99.5); 36 System.out.println("插入前：student="+student); 37 dao.insertStudentCacheId(student); 38 System.out.println("插入后：student="+student); 39 } 40 @Test 41 public void test03(){ 42 dao.deleteStudentById(25); 43 } 44 @Test 45 public void test04(){ 46 Student student=new Student("红酒",23,93.5); 47 student.setId(28); 48 dao.updateStudent(student); 49 } 50 @Test 51 public void test05(){ 52 List<Student> students=dao.selectAllStudents(); 53 for(Student student : students){ 54 System.out.println(student); 55 } 56 } 57 @Test 58 public void test06(){ 59 Map<String, Object> map = dao.selectAllStudentsMap(); 60 System.out.println(map.get("王维")); 61 } 62 @Test 63 public void test07(){ 64 Student student=dao.selectStudentById(33); 65 System.out.println(student); 66 } 67 @Test 68 public void test08(){ 69 List<Student> students = dao.selectStudentsByName("王二"); 70 for (Student student : students) { 71 System.out.println(student); 72 } 73 } 74 75 76 }
插入前：student=Student [id=null, name=明明, score=87.9, age=19] DEBUG [main] - ==> Preparing: insert into student(name,age,score) values(?,?,?) DEBUG [main] - ==> Parameters: 明明(String), 19(Integer), 87.9(Double) DEBUG [main] - <== Updates: 1 插入后：student=Student [id=null, name=明明, score=87.9, age=19] 插入前：student=Student [id=null, name=明明, score=99.5, age=23] DEBUG [main] - ==> Preparing: insert into student(name,age,score) values(?,?,?) DEBUG [main] - ==> Parameters: 明明(String), 23(Integer), 99.5(Double) DEBUG [main] - <== Updates: 1 DEBUG [main] - ==> Preparing: select @@identity DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 1 插入后：student=Student [id=118, name=明明, score=99.5, age=23] DEBUG [main] - ==> Preparing: delete from student where id=? DEBUG [main] - ==> Parameters: 25(Integer) DEBUG [main] - <== Updates: 0 DEBUG [main] - ==> Preparing: update student set name=?,age=?,score=? where id=? DEBUG [main] - ==> Parameters: 红酒(String), 23(Integer), 93.5(Double), 28(Integer) DEBUG [main] - <== Updates: 1 DEBUG [main] - ==> Preparing: select id,name,age,score from student DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 95 Student [id=17, name=王维, score=93.5, age=23] Student [id=18, name=王维, score=93.5, age=23] Student [id=19, name=王维, score=93.5, age=23] Student [id=20, name=王维, score=93.5, age=23] Student [id=21, name=王维, score=93.5, age=23] Student [id=22, name=王维, score=93.5, age=23] Student [id=23, name=王维, score=93.5, age=23] Student [id=24, name=王维, score=93.5, age=23] Student [id=26, name=王维, score=93.5, age=23] Student [id=27, name=王维, score=93.5, age=23] Student [id=28, name=红酒, score=93.5, age=23] Student [id=29, name=安抚王二, score=93.5, age=23] Student [id=30, name=王维一, score=93.5, age=23] Student [id=31, name=王维二, score=93.5, age=23] Student [id=32, name=王二分, score=93.5, age=23] Student [id=33, name=王二维, score=93.5, age=23] Student [id=34, name=王问维, score=93.5, age=23] Student [id=35, name=王维, score=93.5, age=23] Student [id=36, name=明明, score=87.9, age=19] Student [id=37, name=明明, score=99.5, age=23] Student [id=38, name=明明, score=87.9, age=19] Student [id=39, name=明明, score=99.5, age=23] Student [id=40, name=明明, score=87.9, age=19] Student [id=41, name=明明, score=99.5, age=23] Student [id=42, name=明明, score=87.9, age=19] Student [id=43, name=明明, score=99.5, age=23] Student [id=44, name=明明, score=87.9, age=19] Student [id=45, name=明明, score=99.5, age=23] Student [id=46, name=明明, score=87.9, age=19] Student [id=47, name=明明, score=99.5, age=23] Student [id=48, name=明明, score=87.9, age=19] Student [id=49, name=明明, score=99.5, age=23] Student [id=50, name=明明, score=87.9, age=19] Student [id=51, name=明明, score=99.5, age=23] Student [id=52, name=明明, score=87.9, age=19] Student [id=53, name=明明, score=99.5, age=23] Student [id=54, name=明明, score=87.9, age=19] Student [id=55, name=明明, score=99.5, age=23] Student [id=56, name=明明, score=87.9, age=19] Student [id=57, name=明明, score=99.5, age=23] Student [id=58, name=明明, score=87.9, age=19] Student [id=59, name=明明, score=99.5, age=23] Student [id=60, name=明明, score=87.9, age=19] Student [id=61, name=明明, score=99.5, age=23] Student [id=62, name=明明, score=87.9, age=19] Student [id=63, name=明明, score=99.5, age=23] Student [id=64, name=明明, score=87.9, age=19] Student [id=65, name=明明, score=99.5, age=23] Student [id=66, name=明明, score=87.9, age=19] Student [id=67, name=明明, score=99.5, age=23] Student [id=68, name=明明, score=87.9, age=19] Student [id=69, name=明明, score=99.5, age=23] Student [id=70, name=明明, score=87.9, age=19] Student [id=71, name=明明, score=99.5, age=23] Student [id=72, name=明明, score=87.9, age=19] Student [id=73, name=明明, score=99.5, age=23] Student [id=74, name=明明, score=87.9, age=19] Student [id=75, name=明明, score=99.5, age=23] Student [id=76, name=明明, score=87.9, age=19] Student [id=77, name=明明, score=99.5, age=23] Student [id=78, name=明明, score=87.9, age=19] Student [id=79, name=明明, score=99.5, age=23] Student [id=80, name=明明, score=87.9, age=19] Student [id=81, name=明明, score=99.5, age=23] Student [id=82, name=明明, score=87.9, age=19] Student [id=83, name=明明, score=99.5, age=23] Student [id=84, name=明明, score=87.9, age=19] Student [id=85, name=明明, score=99.5, age=23] Student [id=86, name=明明, score=87.9, age=19] Student [id=87, name=明明, score=99.5, age=23] Student [id=88, name=明明, score=87.9, age=19] Student [id=89, name=明明, score=99.5, age=23] Student [id=90, name=明明, score=87.9, age=19] Student [id=91, name=明明, score=99.5, age=23] Student [id=92, name=明明, score=87.9, age=19] Student [id=93, name=明明, score=99.5, age=23] Student [id=94, name=明明, score=87.9, age=19] Student [id=95, name=明明, score=99.5, age=23] Student [id=100, name=明明, score=87.9, age=19] Student [id=101, name=明明, score=99.5, age=23] Student [id=104, name=明明, score=87.9, age=19] Student [id=105, name=明明, score=99.5, age=23] Student [id=106, name=明明, score=87.9, age=19] Student [id=107, name=明明, score=87.9, age=19] Student [id=108, name=明明, score=87.9, age=19] Student [id=109, name=明明, score=87.9, age=19] Student [id=110, name=明明, score=87.9, age=19] Student [id=111, name=明明, score=87.9, age=19] Student [id=112, name=明明, score=87.9, age=19] Student [id=113, name=明明, score=87.9, age=19] Student [id=114, name=明明, score=99.5, age=23] Student [id=115, name=明明, score=87.9, age=19] Student [id=116, name=明明, score=99.5, age=23] Student [id=117, name=明明, score=87.9, age=19] Student [id=118, name=明明, score=99.5, age=23] DEBUG [main] - ==> Preparing: select id,name,age,score from student DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 95 Student [id=35, name=王维, score=93.5, age=23] DEBUG [main] - ==> Preparing: select id,name,age,score from student where id=? DEBUG [main] - ==> Parameters: 33(Integer) DEBUG [main] - <== Total: 1 Student [id=33, name=王二维, score=93.5, age=23] DEBUG [main] - ==> Preparing: select id,name,age,score from student where name like '%王二%' DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 3 Student [id=29, name=安抚王二, score=93.5, age=23] Student [id=32, name=王二分, score=93.5, age=23] Student [id=33, name=王二维, score=93.5, age=23]
