4.1        使用存款和储蓄进程… 6,BeanUtils组件简要介绍

目录

预编译sql处理(防止sql注入)
  • Statement : 执行SQL命令
    • CallableStatement : 试行存款和储蓄进度
    • PreparedStatement : 预编写翻译SQL语句实行
  • 选取预编写翻译SQL语句的吩咐对象的功利 : 幸免频仍sql拼接
    (能够动用占位符), 能够免御sql注入

    • 举个例子: 登录模块输入用户名,密码的时候, 可以制止用户输入的黑心密码

public class App {
    // 连接参数
    //private String url = "jdbc:mysql://localhost:3306/jdbc_demo";
    private String url = "jdbc:mysql:///jdbc_demo";
    private String user = "root";
    private String password = "root";
    private Connection con;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;
    // 1. 没有使用防止sql注入的案例
    @Test
    public void testLogin() {
        // 1.0 模拟登陆的用户名,密码
        String userName = "tom";
        //String pwd = "8881";
        String pwd = " ' or 1=1 -- ";
        // SQL语句
        String sql = "select * from admin where userName='"+userName+"'  and pwd='"+pwd+"' ";
        System.out.println(sql);
        try {
            // 1.1 加载驱动,创建连接
            Class.forName("com.mysql.jdbc.Driver");     
            con = DriverManager.getConnection(url, user, password);
            // 1.2 创建stmt对象
            stmt = con.createStatement();
            // 1.3 执行查询
            rs = stmt.executeQuery(sql);
            // 业务判断
            if (rs.next()) {
                System.out.println("登陆成功, 编号:" + rs.getInt("id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 1.4 关闭
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    // 2. 使用PreparedStatement, 防止sql注入
    @Test
    public void testLogin2() {

        // 1.0 模拟登陆的用户名,密码
        String userName = "tom";
        //String pwd = "8881";
        String pwd = " ' or 1=1 -- ";

        // SQL语句
        String sql = "select * from admin where userName=?  and pwd=? ";
        try {
            // 1.1 加载驱动,创建连接
            Class.forName("com.mysql.jdbc.Driver");     
            con = DriverManager.getConnection(url, user, password);
            // 1.2 创建pstmt对象
            pstmt = con.prepareStatement(sql);   // 对sql语句预编译
            // 设置占位符值
            pstmt.setString(1, userName);
            pstmt.setString(2, pwd);

            // 1.3 执行
            rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.println("登陆成功," + rs.getInt("id"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 1.4 关闭
            try {
                rs.close();
                pstmt.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }   
}

1       大致思路… 2

仓储进度调用
  • 储存进度的定义 :

-- 存储过程
-- 定义分隔符
DELIMITER $$
CREATE PROCEDURE proc_login()
BEGIN
   SELECT * FROM admin;
END $$
-- 调用
CALL proc_login;
  • 积存进度选用示例 :

public class App_call {
    // 全局参数
    private Connection con;
    private Statement stmt;
    private PreparedStatement pstmt;
    private CallableStatement cstmt;  // 存储过程
    private ResultSet rs;
    // 程序中调用存储过程
    @Test
    public void testCall() throws Exception {
        try {
            //1 . 创建连接
            con = JdbcUtil.getConnection();
            //2.  创建执行存储过程的stmt对象
            CallableStatement cstmt = con.prepareCall("CALL proc_login");
            //3.  执行(存储过程)
            rs = cstmt.executeQuery();

            // 遍历结果,测试
            if (rs.next()) {
                String name = rs.getString("userName");
                String pwd = rs.getString("pwd");
                // 测试
                System.out.println(name + pwd);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2       PetShop4的优异数据库连接代码回看… 3

批处理
  • 事务场景:当必要向数据库发送一群SQL语句实行时,应制止向数据库一条条的出殡推行,而应使用JDBC的批管理体制,以升级推行效能。
  • 施行批处理SQL语句:
    1. executeBatch()方法:实践批管理命令
    2. clearBatch()方法:清除批管理命令
  • 金镶玉裹福禄双全批管理有二种情势,
    • 先是种格局:Statement.addBatch(sql) list
      • 亮点:能够向数据库发送多条不相同的SQL语句。
      • 缺陷:SQL语句未有预编写翻译;
        当向数据库发送多条语句一样,但仅参数差异的SQL语句时,需另行写上众多条SQL语句。举例:

  Insert into user(name,password) values(‘aa’,’111’);
  Insert into user(name,password) values(‘bb’,’222’);
  Insert into user(name,password) values(‘cc’,’333’);
  Insert into user(name,password) values(‘dd’,’444’);

Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
String sql1 = "insert into user(name,password,email,birthday) 
 values('kkk','123','abc@sina.com','1978-08-08')";
String sql2 = "update user set password='123456' where id=3";
st = conn.createStatement();
st.addBatch(sql1);  //把SQL语句加入到批命令中
st.addBatch(sql2);  //把SQL语句加入到批命令中
st.executeBatch();
} finally {
 JdbcUtil.free(conn, st, rs);
}
  • 其次种办法:选择Statement.addBatch(sql)格局实现批处理:
    • 可取:发送的是预编译后的SQL语句,推行效能高
    • 劣势:只好使用在SQL语句一样,但参数差异的批处理中。因而此种情势的批管理常常用来在同两个表中批量布署数据,或批量更新表的多少

        conn = JdbcUtil.*getConnection*();
        String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
        st = conn.prepareStatement(sql);
        for(int i=0;i<50000;i++){
            st.setString(1, "aaa" + i);
            st.setString(2, "123" + i);
            st.setString(3, "aaa" + i + "@sina.com");
            st.setDate(4,**new** Date(1980, 10, 10));
            st.addBatch();
            if (i%1000==0){
                st.executeBatch();
                st.clearBatch();
            }
        }
        st.executeBatch();

  public class App {
 // 测试批处理操作
 @Test
  public void testBatch() throws Exception {

  // 模拟数据
  List<Admin> list = new ArrayList<Admin>();
 for (int i=1; i<21; i++) {
   Admin admin = new Admin();
   admin.setUserName("Jack" + i);
   admin.setPwd("888" + i);
   list.add(admin);
  }

  // 保存
  AdminDao dao = new AdminDao();
  dao.save(list);
 }
}

// 封装所有的与数据库的操作
  public class AdminDao {

 // 全局参数
private Connection con;
private PreparedStatement pstmt;
private ResultSet rs;

 // 批量保存管理员
 public void save(List<Admin> list) {
  // SQL
  String sql = "INSERT INTO admin(userName,pwd) values(?,?)";
  try {

   // 获取连接
   con = JdbcUtil.getConnection();
   // 创建stmt 
   pstmt = con.prepareStatement(sql);     // 【预编译SQL语句】

   for (int i=0; i<list.size(); i++) {
    Admin admin = list.get(i);
    // 设置参数
    pstmt.setString(1, admin.getUserName());
    pstmt.setString(2, admin.getPwd());

    // 添加批处理
    pstmt.addBatch();      // 【不需要传入SQL】

    // 测试:每5条执行一次批处理
    if (i % 5 == 0) {
     // 批量执行 
     pstmt.executeBatch();
     // 清空批处理
     pstmt.clearBatch();
    }
   }

   // 批量执行 
   pstmt.executeBatch();
   // 清空批处理
   pstmt.clearBatch();

  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   JdbcUtil.closeAll(con, pstmt, rs);
  }
 }
}

2.1        PrepareCommand. 3

插入数据,获取自拉长值
  • 需求 : 李俊杰 : 18, 张相 : 19
  • 如何统筹数据库?

 编号   员工姓名    年龄    部门
 01     李俊杰     18    开发部
 02     张三       19    开发部
  • 考虑 : 怎样压缩多少冗余 -> 设置外键约束(部门与职员和工人, 一对多的涉及)

 编号    员工姓名    年龄    部门
 01       李俊杰    18      1
 02       张三      19      1

 部门编号     部门名称    
     1         开发部            
  • 安插数据库:

 员工表 (外键表) 【员工表有一个外键字段,引用了部门表的主键】
 部门表(主键表)
  • 编码总体思路 : 保存职员和工人及其对应的机构
    • 手续:先保存部门; 再获得单位主键,再保存员工
    • 付出具体步骤:
      1. 设计javabean
      2. 设计dao
      3. 测试

部门
CREATE TABLE dept(
   deptId INT PRIMARY KEY AUTO_INCREMENT,
   deptName VARCHAR(20)
);
-- 员工
CREATE TABLE employee(
   empId INT PRIMARY KEY AUTO_INCREMENT,
   empName VARCHAR(20),
   dept_id  INT   --  外键字段   
);
-- 给员工表添加外键约束
ALTER TABLE employee ADD CONSTRAINT FK_employee_dept_deptId
    FOREIGN KEY(dept_id) REFERENCES dept(deptId) ;

public class EmpDao {
    private Connection con;
    private PreparedStatement pstmt;
    private ResultSet rs;
    // 保存员工,同时保存关联的部门
    public void save(Employee emp){
        // 保存部门
        String sql_dept = "insert into dept(deptName) values(?)";
        // 保存员工
        String sql_emp = "INSERT INTO employee (empName,dept_id) VALUES (?,?)";
        // 部门id
        int deptId = 0;
        try {
            // 连接
            con = JdbcUtil.getConnection();
            /*****保存部门,获取自增长*******/
            // 【一、需要指定返回自增长标记】
            pstmt = con.prepareStatement(sql_dept,Statement.RETURN_GENERATED_KEYS);
            // 设置参数
            pstmt.setString(1, emp.getDept().getDeptName());
            // 执行
            pstmt.executeUpdate();
            // 【二、获取上面保存的部门子增长的主键】
            rs =  pstmt.getGeneratedKeys();
            // 得到返回的自增长字段
            if (rs.next()) {
                deptId = rs.getInt(1);
            }
            /*****保存员工*********/
            pstmt = con.prepareStatement(sql_emp);
            // 设置参数
            pstmt.setString(1, emp.getEmpName());
            pstmt.setInt(2, deptId);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeAll(con, pstmt, rs);
        }
    }
}

2.2        ExecuteNoQuery. 4

事务
  • 主干概念 : 事务使指一组最小逻辑操作单元,里面有四个操作结合。
    组成工作的每一片段必须要相同的时间提交成功,借使有贰个操作失败,整个操作就回滚
  • 事务ACID特性 :
    • 原子性(Atomicity) :
      原子性是指职业是多少个不可分割的做事单位,事务中的操作依旧都发出,要么都不发生
    • 一致性(Consistency) :
      事务必须使数据库从贰个一致性状态转变成其余三个一致性状态
    • 隔绝性(Isolation) :
      事务的隔开性是多少个用户并发访谈数据库时,数据库为每一个用户打开的业务,不能够被其余职业的操作数据所困扰,八个冒出事务之间要相互隔绝
    • 长久性(Durability) :
      长久性是指多个事情一旦被交付,它对数据库中数据的改观正是长久性的,接下去正是数据库产生故障也不应有对其有其余影响
  • 作业的特色:
    • 原子性,是多个微细逻辑操作单元
    • 一致性,事务进度中,数据处于一样状态
    • 悠久性, 事务一旦付出成功,对数码的改动会反映到数据库中
    • 隔绝性, 事务与作业之间是割裂的

  • 案例 :
    • 须要 : 张三给李四转账
    • 设计 : 账户表
    • 技术 :
      • Connection :
        • void setAutoCommit(boolean autoCommit) :
          设置专门的学业是或不是自动提交; 假设设置为false,表示手动提交业务
      • void commit() : 手动提交业务
      • void rollback() : 回滚
        (出现至极时候,全数曾经实践成功的代码供给回落到业务开端前的情事)
      • Savepoint setSavepoint(String name)
    • 代码示例 :

-- 账户表
CREATE TABLE account(
   id INT PRIMARY KEY AUTO_INCREMENT,
   accountName VARCHAR(20),
   money DOUBLE
);
-- 转账
UPDATE account SET money=money-1000 WHERE accountName='张三';
UPDATE account SET money=money+1000 WHERE accountName='李四';

public class AccountDao {
    // 全局参数
    private Connection con;
    private PreparedStatement pstmt;
    // 1. 转账,没有使用事务
    public void trans1() {
        String sql_zs = "UPDATE account SET money=money-1000 WHERE accountName='张三';";
        String sql_ls = "UPDATE account SET money=money+1000 WHERE accountName='李四';";
        try {
            con = JdbcUtil.getConnection(); // 默认开启的隐士事务
            con.setAutoCommit(true);
            /*** 第一次执行SQL ***/
            pstmt = con.prepareStatement(sql_zs);
            pstmt.executeUpdate();
            /*** 第二次执行SQL ***/
            pstmt = con.prepareStatement(sql_ls);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeAll(con, pstmt, null);
        }

    }
    // 2. 转账,使用事务
    public void trans2() {
        String sql_zs = "UPDATE account SET money=money-1000 WHERE accountName='张三';";
        String sql_ls = "UPDATE1 account SET money=money+1000 WHERE accountName='李四';";

        try {
            con = JdbcUtil.getConnection(); // 默认开启的隐士事务
            // 一、设置事务为手动提交
            con.setAutoCommit(false);

            /*** 第一次执行SQL ***/
            pstmt = con.prepareStatement(sql_zs);
            pstmt.executeUpdate();

            /*** 第二次执行SQL ***/
            pstmt = con.prepareStatement(sql_ls);
            pstmt.executeUpdate();

        } catch (Exception e) {
            try {
                // 二、 出现异常,需要回滚事务
                con.rollback();
            } catch (SQLException e1) {
            }
            e.printStackTrace();
        } finally {
            try {
                // 三、所有的操作执行成功, 提交事务
                con.commit();
                JdbcUtil.closeAll(con, pstmt, null);
            } catch (SQLException e) {
            }
        }
    }
    // 3. 转账,使用事务, 回滚到指定的代码段
    public void trans() {
        // 定义个标记
        Savepoint sp = null;
        // 第一次转账
        String sql_zs1 = "UPDATE account SET money=money-1000 WHERE accountName='张三';";
        String sql_ls1 = "UPDATE account SET money=money+1000 WHERE accountName='李四';";
        // 第二次转账
        String sql_zs2 = "UPDATE account SET money=money-500 WHERE accountName='张三';";
        String sql_ls2 = "UPDATE1 account SET money=money+500 WHERE accountName='李四';";

        try {
            con = JdbcUtil.getConnection(); // 默认开启的隐士事务
            con.setAutoCommit(false);       // 设置事务手动提交

            /*** 第一次转账 ***/
            pstmt = con.prepareStatement(sql_zs1);
            pstmt.executeUpdate();
            pstmt = con.prepareStatement(sql_ls1);
            pstmt.executeUpdate();

            // 回滚到这个位置?
            sp = con.setSavepoint(); 

            /*** 第二次转账 ***/
            pstmt = con.prepareStatement(sql_zs2);
            pstmt.executeUpdate();
            pstmt = con.prepareStatement(sql_ls2);
            pstmt.executeUpdate();

        } catch (Exception e) {
            try {
                // 回滚 (回滚到指定的代码段)
                con.rollback(sp);
            } catch (SQLException e1) {
            }
            e.printStackTrace();
        } finally {
            try {
                // 提交
                con.commit();
            } catch (SQLException e) {
            }
            JdbcUtil.closeAll(con, pstmt, null);
        }
    }
}

3       怎么样写好三个的OracleHelper 5

Jdbc中山高校文本类型的管理
  • 在事实上支付中,程序须求把大文本或二进制数据保存到数据库
    • Oracle中山高校文本数据类型 : Clob(长文本类型,
      MySQL中不协助,使用的是text), Blob(二进制类型)
    • MySQL数据库: Text(长文本类型), Blob(二进制类型)
  • 基本概念:大数量也称之为LOB(Large Objects),LOB又分为:clob和blob
    • clob用于存储大文本。
    • blob用于存款和储蓄二进制数据,举例图像、声音、二进制文等。
  • 对MySQL来说独有blob,而尚未clob,mysql存款和储蓄大文本采取的是Text,Text和blob分别又分为:
    • TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
    • TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

public class App_text {

    // 全局参数
    private Connection con;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;

    @Test
    // 1. 保存大文本数据类型   ( 写longtext)
    public void testSaveText() {
        String sql = "insert into test(content) values(?)";
        try {
            // 连接
            con = JdbcUtil.getConnection();
            // pstmt 对象
            pstmt = con.prepareStatement(sql);
            // 设置参数
            // 先获取文件路径
            String path = App_text.class.getResource("tips.txt").getPath();
            FileReader reader = new FileReader(new File(path));
            pstmt.setCharacterStream(1, reader);

            // 执行sql
            pstmt.executeUpdate();

            // 关闭
            reader.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeAll(con, pstmt, null);
        }
    }

    @Test
    // 2. 读取大文本数据类型   ( 读longtext)
    public void testGetAsText() {
        String sql = "select * from  test;";
        try {
            // 连接
            con = JdbcUtil.getConnection();
            // pstmt 对象
            pstmt = con.prepareStatement(sql);
            // 读取
            rs = pstmt.executeQuery();
            if (rs.next()) {
                // 获取长文本数据, 方式1:
                //Reader r = rs.getCharacterStream("content");

                // 获取长文本数据, 方式2:
                System.out.print(rs.getString("content"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeAll(con, pstmt, null);
        }
    }
}

3.1        PetShop的OracleHelper 5

Jdbc综合作演出习
  • 急需分析:登入、注册、注销;
    • 登入成功 : 展现全数的职工
    • 设计
      • 数据库设计:
        • Admin, 存放全部的登入用户
        • Employee, 寄放全数的职员和工人消息
      • 系统规划
    • 系统结构
      • 分层: 基于mvc方式的分段
      • 品种用到的公用组件、类 (领悟)
    • 编码

3.2        OracleHelper 6

BeanUtils组件简要介绍

  • 次第中对javabean的操作很频仍,
    所以apache提供了一套开源的api,方便对javabean的操作!即BeanUtils组件(成效是简化javabean的操作)
  • 用户能够从www.apache.org下载BeanUtils组件,然后再在项目中引入jar文件!

4       代码示例… 6

导入BenUtils组件

  • 引入commons-beanutils-1.8.3.jar核心包
  • 引进日志扶助包: commons-logging-1.1.3.jar
  • 要是缺乏日志jar文件,报错:
    java.lang.NoClassDefFoundError: org/apache/commons/logging/LogFactory
    at org.apache.commons.beanutils.ConvertUtilsBean.<init>(ConvertUtilsBean.java:157)
    at org.apache.commons.beanutils.BeanUtilsBean.<init>(BeanUtilsBean.java:117)
    at org.apache.commons.beanutils.BeanUtilsBean$1.initialValue(BeanUtilsBean.java:68)

4.1        使用存款和储蓄进程… 6

实例, 基本用法

  • 格局1: 对象属性的正片
    BeanUtils.copyProperty(admin, "userName", "jack");
    BeanUtils.setProperty(admin, "age", 18);
  • 措施2: 对象的正片
    BeanUtils.copyProperties(newAdmin, admin);
  • 办法3: map数据拷贝到javabean中
    (注意:map中的key要与javabean的习性名称同样)
    BeanUtils.populate(adminMap, map);

public  void  test1()  throws  Exception {

 // a. 基本操作
 Admin admin = **new** Admin();
//  admin.setUserName("Jack");
//  admin.setPwd("999");

 // b. BeanUtils组件实现对象属性的拷贝
 BeanUtils.copyProperty(admin, "userName", "jack");
 BeanUtils.setProperty(admin, "age", 18); // copy, set 都可以

 // 总结1: 对于基本数据类型,会自动进行类型转换!

 // c. 对象的拷贝
 Admin newAdmin = new  Admin();
 BeanUtils.copyProperties(newAdmin, admin);

 // d. map数据,拷贝到对象中
 Admin adminMap = new  Admin();
 Map<String,Object> map = new  HashMap<String,Object>();
 map.put("userName", "Jerry");
 map.put("age", 29);
 // 注意:map中的key要与javabean的属性名称一致
 BeanUtils.populate(adminMap, map);

 // 测试
 System.out.println(adminMap.getUserName());
 System.out.println(adminMap.getAge());
}

4.2        批管理之使用PL/SQL. 7

实例, 日期类型的正片

  • 亟待登记日期类型转变器,2种情势参见上面代码

 //2. 自定义日期类型转换器
    @Test
    public void test2() throws Exception {
        // 模拟表单数据
        String name = "jack";
        String age = "20";
        String birth = "   ";

        // 对象
        Admin admin = new Admin();

        // 注册日期类型转换器:1, 自定义的方式
        ConvertUtils.register(new Converter() {
            // 转换的内部实现方法,需要重写
            @Override
            public Object convert(Class type, Object value) {

                // 判断
                if (type != Date.class) {
                    return null;
                }
                if (value == null || "".equals(value.toString().trim())) {
                    return null;
                }


                try {
                    // 字符串转换为日期
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.parse(value.toString());
                } catch (ParseException e) {
                    throw new RuntimeException(e);
                }
            }
        },Date.class);

        // 把表单提交的数据,封装到对象中
        BeanUtils.copyProperty(admin, "userName", name);
        BeanUtils.copyProperty(admin, "age", age);
        BeanUtils.copyProperty(admin, "birth", birth);

        //------ 测试------
        System.out.println(admin);
    }

    //2. 使用提供的日期类型转换器工具类
    @Test
    public void test3() throws Exception {
        // 模拟表单数据
        String name = "userName";
        String age = "20";
        String birth = null;

        // 对象
        Admin admin = new Admin();

        // 注册日期类型转换器:2: 使用组件提供的转换器工具类
        ConvertUtils.register(new DateLocaleConverter(), Date.class);

        // 把表单提交的数据,封装到对象中
        BeanUtils.copyProperty(admin, "userName", name);
        BeanUtils.copyProperty(admin, "age", age);
        BeanUtils.copyProperty(admin, "birth", birth);

        //------ 测试------
        System.out.println(admin);
    }
}

4.3        批管理之使用事务… 8

应用

public  class  WebUtils {

 @Deprecated
 public  static <T> T copyToBean_old(HttpServletRequest request, Class<T> clazz) {
  try {
   // 创建对象
   T t = clazz.newInstance();

   // 获取所有的表单元素的名称
   Enumeration<String> enums = request.getParameterNames();
   // 遍历
   while (enums.hasMoreElements()) {
    // 获取表单元素的名称:<input type="password" name="pwd"/>
    String name = enums.nextElement();  // pwd
    // 获取名称对应的值
    String value = request.getParameter(name);
    // 把指定属性名称对应的值进行拷贝
    BeanUtils.copyProperty(t, name, value);
   }

  return t;
  } catch (Exception e) {
   throw  new  RuntimeException(e);
  }
 }

 /**
  * 处理请求数据的封装
  */
  public  static  <T> T copyToBean(HttpServletRequest request, Class<T> clazz) {
  try {
   // (注册日期类型转换器)
   // 创建对象
   T t = clazz.newInstance();
   BeanUtils.populate(t, request.getParameterMap()); // 使用map
   return  t;
  } catch (Exception e) {
   throw  new  RuntimeException(e);
  }
 }
}

5       运转效果… 10

元数据

  • 在jdbc中获得数据库的概念,譬如:数据库、表、列的概念新闻,
    就用到元数据
  • 在jdbc中可以行使:
    数据库元数据、参数元数据、结果集元数据(元数据定义相关api,
    ..MetaData)

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.junit.Test;

import anderson.sh.utils.JdbcUtil; // 自己的工具库

public class App {

    //1. 数据库元数据
    @Test
    public void testDB() throws Exception {
        // 获取连接
        Connection conn = JdbcUtil.getConnection();
        // 获取数据库元数据
        DatabaseMetaData metaData = conn.getMetaData(); // alt + shift + L  快速获取方法返回值

        System.out.println(metaData.getUserName());
        System.out.println(metaData.getURL());
        System.out.println(metaData.getDatabaseProductName());
    }

    //2. 参数元数据
    @Test
    public void testParams() throws Exception {
        // 获取连接
        Connection conn = JdbcUtil.getConnection();
        // SQL
        String sql = "select * from dept where deptid=? and deptName=?";
        // Object[] values = {"tom","888"};

        PreparedStatement pstmt = conn.prepareStatement(sql);
        // 参数元数据
        ParameterMetaData p_metaDate = pstmt.getParameterMetaData();
        // 获取参数的个数
        int count = p_metaDate.getParameterCount();

        // 测试
        System.out.println(count);
    }

    // 3. 结果集元数据
    @Test
    public void testRs() throws Exception {
        String sql = "select * from dept ";

        // 获取连接
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        // 得到结果集元数据(目标:通过结果集元数据,得到列的名称)
        ResultSetMetaData rs_metaData = rs.getMetaData();

        // 迭代每一行结果
        while (rs.next()) {
            // 1. 获取列的个数
            int count = rs_metaData.getColumnCount();
            // 2. 遍历,获取每一列的列的名称
            for (int i=0; i<count; i++) {
                // 得到列的名称
                String columnName = rs_metaData.getColumnName(i + 1);
                // 获取每一行的每一列的值
                Object columnValue = rs.getObject(columnName);
                // 测试
                System.out.print(columnName + "=" + columnValue + ",");
            }
            System.out.println();
        }   
    }   
}

6       小结… 10

Dao操作的收取(BaseDAO)

  • Dao操作通用的手续:

    1. 写SQL语句
    2. 获取连接
    3. 创建stmt
    4. 执行sql
    • 更新
    • 查询
    1. 关闭/异常
  • 通用的DAO(BaseDao)

    1. 更新 : public void update(String sql, Object[] paramValues);
    2. 查询 :
    • 传扬的什么品种的对象,就封装为啥类型
    • 渴求: 列的名号,要与钦赐项指标对象的性质名称一样)
      Public List<T> query (String sql , Object[] paramValues , Class<T> clazz);

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import anderson.sh.utils.JdbcUtil;

/**
 * 通用的dao,自己写的所有的dao都继承此类;
 * 此类定义了2个通用的方法:
 *  1. 更新
 *      2. 查询
 */
public class BaseDao {

    // 初始化参数
    private Connection con;
    private PreparedStatement pstmt;
    private ResultSet rs;

    /**
     * 更新的通用方法
     * @param sql   更新的sql语句(update/insert/delete)
     * @param paramsValue  sql语句中占位符对应的值(如果没有占位符,传入null)
     */
    public void update(String sql,Object[] paramsValue){

        try {
            // 获取连接
            con = JdbcUtil.getConnection();
            // 创建执行命令的stmt对象
            pstmt = con.prepareStatement(sql);
            // 参数元数据: 得到占位符参数的个数
            int count = pstmt.getParameterMetaData().getParameterCount();

            // 设置占位符参数的值
            if (paramsValue != null && paramsValue.length > 0) {
                // 循环给参数赋值
                for(int i=0;i<count;i++) {
                    pstmt.setObject(i+1, paramsValue[i]);
                }
            }
            // 执行更新
            pstmt.executeUpdate();

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, pstmt, null);
        }
    }

    /**
     * 查询的通用方法
     * @param sql
     * @param paramsValue
     */
    public <T> List<T> query(String sql, Object[] paramsValue,Class<T> clazz){

        try {
            // 返回的集合
            List<T> list = new ArrayList<T>();
            // 对象
            T t = null;

            // 1. 获取连接
            con = JdbcUtil.getConnection();
            // 2. 创建stmt对象
            pstmt = con.prepareStatement(sql);
            // 3. 获取占位符参数的个数, 并设置每个参数的值
            //int count = pstmt.getParameterMetaData().getParameterCount();
            if (paramsValue != null && paramsValue.length > 0) {
                for (int i=0; i<paramsValue.length; i++) {
                    pstmt.setObject(i+1, paramsValue[i]);
                }
            }
            // 4. 执行查询
            rs = pstmt.executeQuery();
            // 5. 获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // ---> 获取列的个数
            int columnCount = rsmd.getColumnCount();

            // 6. 遍历rs
            while (rs.next()) {
                // 要封装的对象
                t = clazz.newInstance();

                // 7. 遍历每一行的每一列, 封装数据
                for (int i=0; i<columnCount; i++) {
                    // 获取每一列的列名称
                    String columnName = rsmd.getColumnName(i + 1);
                    // 获取每一列的列名称, 对应的值
                    Object value = rs.getObject(columnName);
                    // 封装: 设置到t对象的属性中  【BeanUtils组件】
                    BeanUtils.copyProperty(t, columnName, value);               
                }

                // 把封装完毕的对象,添加到list集合中
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, pstmt, rs);
        }
    }
}

import java.util.List;

public class AdminDao extends BaseDao {

    // 删除
    public void delete(int id) {
        String sql = "delete from admin where id=?";
        Object[] paramsValue = {id};
        super.update(sql, paramsValue);
    }

    // 插入
    public void save(Admin admin) {
        String sql = "insert into admin (userName,pwd) values (?,?)";
        Object[] paramsValue = {admin.getUserName(),admin.getPwd()};
        super.update(sql, paramsValue);
    }

    // 查询全部
    public List<Admin> getAll(){
        String sql = "select * from admin";
        List<Admin> list = super.query(sql, null, Admin.class);
        return list;
    }

    // 根据条件查询(主键)
    public Admin findById(int id){
        String sql = "select * from admin where id=?";
        List<Admin> list = super.query(sql, new Object[]{id}, Admin.class);
        return  (list!=null&&list.size()>0) ? list.get(0) : null;
    }   
}

 

1       大约思路

 图片 1

备考:金棕为影响参数

2       PetShop4的经文数据库连接代码回想

PetShop4有3个函数,具体有:

ExecuteReader:能够读一个表的笔录,只可以读不可能写。

ExecuteScalar:只好读一条记下,一般用来判别数据库是还是不是有多少等,只好读不能写。

ExecuteNonQuery:能够写以能够读。

那边介绍一下PrepareCommand、ExecuteNoQuery。

2.1   PrepareCommand

小心:当前函数是private的,不提须求外界调用。

        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters) {
            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null) {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }

2.2   ExecuteNoQuery

此函数:传入连接串、实行项目、SQL、参数

       /// <summary>
        /// Execute a database query which does not include a select
        /// </summary>
        /// <param name="connString">Connection string to database</param>
        /// <param name="cmdType">Command type either stored procedure or SQL</param>
        /// <param name="cmdText">Acutall SQL Command</param>
        /// <param name="commandParameters">Parameters to bind to the command</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            // Create a new Oracle command
            OracleCommand cmd = new OracleCommand();
            //Create a connection
            using (OracleConnection connection = new OracleConnection(connectionString)) {
                //Prepare the command
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

此函数:传入事务、施行项目、SQL、参数

      /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database transaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing database transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

 此函数:传入连接、施行项目、SQL、参数

       /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

3       怎样写好三个的OracleHelper

3.1   PetShop的OracleHelper

PetShop不是写好了吗?为啥还要协和写?

eg:PetShop4的函数不足以方便我们操作数据库,如批量安排须要防注入的参数时,要求等全方位布署完再付出全部业务。

eg:PetShop4的函数在管理存款和储蓄进度里还不周密,再次来到值未有对准。

3.2   OracleHelper

留心:PetShop4在参数上在调用OracleHelper思虑了缓存,这里暂且不考虑。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OracleClient;
using System.Collections;
namespace Util
{
    public abstract class OracleHelper
    {
        /// <summary>
        /// 准备存储过程执行查询
        /// </summary>        
        /// <param name="connectionString">数据库连接</param>
        public static OracleTransaction GetTrans(string connectionString)
        {
            OracleConnection conn = new OracleConnection(connectionString);
            conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            return trans;
        }

        /// <summary>
        /// 返回视图
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataView ExecuteView(String connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(ds);
                DataView dv = ds.Tables[0].DefaultView;
                cmd.Parameters.Clear();
                return dv;
            }
        }
        /// <summary>
        /// 执行并返回影响行数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmdType">执行类型</param>
        /// <param name="cmdText">执行文本</param>
        /// <param name="commandParameters">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, IList commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a database query which does not include a select
        /// </summary>
        /// <param name="connString">Connection string to database</param>
        /// <param name="cmdType">Command type either stored procedure or SQL</param>
        /// <param name="cmdText">Acutall SQL Command</param>
        /// <param name="commandParameters">Parameters to bind to the command</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            // Create a new Oracle command
            OracleCommand cmd = new OracleCommand();

            //Create a connection
            using (OracleConnection connection = new OracleConnection(connectionString))
            {

                //Prepare the command
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }



        ///    <summary>
        ///    Execute    a OracleCommand (that returns a 1x1 resultset)    against    the    specified SqlTransaction
        ///    using the provided parameters.
        ///    </summary>
        ///    <param name="transaction">A    valid SqlTransaction</param>
        ///    <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        ///    <param name="commandText">The stored procedure name    or PL/SQL command</param>
        ///    <param name="commandParameters">An array of    OracleParamters used to execute the command</param>
        ///    <returns>An    object containing the value    in the 1x1 resultset generated by the command</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行并返回影响行数,得手动关闭数据库连接
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmdType">执行类型</param>
        /// <param name="cmdText">执行文本</param>
        /// <param name="commandParameters">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a select query that will return a result set
        /// </summary>
        /// <param name="connString">Connection string</param>
        //// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns></returns>
        public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            //Create the command and connection
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);

            try
            {
                //Prepare the command to execute
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                //Execute the query, stating that the connection should close when the resulting datareader has been read
                OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;

            }
            catch
            {

                //If an error occurs close the connection as the reader will not be used and we expect it to close the connection
                conn.Close();
                throw;
            }
        }


        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                // 若参数Value值为null时,插入到数据库的值应该为DBNull.Value,且为ParameterDirection.Input
                foreach (OracleParameter parm in commandParameters)
                    if (parm.Value == null && parm.Direction == ParameterDirection.Input)
                    {
                        cmd.Parameters.Add(parm.ParameterName, parm.OracleType, parm.Size).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add(parm);
                    }
            }
        }

        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, IList commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                // 若参数Value值为null时,插入到数据库的值应该为DBNull.Value,且为ParameterDirection.Input
                foreach (OracleParameter parm in commandParameters)
                    if (parm.Value == null && parm.Direction == ParameterDirection.Input)
                    {
                        cmd.Parameters.Add(parm.ParameterName, parm.OracleType, parm.Size).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.Add(parm);
                    }
            }
        }
    }
}

 

4       代码示例

4.1   使用存款和储蓄进程

      /// <summary>
        /// 新增
        /// </summary>
        /// <param name="v_dept">实体</param>
        /// <param name="re">返回ID</param>
        /// <param name="msg">返回消息</param>
        /// <returns></returns>
        private void executeWithOracleTrans(DEPT v_dept, ref int re, ref string msg)
        {
            try
            {
                OracleParameter[] paras = new OracleParameter[5];
                paras[0] = new OracleParameter("P_DEPTNO", OracleType.Number);
                paras[0].Value = v_dept.DEPTNO;
                paras[1] = new OracleParameter("P_DNAME", OracleType.VarChar);
                paras[1].Value = v_dept.DNAME;
                paras[2] = new OracleParameter("P_LOC", OracleType.VarChar);
                paras[2].Value = v_dept.LOC;
                paras[3] = new OracleParameter("X_RE", OracleType.Int32);
                paras[3].Direction = ParameterDirection.Output;
                paras[4] = new OracleParameter("X_MSG", OracleType.VarChar, 100);
                paras[4].Direction = ParameterDirection.Output;

               OracleHelper.ExecuteNonQuery(this.OracleConnectString, CommandType.StoredProcedure, "PKG_DEMO.Dept_Add", paras);
                re = Convert.ToInt32(paras[3].Value);
                msg = paras[4].Value.ToString();
            }
            catch (Exception ex)
            {
                re = 9;
                msg = ex.Message;
            }
        }

4.2   批管理之使用PL/SQL

      /// <summary>
        /// 用PL/SQL增加
        /// </summary>
        /// <param name="list_dept"></param>
        /// <param name="re"></param>
        /// <param name="msg"></param>
        private void executeWithPLSQL(IList<DEPT> list_dept, ref int re, ref string msg)
        {
            string sql = string.Empty;
            string insert_sql = string.Empty;
            List<OracleParameter> list_parm = new List<OracleParameter>();
            try
            {
                int i = 0;
                foreach (DEPT v_dept in list_dept)
                {
                    insert_sql += "insert into DEPT (DEPTNO, DNAME, LOC) values(:P_DEPTNO" + i + ", :P_DNAME" + i + ", :P_LOC" + i + ");";
                    OracleParameter[] paras = new OracleParameter[3];
                    paras[0] = new OracleParameter("P_DEPTNO" + i, OracleType.Number);
                    paras[0].Value = v_dept.DEPTNO;
                    paras[1] = new OracleParameter("P_DNAME" + i, OracleType.VarChar);
                    paras[1].Value = v_dept.DNAME;
                    paras[2] = new OracleParameter("P_LOC" + i, OracleType.VarChar);
                    paras[2].Value = v_dept.LOC;
                    list_parm.Add(paras[0]);
                    list_parm.Add(paras[1]);
                    list_parm.Add(paras[2]);
                    i++;
                }
                sql = "begin " +
                    insert_sql +
                  ":X_RE  := 1; " +
                  ":X_MSG := '提示:新增成功!'; " +
                  "commit; " +
                "exception " +
                  "when others then " +
                    "rollback; " +
                    ":X_RE  := 9; " +
                    ":X_MSG := '操作失败:[' || sqlcode || ':' || sqlerrm || ']'; " +
                "end; ";
                OracleParameter x_re = new OracleParameter("X_RE", OracleType.Int32);
                x_re.Direction = ParameterDirection.Output;
                OracleParameter x_msg = new OracleParameter("X_MSG", OracleType.VarChar, 100);
                x_msg.Direction = ParameterDirection.Output;
                list_parm.Add(x_re);
                list_parm.Add(x_msg);
                 OracleHelper.ExecuteNonQuery(this.OracleConnectString, CommandType.Text, sql, list_parm);
                re = Convert.ToInt32(x_re.Value);
                msg = x_msg.Value.ToString();
            }
            catch (Exception ex)
            {
                re = 9;
                msg = ex.Message;
            }
        }

 

4.3   批管理之使用专门的工作

 

       /// <summary>
        /// 用事务新增
        /// </summary>
        /// <param name="list_dept"></param>
        /// <param name="re"></param>
        /// <param name="msg"></param>
        private void executeWithTrans(IList<DEPT> list_dept, ref int re, ref string msg)
        {
            // 启用事务进行控制
            OracleTransaction myTrans = OracleHelper.GetTrans(this.OracleConnectString);
            OracleConnection conn = myTrans.Connection;
            try
            {
                string sql = string.Empty;
                foreach (DEPT o in list_dept)
                {
                    sql = "insert into DEPT(DEPTNO,DNAME,LOC) values(:P_DEPTNO,:P_DNAME,:P_LOC)";
                    OracleParameter[] paras = new OracleParameter[3];
                    paras[0] = new OracleParameter("P_DEPTNO", OracleType.Int32);
                    paras[0].Value = o.DEPTNO;
                    paras[1] = new OracleParameter("P_DNAME", OracleType.VarChar);
                    paras[1].Value = o.DNAME;
                    paras[2] = new OracleParameter("P_LOC", OracleType.VarChar);
                    paras[2].Value = o.LOC;
                    OracleHelper.ExecuteNonQuery(myTrans, CommandType.Text, sql, paras);
                }
                myTrans.Commit();
                re = 1;
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                re = 9;
                msg = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }

5       运营效果

 图片 2

6       小结

学好.Net,从PetShop开始。

源代码下载:

http://files.cnblogs.com/yongfeng/HowToConnectDataBase.rar

数据仓库储存款和储蓄进程下载:

http://files.cnblogs.com/yongfeng/Package.rar

PDF下载:

http://files.cnblogs.com/yongfeng/20130301.rar

水墨画下载:

http://files.cnblogs.com/yongfeng/Media.rar

 

 

相关文章