jQuery UI Widgets Forums Grid JQXGRID not showing any data from mySQL connection

This topic contains 2 replies, has 2 voices, and was last updated by  one 4 years, 6 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author

  • one
    Participant

    I am creating my first database stack with a mySQL database, eclipse, java hibernate, apache, and jqxgrid. When I load the page from the apache sever, I only see an empty jqxgrid without data. I’m not sure why the data isn’t populating the grid. Is there an obvious error with my JSP file below? A sample of the mySQL data is below.

    # id, PassengerID, Survived, Sex, Age, Fare, Pclass_1, Pclass_2, Pclass_3, Family_size, Title_1, Title_2, Title_3, Title_4, Emb_1, Emb_2, Emb_3
    ‘794’, ‘795’, ‘0’, ‘1’, ‘0.3125000000’, ‘0.0154115750’, ‘0’, ‘0’, ‘1’, ‘0.0000000000’, ‘1’, ‘0’, ‘0’, ‘0’, ‘0’, ‘0’, ‘1’
    ‘795’, ‘796’, ‘0’, ‘1’, ‘0.4875000000’, ‘0.0253743100’, ‘0’, ‘1’, ‘0’, ‘0.0000000000’, ‘1’, ‘0’, ‘0’, ‘0’, ‘0’, ‘0’, ‘1’
    ‘796’, ‘797’, ‘1’, ‘0’, ‘0.6125000000’, ‘0.0506104280’, ‘1’, ‘0’, ‘0’, ‘0.0000000000’, ‘0’, ‘1’, ‘0’, ‘0’, ‘0’, ‘0’, ‘1’

     <%@ page import="java.sql.*"%>
    <%@ page import="com.google.gson.*"%>
    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
        pageEncoding="ISO-8859-1"%>
    
    <%
        // (A) database connection
        // "jdbc:mysql://localhost:8080/titnd" - the database url of the form jdbc:subprotocol:subname
        // "dbusername" - the database user on whose behalf the connection is being made
        // "dbpassword" - the user's password
        Connection dbConnection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/titanicDB", "root", "EZOPS");
        // (B) retrieve necessary records from database
        Statement getFromDb = dbConnection.createStatement();
        ResultSet employees = getFromDb
                .executeQuery("SELECT PassengerID, Survived, Sex, Age, Fare FROM titanicD");
        // (C) format returned ResultSet as a JSON array
        JsonArray recordsArray = new JsonArray();
        while (employees.next()) {
            JsonObject currentRecord = new JsonObject();
            currentRecord.add("PassengerID",
                    new JsonPrimitive(employees.getString("PassengerID")));
            currentRecord.add("Survived",
                    new JsonPrimitive(employees.getString("Survived")));
            currentRecord.add("Sex",
                    new JsonPrimitive(employees.getString("Sex")));
            currentRecord.add("Age",
                    new JsonPrimitive(employees.getString("Age")));
            currentRecord.add("Fare",
                    new JsonPrimitive(employees.getString("Fare")));
            recordsArray.add(currentRecord);
        }
        // (D)
        out.print(recordsArray);
        out.flush();
    %>
    
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="ISO-8859-1">
    <title>Insert title here</title>
    
    </head>
    <body>
    
    </body>
    </html>
    • This topic was modified 4 years, 6 months ago by  one. Reason: adding sample data from mysql database

    Peter Stoev
    Keymaster

    Hi one,

    We would suggest you to look at https://www.jqwidgets.com/jquery-widgets-demo/demos/php/index.htm and navigate to the Grid’s examples in that demo page. They show how to use the Grid with MySQL.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    https://www.jqwidgets.com


    one
    Participant

    Hello. I added the missing code from the demo, but it still isn’t working. Any advice will be appreciated. This is my first attempt to use JQwidgets. My updated jsp file is below.

    <!DOCTYPE html>
    <html lang='en'>
    <head>
    <meta charset="ISO-8859-1">
    <title>titanic</title>
    <link type="text/css" rel="Stylesheet" href="css/jqx.base.css" />
        <script type="text/javascript" src="js/jquery.js"></script>
        <script type="text/javascript" src="js/jqxcore.js"></script>
        <script type="text/javascript" src="js/jqxdata.js"></script>
        <script type="text/javascript" src="js/jqxbuttons.js"></script>
        <script type="text/javascript" src="js/jqxscrollbar.js"></script>
        <script type="text/javascript" src="js/jqxmenu.js"></script>
        <script type="text/javascript" src="js/jqxcheckbox.js"></script>
        <script type="text/javascript" src="js/jqxlistbox.js"></script>
        <script type="text/javascript" src="js/jqxdropdownlist.js"></script>
        <script type="text/javascript" src="js/jqxgrid.js"></script>
        <script type="text/javascript" src="js/jqxgrid.selection.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                var source = {
                    datatype: "json",
                    datafields: [{
                        name: 'PassengerID'},
                        {name: 'Survived'},
                        {name: 'Sex'},
                        {name: 'Age'},
                        {name: 'Fare'}
                        ],
                    id: 'PassengerID',
                    url: 'select-data.jsp',
                    async: true
                };
                var dataAdapter = new $.jqx.dataAdapter(source);
                $("#jqxgrid").jqxGrid({
                    width: 740,
                    autoheight: true,
                    source: dataAdapter,
                    columns: [{
                        text: 'PassengerID',
                        datafield: 'PassengerID',
                        width: 100
                    }, {
                        text: 'Survived',
                        datafield: 'Survived',
                        width: 100
                    }, {
                        text: 'Sex',
                        datafield: 'Sex',
                        width: 180
                    }, {
                        text: 'Age',
                        datafield: 'Age',
                        width: 180
                    }, {
                        text: 'Fare',
                        datafield: 'Fare',
                        width: 180
                    }]
                });
            });
        </script>
    </head>
    <body>
    	<div id="jqxgrid"></div>
    </body>
    </html>
    
    <!--grid_data.php
    	#Include the connect.php file
    	include ('connect.php');
    // Connect to the database
    // connection String
    $mysqli = new mysqli($//localhost:3306, $root, $EZOPS, $titanicDB);
    /* check connection */
    if (mysqli_connect_errno())
    	{
    	printf("Connect failed: %s\n", mysqli_connect_error());
    	exit();
    	}
    // get data and store in a json array
    $from = 0;
    $to = 30;
    $query = "SELECT PassengerID, Survived, Sex, Age, Fare FROM titanicD"; // LIMIT ?,?";
    $result = $mysqli->prepare($query);
    $result->bind_param('ii', $from, $to);
    $result->execute();
    /* bind result variables */
    $result->bind_result($PassengerID, $Survived, $Sex, $Age, $Fare);
    /* fetch values */
    while ($result->fetch())
    	{
    	$orders[] = array(
    		'PassengerID' => $PassengerID,
    		'Survived' => $Survived,
    		'Sex' => $Sex,
    		'Age' => $Age,
    		'Fare' => $Fare
    	);
    	}
    echo json_encode($orders);
    /* close statement */
    $result->close();
    /* close connection */
    $mysqli->close();
    -->
Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.