Inserting Data into a Table

Now that we have discovered how to create a connection to a database from Java, let's look at how we can add data into a table in MySQL directly from a Java application. Before we go any further, let's add a table to our firsttest database in MySQL using the following SQL statement in the MySQL console client. Remember, you need to also execute the USE firsttest statement so that MySQL knows which database you want to work with. Here is the SQL statement that we require for this:

mysql> CREATE TABLE playerdata (     -> forename TEXT,     -> surname TEXT,     -> email TEXT);

This table will be able to hold a forename, surname, and an email address. Let's create a simple Java console application that will insert a single row of data into this table. Here is the complete code listing that we require to do this:

Code Listing 16-2: Inserting data into a table from Java

import java.sql.*;     public class DatabaseExample2 {     public static void main(String[] args)     {         try         {             Class.forName("org.gjt.mm.mysql.Driver");         }         catch(ClassNotFoundException e)         {             System.out.println(e);         }                                 try         {             Connection conn;                               System.out.println("Attempting to connect...\n");                           conn = DriverManager.getConnection("jdbc:mysql://                 localhost/firsttest?user=root&password=");                          System.out.println("Connected\n");                         System.out.println("Inserting 1 row of data...\n");                         Statement myStatement = conn.createStatement();                         myStatement.executeUpdate("INSERT INTO playerdata VALUES               ('Andrew', 'Mulholland', '[e-mail]')");                         System.out.println("Attempting to disconnect...\n");                         conn.close();                         System.out.println("Disconnected\n");         }         catch(SQLException e)         {             System.out.println(e);         }        } }

When we compile and execute this code, it will insert one row of data into our playerdata table in our firsttest database. If we go to the MySQL console client and select all the data in the table using this statement:

mysql> SELECT * FROM playerdata;

...we can see that the data has been entered successfully. Figure 16-3 is a screen shot of the MySQL console showing the data entered from Java:

Figure 16-3: This shows the data in MySQL, which has been inserted from our Java console application.

All we have added to this code since the first example is the following two lines:

Statement myStatement = conn.createStatement();             myStatement.executeUpdate("INSERT INTO playerdata VALUES ('Andrew',      'Mulholland', '[e-mail]')");

The first line of code creates a Statement object from our database connection, which is an object used to handle sending SQL statements to the database. The second simply executes the statement that we specify. Note that for this, we are using the executeUpdate method to execute the statement. This is the method that we use to alter data in tables, whereas we will see in the next section that we use the executeQuery method to retrieve data from tables.

Note 

It is possible to use the execute ("String statement") method to execute an SQL statement, which returns a Boolean value of true if a resultset was returned from the database (e.g., if we performed a SELECT statement) or false if the number of rows that were affected was returned (e.g., if we executed an UPDATE statement) or when no result is returned.

executeQuery() returns a ResultSet object containing the result data that we will see in the next example.

executeUpdate() returns an integer value telling the user how many rows were affected by the SQL statement.