2013-03-26

Java Backup Mysql Database

Backups database is important,at this time I will post gives an example of how to backup a mysql database using mysqldump and java swing.
This is just an example that can develop again.To choices in making backups file please visit mysql official website

import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;


public class BackupMysqlDatabase extends JFrame implements ActionListener{

    /**
     *
     */
    private static final long serialVersionUID = 1L;
    JPanel pnl;
    JLabel lbl;
    JTextField tf;
    JButton btnOpen,btnBackup;
    JFileChooser jfc;
    URL url;
    int s;
    /**
     * @param args
     */
    public BackupMysqlDatabase(){
        pnl = new JPanel();
        getContentPane().add(pnl);
       
        lbl = new JLabel("Backup Location");
        pnl.add(lbl);
       
        tf = new JTextField();
        tf.setPreferredSize(new Dimension(200,25));
        pnl.add(tf);
       
        btnOpen = new JButton("Open");
        btnOpen.addActionListener(this);
        pnl.add(btnOpen);
       
        btnBackup = new JButton("Backup");
        btnBackup.addActionListener(this);
        pnl.add(btnBackup);
       
        setTitle("Backup Mysql Database");
        this.setDefaultCloseOperation(EXIT_ON_CLOSE);
        setSize(550,300);
        setLocationRelativeTo(null);
        setVisible(true);
    }
    public static void main(String[] args) {
        SwingUtilities.invokeLater(new Runnable(){
            public void run(){
                new BackupMysqlDatabase();
            }
        });

    }
    @SuppressWarnings("deprecation")
    @Override
    public void actionPerformed(ActionEvent ae) {
        if(ae.getActionCommand().equals("Open")){
            jfc = new JFileChooser();
            jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
            int i = jfc.showSaveDialog(this);
            if(i==JFileChooser.APPROVE_OPTION){
                try {
                    url = jfc.getSelectedFile().toURL();
                    String backup_location = url.toString().replaceAll("file:/", "")+"backup.sql";
                    tf.setText(backup_location);
                } catch (MalformedURLException e) {
                    System.out.println(e);
                }
            }
        }
       
        if(ae.getActionCommand().equals("Backup")){
           
            try {
                url = jfc.getSelectedFile().toURL();
                String host = "Localhost";
                String port = "3306";
                String dbname = "test";
                String user = "root";
                String password = "";
                String backup_location = tf.getText().trim();
                Process p;
                p = Runtime.getRuntime().exec("C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysqldump -h"+host+" -port"+port+" -u "+user+" -p"+password+" --add-drop-database -B "+dbname+" -r "+backup_location);
                s = p.waitFor();
                JOptionPane.showMessageDialog(this, "Finis");
            } catch (MalformedURLException e) {
                System.out.println(e);
            }catch(IOException io){
                System.out.println(io);
            }catch(InterruptedException ie){
                System.out.println(ie);
            }
        }
       
    }

}

2012-12-28

Java Import Excel To Mysql Table

Import Excel to Mysql Table is easy using Apache POI , see previous post
Java Export Mysql Data To Excel , first add poi-3.7-20101029.jar and
mysql-connector-java to lib folder in your project.

file.xls Example
Id Name Address
1 Name 1 Address 1
2 Name 2 Address 2

This is code example

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;


public class ImportData {

    /**
     * @param args
     */
    public static void main(String[] args) {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
            con.setAutoCommit(false);
            PreparedStatement pstm = null ;
            FileInputStream input = new FileInputStream("D://file.xls");
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for(int i=1; i<=sheet.getLastRowNum(); i++){
                row = sheet.getRow(i);
                int id = (int) row.getCell(0).getNumericCellValue();
                String name = row.getCell(1).getStringCellValue();
                String address = row.getCell(2).getStringCellValue();
                String sql = "INSERT INTO tablename VALUES('"+id+"','"+name+"','"+address+"')";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
                System.out.println("Import rows "+i);
            }
            con.commit();
            pstm.close();
            con.close();
            input.close();
            System.out.println("Success import excel to mysql table");
        }catch(ClassNotFoundException e){
            System.out.println(e);
        }catch(SQLException ex){
            System.out.println(ex);
        }catch(IOException ioe){
            System.out.println(ioe);
        }

    }

}



Java Export Mysql Data To Excel          javareveal.blogspot.com
 

Java Export Mysql Data To Excel

Export Mysql Data to Excel using java , we required Apache POI -
the Java API for Microsoft Documents. You can download it from poi.apache.org.
in this article I try to give an example of how to use it.
After download Poi ,Extract that File to your directory. You can find poi-3.7-20101029.jar
or highest version.

Next, add poi-3.7-20101029.jar  to JDK instalation directory. 
Example :C:\Program Files\Java\jdk1.6.0_37\jre\lib\ext
If using Netbeans or Eclipse not need add poi to JDK directory , enough add
poi-3.7-20101029.jar to lib folder in your project. dont forget to add
mysql-connector-java for connection Java and Mysql

This is code example using Eclipse

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;


public class ExportData {

    /**
     * @param args
     */
    public static void main(String[] args) {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
            Statement statement = con.createStatement();
            FileOutputStream fileOut;
            fileOut = new FileOutputStream("file.xls");
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("Sheet 0");
            Row row1 = worksheet.createRow((short)0);
            row1.createCell(0).setCellValue("Name");
            row1.createCell(1).setCellValue("Address");
            Row row2 ;
            ResultSet rs = statement.executeQuery("SELECT Name,Address FROM tablename");
            while(rs.next()){
                int a = rs.getRow();
                row2 = worksheet.createRow((short)a);
                row2.createCell(0).setCellValue(rs.getString(1));
                row2.createCell(1).setCellValue(rs.getString(2));
            }
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
            rs.close();
            statement.close();
            con.close();
            System.out.println("Export Success");
        }catch(ClassNotFoundException e){
            System.out.println(e);
        }catch(SQLException ex){
            System.out.println(ex);
        }catch(IOException ioe){
            System.out.println(ioe);
        }

    }

}


javareveal.blogspot.com