欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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(); 
  } 
}