0
3
Hello guys, in this article we will learn how to work with Java.
Java is one of the most powerful development tools in the world, gaining more and more followers and fans.
Without a doubt the data access is one of the most used features in development. Practically the whole system needs a database, with rare exceptions.
Java, different than languages like PHP does not support access to the database directly, so that it uses an API (group of classes and interfaces) to do the job. The JDBC (Java Database Connectivity) make the sending of SQL statements to any relational database, provided there is a driver that matches the same gift.
Figure 1: Schematic of operation of the JDBC
There are four types of JDBC drivers: 1, 2, 3 and 4, they are:
Type 1: JDBC-ODBC Bridge
It is the simplest type but restricted to the Windows platform. Uses ODBC to connect to the database, converting methods in JDBC calls to ODBC functions. This bridge is typically used when there is a pure-Java driver (type 4) for a given database, because its use is discouraged due to the dependence of the platform.
Type 2: Native-API Driver
The Native-API driver translates JDBC calls to the API calls customer database used. As the JDBC-ODBC Bridge, may need extra software installed on the client machine.
Type 3: Driver Network Protocol
Translates the JDBC call to a network protocol independent of the database used, which is translated into the protocol database for a server. By using an independent protocol, clients can connect Java applications to several different databases. It's more flexible model.
Type 4: Native Driver
Converts JDBC calls directly into the protocol database. Implemented in Java, normally is independent platform and written by the developers themselves. It is the most recommended to be used.
In this article we use the type 4 being the most recommended. Because he takes calls directly in the protocol of the database in question, thus giving better performance, besides, of course, being the simplest to use.
Many may find a certain similarity between JDBC and ODBC, and are absolutely correct, we can say "roughly" the two follow the same idea. Both work as a communication application x Bank, but ODBC is a Windows application restricted to it, while the JDBC because it is written in java, is multiplatform.
Another advantage of JDBC is that it act as a data abstraction layer. Independent of SGBD used, the API is the same, greatly facilitating the life of programmers if there is a need for a database migration.
Now we will see how to connect through JDBC in a Oracle database.
Listing 1: Connecting to an Oracle database
Connection connection = null;
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
// Create a connection to the database
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}
The code is well standardized, we put the server ip, port number, database name, username and password, and put the driver connection.
Now we will see how to access a database in MySQL.
Listing 2: Accessing a MySQL database
Connection connection = null;
try {
// Load the JDBC driver
String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
Class.forName(driverName);
// Create a connection to the database
String serverName = "localhost";
String mydatabase = "mydatabase";
String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// Could not find the database driver
} catch (SQLException e) {
// Could not connect to the database
}
Note that the difference of the code in Listing 1 to 2 is just the name of the driver that changes jdbc:oracle for jdbc:mysql and its application too.
Now we will see how to load a JDBC driver, in this example we will do load the MySQL driver.
Listing 3: Load the JDBC driver
try {
// Load the JDBC driver
String driverName = "org.gjt.mm.mysql.Driver";
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// Could not find the driver
}
Let us understand better now what each command means.
Class.forName - This is without doubt the main command. It is through him that we are calling the JDBC driver.
Connection - Here we are creating an object of type "Connection". This is where the information is stored in your database connection. To be more direct, we use the method "getConnection" of the object "DriverManager" contained in "java.sql" instead of the default constructor, this causes the connection is established immediately. Note the string passed as parameter, the information contained in it are our connection, they are respectively: JDBC driver, host, database path, username and password for last.
Statement - a statement is simple, but vital for any project. The object "Statement" is responsible for receiving commands and SQL to relay information, as well as the return.
Now that we know how to create the connection and loading the driver, what do you think of seeing some basic operations?
We will see now how to delete all records of a table. Be very careful with this code because it will clear all records of your table.
Listing 4: Deleting all records with JDBC
try {
Statement stmt = connection.createStatement();
// Use TRUNCATE
String sql = "TRUNCATE my_table";
// Use DELETE
sql = "DELETE FROM my_table";
// Execute deletion
stmt.executeUpdate(sql);
} catch (SQLException e) {
}
As we know delete all records is very dangerous and little used, the next listing we will see how to delete only one record.
Listing 5: Excluding only one record with JDBC
try {
// Create a statement
Statement stmt = connection.createStatement();
// Prepare a statement to insert a record
String sql = "DELETE FROM my_table WHERE col_string='a string'";
// Execute the delete statement
int deleteCount = stmt.executeUpdate(sql);
// deleteCount contains the number of deleted rows
// Use a prepared statement to delete
// Prepare a statement to delete a record
sql = "DELETE FROM my_table WHERE col_string=?";
PreparedStatement pstmt = connection.prepareStatement(sql);
// Set the value
pstmt.setString(1, "a string");
deleteCount = pstmt.executeUpdate();
System.err.println(e.getMessage());
} catch (SQLException e) {
}
Thus we can only delete one record, one that we passed as parameter.
If we want to insert a record into a database table is very simple, just insert the following code.
Listing 6: Inserting data into the database
try {
Statement stmt = connection.createStatement();
// Prepare a statement to insert a record
String sql = "INSERT INTO my_table (col_string) VALUES('a string')";
// Execute the insert statement
stmt.executeUpdate(sql);
} catch (SQLException e) {
}
If you want to edit or update a table record is also very simple, just we use the code in Listing 7.
Listing 7: Changing records
try {
Statement stmt = connection.createStatement();
// Prepare a statement to update a record
String sql = "UPDATE my_table SET col_string='a new string' WHERE col_string = 'a string'";
// Execute the insert statement
int updateCount = stmt.executeUpdate(sql);
// updateCount contains the number of updated rows
} catch (SQLException e) {
}
I believe that any Java web developer before you even know any framework like Hibernate, Struts, JSF, etc, should know first the JDBC. This is the starting point for developing Java web.
I hope you enjoyed the article and until next time.
See the prices for this post in Mr.Bool Credits System below: