Access to Database using Java

Question

How do I access a database (I’m using Postgresql) through Java? I’ve seen a way that uses objects like Connection and PreparedStatement. [link]

Can someone explain to me about these objects or give me a better way to access the bank? Even the part of the connection between the code and the bank I’ve done, but the rest is hard to learn, since there’s not much of that on the Internet.

In my case, as I am creating a small application, I would like the simplest way to edit tables in the database, with something similar to what I saw in the tutorial.

Answer 1

This is a common question for those who start with Java.

JDBC

First, understand that there is an API called JDBC which encompasses everything needed to connect to a database in Java.

With rare exceptions, this pattern is used to connect to all databases from a Java application. Even higher-level technologies with JPA and other Orms use JDBC as a base.

However, because it is very generic, JDBC is also difficult to understand and confusing at first.

Drivers

JDBC is just an API, that is, a set of interfaces and standards.

To connect effectively, each Database must implement a Driver that manages its specific communication protocol.

Some simpler drivers use ODBC technology, which is a generic communication standard. These drivers are slower and feature fewer optimization features.

Other drivers even implement the native protocol of the Database, allowing full access and maximum optimization.

See more about driver types here.

Connection

Once you have a database and the JDBC Driver, you can then use the JDBC API to establish a connection with the Database.

One of the ways to connect directly to the database is by using the method DriverManager.getConnection() and informing the URL and the access data.

I remember that URL means Universal Resource Identifier, not to be confused with the address of a web page.

If all goes well, the above method will return an object Connection, that represents the connection of the program with the Bank.

Connection Pool

Desktop apps usually open a connection to the Bank and use it until the program ends.

Web applications serve tens, hundreds, thousands of users and cannot afford to open and close connections at all times. In this case a connection pool is used, which consists of keeping a certain number of connections open and reusing them to suit the various users.

JNDI

When you distribute your Java application for third-party use on Application Servers such as Tomcat, Websphere or Weblogic, it is not a good idea to leave the database user and password in configuration files or hard coded in the code.

Who should configure the connection with the bank is the environment administrator and not the programmer.

To solve this problem, the JNDI API is used to provide connections to the database. JNDI is basically an index where you place shared objects that can be accessed by a name or String.

So, what application servers do is take the connections configured by administrators and make available to applications that are installed in the JNDI directory.

When your application is installed on the server, it can access the connection by a name.

Datasources

Another commonly used API to avoid passing user and password to the entire program.

A DataSource is an object Factory that is able to create a connection with your method getConnection.

The idea is that a program only place create a DataSource and in the other classes only the object is used to obtain the connections.

A great advantage of using DataSources is that you can use the pattern of projects Decorator to intercept the creation of connections. This allows you to do from performance analysis, connection caching and even check that the code is not closing connections properly.

Statement

Once you have the connection, you’ll want to run commands, right? Because that’s exactly what they are statements.

Statement is an interface representing a command you will run in the database, such as a UPDATE, INSERT, DELETE, SELECT and others.

PreparedStatement allows you to create commands whose parameters are sent separately. This improves performance if the query is executed several times and also makes the passing of parameters safe, avoiding SQL Injection attacks caused by concatenation of parameters in queries.

CallableStatament lets execute stored procedures.

Architecture

All this explains how to handle connections. Each application needs to understand how to optimize this for best performance.

There is no right way for all cases. You will have to analyze how your program accesses the data and how often.