select seqname.nextval from dual 查询序列下一值
主键用序列来建立
1.先新建一个序列SEQ_RD_CI_APPSUBSYSTEM_ID
<selectKey resultClass="Integer" keyProperty="id"> //将值赋值给id
select SEQ_RD_CI_APPSUBSYSTEM_ID.currval from dual
</selectKey>
< selectKey> 来支持主键自动生成
keyProperty="id" 定义了主键名称 resultClass="Integer" 定义返回类型
在此处也可以将#sid#改为studentPKSequence.nextVal来实现同样的效果
<sqlMap namespace="changeReport">
<!-- 代表的是resultclass的简写 -->
<typeAlias alias="ChgDto" type="com.readysoft.report.change.model.ChgDto" />
<!-- 变更状态报表
Map<String, Object> condition = new HashMap<String, Object>();
condition.put("mt1", mt1);
condition.put("mt2", mt2);
List<ChgDto> chg_list=changeReportService.ChangeStatus(condition); 需要num和status -->
<select id="changeStatus" resultClass="ChgDto" parameterClass="Map">
select count(chg.id)num, sta.sym status
from RD_CHG chg,RD_CHGSTAT sta
where chg.status=sta.code
and chg.OPEN_DATE between #mt1# and #mt2#
group by sta.sym
</select>
<!--
OPEN_DATE
因为chg的open_date都为空 所以改为LAST_MOD_DT,
因为后者有时间 LAST_MOD_DT有时间-->
<!-- 关闭变更 ChgDto.setMt1(mt1); ChgDto.setMt2(mt2);-->
<select id="changeClosed" resultClass="Integer" parameterClass="ChgDto">
select count(chg.id) num
from RD_CHG chg,RD_CHGSTAT sta
where chg.status=sta.code
and chg.status='Closed'
and chg.CLOSE_DATE between #mt1# and #mt2#
</select>
package com.car.dao;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.car.dto.CarInfoDTO;
import com.car.dto.CarValueDTO;
import com.car.jdbc.DB;
import com.ibatis.sqlmap.client.SqlMapClient;
public class CarValueDAOimpl_ibatis implements CarValueDAO {
public static SqlMapClient sqlMapClient=null;
static{
Reader reader;
try {
reader = com.ibatis.common.resources.Resources.getResourceAsReader("SqlMapConfig.xml");
sqlMapClient=com.ibatis.sqlmap.client.SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void add(CarValueDTO carvaluedto) {
}
public void delete(int num) {
}
public List<CarValueDTO> findAll() {
List<CarValueDTO> carList=null;
try {
carList=sqlMapClient.queryForList("car.selectAllCar");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return carList;
}
public CarValueDTO findBynum(int num) {
// List<CarValueDTO> carList=null;
CarValueDTO carValueDTO=null;
try {
carValueDTO=(CarValueDTO) sqlMapClient.queryForObject("car.selectcarBynum", num);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return carValueDTO;
}
public void update(CarValueDTO carvaluedto) {
}
public static void main(String[] args) {
List<CarValueDTO> car_list= new CarValueDAOimpl_ibatis().findAll();
/* for (int i = 0; i < car_list.size(); i++) {
System.out.println(car_list.get(i).getCarnum());
}
for(CarValueDTO carValueDTO:new CarValueDAOimpl_ibatis().findAll()){
System.out.println(carValueDTO.getCarnum());
}*/
CarValueDTO carValueDTO = new CarValueDAOimpl_ibatis().findBynum(10);
System.out.println(carValueDTO.getCarnum()+" "+carValueDTO.getCost());
}
}
<?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 namespace="car">
<!-- typeAlias 给类取别名 -->
<typeAlias alias="Car" type="com.car.dto.CarValueDTO" />
<!-- 返回对象类型的数据 -->
<select id="selectAllCar" resultClass="Car">
select * from carvalue
</select>
<select id="selectcarBynum" parameterClass="Integer" resultClass="Car">
select * from carvalue where carnum=#carnum#
</select>
<insert id="">
</insert>
</sqlMap>
<?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 namespace="changeReport">
<!-- 代表的是resultclass的简写 -->
<typeAlias alias="ChgDto" type="com.readysoft.report.change.model.ChgDto" />
<!-- 变更状态报表
需要num和status -->
<select id="changeStatus" resultClass="ChgDto" parameterClass="Map">
select count(chg.id)num, sta.sym status
from RD_CHG chg,RD_CHGSTAT sta
where chg.status=sta.code
and chg.OPEN_DATE between #mt1# and #mt2#
group by sta.sym
</select>
<!--
OPEN_DATE
因为chg的open_date都为空 所以改为LAST_MOD_DT,
因为后者有时间 LAST_MOD_DT有时间-->
<!-- 关闭变更 -->
<select id="changeClosed" resultClass="Integer" parameterClass="ChgDto">
select count(chg.id) num
from RD_CHG chg,RD_CHGSTAT sta
where chg.status=sta.code
and chg.status='Closed'
and chg.CLOSE_DATE between #mt1# and #mt2#
</select>
<!-- 新建变更 -->
<select id="changeCreated" resultClass="Integer" parameterClass="ChgDto">
select count(chg.id) num1
from RD_CHG chg
where chg.OPEN_DATE between #mt1# and #mt2#
</select>
<!--变更分类 所有的 -->
<select id="changeType" resultClass="ChgDto" >
select count(chg.id) num,ctg.SYM
from RD_CHG chg,RD_CHG_CATEGORY ctg
where chg.Z_CHG_CATEGORY=ctg.ID
group by ctg.SYM
</select>
<!-- 变更分类 -->
<!-- 网络设备 -->
<select id="changeType1" resultClass="Integer" parameterClass="ChgDto">
select count(chg.id) num
from RD_CHG chg,RD_CHG_CATEGORY ctg
where chg.Z_CHG_CATEGORY=ctg.ID
where ctg.ID=61
and chg.OPEN_DATE between #mt1# and #mt2#
group by ctg.SYM
</select>
<!-- 软件产品 -->
<select id="changeType2" resultClass="Integer" parameterClass="ChgDto">
select count(chg.id) num
from RD_CHG chg,RD_CHG_CATEGORY ctg
where chg.Z_CHG_CATEGORY=ctg.ID
where ctg.ID=163
and chg.OPEN_DATE between #mt1# and #mt2#
group by ctg.SYM
</select>
<!-- 主机设备 -->
<select id="changeType3" resultClass="Integer" parameterClass="ChgDto">
select count(chg.id) num
from RD_CHG chg,RD_CHG_CATEGORY ctg
where chg.Z_CHG_CATEGORY=ctg.ID
where ctg.ID=165
and chg.OPEN_DATE between #mt1# and #mt2#
group by ctg.SYM
</select>
</sqlMap>