This topic contains four Java code samples that use the solidDB® JDBC driver.
Java Code Example 1: sample1.java
/** * sample1 JDBC sample application * * * This simple JDBC application does the following using * Solid JDBC driver. * * 1. Registers the driver using JDBC driver manager services * 2. Prompts the user for a valid JDBC connect string * 3. Connects to Solid using the driver * 4. Creates a statement for one query, * ’SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROM TABLES’ * for reading data from one of the Solid system tables. * 5. Executes the query * 6. Fetches and dumps all the rows of a result set. * 7. Closes connection * To build and run the application * * 1. Make sure you have a working Java Development environment * 2. Install and start Solid to connect. Ensure that the * server is up and running. * 3. Append SolidDriver2.0.jar into the CLASSPATH definition used * by your development/running environment. * 4. Create a java project based on the file sample1.java. * 5. Build and run the application. * For more information read the readme.txt file contained in the * solidDB® package. * */
import java.io.*;
public class sample1 {
public static void main (String args[]) throws Exception { java.sql.Connection conn; java.sql.ResultSetMetaData meta; java.sql.Statement stmt; java.sql.ResultSet result; int i;
System.out.println("JDBC sample application starts..."); System.out.println("Application tries to register the driver.");
// this is the recommended way for registering Drivers java.sql.Driver d = (java.sql.Driver)Class.forName("solid.jdbc.SolidDriver"). newInstance();
System.out.println("Driver succesfully registered."); // the user is asked for a connect string System.out.println( "Now sample application needs a connectstring in format:\n" ); System.out.println( "jdbc:solid://<host>:<port>/<user name>/<password>\n" ); System.out.print("\nEnter the connect string >"); BufferedReader reader = new BufferedReader(new InputStreamReader(System.in)); String sCon = reader.readLine(); // next, the connection is attempted System.out.println("Attempting to connect :" + sCon); conn = java.sql.DriverManager.getConnection(sCon);
System.out.println("SolidDriver succesfully connected."); String sQuery = "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROM TABLES";
stmt= conn.createStatement();
result = stmt.executeQuery(sQuery);
System.out.println("Query executed and result set obtained.");
// we get a metadataobject containing information about the // obtained result set System.out.println("Obtaining metadata information."); meta = result.getMetaData(); int cols = meta.getColumnCount();
System.out.println("Metadata information for columns is as follows:"); // we dump the column information about the result set for (i=1; i <= cols; i++) { System.out.println("Column i:"+i+" "+meta.getColumnName(i)+ "," + meta.getColumnType(i) + "," + meta.getColumnTypeName(i)); }
// and finally, we dump the result set System.out.println("Starting to dump result set."); int cnt = 1; while(result.next()) { System.out.print("\nRow "+cnt+" : "); for (i=1; i <= cols; i++) { System.out.print(result.getString(i)+"\t"); } cnt++; } stmt.close(); conn.close();
// and not it is all over System.out.println("\nResult set dumped. Sample application finishes.");
} }
Java code example 1 Output
Solid\DatabaseEngine4.1\jdbc\samples>java sample1.java JDBC sample application starts... Application tries to register the driver. Driver succesfully registered. Now sample application needs a connectstring in format:
jdbc:solid://<host>:<port>/<user name>/<password>
Enter the connect string >jdbc:solid://localhost:1313/dba/dba Attempting to connect :jdbc:solid://localhost:1313/dba/dba SolidDriver succesfully connected. Query executed and result set obtained. Obtaining metadata information. Metadata information for columns is as follows: Column i:1 TABLE_SCHEMA,12,VARCHAR Column i:2 TABLE_NAME,12,VARCHAR Column i:3 TABLE_TYPE,12,VARCHAR Starting to dump result set.
Row 1 : _SYSTEM SYS_TABLES BASE TABLE Row 2 : _SYSTEM SYS_COLUMNS BASE TABLE Row 3 : _SYSTEM SYS_USERS BASE TABLE Row 4 : _SYSTEM SYS_UROLE BASE TABLE Row 5 : _SYSTEM SYS_RELAUTH BASE TABLE Row 6 : _SYSTEM SYS_ATTAUTH BASE TABLE Row 7 : _SYSTEM SYS_VIEWS BASE TABLE Row 8 : _SYSTEM SYS_KEYPARTS BASE TABLE Row 9 : _SYSTEM SYS_KEYS BASE TABLE Row 10 : _SYSTEM SYS_CARDINAL BASE TABLE Row 11 : _SYSTEM SYS_INFO BASE TABLE Row 12 : _SYSTEM SYS_SYNONYM BASE TABLE Row 13 : _SYSTEM TABLES VIEW Row 14 : _SYSTEM COLUMNS VIEW Row 15 : _SYSTEM SQL_LANGUAGES BASE TABLE Row 16 : _SYSTEM SERVER_INFO VIEW Row 17 : _SYSTEM SYS_TYPES BASE TABLE Row 18 : _SYSTEM SYS_FORKEYS BASE TABLE Row 19 : _SYSTEM SYS_FORKEYPARTS BASE TABLE Row 20 : _SYSTEM SYS_PROCEDURES BASE TABLE Row 21 : _SYSTEM SYS_TABLEMODES BASE TABLE Row 22 : _SYSTEM SYS_EVENTS BASE TABLE Row 23 : _SYSTEM SYS_SEQUENCES BASE TABLE Row 24 : _SYSTEM SYS_TMP_HOTSTANDBY BASE TABLE Result set dumped. Sample application finishes.
Java code example 2: sample2.java
/** * sample2 JDBC sample applet * * * This simple JDBC applet does the following using * Solid native JDBC driver. * * 1. Registers the driver using JDBC driver manager services * 2. Connects to Solid using the driver. * Used url is read from sample2.html * 3. Executes given SQL statements * To build and run the application * 1. Make sure you have a working Java Development environment * 2. Install and start Solid to connect. Ensure that * the server is up and running. * 3. Append SolidDriver2.0.jar into the CLASSPATH definition used * by your development/running environment. * 4. Create a java project based on the file sample2.java. * 5. Build and run the application. Check that sample2.html * defines valid url to your environment. * For more information read the readme.txt file contained in the * solidDB® Development Kit package. **/
try { // Load the Solid JDBC Driver Driver d = (Driver)Class.forName ("solid.jdbc.SolidDriver"). newInstance();
// Attempt to connect to a driver. con = DriverManager.getConnection (url);
// If we were unable to connect, an exception // would have been thrown. So, if we get here, // we are successfully connected to the url // Check for, and display and warnings generated // by the connect.
checkForWarning (con.getWarnings ());
// Get the DatabaseMetaData object and display // some information about the connection DatabaseMetaData dma = con.getMetaData ();
// If a SQLWarning object was given, display the // warning messages. Note that there could be // multiple warnings chained together if (warn != null) { textArea.appendText("\n*** Warning ***\n"); rc = true; while (warn != null) { textArea.appendText("SQLState: " + warn.getSQLState () + "\n"); textArea.appendText("Message: " + warn.getMessage () + "\n"); textArea.appendText("Vendor: " + warn.getErrorCode () + "\n"); textArea.appendText("\n"); warn = warn.getNextWarning (); } } return rc; }
// // dispResultSet // Displays all columns and rows in the given result set // private static void dispResultSet (Statement sta, ResultSet rs) throws SQLException { int i; // Get the ResultSetMetaData. This will be used for // the column headings ResultSetMetaData rsmd = rs.getMetaData (); // Get the number of columns in the result set int numCols = rsmd.getColumnCount (); if (numCols == 0) { textArea.appendText("Updatecount is "+sta.getUpdateCount()); return; }
// Display column headings for (i=1; i<=numCols; i++) { if (i > 1) { textArea.appendText("\t"); } try { textArea.appendText(rsmd.getColumnLabel(i)); } catch(NullPointerException ex) { textArea.appendText("null"); } } textArea.appendText("\n");
// Display data, fetching until end of the result set boolean more = rs.next (); while (more) { // Loop through each column, get the // column data and display it for (i=1; i<=numCols; i++) { if (i > 1) { textArea.appendText("\t"); } try { textArea.appendText(rs.getString(i)); } catch(NullPointerException ex) { textArea.appendText("null"); } } textArea.appendText("\n");
// Fetch the next result set row more = rs.next (); } }
private static void printSQLException(SQLException ex) { // A SQLException was generated. Catch it and // display the error information. Note that there // could be multiple error objects chained // together textArea.appendText("\n*** SQLException caught ***\n"); } }
while (ex != null) { textArea.appendText("SQLState: " ex.getSQLState () + "\n"); textArea.appendText("Message: " ex.getMessage () + "\n"); textArea.appendText("Vendor: " ex.getErrorCode () + "\n"); textArea.appendText("\n"); ex = ex.getNextException (); }
Java code example 3: sample3.java
/** * sample3 JDBC sample application * This simple JDBC application does the following using * Solid JDBC driver. * * * 1. Registers the driver using JDBC driver manager services * 2. Prompts the user for a valid JDBC connect string * 3. Connects to Solid using the driver * 4. Drops and creates a procedure sample3. If the procedure * does not exist dumps the related exception. * 5. Calls that procedure using java.sql.Statement * 6. Fetches and dumps all the rows of a result set. * 7. Closes connection * To build and run the application * 1. Make sure you have a working Java Development environment * 2. Install and start Solid to connect. Ensure that the * server is up and running. * 3. Append SolidDriver2.0.jar into the CLASSPATH definition used * by your development/running environment. * 4. Create a java project based on the file sample3.java. * 5. Build and run the application. * For more information read the readme.txt * file contained in the solidDB® Development Kit package. */
import java.io.*; import java.sql.*;
public class sample3 { static Connection conn; public static void main (String args[]) throws Exception { System.out.println("JDBC sample application starts..."); System.out.println("Application tries to register the driver."); // this is the recommended way for registering Drivers Driver d = (Driver)Class.forName("solid.jdbc.SolidDriver"). newInstance(); System.out.println("Driver succesfully registered."); // the user is asked for a connect string System.out.println( "Now sample application needs a connectstring in format:\n"); System.out.println( "jdbc:solid://<host>:<port>/<user name>/<password>\n"); System.out.print("\nEnter the connect string >"); BufferedReader reader = new BufferedReader(new InputStreamReader(System.in)); String sCon = reader.readLine();
// next, the connection is attempted System.out.println("Attempting to connect :" + sCon); conn = DriverManager.getConnection(sCon);
public static void printexp(SQLException ex) { System.out.println("\n*** SQLException caught ***"); while (ex != null) { System.out.println("SQLState: " + ex.getSQLState()); System.out.println("Message: " + ex.getMessage()); System.out.println("Vendor: " + ex.getErrorCode()); ex = ex.getNextException (); } } }
Java code example 4: sample4.java
/** * sample4 JDBC sample application * This simple JDBC application does the following using * Solid JDBC driver. * * 1. Registers the driver using JDBC driver manager services * 2. Prompts the user for a valid JDBC connect string * 3. Connects to Solid using the driver * 4. Drops and creates a table sample4. If the table * does not exist dumps the related exception. * 5. Inserts file given as an argument to database (method Store) * 6. Reads this ’blob’ back to file out.tmp (method Restore) * 7. Closes connection * To build and run the application * 1. Make sure you have a working Java Development environment * 2. Install and start Solid to connect. Ensure that * the server is up and running. * 3. Append SolidDriver2.0.jar into the CLASSPATH definition used * by your development/running environment. * 4. Create a java project based on the file sample4.java. * 5. Build and run the application. * For more information read the readme.txt file * contained in the solidDB® Development Kit package. * */
import java.io.*; import java.sql.*;
public class sample4 { static Connection conn; public static void main (String args[]) throws Exception { String filename = null; String tmpfilename = null;
if (args.length < 1) { System.out.println("usage: java sample4 <infile>"); System.exit(0); } filename = args[0]; tmpfilename = "out.tmp"; System.out.println("JDBC sample application starts..."); System.out.println("Application tries to register the driver."); // this is the recommended way for registering Drivers Driver d = (Driver)Class.forName("solid.jdbc.SolidDriver"). newInstance(); System.out.println("Driver succesfully registered."); // the user is asked for a connect string System.out.println("Now sample application needs a connectstring in format:\n"); System.out.println( "jdbc:solid://<host>:<port>/<user name>/<password>\n"); System.out.print("\nEnter the connect string >"); BufferedReader reader = new BufferedReader(new InputStreamReader(System.in)); String sCon = reader.readLine();
// next, the connection is attempted System.out.println("Attempting to connect :" + sCon); conn = DriverManager.getConnection(sCon); System.out.println("SolidDriver succesfully connected.");
// drop and create table sample4 createsample4();
// insert data into it Store(filename);
// and restore it Restore(tmpfilename);
conn.close(); // and it is all over System.out.println("\nSample application finishes."); } static void Store(String filename) { String sql = "insert into sample4 values(?,?)"; FileInputStream inFileStream ; try { File f1 = new File(filename); int blobsize = (int)f1.length(); System.out.println("Inputfile size is "+blobsize); inFileStream = new FileInputStream(f1); PreparedStatement stmt = conn.prepareStatement(sql); stmt.setLong(1, System.currentTimeMillis()); stmt.setBinaryStream(2, inFileStream, blobsize); int rows = stmt.executeUpdate(); stmt.close(); System.out.println(""+rows+" inserted."); conn.commit(); }