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