spring访问oracle数据库表,spring.net 实现访问oracle数据库，对表增、删、改、查支持事务拦截...

搜索了很多资料终于把spring.net 对oracle的操作实现(winform)，并且事务的控制很好。在这里把源码贴出来，方便大家。

spring.net 版本为1.3.1

1：项目引用组件：

其中common.loggin.dll为必要的组件，因为spring.net的相关日志输入都是基于该组件

2：项目结构

实现简单的层级entity、dao、biz、impl、service

3：配置文件

//app.config 文件

//Objects.xml 此文件和app.config文件配置的注入文件名称保持一致

xmlns:aop = "http://www.springframework.net/aop"

xmlns:db="http://www.springframework.net/database"

xmlns:tx="http://www.springframework.net/tx">

provider="System.Data.OracleClient"

connectionString="Data Source=数据库地址/ORCL;User ID=cvnx2;Password=thinkpad#$#;"/>

type="Spring.Data.Core.AdoPlatformTransactionManager, Spring.Data">

4：dao访问类

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Spring.Data.Core;//使用此引用

using System.Data;

using springAdoStudyEntity;

using Spring.Data.Common;

using springAdoStudyDao.utils;

namespace springAdoStudyDao

{

///

/// 设置参数oracle为特殊符号: sqlserver是@符号

///

public class StudentDao : AdoDaoSupport

{

///

/// 创建student表

///

public void AddStudentTable()

{

StringBuilder sb = new StringBuilder();

sb.Append("CREATE TABLE Student(");

sb.Append("id NUMBER(14) NOT NULL,");

sb.Append("name VARCHAR2(20) NOT NULL,");

sb.Append("age INT NOT NULL,");

sb.Append("sex VARCHAR2(2) NOT NULL,");

sb.Append("mobile VARCHAR2(14) NOT NULL,");

sb.Append("address VARCHAR2(100) NOT NULL");

sb.Append(")");

AdoTemplate.ExecuteNonQuery(CommandType.Text, sb.ToString());

}

///

///  * 新增学生

///

/// 学生信息

/// 插入成功或者失败

public bool AddStudent(StudentEntity studentObj)

{

StringBuilder sql = new StringBuilder();

sql.Append("INSERT INTO STUDENT (ID, NAME, AGE, SEX, MOBILE, ADDRESS)");

sql.Append(" VALUES (:ID, :NAME, :AGE, :SEX, :MOBILE, :ADDRESS)");

IDbParameters p = CreateDbParameters();

p.Add(":ID", DbType.Int64).Value = studentObj.Id;

p.Add(":NAME", DbType.String, 20).Value = studentObj.Name;

p.Add(":AGE", DbType.Int32).Value = studentObj.Age;

p.Add(":SEX", DbType.String, 2).Value = studentObj.Sex;

p.Add(":MOBILE", DbType.String, 14).Value = studentObj.Mobile;

p.Add(":ADDRESS", DbType.String, 100).Value = studentObj.Address;

return AdoTemplate.ExecuteNonQuery(CommandType.Text, sql.ToString(), p) > 0;

}

///

/// 修改学生信息

///

/// 学生信息

/// 修改成功或者失败

public bool UpdateStudent(StudentEntity studentObj)

{

StringBuilder sql = new StringBuilder();

sql.Append("UPDATE STUDENT a SET a.NAME=:NAME,A.AGE=:AGE,A.SEX=:SEX,A.MOBILE=:MOBILE,A.ADDRESS=:ADDRESS WHERE A.ID=:ID");

IDbParameters p = CreateDbParameters();

p.Add(":ID", DbType.Int64).Value = studentObj.Id;

p.Add(":NAME", DbType.String, 20).Value = studentObj.Name;

p.Add(":AGE", DbType.Int32).Value = studentObj.Age;

p.Add(":SEX", DbType.String, 2).Value = studentObj.Sex;

p.Add(":MOBILE", DbType.String, 14).Value = studentObj.Mobile;

p.Add(":ADDRESS", DbType.String, 100).Value = studentObj.Address;

return AdoTemplate.ExecuteNonQuery(CommandType.Text, sql.ToString(), p) > 0;

}

///

/// 删除学生信息

///

/// 学生ID

/// 删除成功或者失败

public bool DelStudent(long id)

{

StringBuilder sql = new StringBuilder();

sql.Append("DELETE FROM STUDENT WHERE ID=:ID");

IDbParameters p = CreateDbParameters();

p.Add(":ID",DbType.Int64,14).Value=id;

return AdoTemplate.ExecuteNonQuery(CommandType.Text, sql.ToString(), p) > 0;

}

///

/// 获取单个学生信息

///

/// 学生ID

/// 学生信息

public StudentEntity GetStudent(long id)

{

StringBuilder sql = new StringBuilder();

sql.Append("SELECT ID, NAME, AGE, SEX, MOBILE, ADDRESS");

sql.Append(" FROM STUDENT WHERE ID=:ID and rownum=1");

IDbParameters p = CreateDbParameters();

p.Add(":ID", DbType.Int64).Value = id;

Object obj = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, sql.ToString(), delegate(IDataReader dataRead, int rowNum)

{

StudentEntity entity = new StudentEntity();

entity.Id = dataRead.GetInt64(0);

entity.Name = dataRead.GetString(1);

entity.Age = dataRead.GetInt32(2);

entity.Sex = dataRead.GetString(3);

entity.Mobile = dataRead.GetString(4);

entity.Address = dataRead.GetString(5);

return entity;

}, p);

if(obj!=null){

IList list=DataConvertList.SpingListConvert(obj as Spring.Collections.LinkedList);

if(list!=null && list.Count>0)

return list[0];

}

return null;

}

///

///  获取所有学生信息

///

/// 学生集合

public IList GetStudentList()

{

StringBuilder sql = new StringBuilder();

sql.Append("SELECT ID, NAME, AGE, SEX, MOBILE, ADDRESS");

sql.Append(" FROM STUDENT ");

Object obj=  AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, sql.ToString(), delegate(IDataReader dataRead,int rowNum)

{

StudentEntity entity = new StudentEntity();

entity.Id = dataRead.GetInt64(0);

entity.Name = dataRead.GetString(1);

entity.Age = dataRead.GetInt32(2);

entity.Sex = dataRead.GetString(3);

entity.Mobile = dataRead.GetString(4);

entity.Address = dataRead.GetString(5);

return entity;

});

return DataConvertList.SpingListConvert(obj as Spring.Collections.LinkedList);

}

}

}

5：impl层代码

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using springAdoStudyEntity;

using springAdoStudyDao;

using springAdoBiz.checkException;

using springAdoStudyCommon;

namespace springAdoBiz

{

public class StudentImpl : IStudentBiz

{

private StudentDao StudentDao { get; set; }

///

/// 创建student表

///

public void AddStudentTable()

{

StudentDao.AddStudentTable();

}

///

///  * 新增学生

///

/// 学生信息

/// 插入成功或者失败

public bool AddStudent(StudentEntity studentObj)

{

CheckStudentException.Check(studentObj);

StudentEntity model = null;

model = StudentDao.GetStudent(studentObj.Id);

if (model != null)

throw new ErrorException("StudentImpl.AddStudent.E0001", "已存在相同的学生信息，不允许重复添加！");

StudentDao.AddStudent(studentObj);

throw new BussinessException("StudentImpl.AddStudent.B0001", "插入已回滚");

return true;

}

///

/// 修改学生信息

///

/// 学生信息

/// 修改成功或者失败

public bool UpdateStudent(StudentEntity studentObj)

{

StudentEntity model = null;

model = StudentDao.GetStudent(studentObj.Id);

if (model != null)

throw new Exception("更新失败，记录不存在！");

return StudentDao.UpdateStudent(studentObj);

}

///

/// 删除学生信息

///

/// 学生ID

/// 删除成功或者失败

public bool DelStudent(long id)

{

StudentEntity model = null;

model = StudentDao.GetStudent(id);

if (model != null)

throw new Exception("删除失败，记录不存在！");

return StudentDao.DelStudent(id);

}

///

/// 获取单个学生信息

///

/// 学生ID

/// 学生信息

public StudentEntity GetStudent(long id)

{

return StudentDao.GetStudent(id);

}

///

///  获取所有学生信息

///

/// 学生集合

public IList GetStudentList()

{

return StudentDao.GetStudentList();

}

}

}

6：service层代码

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using springAdoBiz;

using springAdoStudyEntity;

using Spring.Transaction.Interceptor;

using springAdoStudyCommon;

namespace springAdoStudyService

{

//如果要使用事务，方法必须使用接口实现才能生效，否则属性StudentImpl会注入失败为NULL

public class StudentService:StudentServiceBiz

{

public StudentImpl StudentImpl{ get; set; }

///

/// 创建student表

///

public void AddStudentTable()

{

StudentImpl.AddStudentTable();

}

///

///  * 新增学生

///

/// 学生信息

/// 插入成功或者失败

[Transaction(NoRollbackFor=new Type[]{typeof(ErrorException)},RollbackFor=new Type[]{typeof(BussinessException)})]

public bool AddStudent(StudentEntity studentObj)

{

return StudentImpl.AddStudent(studentObj);

}

///

/// 修改学生信息

///

/// 学生信息

/// 修改成功或者失败

public bool UpdateStudent(StudentEntity studentObj)

{

return StudentImpl.UpdateStudent(studentObj);

}

///

/// 删除学生信息

///

/// 学生ID

/// 删除成功或者失败

public bool DelStudent(long id)

{

return StudentImpl.DelStudent(id);

}

///

/// 获取单个学生信息

///

/// 学生ID

/// 学生信息

public StudentEntity GetStudent(long id)

{

return StudentImpl.GetStudent(id);

}

///

///  获取所有学生信息

///

/// 学生集合

public IList GetStudentList()

{

return StudentImpl.GetStudentList();

}

}

}

7：测试类

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using springAdoStudyService;

using springAdoStudyEntity;

using springAdoStudyDao;

using springAdoBiz;

using Spring.Context.Support;

namespace springAdoStudyUI

{

class Program

{

static void Main(string[] args)

{

Spring.Context.IApplicationContext context = Spring.Context.Support.ContextRegistry.GetContext();

StudentServiceBiz studentService = context.GetObject("StudentService") as StudentServiceBiz;

#region 增加student表

//studentService.AddStudentTable();

#endregion

#region 加入学生信息 (带事务控制)

try

{

StudentEntity entity = new StudentEntity();

entity.Id = 1001;

entity.Name = "迅雷003";

entity.Age = 20;

entity.Sex = "男";

entity.Mobile = "12434354a6";

entity.Address = "广州天河1";

studentService.AddStudent(entity);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

#endregion

#region 获取单个学生信息

//StudentEntity singleEntity=studentService.GetStudent(1000);

#endregion

#region 获取所有学生列表

//IList list = studentService.GetStudentList();

#endregion

#region 更新单个学生信息

//StudentEntity up_entity = new StudentEntity();

//up_entity.Id = 1000;

//up_entity.Name = "迅雷002";

//up_entity.Age = 20;

//up_entity.Sex = "男";

//up_entity.Mobile = "124343545";

//up_entity.Address = "广州天河";

//bool update_success = studentService.UpdateStudent(up_entity);

#endregion

#region 删除单个学生信息

//bool del_success = studentService.DelStudent(1000);

#endregion

Console.Read();

}

}

}

需要源码的可以联系