How to create a database in MySQL with connection object conn (Student Class Example)

Java code for inserting data into the database is done using JDBC (Java Database connectivity).

JDBC is an API to connect the java programs to various databases like MySql, Oracle, MSAccess, etc.

Here Step by step tutorial to insert data from java into the database.

Here MySql or MariaDB database is used for this purpose.

So to insert data in the database we need to set in two ends

Create a Database and table
Write Java (JDBC) code

Let’s see them one by one

Create a Database and table

Open phpMyAdmin click on the home icon or the left side

Click on SQL on the middle menu.

Write create query given below

CREATE USER 'ebhor_user'@'localhost' IDENTIFIED BY '21V6';
GRANT ALL ON *.* TO 'ebhor_user'@'localhost';

create database ebhor;
use ebhor;
create table student(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
roll_no bigint(20) unsigned NOT NULL,
name varchar(100) NOT NULL,
branch varchar(25) NOT NULL,
section varchar(3),
email_id varchar(100),
dob date,
mobile_no varchar(12),
add_date timestamp DEFAULT CURRENT_TIMESTAMP,
primary key(id),
unique key(mobile_no),
unique key(email_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copy and insert above code in SQL query box

After clicking on the go button it will show the below box if the query is successfully executed.

You can see the structure of the table. which show below output.

Now the table is created.

Write JDBC Code

The steps for JDBC are as below

import JDBC classes
Load and register JDBC Driver
Open Database connection
Create a Statement object to pass the query
Execute Statement
Get the result
Close connections

File Structure of Program

Importing JDBC classes

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

Load and register JDBC Driver

Here Class.forName() is used to load and register driver

import java.sql.Connection;
Class.forName("com.mysql.jdbc.Driver");

Open Database connection

DriverManager.getConnection("jdbc:mysql://localhost:3306/ebhor?useUnicode=true&characterEncoding=UTF-8", "ebhor_user", "21V6");

Create a Statement object to pass the query

PreparedStatement ps=con.prepareStatement(query);

Execute the Statement and get the result

Here ps.executeUpdate() execute the query and get the of row affected.

int st=ps.executeUpdate();

Close connections

pre class=”theme:eclipse font:verdana toolbar:2 show-plain:3 lang:java decode:true”> con.close();

Insert a Student Object into the database

Before proceeding add MySQL-connector-java-5.1.14-bin.jar to your classpath.

All file details are as below.

Student.java

Student object that contains basic properties of students like id, rollNo, name, branch,section, emailId,dob, mobileNo, addDate.

package com.ebhor.model;
import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;

public class Student implements Serializable {
    long id;
    long rollNo;
    String name;
    String branch;
    String section;
    String emailId;
    Date dob;
    String mobileNo;
    Timestamp addDate;

    public Student() {

    }

    public Student(long rollNo, String name, String branch, String section, String emailId, Date dob, String mobileNo) {
        this.rollNo = rollNo;
        this.name = name;
        this.branch = branch;
        this.section = section;
        this.emailId = emailId;
        this.dob = dob;
        this.mobileNo = mobileNo;
    }

    public Student(long id, long rollNo, String name, String branch, String section, String emailId, Date dob, String mobileNo, Timestamp addDate) {
        this.id = id;
        this.rollNo = rollNo;
        this.name = name;
        this.branch = branch;
        this.section = section;
        this.emailId = emailId;
        this.dob = dob;
        this.mobileNo = mobileNo;
        this.addDate = addDate;
    }
@Override
    public String toString() {
        return "Student{" + "id=" + id + ", rollNo=" + rollNo + ", name=" + name + ", branch=" + branch + ", section=" + section + ", emailId=" + emailId + ", dob=" + dob + ", mobileNo=" + mobileNo + ", addDate=" + addDate + '}';
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }
    public long getRollNo() {
        return rollNo;
    }

    public void setRollNo(long rollNo) {
        this.rollNo = rollNo;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getBranch() {
        return branch;
    }

    public void setBranch(String branch) {
        this.branch = branch;
    }

    public String getSection() {
        return section;
    }

    public void setSection(String section) {
        this.section = section;
    }

    public String getEmailId() {
        return emailId;
    }

    public void setEmailId(String emailId) {
        this.emailId = emailId;
    }

    public Date getDob() {
        return dob;
    }

    public void setDob(Date dob) {
        this.dob = dob;
    }

    public String getMobileNo() {
        return mobileNo;
    }

    public void setMobileNo(String mobileNo) {
        this.mobileNo = mobileNo;
    }

    public Timestamp getAddDate() {
        return addDate;
    }

    public void setAddDate(Timestamp addDate) {
        this.addDate = addDate;
    }

}

ConnectionFactory .java

This file is responsible to connect with MySql database, after getting connection this will return the connection object

package com.ebhor.dababase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {

    public static Connection getConnection() {
        Connection c = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            c = DriverManager.getConnection("jdbc:mysql://localhost:3306/ebhor?useUnicode=true&characterEncoding=UTF-8", "ebhor_user", "21V6");
        } catch (ClassNotFoundException e) {
            System.out.println("ClassNotFoundException " + e);
        } catch (SQLException e) {
            System.out.println("SQLException " + e);
        }
        return c;
    }
}

StudentJDBCInsert.java

This class will get a connection from ConnectionFactory and insert data in the database and return the rows inserted in the database.

package com.ebhor.dababase;


import com.ebhor.model.Student;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class StudentJDBCInsert {

    public static void main(String[] args) {
        Connection con = ConnectionFactory.getConnection();
        con = ConnectionFactory.getConnection();
        PreparedStatement ps = null;
        int st = 0;
        Student student = new Student(1, "Ram", "CSE", "A", "[email protected]", Date.valueOf("2020-02-04"), "1234567891");
        try {
            String query = "insert into student(roll_no,name,branch,section,email_id,dob,mobile_no) "
                    + "values(?,?,?,?,?,?,?)";
            ps = con.prepareStatement(query);
            ps.setLong(1, student.getRollNo());
            ps.setString(2, student.getName());
            ps.setString(3, student.getBranch());
            ps.setString(4, student.getSection());
            ps.setString(5, student.getEmailId());
            ps.setDate(6, student.getDob());
            ps.setString(7, student.getMobileNo());
            st = ps.executeUpdate();
            System.out.println("inserted student " + st);
        } catch (MySQLIntegrityConstraintViolationException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

    }
}

Output

 
inserted student 1

Insert multiple Student Objects into the database

an efficient way to insert multiple records in the database is using batch processing

Steps to store multiple records in the database

Create multiple objects
Add all to a List
Add list item to batch
executeBatch()

StudnetJDBCBatchInsert.java

this program will insert batch data to MySql database

 
package com.ebhor.dababase;
import com.ebhor.model.Student;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
public class StudentJDBCBatchInsert {
    public static void main(String[] args) {
        Connection con = ConnectionFactory.getConnection();
        con = ConnectionFactory.getConnection();
        PreparedStatement ps = null;
        int batchSize = 50;
        int st[];
        Student student1 = new Student(2, "Sohan", "CSE", "A", "[email protected]", Date.valueOf("2001-01-05"), "17666567591");
        Student student2 = new Student(3, "Rohan", "Mech", "A", "[email protected]", Date.valueOf("2001-02-03"), "1434667591");
        Student student3 = new Student(4, "Nita", "Mech", "A", "[email protected]", Date.valueOf("2001-04-03"), "1434517591");
        Student student4 = new Student(5, "Rita", "Civil", "A", "[email protected]", Date.valueOf("2002-01-02"), "1434563591");
        Student student5 = new Student(6, "Mona", "Mech", "A", "[email protected]", Date.valueOf("2002-03-01"), "1434514621");
        List studentArray = new ArrayList();
        studentArray.add(student1);
        studentArray.add(student2);
        studentArray.add(student3);
        studentArray.add(student4);
        studentArray.add(student5);
        try {
            String query = "insert into student(roll_no,name,branch,section,email_id,dob,mobile_no) "
                    + "values(?,?,?,?,?,?,?)";
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);
            for (Student student : studentArray) {
                ps.setLong(1, student.getRollNo());
                ps.setString(2, student.getName());
                ps.setString(3, student.getBranch());
                ps.setString(4, student.getSection());
                ps.setString(5, student.getEmailId());
                ps.setDate(6, student.getDob());
                ps.setString(7, student.getMobileNo());
                ps.addBatch();
            }
            st = ps.executeBatch();
            con.commit();
            for (int i : st) {
                System.out.println("inserted student " + i);
            }
        } catch (MySQLIntegrityConstraintViolationException e) {
            try {
                con.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(StudentJDBCBatchInsert.class.getName()).log(Level.SEVERE, null, ex);
            }
            e.printStackTrace();
        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(StudentJDBCBatchInsert.class.getName()).log(Level.SEVERE, null, ex);
            }
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Output

inserted student 1
inserted student 1
inserted student 1
inserted student 1
inserted student 1

MySql Admin view

Reading Data using JDBC

StudentJDBCRead .java

This program read student records from the database table and shows them in java console.

 
package com.ebhor.dababase;
import com.ebhor.model.Student;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StudentJDBCRead {
    public static void main(String[] args) {
        Connection con = ConnectionFactory.getConnection();
        con = ConnectionFactory.getConnection();
        PreparedStatement ps = null;
        List studentList = new ArrayList();
        try {
            String query = "select * from student";
            ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getLong("id"));
                student.setRollNo(rs.getLong("roll_no"));
                student.setName(rs.getString("name"));
                student.setBranch(rs.getString("branch"));
                student.setSection(rs.getString("section"));
                student.setEmailId(rs.getString("email_id"));
                student.setDob(rs.getDate("dob"));
                student.setMobileNo(rs.getString("mobile_no"));
                student.setAddDate(rs.getTimestamp("add_date"));
                studentList.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        System.out.println("Student details are");
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
}

Output

Student details are
Student{id=22, rollNo=1, name=Ram, branch=CSE, section=A, [email protected], dob=2020-02-04, mobileNo=1234567891, addDate=2020-08-07 16:38:11.0}
Student{id=23, rollNo=2, name=Sohan, branch=CSE, section=A, [email protected], dob=2001-01-05, mobileNo=17666567591, addDate=2020-08-07 16:38:50.0}
Student{id=24, rollNo=3, name=Rohan, branch=Mech, section=A, [email protected], dob=2001-02-03, mobileNo=1434667591, addDate=2020-08-07 16:38:50.0}
Student{id=25, rollNo=4, name=Nita, branch=Mech, section=A, [email protected], dob=2001-04-03, mobileNo=1434517591, addDate=2020-08-07 16:38:50.0}
Student{id=26, rollNo=5, name=Rita, branch=Civil, section=A, [email protected], dob=2002-01-02, mobileNo=1434563591, addDate=2020-08-07 16:38:50.0}
Student{id=27, rollNo=6, name=Mona, branch=Mech, section=A, [email protected], dob=2002-03-01, mobileNo=1434514621, addDate=2020-08-07 16:38:50.0}

Download the above code from GitHub.

MySql Jdbc Operations for beginner

JTable Pagination in Java JDBC and MySQL Database

Registration Form in Java | Student registration form using java swing source code

Create a Database and table

Write JDBC Code

Importing JDBC classes

Load and register JDBC Driver

Open Database connection

Create a Statement object to pass the query

Execute the Statement and get the result

Close connections

Insert a Student Object into the database

Student.java

ConnectionFactory .java

StudentJDBCInsert.java

Insert multiple Student Objects into the database

Steps to store multiple records in the database

StudnetJDBCBatchInsert.java

Read More