Recent Posts

Tuesday, 13 November 2018

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.
Example
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();
   }
}

Next Tutorial : About PreparedStatement

Previous Tutorial : Aggregate Functions

No comments:

Post a Comment