ActiveJDBC – as ORM : Insert/Update/Delete/List Example
| Insert/Update/Delete/List Example |
Here, in this example we are using insert, list record, limit records, sort records and pagination or records through ActiveJDBC. Updating and deleting records already explained in previous chapter.
Paginating for the web
Although limit, offset and orderBy themselves are quite simple and powerful methods, ActiveJDBC also provides a convenience class called Paginator especially designed for web applications:
Paginator p = new Paginator(Travel.class, 5, "tagent like ?", "%lastminute%").orderBy("tagent desc");
List<Travel> items1 = p.getPage(1);
List<Travel> items2 = p.getPage(2);
The instances of this class are super lightweight and usually attached to a session. An instance of this class can be queried for a current page displayed:
int currentPage = paginator.getCurrentPage();
and for page count like this:
int pageCount = paginator.getPageCount();
Using this class in a context of a web application makes it trivial to build paging through resultsets on web pages.
Select build.xml file from above project and run it.
Travel.java
package com.javaskool;
import org.javalite.activejdbc.Model;
import org.javalite.activejdbc.annotations.Table;
/**
*@author Anuj Verma
*/
@Table("traveltab")
//@IdName("travelId")
//@DbName("JSActiveJDBCDB")
public class Travel extends Model{
String pnr;
public String getPnr() {
return getString("pnr");
}
}
TestManager.java
package com.javaskool;
import java.util.List;
import org.javalite.activejdbc.Base;
import org.javalite.activejdbc.Paginator;
/**
*@author Anuj Verma
*/
public class TestManager {
public static void main(String[] args) {
Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/JSActiveJDBCDB", "root", "root");
//Inserting Record
Travel t = new Travel();
t.set("pnr","BA0123");
t.set("name","bond");
t.set("tagent","lastminute");
t.set("phone",99999999);
t.set("itenerary","Hotel");
t.set("time",5);
t.saveIt();
System.out.println("Travel record inserted.. successfully");
//Finding single Record
System.out.println("Finding Travel record .. Wait...");
Travel t1 = (Travel)Travel.findFirst("pnr = ?", "BA0123");
String name=(String)t1.get("name");
String phone=(String)t1.get("phone");
System.out.println("Name : "+name);
System.out.println("Phone : "+phone);
//Finding All Record
System.out.println("Finding 5 Travel record .. Wait...");
List<Travel> alltrvel = Travel.findAll().limit(5);
System.out.println("id \t pnr \t name \t tagent \t phone \t itenerary \t time");
for(Travel x:alltrvel)
{
Integer id1=(Integer)x.get("id");
String name1=(String)x.get("name");
String phone1=(String)x.get("phone");
String pnr1=(String)x.get("pnr");
String tagent1=(String)x.get("tagent");
String itenerary1=(String)x.get("itenerary");
Integer time1=(Integer)x.get("time");
System.out.println(id1+" \t "+pnr1+" \t "+name1+" \t "+tagent1+" \t "+phone1+" \t "+itenerary1+" \t "+time1);
}
System.out.println("---Finding 5 Travel record from 4th position , since offset is 3.. Wait...");
List<Travel> alltrvelOffset = Travel.findAll().limit(1).offset(3);
System.out.println("id \t pnr \t name \t tagent \t phone \t itenerary \t time");
for(Travel x:alltrvelOffset)
{
Integer id1=(Integer)x.get("id");
String name1=(String)x.get("name");
String phone1=(String)x.get("phone");
String pnr1=(String)x.get("pnr");
String tagent1=(String)x.get("tagent");
String itenerary1=(String)x.get("itenerary");
Integer time1=(Integer)x.get("time");
System.out.println(id1+" \t "+pnr1+" \t "+name1+" \t "+tagent1+" \t "+phone1+" \t "+itenerary1+" \t "+time1);
}
System.out.println("Finding 5 Travel record from 4th position , since offset is 3 in Asc of name.. Wait...");
//List<Travel> alltrvelAsc = Travel.findAll().limit(10).offset(3).orderBy("id asc");
List<Travel> alltrvelAsc = Travel.findAll().limit(10).orderBy("id asc");
System.out.println("id \t pnr \t name \t tagent \t phone \t itenerary \t time");
for(Travel x:alltrvelAsc)
{
Integer id1=(Integer)x.get("id");
String name1=(String)x.get("name");
String phone1=(String)x.get("phone");
String pnr1=(String)x.get("pnr");
String tagent1=(String)x.get("tagent");
String itenerary1=(String)x.get("itenerary");
Integer time1=(Integer)x.get("time");
System.out.println(id1+" \t "+pnr1+" \t "+name1+" \t "+tagent1+" \t "+phone1+" \t "+itenerary1+" \t "+time1);
}
System.out.println("Finding 5 Travel record of travelocity by using Paginator ");
Paginator p = new Paginator(Travel.class, 5, "tagent like ?", "%lastminute%").orderBy("tagent desc");
List<Travel> items1 = p.getPage(1);
List<Travel> items2 = p.getPage(2);
System.out.println("Page1");
for(Travel x:items1)
{
Integer id1=(Integer)x.get("id");
String name1=(String)x.get("name");
String phone1=(String)x.get("phone");
String pnr1=(String)x.get("pnr");
String tagent1=(String)x.get("tagent");
String itenerary1=(String)x.get("itenerary");
Integer time1=(Integer)x.get("time");
System.out.println(id1+" \t "+pnr1+" \t "+name1+" \t "+tagent1+" \t "+phone1+" \t "+itenerary1+" \t "+time1);
}
System.out.println("Page2");
for(Travel x:items2)
{
Integer id1=(Integer)x.get("id");
String name1=(String)x.get("name");
String phone1=(String)x.get("phone");
String pnr1=(String)x.get("pnr");
String tagent1=(String)x.get("tagent");
String itenerary1=(String)x.get("itenerary");
Integer time1=(Integer)x.get("time");
System.out.println(id1+" \t "+pnr1+" \t "+name1+" \t "+tagent1+" \t "+phone1+" \t "+itenerary1+" \t "+time1);
}
int currentPage = p.getCurrentPage();
System.out.println("Current Page is :"+currentPage);
long pageCount = p.getCount();
System.out.println("Total Page is :"+pageCount);
Base.close();
}
}
build.xml
<?xml version="1.0" encoding="UTF-8"?>
<project basedir="." default="run" name="02_activeJDBCwithLimitAndSort">
<path id="myclasspath">
<pathelement location="D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\bin"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/activejdbc-1.4.6.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/activejdbc-instrumentation-1.4.6.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/javalite-common-1.4.6.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/javassist-3.16.1-GA.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/junit-4.8.2.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/mysql-connector-java-5.0.4.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/slf4j-api-1.6.4.jar"/>
<pathelement location="D:/thingstoDO/activeJDBC/jarfile/slf4j-simple-1.6.4.jar"/>
</path>
<property name="classes" value="D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\bin"/>
<target name="build">
<delete dir="${classes}" failonerror="true"/>
<mkdir dir="${classes}"/>
</target>
<target name="compile" depends="build">
<javac srcdir="src" destdir="${classes}" debug="on" optimize="off" deprecation="off" includeantruntime="false">
<classpath refid="myclasspath"/>
</javac>
</target>
<target name="instrument" depends="compile">
<java classname="org.javalite.instrumentation.Main">
<sysproperty key="outputDirectory" value="${classes}"/>
<classpath refid="myclasspath"/>
</java>
</target>
<target name="run" depends="instrument">
<java classname="com.javaskool.TestManager" failonerror="true" fork="true">
<jvmarg value="-Dactivejdbc.log"/>
<classpath refid="myclasspath"/>
</java>
</target>
</project>
Database Script
create database JSActiveJDBCDB;
use JSActiveJDBCDB;
create table travelTab
(
id int primary key auto_increment,
pnr varchar(20),
name varchar(30),
tagent varchar(30),
phone long,
itenerary varchar(20),
time int
);
INSERT INTO `traveltab` VALUES
(1,'BA0121','James','travelocity','1299889988','CAR',4),
(2,'BA0122','raj','lastminute','99999999','Hotel',5),
(3,'BA0123','anuj','cleartrip','99999999','Flight',6),
(4,'BA0124','bond','travelocity','99999999','Hotel',5),
(5,'BA0125','John','travelocity','1299889988','CAR',4),
(6,'BA0126','Charlie','makemytrip','99999999','Hotel',5),
(7,'BA0127','Ravi','cleartrip','99999999','Airline',5),
(8,'BA0128','amit','lastminute','99999999','Flight',5),
(9,'BA0129','Jolly','travelocity','1299889988','CAR',4),
(10,'BA0120','jocky','cleartrip','99999999','Hotel',5);
Console output
Buildfile: D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\build.xml
build:
[delete] Deleting directory D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\bin
[mkdir] Created dir: D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\bin
compile:
[javac] Compiling 2 source files to D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\bin
instrument:
**************************** START INSTRUMENTATION ****************************
Directory: D:\thingstoDO\workspace4activeJDBC\02_activeJDBCwithLimitAndSort\bin
Found model: com.javaskool.Travel
Instrumented class: com.javaskool.Travel in directory: /D:/thingstoDO/workspace4activeJDBC/02_activeJDBCwithLimitAndSort/bin/
**************************** END INSTRUMENTATION ****************************
run:
141 [main] INFO org.javalite.activejdbc.ConnectionsAccess - Attaching connection: com.mysql.jdbc.Connection@b2db5b
141 [main] INFO org.javalite.activejdbc.ConnectionsAccess - Opened connection:com.mysql.jdbc.Connection@b2db5b named: default on thread: Thread[main,5,main]
156 [main] INFO org.javalite.activejdbc.Configuration - Load models from: file:/D:/thingstoDO/workspace4activeJDBC/02_activeJDBCwithLimitAndSort/bin/activejdbc_models.properties
187 [main] INFO org.javalite.activejdbc.Registry - Registered model: class com.javaskool.Travel
203 [main] INFO org.javalite.activejdbc.Registry - Fetched metadata for table: traveltab
Travel record inserted.. successfully
Finding Travel record .. Wait...
281 [main] INFO org.javalite.activejdbc.DB - Query: "INSERT INTO traveltab (itenerary, time, phone, tagent, name, pnr) VALUES (?, ?, ?, ?, ?, ?)", with parameters: <Hotel>, <5>, <99999999>, <lastminute>, <bond>, <BA0123>, took: 78 milliseconds
281 [main] INFO org.javalite.activejdbc.LazyList - Query: "SELECT * FROM traveltab WHERE pnr = ? LIMIT 1", with parameters: <BA0123>, took: 0 milliseconds
281 [main] INFO org.javalite.activejdbc.LazyList - Query: "SELECT * FROM traveltab LIMIT 5", took: 0 milliseconds
297 [main] INFO org.javalite.activejdbc.LazyList - Query: "SELECT * FROM traveltab LIMIT 1 OFFSET 3", took: 0 milliseconds
297 [main] INFO org.javalite.activejdbc.LazyList - Query: "SELECT * FROM traveltab ORDER BY id asc LIMIT 10", took: 0 milliseconds
297 [main] INFO org.javalite.activejdbc.LazyList - Query: "SELECT * FROM traveltab WHERE tagent like ? ORDER BY tagent desc LIMIT 5 OFFSET 0", with parameters: <%lastminute%>, took: 0 milliseconds
297 [main] INFO org.javalite.activejdbc.LazyList - Query: "SELECT * FROM traveltab WHERE tagent like ? ORDER BY tagent desc LIMIT 5 OFFSET 5", with parameters: <%lastminute%>, took: 0 milliseconds
312 [main] INFO org.javalite.activejdbc.DB - Query: "SELECT COUNT(*) FROM traveltab WHERE tagent like ?", with parameters: <%lastminute%>, took: 15 milliseconds
312 [main] INFO org.javalite.activejdbc.DB - Closed connection: com.mysql.jdbc.Connection@b2db5b
312 [main] INFO org.javalite.activejdbc.ConnectionsAccess - Detached connection: default
Name : anuj
Phone : 99999999
Finding 5 Travel record .. Wait...
id pnr name tagent phone itenerary time
1 BA0121 James lastminute 1299889988 CAR 4
2 BA0122 raj lastminute 99999999 Hotel 5
3 BA0123 anuj cleartrip 99999999 Flight 6
4 BA0124 bond lastminute 99999999 Hotel 5
5 BA0125 John travelocity 1299889988 CAR 4
---Finding 5 Travel record from 4th position , since offset is 3.. Wait...
id pnr name tagent phone itenerary time
4 BA0124 bond lastminute 99999999 Hotel 5
Finding 5 Travel record from 4th position , since offset is 3 in Asc of name.. Wait...
id pnr name tagent phone itenerary time
1 BA0121 James lastminute 1299889988 CAR 4
2 BA0122 raj lastminute 99999999 Hotel 5
3 BA0123 anuj cleartrip 99999999 Flight 6
4 BA0124 bond lastminute 99999999 Hotel 5
5 BA0125 John travelocity 1299889988 CAR 4
6 BA0126 Charlie makemytrip 99999999 Hotel 5
7 BA0127 Ravi cleartrip 99999999 Airline 5
8 BA0128 amit lastminute 99999999 Flight 5
9 BA0129 Jolly travelocity 1299889988 CAR 4
10 BA0120 jocky lastminute 99999999 Hotel 5
Finding 5 Travel record of travelocity by using Paginator
Page1
1 BA0121 James lastminute 1299889988 CAR 4
2 BA0122 raj lastminute 99999999 Hotel 5
4 BA0124 bond lastminute 99999999 Hotel 5
8 BA0128 amit lastminute 99999999 Flight 5
10 BA0120 jocky lastminute 99999999 Hotel 5
Page2
17 BA0123 bond lastminute 99999999 Hotel 5
Current Page is :2
Total Page is :6
BUILD SUCCESSFUL
Total time: 2 seconds
| Downloads Example |
Recent Comments