Java OracleConnection类代码示例

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
/** Methode utilisee si on n'utilise PAS les classes maison "OxygenePresistenceBroker" et "GeOxygeneStatementManager".
 * Ceci est a definir dans OJB.properties.
 * Inconvenient : on utilise une variable connection statique,
 * donc impossible de se connecter a plusieurs bases Oracle simultanement.
 * De plus il y a un bug avec OJB : impossible d'ecrire des geometries nulles.
 */
public static Object javaToSql (Object object) {
	try {
		Geometry sdoGeom = IsoAndSdo.iso2sdoapi(GF,(GM_Object)object);
		SDOTemplateFactory sdoTF;
		if (CONNECTION instanceof BatchConnection)  {// ceci est pour OJB
			OracleConnection oConn = (OracleConnection) ((BatchConnection)CONNECTION).getDelegate();
			sdoTF = new SDOTemplateFactoryImpl(oConn);
		} else
			sdoTF = new SDOTemplateFactoryImpl((OracleConnection)CONNECTION);

		STRUCT str = SDOGeometry.geometryToSTRUCT(sdoGeom,sdoTF);
		return str;
	} catch (Exception e) {
		e.printStackTrace();
		return null;
	}
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
public static Object GM_Object2Sdo(Object object, Connection conn) {
	try {
		GeometryFactory gf = OraSpatialManager.getGeometryFactory();
		Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf,(GM_Object)object);
		SDOTemplateFactory sdoTF;
		if (conn instanceof BatchConnection)  {// ceci est pour OJB
			OracleConnection oConn = (OracleConnection) ((BatchConnection)conn).getDelegate();
			sdoTF = new SDOTemplateFactoryImpl(oConn);
		} else
			sdoTF = new SDOTemplateFactoryImpl((OracleConnection)conn);

		STRUCT str = SDOGeometry.geometryToSTRUCT(sdoGeom,sdoTF);
		return str;
	} catch (Exception e) {
		e.printStackTrace();
		return null;
	}
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
/**
 * delegated back to DBDriver to take care of driver related issues between
 * Oracle and standard SQL
 *
 * @param con
 * @param values
 * @param dbArrayType
 *            as defined in the RDBMS
 * @return object that is suitable to be assigned to an array parameter
 * @throws SQLException
 */
public static Array createArray(Connection con, Value[] values,
		String dbArrayType) throws SQLException {
	Object[] data = new Object[values.length];
	for (int i = 0; i < values.length; i++) {
		Value val = values[i];
		if (val != null) {
			data[i] = val.toObject();
		}
	}
	if (dbVendor == DbVendor.ORACLE) {
		OracleConnection ocon = toOracleConnection(con);
		ArrayDescriptor ad = ArrayDescriptor.createDescriptor(dbArrayType,
				ocon);
		return new ARRAY(ad, ocon, data);
	}
	return con.createArrayOf(dbArrayType, data);
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
private static OracleConnection toOracleConnection(Connection con) {
	if (con instanceof OracleConnection) {
		return (OracleConnection) con;
	}
	try {
		return con.unwrap(OracleConnection.class);
	} catch (Exception e) {
		throw new ApplicationError(
				"Error while unwrapping to Oracle connection. This is a set-up issue with your server. It is probably using a pooled-connection with a flag not to allow access to underlying connection object "
						+ e.getMessage());
	}
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
private OracleConnection getConnection() throws SQLException {
    OracleDriver driver = new OracleDriver();
    Properties prop = new Properties();
    prop.setProperty("user",this.USERNAME);
    prop.setProperty("password",this.PASSWORD);
    return (OracleConnection)driver.connect(this.URL,prop);
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
/**
 * Default constructor
 */
public DatabaseConnection(){

	//open the database
	try {
		this.dbConnection = (OracleConnection) DriverManager.getConnection(DB_ADDRESS,USER_NAME, PASSWORD);
		cursor=0;
	} catch (SQLException e) {
		e.printStackTrace();
		IS_OPEN = false;
	}
	//set isOpen to true
	IS_OPEN = true;
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
private void run() throws Exception {
    System.out.println("Event Consumer Start...");
    OracleConnection connection = getConnection();
    Properties properties = new Properties();
    properties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
    properties.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, "true"); //Activates query change notification instead of object change notification.

    // Register notifier
    DatabaseChangeRegistration reg = connection.registerDatabaseChangeNotification(properties);

    reg.addListener(new DatabaseChangeListener() {
        @Override
        public void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent) {
            //TableChangeDescription tcd =  databaseChangeEvent.getTableChangeDescription()[0];
            QueryChangeDescription qcd =  databaseChangeEvent.getQueryChangeDescription()[0];
            TableChangeDescription tcd = qcd.getTableChangeDescription()[0];
            for(RowChangeDescription rcd : tcd.getRowChangeDescription()){
                System.out.println("Updated Row ID:" + rcd.getRowid().stringValue() + " Operation:" + rcd.getRowOperation().name());
                //System.out.printf("Full event: " + databaseChangeEvent);
            /*// emit rowid to Kafka cluster
                ProducerRecord<String,String> record = new ProducerRecord<String,String>(Producer.KAFKA_TOPIC, rcd.getRowid().stringValue(), rcd.getRowid().stringValue());

                Producer.getKafkaProducer().send(record, new Callback() {
                    @Override
                    public void onCompletion(RecordMetadata recordMetadata, Exception e) {
                        if(e != null){
                            e.printStackTrace();
                        } else{
                            System.out.printf("Message sent!");
                        }
                    }
                });*/
            }
        }
    });

    String query = "select * from temp t where t.a = 'a1'";
    Statement stm = connection.createStatement();
    ((OracleStatement) stm).setDatabaseChangeRegistration(reg);

    ResultSet rs = stm.executeQuery(query);
    //stm.executeQuery("select 1 from fdc_vt where 1!=2");
    //stm.executeQuery("select 1 from fdc_pt where 1!=2");
    while(rs.next()){
    }
    // get tables from dcr
    String[] tables = reg.getTables();
    for(String str : tables){
        System.out.println("Tables:"+ str);
    }
    rs.close();
    stm.close();
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
public static String CheckOracleRequisites(Connection connection,
		String userID) {
	String sql = "";
	String role = "";
	String message = "";
	if ((userID != null) && (userID.length() != 0))
		userID = userID.toUpperCase();
	try {
		((OracleConnection) connection).setSessionTimeZone(TimeZone
				.getDefault().getID());
		try {
			sql = "SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '"
					+ userID + "' AND GRANTED_ROLE = 'DBA'";
			PreparedStatement partStatement = connection
					.prepareStatement(sql);
			ResultSet rs = partStatement.executeQuery();
			role = "";
			while (rs.next()) {
				if (role.equals("")) {
					role = rs.getString(1);
					continue;
				}
				role = ":" + role + rs.getString(1);
			}
			if (!role.startsWith("DBA")) {
				message = "DBA ROLE is not available to user '" + userID
						+ "'. Please consult your Oracle DBA.";
			}
			if (rs != null)
				rs.close();
			if (partStatement != null)
				partStatement.close();
			log(message);
			return message;
		} catch (SQLException e2) {
			if (e2.getErrorCode() == 942) {
				message = "DBA / SELECT_CATALOG_ROLE not available to user '"
						+ userID + "'. Please consult your Oracle DBA.";
				log(message);
				return message;
			}
		}
	} catch (SQLException e) {
		try {
			((OracleConnection) connection)
					.setSessionTimeZone(getTimeZoneOffset());
		} catch (Exception ed) {
			sql = "ALTER SESSION SET TIME_ZONE='" + getTimeZoneOffset()
					+ "'";
			log("Serious Error Ora-1804: Unable to set timezone for Oracle. Trying "
					+ sql);
			try {
				PreparedStatement statement = connection
						.prepareStatement(sql);
				int rc = statement.executeUpdate();
				if (statement != null)
					statement.close();
			} catch (SQLException e1) {
				log("Alternate method did not work. It looks that the timezone file on Oracle server are not compatible with current version of Oracle.");
				log("Try \"select count(*) from v$timezone_names\" at your SQL*Plus and if count is 0, contact Oracle support to get a fix.");
				log("You can also look doc ID: 414590.1 and 417893.1 for a solution in metalink.oracle.com");
				log("Continuing with the movement of data and you might notice Oracle -01866 error for the tables having TIMESTAMP column");
				message = "Serious Timezone problem in Oracle database. See console log.";
			}
		}
	}
	return message;
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
@Override
public Object getDatabaseObject(GeometryObject geomObj, Connection connection) throws SQLException {
	JGeometry geometry = null;

	switch (geomObj.getGeometryType()) {
	case POLYGON:
		geometry = convertPolygonToJGeometry(geomObj);
		break;
	case LINE_STRING:
		geometry = convertCurveToJGeometry(geomObj);
		break;
	case POINT:
		geometry = convertPointToJGeometry(geomObj);
		break;
	case MULTI_LINE_STRING:
		geometry = convertMultiCurveToJGeometry(geomObj);
		break;
	case MULTI_POINT:
		geometry = convertMultiPointToJGeometry(geomObj);
		break;
	case ENVELOPE:
		geometry = convertEnvelopeToJGeometry(geomObj);
		break;
	case MULTI_POLYGON:
		geometry = convertMultiPolygonToJGeometry(geomObj);
		break;
	case SOLID:
		geometry = convertSolidToJGeometry(geomObj);
		break;
	case COMPOSITE_SOLID:
		geometry = convertCompositeSolidToJGeometry(geomObj);
		break;
	}

	if (geometry == null)
		throw new SQLException("Failed to convert geometry to internal database representation.");

	try {
		return JGeometry.storeJS(connection.unwrap(OracleConnection.class), geometry);
	} catch (Exception e) {
		throw new SQLException(e.getMessage(), e);
	}
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
/**
 * This is delegated back to DbDriver because oracle driver does not support
 * standard SQL way of doing this. Let DbDriver class be the repository of
 * all Driver related issues
 *
 * @param con
 * @param data
 * @param dbObjectType
 *            as defined in RDBMS
 * @return object that can be assigned to a struct parameter
 * @throws SQLException
 */
public static Struct createStruct(Connection con, Object[] data,
		String dbObjectType) throws SQLException {
	if (dbVendor == DbVendor.ORACLE) {
		OracleConnection ocon = toOracleConnection(con);
		StructDescriptor sd = StructDescriptor
				.createDescriptor(dbObjectType, ocon);
		return new STRUCT(sd, ocon, data);
	}
	return con.createStruct(dbObjectType, data);
}
 

import oracle.jdbc.driver.OracleConnection; //导入依赖的package包/类
/**
 * Create a struct array that can be assigned to procedure parameter. This
 * is delegated to DBDriver because of issues with Oracle driver
 *
 * @param con
 * @param structs
 * @param dbArrayType
 *            as defined in the rdbms
 * @return object that is suitable to be assigned to stored procedure
 *         parameter
 * @throws SQLException
 */
public static Array createStructArray(Connection con, Struct[] structs,
		String dbArrayType) throws SQLException {
	if (dbVendor == DbVendor.ORACLE) {
		OracleConnection ocon = toOracleConnection(con);
		ArrayDescriptor ad = ArrayDescriptor.createDescriptor(dbArrayType,
				ocon);
		return new ARRAY(ad, ocon, structs);
	}
	return con.createArrayOf(dbArrayType, structs);
}