With this example we are going to demonstrate how to insert a row into a table. We can insert rows to a table with the Statement or the PreparedStatement API, according to the frequency of inserted rows. In short, to insert a row into a table you should:
- Load the JDBC driver, using the
forName(String className)API method of the Class. In this example we use the MySQL JDBC driver. - Create a Connection to the database. Invoke the
getConnection(String url, String user, String password)API method of the DriverManager to create the connection. - For inserts that are not executed frequently use the Statement API. Create a Statement, using the
createStatement()API method of the Connection. Execute the query to the database, using theexecuteUpdate(String sql)API method. It returns the count of inserted rows. - For inserts that are executed frequently use the PreparedStatement API. Create a PreparedStatement, using the
prepareStatement(String sql)API method of the Connection. For each one of the rows to be inserted invoke thesetString(int parameterIndex, String x)API method to insert a value to each row, and then invoke theexecuteUpdate()API method to execute the insert.
Let’s take a look at the code snippet that follows:
package com.javacodegeeks.snippets.core;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertRowsExample {
public static void main(String[] args) {
Connection connection = null;
try {
// Load the MySQL JDBC driver
String driverName = "com.mysql.jdbc.Driver";
Class.forName(driverName);
// Create a connection to the database
String serverName = "localhost";
String schema = "test";
String url = "jdbc:mysql://" + serverName + "/" + schema;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
System.out.println("Successfully Connected to the database!");
} catch (ClassNotFoundException e) {
System.out.println("Could not find the database driver " + e.getMessage());
} catch (SQLException e) {
System.out.println("Could not connect to the database " + e.getMessage());
}
try {
/*
* For inserts that are not executed frequently we should use the statement API.
* insertCount contains the number of inserted rows (should be equal to 1)
*/
Statement statement = connection.createStatement();
int insertCount = statement.executeUpdate("INSERT INTO test_table (test_col) VALUES('test_value')");
System.out.println("Inserted test_value successfully : " + insertCount );
/*
* For inserts that are executed frequently we should
* use the prepared statement API.
*/
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table (test_col) VALUES(?)");
// Insert 10 rows
for (int i=0; i<10; i++) {
preparedStatement.setString(1, "test_value_"+i);
// insertCount contains the number of inserted rows (should be equal to 1)
insertCount = preparedStatement.executeUpdate();
System.out.println("Inserted test_value_" + i +" successfully : " + insertCount );
}
} catch (SQLException e) {
System.out.println("Could not insert data to the database " + e.getMessage());
}
}
}
Example Output:
Successfully Connected to the database!
Inserted test_value successfully : 1
Inserted test_value_0 successfully : 1
Inserted test_value_1 successfully : 1
Inserted test_value_2 successfully : 1
Inserted test_value_3 successfully : 1
Inserted test_value_4 successfully : 1
Inserted test_value_5 successfully : 1
Inserted test_value_6 successfully : 1
Inserted test_value_7 successfully : 1
Inserted test_value_8 successfully : 1
Inserted test_value_9 successfully : 1
This was an example of how to insert a row into a table in Java.
