Oracle-JDBC

JDBC连接

导入Oracle依赖

<dependency>
  <groupId>com.oracle</groupId>
  <artifactId>ojdbc6</artifactId>
  <version>11.2.0.3</version>
</dependency>

导入阿里连接池依赖

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.16</version>
</dependency>

配置文件 druid.properties

driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=scott
password=Xian0924
initialSize=5
minIdle=5
maxActive=10
maxWait=3000

连接池类

package com.dbutils;
import com.alibaba.druid.pool.DruidDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
 * @author 羡羡
 */
public class AliPool {
    static DruidDataSource ds;
    //只会运行一次
    static {
        String fname ="druid.properties";
        InputStream file= AliPool.class.getClassLoader().getResourceAsStream(fname);
        Properties prop = new Properties();
        try {
            prop.load(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //读取文件
        String driver =prop.getProperty("driver");
        String url =prop.getProperty("url");
        String name =prop.getProperty("username");
        String pass =prop.getProperty("password");
        String init =prop.getProperty("initialSize");
        String min =prop.getProperty("minIdle");
        String max =prop.getProperty("maxActive");
        String wait =prop.getProperty("maxWait");
        // 配置连接池
        ds = new DruidDataSource();
        ds.setDriverClassName(driver);
        ds.setUrl( url );
        ds.setUsername( name);
        ds.setPassword( pass);
        ds.setInitialSize( new Integer(init));
        ds.setMaxActive( new Integer(max));
        ds.setMinIdle(new Integer(min));
        ds.setMaxWait(new Long(wait));
    }
    // 池里取连接
    public static Connection getConn() {
        Connection conn = null;
        try {
            conn= ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

测试连接

package com.dbutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * @author 羡羡
 */
public class Test {
    public static void main(String[] args) throws SQLException {
        Connection conn=AliPool.getConn();
        System.out.println(conn);
        String se="select NICKNAME from lolgame";
        Statement sta=conn.createStatement();
        ResultSet res=sta.executeQuery(se);
        String cou="";
        while (res.next()) {
            cou=res.getString(1);
            System.out.println(cou);
        }
    }
}

Oacle分页

select * from (select 表别名.*,rownum 行别名 from (select * from 表名) 表别名 where rownum <=A) where 行别名>=B

select * from (select t.*,rownum rn from (select * from emp) t where rownum <=5) where rn>=0;

A:page*size; A=rownum  5=1*5;
B:A-size; B=rn  0=5-5

BLOB存图片

D：\Maven\OracleJDBC

/**
 * 保存图片
 */
@Test
public void save() throws IOException, SQLException {
    //本地图片路径
    File file=new File("D:\\pictures\\头像\\ia_1900000015.jfif");
    int length=(int)file.length();
    //文件输入流
    InputStream input=new FileInputStream(file);
    String sq="insert into IDCARD(ID,pic) values (?,?)";
    Connection conn= AliPool.getConn();
    PreparedStatement stam=conn.prepareStatement(sq);
    stam.setString(1,"1");
    stam.setBinaryStream(2,input,length);
    stam.execute();
    input.close();
}

blob存图

BLOB取出图片

/**
 * 取出图片
 */
@Test
public void Take() throws SQLException, IOException {
    //文件存放地址
    File fil=new File("C:\\Users\\羡羡\\Desktop\\1.jfif");
    String se="select * from IDCARD where ID=?";
    Connection conn=AliPool.getConn();
    PreparedStatement st=conn.prepareStatement(se);
    st.setObject(1,"1");
    ResultSet res=st.executeQuery();
    if(res.next()) {
        OutputStream fos=new FileOutputStream(fil);
        InputStream is=res.getBinaryStream("pic");
        byte[] buff=new byte[4*1024];
        int leng=0;
        while((leng=is.read(buff))!=-1){
            fos.write(buff,0,leng);
        }
        fos.flush();
        fos.close();
        is.close();
    }
}

调用储存过程

储存过程 out 输出的值

CREATE or replace PROCEDURE seall(lid INTEGER,nick out VARCHAR2,pr out NUMBER,nerr out VARCHAR)
as
lolinf LOLGAME%rowtype;
BEGIN
SELECT * INTO lolinf  FROM LOLGAME WHERE ID=lid;
nick:=lolinf.NICKNAME;
pr:=lolinf.PRICE;
EXCEPTION
when no_data_found then
nerr:='没有数据！';
when others then
nerr:='查询错误！';
end;

Java调用储存过程

/**
 * 调用Oracle储存过程
 * @throws SQLException
 */
@Test
public void StorageProcess() throws SQLException {
    //调用Oracle储存过程
    String sql="{call seall(?,?,?,?)}";
    Connection conn= AliPool.getConn();
    CallableStatement st=conn.prepareCall(sql);
    //传入的参数
    st.setInt(1,9);
    //返回的参数(oracle.jdbc.OracleTypes.VARCHAR表示返回参数类型)
    st.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
    st.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);
    st.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
    //执行
    st.execute();
    //拿到返回的值，我的存储过程的参数1是传入参数，2,3,4是传出参数
    System.out.println(st.getString(2) + " "+ st.getString(3) + " "+ st.getString(4));
    st.close();
    conn.close();
}