Tuesday, 18 July 2017

jdbc code to insert data into mysql database

                     In this post, we will see a Java Database Connectivity program (JDBC) for inserting data (record or row) into the table in 'mysql' database.

               Here 'sdldatabase' is the database name. User name is 'sdl' and password is 'sdlpassword'. This program is for retrieving records (tuples) from table 'student' which has four fields (columns).
               Following are the SQL queries implemented on SQL prompt followed by JDBC program for inserting data. Go through them.


SQL queries implemented on mysql prompt:
parag@parag-Inspiron-N4010:~$ mysql -u root -p
Enter password: 

mysql> create user 'sdl'@'localhost' identified by 'sdlpassword';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'sdl'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> create database sdldatabase;

Query OK, 1 row affected (0.00 sec)

mysql> use sdldatabase;

Database changed

mysql> create table student

    -> (
    -> rollno int(5),
    -> class varchar(10),
    -> college varchar(10),
    -> city varchar(10)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into student values(3101,'te','pict','pune');

Query OK, 1 row affected (0.03 sec)

mysql> insert into student values(4301,'be','gcoe','amravati');

Query OK, 1 row affected (0.05 sec)

mysql> select * from student;

+--------+-------+---------+----------+
| rollno | class | college | city     |
+--------+-------+---------+----------+
|   3101 | te    | pict    | pune     |
|   4301 | be    | gcoe    | amravati |
+--------+-------+---------+----------+
2 rows in set (0.00 sec)
                

JDBC Program (jdbcinsert.java):



import java.sql.*;

class jdbcinsert
{
public static void main(String args[]) throws Exception
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sdldatabase","sdl","sdlpassword");
Statement st=con.createStatement();
int i=st.executeUpdate("insert into student values(1201,'se','vjti','mumbai')");
ResultSet rs=st.executeQuery("select * from student");
while(rs.next())
{
System.out.print(rs.getString(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");

System.out.println("\n");
}

rs.close();
st.close();
con.close();

}

}


Note:


                     We have to download mysql-connector.jar file. We can download it from the link http://www.java2s.com/Code/Jar/m/Downloadmysqlconnectorjar.htm. Extract zip file and save it at location /usr/share/java.              

                     To run jdbc program on terminal in Linux, you need to edit .bashrc file. You will get .bashrc file in following location

/home/[user name]/.bashrc

e.g. 

parag@parag-Inspiron-N4010:~$ gedit /home/parag/.bashrc


Then add following line in .bashrc file:

export CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector.jar


Save it and restart the system.


Output:
parag@parag-Inspiron-N4010:~/Desktop/prog$ javac jdbcinsert.java 
parag@parag-Inspiron-N4010:~/Desktop/prog$ java jdbcinsert
3101 te pict pune 

4301 be gcoe amravati 

1201 se vjti mumbai 


No comments:

Post a comment