1.创建视图:
create or replace view es_company_code as
(select ESCO_COMPANY_NO as COMPANY_CODE,ESCO_COMPANY_NAME_CN as COMPANY_NAME,ESCO_COMPANY_TYPE AS COMPANY_TYPE from ES_COMPANY
where ESCO_COMPANY_TYPE like '%C%');
2.使用java调用
- 数据库访问类
/**
*
*/
package db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 〈一句话功能简述〉 〈功能详细描述〉 版本:1.0 作者:Bill 修改日期:2017-11-27 修改内容:
*/
public class DbConnectionUtil {
private final static String driver = "oracle.jdbc.driver.OracleDriver";
private final static String url = "jdbc:oracle:thin:@172.23.2.19:1521/pdborcl.localdomain";
private final static String account = "xxxxx";
private final static String password = "xxxxx";
public static Connection openConnectionDB() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, account, password);
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
return conn;
}
}
// 关闭连接
public static void closeConnectionDB(Connection conn, CallableStatement stat, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stat = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
}
*
*/
package db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 〈一句话功能简述〉 〈功能详细描述〉 版本:1.0 作者:Bill 修改日期:2017-11-27 修改内容:
*/
public class DbConnectionUtil {
private final static String driver = "oracle.jdbc.driver.OracleDriver";
private final static String url = "jdbc:oracle:thin:@172.23.2.19:1521/pdborcl.localdomain";
private final static String account = "xxxxx";
private final static String password = "xxxxx";
public static Connection openConnectionDB() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, account, password);
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
return conn;
}
}
// 关闭连接
public static void closeConnectionDB(Connection conn, CallableStatement stat, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stat = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
}
2.调用
/**
*
*/
package db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import db.model.Company;
/**
* 〈一句话功能简述〉 〈功能详细描述〉 版本:1.0 作者:Bill 修改日期:2017-11-27 修改内容:
*/
public class DbViewDemo {
/**
* 〈一句话功能简述〉
*
* @param
* @param
* @return
* @author Bill
* @throws
*/
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
conn = DbConnectionUtil.openConnectionDB();
cstmt = conn.prepareCall("SELECT * FROM es_company_code WHERE COMPANY_TYPE=?");
cstmt.setString(1, "C");
rs = cstmt.executeQuery();// 执行
List<Company> companyList = new ArrayList<Company>();
while (rs.next()) {
Company company = new Company(rs.getString("COMPANY_CODE"), rs.getString("COMPANY_NAME"), rs.getString("COMPANY_TYPE"));
System.out.println(company);
companyList.add(company);
}
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception exception) {
exception.printStackTrace();
} finally {
DbConnectionUtil.closeConnectionDB(conn, cstmt, rs);
}
}
}
*
*/
package db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import db.model.Company;
/**
* 〈一句话功能简述〉 〈功能详细描述〉 版本:1.0 作者:Bill 修改日期:2017-11-27 修改内容:
*/
public class DbViewDemo {
/**
* 〈一句话功能简述〉
*
* @param
* @param
* @return
* @author Bill
* @throws
*/
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
conn = DbConnectionUtil.openConnectionDB();
cstmt = conn.prepareCall("SELECT * FROM es_company_code WHERE COMPANY_TYPE=?");
cstmt.setString(1, "C");
rs = cstmt.executeQuery();// 执行
List<Company> companyList = new ArrayList<Company>();
while (rs.next()) {
Company company = new Company(rs.getString("COMPANY_CODE"), rs.getString("COMPANY_NAME"), rs.getString("COMPANY_TYPE"));
System.out.println(company);
companyList.add(company);
}
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception exception) {
exception.printStackTrace();
} finally {
DbConnectionUtil.closeConnectionDB(conn, cstmt, rs);
}
}
}