Ibatis学习笔记

Ibatis学习笔记

最近学习了ibatis连接数据库

 

搭建iBatis开发环境：

仅仅需要两个包：数据库驱动包，和ibatis包。

三个配置文件：数据库连接属性文件、总配置文件、实体的映射文件（Map文件）。

数据库连接属性文件：jdbc.properties

内容如下：

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.01:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8
jdbc.username=test
jdbc.password=test123

总配置文件，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="jdbc.properties" />
	<transactionManager type="JDBC">
		<dataSource type="SIMPLE">
			<property name="JDBC.Driver" value="${jdbc.driverClassName}" />
			<property name="JDBC.ConnectionURL" value="${jdbc.url}" />
			<property name="JDBC.Username" value="${jdbc.username}" />
			<property name="JDBC.Password" value="${jdbc.password}" />
		</dataSource>
	</transactionManager>
	<sqlMap resource="UserDevice.xml" />
</sqlMapConfig>

主要两个功能：载入数据库连接；载入实体映射文件。

实体映射文件

内容如下：

<?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="UserDevice" type="com.cjh.test.UserDevice" />
	<select id="getAllUserDevice" resultClass="UserDevice">
		select *
		from user_device;
	</select>
	
	<select id="getUserDeviceById" resultClass="UserDevice">
		select *
		from user_device where id = #id#;
	</select>
	
	<select id="getAllTest" resultClass="java.util.HashMap">
		select *
		from test_test;
	</select>
	<!-- 上面描述的都是单表查询，这个是连表查询的例子 -->
	<select id="homePageList" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		SELECT 
		  v.`attribute_id` AS attributeId,
		  o.attribute_option_id AS attributeOptionId,
		  TRIM(ol.`attribute_option_name`) AS attributeOptionName,
		  v.`value_name` AS valueName,
		  r.`value_key` AS valueKey, 
		  l.`value_content` AS valueContent
		FROM
		  cosplay_display d 
		  JOIN t_attribute_option o 
		    ON d.option_id = o.attribute_option_id 
		    AND o.data_status = 0 
		  JOIN t_attribute_option_lang ol 
		    ON o.attribute_option_id = ol.attribute_option_id 
		    AND ol.data_status = 0 
		    AND ol.`language_id` = #languageId#
		  JOIN product_attribute_function_value v 
		    ON o.`product_attribute_id` = v.`attribute_id` 
		    AND o.attribute_option_id = v.option_id 
		    AND v.data_status = 0 
		    AND v.value_name IS NOT NULL 
		  JOIN product_attribute_function_value_lang l 
		    ON l.value_id = v.id 
		    AND l.data_status = 0 
		    AND l.value_content IS NOT NULL 
		    AND l.`language_id` = 1
		  JOIN product_attribute_function_rule r 
		    ON r.`id` = v.`rule_id` 
		    AND r.`data_status` = 0 
		   	<isNotEmpty prepend="and" property="key">
		    	r.`value_key` = #key#
		    </isNotEmpty>
		WHERE d.data_status = 0 
		  AND d.display_type = #displayType#
		ORDER BY d.`sort`
		LIMIT #returnNum#
	</select>
</sqlMap>

测试java类

实体类：

package com.cjh.test;
public class UserDevice {
	private String deviceId = "";
	private String deviceCompany = "";
	private String deviceType = "";

	public String getDeviceId() {
		return deviceId;
	}

	public void setDeviceId(String deviceId) {
		this.deviceId = deviceId;
	}

	public String getDeviceCompany() {
		return deviceCompany;
	}

	public void setDeviceCompany(String deviceCompany) {
		this.deviceCompany = deviceCompany;
	}

	public String getDeviceType() {
		return deviceType;
	}

	public void setDeviceType(String deviceType) {
		this.deviceType = deviceType;
	}

	@Override
	public String toString() {
		// TODO Auto-generated method stub
		// return super.toString();
		return "device_id:" + deviceId + "，device_company:" + deviceCompany
				+ "，device_type" + deviceType;
	}

}

 

调用类： 

package com.cjh.test;
import java.io.*;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.*;
public class IBatisDemo {
	public static void main(String[] args) throws IOException, SQLException {
		String config = "SqlMapConfig.xml";
		Reader reader = Resources.getResourceAsReader(config);
		SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
		
//		List<UserDevice> list = sqlMap.queryForList("getAllUserDevice");
		List list = sqlMap.queryForList("getAllTest");
//		List<UserDevice> list = sqlMap.queryForList("getUserDeviceById", 8);
		
		for (Object ud : list) {
			Map map = (Map)ud;
			System.out.println(map.get("xyz"));
		}
	}
}