symfony2中mysql和mongodb的增删改查总结

https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#api

$conn = $this->getDoctrine()->getConnection();
$hasAnswer = $conn->fetchAssoc("SELECT * FROM p_question_answer  where testPaperResultId = ? and questionId = ? and type = ?", [$id, $questionId, $type]);
if($hasAnswer) {
    $res = $conn->executeQuery("update p_question_answer set con = ? where id = ?", [$con, $hasAnswer['id']]);
} else {
    $res = $conn->executeQuery("insert into p_question_answer(`testPaperResultId`,`questionId`,`type`,`create_time`,`con`) value(?, ?, ?, ?, ?)", [$id,$questionId,$type,$nowTime,$con]);
}

 

 

生成doctrine 映射关系文件: https://symfony.com/doc/2.3/book/doctrine.html#add-mapping-information

预定义文中用到的变量：

$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’,
))

3.Query Builder查询

分页的查询

$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();

例: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();

更新一个对象包括三步：

1.从Doctrine取出对象
2.修改对象
3.在实体管理者上调用flush()方法

$em = $this->getDoctrine()->getEntityManager();
$repository = $em
　　->getRepository(‘AcmeStoreBundle:Product’)
　　->find($id);； 

if (!$repository) { 

　　throw $this->createNotFoundException('No product found for id '.$id); 
} 

$repository->setName('New product name!'); 
$em->flush(); 
return $this->redirect($this->generateUrl('homepage'));

5.删除

删除一个对象，需要从实体管理者那里调用remove()方法。

$em->remove($repository); 
$em->flush();

 remove()方法告诉Doctrine你想从数据库中移除指定的实体。真正的删除查询没有被真正的执行，直到flush()方法被调用。

6.增加数据

$product = new Product();

$product->setName('A Foo Bar');
$product->setPrice('19.99');
$product->setDescription('Lorem ipsum dolor');      

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

$em->persist($product);

$em->flush();

$product->getId();

 批量写入例子:

/**
* 批量写入数据
*
 *@author wyl
 *@param string $entity
 *@param array $dataList
 *@param array $per
*/

function batchInsertByEntity($entity, $dataList, $per = 1000)
{
    $count = count($dataList);
    for ($i = 0; $i < $count; $i ++) {
        $obj = new $entity();
        foreach ($dataList[$i] as $k => $v) {
        　　$obj->{"set" . $this->ucWords($k)}($v);
        }
        $this->em->persist($obj);
        if (($count % $per) === 0) {
            $this->em->flush();
            $this->em->clear();
        }
    }

    // 除不尽剩下的还是要保存的
    $this->em->flush();
    $this->em->clear();
}

 

二、mongodb的操作

更新操作 

->multiple(true)这句话的意思，可以删除所有goodsId==$goodsId的记录，不加的话，就只删除第一条数据

// 更新当前goodsId下的所有Material 状态status
$dm = $this->get('doctrine_mongodb')->getManager();
$dm->createQueryBuilder('AppBundle:Material')
    ->update()
    ->multiple(true)
    ->field('status')->set(2)
    ->field('goodsId')->equals($goodsId)
    ->getQuery()
    ->execute();

$dm->flush();

1.根据id直接查询一条记录

$dm = $this->get('doctrine_mongodb')->getManager();
$material = $dm->getRepository('AppBundle:Material')->find(new \MongoId($materialId));

2.往mongodb表中插入一条数据

$product = new Product();
$product->setName('A Foo Bar');
$product->setPrice('19.99');

$dm = $this->get('doctrine_mongodb')->getManager();
$dm->persist($product);
$dm->flush();

 

//定义量   $repository = $this->get('doctrine_mongodb') ->getManager() ->getRepository('AcmeStoreBundle:Product');

1.根据$id查询数据
$product = $repository->find($id);$product = $repository->findOneById($id);$product = $repository->findOneByName('foo');// find *all* products$products = $repository->findAll();$products = $repository->findByPrice(19.99);
$product = $repository->findOneBy(array('name' => 'foo', 'price' => 19.99));
//多个条件查询

$products = $repository->findBy(
array(
'name' => 'foo',
'status' => 0,
),
array('price', 'ASC')
);

//查询一条语句后，更改某个字段的值

$dm = $this->get('doctrine_mongodb')->getManager();
$product = $dm->getRepository('AcmeStoreBundle:Product')->find($id);
if (!$product) {
throw $this->createNotFoundException('No product found for id '.$id);
}

$product->setName('New product name!');
$dm->flush();

二、Query Builder查询

$products = $this->get('doctrine_mongodb')
->getManager()
->createQueryBuilder('AcmeStoreBundle:Product')
->field('name')->equals('foo')
->limit(10)
->sort('price', 'ASC')
->getQuery()
->execute()

 http://www.xuejiehome.com/blread-1920.html#index_4