Database sample App - SELECT / INSERT / UPDATE / DELETE - General topics, questions and discussions - Jabaco

Dear visitor, welcome to Jabaco - Community. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Thursday, July 18th 2013, 9:11am

Database sample App - SELECT / INSERT / UPDATE / DELETE

I have provided a full database sample app in the German section of this forum:
Vorstellung Desi / Bauteildatenbank / das Jabaco-Projekt

Since not everybody will find it there I am posting it here again!

It is just a H2 database with a little GUI wrapped around it.
Most of the code will be the same for using a different database like mysql sqlite access ...

H2ArtikelDB.zip

It demonstrates how to connect to a database and the main methods:
- SELECT
- INPUT
- UPDATE
- DELETE

within the database-file:
- VIEW
- JOIN

This project also shows how to use the JBGrid and the MenuBar with a few methods.
Uncommented code also shows how to run a script to build the database itself!

Here http://www.h2database.com/html/main.html you can download the latest H2 version.
From that .zip file you have to extract the compiled database .jar e.g.: \h2\bin\h2-1.3.172.jar
to your local Java installationpath or your Apps path:
- e.g.: 'C:\Program Files\Java\jre7\Lib\ext\h2-1.3.170.jar'

Now adjust the path to the sample database 'Artikel.h2.db' in the code of this project.
in Form1 and Module1:
- e.g. C:\tmp\ArtikelDB\Artikel

System.getProperty("user.dir") as used in the code is not allways reliable!

Dani

UPDATE 2015-11-13:

Since H2 has changed a lot internally when you download the database engine you will probably get a branch > 1.3
In order to use my sample app you will need to add the following to the connection string:

	Jabaco Source
1 2	MV_STORE=FALSE e.g. conn = java#sql#DriverManager.getConnection("jdbc:h2:" & System.getProperty("user.dir")& "\Artikel;MV_STORE=FALSE;TRACE_LEVEL_FILE=0", "xyz", "1234")

This way H2 uses the 'old' storagesystem matching the h2.db file of the sample app!

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

This post has been edited 2 times, last edit by "Dani" (Nov 13th 2015, 2:13pm)

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Thursday, July 18th 2013, 9:13am

to connect to a mysql database use something like this:

	Source code
1 2	java#lang#Class.forName("com.mysql.jdbc.Driver") conn = DriverManager.getConnection("jdbc:mysql://localhost/database", "username", "password")

should work I have not tested it!!

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Tuesday, August 5th 2014, 9:44am

Oh, my.... I just finish reading some general info about h2 database engine... and I feel like I wanted to hit myself for not knowing about what was it coz it turns out that it was this database that i was looking for all along. and im so fed up with sqlite that i didnt give importance to it at first glance. :pinch:

I was just looking for a database which :

fast
Open source
Supports standard SQL, JDBC API
Embedded and Server mode and mixed mode
Multi version concurrency - several computations are executing simultaneously
around 1.5 MB jar file size

It was H2 database Engine ALL ALONG!

anyways.... im glad Dani showed me the way! :thumbsup:

.
.
Spare me, im new to Programming

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Wednesday, August 6th 2014, 6:11am

hi Dani,

Id like to ask if what are the best management tool for H@ database... unfortunately navicat cant populate the h2 database file. is there any specific tools for viewing and managing h2 database files?

TIA

.
.
Spare me, im new to Programming

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Thursday, August 7th 2014, 5:36am

It would be a great help if there is a tool for managing the h2 database file like the sqlite browser of the navicat. I did try using some of the front end tools for h2 but its too messy to work with. out of so many tools listed on h2 site the only tools which i understand better is the RAZORSQL but i had issues with since its generating folders after opening the h2 database. the only thing that i like is its capcbility to create table, view datas inside the table and even adding/editng datas on the table.

And i tried opeing the database attached with your given sample program but I There is no data there.. no matter how i try to view the Artikel.h2 database on your project

oh my... im now having a hard time figuring it and understanding it so i could utilize it much much better.

.
.
Spare me, im new to Programming

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Tuesday, August 19th 2014, 8:47am

Hey there,

H2 itself allready comes with a management ability:
http://www.h2database.com/html/quickstart.html

I can also recomment Execute Query though:
http://executequery.org/index.php

Quoted from "spysonic"

And i tried opeing the database attached with your given sample program but I There is no data there.. no matter how i try to view the Artikel.h2 database on your project

sounds to me like you did not set the connection string or path right!? H2 creates an empty databasefile if that happens!

It is all in the code though...

Dani

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Tuesday, August 19th 2014, 10:19am

Thanks Dani, I'll try the tool you'd advised

BTW, I tried creating a sample database of H2 out of razorsql tool, and it gives a database with .mv extension and not .h2, when i try opening that file it contains my files too. Im curious i thougt it supposed to generate a .h2 database.? did i miss something? or is it a valid database file too?

.
.
Spare me, im new to Programming

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Tuesday, August 19th 2014, 10:46am

Hey there,

I don't know what that file is.

I would recomment downloading the last (2014-04-05) stable H2 file:
http://www.h2database.com/h2-2014-04-05.zip

From that .zip file you have to extract the compiled database .jar e.g.: \h2\bin\h2-1.3.176.jar
to your local Java installationpath or your Apps path:
- e.g.: 'C:\Program Files\Java\jre7\Lib\ext\h2-1.3.176.jar'

Hit F1 in Jabaco and add the 'C:\Program Files\Java\jre7\Lib\ext\h2-1.3.176.jar' to your project.

Now adjust the path to the sample database 'Artikel.h2.db' in the code of this project.
in Form1 and Module1:
- e.g. C:\tmp\ArtikelDB\Artikel

where 'Artikel' is the H2 databasefile. Do not use the full filename: Artikel.h2.db !!!!
This may be a bit confusing but otherwise H2 adds another '.h2.db' to the files.

Dani

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Wednesday, August 20th 2014, 2:12am

your sample app for artikel.db is running fine on my end. but my problem lies in creating my own database out of my installed h2.
my Bad! I downloaded the beta version of h2-2014-07-13 and its generating a .mv.db file automatically..

thanks for giving me the version of h2 you're using.

.
.
Spare me, im new to Programming

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Thursday, August 28th 2014, 4:21am

hello everyone,

im now trying to create a simple app that connects to a H2 server database which is installed in other PC's,
I had my database ready on the other Pc's user directory. working well if i run the app on the server pc, but when i tried to run the app on the other pc it gives an error

did i miss something?
i used :

	Jabaco Source
1	conn = java#sql#DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test","x", "x")

working on the pc where the h2 database is installed, gives me an access denied if i run the app on other pc

.
.
Spare me, im new to Programming

This post has been edited 1 times, last edit by "spysonic" (Aug 28th 2014, 4:26am)

Go to the top of the page

spysonic

Trainee

Posts: 88

Date of registration: Jul 11th 2014

About me: A beginner programmer.

Location: ...Jabaco Academy

Occupation: i have some but still not enough...

Hobbies: Jabaco

Thursday, August 28th 2014, 8:42am

how am i supposed to start the h2 within the app code?
import org.h2.tools.Server;

...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();

yep we can manually start the h2 server database using a command line but how about doing it using the code above?

also, please teach us how to have a connection pool for h2 base on this too:

import java.sql.*;
import org.h2.jdbcx.JdbcConnectionPool;
public class Test {
public static void main(String[] args) throws Exception {
JdbcConnectionPool cp = JdbcConnectionPool.create(
"jdbc:h2:~/test", "sa", "sa");
for (int i = 0; i < args.length; i++) {
Connection conn = cp.getConnection();
conn.createStatement().execute(args);
conn.close();
}
cp.dispose();
}
}

.
.
Spare me, im new to Programming

This post has been edited 1 times, last edit by "spysonic" (Aug 28th 2014, 9:30am)

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Tuesday, October 21st 2014, 7:37pm

Hey there,

this is a sample of how to set up a simple Connection Pool with the H2 Database Sample App I posted above.

It is just a rough outline, you have to adjust the code to your needs!!!

Jabaco Source

'Jabaco sample by Dani: 
'h2 Database ConnectionPool Setup
'http://www.h2database.com/html/main.html

Option Explicit

Import org#h2#Driver
Import org#h2#jdbcx#JdbcConnectionPool

Global strQry As String 
Global cp As org#h2#jdbcx#JdbcConnectionPool
Global conn As java#sql#Connection 
Global st As java#sql#Statement 
Global rs As java#sql#ResultSet

Public Sub grdMain_init()
'within some Sub initialize the pool
   cp = JdbcConnectionPool.create("jdbc:h2:C:\tmp\ArtikelDB\Artikel;TRACE_LEVEL_FILE=0", "xyz", "1234")
   conn = cp.getConnection()
   strQry = "SELECT * FROM EINIGEARTIKEL WHERE ANZEIGEN = True ORDER BY BEZEICHNUNG ASC;"
   rs = conn.createStatement().executeQuery(strQry)
   conn.close()

'and fetching a new connection from the pool whereever needed...
   conn = cp.getConnection()
   strQry = "SELECT * FROM EINIGEARTIKEL WHERE ANZEIGEN = True AND BEZEICHNUNG LIKE '" & strBezeichnung & "%' ORDER BY BEZEICHNUNG ASC;"     
   rs = conn.createStatement().executeQuery(strQry)
'or
'   strQry = "DELETE FROM ARTIKEL " & _
'         "WHERE NRINTERN = " & grdMain.TextMatrix(grdMain.Row, 0) & ";"
'   conn.createStatement().executeUpdate(strQry)
   conn.close()
   'cp.dispose()
End Sub

Dani

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Tuesday, December 1st 2015, 2:36pm

connect to SQLite db file

Hey there,

to use the above sample App with a SQLite database have a look at the following codesample.
It uses the Xerial SQLite JDBC Driver.
... 'SQLite JDBC is a library for accessing SQLite databases through the JDBC API.' ...

Jabaco Source

Option Explicit

   'Add sqlite-jdbc-3.8.11.2.jar to your Java instalation or class path...
   'e.g.: 'C:\Program Files\Java\jre7\Lib\ext\sqlite-jdbc-3.8.11.2.jar'
   'https://bitbucket.org/xerial/sqlite-jdbc/downloads
   'https://bitbucket.org/xerial/sqlite-jdbc/overview
   'https://github.com/xerial/sqlite-jdbc
   
   Import org#sqlite#JDBC
   
'   Import java#sql#Connection
'   Import java#sql#DriverManager
'   Import java#sql#ResultSet
'   Import java#sql#SQLException
'   Import java#sql#Statement

   Dim strQry As String 
   Dim conn As java#sql#Connection 
   Dim st As java#sql#Statement 
   Dim rs As java#sql#ResultSet

Public Sub Form_Load()
   java#lang#Class.forName("org.sqlite.JDBC")

   'sqlight
   'adjust path...!
   conn = java#sql#DriverManager.getConnection("jdbc:sqlite:C:\test\Artikel.db")
   st = conn.createStatement()
   
'   st.executeUpdate("DELETE FROM ARTIKEL WHERE ANZEIGEN = 0;")

   strQry = "SELECT * FROM ARTIKEL WHERE ANZEIGEN = 1 ORDER BY BEZEICHNUNG ASC;"
   rs = st.executeQuery(strQry)
   Do While rs.next()
      Debug.Print rs.getMetaData.getColumnName(1)
   Loop
End Sub

'CREATE TABLE ARTIKEL (NRINTERN BIGINT(2000000000, 10) NOT NULL,NREXTERN VARCHAR(25),BEZEICHNUNG VARCHAR(10),BESCHREIBUNG VARCHAR(100),EAN13 VARCHAR(13),STEUER_ID INT(2000000000, 10),GRUPPE_ID INT(2000000000, 10),GANG_ID INT(2000000000, 10),REGAL_ID INT(2000000000, 10),FACH_ID INT(2000000000, 10),EK DECIMAL(10, 4),VK DECIMAL(10, 4),ANZEIGEN BOOLEAN,GESPERRT INT(2000000000, 10)
);