//1.我们新插入一批数据,要求携带所有的五列(插入操作)
//2 对我们表中用户ID号,为自己学习号的姓名进行修改,修改的新姓名的值为姓名加学号(修改操作)
//3.遍历结果集,对表中所有数据进行查看,将每个人的五个信息逐行打在控制台上(查询操作)
//4.查询所有的结果中的年龄一象,计算我们现在人员的平均年龄(查询操作)
建表:
create table XXXX(
id varchar2(40) not null,
no Integer not null,
name varchar2(40) not null,
password varchar2(40) not null,
age Integer not null
)
插入数据:自己提交(我用PLSQL Developer)上有,
insert into XXXX values('2',2,'小红','112',15);
insert into XXXX values('3',3,'小绿','113',16);
insert into XXXX values('4',4,'小黑','114',24);
insert into XXXX values('5',5,'老王','115',34);
insert into XXXX values('6',6,'老刘','116',44);
insert into XXXX values('7',7,'老路','117',54);
insert into XXXX values('8',8,'生说','118',14);
insert into XXXX values('9',9,'是红','119',15);
自己直接commit就行了
2 搭建环境 同ibatis(基本增删改查)这里不具体写了
4
4 加配置文件 在src/resouce下
{1}--------------sql-map-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
errorTracingEnabled="true"
maxRequests="32"
maxSessions="10"
maxTransactions="5"
useStatementNamespaces="false" />
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver"
value="oracle.jdbc.driver.OracleDriver" />
<property name="JDBC.ConnectionURL"
value="jdbc:oracle:thin:@192.168.137.23:1521:unieap" />
<property name="JDBC.Username" value="tabuser" />
<property name="JDBC.Password" value="tabuser" />
<property name="Pool.MaximumActiveConnections" value="10" />
<property name="Pool.MaximumIdleConnections" value="5" />
<property name="Pool.MaximumCheckoutTime" value="120000" />
<property name="Pool.TimeToWait" value="500" />
<property name="Pool.PingQuery" value="select 1 from dual" />
<property name="Pool.PingEnabled" value="false" />
<property name="Pool.PingConnectionsOlderThan" value="1" />
<property name="Pool.PingConnectionsNotUsedFor" value="1" />
</dataSource>
</transactionManager>
<sqlMap resource="person.xml" />
</sqlMapConfig>
{ 2}--------------person.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="Person">
<typeAlias alias="person" type="cm.neusoft.test.ibatis.pojo.Person" />
<insert id="insertPerson" parameterClass="person">
INSERT INTO XXXX (id,no,name,password,age) VALUES (#id#,#no#, #name#,#password#,#age# )
</insert>
<update id="updatePerson" parameterClass="person">
<![CDATA[
UPDATE XXXX SET id=#id#,no=#no#,name=#name#,password=#password#,age=#age#
WHERE id = #id#
]]>
</update>
<select id="getPerson" parameterClass="java.lang.String" resultClass="person">
<![CDATA[
SELECT id,no,name,password,age FROM XXXX
]]>
</select>
<select id="getPersonAge" parameterClass="java.lang.String" resultClass="int">
<![CDATA[
SELECT sum(age) FROM XXXX
]]>
</select>
<select id="getPersonss" parameterClass="java.lang.String" resultClass="int" >
<![CDATA[
SELECT count(*) FROM XXXX
]]>
</select>
</sqlMap>
5 在src/java下新建2个类
1---pojo
package cm.neusoft.test.ibatis.pojo;
public class Person
{
private String id;
private Integer no;
private String name;
private String password;
private Integer age;
public String getId()
{
return id;
}
public Integer getNo()
{
return no;
}
public void setNo(Integer no)
{
this.no = no;
}
public void setId(String id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getPassword()
{
return password;
}
public void setPassword(String password)
{
this.password = password;
}
public Integer getAge()
{
return age;
}
public void setAge(Integer age)
{
this.age = age;
}
}
1---测试类
package cm.neusoft.test.ibatis.main;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import cm.neusoft.test.ibatis.pojo.Person;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class Test1
{
public static void main(String[] args)
{
String resource = "sql-map-config.xml";
SqlMapClient sqlMapClient = null;
try
{
Reader reader = Resources.getResourceAsReader(resource);
sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
sqlMapClient.startTransaction();
Person person = new Person();
//增加
person.setId("1");
person.setNo(1);
person.setName("韩超");
person.setPassword("111");
person.setAge(25);
sqlMapClient.insert("insertPerson", person);
//修改
// person.setId("1");
// person.setNo(123);
// person.setName("韩超"+person.getId());
// person.setPassword("1234");
// person.setAge(52);
// sqlMapClient.update("updatePerson", person);
//查找表中所有数据(方法一)
// //检索结果保存到list中
// ArrayList rsList = new ArrayList();
// rsList = (ArrayList)sqlMapClient.queryForList("getPerson","");
// for(int i = 0; i < rsList.size();i++){
// person = (Person)rsList.get(i);
// //打印对象中的信息
// System.out.print("学生I D :" + person.getId() + " ; ");
// System.out.print("学生编号 :" + person.getNo() + " ; ");
// System.out.print("学生姓名 :" + person.getName() + " ; ");
// System.out.print("学生密码 :" + person.getPassword() + " ; ");
// System.out.print("学生年龄 :" + person.getAge());
// System.out.println();
// }
//查找表中所有数据(方法二)
// List studentList = sqlMapClient.queryForList("getPerson", null);
// Iterator iterator = studentList.iterator();
// while (iterator.hasNext()) {
// person = (Person) iterator.next();
// System.out.println("id=" + person.getId() + " and name="
// + person.getName() + " age=" + person.getAge());
// }
//查找表年龄平均值
// int m = (Integer)sqlMapClient.queryForObject("getPersonAge", null);
// int n = (Integer)sqlMapClient.queryForObject("getPersonss", null);
// System.out.println(m/n);
sqlMapClient.commitTransaction();
System.out.println("Query user success!");
}
catch (IOException e)
{
System.out.println("呜呜,伤不起啊!!!!");
}
catch (SQLException e)
{
System.out.println("嘻嘻,玩死你啊!!!!");
}
}
}
项目图片