SQL Database access from Java

In its wider sense, a database is a piece of software or library designed to store and manipulate data. But usually– and this is the sense here– we refer specifically to software such as MySQL, Microsoft SQL Server, Oracle etc. These systems, sometimes called SQL Databases, organise data into tables of records or "rows" containing common fields or "columns", and allow commands and queries to be run in a language called SQL or Structured Query Language. SQL queries are run against the database by connecting to it over a network socket. Therefore, the database can be installed on a different machine to the one issuing the queries (though it can also be installed on the same machine).

Java provides a framework called the Java Database Connection or JDBC framework. JDBC provides a number of classes for encapsulating common objects and functions of managing a database: SQL queries, rows from the table, metadata such as the list of tables in the database, or the names and types of fields on a particular table. JDBC also provides a framework for database drivers: the component that communicates with the database in its native format (i.e. knows what actual bytes to send and receive over the socket in order to handle a particular query). Before using JDBC, you generally need to obtain the driver for the specific database you are using (MySQL, SQL Server, Oracle etc). The driver is generally provided in the form of a jar which must be included in the classpath when you run your program (and/or added to your project if using an IDE).

If you are running a servlet on a standard shared hosting plan that also provides database access, then it is quite likely that your hosting provider will already have provided the appropriate JDBC driver ready installed. (And if you are choosing a hosting company for the first time, you may want to consider one that provides JDBC data access ready set up and has clear documentation on how to use it.)

The following steps and topics are generally involved in connecting to a SQL database using JDBC in Java:

• connecting with JDBC: which implies loading the driver for your database included in the jar (as mentioned above) and then using a special URL to open a connection;
• creating a Statement object and executing SQL on it;
• reading the result set returned from executing the SQL statement and taking into account mappings between SQL and Java data types;
• where possible, using prepared statements, which provide security and performance gains;
• closing resources such as statements and result sets at the appropriate point.

---