If you don’t know Persevere yet, check out its impressive list of features (JSONPath/JSON-RPC/Comet support, etc…).
Here is a short tutorial to expose your MySQL tables through the persevere JSON REST interface.
- Download persevere and unzip it.
- Download the JDBC driver for MySQL and put the jar file into the “persevere/lib” folder.
- Create a new file in “persevere/WEB-INF/config” named “myDatabase.json”
- We will now configure the data source: (replace “myDatabase” and “myTable” by your database and table names.)
({
"id":"myDatabase.json",
"sources": [
{
"name":"myTable",
"sourceClass":"org.persvr.datasource.DatabaseTableDataSource",
"driver":"com.mysql.jdbc.Driver",
"connection":
"jdbc:mysql://localhost/myDatabase?user=myDbUser&pass=",
"table":"myTable",
"idColumn":"id",
"dataColumns": [
"lastname",
"firstname",
...and so on with your table columns
],
"schema":{
"data":{"$ref":"../myTable/"}Don’t forget to change your database user name and password !
}
}
]
} - run persevere (“java -jar start.jar”) and visit the following pages:
- http://localhost:8080/myTable
- http://localhost:8080/myTable/
- http://localhost:8080/myTable/1
What to do next ?
- Add other tables: “sources” is an array !
- Use UTF8: Adds the following at the end of the connection string:
&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8 - Add methods to the class or to its prototype (take a look at WEB-INF/config/examples.json)
- Write a script to auto-generate the config file from a database structure (and post it on the persevere forum)
- Read the Persevere Javascript Client Documentation and start coding applications !
Have fun !
Hi Neyric,
Impressive — Persevere. I’m trying to get mysql going, but I’m totally new to JDBC. What is this “sourceClass”-thing? Does it need to stay like that on my localhost?
“sourceClass”:”org.persvr.datasource.DatabaseTableDataSource”
When I go http://localhost:8080/cultivar, I get “Access denied for user ‘root’@'localhost’ (using password: NO)”
Much appreciated, and regards,
CJ
@CJ:
Hi,
The “sourceClass” property tells Persevere which Datastore to use. In our case (MySQL), we tell Persevere to use a relational database, so you should leave it as it is.
Your error is a permission issue. The only line to change is the following :
“jdbc:mysql://localhost/myDatabase?user=myDbUser&pass=yourPasswordHere”
where you should replace:
* “localhost” by your mysql server ip/dns (or leave it unchanged)
* “myDatabase” by your mysql database
* “myDbUser” by your database user
* “yourPasswordHere” by the user’s password
To manage the users in mysql, you could use phpMyAdmin or the CREATE USER sql statement (http://dev.mysql.com/doc/refman/5.1/en/create-user.html)
Hope this help.
Thanks Neyric,
I managed to get it right. Can’t remember now exactly what I missed.
Hi thanks for the tutorial!
I’m getting some trouble with jdbc thought.
I get when i start persvr:
GRAVE: Error attempting to initialize the data source Phone in file vi.js
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
So I thought I might have misplaced the “mysql-connector-java-5.1.7-bin”, still its in the persevere/lib directory.
Some help would be appreciated
The mysql-connector-java library is a single jar file that needs to be placed to the persevere/lib directory and it is good to list in .classpath xml config file. Append the following line:
Hope this helps!
“”
I don’t know if the driver has been changed recently but I got an error…decided not to read the jdbc documentation — what a bad idea — it took me ages to figure out i was getting no where
my error
Access denied for user ‘root’@’localhost’ (using password: NO)
fixed line – change pass to password
“jdbc:mysql://localhost/myDatabase?user=myDbUser&password=yourPasswordHere”
This looks very promising, especially the binding of foreign keys:
sources: [
{
...
dataColumns: [
...
{
foreignSource:"Name of foreign database data source",
databaseColumn:"Name of foreign key column",
relationshipType:"many-to-one",
objectColumn:"Name of object property to map to"
}
]
…
}]
Two question though:
- Is it possible to use inheritance with SQL sources, i.e. Table B inherits from Table A (Table B contains all fields of Table A plus its own)
- Is it possible to specify which references (to foreign rows) that should be automatically resolved (i.e. not lazy loaded)
It seems I can answer the second question myself:
dataColumns:[
...
],
“schema”:{
properties: {
nameOfObjectColumn: {lazy: false}
}
}
Hi,
the MySql JDBJ database driver file
has to be in the persevere/WEB-INF/lib directory and
*not* in persevere/lib
Cheers
Hi.
I was wondering if there is a way to avoid the redundant “sourceClass” “driver” and “connection” info for each table.
Is there any way to inherit those properties ?
Thanks !
hi, my error :
2010-4-14 15:26:19 org.persvr.remote.PersevereFilter$DefaultHandler call waring: org.mozilla.javascript.EcmaError: AccessError: Writing to Customer/1 is not permitted, you do not have write permission
Why? Thanks!!
Hi , neyric
i use mysql. success! but ,Have a wrong When I set column!
my error:
Error: Unable to load /persevere1.0/userstest123/1 status:500
??: java.lang.RuntimeException: java.lang.RuntimeException: The column sex is
not registered as a valid column in this table
help me ! Thanks!!
@yww : you either don’t have this column in the database or in the “datacolumns” of your config…
hi , neyric
Thanks!
Can I add a new column in the web(explorer.html) without modify the config or database?
This can be?
Persevere support to add new column ?
Persevere whether support to add new column in the web?explorer.html??
Hi, i have a question.
Any idea to avoid this exception?
java.lang.RuntimeException: No new unique key was generated from creating a new object
i have a my sql table with primary non incremental key
i hope you can helpme
thanks