数据库的基本操作就是增删改查,这一节我们演示下通过mybatis怎样操作数据库以及怎样在mybaits中配置SQL语句。我们知道,在mybaits中每个实体对象都对应一个Mapper文件,我们在mapper中配置好相关的增删改查语句,
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapping.skusMapper">
<!-- 指定查询SQL, parameterType是传入参数类型,resultType是返回结果-->
<select id="getSku" parameterType="int"
resultType="dto.skus">
SELECT * FROM skus WHERE id=#{id}
</select>
<!-- 添加 -->
<insert id="addSku" parameterType="dto.skus">
INSERT INTO skus (skuname,skucode) VALUES (#{SkuName},#{SkuCode})
</insert>
<!-- 删除 -->
<delete id="delSku" parameterType="java.lang.String">
DELETE FROM skus WHERE SkuCode=#{_parameter}
</delete>
<!-- 修改 -->
<update id="updateSku" parameterType="dto.skus">
UPDATE skus SET skuname=#{SkuName} WHERE SkuCode=#{SkuCode}
</update>
<!-- 查询全部 -->
<select id="getList" resultType="dto.skus">
SELECT Id,SkuName,SkuCode FROM skus
</select>
</mapper>
以上包括基本的增删改查和查询集合。其中parameterType是传入的参数类型,resultType是返回的结果类型,注意当个查询和查询所有结果,resultType的值是一样的。
接下来,我们看看怎么操作Mapper文件。
@Controller
@RequestMapping("/Home")
public class HomeController {
@Resource(name="applePhone")
private IMobilePhone phone;
@RequestMapping(value="index")
public String Index()
{
String msg=phone.PhoneBrand();
System.out.print(msg);
String resource = "/conf.xml";
//加载mybatis的配置文件
InputStream inputstream =this.getClass().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
SqlSession session = sessionFactory.openSession();
String statesql= "mapping.skusMapper.getSku";//在skusMapper.xml中有命名空间+方法名
skus sku = session.selectOne(statesql, 1);
System.out.println(sku.getSkuName());
return "index";
}
@RequestMapping(value="AddSku")
public String AddSku()
{
String resource = "/conf.xml";
//加载mybatis的配置文件
InputStream inputstream =this.getClass().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
SqlSession session = sessionFactory.openSession();
String statesql= "mapping.skusMapper.addSku";//在skusMapper.xml中有命名空间+方法名
skus sku=new skus();
sku.setSkuName("黑色手表");
sku.setSkuCode("00002");
int result = session.insert(statesql,sku);
session.commit();
session.close();
System.out.println(result);
return "index";
}
@RequestMapping(value="DelSku")
public String DelSku()
{
String resource = "/conf.xml";
//加载mybatis的配置文件
InputStream inputstream =this.getClass().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
SqlSession session = sessionFactory.openSession();
String statesql= "mapping.skusMapper.delSku";//在skusMapper.xml中有命名空间+方法名
int result = session.delete(statesql,"00002");
session.commit();
session.close();
System.out.println(result);
return "index";
}
@RequestMapping(value="UpdateSku")
public String UpdateSku()
{
String resource = "/conf.xml";
//加载mybatis的配置文件
InputStream inputstream =this.getClass().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
SqlSession session = sessionFactory.openSession();
String statesql= "mapping.skusMapper.updateSku";//在skusMapper.xml中有命名空间+方法名
skus sku=new skus();
sku.setSkuName("黑色手表男士款式");
sku.setSkuCode("00002");
int result = session.update(statesql, sku);
session.commit();
session.close();
System.out.println(result);
return "index";
}
@RequestMapping(value="List")
public String List()
{
String resource = "/conf.xml";
//加载mybatis的配置文件
InputStream inputstream =this.getClass().getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
SqlSession session = sessionFactory.openSession();
String statesql= "mapping.skusMapper.getList";//在skusMapper.xml中有命名空间+方法名
java.util.List<skus> listResult= session.selectList(statesql);
session.commit();
session.close();
for(dto.skus item:listResult)
{
System.out.print(item.getId()+"--"+item.getSkuCode()+"--"+item.getSkuName());
System.out.print("--------------");
}
return "index";
}
}
上面的代码演示了具体的增删改查操作方法。注意一点,如果插入数据中的中文为乱码,要把数据库连接配置为utf8,如下
<property name="url" value="jdbc:mysql://localhost:3306/erp?useUnicode=true&characterEncoding=utf8" />
&代表&符号。
</pre><p> 表结构如下</p><p> <pre name="code" class="sql">CREATE TABLE `skus` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`SkuName` varchar(50) DEFAULT NULL,
`SkuCode` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8