What is a back-end? A back-end is a stand-alone application program - - PowerPoint PPT Presentation

What is a back-end?

A back-end is a stand-alone application program or the part of an application program that manages storing data to and fetching data from secondary memory. It is often matched by a an application or part of an application that manages user interaction, called a front-end, and (mostly) by a “middle” module that manages business logic and/or data processing for the whole application, referred to as “middle-tier” or “business logic layer”. In most modern applications, the back-end leaves all kinds of processing, except perhaps conversions, to the middle-tier.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 1 / 38

Back-end history

When databases began being used for storing application data it didn’t take long to realize that it was necessary to manipulate the database from within the aplication. The first solution was to allow embedding SQL commands in the program code. A preprocessor was used to substitute the SQL code with function calls to a database management library and then compile the resulting code with a standard compiler. COBOL was the first language that supported SQL-embedding and later C followed. Among all the languages C and C++ became popular but had performance problems due to the translation to and from declarative expressions (SQL) as both are imperative languages.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 2 / 38

Back-end history . . .

When database back-end persistence began being used for the web it started with embedding SQL into languages that were already used to increase dynamics in web pages. Perl, that is made primarily for string manipulation, was a perfect tool and Perl, C and shell-scripts quickly became popular for building everything from back-ends to full-fledged applications using CGI. It was not unusual to see application programs made up by a number of CGI modules with a few HTML pages as a user interface.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 3 / 38

Back-end nowadays

Mainly Java, C#, Ruby or Python based, sometimes C/C++, with ODBC or (for Java) JDBC. On top of that a package that raises the abstraction level, with Java SQLJ, Hibernate or similar packages. Both SQLJ and Hibernate uses JDBC (can be configured to use ODBC). There are similar packages for other languages. My opinion is that you should avoid ODBC with Java as ODBC is implemented in C/C++ which gives you an extra transformation. There are good native Java SQL drivers. Use them instead. Use ODBC only with C, C++ and languages that have good APIs to C and C++ but no native implementations for DB communication. A Python equivalent to Hibenate+JDBC is e.g. Elixir+SQLAlchemy A Ruby equivalent is ActiveRecord

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 4 / 38

Some architectures

I will mainly give examples in Java and assume that JDBC is installed on the CSC computers I will maintain a variety of drivers/packages (not only for Java) on

/info/DD2471/moddb11/lib

More may be installed if needed.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 5 / 38

JDBC

Java DataBase Connectivity (JDBC) API is (for Java) the most important API for developing database based or database driven applications. JDBC provides a standard API that is provider independant but also allows provider specific access JDBC is divided into two parts, the kernel (java.sql) that earlier was delivered with the standard package (JDK) while the other part, JDBC Optional Package (javax.sql.*), that contains the classes you need when developing applications using Enterprise Java Beans (EJB) was delivered with J2EE. Nowadays both libraries are delivered with J2SE (Java 2 Standard Edition) as database access is becoming increasingly popular (necessary?)

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 6 / 38

JDBC, conceptually

implements uses wraps 1..n connects to connects to DBMS Provider specific API Provider−independent API DBMS interface JDBC driver java−sql/javax.sql Java application

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 7 / 38

JDBC, class diagram

<<interface>> CallableStatement <<interface>> PreparedStatement <<interface>> Connection <<interface>> DatabaseMetaData <<interface>> Driver <<interface>> Statement <<interface>> ResultSet <<interface>> ResultSetMetaData DriverManager registers 0..* 0..* 0..* 0..* 1 1 1 1 1 1 provides provides provides creates retrieves

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 8 / 38

JDBC . . .

There are four types of JDBC drivers, named type 1, 2, 3 and 4. It may be important to know which one to pick for your application. Really important when implementing applets. Type 1 and 2 use “native” libraries that are written in C or C++ for the specific machine or platform. It may be difficult to find such a JDBC driver for a certain platform and they must be considered ancient as they mainly need JDK ≤ 1.3

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 9 / 38

Type 1 JDBC driver configuration

Java appl JDBC API driver library ODBC client driver JDBC type 1 DBMS API client library database client DBMS API server library database server

DBMS

A type 1 driver uses a general library (not db-specific) to connect to the DBMS providers connection library. The most common way is to use the JDK JDBC-ODBC bridge. Data “flows” through many layers, easily giving performance problems, so use the method only for testing or for platforms to which there are no

• ther drivers.

I use it only for very odd DBMS (and haven’t used it for many years).

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 10 / 38

Type 2 JDBC driver configuration

Database client application Java JDBC API driver JDBC type 2 client library DBMS API Database server server library DBMS API

DBMS

A type 2 driver uses the DBMS provider connection library directly and must be delivered with the DBMS or as an extra option. It is a better solution than type 1 as

• ne layer is skipped but you still

depend on things written in another language and get a performance loss because of the extra intermediary data structures that are necessary for the data transfer and tranformations.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 11 / 38

Type 3 JDBC driver configuration

Database client Java application JDBC API JDBC type 3 driver Middle−tier application server Middle−tier listener client library DBMS API Database server server library DBMS API

DBMS

A type 3 JDBC driver is implemented entirely in Java using a DBMS provider independant protocol to communicate with a “gateway”. The typical situation is when you need better security or you need to manage security closer to the data source or simply want to bypass applet security restrictions. Because of the middle tier slower than other solutions. The most common case for distributed applications is to divide them into tiers where parts of the application reside on the same server as the database server. On of these is the application middle tier containing application logics.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 12 / 38

Type 4 JDBC driver configuration

DBMS

Java application JDBC API Database client JDBC type 4 driver server library DBMS API Database server

A type 4 JDBC driver is implemented entirely in Java and connects directly to the database server. Before the advent of JIT (Just-In-Time) compilers, type 2 drivers were popular because of their speed. Nowadays type 4 are the most

• popular. No need for transformations

via JNI so 1 layer less. The fastest solution and works on all platforms but provider specific so you need one per DBMS provider. On the

• ther hand most DBMS providers

provide type 4 JDBC drivers. Observe! that Java 1.6 and better require type 4 drivers

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 13 / 38

JDBC drivers and source code

• Remember that your choise of driver doesn’t require changes in the source

code.

• You specify which driver ti use and it works (if there are no errors in your

code).

• Errors do not depend on the driver.
• For the API the driver type doesn’t matter.
• It is possible to make programs that read driver info from the command line or

asks for it at application startup. Many commersial applications use a JDBC-ODBC bridge as default and allows for driver specification from the command line or in a configuration file. If there is a driver specification then that driver will be used instead of the default

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 14 / 38

Example database ER model

I depart from the sample database (the department store/warehouse) implemented in postgreSQL on nestor(nestor2.csc.kth.se).

item name salary manager floor dept department works_on volume supply supplier company address itemno type volume sales employee

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 15 / 38

Example database IRM model

supply works−on

employee

supplier department item sales

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 16 / 38

Open a connection

Two steps:

• 1. Create a connection. As you do it with textual parameters you get the feeling
• f losing control. At compilation time we don’t know if we are OK. Only at

execution time will we know. Two ways:

1.1 In most cases it’s simple. We execute

Class.forName("org.postgresql.Driver")

1.2 Some old drivers cannot register themselves in the DriverManager (good reason to dump them . . . ) and you might be forced to do it for them:

• rg.postgresql.Driver driver =

new org.postgresql.Driver(); DriverManager.registerDriver (driver);

but then your code will contain DBMS specific statements, which you would want to avoid.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 17 / 38

Open a connection . . .

• 2. When you have your connection object you do the actual connection:

Connection con = DriverManager.getConnection( "jdbc:postgresql://nestor2.csc.kth.se:5432/" + "warehouse", username, password);

URLs of this kind are on the form:

protocol:name:subname://server:portno/serverinstance

First part of the URL

"jdbc:postgresql://nestor2.csc.kth.se:5432/warehouse" is the

protocol (jdbc) followed by the name of the DBMS provider (postgresql). There is no subname here but if there was it would be the name of the driver if there is a choise (as is the case for Oracle where you choose driver based

• n what you want to do with the connection, thin/OCI/KPRB (for

applets/stand-alone apps/default) The server is the CSC computer nestor and the port number is the postgreSQL default.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 18 / 38

Open a connection . . .

• 2. (continued)

In UNIX / Linux you can allways find the default port numbers in

/etc/services with e.g. grep -i postgres /etc/services which will

give you

postgres 5432/tcp # POSTGRES postgres 5432/udp # POSTGRES

The UDP protocol is only good for streaming media so FTP is automatically the choise. User name and password will be your normal KTH login name and password. Simply write a piece of code to enter them from the command line or enter them in a form for www and encrypt via SSL. Never put them in the source code. Or write a protected configuration file that the application can read but is hidden for the users.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 19 / 38

Open a connection . . .

Do take care of all exceptions. Not in a general way but exception by exception. If not taken care of, regardless of language, it might confuse the database server side of the application and as most DBMS are professional tools they shut down for security reasons. Big problem with many courses at KTH!

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 20 / 38

Open a connection . . .

class dbAccess { private Connection conn; public dbAccess(String database){ try { Class.forName("org.postgresql.Driver"); usrInfo ui = getUserInfo(); this.conn = DriverManager.getConnection( "jdbc:postgresql://nestor2.csc.kth.se:5432/varuhuset", ui.name(), ui.passwd()); } catch (SQLException e) { e.printStackTrace(); System.exit(1); } catch (InstantiationException e) { e.printStackTrace(); System.exit(1); } catch (IllegalAccessException e) { e.printStackTrace(); System.exit(1); } catch (ClassNotFoundException e) { e.printStackTrace(); System.exit(1); } } }

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 21 / 38

Create a JDBC statement object

A JDBC statement object is used to send SQL statemnets to the DBMS but must not be confused with a SQL statement. THen JDBC statement object is associated with an open connection to the DBMS and not with a specific SQL statement. It is like an open channel to the DBMS and may be used to send more than one SQL Statement to the DBMS and also to command the DBMS to execute the SQLstatements. To create a JDBC statement object you need an active connection

Statement stmnt = conn.createStatement() ;

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 22 / 38

Create a JDBC PreparedStatement

Sometimes it is more efficient to use the Statement subclass PreparedStatement. The difference is that you instantiate a PreparedStatement with an SQL statement as parameter and the the SL statement is sent to the DBMS where it is precompiled. The advantage is that you may include wildcards to use the same statement many times with small variations. A Statement object is compiled every time while a PreparedStatement object is precompiled and optimized once

PreparedStatement objects are also created from an active connection, e.g. PreparedStatement prepUpdateStorage = con.prepareStatement( "UPDATE supply SET volume = ? WHERE dept = ? AND itemno = ?");

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 23 / 38

Create a JDBC PreparedStatement . . .

Before executing the statement object we must supply values for the three parameters, e.g.

prepUpdateStorage.setInt(1, 273); prepUpdateStorage.setString(2, "sports"); prepUpdateStorage.setInt(3, 153);

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 24 / 38

Executing CREATE/INSERT/UPDATE statement objects

You execute in different ways depending on the purpose of the statement and all statements that modifies something is considered to be an update. Thus all DDL statements use the method executeUpdate

Statement stmnt = conn.createStatement(); stmnt.executeUpdate("CREATE TABLE supply " + "(dept varchar(20), itemno smallint, company varchar(30)," + " volume integer, primary key (dept, itemno, company))" ); stmnt.executeUpdate("INSERT INTO supply " + "VALUES (276, ’sports’, 153)" ); String sqlString = "CREATE TABLE sales " + "(dept varchar(20), itemno smallint, volume integer," + " primary key (dept, itemno)" ; stmnt.executeUpdate(sqlString);

The return value is allways 0 (zero) when executing DDL statements.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 25 / 38

Execute CREATE/INSERT/UPDATE statements . . .

When executing data modifying statements the result is a number indicating how many rows were affected. If we prepare a statement as indicated above, we execute it with

int n = prepUpdateStorage.executeUpdate() ;

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 26 / 38

Execute SELECT statements

A DML statement is expected to send back a number of tuples (table rows) withour changing the database and then you use the method executeQuery which returns a reference to a ResultSet object.

String dept; int itemno, vol; ResultSet rs = stmnt.executeQuery("SELECT * FROM lager"); while ( rs.next() ) { dept = rs.getString("dept"); itemno = rs.getInt("itemno"); vol = rs.getInt("volume"); System.out.println(dept + " has " + vol + " of " + itemno + " in store."); }

Observe! that rs does not contain a set (there might be duplicates) and that its internal “pointer” does not point at the first item until after the first next() call!

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 27 / 38

Execute SELECT statements

You may also indicate the ordinal number for the column instead of column names, eg

dept = rs.getString(1); itemno = rs.getInt(2); vol = rs.getInt(4);

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 28 / 38

Some notes on ResultSet

In JDBC there are methods to navigate in the ResultSet and to find out where you are

getRow, isFirst, isBeforeFirst, isLast, isAfterLast

and to make scrollable cursors that allow random access to the ResultSet In the normal case you scroll only forwards but by sending parameter when creating the

ResultSet you may change its type an behaviour. Statement stmnt = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmnt.executeQuery("SELECT * FROM sales");

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 29 / 38

Some notes on ResultSet . . .

There are constants in the ResultSet class that can be used with the methods

createStatement(int resultSetType, int resultSetConcurrency)

and

createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)

to create cursors where you can navigate quite freely and/or isolate from other cursors and/or allow others to interfere and/or keep the cursor alive or kill it at commit. See the j2se documentation, and the Connection and ResultSet interfaces.

http://download.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 30 / 38

Some notes on ResultSet . . .

Then you can move to a specific row, move backwards and forwards. There are good tutorials on the Oracle Java site but remember that scrollable cursors have a large program overhead with quite substantial performance issues. Use with caution.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 31 / 38

Transactions

As a default each SQL statement constitutes a transaction. It’s OK for simple situation but to ensure the ACID (Atomicity, Consistency, Isolation, Durability) property in complex situations you need to take over control. This is done with the Connection object:

try { conn.setAutoCommit(false) ; stmnt.executeUpdate("CREATE TABLE sales (dept verchar(20)," + " itemno smallint, volume integer," + " primary key (dept, itemno))") ; stmnt.executeUpdate("INSERT INTO sales VALUES" + "(’sports’, 153, 327)") ; conn.commit() ; conn.setAutoCommit(true) ; } catch(SQLException ex) { System.err.println("SQLException: " + ex.getMessage()) ; conn.rollback() ; conn.setAutoCommit(true) ; }

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 32 / 38

Transactions . . . & warnings

I misspelled one attribute type in the table declaration. As there is no data type verchar, an exception will be generated.

SQLWarning is a subclass to SQLException and very rare but may occur primarily when

JDBC truncates data in the transfer to/from DBMS. They are linked to the ResultSet unless they can be directly attached to the actual statment. E.g.

ResultSet rs = stmnt.executeQuery("SELECT * FROM lager"); SQLWarning warning = stmnt.getWarnings(); if (warning != null) { System.out.println("\n---Warning---\n"); while (warning != null) { System.out.println("Message: " + warning.getMessage()); System.out.println("SQLState: " + warning.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warning.getErrorCode()); System.out.println(""); warning = warning.getNextWarning(); } }

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 33 / 38

Warnings . . .

SQLWarning warn = rs.getWarnings(); if (warn != null) { System.out.println("\n---Warning---\n"); while (warn != null) { System.out.println("Message: " + warn.getMessage()); System.out.println("SQLState: " + warn.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warn.getErrorCode()); System.out.println(""); warn = warn.getNextWarning(); } }

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 34 / 38

Common Gateway Interface (CGI)

CGI was the first method to introduce dynamic behaviour on the web and had to be descarded for security reasons. Today Perl is back and secure, and many other languages as well. Perl-DBDxxx and Perl-DBIxxx (lookup the possible substitutions for xxx) are a set

• r Perl libraries for DB-management.

All languges that allow SQL embedding may be considered today. Remember that the strength of Java is distribution. In many cases there are solutions with better performance.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 35 / 38

ORM (Object/Relational Mapping)

ORM is a class of wrappers that are used to increase the abstraction level when connecting an object oriented language to a RDBMS. You don’t use ibject orientation in the database but map specific classes in the application directly to tables in the database. Each call to the wrapper class generates a set of JDBC calls that together constitue a transaction. The purpose is to as much as possible free the programmer from coding for persistence. In simple applications they may be just cumbersome and hindersome but in complex applications they help a lot. You do introduce another layer . . . Hibernate www.hibernate.org is one such wrapper

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 36 / 38

Back-ends summary

A back-end is according to this course (there might be differences in defintions) an application or a program module that, through a thin API, allow storing and retrieving of data that an application needs to fullfill its purpose. The API ought to be designed in a way that only allows certain operations, blocks all other attempts to access it and checks as much as possible that permissible

• perations are correct.

A simple back-end may be implemented in JDBC (for Java), ODBC (for C/C++), Perl or the like, while full-fledged back-ends are simpler to program using some kind of DB-wrapper such as Hibernate, Hydrate, sql2java, ActiveRecord (ruby), PDO (Python), SQLObject (Python), Modeling (Python), or the like (see the course web pages)

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 37 / 38

Back-ends summary . . .

Some frameworks use their own DB interface, e.g. Spring. However Spring allows for ORM inclusion and is preconfigured to identify and integrate with iBatis SQL Maps, Hibernate, JDO, Apache OJB, Oracles Toplink . . . As many frameworks have their own interface to DBMS, the question is if there are reasons to use ORMs. And yes, sometimes:

• If the application often use a fetch-change-store cycle.
• If the application often fetch lots of data but change and store individually.
• Special cases of the above: fetch-a-lot-change-occasionally as in many web apps.
• If many classes in the application have a direct mapping onto tables in DB
• No specific optimization requirements (= allow the ORM to use its own as in

Hibernate) But . . . if you aggregate a lot or use extensive updating cycles then it may be better to use the simple method or a wrapper made for that purpose like e.g. iBatis SQL Maps. You may mix, but then be careful. E.g. Hibernate is a pain when mixing.

DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 38 / 38