Exercise: Jar Dependencies and Ant Build

Last modified by superadmin on 2018-01-12 20:27

Exercise: Jar Dependencies and Ant Build

Goal: Use Ant build script to compile and run a Java project using JDBC (obtaining connections via DriverManager).

  1. Set up the database as described in the DB Setup Exercise
  2. Create the following Java classes project3.User and project3.dao.UserDAO
package project3.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

import project3.User;

public class UserDAO {
   
   
    public static void main(String[] args) {
        UserDAO dao = new UserDAO();
        List<User> users = dao.getAll();
        System.out.println("users are = " + users);
       
    }

    protected Connection getConnection() throws Exception {
        //String drivername = "org.gjt.mm.mysql.Driver";
        String drivername = "oracle.jdbc.driver.OracleDriver";
        Class.forName(drivername);
        // String url = "jdbc:mysql://localhost/mctesting";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        Connection con =
            DriverManager.getConnection(url, "mctesting", "mctesting");
        return con;
    }

    public List<User> getAll() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<User> result = new ArrayList<User>();
        try {
            con = getConnection();
            st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM mcuser");
            int count = 0;

            while (rs.next()) {
                User u = getUserFromCursor(rs);
                result.add(u);
            }
           
        } catch (Exception e) {
            e.printStackTrace(System.err);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
        return result;
    }

    public long save(User u) {
        Connection con = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            con = getConnection();

            if (u.getId() == 0L) {
                pstmt =
                        con.prepareStatement("INSERT INTO mcuser " + "SELECT seq_mcuser_id.nextval,?,?,?,?,?,? FROM dual");
            } else {
                pstmt =
                        con.prepareStatement("UPDATE mcuser SET " + "url=?, firstname=?, lastname=?, " +
                                             "loginid=?, password=?, registered=? WHERE mcuser_id=?");
                pstmt.setLong(7, u.getId());
            }
            pstmt.setString(1, u.getEmail());
            pstmt.setString(2, u.getFirstName());
            pstmt.setString(3, u.getLastName());
            pstmt.setString(4, u.getLoginId());
            pstmt.setString(5, u.getPassword());
            pstmt.setDate(6, new java.sql.Date(u.getRegistered().getTime()));
            pstmt.executeUpdate();
            // find the database id of the inserted item
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT seq_mcuser_id.currval FROM dual");
            rs.next();
            long id = rs.getLong(1);
            u.setId(id);
            return id;
        } catch (Exception e) {
            e.printStackTrace(System.err);
            return -1;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    private User getUserFromCursor(ResultSet rs) throws SQLException {
        User u = new User();
        u.setId(rs.getLong("mcuser_id"));
        u.setEmail(rs.getString("email"));
        u.setFirstName(rs.getString("firstname"));
        u.setLastName(rs.getString("lastname"));
        u.setLoginId(rs.getString("loginid"));
        u.setPassword(rs.getString("password"));
        return u;
    }
}
  1. Create a JUnit test for UserDAO
package project3.dao;

import java.util.Date;

import java.util.List;

import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import project3.User;


public class UserDAOTest {
   
    private UserDAO dao;
   
    private static long startTime;

    @BeforeClass
    public static void startTimer() {
            startTime = System.currentTimeMillis();
    }

    @AfterClass
    public static void endTimer() {
            long elapsedMsec = System.currentTimeMillis() - startTime;
            System.err.println("Elapsed time for Oracle NOT-pooled is " + elapsedMsec);
    }



    @Before
    public void setUp() throws Exception {
        dao = new UserDAO();
        User u1 = new User();
        u1.setEmail("aa@aa");
        u1.setFirstName("fName1");
        u1.setLastName("lName1");
        u1.setLoginId("login1");
        u1.setPassword("passw1");
        u1.setRegistered(new Date());
       
        dao.save(u1);
    }    

    /**
     * @see UserDAO#getAll()
     */
    @Test
    public void testGetAll() {
        List<User> users = dao.getAll();
        Assert.assertEquals(1,users.size());
        User u2 = users.get(0);
        Assert.assertTrue(u2.getId() > 0L);
        Assert.assertEquals("a@a",u2.getEmail());
        Assert.assertTrue(u2.getRegistered().getTime() > 0L);
    }
}


In case of MySQL the database creation commands and some SQL statements are different - one has to use AUTO_INCREMENT rather than SEQUENCE (log in as root and execute the following): 

CREATE DATABASE mctesting;
USE mctesting;
CREATE TABLE mcuser (mcuser_id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(50) NOT NULL,  
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    loginid VARCHAR(20) NOT NULL,
    password VARCHAR(20) NOT NULL,
    registered DATETIME NOT NULL,
    PRIMARY KEY(mcuser_id));
insert into mcuser VALUES (NULL, "a","b","c","d","e",NOW());
...
  1. Create a MySQL user 'mctesting': 
create user mctesting identified by 'mctesting';
grant all on mctesting.* to 'mctesting'@'%';
flush privileges;

Bibliography

  1. Good housekeeping practices - how to release resources, when anything can throw an exception? 
Tags:
Created by Kalvis Apsītis on 2008-03-12 18:17
    
This wiki is licensed under a Creative Commons 2.0 license
XWiki Enterprise 6.4 - Documentation