Database+Programming

=DATABASE= A database consists of an organized collection of data for one or more multiple uses. One way of classifying databases involves the type of content, for example: bibliographic, full-text, numeric, image. Other classification methods start from examining database models or database architectures. Software organizes the data in a database according to a database model. As of 2010 the relational model occurs most commonly. Other models such as the hierarchical model and the network model use a more explicit representation of relationships.

Database management systems
A database management system (DBMS) consists of software that organizes the storage of data. A DBMS controls the creation, maintenance, and use of the database storage structures of social organizations and of their users. It allows organizations to place control of organization wide database development in the hands of Database Administrators (DBAs) and other specialists. In large systems, a DBMS allows users and other software to store and retrieve data in a structured way. Database management systems are usually categorized according to the database model that they support, such as the network, relational or object model. The model tends to determine the query languages that are available to access the database. One commonly used query language for the relational database is SQL, although SQL syntax and function can vary from one DBMS to another. A relational database management system (RDBMS) implements features of the relational model. In this context, Date's "Information Principle" states: "the entire information content of the database is represented in one and only one way. Namely as explicit values in column positions and rows in relations. Therefore, there are no explicit pointers between related tables." This contrasts with the object database management system (ODBMS), which does store explicit pointers between related types.

RDBMS components

 * SQL engine - This component interprets and executes the SQL query. It comprises three major components (compiler, optimizer, and execution engine).
 * Transaction engine - Transactions are sequences of operations that read or write database elements, which are grouped together.
 * Storage engine - This component stores and retrieves data records. It also provides a mechanism to store metadata and control information such as undo logs, redo logs, lock tables, etc.
 * Relational engine - Relational objects such as Table, Index, and Referential integrity constraints are implemented in this component.
 * Interface drivers - A user or application program initiates either schema modification or content modification. These drivers[which?] are built on top of SQL. They provide methods to prepare statements, execute statements, fetch results, etc. Examples include DDL, DCL, DML, ODBC, and JDBC. Some vendors provide language-specific proprietary interfaces. For example MySQL provides drivers for PHP, Python, etc.

Relational Databases
A database is really a collection of entities, or objects. These objects control how the data is stored and how it is managed. In a relational database the basic data storage entity is a table. Each entity will have a specific set of attributes (or properties) that define the sort of data that is stored in that entity. In the relational database we refer to these attributes as columns or fields. If we take our object analogy further, then each instance of our entity will have identical attributes but will be uniquely characterized by the values of these attributes. In the database, an instance of our object is analogous to a row, or record, in the table.


 * Field – A single attribute of information, represented as a column in a table.
 * Record – A set of attribute values, represented as a row in a table.
 * Table – A collection of fields and records of data.

JDBC
Java is designed to be platform-independent. A pure Java program written for a Windows machine will run without recompilation on a Solaris Sparc, an Apple Macintosh, or any platform with the appropriate Java virtual machine. JDBC extends this to databases. If you write a Java program with JDBC, given the appropriate database driver, that program will run against any database without having to recompile the Java code. Without JDBC, your Java code would need to run platform-specific native database code, thus violating the Java motto of Write Once, Run Anywhere. JDBC allows you to write Java code, and leave the platform (database) specific code to the driver. The reason that your Java JDBC code is portable between databases is because the database-specific code is contained in a Java class known as the driver. In the event you change databases, you simply change the driver used by your Java code and you are immediately ready to run against the new database. The JDBC API was designed to work with as wide a range of relational databases as possible. The JDBC API makes it very easy to write code to access relational databases by providing a whole range of Java classes and interfaces that encapsulate specific functionality. We can use:
 * A DriverManager class to load a driver
 * A Connection object (by which we mean an instance of a class that implements the Connection interface) to establish a connection to the database
 * A Statement object to encapsulate our SQL statements and send them to the database for execution
 * A ResultSet object in which to retrieve the results of our queries

Structured Query Language
SQL is a computer language for communication with databases. The communicating parties are typically a 'front end' which sends a SQL Statement across a connection to a 'backend' that holds the data. That statement contains instructions to create, read, modify, or delete data. Unlike procedural languages such as Java, SQL is a declarative language. This means that instead of specifying how to perform a particular task, you simply tell SQL what you want to achieve and let the DBMS decide the best way to go about it. SQL is a query language, which means it is designed to extract, organize, and update information in relational databases. Way back in the 1970s, when SQL was invented, SQL was supposed to be an English-like query language that could be used by untrained end users to access and update relational database data without the need for programming.

How Java Interacts with Databases
While there might exist a standard language for querying a relational database, these databases are anything but standard. Every relational database vendor provides its own API and a set of development tools for building applications with its database. These development tools provide an easy path to quickly building simple and fast database applications, but they are proprietary. Applications written with them often end with business rules and logic that cannot easily be moved to another database platform. What is needed here is a development language that can fulfill two basic development needs: These two requirements are easily filled by the Java programming language. Java is platform-independent and provides a common database Application Programming Interface called JDBC. JDBC allows the developer to write applications using a common API that is database-independent. The software developer writes to the API and not the underlying database.
 * Build database applications irrespective of the hardware platform the database is running on. Developers need to be able to write database applications that can be run on any hardware platform. (Intel, Sun, AS/400, etc.)
 * Give the developer the ability to build applications that will run on a variety of database platforms. A developer should be able to write their application once and run it against another database with very little reworking.

Connecting to the Database
To connect to a database, you need an object of the class Connection. The following shows you how to obtain such a connection. With older versions of the JDBC standard, you first need to manually load the database driver class. Starting with JDBC4 (which is a part of Java 6), the driver is loaded automatically. code format="java" String driver = ...; Class.forName(driver); // Load driver

//Next, you ask the DriverManager for a connection. //You need to initialize the url, username, and password strings with the values that apply to your database. String url = ...; String username = ...; String password = ...; Class.forName(driver); // Load driver Connection conn = DriverManager.getConnection(url,username, password);

//When you are done issuing your database commands, //close the database connection by calling the close method: conn.close; code

This is actually a very simple way of connection management. Two problems occur in practice. Larger programs need to connect to the database from many classes. You don't want to propagate the database login information to a large number of classes. Also, it is usually not feasible to use a single connection for all database requests. A container for Java server pages can run many simultaneous web page requests from different browsers. Each page request needs its own database connection. But because opening a database connection is quite slow and page requests come so frequently, database connections need to be pooled rather than closed and reopened. The details can be complex, and there is currently no standard implementation available.It is always a good idea to decouple connection management from the other database code.

Executing SQL Statements
Once you have a connection, you can use it to create Statement objects. You need Statement objects to execute SQL statements. code format="java" Statement stat = conn.createStatement; code

The execute method of the Statement class executes a SQL statement. For example, code format="java" stat.execute("CREATE TABLE Test (Name CHAR(20))"); stat.execute("INSERT INTO Test VALUES ('Rampage')"); code

To issue a query, use the executeQuery method of the Statement class. The query result is returned as a ResultSet object. For example code format="java" String query = "SELECT * FROM Test"; ResultSet result = stat.executeQuery(query); code

For UPDATE statements, you can use the executeUpdate method. It returns the number of rows affected by the statement: code format="java" String command = "UPDATE LineItem" + " SET Quantity = Quantity + 3" + " WHERE Invoice_Number = '587412'"; int count = stat.executeUpdate(command); code

If your statement has variable parts, then you should use a PreparedStatement instead. code format="java" String query = "SELECT * WHERE Account_Number = ?"; PreparedStatement stat = conn.prepareStatement(query); code

The ? symbols in the query string denote variables that you fill in when you make an actual query. You call a set method for that purpose, for example code format="java" stat.setString(3, accountNumber); code

The first parameter of the set methods denotes the variable position: 1 is the first ?, 2 the second, and so on. There are also methods setInt and setDouble for setting numerical variables. After you set all variables, you call executeQuery or executeUpdate. Finally, you can use the generic execute method to execute arbitrary SQL statements. It returns a boolean value to indicate whether the SQL command yields a result set. If so, you can obtain it with the getResultSet method. Otherwise, you can get the update count with the getUpdateCount method.

code format="java" String command = ...; boolean hasResultSet = stat.execute(command); if (hasResultSet) {     ResultSet result = stat.getResultSet; ...   }    else {      int count = stat.getUpdateCount; ...   } code

You can reuse a Statement or PreparedStatement object to execute as many SQL commands as you like. However, for each statement, you should only have one active ResultSet. If your program needs to look at several result sets at the same time, then you need to create multiple Statement objects. When you are done using a ResultSet, you should close it before issuing a new query on the same statement. code format="java" result.close; code

When you are done with a Statement object, you should close it. That automatically closes the associated result set. code format="java" stat.close; code When you close a connection, it automatically closes all statements and result sets.

//SQL Database Sample code//
In the example below we will create a database called cars, that we will use later to read from it using Java. code format="sql" drop database cars; create database cars; use cars; create table car (   id int not null auto_increment,    model varchar(50),    year int,    price decimal(8,2),    primary key(id) ); insert into car (model, year, price) values ("Ford Taurus", 1992, 2589.00); insert into car (model, year, price) values ("Toyota Corolla", 1998, 3954.00); insert into car (model, year, price) values ("Honda Accord", 2005, 8254.00); insert into car (model, year, price) values ("Ford F150",2003, 5890.00); insert into car (model, year, price) values ("Ford Mustang", 1977, 9580.00); insert into car (model, year, price) values ("Subaru Forrester", 2006, 8215.00); insert into car (model, year, price) values ("Jeep Wrangler", 2002, 12589.00); insert into car (model, year, price) values ("Dodge Neon", 2002, 600.00); insert into car (model, year, price) values ("Mitsubishi Eclipse", 1997, 800.00); insert into car (model, year, price) values ("Toyota Camry", 2006, 14580.00); code

After the code above has been entered into the database, we can check the result by looking into to see if the table was created correctly. First we have to start the MySQL command prompt.

Then after it starts, we have to enter all the commands from the prvious database code to create the database and the table, this part was done already, so from this point, we will show if the table was created properly. To show the databases that we have already created, type the following command: code format="sql" SHOW DATABASES; code Then since we are interested on the "Cars" Database, we select that by typing the following command: code format="sql" USE cars; code Then we can see how many tables we have inside this database, in our case we have only one table, named car: code format="sql" SHOW TABLES; code To see how the table car is, do the following: code format="sql" DESCRIBE CAR; code To see the entire table, type the following command: code format="sql" SELECT * FROM CAR; code To do something fun, we can organize the type by only showing the model and the year, and organize that in ascending order of year: code format="sql" SELECT MODEL, YEAR FROM car ORDER BY year; code

//Java code that will read from the Database Cars//
code format="java" import java.sql.*; import java.text.NumberFormat;

public class ListCars {   public static void main(String[] args) {       NumberFormat cf = NumberFormat.getCurrencyInstance;

ResultSet cars = getCars; try {           while (cars.next) {               Car c = getCar(cars); String msg = Integer.toString(c.year); msg += ": " + c.model; msg += " (" + cf.format(c.price) + ")"; System.out.println(msg); }       }        catch (SQLException e)        { System.out.println(e.getMessage); }   }

private static ResultSet getCars {       Connection con = getConnection; try {           Statement s = con.createStatement; String select = "Select model, year, price " + "from car rder by year"; ResultSet rows; rows = s.executeQuery(select); return rows; }       catch (SQLException e)        { System.out.println(e.getMessage); }       return null; }

private static Connection getConnection {       Connection con = null; try {           Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost/Cars"; String user = "root"; String pw = "jodon"; con = DriverManager.getConnection(url, user, pw); }       catch (ClassNotFoundException e)        { System.out.println(e.getMessage); System.exit(0); }       catch (SQLException e)        { System.out.println(e.getMessage); System.exit(0); }       return con; }

private static Car getCar(ResultSet cars) {       try {           String model = cars.getString("Model"); int year = cars.getInt("Year"); double price = cars.getDouble("Price"); return new Car(model, year, price); }       catch (SQLException e)        { System.out.println(e.getMessage); }       return null; }

private static class Car {       public String model; public int year; public double price;

public Car(String model, int year, double price) {           this.model = model; this.year = year; this.price = price; }   } } code
 * The main method begins by calling the getCars method to get a ResultSet object that contains the cars to be listed. Then, a while loop reads each row of the result set. The getCar method is called to create a Car object from the data in the current row. Then, an output string is created and sent to the console. The loop is contained in a try/catch statement because the next method may throw SQLException.
 * The getCars method is responsible for getting a database connection,and then querying the database to get the cars. The first task is delegated to the getConnection method. Then, a Statement is created and executed with the following select statement: select model, year, price from car order by year. Then, the result set is returned to the main method.
 * The getConnection method creates a Connection object to the database.
 * The getCar method extracts the model, year, and price from the current row and uses these values to create a Car object.
 * The Car class is created as an inner class.

Database Programming Wrap Up
Java is a portable and very powerful object-oriented programming language. Relational database management systems allow you to store all of the data required by your Java applications in an efficient and well-defined manner. In addition, they provide means to write business logic and store it directly on the database server, can enforce the transactional integrity, and provide in-built mechanisms for the support of multiple users.

JavaBean **DATABASE**  Swing


 * Chapters **