Developer's Daily Java Education
  front page | java | perl | unix | DevDirectory
   
Front Page
Java
Education
Pure Java
Articles
   
 
JDBC 102: Inserting data into an SQL table
 

Introduction

In our first article we demonstrated how to connect your Java applets and applications to standard SQL databases like Oracle, Informix, Sybase, and others using the JDBC Connection object.  In our examples we showed how to connect to two different databases --  Mini SQL (mSQL), and Interbase -- so you could see how the code changes when you switch from one database to another.

In this article we'll take the next step -- we'll show you how to insert data into a data table using Java, JDBC, and SQL.
 

Our sample database

Before getting into our SQL statements, you need to know what our database table should look like. In all of our examples in this series, we're going to be working with a database named Demo.  In today's example we're going to populate a database table named Customers. Customers is a table contained within the overall Demo database.

Here's what the Customers database table will look like when we're done:
 
Cnum
Lname
Salutation
City
Snum
1001 Simpson Mr. Springfield 2001
1002 McBeal Ms. Boston 2004
1003 Flinstone Mr. Bedrock 2003
1004 Cramden Mr. New York 2001
 
Table 1: Our sample Customers database table will contain these four sample records. 
   
 
How to create an SQL INSERT statement

Today we'll show you that inserting data into an SQL database table is a simple two-step process:

    1. Create a Statement object.
    2. Execute the SQL INSERT command through the Statement object.
If you're comfortable with SQL, this is a very easy process. When Sun/JavaSoft created JDBC, they intended to "make the simple things simple".


 
Here's the code

Our SQL INSERT statements are also pretty simple, especially if you're comfortable with SQL.  Here's an example of how we create our Statement object, and then insert a record for a person named Mr. Simpson, of a town named Springfield:

As you can see, you just (1) create a Statement object, and (2) run your SQL INSERT statement using the Statement object's executeUpdate() method.

If you're not familiar with SQL, note that you must insert your fields in the order in which your table is defined (Cnum, Lname, Salutation, City, and Snum). (Snum stands for Salesperson Number, which we'll use later to link this table to our Salesperson table.)

Inserting the other three records is just as easy as inserting this record. We can just re-use the Statement object, and use our new values:

As you can see, this is pretty easy (once you've seen how it's done). In a real application you'll just replace the string constants we've used with variables that you obtain from (a) an end-user or (b) an input data source.

(Note: In this example, we've assumed that the database table named Customers is already created. You can create your database tables through your database management tools. For instance, we created our sample Customers tables using msqladmin for Mini SQL (mSQL), and through Interbase Server Manager tools under Windows95 for our Interbase server. You can also create your tables through JDBC code, which we'll tackle in a later tutorial.)
 
 
The Insert1.java program

We named our completed application Insert1.java. To help you understand the entire chain of events that are necessary, the full source code for the application is shown in Listing 1.
 

    // Insert1.java:  Demonstrates how to INSERT data into an SQL
    //               database using Java JDBC.
    //
    // Copyright 1998 Developer's Daily (http://www.DevDaily.com). All rights reserved.

    import java.sql.*;

    class Insert1 {
     
        public static void main (String[] args) {
            try {
                String url = "jdbc:msql://200.210.220.1:1114/Demo";
                Connection conn = DriverManager.getConnection(url,"","");
                Statement st = conn.createStatement();
                st.executeUpdate("INSERT INTO Customers " +
                    "VALUES (1001, 'Simpson', 'Mr.', 'Springfield', 2001)");
                st.executeUpdate("INSERT INTO Customers " +
                    "VALUES (1002, 'McBeal', 'Ms.', 'Boston', 2004)");
                st.executeUpdate("INSERT INTO Customers " +
                    "VALUES (1003, 'Flinstone', 'Mr.', 'Bedrock', 2003)");
                st.executeUpdate("INSERT INTO Customers " +
                    "VALUES (1004, 'Cramden', 'Mr.', 'New York', 2001)");

                conn.close();
            } catch (Exception e) {
                System.err.println("Got an exception! ");
                System.err.println(e.getMessage());
            }
     
        }

    }
     
 
Listing 1: The source code for the Insert1.java program shows how to INSERT data into an SQL database using Java and JDBC.
   
 



Download the source code

If you're interested, you can click here to download the source code for the Insert1.java program.  You can test the code on your own system, but note that you'll need to change the lines where we create our url and conn objects to reflect your own database configuration.
 

Conclusion

Inserting data into an SQL database table with JDBC is a simple two-step process. Just (1) create a Statement object, and (2) use the object to run your normal SQL INSERT commands.
 

Resources mentioned in this article

Here are a few links to resources we mentioned in this article:

 

What's Related


Copyright 1998-2008 DevDaily Interactive, Inc.
All Rights Reserved.