jdbc连接oracle数据库 调用存储过程 增加大数据类型等操作

由于头条不好发代码,有兴趣的朋友可直接打开电脑eclipse,把一下代码复制即可运行.建表语句在最后.

/**

* @author liandyao

* @date 2017-7-27

*/

public class OracleJdbc {

//连接oracle

public static Connection getCon(){

Connection con =null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

String url = "jdbc:oracle:thin:@localhost:1521:xe";

String user = "liandyao";

String password = "123";

try {

con = DriverManager.getConnection(url, user, password);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return con;

}

/**

* @param args

*/

public static void main(String[] args) {

//add();

//callPro();

//addUser();

getUser();

}

//批量添加数据

public static void add(){

Connection con = getCon();

PreparedStatement pstm = null;

String sql = "insert into TEMPS values (?)";

try {

pstm = con.prepareStatement(sql);

for(int i=0;i<1000;i++){

pstm.setInt(1, i);

pstm.addBatch();

}

pstm.executeBatch();

} catch (SQLException e) {

e.printStackTrace();

}finally{

try {

pstm.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

/**

* jdbc中使用存储过程

*/

public static void callPro(){

Connection con = getCon();

CallableStatement cast = null;

String sql = "{call pro_getemp(?,?,?)}";

try {

cast = con.prepareCall(sql);

cast.setInt(1, 10);//第一个参数

//注册第二个参数,数字型

cast.registerOutParameter(2, java.sql.Types.INTEGER);

//注册第三个参数,游标类型

cast.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);

cast.execute();//执行存储过程

int count = cast.getInt(2);//取出第二个输出参数

ResultSet rs = (ResultSet) cast.getObject(3);//第三个参数

System.out.println("部门下有:"+count+"人");

while(rs.next()){

String name = rs.getString("ename");

double sal = rs.getDouble("sal");

System.out.println("name"+name+" sal:"+sal);

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

try {

cast.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

//增加大数据类型

public static void addUser(){

Connection con = getCon();

String sql = "insert into t_user values(?,?,?,?)";

try {

PreparedStatement pstm = con.prepareStatement(sql);

pstm.setInt(1, 1);

pstm.setString(2, "高圆圆");

//Reader rd = new FileReader("E:\\05java\\01workspace\\t219\\oracle\\src\\gyy.txt");

InputStream in = OracleJdbc.class.getClassLoader().getResourceAsStream("gyy.txt");

Reader rd = new InputStreamReader(in);//将字节流转换为字符流

pstm.setClob(3,rd);

//pstm.setString(3, "sdsdsdsadsadsadsadsad");

InputStream is = OracleJdbc.class.getClassLoader().getResourceAsStream("gyy.jpg");

pstm.setBlob(4, is);

pstm.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally{

}

}

//取出大数据类型

public static void getUser(){

Connection con = getCon();

String sql = "select * from t_user";

try {

PreparedStatement pstm = con.prepareStatement(sql);

ResultSet rs = pstm.executeQuery();

while(rs.next()){

int id = rs.getInt("userid");

String name = rs.getString("username");

//Reader rd = rs.getCharacterStream("userexp");

String userexp = rs.getString("userexp");

InputStream is = rs.getBinaryStream("user_photo");

OutputStream os = new FileOutputStream("d:\\"+name+".jpg");

int len =0;

byte[] b= new byte[1024];

while((len=is.read(b))!=-1){

os.write(b, 0, len);

}

os.close();

is.close();

System.out.println(id+" "+name+" "+userexp+" ");

}

} catch (SQLException e) {

e.printStackTrace();

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

//表格

create table t_user(

userId number(10) primary key,

userName varchar2(100),

userExp clob, --字符型

user_photo blob --字节型

)