java05_main.java

Many-to-many. Three tables, two foreign key references constructing a many-to-many relationship. Populate with a few rows. Select and print from one side, then from the other.

import java.sql.*;
public class java05_main {
/*
* EXAMPLE - java05 for JDBC
* When you run this example, a new database is created with a two tables
* that need to be related via a many-to-many relationship, so an
* 'intersect' table is also created to keep track of that information.
* Three rows are inserted into each of the main tables and for each
* relationship a row is inserted into the intersect table.
* Finally all the data is read starting from one main table and printed
* and then the data is reread and printed starting from the other main
* table.
*
* Full documentation for RDM is available at http://docs.raima.com/rdm/
*
* For simplicity this example utilizes basic exception handling
* but good programming practices would dictate that this occur in
* a full application.
*/
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
/* Create the connection with a simple connection string */
conn = DriverManager.getConnection("jdbc:raima:rdm://local");
conn.setAutoCommit(true);
CreateDatabase(conn);
/* Open the connection to the database */
stmt = conn.createStatement();
/* Insert three rows into the first main table */
for (int ii = 1; ii < 4; ii++)
{
stmt.executeUpdate("INSERT INTO MainTab1 VALUES " + ii + ", 'main1 " + ii + "'");
}
/* Insert three rows into the second main table */
for (int ii = 1; ii < 4; ii++)
{
stmt.executeUpdate("INSERT INTO MainTab2 VALUES " + ii + ", 'main2 " + ii + "'");
}
/* Insert a row into the intersect table for each combination of
* rows in the main tables */
for (int ii = 1; ii < 4; ii++)
{
for (int jj = 1; jj < 4; jj++)
{
stmt.executeUpdate("INSERT INTO IntTab VALUES " + ii + ", " + jj);
}
}
/* Scan through the relationships from the first main table to the second */
System.out.println("From MainTab1 to MainTab2");
rs = stmt.executeQuery("SELECT m1.str, m2.str FROM MainTab1 m1, MainTab2 m2, IntTab WHERE m1.id = IntTab.id1 AND m2.id = IntTab.id2");
while (rs.next() != false)
{
System.out.println(rs.getString(1) + " -- " + rs.getString(2));
}
}
catch (SQLException sqle)
{
sqle.printStackTrace();
}
finally
{
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
}
}
private static void CreateDatabase(Connection conn)
{
Statement stmt = null;
try
{
/* Create the example database */
stmt = conn.createStatement();
/* Since the database is created here, it cannot be created twice */
try {
stmt.executeUpdate("DROP DATABASE java05");
} catch (SQLException sqle) {}
stmt.execute("CREATE DATABASE java05");
stmt.execute("CREATE TABLE MainTab1 (id INTEGER PRIMARY KEY, str CHAR(30))");
stmt.execute("CREATE TABLE MainTab2 (id INTEGER PRIMARY KEY, str CHAR(30))");
stmt.execute("CREATE TABLE IntTab (id1 INTEGER REFERENCES MainTab1(id), id2 INTEGER REFERENCES MainTab2(id))");
conn.commit();
stmt.close();
stmt = null;
}
catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
}