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

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

Jdbc is a API to connect java program to various databases like MySql, Oracle, MSAccess etc.

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

Here MySql or MariaDB database is used for this purpose.

So to insert data in database we need to set in two end

Create Database and table
Write Java (JDBC) code

Lets see them one by one

Create Database and table

Open phpMyAdmin click on home icon or left side

Click to SQL on 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;

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 click on go button it will show below box if query is successfully executed.

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

Now table is created.

Write JDBC Code

Steps for JDBC is as below

import JDBC classes
Load and register JDBC Driver
Open Database connection
Create Statement object to pass 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;

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");

1 2	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");

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

Create Statement object to pass query

PreparedStatement ps=con.prepareStatement(query);

1	PreparedStatement ps=con.prepareStatement(query);

Execute Statement and get result

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

int st=ps.executeUpdate();

1	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 database

Before proceeding add mysql-connector-java-5.1.14-bin.jar to your class path.

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;
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

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 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;
    }
}

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 connection from ConnectionFactory and insert data in database and return no of rows inserted in 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", "ram@ebhor.com", 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();
            }
        }

    }
}

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

1	inserted student 1

Insert multiple Student Objects into database

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

Steps to store multiple records in 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", "sohan@ebhor.com", Date.valueOf("2001-01-05"), "17666567591");
        Student student2 = new Student(3, "Rohan", "Mech", "A", "rohan@ebhor.com", Date.valueOf("2001-02-03"), "1434667591");
        Student student3 = new Student(4, "Nita", "Mech", "A", "nita@ebhor.com", Date.valueOf("2001-04-03"), "1434517591");
        Student student4 = new Student(5, "Rita", "Civil", "A", "rita@ebhor.com", Date.valueOf("2002-01-02"), "1434563591");
        Student student5 = new Student(6, "Mona", "Mech", "A", "mona@ebhor.com", Date.valueOf("2002-03-01"), "1434514621");
        List<Student> studentArray = new ArrayList<Student>();
        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();
            }
        }

    }
}

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<Student> studentArray = new ArrayList<Student>();

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

<pre class="theme:eclipse font:verdana toolbar:2
 show-plain:3 lang:default decode:true">inserted student 1
inserted student 1
inserted student 1
inserted student 1
inserted student 1

<pre class="theme:eclipse font:verdana toolbar:2

show-plain:3 lang:default decode:true">inserted student 1

inserted student 1

MySql Admin view

Reading Data using JDBC

StudentJDBCRead .java

This program read student records from database table and show 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<Student> studentList = new ArrayList<Student>();
        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);
        }
    }

}

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<Student> studentList = new ArrayList<Student>();

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, emailId=ram@ebhor.com, 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, emailId=sohan@ebhor.com, 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, emailId=rohan@ebhor.com, 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, emailId=nita@ebhor.com, 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, emailId=rita@ebhor.com, 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, emailId=mona@ebhor.com, dob=2002-03-01, mobileNo=1434514621, addDate=2020-08-07 16:38:50.0}

Student details are

Student{id=22, rollNo=1, name=Ram, branch=CSE, section=A, emailId=ram@ebhor.com, 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, emailId=sohan@ebhor.com, 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, emailId=rohan@ebhor.com, 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, emailId=nita@ebhor.com, 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, emailId=rita@ebhor.com, 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, emailId=mona@ebhor.com, dob=2002-03-01, mobileNo=1434514621, addDate=2020-08-07 16:38:50.0}

Download above code fron github