Java Example
From Ingres Community Wiki
Contents |
Example Simple Java Application
This simple example shows how to connect to Ingres using the Ingres JDBC Driver and the Ingres demodb.
Introduction
In this example a single class "CountryList" connects to a database "demodb" on the local machine ("localhost") connecting with the user "ingres" using the method createConnection(). It then performs some SQL operations and quits.
JDBC URL
The parameters for the connection URL are:
- jdbc:ingres://
- Connect using the Ingres JDBC driver
- hostname
- localhost (the name of the computer you are connecting to)
- port
- the port to connect to (3 chars: the two char Ingres Instance ID and the digit 7, e.g., II7)
- database
- the name of the database you wish to access
- user
- the user name to connect with
- password
- the password
For more details please refer to DriverManager.getConnection
Database Operations Performed
The demodb is delivered with Ingres 2006 and contains a table "country" with a list of countries. If this is not available to you you can use the Country Script with the Ingres Terminal Monitor to create the country table.
Once the connection has been established the country "Switzerland" is inserted into the country table with the method insertCountry().
Next all the countries are retrieved (and printed) using the method retrieveCountries().
Finally the country "Switzerland" is removed from the database with the method removeCountry(). This is so that you can run the program more than once without causing a database error: the database is set up do prevent duplicate records being inserted into the country table.
This example was created as part of the Java Ingres Frequent Flyer demonstration program which was written with the Ingres plug-in for Eclipse DTP.
The Example Java Source Code
You can cut and paste this into your favourite Java editor (this was created with Eclipse) and test it with your Ingres installation. Remember to adjust the URL components to suite your local conditions!
package com.ingres.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class CountryList {
// URL Components
private static String demoHost = "localhost";
private static String demoPort = "II7";
private static String demoDb = "demodb";
private static String demoUser = "user=ingres";
private static String demoPassword = "password=*******";
// JDBC Connection variables
private static String demoDbUrl = "jdbc:ingres://" +
demoHost +
":" + demoPort +
"/" + demoDb /*+
";" + demoUser +
";" + demoPassword*/;
private static Connection conn = null;
private static Statement stmt = null;
private static String tableName = "country";
/**
* @param args
*/
public static void main(String[] args) {
System.out.println("Create connection to jdbc:ingres://" +
demoHost +
":" + demoPort +
"/" + demoDb +
" ...");
createConnection();
System.out.println("Insert Switzerland (CH)...");
insertCountry("CH", "Switzerland");
System.out.println("Retrieve and print all countries found:");
retrieveCountries();
// Clean up
System.out.println("Remove CH (Switzerland) from the database...");
removeCountry("CH");
}
/**
* Create a connection to Ingres using the Ingres JDBC driver
* and the private variable demoDbUrl
*/
private static void createConnection()
{
try
{
Class.forName("com.ingres.jdbc.IngresDriver").newInstance();
//Get a connection
conn = DriverManager.getConnection(demoDbUrl);
}
catch (Exception except)
{
except.printStackTrace();
}
}
/**
* Insert a country name uniquely identified by its country code:
* @param countryCode
* @param countryName
*/
private static void insertCountry(String countryCode, String countryName)
{
try
{
stmt = conn.createStatement();
stmt.execute("insert into " + tableName +
"( ct_id, ct_code, ct_name )" +
" values (" +
"next value for ct_id, '" +
countryCode + "','" +
countryName +"')");
stmt.close();
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
/**
* Retrieve and print all countries in the database.
*/
private static void retrieveCountries()
{
try
{
stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("SELECT ct_name, ct_code " +
"FROM " + tableName);
ResultSetMetaData rsmd = results.getMetaData();
int numberCols = rsmd.getColumnCount();
for (int i=1; i<=numberCols; i++)
{
//print Column Names
System.out.print(rsmd.getColumnLabel(i)+"\t\t");
}
System.out.println("\n====");
while(results.next())
{
String countryName = results.getString(1);
String countryCode = results.getString(2);
System.out.println(countryCode + "\t\t" + countryName);
}
results.close();
stmt.close();
System.out.println("====\n");
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
/**
* Remove a country uniquely identified by its countryCode
* @param countryCode
*/
private static void removeCountry(String countryCode) {
try
{
stmt = conn.createStatement();
stmt.execute("delete from " + tableName +
" where ct_code = '" +
countryCode +
"'");
stmt.close();
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
}
JDBC Driver Version
The driver version used creating this example was 3.2 (MD5: 8FF13A38606A5FE3BA3F5D5BD4D28A82 JavaExampleDriver.zip ).
Categories: DBMSDrivers | Examples | Articles | Java
