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.
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.
And also, as an experiment, we will create a simple procedure for saving to the database, which will have both input parameters and output.
Now let's move on to classes in Java.
We display our 3 database tables in the classes as follows:
A little explanation on the code:
The mapping of the following 2 database tables will look like this:
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:
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:
Well, to call our save_book procedure, we will do the following manipulations:
seems to be all. Of course, I could not cover everything, but I think this is enough for a quick start.
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
we will load data only on demand.Set
bookList
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:
- Using HQL (Hibernate Query Language) Queries
List
book = (List )session.createQuery("from Book order by name").list(); - 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(); - 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.OracleDriver jdbc:oracle:thin:@localhost:port:baseName username password org.hibernate.dialect.Oracle10gDialect true
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.