因为昨天看视频学习来着,然后偷了一下懒只更新了一下junit(工欲善其事必先利其器),今天这个估计有点多,文章有点长
先看一下现在的目录结构,因为现在学的是对mysql的操作,抽象一下就是对持久化对象的操作,而且感觉学这个加深对面向对象的更加深入的理解。
ok,原来的文件基本没有动地方,动地方的我说一下,数据库配置,从xml配置到了properties中了,简单来说就是高内聚低耦合,这里新建了一个mapper目录,里面写的是接口,接口中的方法对应着xml中的方法,即id,等一会把代码贴出来相信大家就理解了,还有就是建议放在一个包下,因为设置的时候进行映射有包名映射,这样一次配置就可以用了,而且也比较简单,防止自己遗漏。再来说一下model,系统肯定不是编写一次就一成不变了,肯定后期要进行维护,所以这里虽然对model进行了扩展但并不是暴力的在源文件中进行修改,而且新建了一个java文件更利于扩展,剩下的就不多说了,我写的注释挺详细的。因为我也是初学,不会的先看注释还不会的话可以留言,我能解决的话就会回复的。
好了,上代码(用的到的会贴上的,有的是正在学,写了一半的代码就不贴了,希望谅解,完善后我会贴上github地址):
还是老规矩,我按照正常的逻辑顺序上代码(前提是数据库都创建好了的)
user.java
package com.fanyafeng.model;
import java.util.Date;
/**
* Author: fanyafeng
* Data: 16/10/11 14:56
* Email: fanyafeng@live.cn
*/
public class User {
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
usercustom.java
package com.fanyafeng.model;
/**
* Author: fanyafeng
* Data: 16/10/12 16:54
* Email: fanyafeng@live.cn
*/
public class UserCustom extends User {
}
iusermapper.xml
package com.fanyafeng.mapper;
import com.fanyafeng.model.User;
import com.fanyafeng.model.UserCustom;
import com.fanyafeng.model.UserQueryVo;
import java.util.List;
/**
* Author: fanyafeng
* Data: 16/10/11 14:55
* Email: fanyafeng@live.cn
*/
public interface IUserMapper {
// 只能传一个参数,当需要多个参数时,可以采用pojo
public List<User> queryUserByName(String name);
public User selectUserById(int id);
public void add();
public void del(int id);
public void alter(int id);
public List<UserCustom> findUserByUserQuery(UserQueryVo userQueryVo);
public int findUserCount(UserQueryVo userQueryVo);
public User findUserByIdResultMap(int id);
public List<UserCustom> findUserList(UserQueryVo userQueryVo);
public List<UserCustom> queryUserList(UserQueryVo userQueryVo);
}
user.xml
<?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">
<!--resultType 指定单条记录返回的类型-->
<mapper namespace="com.fanyafeng.dao.IUserDao">
<select id="selectUserById" parameterType="int" resultType="com.fanyafeng.model.User">
select * from user where id = #{id}
</select>
<!--'%${value}' 表示拼接sql串,只能使用value,有可能会引起sql注入的情况-->
<select id="queryUserByName" parameterType="String" resultType="com.fanyafeng.model.User">
SELECT * FROM user WHERE username LIKE #{name}
</select>
<!--INSERT INTO USER (id,username,sex,address) VALUE (null,"陈潇然","女","大学同学")-->
<!-- SELECT LAST_INSERT_ID() 得到刚插入数据库的记录的主键值,只适用于自增主键
keyProperty:将查询到主键值设置到相应的 parameterType 属性中
order 相对于执行的sql语句的顺序-->
<insert id="add" parameterType="com.fanyafeng.model.User">
<selectKey keyProperty="id" order="AFTER" resultType="int">
SELECT LAST_INSERT_ID()
</selectKey>
insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
</insert>
<!--非自增的id-->
<!-- <selectKey keyProperty="id" order="BEFORE" resultType="String">
SELECT uuid();
</selectKey>
insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
-->
<!--根据id进行删除-->
<delete id="del" parameterType="int">
DELETE FROM user WHERE id = #{id}
</delete>
<!--根据id更新数据库-->
<update id="alter" parameterType="com.fanyafeng.model.User">
UPDATE USER SET username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} WHERE id = #{id}
</update>
</mapper>
userqueryvo.java
package com.fanyafeng.model;
import java.util.List;
/**
* Author: fanyafeng
* Data: 16/10/12 16:52
* Email: fanyafeng@live.cn
*/
public class UserQueryVo {
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
private UserCustom userCustom;
public UserCustom getUserCustom() {
return userCustom;
}
public void setUserCustom(UserCustom userCustom) {
this.userCustom = userCustom;
}
}
这里说一下,刚开始的文件没有更改目录名称,后面的为了更容易理解,还有就是符合自己的风格,我进行了更改,接口文件和xml放在了同一个目录下了,iusermapper和user用的是同一个xml,这里注意里面的命名空间
iusermapper.xml
<?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">
<!--resultType 指定单条记录返回的类型-->
<mapper namespace="com.fanyafeng.mapper.IUserMapper">
<sql id="queryUser">
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
AND user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
AND user.username=#{userCustom.username}
</if>
-- collection:指定输入对象中集合属性
-- item:每个遍历生成对象对应的字段名
-- open:开始遍历拼接的串
-- close:结束遍历拼接的串
-- separator:遍历的两个对象中需要拼接的串
<if test="ids!=null">
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</if>
</if>
</sql>
<select id="queryUserList" parameterType="com.fanyafeng.model.UserQueryVo"
resultType="com.fanyafeng.model.UserCustom">
SELECT * FROM USER
<where>
<include refid="queryUser"/>
</where>
</select>
<!--动态sql-->
<select id="findUserList" parameterType="com.fanyafeng.model.UserQueryVo"
resultType="com.fanyafeng.model.UserCustom">
SELECT * FROM USER
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
AND user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
AND user.username=#{userCustom.username}
</if>
</if>
</where>
</select>
<!--定义resultMap
type:resultMap最终映射的java对象
id:对resultMap的唯一标识
-->
<resultMap type="com.fanyafeng.model.User" id="userResultMap">
<!--id表示查询结果中的唯一标识
coumn:查询出来的列名
property:type指定的pojo类型中德属性名
最终resultmap对colu和property做一个映射关系-->
<id column="id_" property="id"/>
<!--
result:对普通映射的定义-->
<result column="username_" property="username"/>
</resultMap>
<select id="findUserByIdResultMap" parameterType="int" resultMap="userResultMap">
SELECT id id_,username username_ FROM user WHERE id=#{VALUE}
</select>
<!--
user.username:pojo的映射
-->
<select id="findUserByUserQuery" parameterType="com.fanyafeng.model.UserQueryVo"
resultType="com.fanyafeng.model.UserCustom">
SELECT * FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}'
</select>
<!--获取查询数量-->
<select id="findUserCount" parameterType="com.fanyafeng.model.UserQueryVo" resultType="int">
SELECT COUNT(*) FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}'
</select>
<select id="selectUserById" parameterType="int" resultType="com.fanyafeng.model.User">
select * from user where id = #{id}
</select>
<!--'%${value}' 表示拼接sql串,只能使用value,有可能会引起sql注入的情况-->
<select id="queryUserByName" parameterType="String" resultType="com.fanyafeng.model.User">
SELECT * FROM user WHERE username LIKE #{name}
</select>
<!--INSERT INTO USER (id,username,sex,address) VALUE (null,"陈潇然","女","大学同学")-->
<!-- SELECT LAST_INSERT_ID() 得到刚插入数据库的记录的主键值,只适用于自增主键
keyProperty:将查询到主键值设置到相应的 parameterType 属性中
order 相对于执行的sql语句的顺序-->
<insert id="add" parameterType="com.fanyafeng.model.User">
<selectKey keyProperty="id" order="AFTER" resultType="int">
SELECT LAST_INSERT_ID()
</selectKey>
insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
</insert>
<!--非自增的id-->
<!-- <selectKey keyProperty="id" order="BEFORE" resultType="String">
SELECT uuid();
</selectKey>
insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
-->
<!--根据id进行删除-->
<delete id="del" parameterType="int">
DELETE FROM user WHERE id = #{id}
</delete>
<!--根据id更新数据库-->
<update id="alter" parameterType="com.fanyafeng.model.User">
UPDATE USER SET username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} WHERE id = #{id}
</update>
</mapper>
还有就是新建了dao目录,这里是ibatis的遗留问题,不难,但是我感觉有点麻烦了,可是有可能有的企业还在用,然后学了一下
iuserdaoimpl.java实现了iuserdao的接口,我记得hibernate一般都这么搞
package com.fanyafeng.dao;
import com.fanyafeng.model.User;
import java.util.List;
/**
* Author: fanyafeng
* Data: 16/10/11 14:55
* Email: fanyafeng@live.cn
*/
public interface IUserDao {
public List<User> queryUserByName(String name);
public User selectUserById(int id);
public void add();
public void del(int id);
public void alter(int id);
}
iuserdao接口实现
package com.fanyafeng.dao;
import com.fanyafeng.model.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import java.util.List;
/**
* Author: fanyafeng
* Data: 16/10/12 11:25
* Email: fanyafeng@live.cn
*/
public class IUserDaoImpl implements IUserDao {
// 需要向dao中注入sqlsessionfactory
// 通过构造方法注入session工厂
private SqlSessionFactory sqlSessionFactory;
public IUserDaoImpl(SqlSessionFactory sqlSessionFactory) {
this.sqlSessionFactory = sqlSessionFactory;
}
public List<User> queryUserByName(String name) {
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> userList = sqlSession.selectList("com.fanyafeng.dao.IUserDao.queryUserByName", name);
sqlSession.close();
return userList;
}
public User selectUserById(int id) {
SqlSession sqlSession = sqlSessionFactory.openSession();
User user = sqlSession.selectOne("com.fanyafeng.dao.IUserDao.selectUserById", id);
sqlSession.close();
return user;
}
public void add() {
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.insert("com.fanyafeng.dao.IUserDao.selectUserById");
sqlSession.close();
}
public void del(int id) {
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.delete("com.fanyafeng.dao.IUserDao.del", id);
sqlSession.commit();
sqlSession.close();
}
public void alter(int id) {
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.update("com.fanyafeng.dao.IUserDao.alter", id);
sqlSession.commit();
sqlSession.close();
}
}
下面虽然是进行的测试,但却是重点,很重要,这里直接加载了xml的编译文件,然后进行相应的事务操作,这里用的是junit,上文中已经说了junit的用法了,不会的话可以去谷歌,推荐类的文章都比我说的详细
先来看一下dao的实现,一会再去看直接映射的实现
dao的实现测试
package test.com.fanyafeng.dao;
import com.fanyafeng.dao.IUserDao;
import com.fanyafeng.dao.IUserDaoImpl;
import com.fanyafeng.model.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;
import java.io.IOException;
/**
* IUserDaoImpl Tester.
*
* @author <Authors name>
* @version 1.0
* @since <pre>十月 12, 2016</pre>
*/
public class IUserDaoImplTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void before() throws Exception {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("Configuration.xml"));
}
@After
public void after() throws Exception {
}
/**
* Method: queryUserByName(String name)
*/
@Test
public void testQueryUserByName() throws Exception {
//TODO: Test goes here...
}
/**
* Method: selectUserById(int id)
*/
@Test
public void testSelectUserById() throws Exception {
//TODO: Test goes here...
IUserDao iUserDao = new IUserDaoImpl(sqlSessionFactory);
User user = iUserDao.selectUserById(1);
System.out.println(user.toString());
}
/**
* Method: add()
*/
@Test
public void testAdd() throws Exception {
//TODO: Test goes here...
}
/**
* Method: del(int id)
*/
@Test
public void testDel() throws Exception {
//TODO: Test goes here...
}
/**
* Method: alter(int id)
*/
@Test
public void testAlter() throws Exception {
//TODO: Test goes here...
}
}
iusermappertest.java
package test.com.fanyafeng.mapper;
import com.fanyafeng.mapper.IUserMapper;
import com.fanyafeng.model.User;
import com.fanyafeng.model.UserCustom;
import com.fanyafeng.model.UserQueryVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
import static org.junit.Assert.*;
/**
* Author: fanyafeng
* Data: 16/10/12 14:57
* Email: fanyafeng@live.cn
*/
public class IUserMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void before() throws Exception {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("Configuration.xml"));
}
@Test
public void testSelectUserById() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 通过mapper映射
IUserMapper iUserMapper = sqlSession.getMapper(IUserMapper.class);
iUserMapper.selectUserById(1);
System.out.println(iUserMapper.selectUserById(1).toString());
List<User> userList = iUserMapper.queryUserByName("%亚风%");
for (int i = 0; i < userList.size(); i++) {
System.out.println(userList.get(i).toString());
}
}
@Test
public void testFindUserByUserQuery() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserMapper iUserMapper = sqlSession.getMapper(IUserMapper.class);
UserQueryVo userQueryVo = new UserQueryVo();
UserCustom userCustom = new UserCustom();
userCustom.setSex("男");
userCustom.setUsername("李宁");
userQueryVo.setUserCustom(userCustom);
List<Integer> ids=new ArrayList<Integer>();
ids.add(9);
ids.add(10);
ids.add(23);
userQueryVo.setIds(ids);
List<UserCustom> userCustomList = iUserMapper.queryUserList(userQueryVo);
for (int i = 0; i < userCustomList.size(); i++) {
System.out.println(userCustomList.get(i).toString());
}
int count = iUserMapper.findUserCount(userQueryVo);
System.out.println("查询结果:" + count);
User user = iUserMapper.findUserByIdResultMap(1);
System.out.println(user.toString());
}
}
数据库服务打开就可以测试了,这里的xml编写sql语句比在mysqlworkbench方便,本来想找几篇连贯的文章学习的,无奈没有只能硬着头皮看eclipse的学习视频然后自己在intellij上自己摸索,然后自己写文章了