Netbean Configuration For Csql

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

     

java.sql.ResultSetMetadata java.sql.DatabaseMetadata java.sql.SqlException javax.sql.DataSource javax.sql.ConnectionPoolDataSource javax.sql.PooledConnection

Important Interfaces       

java.sql.Driver java.sql.Connection java.sql.PreparedStatement java.sql.ResultSet java.sql.Statement java.sql.SqlException java.sql.ParameterMetadata

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) 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

for

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.

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(); 6. cStmt.executeUpdate("Drop Click on CsqlTest in your left table hand pan under Projects list. Right T1;"); 7. con.close(); Click ‘Add JAR/Folders...’ It will show following dialog box. }catch(Exception e) { System.out.println("Exception in Test: "+e); e.printStackTrace(); } }

click on ‘libraries’

Figure-3

8. Select the ‘CsqlJdbcDriver.jar’ file, which will be under csql3.0-ent-linuxx86/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. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <% 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."); } %>

Figure-4

6. 7. 8. 9.

Add CSQL JDBC driver in Libraries as instructed figure-3. Click on ‘service’ tab on left hand pan and click on ‘server’. Right click on ‘GlassFish V2’ and click on ‘start’ to start application server. 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-linuxx86/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

Author

http://www.csqldb.com

Bijaya Kuamr Sahu

http://sourceforge.net/projects/csql

Database Developer Lakshya Solution Pvt Ltd