11 Replies Latest reply on Sep 26, 2012 9:53 AM by Alex Popa-Tesileanu

    Hibernate Tools question

    Alex Popa-Tesileanu Newbie

      Hi,

      I tried to use reverse engineering to generate domain objects from database schema.

      Everything appear to be ok.

      When I tried to insert a record in the table I find it is a problem. Because all fields in the table are defined not null and have default values. The problem is hibernate try to insert all fields whether I completed only a few. Because not all fields are initialised with default values hibernate put null in the fields not initialised. This result in error on insert operation. I resolved this issue by setting values to properties in generated POJO's.

      The idea is I have hundreds of tables and I don't want to manually set default values for properties.

       

      I have a question:

      It is possible to set somehow reverse engineering to generate properties with default values taken from table definition?

      Ex.

      public class AaaProduse implements java.io.Serializable {

       

       

        private String codProd;

        private String denProd="";

        private String sirGrupare="";

        private String sirCautare="";

        private BigDecimal stoc=0;

      ..............

      instead of:

       

      public class AaaProduse implements java.io.Serializable {

       

       

        private String codProd;

        private String denProd;

        private String sirGrupare;

        private String sirCautare;

        private BigDecimal stoc;

       

      Thank you very much,

      Alex

        • 1. Re: Hibernate Tools question
          Max Rydahl Andersen Master

          its not possible to read it from the table definition since is not available in JDBC metadata afaik (it is for certain databases in db specific fields though).

           

          There are code to define what the default init will contain, but that currently just covers collections.

          • 2. Re: Hibernate Tools question
            Alex Popa-Tesileanu Newbie

            Thank you for response.

            I found a solution using dynamic_update and dynamic_insert but in some conditions this can decrease performance.

            Also still search for a solution to generate properties not with default values from table columns but with empty value for property data type.

            Ex.: for string ="", for numbers=0, etc.

            Any idea will be welcome.

             

            Alex

            • 3. Re: Hibernate Tools question
              Max Rydahl Andersen Master

              Any particular reason your columns doesn't have not-null set ?

               

              in hbm.xml you can add a "default-value" meta-attribute.

               

              <property name="content" type="string" length="10000">  
                <meta attribute="default-value">""</meta>
               </property>
              

               

              that will make it generate:

               

              content = ""
              

               

              For reverse engineering this is trickier, but there are two options.

               

              Create a reveng.xml file that sets <meta> for the columns you need it for.

              Example of that can be found at https://github.com/hibernate/hibernate-tools/blob/master/src/test/org/hibernate/tool/test/jdbc2cfg/overridetest.reveng.xml#L58

               

              Mind you that reveng.xml is "additative" meaning you can just specify the column name, everything else will come from jdbc.

               

              If that is not enough you would need to implement a ReverseEngineeringStrategy that has a method similar to:

               

              public Map columnToMetaAttributes(TableIdentifier identifier, String column) {
                                  Map result = new HashMap<String, MetaAttribute>();
                
                                  MetaAttribute ma = new MetaAttribute("default-value");
                
                                  String defaultValue = logicToFigureOutValueBasedonTableColumn(identifier, column);
                
                                  ma.addValue(defaultValue);
                                  result.put("default-value", ma);
                                  return result;
               }
              
              • 4. Re: Hibernate Tools question
                Alex Popa-Tesileanu Newbie

                Hi,

                The columns have "not null" set because of programming language I used witch don't deal well with nulls. Also all columns have default value set.

                Even I will migrate to java will be a period in witch the old and the new  client will access the database in same time.

                The idea is I have to deal with 255 tables witch have 4552 columns. And growing. This is why the first option is not the best solution.

                What I want is, when I add a table, a column or I change it, to easily regenerate domain all domain objects.

                The second option I think is the best.

                I thought previously to use ReverseEngineeringStrategy to remove table prefix. But I did not know about columnToMetaAttributes. This is very helpful. I tried to put a dummy hardcoded value for each field and it work. What I have to do is to extract the default value from the database.

                I know the table, I know the field I will connect to database and extract the default value.

                The question is: it is somehow possible to get the database connection from "hibernate console configuration" instead of create a new one ?  I think this will speed-up the reveng process.

                 

                Your response was very helpful.

                Thank You very much,

                Alex

                • 5. Re: Hibernate Tools question
                  Max Rydahl Andersen Master

                  simply access getRuntimeInfo().getConnectionProvider().getConnection()

                   

                  (don't forget to call connectionProvider.closeConnection()!)

                   

                   

                  Note, runtimeinfo is not avaiable before the configure method have been called. Just something to be aware of.

                  • 6. Re: Hibernate Tools question
                    Alex Popa-Tesileanu Newbie

                    Thank You

                    I will try a.s.a.p.

                    Alex

                    • 7. Re: Hibernate Tools question
                      Alex Popa-Tesileanu Newbie

                      Hi,

                      I tried to get the connection from RuntimeInfo without success.

                      I created two functions: initConn and initConn1.

                      In first one I get the connection from RuntimeInfo. The result is:

                          

                      java.lang.NullPointerException

                                at ro.alex.CustReveng.initConn(CustReveng.java:49)

                                at ro.alex.CustReveng.<init>(CustReveng.java:30)

                                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                                at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

                                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

                                at java.lang.reflect.Constructor.newInstance(Unknown Source)

                                at org.jboss.tools.hibernate4_0.console.ConsoleExtension4_0.loadreverseEngineeringStrategy(ConsoleExtension4_0.java:297)

                                at org.jboss.tools.hibernate4_0.console.ConsoleExtension4_0.access$1(ConsoleExtension4_0.java:293)

                                at org.jboss.tools.hibernate4_0.console.ConsoleExtension4_0$3.execute(ConsoleExtension4_0.java:256)

                                at org.hibernate.console.execution.DefaultExecutionContext.execute(DefaultExecutionContext.java:63)

                                at org.jboss.tools.hibernate4_0.HibernateExtension4_0.execute(HibernateExtension4_0.java:211)

                                at org.jboss.tools.hibernate4_0.console.ConsoleExtension4_0.buildConfiguration(ConsoleExtension4_0.java:237)

                                at org.jboss.tools.hibernate4_0.console.ConsoleExtension4_0.runExporters(ConsoleExtension4_0.java:167)

                                at org.jboss.tools.hibernate4_0.console.ConsoleExtension4_0.launchExporters(ConsoleExtension4_0.java:126)

                                at org.hibernate.eclipse.launch.CodeGenerationLaunchDelegate.launch(CodeGenerationLaunchDelegate.java:270)

                                at org.eclipse.debug.internal.core.LaunchConfiguration.launch(LaunchConfiguration.java:855)

                                at org.eclipse.debug.internal.core.LaunchConfiguration.launch(LaunchConfiguration.java:704)

                                at org.eclipse.debug.internal.ui.DebugUIPlugin.buildAndLaunch(DebugUIPlugin.java:1047)

                                at org.eclipse.debug.internal.ui.DebugUIPlugin$8.run(DebugUIPlugin.java:1251)

                                at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

                       

                       

                      In the initConn1 I created a new con nection and it work.

                      Bellow is the code:

                          

                      package ro.alex;

                       

                       

                      import java.sql.Connection;

                      import java.sql.DriverManager;

                      import java.sql.ResultSet;

                      import java.sql.SQLException;

                      import java.sql.Statement;

                      import java.util.Date;

                      import java.util.HashMap;

                      import java.util.Map;

                       

                       

                      import org.hibernate.cfg.reveng.DelegatingReverseEngineeringStrategy;

                      import org.hibernate.cfg.reveng.ReverseEngineeringRuntimeInfo;

                      import org.hibernate.cfg.reveng.ReverseEngineeringStrategy;

                      import org.hibernate.cfg.reveng.TableIdentifier;

                      import org.hibernate.mapping.MetaAttribute;

                      import org.apache.log4j.Logger;

                      import org.apache.log4j.BasicConfigurator;

                       

                       

                       

                       

                      public class CustReveng extends DelegatingReverseEngineeringStrategy {

                      private ReverseEngineeringRuntimeInfo RuntimeInfo;

                      private Connection conn;

                      static Logger logger = Logger.getLogger(CustReveng.class);

                       

                       

                      public CustReveng(ReverseEngineeringStrategy strategy) {    

                          super (strategy);    

                          BasicConfigurator.configure();

                          configure(getRuntimeInfo());

                          initConn();

                          //initConn1();

                      }

                       

                       

                      public Connection getConn() {

                                return conn;

                      }

                      public void setConn(Connection conn) {

                                this.conn = conn;

                      }

                      public void setRuntimeInfo(ReverseEngineeringRuntimeInfo runtimeInfo) {

                                RuntimeInfo = runtimeInfo;

                      }

                      public ReverseEngineeringRuntimeInfo getRuntimeInfo() {

                                return RuntimeInfo;

                      }

                       

                       

                      public void initConn(){

                                try {

                                          setConn(getRuntimeInfo().getConnectionProvider().getConnection());

                                } catch (SQLException e) {

                                          // TODO Auto-generated catch block

                                          e.printStackTrace();

                                }

                      }

                       

                       

                      public void initConn1(){

                                //Create a new database connection

                                 String dbUrl = "jdbc:mysql://localhost:3307/conta";

                                 try {

                                           setConn(DriverManager.getConnection (dbUrl,"user","password"));

                                 }

                                 catch(SQLException e) {

                                           e.printStackTrace();

                                           logger.info("test"+e.getMessage());

                                 }

                      }

                       

                        public Map<String, MetaAttribute> columnToMetaAttributes(TableIdentifier identifier, String column) {

                            Map<String, MetaAttribute> result = new HashMap<String, MetaAttribute>();

                       

                            MetaAttribute ma = new MetaAttribute("default-value");

                            String table;

                            table=identifier.getName();

                            String defaultValue = getTableColumnDefault(table, column);

                            if(defaultValue!=null && !defaultValue.isEmpty()){

                                      ma.addValue(defaultValue);

                                      result.put("default-value", ma);

                            }

                            return result;

                      }

                       

                      public String getTableColumnDefault(String table, String column){

                                 String xdefault = "";

                                 String xType = "";

                                 String query = "SHOW COLUMNS FROM "+table+" LIKE '"+column+"'";

                                 try {

                                           Connection con = getConn();

                                           Statement stmt = con.createStatement();

                                           ResultSet rs = stmt.executeQuery(query);

                                           while (rs.next()) {

                                                     xdefault = rs.getString(5);

                                                     xType = rs.getString(2);

                                                     //logger.info("Default value for "+column+" "+xdefault);

                                           } //end while

                                 } //end try

                                 catch(SQLException e) {

                                 e.printStackTrace();

                                 logger.info("SQL Error: "+e.getMessage());

                                 }

                                 //Code to calculate default value

                                 //logger.info("Type value for "+column+" "+(xType.substring(0,Math.min(xType.length(), 7))));

                                 if(xType.substring(0,Math.min(xType.length(), 4)).equals("char")){

                                           if(xType.trim().equals("char(1)")){

                                                     xdefault="\'"+xdefault+"\'";

                                           }else{

                                                     xdefault="\""+xdefault+"\"";

                                           }

                                 }else if(xType.substring(0,Math.min(xType.length(), 7)).equals("varchar")){

                                           if(xType.trim().equals("varchar(1)")){

                                                     xdefault="\'"+xdefault+"\'";

                                           }else{

                                                     xdefault="\""+xdefault+"\"";

                                           }                    

                                 }else if(xType.trim()=="text"){

                                           xdefault="\""+xdefault+"\"";

                                 }else if(xdefault!=null && !xdefault.isEmpty() && (xType.trim().equals("date") || xType.trim().equals("datetime")) ){

                                           xdefault="new Date(0)";

                                 }else if(xdefault!=null && !xdefault.isEmpty() && (xType=="decimal" || xType.trim()=="int" || xType.trim()=="smallint" || xType.trim()=="tinyint" )){

                                           xdefault="0";

                                 }else{

                                           xdefault="\"ccc\"";

                                 }

                                

                                          

                      return xdefault;

                      }  

                       

                       

                      public void finalize(){

                                try {

                                          getConn().close();

                                } catch (SQLException e) {

                                          // TODO Auto-generated catch block

                                          e.printStackTrace();

                                }

                                try {

                                          super.finalize();

                                } catch (Throwable e) {

                                          // TODO Auto-generated catch block

                                          e.printStackTrace();

                                }

                      }

                       

                      }

                       

                       

                      I can't figure out where is the problem.

                      getTableColumnDefault function is not yet finished. Can be helpful if  I can get JDBC type and Hibernate type.

                      It is possible ?

                       

                      Thank You,

                      Alex

                      • 8. Re: Hibernate Tools question
                        Max Rydahl Andersen Master

                        you cannot access the connection in the constructor.

                         

                        have to wait until runtime info have been given to you, once it actually calls method on the class.

                         

                        you are doing it too early hence why it is null in the constructor.

                        1 of 1 people found this helpful
                        • 9. Re: Hibernate Tools question
                          Alex Popa-Tesileanu Newbie

                          Hi,

                          The problem was how to get RuntimeInfo in order to get the connection.

                          The obvious response is to override configure method:

                          @Override

                                    public void configure(ReverseEngineeringRuntimeInfo runtimeInfo) {

                                              // TODO Auto-generated method stub

                                              super.configure(runtimeInfo);

                                              setRuntimeInfo(runtimeInfo);

                                              try {

                                                        setConn(getRuntimeInfo().getConnectionProvider().getConnection());

                                              } catch (SQLException e) {

                                                        // TODO Auto-generated catch block

                                                        e.printStackTrace();

                                              }

                                    }

                           

                          Problem solved. It work.

                          Bellow is all code, maybe will be useful for someone.

                          It is hardcoded for MySQL and doesn't include all data types.

                          Can be improved if will be possible to get column defaults and hibernate datatype in columnToMetaAttributes function.

                           

                           

                          package ro.alex.reveng;

                           

                           

                          import java.sql.Connection;

                          import java.sql.ResultSet;

                          import java.sql.SQLException;

                          import java.sql.Statement;

                          import java.util.HashMap;

                          import java.util.Map;

                           

                           

                          import org.hibernate.cfg.reveng.DelegatingReverseEngineeringStrategy;

                          import org.hibernate.cfg.reveng.ReverseEngineeringRuntimeInfo;

                          import org.hibernate.cfg.reveng.ReverseEngineeringStrategy;

                          import org.hibernate.cfg.reveng.TableIdentifier;

                          import org.hibernate.mapping.MetaAttribute;

                          import org.apache.log4j.Logger;

                          import org.apache.log4j.BasicConfigurator;

                           

                           

                          public class CustReveng extends DelegatingReverseEngineeringStrategy {

                                    private ReverseEngineeringRuntimeInfo RuntimeInfo;

                                    private Connection conn;

                                    static Logger logger = Logger.getLogger(CustReveng.class);

                           

                           

                                    public CustReveng(ReverseEngineeringStrategy strategy) {

                                              super(strategy);

                                    }

                           

                           

                                    public Connection getConn() {

                                              return conn;

                                    }

                           

                           

                                    public void setConn(Connection conn) {

                                              this.conn = conn;

                                    }

                           

                           

                                    public void setRuntimeInfo(ReverseEngineeringRuntimeInfo runtimeInfo) {

                                              RuntimeInfo = runtimeInfo;

                                    }

                           

                           

                                    public ReverseEngineeringRuntimeInfo getRuntimeInfo() {

                                              return RuntimeInfo;

                                    }

                           

                           

                                    @Override

                                    public void configure(ReverseEngineeringRuntimeInfo runtimeInfo) {

                                              // TODO Auto-generated method stub

                                              super.configure(runtimeInfo);

                                              setRuntimeInfo(runtimeInfo);

                                              try {

                                                        setConn(getRuntimeInfo().getConnectionProvider().getConnection());

                                              } catch (SQLException e) {

                                                        // TODO Auto-generated catch block

                                                        e.printStackTrace();

                                              }

                                    }

                           

                           

                                    public Map<String, MetaAttribute> columnToMetaAttributes(

                                                        TableIdentifier identifier, String column) {

                           

                           

                                              Map<String, MetaAttribute> result = new HashMap<String, MetaAttribute>();

                                              MetaAttribute ma = new MetaAttribute("default-value");

                                              String defaultValue = getTableColumnDefault(identifier, column);

                                              if (defaultValue != null && !defaultValue.isEmpty()) {

                                                        ma.addValue(defaultValue);

                                                        result.put("default-value", ma);

                                              }

                                              return result;

                                    }

                           

                           

                                    public String tableToClassName(TableIdentifier tableIdentifier) {

                                              tableIdentifier = removePrefix(tableIdentifier);

                                              return super.tableToClassName(tableIdentifier);

                                    }

                           

                           

                                    private TableIdentifier removePrefix(TableIdentifier tableIdentifier) {

                                              String name = tableIdentifier.getName();

                                              if (name.toLowerCase().startsWith("aaa_")) {

                                                        tableIdentifier = new TableIdentifier(tableIdentifier.getSchema(),

                                                                            tableIdentifier.getCatalog(), name.substring(4));

                                              }

                                              return tableIdentifier;

                                    }

                           

                           

                                    public String getTableColumnDefault(TableIdentifier identifier,

                                                        String column) {

                                              String xdefault = "";

                                              String xType = "";

                                              String query = "SHOW COLUMNS FROM " + identifier.getName() + " LIKE '"

                                                                  + column + "'";

                                              try {

                                                        Connection con = getConn();

                                                        Statement stmt = con.createStatement();

                                                        ResultSet rs = stmt.executeQuery(query);

                                                        while (rs.next()) {

                                                                  xdefault = rs.getString(5);

                                                                  xType = rs.getString(2);

                                                        } // end while

                                              } // end try

                                              catch (SQLException e) {

                                                        e.printStackTrace();

                                                        logger.info("SQL Error: " + e.getMessage());

                                              }

                                              // Code to calculate default value

                                              if (xType.substring(0, Math.min(xType.length(), 4)).equals("char")) {

                                                        if (xType.trim().equals("char(1)")) {

                                                                  if (xdefault.isEmpty()) {

                                                                            xdefault = "\' \'";

                                                                  } else {

                                                                            xdefault = "\'" + xdefault + "\'";

                                                                  }

                                                        } else {

                                                                  xdefault = "\"" + xdefault + "\"";

                                                        }

                                              } else if (xType.substring(0, Math.min(xType.length(), 7)).equals(

                                                                  "varchar")) {

                                                        if (xType.trim().equals("varchar(1)")) {

                                                                  xdefault = "\'" + xdefault + "\'";

                                                        } else {

                                                                  xdefault = "\"" + xdefault + "\"";

                                                        }

                                              } else if (xType.trim() == "text") {

                                                        xdefault = "\"" + xdefault + "\"";

                                              } else if (xType.trim().equals("timestamp")) {

                                                        xdefault = "";

                                              } else if (xdefault != null

                                                                  && !xdefault.isEmpty()

                                                                  && (xType.trim().equals("date") || xType.trim().equals(

                                                                                      "datetime"))) {

                                                        xdefault = "new Date(0)";

                                              } else if (xType.substring(0, Math.min(xType.length(), 7)).equals(

                                                                  "decimal")) {

                                                        if (xdefault != null && xdefault.isEmpty()) {

                                                                  xdefault = "new BigDecimal(0)";

                                                        } else {

                                                                  xdefault = "new BigDecimal(" + xdefault + ")";

                                                        }

                                              } else if (xType.substring(0, Math.min(xType.length(), 3))

                                                                  .equals("int")

                                                                  || xType.substring(0, Math.min(xType.length(), 8)).equals(

                                                                                      "smallint")

                                                                  || xType.substring(0, Math.min(xType.length(), 7)).equals(

                                                                                      "tinyint")) {

                                                        xdefault = "0";

                                              } else {

                                                        xdefault = "\"ccc\""; //Dummy value for control

                                              }

                           

                                              ///For compositeId's don't need to initialize variables

                                              boolean isPk = false;

                                              try {

                                                        ResultSet PKColumns = conn.getMetaData().getPrimaryKeys(

                                                                            conn.getCatalog(), "conta", identifier.getName());

                                                        while (PKColumns.next()) {

                                                                  if (PKColumns.getString(4).equals(column)) {

                                                                            isPk = true;

                                                                  }

                                                        }

                                              } catch (SQLException e) {

                                                        // TODO Auto-generated catch block

                                                        e.printStackTrace();

                                              }

                                              if (isPk) {

                                                        xdefault = "";

                                              }

                                              // }

                                              return xdefault;

                                    }

                           

                           

                                    public void finalize() {

                                              try {

                                                        getConn().close();

                                              } catch (SQLException e) {

                                                        // TODO Auto-generated catch block

                                                        e.printStackTrace();

                                              }

                                              try {

                                                        super.finalize();

                                              } catch (Throwable e) {

                                                        // TODO Auto-generated catch block

                                                        e.printStackTrace();

                                              }

                           

                           

                                    }

                           

                           

                          }

                           

                           

                          Thank you very much for help,

                          Alex

                          • 10. Re: Hibernate Tools question
                            Max Rydahl Andersen Master

                            Glad you solved it - one thing I noticed is you are closing the connection directly - you should use the connectionprovider to close/release it.

                            • 11. Re: Hibernate Tools question
                              Alex Popa-Tesileanu Newbie

                              Yes, you are right.

                              It remains unchanged from the time when I created custom connection.

                              I will change.

                               

                              Regards,

                              Alex