**********Mapper动态代理开发*********
四大原则:
1> 接口方法需要与mapper.xml要调用的sql语句的id一致;
2> 接口形参类型需要与mapper.xml中parameterType一致;
3> 接口返回值需要与mapper.xml中ResultType一致;
4> mapper.xml中namespace要与接口的全包名一致;
-----------------------------------
**********Mapper动态代理开发*********
四大原则:
1> 接口方法需要与mapper.xml要调用的sql语句的id一致;
2> 接口形参类型需要与mapper.xml中parameterType一致;
3> 接口返回值需要与mapper.xml中ResultType一致;
4> mapper.xml中namespace要与接口的全包名一致;
-----------------------------------
*********使用MyBatis开发Dao层**********
1> UserDo.java
public interface UserDao{
//根据id查询用户
}
************根据ID删除用户***********
1> HelloMyBaties.java
class HelloMyBaties{
@Test
public void Test5() throws IOException{
//配置文件
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource);
//需要sqlSessionFactoryBuilder
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionFactory
SqlSessionFactory ssf = ssfb.build(in);
//产生一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库(根据ID删除)
session.delete("UserMapper.deleteUserById",13);
//由于配置了JDBC事务,需要使用commit()来实际操作数据
session.commit();
}
}
2> UserMapper.xml
<!-- 根据ID删除用户 -->
<delete id = "deleteUserById" parameterType="Integer">
delete from user where u_id = #{u_id}
</delete>
****************修改用户****************
1> HelloMyBatis.java
class HelloMyBaties{
@Test
public void Test4() throws IOException{
//配置文件
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource);
//sqlSessionFactoryBuilder
SqlSessionFactoruBuilder ssfb = new sqlSessionFactoryBuilder();
//创建sqlSessionFactory
SqlSessionFactory ssf = ssfb.builder(in);
//生成一个sqlSession
SqlSession session = ssf.openSession();
//操作数据(原id=13的是小明)
User user = new User();
user.setU_id(13);
user.setU_username("张飞");
user.setU_password("111111");
user.setU_sex("2");
session.update("UserMapper.updateUser",user);
session.commit();
}
}
2> UserMapper.xml
<!-- 修改用户 -->
<update id="updateUser" parameterType="com.XXX.bean.User">
update user set u_username = #{u_username},u_password=#{u_password},u_sex=#{u_sex}
where u_id = #{u_id}
</update>
**************添加用户***************
1> HelloMyBatis.java
class HelloMyBatis{
public void test3() throws IOException{
//配置文件
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resources.getResourceAsStream(resource);
//需要sqlSessionFactoryBuiler
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建SqlSessionFactory
SqlSessionFactory ssf = ssfb.build(in);
//产生一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
User user = new User();
user.setU_username("小明");
user.setU_password("123456");
user.setU_sex("1");
user.setU_createTime(new Date());
user.setU_cid(1);
session.insert("UserMapper.insertUser",user);
//由于配置文件中开启了JDBC事务,需要commit(),才能真正完成添加操作
session.commit();
}
}
2> UserMapper.xml
<!-- 添加用户 -->
<insert id="insertUser" parameterType="com.XXX.bean.User">
INSERT INTO user VALUES(NULL,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})
</insert>
字符串拼接 ${} 与 占位符#{} 区别 :
1> #{} 防SQL注入攻击,尽量使用
2> ${}:${1} <==> 1 (没有引号)
${王} <==>王 (没有引号)
所以SQL语句中 LIKE '%${value}%',等价于 LIKE '%王%'。
3> #{}:#{1} <==> '1'
#{王} <==> '王'
SQL语句中使用时 LIKE "%"#{name}"%",等价于 LIKE "%"'王'"%"
*******根据名字模糊查询********
1> HelloMyBatis.java
public class HelloMyBatis{
@Test
public void Test2() throws IOException{
//配置文件
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resouces.getResourceAsStream(resource);
//需要sqlSessionFactoryBuilder
sqlSessionFactoryBuilder ssfb = new sqlSessionFactoruBuilder();
//创建sqlSessionFactory
sqlSessionFactory ssf = ssfb.builder(in);
//产生一个sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2:sql语句的参数
List<User> list= session.selectList("UserMapper.selectUserByName","王");
for(User u : list){
System.out.println(u);
}
}
}
2> UserMapper.xml
<!-- ${value}遵循的是OGNL表达式,内部必须为value,其他值会报错 -->
<select id="selectUserByName" parameterType="String" resultType="com.XXX.bean.User">
SELECT * FROM USER WHERE u_username LIKE '%${value}%'
</select>
************根据ID查询************
1> HelloMybatis.java
public class HelloMyBatis{
@Test
public void Test()ThrowsIOException{
//配置文件路径
String resource = "sqlMapConfig.xml";
//读取配置文件
InputStream in = Resource.getResourceAsStream(resource);
//需要sqlSessionFactoryBuilder
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//创建sqlSessionFactory
SqlSessionFactory ssf = ssfb.build(in);
//创建sqlSession
SqlSession session = ssf.openSession();
//操作数据库
//参数1:要操作的sql语句 参数2:sql语句的参数
User user = session.selectOne("UserMapper.selectUserById",1);
System.out.println(user);
}
}
2> UserMapper.xml
<mapper namespace="UserMapper">
<select id="selectUserById" parameterType="Integer" resultType="com.XXX.bean.User">
select * from user where u_id = #{id}
</select>
</mapper>
-----------------------------------------------
3>引入日志文件log4j.properties
#日志级别
log4j.rootLogger = DEBUG,stdout
#控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleApperder
log4j.appender.stdout.layout=org.apache.log4j.Patternlayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
注:笔记内容只是记录,未进行实测
where标签可以去掉开头的and标签
关联查询
sql:select * from user u join country c on u.'u_cid'=c.'c_id'
<resultMap type=
注意:
在mapper动态开发中,根据返回值类型自动选择
四大原则:
1、接口方法名需要与mapper.xml中的sql语句的id一致
2、接口的形参类型需要与mapper.xml parameterType一致
3、接口的返回值需要与mapper.xml resultType一致
4、mapper.xml中的namespace要与接口的全包名一致
commit():提交给数据库
占位符拼接