symfony mysql_symfony2中mysql和mongodb的增删改查总结

╰+哭是因爲堅強的太久メ 2023-01-11 14:26 127阅读 0赞

预定义文中用到的变量：

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

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

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