执行多条SQL语句,
用的是Statement自带的方法,这也是和PrepareStatement
。顺便说说,PrepareStatement适合执行需要外部传值的SQL语句,因为它可以对SQ
L预处理进行赋值(有内置的setInt/setString)。
下面是执行多条SQL语句:
Statement sm = null;
try
{
dpConn = DbPoolConnection.getInstance().getConnection();
dpConn.setAutoCommit(false);
sm = dpConn.createStatement();
String[] ids = toID.split(",");
for (int i = 0; i < ids.length; i++)
{
// System.out.println("UPDATE pushmsg a,pushmsg b SET b.pushSwitch = a.pushSwitch where a.id="
// + fromID + " and b.id=" + ids[i]);
sm.addBatch("UPDATE pushmsg a,pushmsg b SET b.pushMarketSign = a.pushMarketSign,b.pushSwitch = a.pushSwitch,b.pushTime = a.pushTime,b.pushInterval = a.pushInterval,b.pushTitle = a.pushTitle where a.id="
+ fromID + " and b.id=" + ids[i]);
}
sm.executeBatch();
dpConn.commit();
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DBManage.close(rs, sm, dpConn);
}
Mysql一个独特的函数语法:
如果你的Mysql数据库中有个字段是这样的结构
那么你一定用得上这个函数,比如 当你需要查询含有25的pushID记录的时候,你就可以这样:
SELECT * from TANBLE_NAME where find_in_set('25',pushID); 此外,在Mysql中貌似不支持
字段和
变量用"+"拼接,如:
update wallpaper set pushID = pushID + "test" where id = 1
请相信我,这样的结果一定不是你想要的,用这个吧
update wallpaper set pushID = CONCAT(pushID,'test')
还有,在一张表里将 id=1 的记录中的某个字段 更新到 id=2里面去,你会怎么做:
update table_name a set a.column_name = b.column_name from (select * from table_name where id=4) b where a.id=1
或者
UPDATE table_name SET column_name = (SELECT column_name FROM table_name WHERE id = 1) WHERE id = 2;
相信我,那样是不行的,会报错”update 后面不能跟from条件“,用这个吧:
UPDATE pushmsg a,pushmsg b SET a.pushSwitch = b.pushSwitch where a.id=1 and b.id=4;
我的博客其他文章列表
http://my.oschina.net/helu