Bit operation implementation in Derby

How do I perform bit operations in Derby? Recently, the company decided to switch the database used by the project from SQL Server to JAVA DB. We all know that Apache has a famous open-source database called Derby. The company decided to use this database as its own database. Therefore, you cannot rewrite the original SQL statement with the Derby syntax. The syntax is incompatible. One of these operations bothered me one afternoon. I searched many examples on the Internet and found that there is only one method, that is, using MoD functions to replace bitwise operations. However, this method is very troublesome and inefficient. Fortunately, I found that Derby also supports udfs (user-defined functions), and Java functions can be defined as the built-in functions of Derby. This is much simpler. The following is an example.

Here I will not explain how to install Java, eclipse, and Derby. Assume that Java, eclipse, and Derby are installed on all machines. If you are interested, visit http://db.apache.org/derby/docs/dev/getstart/to get the detailed content of derbyte.

1. Configure Derby to eclipse

Download the Derby Installation File derby_core_plugin_10.6.1.rarand derby_ui_doc_plugin_1.1.2.rar. Decompress the package and copy the folder in plugins to eclipse plugins.

 

2. Create a Java project in eclipse. My example project is as follows:

 

3. Set the database path of Derby, right-click the project, and select Properties from the menu that appears,

If your derby server fails, the home directory can be modified. I point it to the K: \ softwares \ Derby directory in the project.

 

4. manually create databases and test tables in Derby

First, open the Derby console interaction window. In the interaction window, we can manually execute some SQL statements

Then, create a database (firstdb) and a table (test_tb), and you can see the newly generated file in the database directory folder as follows:

Here, we basically need the test environment, even if the configuration is complete, but here we should note that Derby uses the ';' sign as the Terminator. Friends who use the Derby interactive window for the first time must remember to add it ^_^.

 

5. Start writingCodeTo implement the functions we need,

First, create a class with the main function. Create a JDBC connection for Derby. Pay attention to the dbname variable, because here I set the path to absolute or relative path.

 

Public static connection creatjdbcconnection () {string driver = "org. apache. derby. JDBC. embeddeddriver "; string dbname =" K: \ softwares \ Derby \ firstdb "; // create = true will create a new database. we have create a database // by manual, so set the create flag is false. string connectionurl = "JDBC: Derby:" + dbname + "; Create = false;"; connection conn = NULL; try {class. forname (driver);} catch (classnotfoundexception e) {e. printstacktrace ();} Try {conn = drivermanager. getconnection (connectionurl);} catch (sqlexception e) {e. printstacktrace ();} return conn ;}
 

 

 

Create a Java implementation function and a derby function.

 

 
Public static int bitwiseand (int A, int B) {return A & B ;}
 

This function is easy to say. The only thing you need to talk about is the following Derby function.

 

 

Public static Boolean registerudf (connection conn) {string bitwiseandfunc = "create function bitwiseand (" +"A int, B INT) "+"Returns int"+" Parameter style Java "+" No SQL "+" language Java "+" external name'Code. Ace. example. derbyclass. bitwiseand'"; Try {Statement S = conn.createstatement(;;s.exe cute (bitwiseandfunc); Return true;} catch (sqlexception e) {string theerr = (e ). getsqlstate (); If (theerr. equals ("x0y68") return true; E. printstacktrace (); Return false ;}}
 

Note the red part. The case, type, and name of the three parts must be consistent with the implementation function. Otherwise, an exception is thrown or the call fails. In addition, I added some code in exception handling to handle the existing problems of the function, because the function you created in Derby always exists in the database, it has no relationship with whether the connection is disconnected, so you must handle the problems existing in the function. If the above two steps are completed, we can use this function in the SQL statement.

 

 

 
Select ID, name, flag from test_tb whereBitwiseand(Flag ,?) = 1
 

 

Below we will write some code to test this function.

 

 
Public static void testbitwiseandoperation (connection conn) {try {preparedstatement psselect = conn. preparestatement ("select ID, name, flag from test_tb where" + "bitwiseand (flag ,?) = 1 "); psselect. setint (1, 1 );//Please change the second value to 2, and run againResultset rs = psselect.exe cutequery (); If (RS. next () {formatter F = new formatter (); F. format ("ID = % d, name = % s, flag = % d", Rs. getint (1), RS. getstring (2), RS. getint (3); system. out. println (F. tostring () ;}} catch (sqlexception e) {e. printstacktrace ();}}
 

Note: Before starting the test, start the Derby server,

 

The above briefly introduces how to use Java to solve the problem that the Derby database does not support some functions. For more applications, you can obtain them on the official Derby website. The complete Derby installation package also provides some examples.Program.

The example code of the example contains derby.rar and javadb.rar.