1.在一个含有自增列的数据库里完成插入的操作并且获取主键的值
举例:
新建一个实体类student
public class Student {
private int sid;
private String name;
private int age;
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
'}';
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Student() {
}
}
在配置的时候使用三个属性:
(1):useGeneratedKeys=“true/false” (true的时候为使用自增的列,false的时候为不使用)在为true的时候不管设置要插入数据的主键的值为多少,他只会自增下去 当false的时候的话,不使用主键的值,不会获取主键的值
(2):keyProperty =“属性名”(将主键的值存入实体的属性中)
(3):keyColumn=“主键列的名称”
@org.junit.Test
public void insert() {
SqlSession sqlSession = factory.openSession();
Student student = new Student();
student.setName("java");
student.setAge(100);
System.out.println("插入之前的id的值"+student.getSid());
sqlSession.insert("com.baidu.mapper.StudentMapper.insert", student);
sqlSession.commit();
System.out.println("插入之后的id的值"+student.getSid());
sqlSession.close();
}
2.动态的sql语句之 foreach的语句
举例:select * from student where id in (208,209,210,211);
利用mybatis实现
mapper实现
<select id="select" parameterType="list" resultType="com.baidu.entity.Student">
select * from student where id in
<foreach collection="list" item="i" open="(" close=")" separator=",">
#{i}
</foreach>
</select>
public void select(){
SqlSession sqlsession = factory.openSession();
List<Integer> list = new ArrayList<Integer>();
list.add(208);
list.add(209);
list.add(210);
list.add(211);
List<Object> students = sqlsession.selectList("com.www.mapper.StudentMapper.select", list);
for (Object student : students) {
System.out.println(student);
}
sqlsession.close();
}
3.动态的sql语句之if语句
例如动态的查询(条件有最大的年龄和最小的年龄,还有姓名的模糊的查询)
在这时刻就要考虑到,每个条件有对应的值。这个时候我们就需要利用map集合的键值对的关系来存储条件
代码如下:
mapper.xml的部分
<select id="selectByCondition" parameterType="map" resultType="com.www.entity.Student">
select * from student
<where>
<if test="name != null">
and name like #{name}
</if>
<if test="MaxAge != null and MinAge !=null">
and age between #{MinAge} and #{MaxAge}
</if>
</where>
</select>
public void selectByCondition(){
SqlSession sqlSession = factory.openSession();
Map<String,Object> map = new HashMap<String, Object>();
map.put("name","李%");
map.put("MaxAge",100);
map.put("MinAge",60);
List<Object> list = sqlSession.selectList("com.www.mapper.StudentMapper.selectByCondition", map);
for (Object o : list) {
System.out.println(o.toString());
}
sqlSession.close();
}
举例:动态的sql语句的修改(if 和set)
mapper.xml
<update id="update" parameterType="map">
update student
<set>
<if test="name != null">
name = #{name}
</if>
</set>
where age = #{age}
</update>
public void update(){
SqlSession sqlSession = factory.openSession();
Map<String,Object> map = new HashMap<>();
map.put("name","hello word ");
map.put("age",100);
sqlSession.update("com.baidu.mapper.StudentMapper.update",map);
sqlSession.commit();
sqlSession.close();
}