Java jdbc insert sql

Java Guides

In the previous tutorial, we have seen how to create a table in a PostgreSQL database using Java. In this tutorial, you will learn how to insert data into a table in the PostgreSQL database using the JDBC API.

In this example, we will see how to insert a single row as well as multiple rows into the PostgreSQL database.

Technologies used

Download PostgreSQL JDBC Driver

To connect to the PostgreSQL database server from a Java program, you need to have a PostgreSQL JDBC driver. You can download the latest version of the driver on the postgresql.org website via the download page.

 https://mvnrepository.com/artifact/org.postgresql/postgresql --> dependency> groupId>org.postgresqlgroupId> artifactId>postgresqlartifactId> version>42.2.9version> dependency>

// https://mvnrepository.com/artifact/org.postgresql/postgresql compile group: 'org.postgresql', name: 'postgresql', version: '42.2.9'

PostgreSQL Database Setup

Inserting one row into a table

• Establish a database connection to get a Connection object.
• Create a Statement object from the Connection object.
• Execute the INSERT statement.
• Close the database connection.

Here is the complete Java program to insert a single row into the «users» table in the PostgreSQL database:

package net.javaguides.postgresql.tutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Insert PrepareStatement JDBC Example * * @author Ramesh Fadatare * */ public class InsertRecordExample < private final String url = "jdbc:postgresql://localhost/myDB"; private final String user = "postgres"; private final String password = "root"; private static final String INSERT_USERS_SQL = "INSERT INTO users" + " (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);"; public static void main(String[] argv) throws SQLException < InsertRecordExample createTableExample = new InsertRecordExample(); createTableExample.insertRecord(); > public void insertRecord() throws SQLException < System.out.println(INSERT_USERS_SQL); // Step 1: Establishing a Connection try (Connection connection = DriverManager.getConnection(url, user, password); // Step 2:Create a statement using connection object PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) < preparedStatement.setInt(1, 1); preparedStatement.setString(2, "Tony"); preparedStatement.setString(3, "tony@gmail.com"); preparedStatement.setString(4, "US"); preparedStatement.setString(5, "secret"); System.out.println(preparedStatement); // Step 3: Execute the query or update query preparedStatement.executeUpdate(); > catch (SQLException e) < // print SQL exception information printSQLException(e); > // Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

Inserting multiple rows into a table

• Create a database connection.
• Create a PreparedStatement object.
• Call the addBatch() method of the PreparedStatement object.
• Call the executeBatch() method to submit a batch of the INSERT statements to the PostgreSQL database server for execution.
• Close the database connection.

Here is the complete Java program to insert multiple rows into the «users» table in the PostgreSQL database:

package net.javaguides.postgresql.tutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Arrays; import java.util.List; public class InsertMultipleRecordsExample < private final String url = "jdbc:postgresql://localhost/myDB"; private final String user = "postgres"; private final String password = "root"; private static final String INSERT_USERS_SQL = "INSERT INTO users" + " (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);"; /** * insert multiple users */ public void insertUsers(List < User > list) < try ( Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement statement = conn.prepareStatement(INSERT_USERS_SQL);) < int count = 0; for (User user: list) < statement.setInt(1, user.getId()); statement.setString(2, user.getName()); statement.setString(3, user.getEmail()); statement.setString(4, user.getCountry()); statement.setString(5, user.getPassword()); statement.addBatch(); count++; // execute every 100 rows or less if (count % 100 == 0 || count == list.size()) < statement.executeBatch(); > > > catch (SQLException ex) < System.out.println(ex.getMessage()); > > public static void main(String[] args) < InsertMultipleRecordsExample example = new InsertMultipleRecordsExample(); example.insertUsers(Arrays.asList(new User(2, "Ramesh", "ramesh@gmail.com", "India", "password123"), new User(3, "John", "john@gmail.com", "US", "password123"))); > >

In this tutorial, we have shown you how to insert one or multiple rows into the PostgreSQL database using the JDBC API.

Источник

Java JDBC Insert Example: How to insert data into a SQL table

In my first Java JDBC tutorial (How to connect to a JDBC database) I demonstrated how to connect your Java applications to standard SQL databases like MySQL, SQL Server, Oracle, SQLite, and others using the JDBC Connection object.

In this article I’ll take the next step and show how to insert data into a database table using Java, JDBC, and SQL.

Step 1: A sample database

Before getting into the SQL INSERT statements, you need to know what the sample database table looks like. In all of my examples in this series, I’ll be working with a database named Demo that has a database table named Customers .

Here’s what the Customers database table looks like:

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

How to create a JDBC INSERT statement

Inserting data into a SQL database table using Java is a simple two-step process:

1. Create a Java Statement object.
2. Execute a SQL INSERT command through the JDBC Statement object.

If you’re comfortable with SQL, this is a simple process. When Sun (now Oracle) created JDBC, they intended to “make the simple things simple.”

Step 2: Execute the JDBC INSERT statement

Here’s an example of how to create a Java Statement object, and then insert a record for a person named Mr. Simpson, of a town named Springfield:

// create a Statement from the connection Statement statement = conn.createStatement(); // insert the data statement.executeUpdate("INSERT INTO Customers " + "VALUES (1001, 'Simpson', 'Mr.', 'Springfield', 2001)");

As this shows, you (1) create a JDBC Statement object from your Connection instance, and (2) run your SQL INSERT statement using the Statement object’s executeUpdate method. (I show the complete process of obtaining a database Connection object below.)

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 to insert our new values:

statement.executeUpdate("INSERT INTO Customers " + "VALUES (1002, 'McBeal', 'Ms.', 'Boston', 2004)"); statement.executeUpdate("INSERT INTO Customers " + "VALUES (1003, 'Flinstone', 'Mr.', 'Bedrock', 2003)"); statement.executeUpdate("INSERT INTO Customers " + "VALUES (1004, 'Cramden', 'Mr.', 'New York', 2001)");

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, I assumed that the database table named Customers is already created. You can create your database tables through your database management tools.

The JdbcInsert1.java program

To help you understand how this process works, the following source code shows a complete Java program that creates a Connection to the database, and then inserts the data as shown previously:

import java.sql.*; /** * JdbcInsert1.java - Demonstrates how to INSERT data into an SQL * database using Java JDBC. */ class JdbcInsert1 < 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()); >> >

If you’re interested, you can download the Java source code for the JdbcInsert1.java program here. You can test the code on your own system, but note that you’ll need to change the lines where I create my url and conn objects to reflect your own database configuration. You’ll also need the appropriate JDBC driver for the database you’re using.

Related Java JDBC content

When it comes to inserting data into a database, most developers prefer to use a PreparedStatement instead of a Statement , as the PreparedStatement is more secure. (But when you’re first learning JDBC it’s helpful to see the Statement first.) Please see these articles for more information on how to use a PreparedStatement :

Summary

In summary, 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.

Источник

Java jdbc insert sql

Для добавления, редактирования и удаления данных мы можем ипользовать рассмотренный в прошлой теме метод executeUpdate . С помощью результата метода мы можем проконтроллировать, сколько строк было добавлено, изменено или удалено.

Добавление

Так, возьмем созданную в прошлой теме таблицу Products:

CREATE TABLE Products ( Id INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(20), Price INT )

И добавим в эту таблицу несколько объектов:

import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); int rows = statement.executeUpdate("INSERT Products(ProductName, Price) VALUES ('iPhone X', 76000)," + "('Galaxy S9', 45000), ('Nokia 9', 36000)"); System.out.printf("Added %d rows", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >

Для добавления данных в БД применяется команда INSERT. В данном случае в таблицу Products добавляется три объекта. И после выполнения программы на консоли мы увидим число добавленных объектов:

C:\Java>javac Program.java C:\Java>java -classpath c:\Java\mysql-connector-java-8.0.11.jar;c:\Java Program Added 3 rows C:\Java>

А добавленные строки мы можем увидеть в таблице в бд MySQL:

Редактирование

Изменим строки в таблице, например, уменьшим цену товара на 5000 единиц. Для изменения применяется команда UPDATE:

import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); int rows = statement.executeUpdate("UPDATE Products SET Price = Price - 5000"); System.out.printf("Updated %d rows", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >

Удаление

Удалим один объект из таблицы с помощью команды DELETE:

import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); int rows = statement.executeUpdate("DELETE FROM Products WHERE row(s) deleted", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >

Как видно из примеров, не так сложно взаимодействовать с базой данных. Достаточно передать в метод executeUpdate нужную команду SQL.

Источник