derbydriver.java

Example of Derby DB usage. Simple student system implemented using Swing.

import org.apache.derby.impl.jdbc.EmbedSQLException;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;

public class DerbyDriver
{
    public static final String DB_NAME = "jdbc:derby:mydb";
    public static final String DB_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

    private void createDB(Connection conn)
    {
        StudentRecord[] studData = {new StudentRecord(1,"student1",1),
                                    new StudentRecord(2,"student2",2),
                                    new StudentRecord(3,"student3",3)};

        UserRecord[] userData = {new UserRecord("admin", "admin1234", 3),
                                 new UserRecord("admin2", "admin6734", 3),
                                 new UserRecord("admin3", "admin8934", 3)};
        try
        {
            for(int i=0; i<studData.length; i++)
                insertStudentRecord(conn, studData[i]);

            for(int i=0; i<userData.length; i++)
                insertUserRecord(conn, userData[i]);
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return;
        }
        System.out.println("Successfully created new DB");
    }

    public int insertStudentRecord(Connection conn, StudentRecord rec) throws Exception
    {
        Statement s = conn.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("insert into students values(").append(rec.getId()).append(",");
        sb.append("'").append(rec.getName()).append("',");
        sb.append(rec.getGrade()).append(")");
        int rows = s.executeUpdate(sb.toString());
        s.close();
        System.out.println("Inserted "+rows+" student record(s)");
        return rows;
    }

    public int insertUserRecord(Connection conn, UserRecord rec) throws Exception
    {
        Statement s = conn.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("insert into users values('").append(rec.getName()).append("',");
        sb.append("'").append(rec.getPassword()).append("',");
        sb.append(rec.getLoginAtt()).append(")");
        int rows = s.executeUpdate(sb.toString());
        s.close();
        System.out.println("Inserted "+rows+" user record(s)");
        return rows;
    }

    public int deleteUserRecord(Connection conn, String name) throws Exception
    {
        Statement s = conn.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("delete from users where name like '").append(name).append("'");
        int rows = s.executeUpdate(sb.toString());
        s.close();
        System.out.println("Deleted "+rows+" user record(s)");
        return rows;
    }

    public int deleteStudentRecord(Connection conn, int recordId) throws Exception
    {
        Statement s = conn.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("delete from students where id=").append(recordId);
        int rows = s.executeUpdate(sb.toString());
        s.close();
        System.out.println("Deleted "+rows+" student record(s)");
        return rows;
    }

    public List getStudentRecords(Connection conn, int recordId, StudentRecord data) throws Exception
    {
        Statement s = conn.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("select * from students");
        boolean wasAppend=false;

        if(recordId>-1)
        {
            if(!wasAppend)
            {
                sb.append(" where ");
                wasAppend=true;
            }
            sb.append("id=").append(recordId);
        }

        if(data!=null)
        {
            if(data.getName()!=null)
                if(data.getName().length()>0)
                {
                    if(wasAppend)
                        sb.append(" and ");
                    else
                        sb.append(" where ");
                    sb.append("name like '").append(data.getName()).append("'");
                }

            if(data.getGrade()>=0)
            {
                if(wasAppend)
                    sb.append(" and ");
                else
                    sb.append(" where ");
                sb.append("grade=").append(data.getGrade());
            }

        }

        ResultSet rs = null;
        rs = s.executeQuery(sb.toString());
        List res = new ArrayList();
        while ( rs.next() )
        {
            StudentRecord rec = new StudentRecord(rs.getInt(1),rs.getString(2),rs.getFloat(3));
            res.add(rec);
        }
        rs.close();
        System.out.println("Fetched "+res.size()+" student record(s)");
        return res;
    }

    public List getUserRecords(Connection conn, UserRecord data) throws Exception
    {
        Statement s = conn.createStatement();
        StringBuffer sb = new StringBuffer();
        sb.append("select * from users");
        boolean wasAppend=false;

        if(data!=null)
        {
            if(data.getName()!=null)
                if(data.getName().length()>0)
                {
                    if(!wasAppend)
                    {
                        sb.append(" where ");
                        wasAppend=true;
                    }
                    sb.append("name like '").append(data.getName()).append("'");
                }

            if(data.getPassword()!=null)
                if(data.getPassword().length()>0)
                {
                    if(wasAppend)
                        sb.append(" and ");
                    else
                        sb.append(" where ");
                    sb.append("password like '").append(data.getPassword()).append("'");
                }

            if(data.getLoginAtt()>=0)
            {
                if(wasAppend)
                    sb.append(" and ");
                else
                    sb.append(" where ");
                sb.append("login_att=").append(data.getLoginAtt());
            }

        }

        ResultSet rs = null;
        rs = s.executeQuery(sb.toString());
        List res = new ArrayList();
        while ( rs.next() )
        {
            UserRecord rec = new UserRecord(rs.getString(1),rs.getString(2),rs.getInt(3));
            res.add(rec);
        }
        rs.close();
        System.out.println("Fetched "+res.size()+" user record(s)");
        return res;
    }

    public int updateStudentRecord(Connection conn, int recordId, StudentRecord newData) throws Exception
    {
        boolean notEmpty = false;
        int rows=0;
        Statement s = conn.createStatement();
        StringBuffer sql = new StringBuffer();
        sql.append("update students");
        StringBuffer set = new StringBuffer(" set ");
        if(newData.getName()!=null)
            if(newData.getName().length()>0)
            {
                notEmpty = true;
                set.append("name=");
                set.append("'").append(newData.getName()).append("', ");
            }

        if(newData.getGrade()>=0)
        {
            notEmpty = true;
            set.append("grade=");
            set.append(newData.getGrade()).append(", ");
        }

        if(notEmpty)
        {
            set.setLength(set.length()-2);
            sql.append(set.toString());
            sql.append(" where id=").append(recordId);
            rows = s.executeUpdate(sql.toString());
            System.out.println("Updated "+rows+" student record(s)");
        }
        s.close();
        return rows;
    }

    public int updateUserRecord(Connection conn, String name, UserRecord newData) throws Exception
    {
        boolean notEmpty = false;
        int rows=0;
        Statement s = conn.createStatement();
        StringBuffer sql = new StringBuffer();
        sql.append("update users");
        StringBuffer set = new StringBuffer(" set ");

        if(newData.getName()!=null)
            if(newData.getName().length()>0)
            {
                notEmpty = true;
                set.append("name=");
                set.append("'").append(newData.getName()).append("', ");
            }

        if(newData.getPassword()!=null)
            if(newData.getPassword().length()>0)
            {
                notEmpty = true;
                set.append("password=");
                set.append("'").append(newData.getPassword()).append("', ");
            }

        if(newData.getLoginAtt()>=0)
        {
            notEmpty = true;
            set.append("login_att=");
            set.append(newData.getLoginAtt()).append(", ");
        }

        if(notEmpty)
        {
            set.setLength(set.length()-2);
            sql.append(set.toString());
            sql.append(" where name like '").append(name).append("'");
            rows = s.executeUpdate(sql.toString());
            System.out.println("Updated "+rows+" user record(s)");
        }
        s.close();
        return rows;
    }

    private boolean createTables(Connection conn)
    {
        boolean res=false;
        try
        {
            Statement s = conn.createStatement();

            res = s.execute("create table users ( user_id integer, " +
                "                               fname varchar(20), " +
                "                               lname varchar(20), " +
                "                               login varchar(10), " +
                "                               psw varchar(10), " +
                "                               course integer, " +
                "                               group varchar(5), " +
                "                               is_admin integer " +                    
                ")");

            if(!res)
                System.out.println("Created table 'users'");

            
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return res;
    }

    public Connection getConnection(String driver, String dbName)
    {
        Connection conn = null;
        try
        {
            Class.forName(driver);
            conn = DriverManager.getConnection(dbName);
        }
        catch(EmbedSQLException eex)
        {
            try
            {
                conn = DriverManager.getConnection(dbName+";create=true");
                createTables(conn);
            }
            catch(Exception ex)
            {
                ex.printStackTrace();
            }
            createDB(conn);
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return conn;
    }

    public void printDB(Connection conn)
    {
        List res = null;
        try
        {
             res = getStudentRecords(conn, -1, null);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        if(res!=null)
            if(res.size()>0)
            {
                for(int i=0; i<res.size(); i++)
                {
                    StudentRecord rec = (StudentRecord)res.get(i);
                    System.out.println("ID="+rec.getId()+", Name is '"+rec.getName()+"', Grade="+rec.getGrade());
                }
            }

        res=null;
        try
        {
            UserRecord ur = new UserRecord(null, null, -1);
            res = getUserRecords(conn, ur);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        if(res!=null)
            if(res.size()>0)
            {
                for(int i=0; i<res.size(); i++)
                {
                    UserRecord rec = (UserRecord)res.get(i);
                    System.out.println("User name is '"+rec.getName()+"', User password is '"+rec.getPassword()+"', logins allowed "+rec.getLoginAtt());
                }
            }

    }
}