Sunday, June 12, 2011

Java Hibernate with MSSQL EXPRESS hibernate simple utilities

 Хэрэв sql express 2005 дээр hibernate хийх гэж байгаа бол connection error гарвал дараах байдлаар асуудлыг ингэж тохируулж болно. энэ хэсэг нэг их чухал биш угаасаа

NetBeans iin hibernate category -оос сонгоод оруулчина. дараалал нь миний блог дээр байгаа

IDE: Netbeans 6.5

Framework Hibernate:3.2.5


Problems Faced:
Step 1. Start NetBeans.
Open Services.
Right click on Database.
Click new connection.
Do the entries: According to the database exists in SQL SERVER 2005. Like: Driver name:MS SQL Server 2005
host: localhost
database: abc
user: zyx
password: ******
Click OK.

Problem 1: First I am going to make new connection with Database: MS SQL Server 2005 Express, through sqljdbc dirver 1.2. It wasn't be connected earlier.
SQL Server is running.

It gave an Exception "The TCP/IP connection to the host is failed. java.net.ConnectException. Connection refused."


Now, I open SQL Server 2005 Configuration Manager and there I open SQL Server Network Configuration>protocols for SQLEXPRESS>TCP/IP(right click) Properties>IP Addresses tab> IPAll> TCP Port:1433(changed)> apply and ok.




Энэ бол жишээ код ингэж олон серверт тохируулан програмчилж болно
үзээд тохирох газар нь байрлуулаарай

PropertyConfiguration бол зүгээр л нэг Properties уншиж бичдэг класс өөрсдөө зохиогоод эсвэл шууд хатуу текст бичээд шалгачихсан ч болно.

HibernateSession.java

// Create the SessionFactory from standard (hibernate.cfg.xml)
            // config file.
            AnnotationConfiguration anno = new AnnotationConfiguration().configure();
            try {
                if (PropertyConfiguration.index != null) {
                    String serverName = PropertyConfiguration.getPropertyValue(ProgramProperty.Program_ServerName);
                    String databaseName = PropertyConfiguration.getPropertyValue(ProgramProperty.Program_DatabaseName);
                    String connType = PropertyConfiguration.getPropertyValue(ProgramProperty.Program_ConnectionType);
                    if (connType.toLowerCase().equals(SqlConnectionType.Mssql.name().toLowerCase()))
                    {
                        String url = "jdbc:sqlserver://" + serverName + ":1433;databaseName=" + databaseName;
                        anno.setProperty("hibernate.connection.url", url);
                    }
                    else if (connType.toLowerCase().equals(SqlConnectionType.MssqlExpress.name().toLowerCase()))
                    {
                        String url = "jdbc:microsoft:sqlserver://" + serverName + ":1433;databaseName=" + databaseName;
                        anno.setProperty("hibernate.connection.url", url);
                    }
                }
            } catch (Exception ex) {
                System.err.println("new AnnotationConfiguration().configure() finished.");
                System.err.println("Initial SessionFactory configuration failed." + ex);
            }


HibernateUtil.java
Энэ бол бэлэн код юм ямар нэг класс үүсгээд дотор нь хуулчих тэгээд HibernateSession.java тайгаа хослоод ажиллачина.
Нэг main() { дотроо туршаад үзээрэй }

public static List execute(String procedure, List<DataHolderEntity> filter) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();

            Query query = (procedure.startsWith("select ") || procedure.startsWith("from "))
                    ? newSession.createQuery(procedure) : newSession.getNamedQuery(procedure);
            addQueryParameters(query, filter, false);
            List data = query.list();

            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static int execute(String procedure, List<DataHolderEntity> filter, boolean onlyUseIndex0){
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();

            Query query = (procedure.startsWith("select ") || procedure.startsWith("from "))
                    ? newSession.createQuery(procedure) : newSession.getNamedQuery(procedure);
            addQueryParameters(query, filter, onlyUseIndex0);
            int ret = query.executeUpdate();

            newSession.close();

            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        }
    }

    public static int executeSave(Object model) {
        Session newSession = null;
        Transaction newTransaction = null;
        try {
            newSession = HibernateSessionUtil.getSessionFactory().openSession();
            newTransaction = newSession.beginTransaction();

            newSession.save(model);

            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        } finally {
            if (newTransaction != null) {
                newTransaction.commit();
            }
            if (newSession != null) {
                newSession.close();
            }
        }
    }

    public static int executeUpdate(Object model) {
        Session newSession = null;
        Transaction newTransaction = null;
        try {
            newSession = HibernateSessionUtil.getSessionFactory().openSession();
            newTransaction = newSession.beginTransaction();

            newSession.saveOrUpdate(model);

            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        } finally {
            if (newTransaction != null) {
                newTransaction.commit();
            }
            if (newSession != null) {
                newSession.close();
            }
        }
    }

    public static int executeDelete(Object model) {
        Session newSession = null;
        Transaction newTransaction = null;
        try {
            newSession = HibernateSessionUtil.getSessionFactory().openSession();
            newTransaction = newSession.beginTransaction();

            newSession.delete(model);
           
            return 1;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        } finally {
            if (newTransaction != null) {
                newTransaction.commit();
            }
            if (newSession != null) {
                newSession.close();
            }
        }
    }

    public static int executeDelete(String tableName, String keyField, Long id) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();

            Query q = newSession.createQuery("delete from " + tableName + " as s where s." + keyField + " =:" + keyField);
            q.setLong(keyField, id);
            int rowCount = q.executeUpdate();

            newTransaction.commit();
            newSession.close();

            return rowCount;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        }
    }

    public static Object executeNonQuery(String hql, Object parameters, boolean scaler) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
           
            boolean b = hql.startsWith("select ") || hql.startsWith("from ");
            Query query = b ? newSession.createQuery(hql) : newSession.getNamedQuery(hql);
            if (parameters != null) {
                if (b) {
                    Object[] iparams = (Object[])parameters;
                    for(int i = 0; i < iparams.length; i++) {
                        query.setParameter(i, iparams[i]);
                    }
                } else {
                    String[] pars = query.getNamedParameters();
                    Map pmap = (Map)parameters;
                    for (String par : pars) {
                        query.setParameter(par, pmap.get(par));
                    }
                }
            }
            Object object = null;
            if(scaler) {
                object = query.uniqueResult();
            } else {
                object = query.executeUpdate();
            }
            newTransaction.commit();
            newSession.close();

            return object;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return 0;
        }
    }
   
    public static List executeQuery(String hql) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
           
            Query query = (hql.startsWith("select ") || hql.startsWith("from "))
                    ? newSession.createQuery(hql) : newSession.getNamedQuery(hql);
            List data = query.list();

            newTransaction.commit();
            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static List executeQuery(String hql, Object[] parameters) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
            boolean b = hql.startsWith("select ") || hql.startsWith("from ");
            Query query = b ? newSession.createQuery(hql) : newSession.getNamedQuery(hql);
            if (parameters != null) {
                if (b) {
                    for(int i = 0; i < parameters.length; i++) {
                        query.setParameter(i, parameters[i]);
                    }
                } else {
                    String[] pars = query.getNamedParameters();
                    int i = 0;
                    for (String par : pars) {
                        query.setParameter(par, parameters[i]);
                        i++;
                    }
                }
            }
            List data = query.list();

            newTransaction.commit();
            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static List executeCriteriaQuery(CriteriaHandler criteriaHandler, Class<?> type) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();
           
            Criteria crit = newSession.createCriteria(type);
            crit = criteriaHandler.CriteriaConfigure(crit);
            List data = crit.list();
           
            newTransaction.commit();
            newSession.close();

            return data;
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return null;
        }
    }
   
    public static boolean Login(String tableName, String name, String password) {
        try {
            Session newSession = HibernateSessionUtil.getSessionFactory().openSession();
            Transaction newTransaction = newSession.beginTransaction();

            Query query = newSession.createQuery("from "
                    + (tableName == null ? "UserInfo" : tableName) + " where ( Email=? or Name=? ) and Password=?");
            query.setString(0, name);
            query.setString(1, name);
            query.setString(2, password);
            List l = query.list();
           
            newTransaction.commit();
            newSession.close();
           
            if (l.size() > 0) {
                ProgramSession.User.fill(l.get(0));
                return true;
            } else {
                return false;
            }           
        } catch (Exception ex) {
            SystemUtil.message(ex);
            return false;
        }
    }

No comments: