Hibernate for the smallest and not only

    Good day to all! When writing a program that will somehow interact with the database, they use different means. This is the good old jdbc , also used: EclipseLink , TopLink , iBatis (already MyBatis ), Spring Framework and of course the hero of our article - Hibernate . Of course, I did not list all the tools for working with the database here, but I tried to indicate the most common ones. This article will show how to use Hibernate to call stored procedures, map both tables and class queries. We will use Oracle as a test database.
    We will prepare everything necessary for the experiments. Let's start with the database. To begin, create 3 tablets, on which we will practice.
    CREATE TABLE book
    (
        id             NUMBER NOT NULL,
        name           VARCHAR2 (100 BYTE) NOT NULL,
        description    VARCHAR2 (1000 BYTE) NOT NULL,
        CONSTRAINT pk$book_id PRIMARY KEY (id)
    )
    CREATE TABLE student
    (
        id         NUMBER NOT NULL,
        name       VARCHAR2 (100 BYTE) NOT NULL,
        CONSTRAINT pk$student_id PRIMARY KEY (id)
    )
    CREATE TABLE catalog
    (
        id_student   NUMBER NOT NULL,
        id_book      NUMBER NOT NULL
    )
    

    Now create a function. which will return the title of the book to us by its id, an example is stupid, but it will show the principle of calling a function with an input numeric parameter and the output string.
    CREATE OR REPLACE
    FUNCTION get_book_name_by_id (p_id IN NUMBER)
        RETURN VARCHAR2
    IS
        v_name   VARCHAR2 (100);
    BEGIN
        SELECT name
          INTO v_name
          FROM book
         WHERE id = p_id;
        RETURN v_name;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            RETURN 'ну нет такой книги!!';
    END;
    

    And also, as an experiment, we will create a simple procedure for saving to the database, which will have both input parameters and output.
    CREATE OR REPLACE 
    PROCEDURE save_book (p_id      IN OUT NUMBER,
                             p_name    IN     VARCHAR2,
                             p_descr   IN     VARCHAR2)
    IS
    BEGIN
        IF p_id > 0
        THEN
            UPDATE book
               SET name = p_name, description = p_descr
             WHERE id = p_id;
        ELSE
            SELECT catalog_seq.NEXTVAL INTO p_id FROM DUAL;
            INSERT INTO book
            VALUES (p_id, p_name, p_descr);
        END IF;
    END;
    

    Now let's move on to classes in Java.
    We display our 3 database tables in the classes as follows:
    @Entity
    @Table
    public class Student implements Serializable {
    	private static final long serialVersionUID = -5170875020617735653L;
    	@Id  
    	@GeneratedValue(strategy = GenerationType.AUTO, generator = "my_entity_seq_gen")
    	@SequenceGenerator(name = "my_entity_seq_gen", sequenceName = "catalog_seq")
    	private long id;
    	@Column
    	private String name;
    	@OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
    	private Set bookList;
    		// здесь идет реализация методов  getter, setter, hashCode(), equals(), toString()
    }
    

    A little explanation on the code:
    • if your class name does not coincide with the name of the table, then for example we write like this: Table (name = "STUDENT");
    • one of the requirements of the framework is that each table must have an id, also if the name of the id field of the table and the name of our variable do not match, then the annotation will look like this: Id @Column (name = “table id in the database”);
    • using the annotations @GeneratedValue and @SequenceGenerator, we determine the strategy for generating a unique identifier, in this case we say that when saving information to the database, we take a number from sequence with the name "catalog_seq";
    • since we have one student can have several books, we will display this using the annotation like this @OneToMany (mappedBy = "student", fetch = FetchType.LAZY), where mappedBy = "student" is the name of the field in the Book class (see below), and FetchType.LAZY - tells us that the collection
      Set bookList
      we will load data only on demand.

    The mapping of the following 2 database tables will look like this:
    @Entity
    @Table
    public class Book implements Serializable {
    	private static final long serialVersionUID = 1L;
    	@Id
    	@Column(name="ID")
    	@GeneratedValue(strategy = GenerationType.AUTO, generator = "my_entity_seq_gen")
    	@SequenceGenerator(name = "my_entity_seq_gen", sequenceName = "catalog_seq")
    	private long id;
    	@Column(name="NAME",unique = true, nullable = false, length = 100)
    	private String name;
    	@Column(name="DESCRIPTION",unique = true, nullable = false, length = 100)
    	private String description;
    	@ManyToOne(fetch = FetchType.LAZY,optional=true)
    	@JoinTable(name = "CATALOG", joinColumns = @JoinColumn(name = "ID_BOOK"), inverseJoinColumns = @JoinColumn(name = "ID_STUDENT"))
    	private Student student;
    	// здесь идет реализация методов  getter, setter, hashCode(), equals(), toString() 
    }
    

    Constraints for fields can be set directly in the annotation, this is done by the following line @Column (name = "NAME", unique = true, nullable = false, length = 100). With the annotations @ManyToOne and @JoinTable (name = "CATALOG") we say that the Book table and the Student table have a many-to-many relationship through the Catalog table, therefore, all changes to Book and Student will automatically be applied to Catalog table.
    Well, we are done with mapping tables, and now we will move on to working directly with the database.
    Data extraction while filling the collection can be done in different ways:
    1. Using HQL (Hibernate Query Language) Queries
      	List book = (List)session.createQuery("from Book order by name").list();
      	
    2. using SQL queries
      	List book = (List)session.createSQLQuery("select ID, DESCRIPTION, NAME from book order by NAME")
      	.addScalar("id",Hibernate.LONG).addScalar("name").addScalar("description")
      	.setResultTransformer(Transformers.aliasToBean(Book.class)).list();
      	
    3. with Criteria
      	List book=(List)session.createCriteria(Book.class).createAlias("student", "st").add(Restrictions.eq("st.name", "Maxim")).list();
      	

    Let's move on to working with stored procedures. In order to call the function, which by id will return the name of the book, we perform the following steps:
    String bookName = (String)session.createSQLQuery("{? = call get_book_name_by_id (:id)}").setLong("id",1).uniqueResult();
    
    Suppose we do not have a table on the server called Student, but there is only a function that returns a cursor:
    FUNCTION get_all_students
        RETURN SYS_REFCURSOR
    IS
        l_cur   SYS_REFCURSOR;
    BEGIN
        OPEN l_cur FOR
            SELECT *
              FROM student
           ORDER BY 1;
        RETURN l_cur;
    END;
    
    then mapping will take place as follows, instead of annotation @Table(name="STUDENT")we write
    @NamedNativeQuery(name="getAllStudent",query="{? = call get_all_students}", callable=true, resultClass=Student.class)
    And the call to this function will be as follows:
     List student = (List) session.getNamedQuery("entity").list();

    Well, to call our save_book procedure, we will do the following manipulations:
    CallableStatement st = session.connection().prepareCall("{call save_book(?,?,?)}");
    			st.setLong(1,0);
    			st.setString(2, "Золотой ключик, или Приключения Буратино");
    			st.setString(3,"повесть-сказка Алексея Николаевича Толстого");
    			st.registerOutParameter(1, java.sql.Types.NUMERIC);
    			st.execute();
    System.out.println(st.getLong(1));
    
    As you probably noticed, when writing commands to access the database and populate the collection, the word session was used. In our case, this word indicates the main interface between our Java application and the Hibernate framework, i.e. org.hibernate.Session session. But first, we need to use another fundamental and important interface - SessionFactory. SessionFactory is the global factory responsible for a specific database. To get this factory we need to get an instance of the org.hibernate.cfg.Configuration class. It is done like this:
     SessionFactory sessions = new Configuration().configure().buildSessionFactory();
    
    Where Configuration (). Configure (). BuildSessionFactory () parses a file called hibernate.cfg.xml, which is located next to the program being called, of course, if the path is not specified. Here is the configuration file, which shows the configuration of the connection to the database and the display of our tables:
    oracle.jdbc.driver.OracleDriverjdbc:oracle:thin:@localhost:port:baseNameusernamepasswordorg.hibernate.dialect.Oracle10gDialecttrue
    And now, when we have a factory in which there is all the necessary configuration (connection to the database through the pool, mapping / display of tables, etc.), we can work with Session
    Session session = sessions.openSession();
    You can work with transactions this way: session.beginTransaction();and, accordingly, it session.getTransaction().commit();
    seems to be all. Of course, I could not cover everything, but I think this is enough for a quick start.

    Also popular now: