Jaspersoft Studio Create New Connection With Mysql Database

Jaspersoft Studio is a free Ide for creating a file report like IReport. Download and view other information here.

This tool is almost similar to the usual I use IReport to create report files in java applications. Not much is being done to connection settings Jaspersoft Studio and mysql database, Only need to create a new connection and adds mysql-connector-java to mysql connect with Jaspersoft Studio.

See the video how to make a connection to a Mysql Database and Jaspersoft
https://www.youtube.com/watch?v=wMNQXLPkK8g

Java Display Google Maps

This time i want share tips simplest maps showing in java swing,To display of maps in swing this we can use ireport that provides a facility map,Netbeans as Editor and Mysql as a data storage. view video tutorials on youtube

Start it,Here is the steps..

1. to do is make table at mysql to store Id,Location_Name,Latitude and Longitude.

- Login to Mysql Database

- Create new database or Entered one database in mysql (in this example i use the database 'test')
         USE test;

- Create new Table and named 'locations'
        CREATE TABLE `locations` (
        `Id` int(11) NOT NULL AUTO_INCREMENT,
        `Location_Name` varchar(200) DEFAULT NULL,
       `Latitude` float(10,5) NOT NULL,
   `Longitude` float(10,5) NOT NULL,
        PRIMARY KEY (`Id`));

- Insert row data into table 'locations'
        INSERT INTO locations VALUES
(null,'Cirebon city square','-6.70949','108.55924'),
(null,'The heritage of "Sunan Gunungjati" Cirebon','-6.67388','108.54136'),
(null,'The great mosque "Sang Cipta Rasa" Cirebon','-6.72552','108.56992');




2. Connect ireport with a database mysql and designing map with ireport

3. Create a new project MapsOnSwing on netbeans ide , in this example I'm using netbeans 7.3.

- Add the required libraries to the project lib:
  mysql-connector-java-5.1.25-bin.jar or another version
  jasperreports-4.5.0.jar or another version , adapt with the version of ireport
  groovy-all-1.5.5.jar
  commons-logging-1.1.jar
  commons-digester-1.7.jar
  commons-collections-3.2.1.jar
  commons-beanutils-1.8.2.jar




4. After the project is created, then the package will be formed mapsonswing , in this package there is already a class MapsOnSwing.java





5.  In mapsonswing package create new class MainFrame extend to JFrame,MainFrame design like the picture below :

Add desktoppane, combobox to zoom location,ComboBox Id_Location and Button to show Map

Add item to ComboBox Zoom : 15 , 16 , 17 , 18

Add item to ComboBox Id_Location : 1 , 2 , 3 or it could also take id_location from table location

Add Button to show Map

6.  Edit mapsonswing.java like this

7. In mapsonswing package create new Class IFrame extends JInternalFrame, add two parameter in constructor IFrame (int zoom,int id).




8. Make command buttons, when in clicks will feature internalframe that contains map


9.Build and run project , the result will be like this

finished , see the video on youtube

Java Restore Mysql Database

This time I want to share how to restore a mysql database from a backup file using java.
About how backups mysql database you can see here
Swing components needed are a JFrame, JPanel,JTextfield and JButton
We also need a JFilechooser to seek the path of a file that will be on make a URL,Class Process to call mysql And one more components JOptionpane as a sign that the process of restore a database mysql has been completed

The process is we search for a location file backups by using JFileChooser that will produce the path a file and will be used as a Url, then the Url will be set to JTextField.
then call restoreMysql() method that will run Class Process with parameter mysql path and file bacukup
This is an example code..

import java.awt.FlowLayout;
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.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;


public class RestoreMysql extends JFrame{

    /**
     *
     */
    private static final long serialVersionUID = 1L;
    JPanel pnl;
    JTextField tf;
    JButton bopen;
    JButton brestore;
   
    public RestoreMysql(){
       
        pnl = new JPanel();
        pnl.setLayout(new FlowLayout());
        this.getContentPane().add(pnl);
       
        tf = new JTextField(null,25);
        pnl.add(tf);
       
        bopen = new JButton("Open");
        pnl.add(bopen);
        bopen.addActionListener(new ActionListener(){

            @SuppressWarnings("deprecation")
            @Override
            public void actionPerformed(ActionEvent ae) {
                JFileChooser jfc = new JFileChooser();
                int i = jfc.showOpenDialog(pnl);
                if(i==JFileChooser.APPROVE_OPTION){
                    URL url;
                    try {
                        url = jfc.getSelectedFile().toURL();
                        String file = url.toString().replaceAll("file:/", "").trim();
                        tf.setText(file);
                    } catch (MalformedURLException e) {
                        e.printStackTrace();
                    }
                   
                }
               
            }
           
        });
       
        brestore = new JButton("Restore");
        pnl.add(brestore);
        brestore.addActionListener(new ActionListener(){

            @Override
            public void actionPerformed(ActionEvent ae) {
                restoreMysql(tf.getText());
            }
           
        });
       
        this.setDefaultCloseOperation(EXIT_ON_CLOSE);
        this.setSize(500, 150);
        this.setLocationRelativeTo(null);
        this.setVisible(true);
    }
   
    private void restoreMysql(String file) {
        String user = "root";
        String pass = "";
       
        try{
            String[] executeCmd = new String[]{"C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysql","-hlocalhost","-port3306", "--user=" + user, "--password=" + pass, "-e", "source "+file};
            Process p = Runtime.getRuntime().exec(executeCmd);
           
            int in = p.waitFor();
            JOptionPane.showMessageDialog(this, "Restore finis..");
        } catch (InterruptedException ex) {
            System.out.println(ex);
            JOptionPane.showMessageDialog(this, ex);
        }catch(IOException e){
            System.out.println(e);
            JOptionPane.showMessageDialog(this,e);
        }
    }
   
    public static void main(String[] args){
        SwingUtilities.invokeLater(new Runnable(){
            public void run(){
                new RestoreMysql();
            }
        });
    }

}

hopefully helpful...

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);
            }
        }
       
    }

}

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