Java通过MyBatis框架对MySQL数据进行增删查改的基本方法
程序员文章站
2024-03-12 13:35:08
1. 查询
除了单条记录的查询,这里我们来尝试查询一组记录。
iusermapper接口添加下面方法:
list getusers(...
1. 查询
除了单条记录的查询,这里我们来尝试查询一组记录。
iusermapper接口添加下面方法:
list<user> getusers(string name);
在user.xml中添加:
<resultmap type="user" id="userlist"><!-- type为返回列表元素的类全名或别名 --> <id column="id" property="id" /> <result column="name" property="name" /> <result column="age" property="age" /> <result column="address" property="address" /> </resultmap> <select id="getusers" parametertype="string" resultmap="userlist"><!-- resultmap为上面定义的user列表 --> select * from `user` where name like #{name} </select>
测试方法:
@test public void querylisttest() { sqlsession session = sqlsessionfactory.opensession(); try { iusermapper mapper = session.getmapper(iusermapper.class); list<user> users = mapper.getusers("%a%"); // %在sql里代表任意个字符。 for (user user : users) { log.info("{}: {}", user.getname(), user.getaddress()); } } finally { session.close(); } }
如果联表查询,返回的是复合对象,需要用association关键字来处理。
如user发表article,每个用户可以发表多个article,他们之间是一对多的关系。
(1) 创建article表,并插入测试数据:
-- drop the table if exists drop table if exists `article`; -- create a table named 'article' create table `article` ( `id` int not null auto_increment, `user_id` int not null, `title` varchar(100) not null, `content` text not null, primary key (`id`) ) engine=innodb auto_increment=1 default charset=utf8; -- add several test records insert into `article` values ('1', '1', 'title1', 'content1'), ('2', '1', 'title2', 'content2'), ('3', '1', 'title3', 'content3'), ('4', '1', 'title4', 'content4');
(2) com.john.hbatis.model.article类:
public class article { private int id; private user user; private string title; private string content; // getters and setters are omitted }
(3) 在iusermapper中添加:
list<article> getarticlesbyuserid(int id);
(4) 在user.xml中添加:
<resultmap type="com.john.hbatis.model.article" id="articlelist"> <id column="a_id" property="id" /> <result column="title" property="title" /> <result column="content" property="content" /> <association property="user" javatype="user"><!-- user属性映射到user类 --> <id column="id" property="id" /> <result column="name" property="name" /> <result column="address" property="address" /> </association> </resultmap> <select id="getarticlesbyuserid" parametertype="int" resultmap="articlelist"> select u.id, u.name, u.age, u.address, a.id a_id, a.title, a.content from article a inner join user u on a.user_id=u.id and u.id=#{id} </select>
(5)测试方法:
@test public void getarticlesbyuseridtest() { sqlsession session = sqlsessionfactory.opensession(); try { iusermapper mapper = session.getmapper(iusermapper.class); list<article> articles = mapper.getarticlesbyuserid(1); for (article article : articles) { log.info("{} - {}, author: {}", article.gettitle(), article.getcontent(), article.getuser().getname()); } } finally { session.close(); } }
附:
除了在association标签内定义字段和属性的映射外,还可以重用user的resultmap:
<association property="user" javatype="user" resultmap="userlist" />
2. 新增
iusermapper接口添加下面方法:
int adduser(user user);
user.xml添加:
<insert id="adduser" parametertype="user" usegeneratedkeys="true" keyproperty="id"><!-- usegeneratedkeys指定mybatis使用数据库自动生成的主键,并填充到keyproperty指定的属性上。如果未指定,返回对象拿不到生成的值 --> insert into user(name,age,address) values(#{name},#{age},#{address}) </insert>
测试方法:
@test public void addusertest() { user user = new user("lucy", 102, "happy district"); sqlsession session = sqlsessionfactory.opensession(); try { iusermapper mapper = session.getmapper(iusermapper.class); int affectedcount = mapper.adduser(user); session.commit(); // 默认为不自动提交。调用session.getconnection().getautocommit()查看 log.info("{} new record was inserted successfully whose id: {}", affectedcount, user.getid()); } finally { session.close(); } }
3. 更新
接口添加方法:
int updateuser(user user);
user.xml添加:
<update id="updateuser" parametertype="user"> update `user` set name=#{name}, age=#{age}, address=#{address} where id=#{id} </update>
测试方法:
@test public void updateusertest() { sqlsession session = sqlsessionfactory.opensession(); try { iusermapper mapper = session.getmapper(iusermapper.class); user user = mapper.getuserbyid(8); user.setaddress("satisfied district"); int affectedcount = mapper.updateuser(user); // 除了要修改的属性外,user的其它属性也要赋值,否则这些属性会被数据库更新为初始值(null或0等),可以先查询一次,但这样会增加和数据库不必要的交互。后面的条件判断能避免此问题。 log.info("affected count: {}", affectedcount); session.commit(); } finally { session.close(); } }
4. 删除
接口添加方法:
int deleteuser(int id);
user.xml添加:
<delete id="deleteuser" parametertype="int"> delete from `user` where id=#{id} </delete>
测试方法:
@test public void deleteusertest() { sqlsession session = sqlsessionfactory.opensession(); try { iusermapper mapper = session.getmapper(iusermapper.class); int affectedcount = mapper.deleteuser(8); log.info("affected count: {}", affectedcount); session.commit(); } finally { session.close(); } }