STORED PROCEDURE PROGRAMMING IN JAVA USING MYSQL

Some of the benefits of stored procedure programming are faster execution of sql statements and also preventing sql injection. So whenever the application logic of your application is data centric, it is better to use stored procedure.

To access mysql stored procedure in your java application, you first create the stored procedure in mysql. Below is a sample created stored procedure in mysql.

   1: DELIMITER $$

   2: DROP PROCEDURE IF EXISTS `test`.`cdinsert`$$

   3: CREATE DEFINER=`root`@`localhost` PROCEDURE `cdinsert`(cID varchar(15), tdate varchar(15),cdNo int)

   4: BEGIN

   5:  if exists(select ID from cddetail where cdID=cID) then

   6:   update cddetail set transdate=tdate,cdNum=cdNo where cdID=cID;

   7:  else

   8:   insert into cddetail(cdID,transdate,cdNum) values (cID,tdate,cNo);

   9:  end if;

  10: END$$

  11: DELIMITER ;

You then create your java application  to access the stored procedure as shown below

   1: import java.sql.*;

   2: public class storproc {

   3:  

   4:     Connection conn = null;

   5:     Statement stm = null;

   6:     static final String url = "jdbc:mysql://localhost/test";

   7:     static final String user = "root";

   8:     static final String pswd = "password";

   9:  

  10:     //connecting to the mysql database

  11:     public Connection getConnection() {

  12:         try {

  13:             conn = DriverManager.getConnection(url, user, pswd);

  14:         } catch (Exception ex) {

  15:             System.out.println("error occured " + ex.toString());

  16:         }

  17:         return conn;

  18:     }

  19:  

  20:     public void insertNew(String line) {

  21:         try {

  22:             String input[] = line.split(",");

  23:             //calling the created storedprocedure 

  24:             //and passing the required parameters

  25:             CallableStatement cs = this.getConnection().

  26:             prepareCall("{call insertnew(?,?,?)}");

  27:             

  28:             cs.setString(1, input[0]);

  29:             cs.setString(2, input[1]);

  30:             cs.setInt(3, Integer.parseInt(input[2]));

  31:             cs.execute();

  32:         } catch (Exception e) {

  33:             System.out.println("errr  " + e.toString());

  34:         } finally {

  35:             try {

  36:                 conn.close();

  37:             } catch (SQLException ex) {

  38:                 System.out.println("error occured " + ex.toString());

  39:             }

  40:         }

  41:  

  42:     }

  43: }

Advertisements

LOAD IMAGES DYNAMICALLY INTO JASPER REPORTS

If you want to insert images from the file system (e.g. C:\images folder) into jasper reports at runtime, for example changing a logo depending on user selection or simply allowing the user to browse for his or her image to be displayed  in the report then here is a simple process of doing that.

Let start with the report design using ireport. In this instance the picture will be passed to the report as a parameter.

So let’s create a parameter in our report and call it “photo”

image

As shown in the picture above, the data type of our parameter should be “java.lang.Object”

After this, drag the image , from the tool bar, on to your report.Right click on the image and choose “Properties” from the menu

image

Under the image tab of the dialog box, make sure you select “java.awt.image” for the Image Expression Class. Once this is done, you are done with the report design

Lets go to netbeans , do some coding and connect our designed report to a java application.

First we need to declare a variable of type image,

Image photo;

Also we create a method , which on click of a button, loads a picture from the file system and initializes the “photo: variable.

JFileChooser fc = new JFileChooser();

private void getPicture() {

fc.setFileFilter(new FileNameExtensionFilter("Images", "jpg", "gif", "bmp"));

if (fc.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) {

fc.setCurrentDirectory(fc.getCurrentDirectory());

ImageIcon icon = new ImageIcon(fc.getSelectedFile().getAbsolutePath());

icon = new ImageIcon(icon.getImage().getScaledInstance(350, 350, Image.SCALE_DEFAULT));

photo = icon.getImage();

}

After getting our picture, create an instance of the Map class and pass our “variable” as a parameter.

Map<String, Object> param = new HashMap<String, Object>();

param.put("photo", photo); //the “photo” should be the same name as the parameter name in our report

We then create a method that passes our created parameter to the report and we are done. below is the method that does the job.

private void generateReports(String name, Map param) {

try {

String source = "C:/sabonay/jasperreports/" + name + ".jrxml";

if (new File(source).exists() == false) {

xputils.showMessage("Please  report Source does not exist");

return;

}

JasperReport jasperReport = JasperCompileManager.compileReport(source);

JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, param, new JREmptyDataSource());

JasperViewer.viewReport(jasperPrint, false);

//the false parameter makes sure the application does not close on closing the report

} catch (Exception e) {

System.out.println("reports Error " + e.toString());

}}

Make sure to reference the necessary jasper jar files in your application

FORM.SHOWDIALOG() IN JAVA

Have you wondered how to show a panel(form) as a dialog in java applications as done in .NET applications by using the form.showDialog() method?  For example, when a user selects “Add New” from a Customer combobox, the customer panel shows as a dialog for a new entry before the user can continue. If you have been searching for this, here is your stop.

We will be making use of one JDialog, and your FORMS(JPanels in this sense).

To implement this add a JDialog to your application. Declare a variable contentPane and add the constructor like the one specified below.

private Container contentPane = null;

public popupsjdialog(java.awt.Frame parent, JPanel panel, boolean modal) {
//super(parent, modal);
contentPane = this.getContentPane();
initComponents();
this.setSize(panel.getSize().width + 30, panel.getSize().width + 50);
panel.setLocation(10, 30);
contentPane.add(panel);
repaint();
this.setLocationRelativeTo(null);

}

Your are now done. Now to show any JPanel as a dialog add the following code to your event.

helpdetailpanel hd = new helpdetailpanel(helpContent);
hd.setSize(700, 700);
popupsjdialog d = new popupsjdialog(null, hd, true);
d.setVisible(true);

Helpdetailpanel is a Jpanel (form) you want to show as a dialog and the popupsjdialog is an instance of the Jdialog we created its constructot above.

Cheers !!!!!!!!!!!!!!!!!

READING EXCEL FILES IN JAVA APPLICATIONS

Have you wondered about how to read excel files(.xls,xlsx), in Java Applications without using any API, then here is your stop. With this you can read excel 2003 and also 2007.We will be going through simple steps in reading the content of excel files and populating it into a Jtable. We will be working in the Microsoft Windows environment.

First, you have to create a datasource pointing to where your excel file is located. To do this, Go to Control panel ——— >   Administrative Tools ———- > Data Sources (ODBC). Below is an interface you are likely to see.

datasource face

Select the “User DSN” tab and click on the add button and select the driver “Microsoft Excel Driver (.xls,xlsx)” . You will be presented with the interface below, enter the name of the data source (Note, this is the name you will reference from you Java Application) and click on the “Select Workbook” button to select the excel file you want to read. You are now done with setting up the datasource.

choose driverselect workbook

Let’s now go to our Jave IDE (Netbeans) and do some small coding.  We will be using the JDBC-ODBC driver for reading our data. You don’t need to add the driver to your application as Windows OS comes with the Driver already installed.

We are creating a simple application where the user enters the datasource name and the sheet name, then on click of a button the data if populated into a JTable

 

We do this by creating a method

private void GetData(String excelDSN, String sheetname) {

        Connection connection = null;

        Statement st;

        ResultSet rsltSet;

        ResultSetMetaData rsltSetMetaData;

        String excelURL = "jdbc:odbc:" + excelDSN;

        String query = "Select * from [" + sheetname + "$]";

 

        try {

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            connection = DriverManager.getConnection(excelURL);

            //System.out.println("ReadExcel connection = " + connection);

            st = connection.createStatement();

            //System.out.println("ReadExcel connection.createStatement() = " + st);

            rsltSet = st.executeQuery(query);

            //System.out.println("ReadExcel rsltSet = " + rsltSet);

            rsltSetMetaData = rsltSet.getMetaData();

           

          //creating a datamodel for our JTable

            DefaultTableModel aModel = new DefaultTableModel() {

                @Override

                //setting the JTable uneditalble

                public boolean isCellEditable(int row, int column) {

                    return false;

                }

            };

            //adding the excel column names to the JTable

            Object[] tableColumnNames = new Object[rsltSetMetaData.getColumnCount()];

            for (int i = 1; i <= rsltSetMetaData.getColumnCount(); i++) {

                tableColumnNames[i-1] = rsltSetMetaData.getColumnName(i);

            }

            aModel.setColumnIdentifiers(tableColumnNames);

            //adding the rows to the JTable

            Object[] objects = new Object[rsltSetMetaData.getColumnCount()];

            while (rsltSet.next()) {

                for (int i = 1; i <= rsltSetMetaData.getColumnCount(); i++) {

                    objects[i-1] = rsltSet.getObject(i);

                }

                aModel.addRow(objects);

            }

            // setting the table model of the jtable

            this.jTable1.setModel(aModel);

        } catch (Exception ex) {

            JOptionPane.showMessageDialog(null, ex.toString() );

        }

    }

Note , In reading records from excel, the index starts from one not zero(0). Once you starting getting this exception “[Microsoft][ODBC Driver Manager] Invalid descriptor index.” Get the index , its 1 not zero

Sample interface of the Application

the app

CHARTING WITH JASPER REPORTS

 This is a tutorial on how to create charts (Pie and bar Charts) in Jasper Reports using  a Java Object ( with fields, cname and mark)  as a datasoure

We will be using ireport 3.0 and Netbeans 6.1  as the development environment. Make sure to add the jasper report libraries to your projects.

Design your report by creating the necessary fields (cname, mark) in the ireport.  Make sure the names of the field are the same as they appear in the entity class (the datasource) including capitalization

Drag a chart (using the chart tool on the tool bar) onto your report (make sure to place a bar chart at the summary band on the report designer, a pie chart can be placed in the pageHeader band). For a bar chart , below is a picture showing how to set the properties (the series expression uses the field that should be on the horizontal axis, the category expression is a field to group the fields on the horizontal axis, eg if subjects are on the horizontal axis, but you want to group them into 1st term , 2nd term, then a field “term”  will be used as the category expression. The value expression will be the field to be displayed vertically and the label expression will be the label names for your bars)

 

 

 

 

 

 

 

 With a pie Chart, for example if you are to create a pie chart with these values (maths:30, science:20), the key expression is the name field and the value expression been the values field. Below is a picture showing this.

 Setting pie chart properties

Then you go to netbeans to do some small coding.

Create a class with field cname and mark, where cname as of type sting and mark is double

Write the code below under an actionevent

    Map<String, Object> param = new HashMap<String, Object>();

    List<CourseMarks> clist = new ArrayList<CourseMarks>();

 //CourseMArks  is the class with the two files   

   CourseMarks c1 = new CourseMarks();

    c1.setCname(“English”);

    c1.setMark(58.00);

    clist.add(c1);

   

    c1 = new CourseMarks();

    c1.setCname(“Social  Studies”);

    c1.setMark(68.00);

    clist.add(c1);

   

    c1 = new CourseMarks();

    c1.setCname(“Culture”);

    c1.setMark(78.00);

    clist.add(c1);

   

    c1 = new CourseMarks();

    c1.setCname(“Maths”);

    c1.setMark(38.00);

    clist.add(c1);

           

    c1 = new CourseMarks();

    c1.setCname(“Twi”);

    c1.setMark(88.00);

    clist.add(c1);  

 generateReports(“charting”, clist, param);

 

Below is the detail of the generateReports Method

private void generateReports(String name, Collection data, Map param) {

        try {

            String source = “C:/sabonay/jasperreports/” + name + “.jrxml”;

            if (new File(source).exists() == false) {

                xputils.showMessage(“Please go to setting and Choose report Source”);

                return;

            }

            JRBeanCollectionDataSource jrbc = new JRBeanCollectionDataSource(data);

            JasperReport jasperReport = JasperCompileManager.compileReport(source);

            JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, param, jrbc);

            JasperViewer.viewReport(jasperPrint, false);

        } catch (Exception e) {

            e.printStackTrace();

            System.out.println(“reports Error  ” + e.toString());

        }

    }

Below is a sample report when the app is run

After running the App
After running the App

CREATING JASPER REPORTS WITH DYNAMIC IMAGES

This is a tutorial on how to embed images stored in a database (javaDB, MySQL,etc) into a jasper repors at runtime. This tutorial uses EJBs for creating an object for the table and its fields from the database. I’m also using Collection (List) of objects as the data source for the jasper reports. It is assumed the reader knows how to create reports using jasper reports.

The image field in the database table can be of data type blob, medium blob or long blob. In our entity class for the table, the datatype of the image (photo) field will be of the type byte [].Note that the image that will be sent to the report will be a type of java.awt.image, so therefore we create one additional field of type image in our class. In your code, create a method that converts a blob to image. (This can easily be done using image icon, as it accept blob and you can get image from it as well)

We will be using ireport 3.0, Netbeans 6.5 , Sun Application Server( because of me using session beans, but will not be necessary when creating desktop applications) as the development environment. Make sure to add the jasper report libraries to your projects.

Design your report by creating the necessary fields in the ireport.  Make sure the names of the field are the same as they appear in the entity class (the datasource) including capitalization as shown below

report1

In creating the fields, the image (photo) field should be of the type Object. After creating the image field, insert an image into your report from the ireport toolbar.  Then bind the image to the image (photo) field then you are done with the design of your report.

report3

Save your report. Note that the file type of your report can be .jrxml or .jasper. I’m using the .jasper as it removes the overhead of compiling it in your code in case it is .jrxml

Then you go to netbeans to do some small coding.

Write the code below under an actionevent

generateReports("StudentDetail", reportFactory.returnStudentsBySchool(s.getSchID()), param);

the “studentdetail” is the name of the report file, “param” is a map object which is used  incase you are filling part of your report with parameters (usually user imput). Parameters are also created the same way as creating fields but under the parameters section

Exxample of creating a map object in java

Map<String, Object> param = new HashMap<String, Object>();

param.put("school", “Abakrampa”); //school is the name of the parameter as it in the report inteface

“returnStudentsBySchool(s.getSchID())” is a method that returns a collection of the object (our datasource, and this is the detail

public static Collection returnStudentsBySchool(int schid) {

educationSSSSessionRemote session = lookupnewEducateFacadeBean();//session beans

List data = null;

List<StudentDetail> results = null;

data = session.findStudentBySchool(schid);// returns the students object with blob

ListIterator<StudentDetail> lstmb = data.listIterator();

results = new ArrayList<StudentDetail>();

// converting the byte[] to image type for all the student objects

ImageIcon n;

while (lstmb.hasNext()) {

StudentDetail s = lstmb.next();

if (s.getPicture() != null) {

n = new ImageIcon((byte[]) s.getPicture());

} else {

n = new ImageIcon();

}

s.setPhoto(n.getImage(),

results.add(s);

}

return results;

}

The generate report method

private void generateReports(String name, Collection data, Map param) {

try {

URL urlfilename = null;

String oSource = "reports/" + name;

String rSource = "reports/" + name + ".jasper";

urlfilename = getClass().getResource(rSource);

String reportfile = null;

if (urlfilename != null) {

reportfile = urlfilename.getPath();

} else {

return;

}

JRBeanCollectionDataSource jrbc = new JRBeanCollectionDataSource(data);

JasperPrint jasperPrint = JasperFillManager.fillReport(reportfile, param, jrbc);

JasperViewer.viewReport(jasperPrint, false);//"

// when the false is absent on closing the report it closes your java apllication

} catch (Exception e) {

e.printStackTrace();

System.out.println("reports Error  " + e.toString());

}   }

Below is a sample report when the app is run

report4

Running Client part of Java Enterprise Application using a batch file

Below is a simple way of running the client part of a J2EE application, by creating a batch file. Some assumtions made are :

1. JDK is installed on the Computer

2. Glassfish is installed and the EJB module deployed

To create a batch file, open notepad.exe, type your need commands and save your file as filename.bat

Below is the content of the batch file

Rem the portion below starts the Glassfish server if it has been shutdown

cd C:\Sun\SDK\lib
call “C:\Sun\SDK\config\asenv.bat”
call “%AS_INSTALL%\bin\asadmin.bat” start-domain domain1

goto e

Rem the portion below call the jar file, by going to its directory and

Rem calling the full class name (including package name of the start up file

:e
cd c:\
cd C:\sabon\eduSSS
set APPCPATH=.\
appclient -client EducationSSSClient.jar -mainclass com.sabonay.educationSss.ui.LoginForm
goto end

:end

Below is a picture after running the script

scriptspage