Objectif
Copier le schéma Scott d’Oracle sur le Schéma d’un user nouvellement créé. En utilisant les outils de migration standard Oracle et un très simple programme de copie.
Introduction
Copier des bases de données entre différents supports, c’est possible à condition que les types de données soient compatibles.
Si l’on regarde et exécute les outils d’import export d’Oracle ( cf. http://oracle.developpez.com/guide/sauvegarde/generalites/ par exemple ) , nous pouvons faire très simple … ou très compliqué selon le cas.
Il faut prendre en compte pas mal de choses :
- Les séquences ( pour ceux qui ont envie de gérer les séquences ).
- concernant Access, il faut différencier les types Integer et Counter ( java.sql.Types.4 ), qui est un Integer auto-incrémenté
- les types non-compatibles ( voir les autres billets )
- Les objets non compatibles sql92 et sql99 comme les LOB et tous les autres non connus par Java.
- etc.
Pour rester simple, voici expliqué un bout de code simple et concis permettant de gérer la recopie du schéma Scott d’Oracle.
Procédure
- Exporter le schéma Scott
- Créer le schéma testJdbc
- Importer le schéma Scott dans testJdbc
- Vérifier la présence des tables
- Lancer la classe java de recopie
Exporter le schéma Scott
Taper en ligne de commande sous Windows :
exp scott/tiger file=/schemaScottVide.dmp rows=n
Créer l’utilisateur testJdbc
Taper en ligne de commande sous Windows :
sqlplus system/manager
drop user testjdbc cascade;
create user testjdbc identified by testjdbc;
grant connect , resource to testjdbc;
grant CREATE SESSION to testjdbc;
conn testjdbc/testjdbc
exit
Importer le schéma Scott dans testJdbc
Taper en ligne de commande sous Windows :
imp testjdbc/testjdbc file=/schemaScottVide.dmp commit=y fromuser=scott touser=testjdbc
Vérifier la présence des tables
Taper en ligne de commande sous Windows :
sqlplus testjdbc/testjdbc
select table_name from cat
Le code permettant la copie
package speh.jdbc.test.exec;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import speh.jdbc.core.DBUtilities;
import speh.jdbc.core.UneBdD;
public class CopySchemaScottPreparedStatement
{
// ===============================================================================
DBUtilities dbi = null ;
DBUtilities dbo = null ;
Vector types ;
ResultSet rsIn ;
PreparedStatement pstmt;
// ===============================================================================
public CopySchemaScottPreparedStatement( String _in , String _out , boolean _mustDeleteBefore , String _tables[] )
{
//
// connexions schemas in et out
//
System.out.println ( "connexion" ) ;
UneBdD bddIn = new UneBdD()._getByAlias( _in , true ) ;
dbi = new DBUtilities(bddIn) ;
UneBdD bddOut = new UneBdD()._getByAlias( _out , true ) ;
dbo = new DBUtilities(bddOut) ;
//
// vider contenus si demande
// remarque : vider sens inverse insert cause FK
//
if ( _mustDeleteBefore )
{
for ( int i = _tables.length -1 ; i > -1 ; i-- )
{
System.out.println( "delete " + _tables[i] + " ..." ) ;
boolean b = dbo._executerDeleteTable( _tables[i] , true ) ;
}
}
//
// recuperer structure types jdbc ( java.sql.Types.xxx )
// copier tuple par tuple, champ par champ
//
int nbTables = 0 ;
for ( int i = 0 ; i < _tables.length ; i++ )
{
System.out.println( "copy " + _tables[i] + " ..." ) ;
_structureTable( _tables[i] ) ;
if ( types.size() > 0 )
{
_copyTable( _tables[i] ) ;
nbTables++ ;
}
}
System.out.println( "tables traitées : " + nbTables ) ;
//
// deconnexions schemas in et out
//
dbi._deConnexion() ;
dbo._deConnexion() ;
System.out.println ( "déconnexion" ) ;
}
// ===============================================================================
void _structureTable( String _tableName )
{
//
// recuperer structure types jdbc ( java.sql.Types.xxx )
// teste pas catalogue et schema : peut y avoir ambiguite
//
types = new Vector() ;
try
{
DatabaseMetaData meta;
meta = dbi.conn.getMetaData();
ResultSet rs = meta.getColumns(null, null, _tableName, null);
while ( rs.next() )
{
int type = rs.getInt("DATA_TYPE") ;
types.add(type) ;
}
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
System.err.println( "STOP." ) ;
System.exit(0) ;
}
}
// ===============================================================================
void _copyTable( String _tableName )
{
//
// cree un PreparedStatement pour faciliter copy
// et eviter les transcodages de champs affiches
//
String sql1 = "INSERT INTO " ;
String sql2 = " VALUES( ? " ;
String sql3 = " )" ;
String sqlRepet = ", ? " ;
String sqlInsert = sql1 + _tableName + sql2 ;
for ( int i = 1 ; i < types.size() ; i++ )
{
sqlInsert = sqlInsert + sqlRepet ;
}
sqlInsert = sqlInsert + sql3 ;
//
// lecture toute la table en entree
//
String selectSql = "select * from " + _tableName ;
rsIn = dbi._executerSelect(selectSql) ;
try
{
pstmt = dbo.conn.prepareStatement(sqlInsert);
//
// recopier tuple par tuple, champ par champ
//
int nbTuples = 0 ;
while (rsIn.next())
{
for ( int ix = 1 ; ix < types.size() + 1 ; ix++ )
{
int type = Integer.parseInt(types.elementAt(ix-1).toString()) ;
switch (type)
{
case java.sql.Types.DECIMAL: // 3
pstmt.setBigDecimal( ix , rsIn.getBigDecimal(ix) );
break;
case java.sql.Types.VARCHAR: // 12
pstmt.setString( ix , rsIn.getString(ix) );
break;
case java.sql.Types.TIMESTAMP: // 93
pstmt.setTimestamp( ix , rsIn.getTimestamp(ix) );
break;
default: System.out.println("pas trouvé type # " + type); System.exit(0) ; break;
}
}
pstmt.executeUpdate();
nbTuples++ ;
}
System.out.println(" ... tuples traités : " + nbTuples ) ;
}
catch (SQLException e)
{
e.printStackTrace();
try
{
dbo.conn.rollback() ;
}
catch (SQLException e1)
{
e1.printStackTrace();
}
System.out.println("STOP.") ;
System.exit(0) ;
}
}
// ===============================================================================
public static void main( String args[] )
{
//
// alias des schemas in et out
// tableau des tables a traiter dans l'ordre des insert
// qui respecte les FK pre-existantes
//
String in = "jdbcOracleMabase" ;
String out = "jdbcOracleTestJdbc" ;
String tables[] = {"ACCOUNT","BONUS","RECEIPT","SALGRADE","DEPT","EMP" } ;
CopySchemaScottPreparedStatement appli = new CopySchemaScottPreparedStatement(in, out, true , tables) ;
appli.toString() ;
System.out.println ( "Traitement terminé." ) ;
}
// ===============================================================================
}
Quelques commentaires
- les classes DBUtilities et UneBdD ne sont là que pour gérer l’interface avec les bases de données déclarées dans un fichier properties
- l’utilisation d’un PreparedStatement est bien pratique parce qu’elle permet d’éviter d’écrire en toutes lettres les ordres INSERT dans les formats propriétaires – notamment aux dates –
- il faut rapprocher les types java/jdbc gérés des méta-données et les ordres resultSet.getXXX et setXXX correspondant
- les ordres resultSet.setNull n’ont pas à être gérés en valorisant directement avec pstmt.setString( ix , rsIn.getString(ix) );
- les tables à copier sont écrites dans l’ordres des contraintes de clés étrangères à vérifier, les plus contraintes à la fin.
Trace de la sortie
connexion
charge les bd recensées dans bin/speh/jdbc/sgbd.properties
+- charge jdbcMysqlScottTiger
+- charge jdbcOracleMabase
+- charge jdbcOracleTestJdbc
+- charge jdbcSqlServerAdventureWorks
+- charge odbcAccess2000
+- charge odbcAccess97
+- charge odbcAccessDeluxecd
+- charge odbcDbase
+- charge odbcDbaseSourcedbase
+- charge odbcMabase
+- charge odbcSqlServerScottTiger
delete EMP ...
rc = 14
delete DEPT ...
rc = 4
delete SALGRADE ...
rc = 5
delete RECEIPT ...
rc = 1
delete BONUS ...
rc = 0
delete ACCOUNT ...
rc = 5
copy ACCOUNT ...
... tuples traités : 5
copy BONUS ...
... tuples traités : 0
copy RECEIPT ...
... tuples traités : 1
copy SALGRADE ...
... tuples traités : 5
copy DEPT ...
... tuples traités : 4
copy EMP ...
... tuples traités : 14
tables traitées : 6
déconnexion
Traitement terminé.
Pour approfondir les sujets
Méta-données de base de données : http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html
Méta-données de ResultSet : http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html
Types de données : http://www.javelinsoft.com/jgenerator/guide/types.html
Description du schéma Scott d’Oracle : http://www.oracle.com/technology/sample_code/tech/sql_plus/htdocs/demobld.html
Schéma simplifié du schéma Scott : http://oracle.phpmagazine.net/upload/2008/12/drawing_er_diagrams_for_oracle/schema2.png
