由于头条不好发代码,有兴趣的朋友可直接打开电脑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 --字节型
)
