Java JDBC数据库之存储过程调用

xiaoxiao2021-02-28 29

http://www.verejava.com/?id=16998480035641

下载 Oracle 或者 Mysql Jar 驱动 mysql-connector-java-3.0.10-stable-bin.jar ojdbc14.jar package com.db; 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 DBUtil { private Connection conn;//数据库的链接对象 public DBUtil() { //加载驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //打开数据的链接 public Connection openConnection() { try { conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "myspace", "myspace123"); } catch (SQLException e) { e.printStackTrace(); } return conn; } //执行 INSERT DELETE UPDATE 的方法 public boolean executeUpdate(String sql) { //打开数据库 openConnection(); //创建执行 sql 的对象 try { Statement stmt=conn.createStatement(); //执行sql 语句 if(stmt.executeUpdate(sql)>0) { return true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBClose(); } return false; } //执行 sql 的查找 SELECT 的方法 public ResultSet executeQuery(String sql) { //打开数据库链接 openConnection(); //创建执行sql 的对象 Statement stmt=null; ResultSet rs=null; try { stmt = conn.createStatement(); rs=stmt.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); } return rs; } //绑定的sql INSERT DELETE UPDATE 方法 public boolean executeUpdate(String sql,Object[] params) { //打开数据库 openConnection(); //创建绑定对象 try { PreparedStatement pstmt=conn.prepareStatement(sql); //填充？的值 for(int i=0;params!=null&&i<params.length;i++) { if(params[i] instanceof Integer) { pstmt.setInt(i+1, Integer.parseInt(params[i].toString())); continue; } if(params[i] instanceof Float) { pstmt.setFloat(i+1, Float.parseFloat(params[i].toString())); continue; } if(params[i] instanceof Double) { pstmt.setDouble(i+1, Double.parseDouble(params[i].toString())); continue; } if(params[i] instanceof String) { pstmt.setString(i+1, params[i].toString()); } //java.sql.Date, java.util.Date if(params[i] instanceof java.util.Date) { //将 java.util.Date 转成 java.sql.Date java.util.Date javaDate=(java.util.Date)params[i]; java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime()); pstmt.setDate(i+1, sqlDate); continue; } if(params[i] instanceof java.util.Timestamp) { //将 java.util.Date 转成 java.sql.Date java.util.Date javaDate=(java.util.Date)params[i]; java.sql.Timestamp timestamp=new java.sql.Timestamp(javaDate.getTime()); pstmt.setTimestamp(i+1, timestamp); continue; } } //执行绑定的 sql 语句 if(pstmt.executeUpdate()>0) { return true; } } catch (SQLException e) { e.printStackTrace(); } finally { DBClose(); } return false; } //绑定的sql 查找 SELECT 方法 public ResultSet executeQuery(String sql,Object[] params) { //打开数据库 openConnection(); //创建绑定对象 try { PreparedStatement pstmt=conn.prepareStatement(sql); //填充？的值 for(int i=0;params!=null&&i<params.length;i++) { if(params[i] instanceof Integer) { pstmt.setInt(i+1, Integer.parseInt(params[i].toString())); continue; } if(params[i] instanceof Float) { pstmt.setFloat(i+1, Float.parseFloat(params[i].toString())); continue; } if(params[i] instanceof Double) { pstmt.setDouble(i+1, Double.parseDouble(params[i].toString())); continue; } if(params[i] instanceof String) { pstmt.setString(i+1, params[i].toString()); } //java.sql.Date, java.util.Date if(params[i] instanceof java.util.Date) { //将 java.util.Date 转成 java.sql.Date java.util.Date javaDate=(java.util.Date)params[i]; java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime()); pstmt.setDate(i+1, sqlDate); continue; } if(params[i] instanceof java.util.Timestamp) { //将 java.util.Date 转成 java.sql.Date java.util.Date javaDate=(java.util.Date)params[i]; java.sql.Timestamp timestamp=new java.sql.Timestamp(javaDate.getTime()); pstmt.setTimestamp(i+1, timestamp); continue; } } //执行绑定的 sql 语句 return pstmt.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return null; } //关闭数据库链接 public void DBClose() { if(conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } package com.sp.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import com.db.DBUtil; public class TestAdd { public static void main(String[] args) { DBUtil db=new DBUtil(); //打开数据库链接 Connection conn=db.openConnection(); //定义存储过程的 sql String sql="call pgk_dept.sp_add_dept(?,?)"; //执行 sql 存储过程的对象 try { CallableStatement cstmt=conn.prepareCall(sql); //绑定值 cstmt.setInt(1, 1); cstmt.setString(2, "IT"); //执行 cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { db.DBClose(); } } } package com.sp.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import com.db.DBUtil; public class TestDelete { public static void main(String[] args) { DBUtil db=new DBUtil(); //打开数据库链接 Connection conn=db.openConnection(); //定义存储过程的 sql String sql="call pgk_dept.sp_delete_dept(?)"; //执行 sql 存储过程的对象 try { CallableStatement cstmt=conn.prepareCall(sql); //绑定值 cstmt.setInt(1, 1); //执行 cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { db.DBClose(); } } } package com.sp.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import com.db.DBUtil; public class TestUpdate { public static void main(String[] args) { DBUtil db=new DBUtil(); //打开数据库链接 Connection conn=db.openConnection(); //定义存储过程的 sql String sql="call pgk_dept.sp_update_dept(?,?)"; //执行 sql 存储过程的对象 try { CallableStatement cstmt=conn.prepareCall(sql); //绑定值 cstmt.setInt(1, 1); cstmt.setString(2, "测试"); //执行 cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { db.DBClose(); } } } package com.sp.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import oracle.jdbc.OracleTypes; import com.db.DBUtil; public class TestFind { public static void main(String[] args) { DBUtil db=new DBUtil(); //打开数据库链接 Connection conn=db.openConnection(); //定义存储过程的 sql String sql="call pgk_dept.sp_find_dept(?)"; //执行 sql 存储过程的对象 try { CallableStatement cstmt=conn.prepareCall(sql); //绑定值设置传出来的参数 cstmt.registerOutParameter(1, OracleTypes.CURSOR); //执行存储过程才调用 cstmt.executeUpdate(); //获得游标参数 ResultSet rs=(ResultSet)cstmt.getObject(1); while(rs.next()) { int id=rs.getInt("id"); String name=rs.getString("name"); System.out.println(id+","+name); } } catch (SQLException e) { e.printStackTrace(); } finally { db.DBClose(); } } }

http://www.verejava.com/?id=16998480035641

转载请注明原文地址: https://www.6miu.com/read-2624546.html

技术

最新回复(0)

Java JDBC数据库 之 存储过程调用

技术

Java JDBC数据库之存储过程调用