JDBC connection to Virtuoso under LOG4JDBC

Submitted by admin on Wed, 02/24/2016 - 12:09
Attachment Size
log4jdbc4-1.2.jar 63.4 KB
virtjdbc4_1.jar 299.42 KB

In the following I describe two ways to connect. You must have already checked this page for more information.

http://docs.openlinksw.com/virtuoso/VirtuosoDriverJDBC.html

The JDBC driver is available under the folder

/usr/local/src/virtuoso-opensource/libsrc/JDBCDriverType4

assuming you have already cloned the git repository from here

https://github.com/openlink/virtuoso-opensource

inside a path like /usr/local/src/

Next, you have to register the library in your CLASSPATH, see this <a href="http://cambio.name/personal/sites/default/files/Screen%20Shot%202016-02…;

a) The simple way

<code>

public class Database {

static String username = "dba";
static String password = "dba";

public static Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1111/", username, password);
return conn;
}

}

</code>

Next, you can call connect to Virtuoso thus, for example:

<code>
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
public static void main(String[] args) throws SQLException {
Connection conn = Database.getConnection();
String sql = "SELECT S FROM DB.DBA.RDF_QUAD";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}

</code>

b) The way using LOG4JDBC to log SQL queries

Download the library log4jdbc4-1.2.jar from

https://code.google.com/archive/p/log4jdbc/downloads

to a path like

/Users/myuser/Documents/workspace/Evolvement/lib

Next, you have to register the library in your CLASSPATH, see attachment. "workspace" is the folder where Eclipse saves my Java projects.

<code>

public class Database {

static String username = "dba";
static String password = "dba";

public static ConnectionSpy getConnectionSpy() throws SQLException {
DriverSpy driverSpy = new DriverSpy();
Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
ConnectionSpy conn = (ConnectionSpy) driverSpy.connect("jdbc:log4jdbc:virtuoso://localhost:1112/", props);
return conn;
}

}
</code>

<code>
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
public static void main(String[] args) throws SQLException {
Connection conn = Database.getConnectionSpy();
String sql = "SELECT S FROM DB.DBA.RDF_QUAD";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}

</code>

Here, this is my pom.xml just in case

<code>
<project xmlns="http://maven.apache.org/POM/4.0.0&quot; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;
<modelVersion>4.0.0</modelVersion>
<groupId>virtuoso</groupId>
<artifactId>virtuoso</artifactId>
<version>0.0.1-SNAPSHOT</version>
<build>
<sourceDirectory>src</sourceDirectory>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source/>
<target/>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>org.apache.jena</groupId>
<artifactId>apache-jena-libs</artifactId>
<type>pom</type>
<version>2.13.0</version>
<exclusions>
<exclusion>
<artifactId>slf4j-log4j12</artifactId>
<groupId>org.slf4j</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-math3</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.1</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.16</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.6</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>

</dependencies>
</project>
</code>

and also my log4j.properties that must be located inside a folder called "resources" like this

/Users/myuser/Documents/workspace/Evolvement/resources

<code>
# Root logger option

log4j.rootLogger=INFO, stdout

# Direct log messages to stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

# log4j.logger.jdbc.sqlonly=INFO
# log4j.logger.jdbc.sqltiming=INFO
log4j.logger.jdbc.sqlonly=OFF
log4j.logger.jdbc.sqltiming=OFF
log4j.logger.jdbc.audit=OFF
log4j.logger.jdbc.resultset=ERROR
log4j.logger.jdbc.connection=ERROR
log4j.logger.jdbc.resultsettable=ON

</code>

References

http://www.mkyong.com/java/java-properties-file-examples/

Imagen