java中用事物控制语言调用数据库中的数据，以及调用存储过程或函数

定义一个工具类：

package com.etc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Util {
    
   static {//静态代码块 引入oracle 连接驱动包
      try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch (ClassNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   }
   
    public static Connection getCon(){ //定义一个静态方法与数据库连接
     Connection con=null;
    
     try {
   con =DriverManager.getConnection("jdbc:oracle:thin:@172.18.2.248:1521:leoorcl","bruce","bruce");
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
     return con;
    }
   
     public static void close(ResultSet rs,Statement st,Connection con){//定义一个静态方法关闭对应的操作
      
       if(rs == null){
           try {
     rs.close();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
         }
         if(st == null){
          try {
    st.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
        }
         if(con == null){
          try {
    con.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
        }
     }
    
    
     public static void close(PreparedStatement ps,Connection con){
      
       if(ps == null){
           try {
     ps.close();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
         }
      
       
         if(con == null){
          try {
    con.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
        }
     }
  
}

 

 

数据封装类：

package com.ect.entity;

public class UserEntity {
       
  private int name;
  private int money;
public int getName() {
 return name;
}
public void setName(int name) {
 this.name = name;
}
public int getMoney() {
 return money;
}
public void setMoney(int money) {
 this.money = money;
}
 
}

 

 

定义一个借口，里面是操作数据库的方法：

package com.etc.dao;

import java.util.List;

import com.ect.entity.UserEntity;

public interface IUserDao {  //定义一个userdao 结果
   
  public List<UserEntity> selectAll();//查询数据，返回值为数据封装类型的list
  public int insertItem(UserEntity user);//插入数据
}

 

定义一个类继承接口：

package com.ect.daoimpl;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ect.entity.UserEntity;
import com.etc.dao.IUserDao;
import com.etc.util.Util;

public class UserDaoImpl implements IUserDao { //继承userdao借口，重写方法

 @Override
 public List<UserEntity> selectAll() {
    
    List<UserEntity> list = new ArrayList<UserEntity>();
    Connection con=null;
    Statement st = null;
    ResultSet rs =null;
  
    con=Util.getCon();
   
    try {
   st= con.createStatement();  //创建一个执行语句
   rs = st.executeQuery("select * from userr");
   while(rs.next()){
    
     UserEntity user = new UserEntity();
     user.setName(rs.getInt("name"));
     user.setMoney(rs.getInt("money"));
     list.add(user);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
     Util.close(rs, st, con);
  
  return list;
 }

 public int insertItem(UserEntity user) {
    Connection con=null;
    PreparedStatement ps = null;
    int re=0;
    con=Util.getCon();
   
    try {
   con.setAutoCommit(false);
   ps = con.prepareStatement("insert into userr(name,money) values(?,?)");
   ps.setInt(1,user.getName());
   ps.setInt(2, user.getMoney());
      re = ps.executeUpdate();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  if(re>0){
   try {
    con.commit();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  } else {
   try {
    con.rollback();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
     Util.close(ps,con);
  
  return re;
 }

}

 

用单元测试测试 方法：

package com.etc.test;

import java.util.List;

import org.junit.Test;

import com.ect.daoimpl.UserDaoImpl;
import com.ect.entity.UserEntity;
import com.etc.dao.IUserDao;

public class testMain {

 @Test
 public void testSelect(){
   
  UserDaoImpl userImpl = new UserDaoImpl();
  List<UserEntity> userlist = userImpl.selectAll();
  for(UserEntity u:userlist){
   System.out.println("name:"+u.getName()+":money:"+u.getMoney());
  }
 }
 
 @Test
 public void testInsert(){
   
  UserDaoImpl userImpl = new UserDaoImpl();
  UserEntity user = new UserEntity();
  user.setName(100);
  user.setMoney(123456);
  int re = userImpl.insertItem(user);
  if(re>0){
   System.out.println("insert success");
  }else{
   System.out.println("insert faild");
  }
 }
}

 

调用存储过程和函数的代码：

package com.etc.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class testProcedure {

 /**
  * @param args
  */
 public static void main(String[] args) {
  Connection con = null;//连接数据库类
  CallableStatement cs = null; //调用存储过程或者函数的执行容器
  ResultSet rs = null; //结果返回集
     try {
  Class.forName("oracle.jdbc.driver.OracleDriver");//引入oracle 连接驱动包
  //加载连接数据库的类
  con =DriverManager.getConnection("jdbc:oracle:thin:@172.18.2.248:1521:leoorcl","bruce","bruce");
     cs = con.prepareCall("{call mypro(?)}");//调用存储过程，参数为游标
     cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);//设置返回值类型  必须设置类型，此处为cursor 类型
     cs.execute();
     rs=(ResultSet)cs.getObject(1);//将第一个值返回到ResultSet中
     while(rs.next()){
      
      System.out.println(rs.getString("name")); //此处仅是测试
     }
     /**
      *  调 函数
      *  cs = con.prepareCall("{?=call myfunc()}");//调用无参的函数
      *   cs.registerOutParameter(1, oracle.jdbc.OracleTypes.INTERGER);//函数返回值类型
      *   cs.execute(); //执行语句
      *   int re = cs.setInt(1);//获取返回值
      *   System.out.println(re);
      */
     } catch (Exception e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 }finally{
    if(con==null){
     try {
    con.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
    }
 }
 }
/**
 *  数据库中procedure
 *   create or replace procedure mypro(myre out mypack.mycur)  is
     begin
    open myre for select * from userr;
    end mypro;
   
    数据库中package
     create or replace package mypack is
     type mycur is ref cursor;
     end mypack;

 */
}