jdbc——序列日期转换执行存储过程（CallableStatement）对象序列化实例

最新推荐文章于 2020-03-22 16:55:41 发布

一JJL

最新推荐文章于 2020-03-22 16:55:41 发布

阅读量567

点赞数

CC 4.0 BY-SA版权

分类专栏：框架文章标签： CallableStatement 序列日期转换执行存储过程对象序列化实例

本文链接：https://blog.youkuaiyun.com/tcbdbd/article/details/96423619

框架专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了在JDBC中如何进行Oracle序列的创建与使用，包括查询nextval和currval。同时讲解了日期转换的注意事项，如查询时必须在关闭连接前取值。此外，还阐述了CallableStatement的用法，用于执行存储过程，包括单个输出、多个输出及图片存储到数据库的示例。最后，展示了如何通过对象序列化将对象存入和读取数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

序列

创建序列

create sequence seq_student_stuid
start with 1000
increment by 1
nomaxvalue
nominvalue
nocycle
nocache;

第一次查询要先查询nextval，然后才能查询currval
例如：

select seq_student_stuid.nextval from dual;
select seq_student_stuid.currval from dual;

日期转换

//把一个输入的日期转换为固定格式的日期
String borndt=input.next();
Date borndate=new SimpleDateFormat("yyyy-MM-dd").parse(borndt);
ps.setDate(4,new java.sql.Date(borndate.getTime()));
//查询学生表中的出生日期
select stuname,to_char(borndate,'yyyy-mm-dd hh24:mi:ss') from student;
//此时查询到的时间时分秒都是0
//如果需要时间精确到时分秒，需要使用时间戳
//时间戳内放的是毫秒数
ps.setTimestamp(4,new Timestamp(borndate.getTime()));

如果是查询，取完值了才能close
增加删除修改，执行完了就可以close

执行存储过程

CallableStatement
用于执行sql存储过程的界面
格式：{call p_getsal(?)}，有几个参数就写几个问号
对于输入类型的参数，直接使用setInt等赋值即可
对于输出类型的参数，要先注册再执行
----过程返回单个数据

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLType;
/*
 * create or replace procedure p_getsal
(
    v_empno in out number
)
is
begin
   select sal into v_empno from emp where empno=v_empno;
end;
 */
public class ExecuteProc {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url, user, password);
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]} 
		CallableStatement call= conn.prepareCall("{call p_getsal(?)}");
		//输入型 in setxxx()赋值即可
		call.setInt(1, 7788);
		//输出型 out 先注册
		call.registerOutParameter(1, java.sql.Types.NUMERIC);
		call.execute();
		
		double sal=call.getDouble(1);
		System.out.println("7788工号的工资是:"+sal);
		call.close();
		conn.close();
		
	}
}

----过程返回多个数据

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

/*
 * create or replace procedure p_getemp
(
    v_deptno in number,
    v_result out sys_refcursor
)
is
begin
    open v_result for select ename,job,sal from emp where deptno=v_deptno;
  end;
 */
public class ExecuteCursor {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url, user, password);
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]} 
		CallableStatement call= conn.prepareCall("{call p_getemp(?,?)}");
		call.setInt(1, 10);
		call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR );
		call.execute();
		//将拿到的结果强转为游标类型
		ResultSet rs= (ResultSet)call.getObject(2);
        while(rs.next()) {
        	System.out.println(rs.getString("ename")+"\t"+rs.getString("job")+"\t"+rs.getDouble("sal"));
        }
		rs.close();
		call.close();
		conn.close();
		
	}
}

----把图片存入数据库
–首先创建表

create table images
(
imgid number(10),
image blob,
imgname varchar(50)
);

–往表中添加数据

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class AddBlob {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url, user, password);
		String sql="insert into images values(?,?,?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, 1001);
		InputStream in=new FileInputStream("D:\\a.jpg"); 
		ps.setBlob(2, in);
		ps.setString(3, "a.jpg");
		int rows=ps.executeUpdate();
		ps.close();
		conn.close();
		if(rows>0) {
			System.out.println("添加成功.............");
		}
		
	}
}

–取出表中图片

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ReadBLOB {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url, user, password);
		String sql="select * from images where imgid=1001";
		PreparedStatement ps=conn.prepareStatement(sql);
		ResultSet rs= ps.executeQuery();
		if(rs.next()) {
			int imgid=rs.getInt("imgid");
			String imagename=rs.getString("imgname");
			Blob blob=rs.getBlob("image");
			InputStream in=blob.getBinaryStream();
			OutputStream out=new FileOutputStream("d:\\"+imagename);
			byte [] bt=new byte[1024];
			int len=0;
			while((len=in.read(bt))!=-1) {
				out.write(bt,0,len);
			}
			in.close();
			out.close();
			System.out.println("文件编号:"+imgid+" 文件名:"+imagename);
		}
		rs.close();
		conn.close();
		ps.close();
		
	}
}

对象序列化

将一个对象存入数据库，并读取出来
首先创建一个对象

import java.io.Serializable;
import java.util.Date;

public class Student implements Serializable{
	private int stuId;
	private String stuname;
	private String gender;
	private Date borndate;
	private String phone;
	private String address;
	public int getStuId() {
		return stuId;
	}
	public void setStuId(int stuId) {
		this.stuId = stuId;
	}
	public String getStuname() {
		return stuname;
	}
	public void setStuname(String stuname) {
		this.stuname = stuname;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public Date getBorndate() {
		return borndate;
	}
	public void setBorndate(Date borndate) {
		this.borndate = borndate;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Student(int stuId, String stuname, String gender, Date borndate, String phone, String address) {
		super();
		this.stuId = stuId;
		this.stuname = stuname;
		this.gender = gender;
		this.borndate = borndate;
		this.phone = phone;
		this.address = address;
	}
	@Override
	public String toString() {
		return "Student [stuId=" + stuId + ", stuname=" + stuname + ", gender=" + gender + ", borndate=" + borndate
				+ ", phone=" + phone + ", address=" + address + "]";
	}
	
}

在oracle数据库中创建一个表，用来存储这个对象

create table tbobjects
(oid number(10) not null,
object blob not null
);

把对象存入数据库表中

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class Test {
	public static void main(String[] args) throws ParseException, ClassNotFoundException, SQLException, IOException {
		List<Student> list=new ArrayList<Student>();
		DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
		list.add(new Student(10000,"张三","男",df.parse("2009-9-13"),"13899990000","许昌市魏都区八一东路"));
		list.add(new Student(10001,"李四","男",df.parse("2009-8-13"),"13899990000","许昌市魏都区八一东路"));
		list.add(new Student(10002,"王五","女",df.parse("2009-9-19"),"13899990000","许昌市魏都区八一东路"));
		list.add(new Student(10003,"赵六","男",df.parse("2009-12-13"),"13899990000","许昌市魏都区八一东路"));
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url, user, password);
		
		String sql="insert into tbobjects values(?,?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, 100);

		ByteArrayOutputStream bo=new ByteArrayOutputStream();
		ObjectOutputStream out=new ObjectOutputStream(bo);
		out.writeObject(list);
		bo.close();
		out.close();
		byte [] bt=bo.toByteArray();		
		InputStream in=new ByteArrayInputStream(bt);
		ps.setBlob(2, in);
		int rows=ps.executeUpdate();
		ps.close();
		conn.close();
		if(rows>0) {
			System.out.println("保存成功");
		}
		
		
	}
}

把对象从数据库中读出

import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class ReadList {
	public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url, user, password);
		String sql="select * from tbobjects where oid=?";
		PreparedStatement ps= conn.prepareStatement(sql);
		ps.setInt(1, 100);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			int oid=rs.getInt("oid");
			System.out.println("对象编号:"+oid);
			Blob blob=rs.getBlob("object");
			InputStream in= blob.getBinaryStream();
			ObjectInputStream oin=new ObjectInputStream(in);
			List<Student> list=(List<Student>) oin.readObject();
			oin.close();
			for(Student stu: list) {
				System.out.println(stu);
			} 
		}
		rs.close();
		ps.close();
		conn.close();
	}
}