使用Mybatis对MySql数据库进行操作实例

初学Mybatis,参考网络上的教程,自己做的个小练习,适合我这种刚入门的新手。

 

1.环境(数据库创建步骤省略)

MyEclipse 10      Mysql5.5     

 

 

2.新建项目，导入包

mybatis-3.2.3.jar

mysql-connector-java-5.1.24-bin.jar

ibatis-2.3.0.677.jar

 

3.创建SqlMapConfig.xml文件

<?xml version="1.0" encoding= "UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd" >

<sqlMapConfig>
     <settings useStatementNamespaces ="true" />
     <transactionManager type ="JDBC">
            <dataSource type ="SIMPLE">
                 <property name ="JDBC.Driver" value= "com.mysql.jdbc.Driver" />
                 <property name ="JDBC.ConnectionURL" value= "jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8" />
                 <property name ="JDBC.Username" value="root" />
                 <property name ="JDBC.Password" value="root" />
            </dataSource >
     </transactionManager >
     <sqlMap resource ="StudentMapper.xml" />
         
</sqlMapConfig>

4.创建StudentMapper.xml文件

<?xml version="1.0" encoding= "UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd" >

<sqlMap >
     <typeAlias alias ="Student" type= "com.igaming.entity.Student" />
     <!--插入操作 -->
     <insert id ="insert" parameterClass="Student">
           insert into Student(student_id, student_name, student_sex,
           student_birthday, class_id)
           values (#student_id#, #student_name#,
           #student_sex#, #student_birthday#,
           #class_id#)
            <selectKey resultClass ="java.lang.String" keyProperty="student_id" >
                select last_insert_id() as id
            </selectKey >
     </insert >

</sqlMap>

5.创建Student.java实例

public class Student {
     private String student_id;
     private String student_name;
     private String student_sex;
     private Date student_birthday;
     private String class_id;
     
     public Student() {   }

     public Student(String student_id, String student_name, String student_sex,Date student_birthday, String class_id) {
            this. student_id = student_id;
            this. student_name = student_name;
            this. student_sex = student_sex;
            this. student_birthday = student_birthday;
            this. class_id = class_id;
     }
         // ..........（注：此处省略get,set方法）
}

6.编写测试类：

Demo.java

 

public class Demo {
     static Reader rd = null;
     static SqlMapClient smc = null;
     /**
      * @param args
      * @throws IOException
      * @throws SQLException
      */
     public static void main(String[] args) throws IOException, SQLException {
            rd = Resources.getResourceAsReader("SqlMapConfig.xml");
            smc = SqlMapClientBuilder.buildSqlMapClient(rd);
           
            /* This would insert one record in Employee table. */
           System. out.println( "Going to insert record.....");
            //新增
            insetInfo();
           System. out.println( "Record Inserted Successfully ");

     }     
     /**
      * 新增
      * @throws SQLException
      */
     public static void insetInfo() throws SQLException{
           Student em = new Student( "10007", "张三", "男" , new Date(), "2");
            smc.insert( "insert", em);
     }
}

运行测试结果：

数据库：

 

查询操作：

在StudentMapper.xml配置文件中添加如下代码：

     <!-- 查询操作 -->
      <select id = "getAll" resultClass= "Student">
           select * from Student
      </select >

在Demo.java中编写查询方法：

      /**
      * 查询
      * @throws SQLException
      */
     public static void selectInfo() throws SQLException {
           List<Student> listStudentInfo = (List<Student>) smc.queryForList("getAll", null);          
            for (Student s : listStudentInfo) {
                     System. out.println(s.getStudent_id()+ "  "+s.getStudent_name()+"  "+s.getStudent_sex()+ "  "+s.getStudent_birthday()+"  "+s.getClass_id());
           }
     }

 

测试结果：

 

更新数据库数据：

 

在StudentMapper.xml配置文件中添加如下代码：

   <!-- ibatis更新操作 -->
     <update id ="updateById" parameterClass="java.lang.String">
           update Student set student_name=" Admol" where student_id=#student_id#
     </update >

在Demo.java中编写测试方法：

     /**
      * 更新操作
      * @throws SQLException
      */
     public static void updateById() throws SQLException{
            smc.update( "updateById", "10002"); 
     }  

测试结果如下：

 

删除操作：

 

在StudentMapper.xml配置文件中添加如下代码：

 <!-- 删除操作 -->
     <delete id ="deleteStudentByid" parameterClass="java.lang.String" >
           delete from Student where student_id=#student_id#
     </delete >

在Demo.java中编写测试方法：

   /**
      * 删除操作
      * @throws SQLException
      */
     public static void deleteStudentByid() throws SQLException{
            //删除 
      int i=smc.delete("deleteStudentByid", "10004"); 
      System.out.println("i="+i); 
     } 

 

测试结果如下：

 

注意:

1.插入到数据库中乱码解决办法为：

jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8

2.StudentMapper.xml中增删改查的id属性必须和Demo中方法insert() query() delete() update()中的值对应。

 

 

项目整体结构图: