/** You may copy this example and use it for any purpose, commercial or otherwise. */

import com.ricebridge.xmlman.XmlManager;
import com.ricebridge.xmlman.RecordSpec;

import java.sql.*;
import java.util.*;
import java.io.*;


public class Database {
  public static void main( String[] args ) throws Exception {

    // Create XmlManager and define our record XPaths
    XmlManager xmlman = new XmlManager();
    RecordSpec rs = new RecordSpec("/database/product", 
                                   new String[]{"@id","code","name","price"});

    // Edit this for your local database. 
    // The table definition is in product.sql.
    String dburl 
      = "jdbc:mysql://localhost/mydatabase?"
      + "user=username&password=password";
    Driver d = (Driver) Class.forName( "com.mysql.jdbc.Driver" ).newInstance(); 
    DriverManager.registerDriver( d );
    Connection con = DriverManager.getConnection(dburl);

    // Import data into database.
    PreparedStatement ps = con.prepareStatement( "INSERT INTO Product VALUES (?,?,?,?)" ); 
    List data = xmlman.load( "products.xml", rs );
    for( int line = 0; line < data.size(); line++ ) {
      String[] fields = (String[]) data.get(line);
      ps.setInt(    1, Integer.parseInt( fields[0] ) );
      ps.setString( 2, fields[1] );
      ps.setString( 3, fields[2] );
      ps.setInt(    4, Integer.parseInt( fields[3] ) );
      ps.executeUpdate();
    }

    // Export data from database.
    ps = con.prepareStatement( "SELECT * FROM Product" ); 
    ResultSet resultset = ps.executeQuery();
    xmlman.saveResultSet( "products-export.xml", rs, resultset );

    con.close();
  }
}