Oracle
下面用JDBC连接Oracle数据库去操作大文本数据和二进制数据
//使用JDBC连接Oracle数据库操作二进制数据 /* * 对于Oracle数据库和Mysql数据库是有所不同的。 * 1.Oracle定义了BLOB字段，但是这个字段不是真正地存储二进制数据 * 2.向这个字段存一个BLOB指针，获取到Oracle的BLOB对象,把二进制数据放到这个指针里面,指针指向BLOB字段 * 3.需要事务支持 * * */ public class Demo7 { @Test public void add() { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = UtilsDemo.getConnection(); //开启事务 connection.setAutoCommit(false); //插入一个BLOB指针 String sql = "insert into test4(id,image) values(?,empty_blob())"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, 1); preparedStatement.executeUpdate(); //把BLOB指针查询出来,得到BLOB对象 String sql2 = "select image from test4 where id= ? for update"; preparedStatement = connection.prepareStatement(sql2); preparedStatement.setInt(1, 1); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { //得到Blob对象--当成是Oracle的Blob,不是JDBC的,所以要强转[导的是oracle.sql.BLOB包] BLOB blob = (BLOB) resultSet.getBlob("image"); //写入二进制数据 OutputStream outputStream = blob.getBinaryOutputStream(); //获取到读取文件读入流 InputStream inputStream = Demo7.class.getClassLoader().getResourceAsStream("01.jpg"); int len=0; byte[] bytes = new byte[1024]; while ((len = inputStream.read(bytes)) > 0) { outputStream.write(bytes, 0, len); } outputStream.close(); inputStream.close(); connection.setAutoCommit(true); } } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { UtilsDemo.release(connection, preparedStatement, null); } } @Test public void find() { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = UtilsDemo.getConnection(); String sql = "SELECT * FROM test4 WHERE id=1"; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { //获取到BLOB对象 BLOB blob = (BLOB) resultSet.getBlob("image"); //将数据读取到磁盘上 InputStream inputStream = blob.getBinaryStream(); FileOutputStream fileOutputStream = new FileOutputStream("d:\\zhongfucheng.jpg"); int len=0; byte[] bytes = new byte[1024]; while ((len = inputStream.read(bytes)) > 0) { fileOutputStream.write(bytes, 0, len); } inputStream.close(); fileOutputStream.close(); } } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { UtilsDemo.release(connection, preparedStatement, null); } } }
对于JDBC连接Oracle数据库操作CLOB数据,我就不再重复了,操作跟BLOB几乎相同
4.获取数据库的自动主键列
为什么要获取数据库的自动主键列数据?
应用场景:
有一张老师表，一张学生表。现在来了一个新的老师，学生要跟着新老师上课。
我首先要知道老师的id编号是多少，学生才能知道跟着哪个老师学习【学生外键参照老师主键】。
@Test public void test() { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); String sql = "INSERT INTO test(name) VALUES(?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "ouzicheng"); if (preparedStatement.executeUpdate() > 0) { //获取到自动主键列的值 resultSet = preparedStatement.getGeneratedKeys(); if (resultSet.next()) { int id = resultSet.getInt(1); System.out.println(id); } } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(connection, preparedStatement, null); }
5.调用数据库的存储过程
调用存储过程的语法：
{call <procedure-name>[(<arg1>,<arg2>, ...)]}
调用函数的语法：
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
如果是Output类型的，那么在JDBC调用的时候是要注册的。如下代码所示：
/* jdbc调用存储过程 delimiter $$ CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255)) BEGIN SELECT CONCAT('zyxw---', inputParam) into inOutParam; END $$ delimiter ; */ //我们在JDBC调用存储过程,就像在调用方法一样 public class Demo9 { public static void main(String[] args) { Connection connection = null; CallableStatement callableStatement = null; try { connection = JdbcUtils.getConnection(); callableStatement = connection.prepareCall("{call demoSp(?,?)}"); callableStatement.setString(1, "nihaoa"); //注册第2个参数,类型是VARCHAR callableStatement.registerOutParameter(2, Types.VARCHAR); callableStatement.execute(); //获取传出参数[获取存储过程里的值] String result = callableStatement.getString(2); System.out.println(result); } catch (Exception e) { e.printStackTrace(); }finally { try { connection.close(); callableStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } }
参考资料：
----------------------------------------------------------------------------------过程 #修改mysql语句的结果符为// mysql > delimiter // #定义一个过程，获取users表总记录数，将10设置到变量count中 create procedure simpleproc(out count int) begin select count(id) into count from users; end // #修改mysql语句的结果符为; mysql > delimiter ; #调用过程，将结果覆给变量a，@是定义变量的符号 call simpleproc(@a); #显示变量a的值 select @a; //以下是Java调用Mysql的过程 String sql = "{call simpleproc(?)}"; Connection conn = JdbcUtil.getConnection(); CallableStatement cstmt = conn.prepareCall(sql); cstmt.registerOutParameter(1,Types.INTEGER); cstmt.execute(); Integer count = cstmt.getInt(1); System.out.println("共有" + count + "人"); ----------------------------------------------------------------------------------函数 #修改mysql语句的结果符为// mysql > delimiter // #定义一个函数，完成字符串拼接 create function hello( s char(20) ) returns char(50) return concat('hello，',s,'!'); // #修改mysql语句的结果符为; mysql > delimiter ; #调用函数 select hello('world'); //以下是Java调用Mysql的函数 String sql = "{? = call hello(?)}"; Connection conn = JdbcUtil.getConnection(); CallableStatement cstmt = conn.prepareCall(sql); cstmt.registerOutParameter(1,Types.VARCHAR); cstmt.setString(2,"zhaojun"); cstmt.execute(); String value = cstmt.getString(1); System.out.println(value); JdbcUtil.close(cstmt); JdbcUtil.close(conn);
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/format,webp/resize,h_160,m_lfit)