Java through the MyBatis framework for MySQL data additions and deletions to check the basic methods _java

1. Query

In addition to a single recorded query, here we try to query a set of records.

The Iusermapper interface adds the following methods:

List<user> getusers (String name); 

Add in User.xml:

<resultmap type= "User" id= "userlist" ><!--type is the full name of the class that returns a list element or an alias--> <id "id" column= 
  "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 for the user list defined above--> 
  select * from ' user ' where name is like #{name}< C11/></select> 

Test method:

@Test public 
void Querylisttest () { 
  sqlsession session = Sqlsessionfactory.opensession (); 
  try { 
    Iusermapper mapper = Session.getmapper (iusermapper.class); 
    list<user> users = mapper.getusers ("%a%"); % represents any character in SQL. For 
    (User user:users) { 
      log.info ("{}: {}", User.getname (), user.getaddress ()); 
    } 
  } finally { 
    Session.close (); 
  } 
 

If a table query returns a composite object, it needs to be handled with the Association keyword.
such as user publish article, each user can publish multiple article, they are a pair of many relations.

(1) Create the article table and insert the test data :

--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 NOT NULL, 
  ' content ' text is not NULL, 
  PRIMARY KEY (' id ') 
engine=innodb auto_in Crement=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 class:

public class Article { 
  private int id; 
  private user user; 
  Private String title; 
  Private String content; 
  Getters and setters are omitted 
} 

(3) Add in Iusermapper:

list<article> getarticlesbyuserid (int id); 

(4) Add in 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 property to map to the user class--> 
    <id column= "id" property= "id"/> 
    <result column= "name" property= "name"/> 
    <result "Address" column= " 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 method:

@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.g Etuser (). GetName ()); 
    } 
  finally { 
    session.close (); 
  } 
} 

Report:
In addition to defining the mappings of fields and properties within the association tag, you can also reuse the user's Resultmap:

<association property= "user" javatype= "user" resultmap= "userlist"/> 

2. New

The Iusermapper interface adds the following methods:

int addUser (user user);

User.xml Add:

<insert id= "AddUser" parametertype= "User" usegeneratedkeys= "true" keyproperty= "id" ><!-- Usegeneratedkeys Specifies that MyBatis uses the primary key automatically generated by the database and populates the properties specified by Keyproperty. If not specified, the returned object does not get the generated value--> 
  insert INTO user (name,age,address) VALUES (#{name},#{age},#{address}) 
</insert > 

Test method:

@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 (); Default is not autocommit. Call Session.getconnection (). Getautocommit () 
    to view Log.info ("{} new record is inserted successfully whose ID: {}", Affectedcount, User.getid ()); 
  finally { 
    session.close (); 
  } 
} 

3. Update

Interface Add Method:

int updateuser (user user); 

User.xml Add:

<update id= "UpdateUser" parametertype= "user" > 
  update ' user ' set Name=#{name}, Age=#{age}, Address=#{address } 
  where Id=#{id} 
</update> 

Test method:

@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); In addition to the properties that you want to modify, other properties of user are assigned values that are updated by the database to the original value (null or 0, and so on), and can be queried once, but this increases the unnecessary interaction with the database. The following conditions can be judged to avoid this problem. 
    Log.info ("Affected count: {}", affectedcount); 
    Session.commit (); 
  } finally { 
    session.close (); 
  } 

4. Delete

Interface Add Method:

int deleteuser (int id); 

User.xml Add:

<delete id= "DeleteUser" parametertype= "int" > 
  delete from ' user ' where Id=#{id} 
</delete> 

Test method:

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