Page Index Toggle Pages: 1 Send TopicPrint
Hot Topic (More than 10 Replies) JPlanner (Read 10459 times)
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
JPlanner
Jan 14th, 2019 at 11:34am
Print Post  
Hello Guys. Can any one knows how to connect the JPlanner to a database?

My Project is about SCHEDULING. I need to MARK THE DATES on the Calendar Automatically if that date is equal on the DATE VALUE on my database when i run the program.

Hoping for your help  Smiley
  
Back to top
 
IP Logged
 
Iva_P
YaBB Moderator
*****
Offline


I Love MindFusion!

Posts: 41
Joined: Jun 19th, 2013
Re: JPlanner
Reply #1 - Jan 15th, 2019 at 10:55am
Print Post  
Hi Jack, here is the source code of the single *.java file from the sample application I've built for your question. Here we connect to a MySQL database called bookings and read column "booked_date" from a table "booking_data". We use the standard Oracle JDBC driver, the platform-independent version. The column contains Date values and we color the calendar cells that correspond to these dates in pink.

Code
Select All
// ========== MainWindow.java ========

import java.awt.*;
import javax.swing.*;

import com.mindfusion.common.DateTime;
import com.mindfusion.drawing.SolidBrush;
import com.mindfusion.scheduling.*;
import com.mindfusion.scheduling.model.Style;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class MainWindow extends JFrame
{
	public static void main(String[] args)
	{
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				MainWindow window = null;
				try {
					window = new MainWindow();
					window.setVisible(true);
				}
				catch (Exception exp) {
				}
			}
		});
	}

	public MainWindow()
	{
		setDefaultCloseOperation(EXIT_ON_CLOSE);
		setSize(368, 362);
		setTitle("MindFusion.Scheduling Sample: Minimal Application");

		calendar = new Calendar();
		calendar.setTheme(ThemeType.Light);

		Container cp = getContentPane();
		cp.setLayout(new BorderLayout());
		cp.add(calendar, BorderLayout.CENTER);

		establishConnection();
	}

	private void establishConnection()
	{

		Connection conn = null;
		try {
		    // db parameters
		    String url       = "jdbc:mysql://localhost:3306/bookings";
		    String user      = "mindfusion";
		    String password  = "mindfusion13";
		    String sql = "SELECT booked_date FROM booking_data";



		    // create a connection to the database
		    conn = DriverManager.getConnection(url, user, password);
		    Statement stmt  = conn.createStatement();
		    ResultSet rs    = stmt.executeQuery(sql);
		    while (rs.next()) {
		    	   System.out.println(rs.getString("booked_date"));
		    	   Date date = rs.getDate("booked_date");

		    	   DateStyle dStyle = new DateStyle();
		    	   dStyle.setFrom(new DateTime(date));
		    	   dStyle.setTo(new DateTime(date));
		    	   Style style = new Style();
		    	   style.setBrush(new SolidBrush(Color.pink));
		    	   dStyle.setStyle(style);

		    	   calendar.getDayStyles().add(dStyle);

		    	}

		} catch(SQLException e) {
		   System.out.println(e.getMessage());
		} finally {
		 try{
		           if(conn != null)
		             conn.close();
		 }catch(SQLException ex){
		           System.out.println(ex.getMessage());
		 }
		}

	}

	private Calendar calendar;
	private static final long serialVersionUID = 1L;
}

 



Your questions and comments are welcome.

« Last Edit: Jan 15th, 2019 at 2:03pm by Forum Admin »  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #2 - Jan 16th, 2019 at 3:49pm
Print Post  
Thank you very much Admin! Smiley Smiley Smiley
  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #3 - Jan 16th, 2019 at 3:55pm
Print Post  
I will try this code and come back for the result! Thankyou Again Admin
  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #4 - Jan 16th, 2019 at 4:48pm
Print Post  
Admin

I'm having errors on this line of code:

dStyle.setFrom(new DateTime(date));
dStyle.setTo(new DateTime(date));

The error (Incompatible types: Date cannot be converted to long)

I create a code that converts the Date to long:

Date date = rs.getDate("booked_date");
long dateInLong = date.getTime();

dStyle.setFrom(new DateTime(dateInLong));
dStyle.setTo(new DateTime(dateInLong));

Then if i run the program nothings happen, but if i put this:

dStyle.setFrom(new DateTime(dateInLong));
dStyle.setTo(new DateTime(dateInLong));

in comment

The Calendar Marks the Date Today. The problem is i need to mark also all the dates in the database.

Hoping for another replay admin
Thank you again for the help
« Last Edit: Jan 17th, 2019 at 2:41am by Capt.jack »  
Back to top
 
IP Logged
 
Iva_P
YaBB Moderator
*****
Offline


I Love MindFusion!

Posts: 41
Joined: Jun 19th, 2013
Re: JPlanner
Reply #5 - Jan 17th, 2019 at 10:46am
Print Post  
Hi Jack,
The problem is most likely caused by the way MindFusion calendar handles ticks compared to the Java classes. From the ticks that you provide to the MindFusion.DateTime constructor it builds an object that represents another date, which is not visible in the near date range. I do not know your database and your data, neither how you read your values, but I suggest you read the date components of the DB date and construct a MindFusion DateTime object using some of the other constructors:

https://www.mindfusion.eu/onlinehelp/jplanner/index.htm?O_T_com_mindfusion_commo...
  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #6 - Jan 17th, 2019 at 1:51pm
Print Post  
Hello Admin,

This are the values for the booking_data table:

book_id       booked_date

    1                January 18, 2019
    2                January 19, 2019
    3                January 20, 2019

This are the codes i'm using, i do some modification on the code you provide for me. I Already connect this to my database and it works. The only problem is,  it did not mark the other date value from the booked_date column except the date today


import com.mindfusion.common.DateTime;
import com.mindfusion.drawing.SolidBrush;
import java.awt.*;
import javax.swing.*;

import com.mindfusion.scheduling.*;
import com.mindfusion.scheduling.awt.AwtCalendar;
import com.mindfusion.scheduling.model.Style;
import com.mindfusion.drawing.Color;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.Date;

public class MainWindow extends JFrame {

    public static void main(String[] args) {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                MainWindow window = null;
                try {
                    window = new MainWindow();
                    window.setVisible(true);
                } catch (Exception exp) {
                }
            }
        });
    }

    public MainWindow() {
        conn = databaseconn.ConnecrDb();
        setDefaultCloseOperation(EXIT_ON_CLOSE);
        setSize(368, 362);
        setTitle("MindFusion.Scheduling Sample: Minimal Application");

        calendar = new AwtCalendar();
        calendar.setTheme(ThemeType.Light);

        Container cp = getContentPane();
        cp.setLayout(new BorderLayout());
        cp.add(calendar, BorderLayout.CENTER);

        establishConnection();
    }

    private void establishConnection() {

        Connection conn = null;
        try {
            // db parameters
            String sq1l = "select count(book_id) as total from booking_data";

            conn = databaseconn.ConnecrDb();
            pst = conn.prepareStatement(sq1l);
            rs = pst.executeQuery();

            if (rs.next()) {

                int total = Integer.parseInt((rs.getString("total")));
                System.out.println("total" + total);
                for (int i = 0; i <= total; i++) {

                    do {

                        try {
                            String a = Integer.toString(i);
                            String sq12 = "select booked_date from booking_data where book_id = '" + a + "'";

                            conn = databaseconn.ConnecrDb();
                            pst = conn.prepareStatement(sq12);
                            rs = pst.executeQuery();

                            if (rs.next()) {

                                Date date = rs.getDate("booked_date");
                                System.out.println(date);

                                long dateInLong = date.getTime();


                                Style style = new Style();
                                style.setBrush(new SolidBrush(new Color(102, 255, 102)));
                               
                                DateStyle dStyle = new DateStyle();
                                dStyle.setStyle(style);
                                dStyle.setFrom(new DateTime(dateInLong));
                                dStyle.setTo(new DateTime(dateInLong));

                                calendar.getDayStyles().add(dStyle);

                            }
                        } catch (SQLException e) {
                            System.out.println(e.getMessage());
                        }
                    } while (i > total);
                }
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }

    }

    private AwtCalendar calendar;
    private static final long serialVersionUID = 1L;
    Connection conn = null;
    ResultSet rs = null;
    reparedStatement pst = null;
}
  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #7 - Jan 18th, 2019 at 3:01am
Print Post  
Hello Admin,

Should i correct the format of the Dates inside the booked_date column based on the Format that will accept by the Calendar?

If yes, what is the format of the date that the calendar will accept.

Or How can i format the Calendar itself for which date format will be accepted?
  
Back to top
 
IP Logged
 
Iva_P
YaBB Moderator
*****
Offline


I Love MindFusion!

Posts: 41
Joined: Jun 19th, 2013
Re: JPlanner
Reply #8 - Jan 18th, 2019 at 10:57am
Print Post  
As posted earlier, here are the constructors, which you can use to create DateTime instances that the calendar will accept:

https://www.mindfusion.eu/onlinehelp/jplanner/index.htm?O_T_com_mindfusion_commo...

Take for example the first constructor:

https://www.mindfusion.eu/onlinehelp/jplanner/index.htm?C_com_mindfusion_common_...

public DateTime (
    int year,
    int month,
    int day
)

You can get the year, month and day of your Date with getDay, getTime and getYear and then construct MindFusion DateTime object like this:

DateTime dateTime = new DateTime(myDate.getYear(), myDate.getMonth(), myDay.getDay());
« Last Edit: Jan 21st, 2019 at 8:59am by Iva_P »  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #9 - Jan 24th, 2019 at 1:50am
Print Post  
Hello Admin,

Good day!

Admin where should i put that line of codes?
Can you give example again? please
  
Back to top
 
IP Logged
 
Iva_P
YaBB Moderator
*****
Offline


I Love MindFusion!

Posts: 41
Joined: Jun 19th, 2013
Re: JPlanner
Reply #10 - Jan 24th, 2019 at 9:10am
Print Post  
Replace this code:

==========
if (rs.next()) {

Date date = rs.getDate("booked_date");
System.out.println(date);

long dateInLong = date.getTime();

Style style = new Style();
style.setBrush(new SolidBrush(new Color(102, 255, 102)));

DateStyle dStyle = new DateStyle();
dStyle.setStyle(style);
dStyle.setFrom(new DateTime(dateInLong));
dStyle.setTo(new DateTime(dateInLong));

calendar.getDayStyles().add(dStyle);

}
==========



with this:

===========


if (rs.next()) {

Date date = rs.getDate("booked_date");
System.out.println(date);


Style style = new Style();
style.setBrush(new SolidBrush(new Color(102, 255, 102)));

DateStyle dStyle = new DateStyle();
dStyle.setStyle(style);
dStyle.setFrom(new DateTime(date.getYear(), date.getMonth(), date.getDay()));
dStyle.setTo(new DateTime(date.getYear(), date.getMonth(), date.getDay()));

calendar.getDayStyles().add(dStyle);

}

===============
  
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #11 - Jan 24th, 2019 at 10:31am
Print Post  
Hello Admin

Thank you for another help.

I'm still not having the result that i want from this code.
The Calendar only mark the current date.. nothing more nothing less. Maybe i need to change my target result.

How about this scenario.

If the date on my database is the date yesterday, the calendar should mark that date red. Or if the date on my database is equal to the current date , the calendar should mark the date green.

Then target result is to mark multiple dates by colors red and green

I'm not having trouble on fetching the dates from the database. the only problem is marking the dates on the calendar.

Thank you again admin for helping.
  
Back to top
 
IP Logged
 
Iva_P
YaBB Moderator
*****
Offline


I Love MindFusion!

Posts: 41
Joined: Jun 19th, 2013
Re: JPlanner
Reply #12 - Jan 25th, 2019 at 10:57am
Print Post  
Can you tell me which database you are using and export it as an *.sql file and send it to me? I send you an sql file with the exported table that I use including Create Table command. The database is MySQL.
  

booking_data.rar ( 0 KB | 191 Downloads )
Back to top
 
IP Logged
 
Capt.jack
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 10
Joined: Jan 12th, 2019
Re: JPlanner
Reply #13 - Jan 25th, 2019 at 6:38pm
Print Post  
Hello Admin

I use MSSQL SERVER for my database, and i just copied the data's from the sql file that you send.

This are the codes that i'm using

private void establishConnection() {

try {
String sq12 = "select book_date from booking_data";

conn = databaseconn.ConnecrDb();
pst = conn.prepareStatement(sq12);
rs = pst.executeQuery();

while (rs.next()) {

//Date from my database
Date date = rs.getDate("book_date");

// Current date for comparing
Date date1 = new Date();

// Converting Current Date to DateTime Value
DateTime dt1 = new DateTime(date1.getTime());

// Converting Date form my database to DateTime Value
DateTime dt = new DateTime(date.getTime());

System.out.println(dt + " " + dt1);

// Compare if both are equal, the calendar should mark the Date GREEN
if (dt1.compareTo(dt) == 0) {

DateStyle dStyle = new DateStyle();
dStyle.setFrom(dt);
dStyle.setTo(dt);
Style style = new Style();
style.setBrush(new SolidBrush(new Color(0, 120, 215)));
dStyle.setStyle(style);
calendar.getDayStyles().add(dStyle);

}

// Compare if the current date is less than the date from my database
// The Calendar should mark the Date RED
else if (dt1.compareTo(dt) < 0) {

DateStyle dStyle2 = new DateStyle();
dStyle2.setFrom(dt);
dStyle2.setTo(dt);
Style style2 = new Style();
style2.setBrush(new SolidBrush(new Color(255, 102, 102)));
dStyle2.setStyle(style2);
calendar.getDayStyles().add(dStyle2);

}

// Compare if the current date is greater than the date from my database
// The Calendar should mark the Date ORANGE
else if (dt1.compareTo(dt) > 0) {

DateStyle dStyle3 = new DateStyle();
dStyle3.setFrom(dt);
dStyle3.setTo(dt);
Style style3 = new Style();
style3.setBrush(new SolidBrush(new Color(51, 102, 255)));
dStyle3.setStyle(style3);
calendar.getDayStyles().add(dStyle3);

}

}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}

}


Than you for Helping Admin.
  

booking_date_SQLSERVER_.rar ( 0 KB | 189 Downloads )
Back to top
 
IP Logged
 
Iva_P
YaBB Moderator
*****
Offline


I Love MindFusion!

Posts: 41
Joined: Jun 19th, 2013
Re: JPlanner
Reply #14 - Jan 29th, 2019 at 1:32pm
Print Post  
Hi Jack,
I've connected the sample application to MS SQL to test it. It runs without problems and colors the three cells that represents the 3 days recorded in the DB. In order to see it running as it should I had to correct several things:

- enable TCP/IP for the MS SQL server
- enable Server browsing
- enable both SQL and Windows authentication. Only Windows authentication with Integrated security did not work with the latest JDBC driver for MS SQL provided by Microsoft
- grant SELECT right to my MS SQL user

In some of the cases I got the application running but no days were selected because the DB connection failed but the exception was handled and did not stop the application from executing.

What guided me were the error messages printed in the Console. As you can see from the code above we include System.out.println + the exception message in the catch block. Take a look at your console, perhaps there are also error messages there.

For your reference I quote the code that connects to the MS SQL Server on my computer:

==============

try {
// db parameters

String sql = "SELECT booked_date FROM booking_data";

String userName = "mindfusion";
String password = "mf123";

String url =
"jdbc:sqlserver://DESKTOP-NV9S0TU\\SQLEXPRESS;databaseName=bookings;";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(url, userName, password);

pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("booked_date"));
Date date = rs.getDate("booked_date");

DateStyle dStyle = new DateStyle();
dStyle.setFrom(new DateTime(date));
dStyle.setTo(new DateTime(date));
Style style = new Style();
style.setBrush(new SolidBrush(Color.pink));
dStyle.setStyle(style);

calendar.getDayStyles().add(dStyle);

}

} catch(SQLException e) {
System.out.println(e.getMessage());
} finally {
try{
if(conn != null)
conn.close();
}catch(SQLException ex){
System.out.println(ex.getMessage());
}
}

==================
-
  
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send TopicPrint