使用Ibatis操作MySQL数据库

水深无声 2022-08-05 01:24 256阅读 0赞

iBatis 是apache 的一个开源项目，一个O/R Mapping 解决方案，iBatis 最大的特点就是小巧，上手很快。如果不需要太多复杂的功能，iBatis 是能够满足你的要求又足够灵活的最简单的解决方案，现在的iBatis 已经改名为Mybatis 了。

使用Ibatis操作数据库，我们的操作对象依然是student 表，创建语句：

CREATE TABLE student(  
        ID VARCHAR(5),  
        name VARCHAR(20),  
        age int(3),  
        FM VARCHAR(1),  
        PRIMARY KEY(ID)  
    )

创建之后结果如下图：  
![SouthEast][]

有了数据库之后，我们同样需要创建一个Student类，以描述该对象。

package NetEase.ibatistest;
    
    public class Student{
    	private String ID;
    	private String name;
    	private int age;
    	private String FM;
    	
    	public Student(){		
    	}
    	
    	public Student(String ID,String name,int age, String FM){
    		this.ID = ID;
    		this.name = name;
    		this.age = age;
    		this.FM = FM;
    	}
    	
    	public void setID(String ID){
    		this.ID = ID;
    	}
    	
    	public String getID(){
    		return this.ID;
    	}
    
    	public void setname(String name){
    		this.name = name;
    	}
    	
    	public String getname(){
    		return this.name;
    	}
    
    	public void setage(int age){
    		this.age = age;
    	}
    	
    	public int getage(){
    		return age;
    	}
    	
    	public void setFM(String FM){
    		this.FM = FM;
    	}
    	
    	public String getFM(){
    		return this.FM;
    	}	
    	
    	public void display(){
    		System.out.println(ID + " " + name + " " + age + " " + FM);
    	}
    	
    	
    }

接下来需要配置数据源，我们先准备一个SQLMap.properties文件

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8
    username=root
    password=snow

接下来配置用于操作数据库的Student.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="NetEase.ibatistest.Student" />
    
    	<insert id="addstudent" parameterClass="Student">
    		INSERT INTO class.student(ID,name,age,FM)VALUES(#ID#,#name#,#age#,#FM#)
    	</insert>	
    	
    	<delete id="delstudentbyID" parameterClass="String">
    		DELETE FROM class.student WHERE ID=#ID#
    	</delete>
    	
    	<delete id="delstudentbyname" parameterClass="String">
    		DELETE FROM class.student WHERE name=#name#
    	</delete>
    	
    	<delete id="delallstudent">
    		DELETE FROM class.student
    	</delete>
    	
    	<update id="updatestudent" parameterClass="Student">
    		UPDATE class.student SET name=#name#,age=#age#,FM=#FM# WHERE ID=#ID#
    	</update>	
    	
    	<select id="allstudent" resultClass="Student" >
    		SELECT * FROM class.student
    	</select>
     		
    	<select id="querybyID" resultClass="Student">
    		SELECT * FROM class.student WHERE ID=#ID#
    	</select>
    	
    	<select id="querybyname" resultClass="Student">
    		SELECT * FROM class.student WHERE name=#name#
    	</select>
    	
    	
    	<resultMap id="result" class="Student">
    		<result property="ID" column="ID" />
    		<result property="name" column="name" />
    		<result property="age" column="age" />
    		<result property="FM" column="FM" />	
    	</resultMap>
    		
    	<select id="querybyage" resultMap="result">
    		SELECT * FROM class.student WHERE age=#age#
    	</select>
    	
     
    </sqlMap>

有了Student.xml和SQLMap.properties两个配置文件之后，我们就可以配置数据库源了  
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>
    	<properties resource="NetEase/ibatistest/SQLMap.properties"/>
    	
        <settings 
        	enhancementEnabled="true" 
        	maxTransactions="32"
            maxRequests="512" 
            maxSessions="128" 
            cacheModelsEnabled="true"
            lazyLoadingEnabled="false" 
            statementCachingEnabled="false"
            useStatementNamespaces="true" />
        
        
        <transactionManager type="JDBC">
            
            <dataSource type="SIMPLE">
                <property name="JDBC.Driver" value="${driver}" />
                <property name="JDBC.ConnectionURL" value="${url}" />
                <property name="JDBC.Username" value="${username}" />
                <property name="JDBC.Password" value="${password}" />
            </dataSource>
        </transactionManager>
        
        <sqlMap resource="NetEase/ibatistest/Student.xml" />  
    		
    </sqlMapConfig>

与Spring中的JDBC框架类似，Ibatis也是用DAO实现数据库操作的。

StudentDao.java

package NetEase.ibatistest;
    
    import java.util.List;
    
    public interface StudentDao{
    	
    	public boolean addstudent(Student student);
    	
    	public boolean delstudentbyID(String ID);
    	
    	public boolean delstudentbyname(String name);
    	
    	public boolean delallstudent();
    	
    	public boolean updstudent(Student student);
    	
    	public List<Student> allstudent();
    	
    	public Student querystudentbyID(String ID);
    	
    	public List<Student> querystudentbyname(String name);
    	
    	public List<Student> querystudentbyage(int age);
    	
    }

接口需要实现 StudentDaoImp.java

package NetEase.ibatistest;
    
    import java.io.IOException;
    import java.io.Reader;
    import java.sql.SQLException;
    import java.util.List;
    
    import com.ibatis.common.resources.Resources;
    import com.ibatis.sqlmap.client.SqlMapClient;
    import com.ibatis.sqlmap.client.SqlMapClientBuilder;
    
    
    
    public class StudentDaoImp implements StudentDao{
    	private static SqlMapClient sqlmapclient = null;
    	
    	// 读取配置文件
    	static{
    		try{
    			Reader reader = Resources
    					.getResourceAsReader("NetEase/ibatistest/SQLMapConfig.xml");
    			sqlmapclient = SqlMapClientBuilder.buildSqlMapClient(reader);
    			reader.close();					
    		}catch(IOException e){
    			e.printStackTrace();
    		}		
    	}
    
    	public boolean addstudent(Student student) {
    		Object object = null;
    		boolean flag = false;
    		try{
    			object = sqlmapclient.insert("addstudent", student);
    			System.out.println("return:" + object);
    		}catch(SQLException e){
    			e.printStackTrace();
    		}
    		if(object != null){
    			flag = true;
    		}
    		return flag;
    	}
    
    	public boolean delstudentbyID(String ID) {
    		Object object = null;
    		boolean flag = false;
    		try{
    			object = sqlmapclient.delete("delstudentbyID",ID);
    			System.out.println("return:" + object + "行受操作影响");
    		}catch(SQLException e){
    			e.printStackTrace();
    		}		
    		if(object != null){
    			flag = true;
    		}
    		return flag;
    	}
    
    	public boolean delstudentbyname(String name) {
    		Object object = null;
    		boolean flag = false;
    		try{
    			object = sqlmapclient.delete("delstudentbyname",name);
    			System.out.println("return:" + object + "行受操作影响");
    		}catch(SQLException e){
    			e.printStackTrace();
    		}		
    		if(object != null){
    			flag = true;
    		}
    		return flag;
    	}
    	
    	public boolean delallstudent() {
    		Object object = null;
    		boolean flag = false;
    		try{
    			object = sqlmapclient.delete("delallstudent");			
    //			sqlmapclient.delete("delallstudent", object);
    			System.out.println("return:" + object + "行受操作影响");
    		}catch(SQLException e){
    			e.printStackTrace();
    		}
    		if(object != null){
    			flag = true;
    		}
    		return flag;
    	}	
    
    	public boolean updstudent(Student student) {
    		Object object = null;
    		boolean flag = false;
    		try{
    			object = sqlmapclient.update("updatestudent", student);
    			System.out.println("return:" + object + "行受操作影响");
    		}catch(SQLException e){
    			e.printStackTrace();
    		}
    		if(object != null){
    			flag = true;
    		}
    		return flag;
    	}
    
    	public List<Student> allstudent() {
    		Object object = null;
    		List<Student> students = null;
    		try {
    			students = sqlmapclient.queryForList("allstudent", object);
    			
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return students;
    	}
    
    	public Student querystudentbyID(String ID) {
    		Student student = null;
    		try {
    			student = (Student) sqlmapclient.queryForObject("querybyID", ID);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return student;
    	}
    
    	//会有名字相同的情况！
    	public List<Student> querystudentbyname(String name) {
    		List<Student> students = null;
    		try {
    			students = sqlmapclient.queryForList("querybyname", name);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return students;
    	}
    	
    	public void displaydata(){
    		List<Student> students = allstudent();
    		for (Student s : students){
    			s.display();
    		}	
    	}
    
    	public List<Student> querystudentbyage(int age) {
    		List<Student> students = null;
    		try {
    			students = sqlmapclient.queryForList("querybyage", age);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return students;
    	}
    	
    }

最后依然是一个测试文件Maintest.java

package NetEase.ibatistest;
    
    import java.util.List;
    
    public class Maintest{
    	public static void main(String [] args){
    		StudentDaoImp studentdaoimp = new StudentDaoImp();
    		String[] ID = { "2008", "2009", "2010", "1990", "2015","2018" };
    		String[] name = { "Wang", "Hui", "Yu", "Yuan", "Yuan", "Yang"};
    		int[] age = { 16, 18, 20, 20, 22, 21 };
    		String[] FM = {"F", "F", "M", "M", "M", "F"};
    		
    		Student student = null;
    		List<Student>students = null;		
    		
    		
    		System.out.println("-----------addstudent-----------");
    		for(int i=0; i<ID.length; i++)
    		{
    			student = new Student(ID[i],name[i],age[i],FM[i]);
    			studentdaoimp.addstudent(student);
    		}
    		studentdaoimp.displaydata();
    		
    		System.out.println("-----------deletestudentbyID-----------");
    		studentdaoimp.delstudentbyID("2018");
    		studentdaoimp.displaydata();
    		
    		System.out.println("-----------deletestudentbyname-----------");
    		studentdaoimp.delstudentbyname("Wang");
    		studentdaoimp.displaydata();
    		
    		System.out.println("-----------updatestudent-----------");
    		student = new Student("2015","Yuan",18,"M");
    		studentdaoimp.updstudent(student);
    		studentdaoimp.displaydata();
    		
    		System.out.println("-----------querybyID-----------");
    		student = studentdaoimp.querystudentbyID("1990");
    		student.display();
    		
    		System.out.println("-----------querybyname-----------");
    		students = studentdaoimp.querystudentbyname("Yuan");
    		for(Student i : students){
    			i.display();
    		}		
    
    		System.out.println("-----------querybyage-----------");
    		students = studentdaoimp.querystudentbyage(18);
    		for(Student i : students){
    			i.display();
    		}			
    		
    		System.out.println("-----------deletealldstudent-----------");
    		studentdaoimp.delallstudent();
    		
    	}
    }

Maven项目依赖库pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" 
    		 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      		 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
    
      <groupId>NetEase</groupId>
      <artifactId>ibatistest</artifactId>
      <version>0.0.1-SNAPSHOT</version>
      <packaging>jar</packaging>
    
      <name>ibatistest</name>
      <url>http://maven.apache.org</url>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
      </properties>
    
      <dependencies>
        <dependency>
        	<groupId>junit</groupId>
        	<artifactId>junit</artifactId>
        	<version>3.8.1</version>
        	<scope>test</scope>
        </dependency>
       
    	<dependency>
    		<groupId>mysql</groupId>
    		<artifactId>mysql-connector-java</artifactId>
    		<version>5.1.35</version>
    	</dependency>
    
    	
    	
    	<dependency>
    		<groupId>org.apache.ibatis</groupId>
    		<artifactId>ibatis-sqlmap</artifactId>
    		<version>2.3.4.726</version>
    	</dependency>
    	
    
      </dependencies>
    </project>

程序运行结果：

-----------addstudent-----------
    return:null
    return:null
    return:null
    return:null
    return:null
    return:null
    1990 Yuan 20 M
    2008 Wang 16 F
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 22 M
    2018 Yang 21 F
    -----------deletestudentbyID-----------
    return:1行受操作影响
    1990 Yuan 20 M
    2008 Wang 16 F
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 22 M
    -----------deletestudentbyname-----------
    return:1行受操作影响
    1990 Yuan 20 M
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 22 M
    -----------updatestudent-----------
    return:1行受操作影响
    1990 Yuan 20 M
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 18 M
    -----------querybyID-----------
    1990 Yuan 20 M
    -----------querybyname-----------
    1990 Yuan 20 M
    2015 Yuan 18 M
    -----------querybyage-----------
    2009 Hui 18 F
    2015 Yuan 18 M
    -----------deletealldstudent-----------
    return:4行受操作影响

本文参考文献：

[http://www.cnblogs.com/ycxyyzw/archive/2012/10/13/2722567.html][http_www.cnblogs.com_ycxyyzw_archive_2012_10_13_2722567.html]

[SouthEast]: /images/20220805/48dc758b24934224a55bd018020d24ce.png
[http_www.cnblogs.com_ycxyyzw_archive_2012_10_13_2722567.html]: http://www.cnblogs.com/ycxyyzw/archive/2012/10/13/2722567.html