MrBool
You must be logged in to give feedback. Click here to login
[Close]

You must be logged to download.

Click here to login

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Using JDBC to Perform Data Manipulation in Java

See in this article how to manipulate data with JDBC, we will see how to access and connect to the database, insert, edit and delete data in the database.

[close]

You didn't like the quality of this content?

Would you like to comment what you didn't like?

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.

Accessing Data with JDBC

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.

Schematic of operation of the JDBC

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.

How to load the JDBC driver

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?

Deleting all records from the table

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.

Inserting records with JDBC

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) {  
    } 

Updating records

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) {  
    }

Conclusion

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.



Front-end developer and WebEditor of MrBool.com. I work in the web design area for over 4 years. For freelance work visit my portfolio: www.ricardoarrigoni.com.br or if you prefer send me an email.

What did you think of this post?

Did you like the post?

Help us to keep publishing good contents like this.

SUPPORT US

funded

remaining

[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually � in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits � in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login