symfony2 数据库操作

预定义文中用到的变量：

$em = $this->getDoctrine()->getEntityManager();

$repository = $em->getRepository(‘AcmeStoreBundle:Product’)；

1.常用的查询

$repository->find($id); //获取的是一条数据

$repository->findAll();  //获取的是数组

$repository->findOneByName(‘Foo’);//获取的是一条数据

$repository->findBy(array(‘name’ => ‘foo’，‘price’ => 19.99),array(‘price’ => ‘ASC’));//获取的是一个数组

2、DQL

例题1.

$query = $em->createQuery(
‘SELECT p FROM AcmeStoreBundle:Product p WHERE p.price > :price ORDER BY p.price ASC’
)->setParameter(‘price’, ’19.99′);

$products = $query->getResult();

注：

（1） 获得一个结果可以用：$product = $query->getSingleResult();

（2） setParameter(‘price’, ’19.99′);运用这个外部方法来设置查询语句中的 “占位符”price 的值，而不是直接将数值写入查询语句中，有利于防止SQL注入攻击，你也可以设置多个参数：

->setParameters(array(
‘price’ => ’19.99′,
‘name’ => ‘Foo’,
))

例题2：

$query = $em->createQuery('SELECT min(bp.price) as minPrice FROM AppBundle:BookPackage bp WHERE bp.bookId=:bookId and bp.status<>2');
$query->setParameter('bookId', $bookId);
return $query->getSingleScalarResult();

例题3、
$goodsIdListString = $this->_getGoodsIdList($materialList);
$em = $this->getDoctrine()->getManager();
$query = $em->createQuery('SELECT p  FROM AppBundle:Photo p WHERE p.subjectId in ('.$goodsIdListString.') and p.type=1 and p.status=0  ORDER BY p.createTime DESC');
$photoList = $query->getResult();

例题4、
if ($status == InventoryOrder::STATUS_SUBMITTED) {
    $index = 'i.submitTime';
} else if ($status == InventoryOrder::STATUS_UNSUBMITTED) {
    $index = 'i.createTime';
} else if (empty($status)) {
    $index = 'i.createTime';
} else {
    throw new \Exception('时间格式不正确', BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}

$sql = 'SELECT i FROM AppBundle:InventoryOrder i WHERE ';
if(empty($startDate)) {
    throw new \Exception('起始时间不为空', BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
if (empty($endDate)) {
    $endDate = $startDate;
}
$startTimestamp = strtotime($startDate);
$endTimestamp = strtotime($endDate);

if ($startTimestamp > $endTimestamp) {
    throw new \Exception('时间参数错误', BaseException::ERROR_CODE_ILLEGAL_PARAMETER);
}
$sql .= $index;
$sql .= ' BETWEEN :startDate AND :endDate ';
$start = date('Y-m-d 00:00:00', $startTimestamp);
$end = date('Y-m-d 23:59:59', $endTimestamp);
$params['endDate'] = $end;
$params['startDate'] = $start;
if (!empty($status)) {
    $sql .= ' AND i.status = :status';
    $params['status'] = $status;
}
$sql .=' ORDER By i.createTime DESC';

$query = $this->entityManager->createQuery($sql);
$orderList = $query->setParameters($params)->getResult();

3.Query Builder查询
例题1、
$query = $repository->createQueryBuilder(‘p’) ->where(‘p.price > :price’) ->setParameter(‘price’, ’19.99′) ->orderBy(‘p.price’, ‘ASC’) ->getQuery();

$products = $query->getResult();

例题2、
这其实是一个分页的查询
$repository = $this->getDoctrine()
    ->getRepository('AppBundle:Goods');
$query = $repository->createQueryBuilder('p')
    ->where('p.name like :name')
    ->andwhere('p.status = 0')
    ->setParameter('name', "$fuzzyGoodsInfo")
    ->orderBy('p.sales', 'DESC')
    ->setFirstResult($pageSize * $page)  
    ->setMaxResults($pageSize)  //相当于limit  取多少条数据 setLimit(100);
    ->getQuery();
$goodsList = $query->getResult();

例题3.使用queryBuilder方法：查询一个数组结果：materialId等于$materialId，并且action在$actionList数组内的。并且按照时间排序
$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$resultList = $queryBuilder->select('s')
    ->from('AppBundle:StockHistory', 's')
    ->Where('s.materialId = :materialId')
    ->andWhere($queryBuilder->expr()->in('s.action', $actionList))
    ->orderBy('s.createTime', 'DESC')
    ->setParameter('materialId', $materialId)
    ->getQuery()->getResult();

4.SQL的更新
$query = $em->createQuery("UPDATE AppBundle:ReceiverAddress u SET u.defaultFlag = 0 WHERE u.userId = :userId")->setParameter('userId',$userId);
$result = $query->getResult();