كيفية استخدام قاعدة بيانات hypersql في الجافا

في الدرس دا سيتم شرح  كيفية استخدام قاعدة بيانات hypersql
و الشرح تم باستخدام برنامج netbeans

و في هذه المرة قمت بانشاء class خاصة بالقيام بجميع الوظائف المتعلقة بقاعدة البيانات من حيث انشائها و الاتصال بها و القيام بعمليات عليها و اغلاقها .



نبتدئ بالفيديو

رابط تحميل ملف hsql.jar
http://www.mediafire.com/file/8h5el48hnzct542/hsqldb.jar

كود الكلاس :

import java.io.File;
import java.sql.*;
import java.util.logging.*;

/**
 *
 * @author shalkam
 */
public class hsql_conn {

    private String absPath;
    private static final hsql_conn INSTANCE = new hsql_conn();

    public static final hsql_conn getInstance() {
        return INSTANCE;
    }

    public hsql_conn() {
        String path = new File(getClass().getResource("").getPath()).getParentFile().getParent();
        String det = path.substring(0, 5);
        if (det.equals("file:")) {
            absPath = path.substring(5) + System.getProperty("file.separator") + "data" + System.getProperty("file.separator");
        } else {
            absPath = path + System.getProperty("file.separator") + "data" + System.getProperty("file.separator");
        }
    }

    /**
     * create the database if it doesn't exist
     * @param path String the directory at which the database will be created it should have file separator in the end
     * @return created boolean true if the database is created
     */
    public boolean createDB(String query) {
        boolean created = false;
        File f = new File(absPath);
        if (!f.exists()) {
            f.mkdir();
            try {
                Class.forName("org.hsqldb.jdbc.JDBCDriver");
            } catch (Exception e) {
                System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
            }
            try {
                Connection c = DriverManager.getConnection("jdbc:hsqldb:file:" + absPath + "data;");
                Statement stat = c.createStatement();
                stat.execute(query);
                stat.close();
            } catch (SQLException ex) {
                Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            }
            created = true;
        }
        return created;
    }

    /**
     * Handles queries that will have results like queries using the "Select" statement
     * @param query String  the sql query to execute against the database
     * @return listOfMaps List the result from the query in the form of list of linked hash maps
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public ResultSet doQuery(String query) throws SQLException, ClassNotFoundException {
        Connection c = this.getConnection();
        Statement stat = c.createStatement();
        ResultSet rs = stat.executeQuery(query);
        return rs;
    }

    /**
     * Handles queries that won't have results like queries using statements like "create", "drop" or "delete"
     * @param query String  the sql query to execute against the database
     * @return rs boolean true if successful
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean execQuery(String query) throws SQLException, ClassNotFoundException {
        Connection c = this.getConnection();
        Statement stat = c.createStatement();
        boolean rs = stat.execute(query);
        stat.close();
        return rs;
    }

    /**
     * connects to the database
     * @return c the connection to the database
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        Connection c = DriverManager.getConnection("jdbc:hsqldb:file:" + absPath + "data;ifexists=true");
        return c;
    }

    /**
     * shuts down the database
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void shutdownDB() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        Connection c = DriverManager.getConnection("jdbc:hsqldb:file:" + absPath + "data;shutdown=true");
    }
}

كود دالة تظهر كيفية استخدام الفئة class

public void functions() throws SQLException, ClassNotFoundException {
        hsql_conn connection = hsql_conn.getInstance();
        if(connection.createDB("create table mytable (age int, name varchar(255))")) System.out.println("تم انشاء قاعدة البيانات بنجاح");
        else System.out.println("لم يتم انشاء قاعدة البيانات او انها تم انشاؤها بالفعل");
        connection.execQuery("insert into mytable values(5 , 'shalkam')");
        ResultSet rs = connection.doQuery("select * from mytable");
        while(rs.next()) {
            System.out.println(rs.getString("age") + " - " + rs.getString("name"));
        }
    }