Working with MySQL Database

Working with MySQL Database

In MySQL, everything we have to work with our own Databases, which are also known as Logical Databases.

JDBC Information

In general, we can use Type-4 Driver to communicates with MySQL Database which is provided by MySQL Vendor. Driver Software is available in the following Jar File.

mysql-connector-java-5.1.41-bin.jar

We have to download separately from MySql Web Site.

JDBC URL : jdbc:mysql://localhost:3306/empdb

If MySQL is available in Local System then we can specify JDBC URL as above. Other we have to replace ipaddress of remote machine in the place of localhost.

Driver Class Name : com.mysql.jdbc.Driver
User Name : root
Password : root

We required to Set Class Path of MySql Driver Jar File.

E.g

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class JdbcMySQLDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select * from emp"; 
      Class.forName("com.mysql.jdbc.Driver"); 
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb","root","root"); 
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery(sqlQuery);
      while(rs.next()) {
         System.out.println(rs.getInt(1)+".."+rs.getString(2)+".."+rs.getDouble(3)+".."+rs.getString(4));
      }
      con.close();
   }
}

Copy data from Oracle to MySql database

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class JdbcMySQLDemo { 
   public static void main(String[] args) throws Exception {
      Connection con1 = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
      Connection con2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb","root","root"); 
      Statement st1 = con1.createStatement();
      Statement st2 = con2.createStatement();
      ResultSet rs = st1.executeQuery("select * from emp");
      while(rs.next()) {
         count++;
         int eno = rs.getInt(1);
         String ename = rs.getString(2);
         double esal = rs.getDouble(3);
         String eaddr = rs.getString(4);
         String sqlQuery = String.format("insert into employees values(%d,'%s',%f,'%s')",eno,ename,esal,eaddr);
         st2.executeUpdate(sqlQuery);
      }
      System.out.println("Total Data copied from Oracle to MySQL and number of records : "+ count);
      con1.close();
      con2.close();
   }
}
Working with MySQL Database

Scroll to top