Java tutorial
/* * Copyright 2009-2014 Jagornet Technologies, LLC. All Rights Reserved. * * This software is the proprietary information of Jagornet Technologies, LLC. * Use is subject to license terms. * */ /* * This file JdbcIaPrefixDAO.java is part of Jagornet DHCP. * * Jagornet DHCP is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * Jagornet DHCP is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with Jagornet DHCP. If not, see <http://www.gnu.org/licenses/>. * */ package com.jagornet.dhcp.db; import java.net.InetAddress; import java.net.UnknownHostException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.PreparedStatementSetter; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.jdbc.support.GeneratedKeyHolder; import com.jagornet.dhcp.server.request.binding.Range; import com.jagornet.dhcp.util.Subnet; import com.jagornet.dhcp.util.Util; /** * The JdbcIaPrefixDAO implementation class for the IaPrefixDAO interface. * * @author A. Gregory Rabil */ public class JdbcIaPrefixDAO extends JdbcDaoSupport implements IaPrefixDAO { private static Logger log = LoggerFactory.getLogger(JdbcIaPrefixDAO.class); /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#create(com.jagornet.dhcpv6.db.IaPrefix) */ public void create(final IaPrefix iaPrefix) { /** * Note: see https://issues.apache.org/jira/browse/DERBY-3609 * "Formally, Derby does not support getGeneratedKeys since * DatabaseMetaData.supportsGetGeneratedKeys() returns false. * However, Statement.getGeneratedKeys() is partially implemented, * ... since it will only return a meaningful result when an single * row insert is done with INSERT...VALUES" * * Spring has thus provided a workaround as described here: * http://jira.springframework.org/browse/SPR-5306 */ GeneratedKeyHolder newKey = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement( "insert into iaprefix" + " (prefixaddress, prefixlength, starttime, preferredendtime," + " validendtime, state, identityassoc_id)" + " values (?, ?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS); ps.setBytes(1, iaPrefix.getIpAddress().getAddress()); ps.setInt(2, iaPrefix.getPrefixLength()); java.sql.Timestamp sts = new java.sql.Timestamp(iaPrefix.getStartTime().getTime()); ps.setTimestamp(3, sts, Util.GMT_CALENDAR); java.sql.Timestamp pts = new java.sql.Timestamp(iaPrefix.getPreferredEndTime().getTime()); ps.setTimestamp(4, pts, Util.GMT_CALENDAR); java.sql.Timestamp vts = new java.sql.Timestamp(iaPrefix.getValidEndTime().getTime()); ps.setTimestamp(5, vts, Util.GMT_CALENDAR); ps.setByte(6, iaPrefix.getState()); ps.setLong(7, iaPrefix.getIdentityAssocId()); return ps; } }, newKey); Number newId = newKey.getKey(); if (newId != null) { iaPrefix.setId(newId.longValue()); } } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#update(com.jagornet.dhcpv6.db.IaPrefix) */ public void update(final IaPrefix iaPrefix) { String updateQuery = "update iaprefix" + " set prefixaddress=?," + " prefixlength=?," + " starttime=?," + " preferredendtime=?," + " validendtime=?," + " state=?," + " identityassoc_id=?" + " where id=?"; getJdbcTemplate().update(updateQuery, new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setBytes(1, iaPrefix.getIpAddress().getAddress()); ps.setInt(2, iaPrefix.getPrefixLength()); Date start = iaPrefix.getStartTime(); if (start != null) { java.sql.Timestamp sts = new java.sql.Timestamp(start.getTime()); ps.setTimestamp(3, sts, Util.GMT_CALENDAR); } else { ps.setNull(3, java.sql.Types.TIMESTAMP); } Date preferred = iaPrefix.getPreferredEndTime(); if (preferred != null) { java.sql.Timestamp pts = new java.sql.Timestamp(preferred.getTime()); ps.setTimestamp(4, pts, Util.GMT_CALENDAR); } else { ps.setNull(4, java.sql.Types.TIMESTAMP); } Date valid = iaPrefix.getValidEndTime(); if (valid != null) { java.sql.Timestamp vts = new java.sql.Timestamp(valid.getTime()); ps.setTimestamp(5, vts, Util.GMT_CALENDAR); } else { ps.setNull(5, java.sql.Types.TIMESTAMP); } ps.setByte(6, iaPrefix.getState()); ps.setLong(7, iaPrefix.getIdentityAssocId()); ps.setLong(8, iaPrefix.getId()); } }); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#getById() */ public IaPrefix getById(Long id) { try { return getJdbcTemplate().queryForObject("select * from iaprefix where id = ?", new IaPrefixRowMapper(), id); } catch (EmptyResultDataAccessException ex) { log.warn("IaPrefix not found for ID=" + id + ": " + ex); return null; } } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#getByInetAddress(InetAddress) */ public IaPrefix getByInetAddress(InetAddress inetAddr) { return getJdbcTemplate().queryForObject("select * from iaprefix where ipprefix = ?", new IaPrefixRowMapper(), inetAddr.getAddress()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#deleteById(java.lang.Long) */ public void deleteById(Long id) { getJdbcTemplate().update("delete from iaprefix where id = ?", id); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#deleteByInetAddress(java.net.InetAddress) */ public void deleteByInetAddress(InetAddress inetAddr) { getJdbcTemplate().update("delete from iaprefix where prefixaddress = ?", inetAddr.getAddress()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#deleteNotInRanges(java.util.List) */ public void deleteNotInRanges(List<Range> ranges) { List<byte[]> args = new ArrayList<byte[]>(); StringBuilder query = new StringBuilder(); query.append("delete from iaprefix where prefixaddress"); Iterator<Range> rangeIter = ranges.iterator(); while (rangeIter.hasNext()) { Range range = rangeIter.next(); query.append(" not between ? and ?"); args.add(range.getStartAddress().getAddress()); args.add(range.getEndAddress().getAddress()); if (rangeIter.hasNext()) query.append(" and prefixaddress"); } getJdbcTemplate().update(query.toString(), args.toArray()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#findAllByIdentityAssocId(long) */ public List<IaPrefix> findAllByIdentityAssocId(final long identityAssocId) { return getJdbcTemplate().query("select * from iaprefix where identityassoc_id = ? order by prefixaddress", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setLong(1, identityAssocId); } }, new IaPrefixRowMapper()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#findAllBySubnet(com.jagornet.dhcpv6.util.Subnet) */ public List<IaPrefix> findAllBySubnet(Subnet subnet) { return findAllByRange(subnet.getSubnetAddress(), subnet.getEndAddress()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#findAllByRange(java.net.InetAddress, java.net.InetAddress) */ public List<IaPrefix> findAllByRange(final InetAddress startAddr, final InetAddress endAddr) { return getJdbcTemplate().query("select * from iaprefix" + " where prefixaddress >= ? and prefixaddress <= ?" + " order by prefixaddress", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setBytes(1, startAddr.getAddress()); ps.setBytes(2, endAddr.getAddress()); } }, new IaPrefixRowMapper()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#findAllOlderThan(java.util.Date) */ public List<IaPrefix> findAllOlderThan(Date date) { return getJdbcTemplate().query( "select * from iaprefix" + " join identityassoc ia on identityassoc_id=ia.id" + " where ia.iatype = ?" + " and validendtime < ? order by validendtime", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setByte(1, IdentityAssoc.PD_TYPE); java.sql.Timestamp ts = new java.sql.Timestamp(new Date().getTime()); ps.setTimestamp(2, ts, Util.GMT_CALENDAR); } }, new IaPrefixRowMapper()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#findUnusedByRange(java.net.InetAddress, java.net.InetAddress) */ public List<IaPrefix> findUnusedByRange(final InetAddress startAddr, final InetAddress endAddr) { final long offerExpiration = new Date().getTime() - 12000; // 2 min = 120 sec = 12000 ms return getJdbcTemplate().query("select * from iaprefix" + " where ((state=" + IaPrefix.ADVERTISED + " and starttime <= ?)" + " or (state=" + IaPrefix.EXPIRED + " or state=" + IaPrefix.RELEASED + "))" + " and prefixaddress >= ? and prefixaddress <= ?" + " order by state, validendtime, ipaddress", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { java.sql.Timestamp ts = new java.sql.Timestamp(offerExpiration); ps.setTimestamp(1, ts); ps.setBytes(2, startAddr.getAddress()); ps.setBytes(3, endAddr.getAddress()); } }, new IaPrefixRowMapper()); } /* (non-Javadoc) * @see com.jagornet.dhcpv6.db.IaPrefixDAO#findExistingIPs(java.net.InetAddress, java.net.InetAddress) */ public List<InetAddress> findExistingIPs(final InetAddress startAddr, final InetAddress endAddr) { return getJdbcTemplate().query("select prefixaddress from iaprefix" + " where prefixaddress >= ? and prefixaddress <= ?" + " order by prefixaddress", new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setBytes(1, startAddr.getAddress()); ps.setBytes(2, endAddr.getAddress()); } }, new RowMapper<InetAddress>() { @Override public InetAddress mapRow(ResultSet rs, int rowNum) throws SQLException { InetAddress inetAddr = null; try { inetAddr = InetAddress.getByAddress(rs.getBytes("prefixaddress")); } catch (UnknownHostException e) { // re-throw as SQLException throw new SQLException("Unable to map prefixaddress", e); } return inetAddr; } }); } /** * The Class IaPrefixRowMapper. */ protected class IaPrefixRowMapper implements RowMapper<IaPrefix> { /* (non-Javadoc) * @see org.springframework.jdbc.core.simple.RowMapper#mapRow(java.sql.ResultSet, int) */ @Override public IaPrefix mapRow(ResultSet rs, int rowNum) throws SQLException { IaPrefix iaPrefix = new IaPrefix(); iaPrefix.setId(rs.getLong("id")); try { iaPrefix.setIpAddress(InetAddress.getByAddress(rs.getBytes("prefixaddress"))); } catch (UnknownHostException e) { // re-throw as SQLException throw new SQLException("Unable to map ipaddress", e); } iaPrefix.setPrefixLength(rs.getShort("prefixlength")); iaPrefix.setStartTime(rs.getTimestamp("starttime", Util.GMT_CALENDAR)); iaPrefix.setPreferredEndTime(rs.getTimestamp("preferredendtime", Util.GMT_CALENDAR)); iaPrefix.setValidEndTime(rs.getTimestamp("validendtime", Util.GMT_CALENDAR)); iaPrefix.setState(rs.getByte("state")); iaPrefix.setIdentityAssocId(rs.getLong("identityassoc_id")); return iaPrefix; } } }