Java JDBC Insert Example

Posted in /  

Java JDBC Insert Example
ramyashankar

Ramya Shankar
Last updated on April 25, 2024

    Web applications collect data from the user through the User Interface (UI) and insert the information into the database. This process happens automatically without us running any database scripts or monitoring data. This magic is done by JDBC (Java DataBase Connectivity), which is a Java API to connect Java code to any database.

    What is JDBC (Prepared)Statement and JDBC insert

    Through JDBC, we can perform all the standard SQL operations like insert, update, delete, etc. First, we need to establish the database connection, then create the SQL statements, execute them, and then verify the results.

    Connecting to the database

    To connect to the database, we have to establish JDBC connection using the getConnection() method by specifying the DriverManager or the DataSource:

    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", "jack@1880");
    connectionProps.put("password", "kb@ert5k");
    conn = DriverManager.getConnection(
    
            "jdbc:" + "mysql" + "://" +
            "localhost" +
            ":" + "8080" + "/",
            connectionProps);

    Note that the servername and port number has to be as per your project. Here we have used localhost and 8080. Same way, here the dbms is mysql, but it depends on your dbms. The same applies for the username and password properties too.

    Handling exceptions

    The above code is correct except one thing – we have not handled SQLException. If there is any problem with establishing the connection, the program will throw SQLException, so we must add a try/catch block to handle the same:

        try {
    conn = DriverManager.getConnection(
            "jdbc:" + "mysql" + "://" +
            "localhost" +
            ":" + "8080" + "/",
            connectionProps);
    } catch (SQLException e) {
    System.out.println("Couldn't connect!");
    }

    Creating a series of inserts

    We can insert rows using java sql Statement class or PreparedStatement class. The latter is preferred because it is secure, but to understand it we need to also learn about the former.

    Using Statement

    Create a statement using the connection established:

    Statement stmt = conn.createStatement();

    Now, create the query and execute it.

    String sql = "insert into employee values(1200, 'Jack Cha', '29-10-2013', 45000, 'Java')";
    stmt.executeUpdate(sql);

    To insert more queries at once, we can add them to a batch, and then execute the batch:

    stmt.addBatch("insert into employee values(1201, 'Sam S', '29-10-2019', 25000, 'Java'");
    stmt.addBatch("insert into employee values(1202, 'Micheal', '19-10-2010', 75000, 'Java'");
    stmt.addBatch("insert into employee values(1203, 'Maria', '29-03-2014', 35000, 'Java'");
    stmt.addBatch("insert into employee values(1204, 'Joe', '09-10-2015', 30000, 'Java'");
    
    stmt.executeBatch();
    stmt.close();

    Using PreparedStatement

    The process is the same, but we can prepare the query using setter methods. Suppose we are getting all the values from an object emp of the class Employee.

    sql = "insert into employee values(?, ?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, emp.getId());
    pstmt.setString(2, emp.getName());
    pstmt.setDate(3, emp.getDate());
    pstmt.setFloat(4, emp.getSalary());
    pstmt.setString(5, emp.getSkill());
    
    pstmt.executeUpdate();

    We can perform batch operations using PreparedStatement as well.

    pstmt.setInt(1, emp1.getId());
    pstmt.setString(2, emp1.getName());
    pstmt.setDate(3, emp1.getDate());
    pstmt.setFloat(4, emp1.getSalary());
    pstmt.setString(5, emp1.getSkill());
    pstmt.addBatch();
    pstmt.setInt(1, emp2.getId());
    pstmt.setString(2, emp2.getName());
    pstmt.setDate(3, emp2.getDate());
    pstmt.setFloat(4, emp2.getSalary());
    pstmt.setString(5, emp2.getSkill());
    pstmt.addBatch();
    pstmt.executeBatch();
    pstmt.close();

    Summary

    We have seen that there are different ways to perform a JDBC insert, using Statement and PreparedStatement. To follow this article, you should know the basics of SQL. It is also important to handle SQLException so that the program terminates gracefully in case of any failure.

    People are also reading:

    Leave a Comment on this Post

    0 Comments