CODEWITHSUNDEEP
mysqlgrailsgroovy
engineerKev
engineerKev

Reputation: 335

making a table with data from mySQL using groovy grails

I'm working on a grails webapp that will "grab" data from a mySQL database, which I've already set up and connected to using groovy, and display said data(total number of running threads right now) in a table and also in a graph. I have searched all over and I have figured out that: 

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydatabase",
        "root", pw, "com.mysql.jdbc.Driver")
    sql.eachRow("SELECT second, threads FROM threadcount " ){
        println it.seconds
        println it.threads
            }

will grab my data from my table " threadcount" and print it in the console. The code above is what I have written in my controller file right now. That is great and it works, but I want to be able to display it on a webpage. I've also gone through some tutorials for grails and have learned that another way to grab the data from my db is to do the following.

    def current = { 
    def allProjects = Threadcount.list()
    [dbContent:allProjects]

}

In my grails view file I have this:

<html>
<head>
  <title>Active Threads</title>
</head>
<body>
   <table border = "1">
   <g:each in = "${dbContent}" status = "i" var = "thisProject">
     <tr>
         <th>SECOND</th>
         <th>THREADS</th>
     </tr>
     <tr>
          <td>${thisProject.second}</td>
          <td>${thisProject.threads}</td>
     </tr>
   </g:each>
   </table>
</body>
</html>

but no matter what I do or whether I use sql.query, sql.row, sql.eachRow, or Threadcount.list() I am unable to display all of my data. I have 14 entrees in my database. With the Threadcount.list() method I get just the first row of my mySQL table, and when I use any of the sql. methods I don't even get the first row to show up. I just get the following error message:

No such property: dbConent2 for class: threadsapp.ThreadcountController

As for my question(s). What am I doing wrong? Or what is the better way to do this? and why does 

dbContent2<<it.toRowResult()
[dbContent:dbContent2]

not work either?

At this point I don't know what else to try, which is why I came here. Thanks in advance, and sorry for the long and wordy question

PS here are some of the websites where I read about groovy and sql
groovy sql
groovy database features

Upvotes: 0

Views: 2529

Answers (1)

Alidad
Alidad

Reputation: 5538

You said:

grails adds "version" and "id" for no reason. So I deleted the domain file.

Grails adds ID and version for reason, if you don't need them you can configure Grails not to add them. However, I recommend to follow Grails convention as its the result of best practices and experience which resulted into the framework.

For version it's as easy as: 

class Threadcount {
    …
    static mapping = {
        version false
    }
}

For ID you can use existing unique key or create composite key here. 

 static mapping = {
        id composite: ['firstName', 'lastName']
    }

for other combinations look here

Or if you already have a build database Grails allows you to map to it without any issue here.

Basically you can tell Grails not to create any database objects just use what I have, look into dataSource.groovy.

 development {
        dataSource {
            dbCreate = "validate" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
        }
    }

By not using Grails domain classes you are causing yourself so many problems. Those tutorials are following Grails convention and assumes you are doing the basic MVC model.

I assume you had this line when you already had your domains, if you properly map your domains and tables allProjects should return all the records in Threadcount Table. 

def current = { 
    def allProjects = Threadcount.list()
    [dbContent:allProjects]

}

To resolve your issue I suggest first properly map your domains using one of the many tutorials showing basic domain operations, this will also eliminate executing direct queries against the database and helps you to iterate your data very effectively.

Upvotes: 1

Related Questions