数据库:
create table school(
id int(11) not null auto_increment comment '主键',
name varchar(20) comment '学校名称',
address varchar(100) comment '学校地址',
create_time datatime comment '数据上传时间',
primary key (id)
)
实体类:
package com.test.entity;
public class School{
private Integer id;//主键id
private String name;//学校名称
private String address//学校地址
private Date createTime//数据上传时间
}
现在开始添加功能
mybaties: schooMapper.xml:
name,address,create_time
insert into school ()values(#{name},#{address},#{createTime})
下面的可以返回id,但是需要主键为自增(id在这儿要不要都可以)
insert into school (id,)values(#{id},#{name},#{address},#{createTime})
这个第二种添加可以返回主键id的,好像是返回添加的最后一个id--不需要自增(这个不确切知道)
insert into school ()values(#{name},#{address},#{createTime})
SELECT LAST_INSERT_ID() AS VALUE
--------现在开始批量添加
insert into school ()values
(#{l.name},#{l.address},#{l.createTime})
java代码:
public int save(School test);
public int saveReturnIdOne(School test);
public int saveReturnIdTwo(School test);
public void bathSave(List list);
现在开始删除功能
delete from school where id=#{id}
delete from school where id in
#{l}
delete from school where id in (${id})
java代码:
public int deleteById(Integer list);
public int bathDelete(List list);
public int bathDeleteTwo(Mapid);
现在开始修改功能
update school set name=#{name}, address=#{address}, create_time=#{createTime} where id=#{id,jdbcType=INTEGER}
update school
name=#{name},
address=#{address},
create_time=#{createTime}
where id=#{id}
update school
name=#{l.name},
address=#{l.address},
create_time=#{l.createTime}
where id=#{l.id,jdbcType=INTEGER}
java代码:
public void update(TestEntity test);
public void updateSet(TestEntity test);
public void bathUpdate(List list);
现在开始查询功能(批量查询和删除的一样)
xml:
select id, from school where id =#{id}
select id, from school where address=#{address} limit #{begin},#{end}
select id, from school where name like concat('%',#{name},'%')
select group_concat(id) from school where name like concat('%',#{name},'%')
select id, from school
id=#{id}
and name=#{name}
and address=#{address}
and create_time=#{createTime}
java代码:
public School getById(Integer id);
public List getLimit(Map map);
public List getLikeName(String name);
public String getIdsLikeName(String name);
public List getWhere(School test);
下面是从其它地方看到:
同时执行多条sql的办法:
1、最简单的办法:在MySQL的连接字符串中设置allowMultiQueries参数置为true。(只有MySQL Connector/J 3.1.1以上版本才支持) 。例如:在jdbc下设置连接字符串的时候设成如下的形式:
jdbc:mysql://192.168.3.180/sample?user=root&password=&allowMultiQueries=true就可以执行多条语句了
在odbc下也是可以设置的,方法如下:
设置 ODBC -- 配置 --Detials -- Flags 3 -- 钩上 Allow multiple statements,这样就可以了。
结论:第一种方式最简单。
2、在程序中对SQL语句以分号拆分成多条SQL语句,然后使用Statement的addBatch方法,最后executeBatch就行。
希望对以后遇到此类问题的朋友有所帮助。
关于Statement的execute(String sql)语句能够同时执行多条SQL语句, 可以看MySQL自带的测试例子:
可查看testsuite.regression包下的ResultSetRegressionTest类: 这个可以查看:MySQL for Java的SQL注入测试
public class ResultSetRegressionTest extends BaseTestCase {
public void testBug33678() throws Exception {
if (!versionMeetsMinimum(4, 1)) {
return;
}
createTable("testBug33678", "(field1 INT)");
// allowMultiQueries=true设置
Connection multiConn = getConnectionWithProps("allowMultiQueries=true");
Statement multiStmt = multiConn.createStatement();
try {
multiStmt.setFetchSize(Integer.MIN_VALUE);
// 一次性执行多条SQL语句
multiStmt.execute("SELECT 1 UNION SELECT 2; INSERT INTO testBug33678 VALUES (1); UPDATE testBug33678 set field1=2; INSERT INTO testBug33678 VALUES(3); UPDATE testBug33678 set field1=2 WHERE field1=3; UPDATE testBug33678 set field1=2; SELECT 1");
// 以下代码省略...
}
}