[code]
一旦简单了就易于维护,代码量也就少了,效率就提高,成本就降低了,老板就笑了
缺点:参数只能带一个,sql还要自己写
与jdbc相比:
减少61%的代码量,简单,性能增强,sql与代码分离
简化了项目分工,增强移植性
Student.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-2.dtd">
<sqlMap>
<typeAlias alias="Student" type="com.zwz.Student"/>
<select id="selectAllStudent" resultClass="Student">
select *
from student
</select>
<select id="selectStudentById" parameterClass="int" resultClass="Student">
select *
from student
where sid=#sid#
</select>
<insert id="insertStudent" parameterClass="Student">
insert into Student(sid,sname,major,birth,score)
values(#sid#,#sname#,#major#,#score#)
</insert>
<delete id="deleteStudentById" parameterClass="int">
delete
from Student
where sid=#sid#
</delete>
<update id="updateStudentById" parameterClass="Student">
update Student
set
major=#major#,
sname=#sname#,
birth=#birth#,
score=#score#
where
sid=#sid#
</update>
<select id="selectStudentByName" parameterClass="String" resultClass="Student">
select sid,sname,major,birth,score
from Student
where sname like '%$sname$%'
</select>
<insert id="insertStudentBySequence" parameterClass="Student">
<selectKey resultClass="int" keyProperty="sid">
select studentPKSequence.nextVal
from dual
</selectKey>
insert into Student(sid,sname,birth,major,score)
values(#sid#,#sname#,#birth#,#major#,#score#)
</insert>
<insert id="addWebSite" parameterClass="WebSite">
insert into website(site_name, url,emplid) values (#siteName#,#url#,#emplId#)
<selectKey resultClass="java.lang.Integer" keyProperty="id">
SELECT
@@IDENTITY AS ID
</selectKey>
</insert>
<parameterMap class="pro" id="java.util.Map">
<parameter property="name" jdbcType="VARCHAR"
javaType="string" mode="IN"/>
</parameterMap>
<procedure id="pro_insert" parameterMap="pro">
{call new_proc(?)}
</procedure>
</sqlMap>
<isNotNull property="queryString" prepend=" AND ">
(e.NAME LIKE '%$queryString$%'
OR e.CHINESE_NICKNAME LIKE '%$queryString$%'
OR e.EMPLID LIKE '%$queryString$%'
OR e.TAOBAO_WANGWANG LIKE '%$queryString$%'
OR e.EMAIL LIKE '%$queryString$%'
OR e.ALI_WANGWANG LIKE '%$queryString$%')
</isNotNull>
ORDER BY ur.id DESC
<isNull property="queryString">
LIMIT #pageSize# offset #startRow#
</isNull>
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>
<!-- Configure a built-in transaction manager. If you're using an
app server, you probably want to use its transaction manager
and a managed datasource -->
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="org.hsqldb.jdbcDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:hsqldb:."/>
<property name="JDBC.Username" value="sa"/>
<property name="JDBC.Password" value="sa"/>
</dataSource>
</transactionManager>
<!-- List the SQL Map XML files. They can be loaded from the
classpath, as they are here (com.domain.data...) -->
<sqlMap resource="com/mydomain/data/Account.xml"/>
<!-- List more here...
<sqlMap resource="com/mydomain/data/Order.xml"/>
<sqlMap resource="com/mydomain/data/Documents.xml"/>
-->
</sqlMapConfig>
<insert id="INSERT-SPM-PAGE-ACLS" parameterClass="java.util.List">
<![CDATA[
insert into T_SPM_SITE_PAGE_ACL(PAGE_ID, USER_ID, USER) values
]]>
<iterate conjunction=",">
<![CDATA[
(#list[].pageId#, #list[].userId#, #list[].userName#)
]]>
</iterate>
</insert>
e.EMPLID in
<iterate property="employeeIdList" open="(" close=")" conjunction=",">
#employeeIdList[]#
</iterate>
注意 报错就这么加cdata
<select id="getStarName" resultClass="java.util.Map" parameterClass="java.util.List">
<![CDATA[
SELECT classify_level_id,star_name from bdc_spm_dim_star
where classify_level_id in
]]>
<iterate open="(" close=")" conjunction=",">
<![CDATA[
#list[].levelId#
]]>
</iterate>
</select>
<select id="get_emplId_by_email_account" parameterClass="list" resultClass="java.util.HashMap">
<![CDATA[
select emplid,email from t_employee
where email in
]]>
<iterate open="(" close=")" conjunction=",">
<![CDATA[
#[]#
]]>
</iterate>
</select>
sqlMapClient.queryForMap("person.get_emplId_by_email_account", accounts,
"emplid");
StudentDaoImpl.java
package com.zwz;
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 StudentDaoImpl implements IStudentDao {
public static SqlMapClient sqlMapClient = null;
static {
try {
Reader reader = Resources.getResourceAsReader("com/zwz/SqlMapConfig.xml");
sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public void addStudent(Student student) {
try {
sqlMapClient.insert("insertStudent", student);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void addStudentBySequence(Student student) {
// TODO Auto-generated method stub
}
public void deleteStudentById(int id) {
try {
sqlMapClient.delete("deleteStudentById",id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public List<Student> queryAllStudent() {
List<Student> s = null;
try {
s = sqlMapClient.queryForList("selectAllStudent");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return s;
}
public Student queryStudentById(int id) {
Student student = null;
try {
student = (Student) sqlMapClient.queryForObject("selectStudentById",id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
public List<Student> queryStudentByName(String name) {
List<Student> studentList = null;
try {
studentList = sqlMapClient.queryForList("selectStudentByName", name);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public void updateStudentById(Student student) {
try {
sqlMapClient.update("updateStudentById",student);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void goPro(){
Map m = new HashMap(1);
m.put("name",new String("LISI"));
sqlMap.update("pro_insert",m);
}
public void batchSql(){
Account c1 = new Account();
c1.setUsername("zhangsan");
c1.setPassword("123");
c1.setGroupname("CN");
Account c2 = new Account();
c2.setUsername("lisi");
c2.setPassword("nanjing");
c2.setGroupname("cn");
sqlMap.startBatch();
sqlMap.insert("insertOneUser3",c1);
sqlMap.insert("insertOneUser3",c2);
sqlMap.executeBatch();
}
public void insertPageAcls(List<SpmPageAcl> pageAcls) throws DAOException {
try {
sqlMapClient.insert("INSERT-SPM-PAGE-ACLS", pageAcls);
} catch (SQLException e) {
throw new DAOException("insertSpmPageError", e);
}
}
public java.util.Map queryForMap(java.lang.String id,
java.lang.Object parameterObject,
java.lang.String keyProp)throws java.sql.SQLException
//该方法有三个参数,第一个是statement,第二个是查询入参,第三个是返回的Map集合的key属性。
public Map<String, User> getUserMap2() throws SQLException
{
return sqlMapClient.queryForMap("User.queryForMap2", null, "userID");
}
//Dao里面有这样两个方法,第一个作用就是查询出所有的User对象,以User对象的userID为key存放到Map集合中返回。
}
[/code]
一旦简单了就易于维护,代码量也就少了,效率就提高,成本就降低了,老板就笑了
缺点:参数只能带一个,sql还要自己写
与jdbc相比:
减少61%的代码量,简单,性能增强,sql与代码分离
简化了项目分工,增强移植性
Student.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-2.dtd">
<sqlMap>
<typeAlias alias="Student" type="com.zwz.Student"/>
<select id="selectAllStudent" resultClass="Student">
select *
from student
</select>
<select id="selectStudentById" parameterClass="int" resultClass="Student">
select *
from student
where sid=#sid#
</select>
<insert id="insertStudent" parameterClass="Student">
insert into Student(sid,sname,major,birth,score)
values(#sid#,#sname#,#major#,#score#)
</insert>
<delete id="deleteStudentById" parameterClass="int">
delete
from Student
where sid=#sid#
</delete>
<update id="updateStudentById" parameterClass="Student">
update Student
set
major=#major#,
sname=#sname#,
birth=#birth#,
score=#score#
where
sid=#sid#
</update>
<select id="selectStudentByName" parameterClass="String" resultClass="Student">
select sid,sname,major,birth,score
from Student
where sname like '%$sname$%'
</select>
<insert id="insertStudentBySequence" parameterClass="Student">
<selectKey resultClass="int" keyProperty="sid">
select studentPKSequence.nextVal
from dual
</selectKey>
insert into Student(sid,sname,birth,major,score)
values(#sid#,#sname#,#birth#,#major#,#score#)
</insert>
<insert id="addWebSite" parameterClass="WebSite">
insert into website(site_name, url,emplid) values (#siteName#,#url#,#emplId#)
<selectKey resultClass="java.lang.Integer" keyProperty="id">
SELECT
@@IDENTITY AS ID
</selectKey>
</insert>
<parameterMap class="pro" id="java.util.Map">
<parameter property="name" jdbcType="VARCHAR"
javaType="string" mode="IN"/>
</parameterMap>
<procedure id="pro_insert" parameterMap="pro">
{call new_proc(?)}
</procedure>
</sqlMap>
<isNotNull property="queryString" prepend=" AND ">
(e.NAME LIKE '%$queryString$%'
OR e.CHINESE_NICKNAME LIKE '%$queryString$%'
OR e.EMPLID LIKE '%$queryString$%'
OR e.TAOBAO_WANGWANG LIKE '%$queryString$%'
OR e.EMAIL LIKE '%$queryString$%'
OR e.ALI_WANGWANG LIKE '%$queryString$%')
</isNotNull>
ORDER BY ur.id DESC
<isNull property="queryString">
LIMIT #pageSize# offset #startRow#
</isNull>
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>
<!-- Configure a built-in transaction manager. If you're using an
app server, you probably want to use its transaction manager
and a managed datasource -->
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="org.hsqldb.jdbcDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:hsqldb:."/>
<property name="JDBC.Username" value="sa"/>
<property name="JDBC.Password" value="sa"/>
</dataSource>
</transactionManager>
<!-- List the SQL Map XML files. They can be loaded from the
classpath, as they are here (com.domain.data...) -->
<sqlMap resource="com/mydomain/data/Account.xml"/>
<!-- List more here...
<sqlMap resource="com/mydomain/data/Order.xml"/>
<sqlMap resource="com/mydomain/data/Documents.xml"/>
-->
</sqlMapConfig>
<insert id="INSERT-SPM-PAGE-ACLS" parameterClass="java.util.List">
<![CDATA[
insert into T_SPM_SITE_PAGE_ACL(PAGE_ID, USER_ID, USER) values
]]>
<iterate conjunction=",">
<![CDATA[
(#list[].pageId#, #list[].userId#, #list[].userName#)
]]>
</iterate>
</insert>
e.EMPLID in
<iterate property="employeeIdList" open="(" close=")" conjunction=",">
#employeeIdList[]#
</iterate>
注意 报错就这么加cdata
<select id="getStarName" resultClass="java.util.Map" parameterClass="java.util.List">
<![CDATA[
SELECT classify_level_id,star_name from bdc_spm_dim_star
where classify_level_id in
]]>
<iterate open="(" close=")" conjunction=",">
<![CDATA[
#list[].levelId#
]]>
</iterate>
</select>
<select id="get_emplId_by_email_account" parameterClass="list" resultClass="java.util.HashMap">
<![CDATA[
select emplid,email from t_employee
where email in
]]>
<iterate open="(" close=")" conjunction=",">
<![CDATA[
#[]#
]]>
</iterate>
</select>
sqlMapClient.queryForMap("person.get_emplId_by_email_account", accounts,
"emplid");
StudentDaoImpl.java
package com.zwz;
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 StudentDaoImpl implements IStudentDao {
public static SqlMapClient sqlMapClient = null;
static {
try {
Reader reader = Resources.getResourceAsReader("com/zwz/SqlMapConfig.xml");
sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public void addStudent(Student student) {
try {
sqlMapClient.insert("insertStudent", student);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void addStudentBySequence(Student student) {
// TODO Auto-generated method stub
}
public void deleteStudentById(int id) {
try {
sqlMapClient.delete("deleteStudentById",id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public List<Student> queryAllStudent() {
List<Student> s = null;
try {
s = sqlMapClient.queryForList("selectAllStudent");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return s;
}
public Student queryStudentById(int id) {
Student student = null;
try {
student = (Student) sqlMapClient.queryForObject("selectStudentById",id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
public List<Student> queryStudentByName(String name) {
List<Student> studentList = null;
try {
studentList = sqlMapClient.queryForList("selectStudentByName", name);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public void updateStudentById(Student student) {
try {
sqlMapClient.update("updateStudentById",student);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void goPro(){
Map m = new HashMap(1);
m.put("name",new String("LISI"));
sqlMap.update("pro_insert",m);
}
public void batchSql(){
Account c1 = new Account();
c1.setUsername("zhangsan");
c1.setPassword("123");
c1.setGroupname("CN");
Account c2 = new Account();
c2.setUsername("lisi");
c2.setPassword("nanjing");
c2.setGroupname("cn");
sqlMap.startBatch();
sqlMap.insert("insertOneUser3",c1);
sqlMap.insert("insertOneUser3",c2);
sqlMap.executeBatch();
}
public void insertPageAcls(List<SpmPageAcl> pageAcls) throws DAOException {
try {
sqlMapClient.insert("INSERT-SPM-PAGE-ACLS", pageAcls);
} catch (SQLException e) {
throw new DAOException("insertSpmPageError", e);
}
}
public java.util.Map queryForMap(java.lang.String id,
java.lang.Object parameterObject,
java.lang.String keyProp)throws java.sql.SQLException
//该方法有三个参数,第一个是statement,第二个是查询入参,第三个是返回的Map集合的key属性。
public Map<String, User> getUserMap2() throws SQLException
{
return sqlMapClient.queryForMap("User.queryForMap2", null, "userID");
}
//Dao里面有这样两个方法,第一个作用就是查询出所有的User对象,以User对象的userID为key存放到Map集合中返回。
}
[/code]