If we need to insert some record into a database table from our java application,the JDBC API provides ways to insert into database in Java . Here we are explaining the concept with help of suitable example.
Insert into database in Java
We are using MySQL server 5.6 . The free version of MySQL is available here.
For interacting with database directly we can either use the default command line client of MySQL or an object browser like SQLyog. The community version of SQLyog is available for learning purposes.We can download it from here
Now login to the MySQL either from the command line client or from the SQLyog .
For inserting a record to database table we need to have a database in our newly installed MySQL environment. Also there should be one database table to store it.Let us create these things first.
The queries given below creates a database with name testdb and table testtable.The table testtable is having two columns:number(of type int) and data(of type varchar).
1)Create database
CREATE DATABASE testdb;
2)Create table
USE testdb;
CREATE TABLE testtable ( number INT PRIMARY KEY, DATA VARCHAR(100) );
Now let us discuss the java code.The steps in inserting data to database are :
1)Load JDBC driver
2)Establish connection with database
3)Do the transaction
4)Close the connection.
Now let us see the code.For this code we should put the MySQL connector jar in class path (The jar contains the driver and other components for connecting with MysQL database).We can download the MySQL connector jar from here.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBInsertSample {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultset = null;
public DBInsertSample() {
}
public void insert() {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "username",
"password");
statement = connection.createStatement();
int result = statement.executeUpdate("insert into testtable values('1','cat')");
if(result >= 0){
String query = "select * from testtable";
resultset = statement.executeQuery(query);
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " ; Data = " + resultset.getString(2));
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (resultset != null)
resultset.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
// TODO Auto−generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
DBInsertSample sample = new DBInsertSample();
sample.insert();
}
}
The line starts with class.forName() loads the driver class for connecting with MySQL server. Then a connection is establishing and executing the update task. Then iterating the table for getting the contents. If we run the code only once, we will get the output. If we try to run more times exception will be thrown.Because database is not allowing the duplication primary key value. (We defined the field number as primary key while creating the table).
Output
Number = 1 ; Data = cat