WORKING WITH CSQL IN NETBEANS

adorableautomaticΛογισμικό & κατασκευή λογ/κού

15 Αυγ 2012 (πριν από 4 χρόνια και 10 μήνες)

210 εμφανίσεις

WORKING WITH CSQL ON NETBEANS
Introduction
CSQL Main Memory Database and Cache is an easily accessible and powerful database management

system, which includes the CSQL Main Memory Database, and Cache to MySQL, PostgreSQL,

Oracle, Sybase and DB2 disk based database management system. It is used to cache frequently

accessed data from disk based database systems in the middle (application server) tier alongside

applications and store data in main memory rather than disk, allowing it to be retrieved very quickly. It

is used for applications where extremely fast response times are critical.
CSQL and its associated suite of products have a single design objective and that is undisruptive

performance, close to 30 times faster than traditional database options and it has achieved these

performances because it has been designed from scratch to achieve performance.
Supported Feature Summary

Full ACID Support

Highly Concurrent

Indexes

Recovery

Isolation Level Support

Constraints

Statement Cache

SQL 92 Entry Level

ODBC 3.0

JDBC 3.0

PHP on top of ODBC

Network and more.
CSQL JDBC 3.0 driver
A JDBC Type-II driver, supports following classes and interfaces of
java.sql
and
javax.sql
package.
Important classes

java.sql.Date

java.lang.DriverManager

java.sql.Time
Important Interfaces

java.sql.Driver

java.sql.Connection

java.sql.PreparedStatement

java.sql.ResultSet

java.sql.Statement

java.sql.SqlException

java.sql.ParameterMetadata

java.sql.ResultSetMetadata

java.sql.DatabaseMetadata

java.sql.SqlException

javax.sql.DataSource

javax.sql.ConnectionPoolDataSource

javax.sql.PooledConnection
CSQL Native Library

libcsqljdbc.so
CSQL JDBC Driver


CSqlJdbcDriver.jar
CSQL user can connect to database through driver manager, data sources and connection pooling data

source object. For programmers, those are using Netbeans IDE can also connect to CSQL.
For the configuration of Net beans IDE follow the below mentioned step-by-step procedure.
Preliminary Setup
1.
Download the
Net beans IDE
package from the
http://www.netbeans.org/downloads/
and

install.
2.
Download CSQL Database from
http://www.csqldb.com/download.html
and extract (.tar.gz)

file
3.
Enter into the csql home directory. Run
(. ./setupenv.ksh)

for necessary path

setup and run csqlserver
.
$ tar –xvf csql3.0-ent-linux-x86.tar.gz
$ cd csql3.0-ent-linux-x86
$ . ./setupenv.ksh
$ csqlserver
Sys_DB [Size=0001MB]
User_DB [Size=0010MB]
Database Server Started...
4.
Open another terminal. Go to CSQL home directory and Run (
. ./setupenv.ksh
) for

necessary path setup. Run Netbeans on that Terminal
.
$ cd csql3.0-ent-linux-x86
$ . ./setupenv.ksh
$ netbeans
5.
Now you encounter the below screen
Figure-1
NOTE: If Netbeans Ide is not started . Set PATH to
< Netbeans Home directory>/bin .
Configuring Netbeans IDE For Simple Java Application to Connect CSQL

MMDB
For Simple java Application, follow the below steps
1.
Go to
File
in main toolbar, Select
‘New Projects’
from the drop down menu, a new dialog

box will appear
2.
Select
‘Java’
as Categories and click Next.
3.
Type a project name in the
Project Name
box. (In below figure it is
CsqlTest
)
Figure-2

4.
Click on
Finish.
5.
Write following code in the
Main.java
file.
6.
Click on
CsqlTest
in your left hand pan under Projects list. Right click on
‘libraries’
7.
Click
‘Add JAR/Folders...’
It will show following dialog box.
import java.sql.*;
public class
Main
{
public static void main(
String
[] args)
{
try{
Class.forName("csql.jdbc.JdbcSqlDriver");
Connection con=DriverManager.getConnection("jdbc:csql","root","manager");
Statement cStmt=con.createStatement();
cStmt.execute("CREATE TABLE T1 (f1 integer, f2 smallint, f3 tinyint, f4

bigint, f5 float, f6 char(10), f7 date, f8 time, f9 timestamp);");
con.commit();
PreparedStatement stmt=null,selStmt=null;
stmt=con.prepareStatement("INSERT INTO T1(f1,f3,f5,f6,f7,f8,f9)

VALUES(?,?,?,?,?,?,?);");
int ret=0;
for(int i=0;i<10;i++)
{
stmt.setInt(1,i);
stmt.setByte(2,(byte)(i+2));
stmt.setFloat(3,(float)1000+i);
stmt.setString(4,
String
.valueOf("Nihar"+i));
stmt.setDate(5,
Date
.valueOf("2008-03-21"));
stmt.setTime(6,
Time
.valueOf("18:00:00"));
stmt.setTimestamp(7,
Timestamp
.valueOf("2008-03-21 18:00:00"));
ret=stmt.executeUpdate();
if(ret!=1) break;
}
con.commit();
stmt=con.prepareStatement("INSERT INTO T1(f1,f2,f4,f6,f7,f8,f9)

values(10,11,101,'Nihar10','2008-03-21','18:00:00','2008-03-21 18:00:00');");
stmt.executeUpdate();
stmt.close();
con.commit();
selStmt=con.prepareStatement("Select * from T1");

ResultSet
rs=null;
rs=selStmt.executeQuery();
while(rs.next())
{

System
.out.println("Tuple value is " + rs.getInt(1) + " "+
rs.getShort(2) + " "+
rs.getByte(3) + " "+
rs.getLong(4) + " "+
rs.getFloat(5) + " "+
rs.getString(6) + " "+
rs.getDate(7) + " "+
rs.getTime(8) + " "+
rs.getTimestamp(9) + " "
);
}
rs.close();
con.commit();
cStmt.executeUpdate("Drop table T1;");
con.close();
}catch(
Exception
e) {

System
.out.println("Exception in Test: "+e);
e.printStackTrace();
}
}
Figure-3
8.
Select the
‘CsqlJdbcDriver.jar’
file, which will be under
csql3.0-ent-linux-
x86/lib
directory.
9.
Click on OK
10.
Right Click on
CsqlTest
package from Project lists.
11.
Click Run to run application.
Configuring Netbeans IDE For Connecting Through DataSource
1.
Go to
File
in main toolbar, Select
‘New Projects’
from the drop down menu, a new dialog

box will appear
2.
Select
‘Web’
as Categories and
‘Web Application’
as Projects. Click Next.
3.
Type a project name in the
Project Name
box. (In Figure -it is
CsqlTestWeb
)
4.
Click on next. Choose application sever. Click on
Finish.
5.
Write following code in
index.jsp
file.
Figure-4
<html> <head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title> </head>
<body>
<%
try{
javax.naming.Context cxc= new javax.naming.InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) cxc.lookup("jdbc/bijaya");
java.sql.Connection conn=ds.getConnection("root", "manager");
java.sql.Statement stmt=conn.createStatement();
stmt.execute("CREATE TABLE papu (f1 int,f2 int);");
out.println("Table created on csql ");
conn.close();
} catch (java.sql.SQLException e){
out.println("An error occurred.");
}
%>
</body>
</html>
6.
Add CSQL JDBC driver in Libraries as instructed figure-3.
7.
Click on
‘service’
tab on left hand pan and click on
‘server’
.
8.
Right click on
‘GlassFish V2’
and click on
‘start’
to start application server.
9.
Again right click on
‘GlassFish V2’
and then click
'application sever admin

console'
.It will open browser as displayed below (Username:admin, Password:

adminadmin)
Figure-5
10.
Go to application sever, click on
JVM ‘settings’
, from their in
‘Path Settings’
set

‘Classpath Prefix’
to
/home/bijaya/csql3.0-ent-linux-
x86/lib/CSqlJdbcDriver.jar

11.
‘Native Library Path’
Suffix to
/home/bijaya/csql3.0-ent-linux-x86/lib as

shown in the image
.
Figure-6
12.
Go to
‘Resources’
, Click on ‘JDBC’ and then on
‘Connection Pools’
.
13.
In
‘General Settings’
, give a name (say csql). Choose
‘Resource type’
is

‘javax.sql.DataSource’
and click next.
14.
Set
‘Datasource Classname’
as
‘csql.jdbc.JdbcSqlDataSource’
and Add properties

as shown in the figure
Figure-7
15.

click Finish and Ping it for successful connection to csql.
Note
: Mode can be
csql , gateway or adapter
as per the requirement.
16.
Click on
‘JDBC Resources’
from
Resources->JDBC

17.
Click on
‘New’
. It will show following page.
Figure-8
18.
Set
‘JNDI Name’
as
‘jdbc/bijaya’
.
‘Pool’
as
‘csql’
and click on
Ok
19.
Go to Netbeans Ide Editor. Right click on
‘CsqlTestWeb’
from Left hand pan.
20.
Click on
‘Build’
.
21.
Again right click on
‘CsqlTestWeb’
from Left hand pan and click
‘Run’
to run web

application.
For More Information Visit
http://www.csqldb.com
http://sourceforge.net/projects/csql
Author
Bijaya Kuamr Sahu
Database Developer
Lakshya Solution Pvt Ltd