Need Suggestion (Java/SQL)

[puneetkay]

Hello Everyone,

Few days ago, There was active thread on creating own DBMS in Java. Its not an easy task so i decided to create Object-Relational Mapping Framework and i have done so.

I have tested all methods and its working perfectly. Now i have decided to make it available for all users.

Before putting it on my website for download. I want to expand it and add more features into it.

You can get the Overview of Pikk Framework : Here

I have named it Pikk. Currently it supports :

* Java classes to Database tables mapping.
* Java data types to SQL data types.
* One-2-Many and Many-2-Many relationship between classes.
* saveObject() , saveObjects() and getObjects() for inserting, updating and retrieving data from database.
* sum() , count() , max() methods.

EG :

Person extends DataObject
------------------
+ String name;
+ int age;
+ String city;

mappingObj.addTable(Person.class,"PersonTable");

PikkMapping file will be autogenerated.

Table will be created "PersonTable" with attributes id,name,age,city.

Person p = new Person();
p.name = "Test";
p.age = "10";
p.city = "MyCity";

connectionObj.saveObject(p);

Insert query will be generated.

This is how it works! I tried my best to make it easiest.

Please give me your suggestions and reviews.

Regards,

[verruckt24]

Hi Puneet,
I haven't gone through your ORM framework, I need sometime for that, but on first thoughts I would like to suggest you another very popular ORM framework - Hibernate - which you might have heard of. This is a project similar to what you have started. Since you talk about adding features to your framewor, going through this you would be able to know a lot of features that a full production release of such a system might have and then deduce a set of core features that you need to have in such a framework/system.

[puneetkay]

Hie verruckt24,

Yes, even i have used Hibernate many times.

Now im working on supporting Blob/Clob and supporting multiple collections.

Let me explain, Where i actually need help:

DataObject classes :

import com.puneetk.pikk.DataObject;

public class Person extends DataObject {
	// DO classes must extends com.puneetk.pikk.DataObject class
	// DataObject already have variable (Long id) so need to declare another one.
	public String name;
	public String phn;
	public int age;
	// If you want, you can also generate getters/setters.
	// All variable must be PUBLIC.
}

public class Manager extends Person {
	public String dept;
	public ArrayList<Employee> empList; // for relationship
	public int numbers;	
}

public class Employee extends Person{
	public int empPos;
	public String dept;
	public long managerId; // will work as foreign key, 
	
}

Demo Class Coding :

package com.puneetk.pikk.demo;

import java.util.ArrayList;
import java.util.Iterator;

import com.puneetk.pikk.Configuration;
import com.puneetk.pikk.Connection;
import com.puneetk.pikk.Mapping;
import com.puneetk.pikk.Parameter;
import com.puneetk.pikk.Pikk;
import com.puneetk.pikk.Table;
import com.puneetk.pikk.demo.mydo.Employee;
import com.puneetk.pikk.demo.mydo.Manager;
import com.puneetk.pikk.demo.mydo.Manager2;
public class TestPikk {
	public static void main(String[] args) {
		try{
			//// in this part of code, We need to set configs
			Configuration configObj = new Configuration();
			configObj.setDatabasePath("localhost/test"); // path to DB
			configObj.setDbms(Configuration.MYSQL); // this demo code is using MySQL
			configObj.setPassword("Pass#1234"); // Password 
			configObj.setShowCommands(true); // show queries and error generate by Pikk
			configObj.setUsername("root"); // Username
			/// done!
			
			// now create the main Pikk Object.
			
			// this class have one and the only constructor with Configration object as parameter.
			Pikk pikkObj = new Pikk(configObj);
			
			// Mapping is an interface. We need it to create tables and PikkMapping file.
			Mapping mappingObj = pikkObj.getMapping();
			// addTable method is used to add tables to Mapping.
			mappingObj.addTable(new Table(Employee.class,"Employee"));
			// Table Constructor : Table(java.lang.Class DOClass, String TableName)
			mappingObj.addTable(new Table(Manager.class,"Manager"));
			// Once all tables added, invoked creatingMapping() method.
			mappingObj.createMapping();
			// Tables are created and so the PikkMapping file.
			/// run mapping code part only once. Or this will drop and re-create tables everytime.
			
			
			// Connection is an Interface, to work tables.
			Connection connectionObj = pikkObj.getConnection();
			
			// Must invoke loadMapping() method before using any Connection method. 
			connectionObj.loadMapping();
			
			// Manager (subclass of Person extends DO)class Object.
			Manager mn = new Manager();
			mn.setAge(20);
			mn.setDept("Selling");
			mn.setName("Kalra");
			mn.setPhn("N/A");
			mn.setNumbers(5);
			
			ArrayList<Employee> arr = mn.getEmpList();
			
			// Employee (subclass of Person extends DO)class Object.
			Employee e = new Employee();
			e.setAge(17);
			e.setDept("Selling-E");
			e.setEmpPos(1);
			e.setName("Puneet");
			e.setPhn("N/A");
			
			// add to ArrayList
			arr.add(e);
			
			Employee e2 = new Employee();
			e2.setAge(21);
			e2.setDept("Selling-E");
			e2.setEmpPos(2);
			e2.setName("Puneet");
			e2.setPhn("N/A");
			arr.add(e2);
			
			// now invoking saveObject(Object doSubClassObject) for inserting to DB.
			connectionObj.saveObject(mn);
			
			// addParameter() method, for Parameterized queries.
			connectionObj.addParameter(new Parameter("@name","Puneet",false));
			// Parameter Constructor : Parameter(String paramName, Object paramValue, boolean overwrite)
			
			System.out.println("Sum : "+connectionObj.sum(Employee.class, "age","name=@name"));
			System.out.println("Count : "+connectionObj.count(Manager.class));
			System.out.println("Count Condition : "+ connectionObj.count(Employee.class, "managerId=1"));
			
			
			System.out.println("****** RETRIEVING DATA");
			
			ArrayList ar = connectionObj.getObjects(Manager.class, "");
			Iterator it = ar.iterator();
			while(it.hasNext()){
				Manager m2 = (Manager)it.next();
				System.out.println(m2);
				for(Employee e3 : m2.getEmpList()){
					System.out.println(e3);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println("Error!");
		}
	}
}

Output :

NOTICE : Session Created
DROP TABLE Employee
CREATE TABLE Employee ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(1024) NOT NULL , phn VARCHAR(1024) NOT NULL , age INT NOT NULL , empPos INT NOT NULL , dept VARCHAR(1024) NOT NULL , managerId BIGINT NOT NULL ,  PRIMARY KEY(id) ) 
DROP TABLE Manager
CREATE TABLE Manager ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(1024) NOT NULL , phn VARCHAR(1024) NOT NULL , age INT NOT NULL , dept VARCHAR(1024) NOT NULL , numbers INT NOT NULL ,  PRIMARY KEY(id) ) 
NOTICE : Pikk Mapping File Generated.
File Path : E:\Pikk\PikkMapping.pk
NOTICE : Pikk Mapping File Loaded
INSERT INTO Manager (dept,numbers,name,phn,age) VALUES ('Selling','5','Kalra','N/A','20')
INSERT INTO Employee (empPos,dept,managerId,name,phn,age) VALUES ('1','Selling-E','1','Puneet','N/A','17')
INSERT INTO Employee (empPos,dept,managerId,name,phn,age) VALUES ('2','Selling-E','1','Puneet','N/A','21')
SET @name = 'Puneet'
SELECT SUM(age) FROM Employee WHERE name='Puneet'
Sum : 38.0
SELECT COUNT(id) FROM Manager
Count : 1
SELECT COUNT(id) FROM Employee WHERE managerId=1
Count Condition : 2
****** RETRIEVING DATA
SELECT * FROM Manager
SELECT * FROM Employee WHERE managerId='1'
1 - Kalra - N/A
1 - 1 - Selling-E
2 - 2 - Selling-E

I need you to suggest me more methods for Connection Interface. I want that user mainly focus of their programming logic instead of dividing in SQL queries and ResultSet.

Waiting for your reply,
Regards,