Pages

Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Wednesday, March 25, 2020

Tuesday, March 24, 2020

Saturday, November 9, 2019

Docker container unhealthy

The other day I was just starting all my docker instances by running docker-compose up -d like I often do, but it started failing telling me that some of the containers were unhealthy.


results-db is up-to-date
Starting db cnlts_main-db_1 ...

Starting db cnlts_main-db_1 ... done


ERROR: for cnlts-db-flyway Container "0ab31508826c" is unhealthy.
ERROR: Encountered errors while bringing up the project.


After running docker logs "0ab31508826c" to see what was going on in the container, I noticed this:



initdb: could not create directory "/var/lib/postgresql/data/pg_xlog": No space left on device

So went to the docker preferences page using the docker GUI to try to find the memory allocation in the disk settings

                                 

So there I say that the Disk image size was close to the max. One option was to increase it but I didn't want to do that because it would take unnecessarily more resources from my laptop. I think 64GB is more than enough. So what I just had to do was to delete that file called Docker.raw

The path to that file was written in the gui. I just had to go to that directory and delete it.
rm /Users/<username>/Library/Containers/com.docker.docker/Data/vms/0/Docker.raw 



Tuesday, January 8, 2019

Thursday, December 10, 2015

Queries to explore a database you don't know much about - Oracle SQL quick ref 4

This is the last of the posts I will do for now on Oracle quick refereces.
I decided to write this 4 blog articles to save me time using Google in the future.  So this last one is a bit peculiar, there are just a bunch of SQL commands that can be very useful for exploring a new database you don't know much about.

-- Finds all the columns in those tables that contain the string ESS
-- in the table name and orders them alphabetically
SELECT table_name, column_name
FROM ALL_TAB_COLS
WHERE table_name LIKE '%ESS%'
AND owner != 'MY_SCHEMA'
ORDER BY table_name;

-- Same as the previous one but includes more data and excludes the owner SYS
SELECT *
FROM ALL_TAB_COLS
WHERE table_name LIKE '%ESS%'
AND owner != 'SYS';

-- All the constraints for the tables in all schemas in the database
SELECT * 
FROM all_constraints;

-- All the constraints for the tables in all an specific schema
SELECT * 
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA';

-- All the foreign key constraints for the tables in all an specific schema
SELECT * 
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA'
AND UPPER(constraint_type) LIKE 'R';

-- This finds the primary key of a table
SELECT UPPER(constraint_name) 
FROM all_constraints
WHERE constraint_type IN ('P', 'U')
AND UPPER(table_name) LIKE UPPER(:r_table_name)
AND UPPER(owner) LIKE 'MY_SCHEMA';
   
-- Using the previous command as input to the following, allows us to find all of the
-- children of a particular table(Have as FK the PK of their parent)
SELECT table_name, constraint_name, status, owner
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA'
AND UPPER(constraint_type) LIKE 'R'
AND UPPER(r_constraint_name) IN
 (
   SELECT UPPER(constraint_name) 
   FROM all_constraints
   WHERE constraint_type IN ('P', 'U')
   AND UPPER(table_name) LIKE UPPER(:r_table_name)
   AND UPPER(owner) LIKE 'MY_SCHEMA'
 )
ORDER BY table_name, constraint_name;

--Find the parents of a table
SELECT * 
  FROM all_constraints
   WHERE constraint_type IN ('P', 'U')
   AND UPPER(owner) LIKE 'MY_SCHEMA'
   AND UPPER(constraint_name) in
   (
    SELECT UPPER(r_constraint_name)
    FROM all_constraints
    WHERE UPPER(table_name) LIKE UPPER(:r_table_name)
    AND UPPER(constraint_type) LIKE 'R'
   );
   
--Finding tables that contain a word in their name   
SELECT table_name, owner
FROM all_tables
WHERE UPPER(table_name) LIKE '%something%'; 

Subquery basics - Oracle SQL quick ref 3

A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. A sub-query is also called an inner query or inner select, while the statement containing a sub-query is also called an outer query or outer select.

Depending on the amount of results a sub-query returns, we distinguish 2 types of sub-queries:


  • Single row sub-queries: Queries that return only one row from the inner SELECT statement
  • Multiple row sub-queries: Queries that return multiple rows from the inner SELECT statement


In the following single sub-query example, the AVG function returns just one result which is used by the outer query.

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary;


In the next example a multiple row sub-query is used to return some departments which the outer query will use in its IN clause.

SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM departments
WHERE location_id=2897); 

It is important that both single row and multiple row sub-queries are correctly used in a context that either expects a single result or a multiple result respectively.
Its important to mention that the Oracle server will always execute sub-queries first.

Sub-queries can be used in the WHERE clause, but also in the HAVING clause.
This example returns job with the lowest average salary.

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                                           FROM employees 
                                           GROUP BY job_id);


The difference between HAVING and WHERE, is that WHERE does not work with aggregated functions, such as AVG, COUNT...




Using Joins - Oracle SQL quick ref 2

NATURAL JOIN is a type of join that relies on both tables involved on the join to have a common column name. For example if an employee table has a foreign key called department_id and the departments table has a primary key called department_id, then it is possible to do a natural join because in both sides there is a column with the same name.

SELECT employee_id, last_name, department_id, department_name
FROM employees NATURAL JOIN departments;

Here another example, where both the locations and departments table, have location_id:

SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
WHERE department_id IN (20, 50);

There is a type of join, that uses the USING keyword. This Join, requires that the column that is used for the join is explicitly mentioned in the USING clause.

SELECT employee_id, last_name, location_id, department_id
FROM employees JOIN departments
USING (department_id);

When we use the USING keyword, we are allowed to use aliases if we want but the only limitation is that the WHERE clause cannot use aliases. For example, WHERE d.location_id... would not be valid.

SELECT l.city, d.department_name
FROM locations l JOIN departments d
USING (location_id)
WHERE location_id = 1400;


SELECT first_name, d.department_name, d.manager_id
FROM employees e JOIN departments d
USING (department_id)
WHERE department_id = 50;


The ON keyword is the most commonly used way of doing joins in this type of does not mandate that the columns in both sides have the same name, also there are no limitations in the usage of aliases in the WHERE clause.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id);


The JOIN clause has an AND part, which is equivalent to the WHERE clause, so WHERE is not needed but can also be used along side.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
AND e.manager_id=149;


SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.manager_id=149;

It is possible to have as many JOIN clauses as desired in a query.

SELECT e.last_name, e.job_id, e.department_id, d_depatment_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
WHERE l.city = 'London';

Sometimes its necessary to perform self joins. In this example a non normalized table of employees, has a row to represent that the worker is a manager. So if we wanted to see who is the manager of who, we would have to do something like this.

SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker
JOIN employees manager ON (worker.manager_id = manager.employee_id);

Also in SQL, there are other types of joins called OUTER and INNER  that are useful when we want to widen or narrow the result set. INNER JOIN  will only return rows, if both tables in the join have associated data in the other side of the join. OUTER JOIN is not that strict, they will still return rows even if they have no associated data in the other side of the join. While there is one type of INNER JOIN, the OUTER JOIN cam come in 3 flavours(LEFT,RIGHT, FULL).

The LEFT OUTER JOIN will include all employees and their department name even if they don't have an associated department.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

The RIGHT OUTER JOIN will include all the departments that are associated with employees, but also all the departments that are not associated with employees at all.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);


The FULL OUTER JOIN will return all the departments even if they are not associated with employees and also all the employees even if they are not associated with departments.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);


The INNER JOIN will return all results that have employees with departments and all departments with employees.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e  INNER JOIN departments d
ON (e.department_id = d.department_id);



Grouping data - Oracle SQL quick ref 1

Oracle SQL group functions operate on sets of rows to give one result per group of inputs(In other words, many inputs, one output): AVG,COUNT,MAX,MIN,SUM...

SELECT MAX(salary) max, MIN(salary) min, SUM(salary) sum, ROUND(AVG(salary),0) avg
FROM employees;

SELECT COUNT(*) numberOfEmployees
FROM employees;

Sometimes the rows of data, may contain duplications that we don't want to be taken into account.
Then the DISTINCT keyword, can be useful to suppress the duplicates.

SELECT COUNT(DISTINCT department_id)
FROM employees

By default group functions will ignore null values but if we want to not ignore then, we should use the NVL function.

SELECT NVL(AVG(commission_pct))
FROM employees;

It is possible in sql to select different groups/sub sets of data, from one same table, this is know as grouping. The GROUP BY keyword allows us to divide the rows of a table into groups and optionally latter apply a group function to act upon each of those groups.
For example, we could get the average salary for each department using the group by function:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id; 

Important!: If a SELECT statement contains a GROUP BY clause, all non aggregate functions(department_id) that are defined in the SELECT, need to also be in the GROUP BY clause.

When working with GROUP BY and we want to add additional restrictions to the groups, we are not allowed to use the WHERE clause, instead we must use the HAVING clause.
For example lets say that we want to see the departments and their max salaries, but only if the max salary of the department is greater than 10000:

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;


Wednesday, February 4, 2015

Exposing the data layer of your app using REST

The more we sepparate the concerns of our system, the more mainteinable it becomes.

It is very common to find applications written in such way that the data access mechanisms(SQL files, JDBC client code, ORM mappings...) are located just next to(coupled/interdependant) the service/bussiness logic. This often makes finding bug, making a change, etc.. harder.

Calculating a result and storing it, are different things. So why not sepparating those 2 responsibilities among different applications?

One would be responsible of making sure the results are calculated and the other will just provide data management support.
In my opinion the result of doing this is a system that is more understandable, maintainable and upgrade friendly.

In many companies, the data is often managed by database engineering teams which have: schedules, goals and even different managers than the development teams. In this type of organization, delays, missunderstandings, conflicts of interests and work de-synchronization are very common. So to make the most of a decoupled system, we not just need a good software approach, but also a process and team structure that are compatible with it(But this may be a topic for another post). This type of decoupling will not just make maintenance easy for the developers but also, it will probably encourage discussion about the process and the teams structure.

In my example I decided expose 2 persistent services via 1 url and persisting simultaniously in 2 types of databases(a sql and a no-sql DB).

This is the implementation of the no-sql adapter


 public class NoSqlAddressInsertAdapter implements CreateService {  
   private final MongoClient mongoClient;  
   @Inject  
   public NoSqlAddressInsertAdapter(MongoClient mongoClient) {  
     this.mongoClient = mongoClient;  
   }  
   @Override  
   public void create(Address address) {  
     DBCollection collection = mongoClient.getDB("radadata").getCollection("address");  
     collection.insert(toNoSqlAddress(address));  
   }  
   private AddressNoSql toNoSqlAddress(Address address) {  
     AddressNoSql addressNoSql = new AddressNoSql();  
     addressNoSql.append("firstline", address.getFirstLine());  
     addressNoSql.append("secondline", address.getSecondLine());  
     addressNoSql.append("postcode", address.getPostcode());  
     addressNoSql.append("persons", address.getPersons().stream().map(toNoSqlPersons()).collect(toList()));  
     return addressNoSql;  
   }  
   private Function<Person, PersonNoSql> toNoSqlPersons() {  
     return person -> {  
       PersonNoSql personNoSql = new PersonNoSql();  
       personNoSql.append("firstname", person.getFirstName());  
       personNoSql.append("secondname", person.getSecondName());  
       return personNoSql;  
     };  
   }  
 }  

This is the implementation of the sql-adapter


 public class SqlAddressInsertAdapter implements CreateService {  
   @Inject  
   public SqlAddressInsertAdapter() {  
   }  
   private static SessionFactory getSessionFactory() {  
     return HibernateUtil.getSessionFactory();  
   }  
   private Session session;  
   @Override  
   public void create(Address address) {  
     session = SqlAddressInsertAdapter.getSessionFactory().getCurrentSession();  
     session.beginTransaction();  
     Set<ORMPerson> ormPersons = address.getPersons().stream().map(toOrmPersons()).collect(toSet());  
     ORMAddress ormAddress = new ORMAddress();  
     ormAddress.setFirstLine(address.getFirstLine());  
     ormAddress.setSecondLine(address.getSecondLine());  
     ormAddress.setPostcode(address.getPostcode());  
     ormAddress.setOrmPersons(ormPersons);  
     session.save(ormAddress);  
     session.getTransaction().commit();  
   }  
   @Override  
   public void create(Person person) {  
     //  
   }  
   private Function<Person, ORMPerson> toOrmPersons() {  
     return person -> new ORMPerson(person.getFirstName(),person.getSecondName());  
   }  
 }  

Note that both adapters use their specific domain objects, one uses ORM(Those ORMClasses are hibernate entities) and the other doesn't.

This is a sample REST endpoint will allow access to those services simultaniously


 @Service  
 @Path("insertperson")  
 public class InsertAddressResource {  
   private final services.nosqlcrud.CreateService noSqlcreateService;  
   private final services.sqlcrud.CreateService sqlCreateService;  
   @Inject  
   public InsertAddressResource(services.nosqlcrud.CreateService noSqlcreateService,  
                  services.sqlcrud.CreateService sqlCreateService) {  
     this.noSqlcreateService = noSqlcreateService;  
     this.sqlCreateService = sqlCreateService;  
   }  
   @POST  
   @Consumes({"application/json"})  
   public void insert(Address address) {  
     noSqlcreateService.create(address);  
     sqlCreateService.create(address);  
   }  
   /*  
     A Sample Json to POST:  
     URL: http://localhost:9998/insertperson  
     Content Type: application/json  
     {  
      "firstline": "street bla bla",  
      "secondline": "town of bla bla",  
      "postcode": "ble ble ble",  
      "persons": [  
         {"firstname":"Armin","secondname":"Josef"},  
         {"firstname":"Johan","secondname":"Uhgler"}  
       ]  
     }  
   */  
 }  

This snippets of code are just part of a demo app I wrote some days ago to show how to expose the data layer via REST.
The rest of the project, can be found at: https://github.com/SFRJ/Rest-Approach-to-Data-Persistence-R.A.D.A-

Monday, February 24, 2014

paranoia testing

Somebody told me once a funny story about a woman who day after day kept arriving late to work, because once on her way to the office, she kept having the need to go back home again and again just to check that she unplugged the iron from the wall socket. Her unfounded fear was such that one day she finally decided to take the iron with her to the office in her handbag.

A common unfounded fear that some developers sometimes have when working with the database, is that they will fail to query it for whatever reason and they feel the need of testing that the database is returning them the expected values. It is common to see horrible tests like this one:

   @Test  
   public void whenAddingANewUserTheUserIsSavedToTheDatabase() {  
     //Tell hibernate to add this user to the database   
     ormAdapter.add(userFactory("djordje", "123"));  
     //Find the user and check its values  
     User savedValueInDatabase = adapter.find("djordje");  
     assertThat(savedValueInDatabase.getName(),is("djordje"));  
     assertThat(savedValueInDatabase.getPassword(),is("123"));  
     //Delete the test data  
     ormAdapter.delete(savedValueInDatabase.getName());  
   }   

The above test has many problems, not just it is slow, also it is wrongly aimed because is not testing the functionality of the application, it just testing that some third party framework(hibernate in this case) is working properly.

Developers who do this, don't understand that the database is just a detail/add-on of the application.
It is fine to perform a smoke test to ping the database if they want(just to verify that the configuration of the ORM framework was correctly done), but testing that the HQL or the hibernate criteria are returning some values is wrong.
"Hibernate, MySQL, Oracle... all are just vendors, their solutions will work, you don't need to test them again!"

Focus on testing the functionality by mocking the dependencies you have with the database.
The only thing that counts at the end of the day is that the inputs and outputs to the service layer are processed correctly. If the data comes from a database, a file, a socket... doesn't matter at all.
Here an alternative test that will mock a dependency to the database and will check that the data is processed correctly:

   @Test  
   public void loginTest() {   
     PersonManagementAdapterORM adapterORM = mock(PersonManagementAdapterORM.class);  
     when(adapterORM.find("djordje","123")).thenReturn(new Person("djordje","123"));  
     LoginService service = new LoginServiceImpl(adapterORM);  
     boolean authorized = service.login("djordje","123");  
     verify(adapterORM).find("djordje","123");  
     assertThat(authorized,is(true));  
   }  

Now tests main concern is the functionality and not how the data is added to, or retrieved from the database.
I trained the mock to behave as expected and I verified that its behavoir was executed as expected.
The database was not at all a concern.

Wednesday, June 15, 2011

Connecting to a database using JDBC

Two college friends asked me to create a "Hello World" level pragmatical explanation of how a java system can connect to a database and interact with data. Also i've been told, to use any java api or tool i wish to achieve this task.

I think the easiest way to make a demo of how to connect to a database, is to use the JDBC api, that is included in the JSE-SDK since years. Also i think that for a learning purposes and better understanding of how java works with databases, this would be the best(Instead of using ORM tools like Hibernate or JPA)

Ok, lets get started:

Prerequisites:
-The java SE JDK
-
An IDE(I suggest you eclipse)
-A database driver(More info about JDBC drivers )
-A database server(For its simplicity to use, i would recommend you  Microsoft SQL 2005)

The source code:


1:  import java.sql.Connection;  
2:  import java.sql.DriverManager;  
3:  import java.sql.ResultSet;  
4:  import java.sql.SQLException;  
5:  import java.sql.Statement;  
6:  public class DB {  
7:   public void dbConnect() {  
8:   Connection con = null;  
9:   Statement stmt = null;  
10:   ResultSet rs = null;  
11:   String connectionURL = "jdbc:sqlserver://127.0.0.1;databaseName=forJDBC;user=george;password=george;";  
12:   try {  
13:    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
14:    con = DriverManager.getConnection(connectionURL);  
15:    System.out.println("Connected!");  
16:    String SQL = "SELECT * FROM Samples";  
17:    stmt = con.createStatement();  
18:    rs = stmt.executeQuery(SQL);  
19:    while (rs.next()) {  
20:    System.out.println();  
21:    System.out.println(rs.getString(1) + "-" + rs.getString(2));  
22:    }  
23:   } catch (SQLException e) {  
24:    e.printStackTrace();  
25:   } catch (ClassNotFoundException e) {  
26:    e.printStackTrace();  
27:   } finally {  
28:    if (rs != null) {  
29:    try {  
30:     rs.close();  
31:    } catch (Exception e2) {  
32:     e2.printStackTrace();  
33:    }  
34:    if (stmt != null) {  
35:     try {  
36:     rs.close();  
37:     } catch (Exception e2) {  
38:     e2.printStackTrace();  
39:     }  
40:    }  
41:    if (con != null) {  
42:     try {  
43:     rs.close();  
44:     } catch (Exception e2) {  
45:     e2.printStackTrace();  
46:     }  
47:    }  
48:    }  
49:   }  
50:   }  
51:  }  
Explanation:


1- The first thing we need is to add the JDBC driver that will allow us to connect to the database into the projects builth path:


2.1-Make sure the database server is started.(Make sure you have logged in as an administrator)












2.2- Create a table in the database and add some data.
In this example i will not create the tables programatically. You can do it your selves with SQL Management Studio or similar tool. Just create a Database that meets the configuration above, and add two it a table called Sample with to VARCHAR fields. If you want to know more about how to programatically create tables with JDBC follow this link: http://www.roseindia.net/jdbc/jdbc-mysql/CreateTable.shtml


3-Create a connection URL:


1:  String connectionURL = "jdbc:sqlserver://127.0.0.1;databaseName=forJDBC;user=djoleP;password=djoleP;";  

4-Find the driver located path the ClassPath (if needed)

 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  

5-Connect to the database

 con = DriverManager.getConnection(connectionURL);  
 System.out.println("Connected!");  

6-Create a SQL Querry and an Statement, to be able to execute the query

1:  String SQL = "SELECT * FROM Samples";  
2:    stmt = con.createStatement();  
3:    rs = stmt.executeQuery(SQL); 


6-Extract data until the result set is empty

1:  while (rs.next()) {  
2:    System.out.println();  
3:    System.out.println(rs.getString(1) + "-" + rs.getString(2));  
4:    }  



7-Close the result set, connection and other streams that you might open(The transaction is finished!)


1:  finally {  
2:    if (rs != null) {  
3:    try {  
4:     rs.close();  
5:    } catch (Exception e2) {  
6:     e2.printStackTrace();  
7:    }  
8:    if (stmt != null) {  
9:     try {  
10:     rs.close();  
11:     } catch (Exception e2) {  
12:     e2.printStackTrace();  
13:     }  
14:    }  
15:    if (con != null) {  
16:     try {  
17:     rs.close();  
18:     } catch (Exception e2) {  
19:     e2.printStackTrace();  
20:     }  
21:    }  
22:    }  





Download the source codes(The database driver is included in the project):
http://www.mediafire.com/?9f6ka2y4ctp6630

This post goes dedicated to my two good colleges from college Tripo and Zoran.




Friday, June 10, 2011

Glassfish 3 integrated database. What a great tool!

The database is one of the most important parts in almost every web project. It allows to store persistent data, for further use or analysis. Most of today web applications would be useless without a database.

Soon after starting using glassfish server, i discovered that there is a great tool already included in this server.
The Derby database. In this post i want to give you a quick tutorial, on how to configure it to be able to use it with your JEE6 application and the eclipse IDE.

STEP 1 DEFINE A DRIVER TO CONNECT TO APPACHE DERBY
The driver will be used by our web app to connect to the database, depending on the database vendor, the driver is different. The driver we need should be found in the glassfish application server file system.

Prerequisites:
Install appropriate versions of Eclipse, Data Tools Platform, EMG, GEF, and Apache Derby. If necessary, switch to the Database Development perspective.

You can also create the driver definition when creating the connection profile.
  1. Select Window > Preferences from the main menu bar.
  2. Expand Data Management > Connectivity and select Driver Definitions.
  3. Click Add.
  4. In the Name/Type tab, indicate the version of Derby on which you want to create the driver definition.
    1. Select Derby for the Vendor Filter to narrow the display to only Derby driver definitions
    2. Select Derby Embedded JDBC Driver for the version of Derby you have installed.
    3. (Optional) Modify the Driver Name if a driver definition with this name already exists.
  5. In the JAR List tab, highlight the default derby.jar file and click Edit JAR/Zip to indicate the fully qualified path of the Derby JAR file.
    1. Browse to the location of the ZIP file.
    2. Click Open.
  6. (Optional) In the Properties tab, modify the default properties for the driver definition template.
Note: Options in the Properties tab vary based on database server type. You can edit or enter a property in the Value box.

  1. Click OK.
 NOTE: THE driver.jar is for emdebed database while driverclient.jar is for normal database(driverclient should be used).
STEP 2 CREATE THE DATABASE
I found a great tutorial on using Glassfish v3 with Eclipse at http://programming.manessinger.com/tutorials/an-eclipse-glassfish-java-ee-6-tutorial/ . Here I will summarize how to create a Derby DB in eclipse and how to configure connection pool and JDBC resources using the Glassfish Administration Console.

Before doing this make sure that the Glassfish preference Start the JavaDB database process when Starting GlassFish Server is ticked.

First we have to create the Derby Database, in eclipse is very simple. Go in
 Data Source Explorer tab and on the contextual menu of Database Connection select new. Now select Derby as Connection Profile Type and set the name of the database, for example ShortRental Database. 
Now in the next step set the database properties as in figure:


(Don’t close this window and click ok until you don’t finish in the administrator console)


Now start the GlassFish server and connect to the administration console (the default port is 4860) and go toResources / JDBC / Connection Pools. Now create a new Connection Pool. Set the parameters as in figure:

Now pass to the next step, leave the properties at bottom as they are, and set the advanced properties as in the Derby DB, see the next screenshot:



At this point the configuration is completed, make sure the Ping checkbox is enabled and now you can try to ping the database.

The last operation to do is to define a JNDI name for access the database. Go to
 Resources / JDBC / JDBC Resources, add a new one and connect it to the just cretaed pool.


The last step would be in our eclipse project add a new JDBC resource.(File>New>Other>JDBC Resource)


When interacting with the database, it is likely to get a JNDI error regarding to the pool name, to fix it you have set the attribute  pool-name to exact the same pool name as you gave in the glassfish admin panel. 
(Example: ShortRentalPool)

Share with your friends