Tuesday, 18 July 2017

jdbc code to retrieve data from mysql database

                     In this post, we will see a Java Database Connectivity program (JDBC) for selecting records or retrieving data from 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 retrieving 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 (jdbc.java):

import java.sql.*;

class jdbc

{
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();
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 jdbc.java
parag@parag-Inspiron-N4010:~/Desktop/prog$ java jdbc
3101 te pict pune 

4301 be gcoe amravati 



                  
                      Check jdbc code for inserting data into mysql database in this post: http://www.comrevo.com/2017/07/jdbc-code-to-insert-data-into-mysql-database.html


No comments:

Post a comment