JavaWeb——JDBC与Mybatis学习笔记

JDBC 1.JDBC快速入门 01.JDBC快速入门—代码

        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //3.定义sql
        String sql = "update tb_user set password = 123 where id = 1";

        //4.获取执行sql的对象
        Statement stmt = conn.createStatement();

        //5.执行sql
        int count = stmt.executeUpdate(sql);//受影响的行数

        //6.处理结果
        System.out.println(count);

        //7.释放资源
        stmt.close();
        conn.close();

02.JDBC—API详解 connection

事务：要么都执行，要么都不执行，回滚事务是让已经执行的事务回到原来的样子。

        try {
            //开启事务
            conn.setAutoCommit(false);
            //5.执行sql--1
            int count1 = stmt.executeUpdate(sql1);//受影响的行数
            //6.处理结果
            System.out.println(count1);
            //5.执行sql--2
            int count2 = stmt.executeUpdate(sql2);//受影响的行数
            //6.处理结果
            System.out.println(count2);
            //关闭事务
            conn.commit();
        } catch (Exception throwables) {
            //回滚事务，出现异常回滚
            conn.rollback();
            throwables.printStackTrace();
        }

ResultSet

        //6.处理结果(列)
        while (rs.next()){
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int pw = rs.getInt(3);

            System.out.println("id = " + id);
            System.out.println("name = " + name);
            System.out.println("pw = " + pw);
            System.out.println("--------------");
        }

        //6.处理结果(名称)
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("username");
            int pw = rs.getInt("password");

            System.out.println("id = " + id);
            System.out.println("name = " + name);
            System.out.println("pw = " + pw);
            System.out.println("--------------");
        }

preparedStatement

防止注入

        String name = "zhangsan";
        String pwd = "'or'1'='1";  //如果不用这种方法就会登录成功
        //3.定义sql
        String sql = "select * from tb_user where username = ? and password = ?";

        //4.获取执行sql的对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //5.设置？的值
        pstmt.setString(1,username);
        pstmt.setString(2,password);
        //6.执行sql
        ResultSet rs = pstmt.executeQuery();

        //6.处理结果

        if (rs.next()){
            System.out.println("login");
        }else {
            System.out.println("no-login");
        }
        //7.释放资源
        pstmt.close();
        conn.close();

2.Druid快速入门

配置文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT&allowPublicKeyRetrieval=true
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

        //1.导jar包
        //2.配置数据文件
        //3.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("E:\IdeaJavaWeb\jdbc\druid.properties"));
        //4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据连接Connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

MyBatis 1.MyBatis快速入门 01.pom.xml

    
        
        
            org.mybatis
            mybatis
            3.5.5
        
        
        
            mysql
            mysql-connector-java
            5.1.46
        

        
            junit
            junit
            4.13
            test
        

        
        
            org.slf4j
            slf4j-api
            1.7.20
        
        
        
            ch.qos.logback
            logback-classic
            1.2.3
        
        
        
            ch.qos.logback
            logback-core
            1.2.3
        
    

02.mybatis-config.xml

03.UserMapper.xml

    select * from tb_user;
  

04.main函数

//1.记载Mybatis核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取sqlSession对象，用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.执行sql(test是mapper中的名称空间，selectAll是id)(如果查询1个结果可以用selectOne)
List users = sqlSession.selectList("test.selectAll");
System.out.println(users);
//4.释放资源
sqlSession.close();

2.Mapper代理开发

1.定义与SQL映射文件同名的Mapper接口，并且将Mapper接口和SQL映射文件放置在同一目录下

2.设置SQL映射文件的namespace属性为Mapper接口全限定名

3.在 Mapper 接口中定义方法，方法名就是SQL映射文件中sql语句的id，并保持参数类型和返回值类型一致

4.编码

​ 1.通过 SqlSession 的 getMapper方法获取 Mapper接口的代理对象

​ 2.调用对应方法完成sql的执行

01.pom.xml

 
        
        
            org.mybatis
            mybatis
            3.5.5
        
        
        
            mysql
            mysql-connector-java
            5.1.46
        

        
            junit
            junit
            4.13
            test
        

        
        
            org.slf4j
            slf4j-api
            1.7.20
        
        
        
            ch.qos.logback
            logback-classic
            1.2.3
        
        
        
            ch.qos.logback
            logback-core
            1.2.3
        
    

02.mybatis-config.xml

03.UserMapper.xml

    select * from tb_user;
  

 
04.UserMapper.java 
public interface UserMapper {

    List selectAll();
}

 
05.main函数 
//1.记载Mybatis核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取sqlSession对象，用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取UserMapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List users = userMapper.selectAll();
System.out.println(users);
//4.释放资源
sqlSession.close();
 
3.常见问题 
01.实体类属性名和数据库表列名不一致 
数据库表的字段名称 和 实体类的属性名称 不一样，则不能自动封装数据
 
1.起别名:对不一样的列表名起别名，让别名的实体类的属性名一样
 可以使用sql片段
 

    id, brand_name as brandName, company_name as companyName, ordered, description, status


    select 
    from tb_brand;

2.resultMap:
① 定义标签
② 在语句标签中，使用resultMap 替换 resultType 属性

    

        
        
    

    
        select *
        from tb_brand;
    
 
02.参数占位符、参数类型、特殊字符 
*参数占位符：
 
1. #{}：将其替换为 ？，为了防止sql注入
2. ${}：拼sql，会出现sql注入
 
使用时机：
 *参数传递的时候：#{}
 *表名或者列名不固定的时候：${}，但是会出现sql注入的问题
 
*参数类型:parameterType可以省略
 
*特殊字符处理：
 1.转义字符
 2.CDATA区
 

    select * from tb_brand where id
     
     #{id};

03.多条件查询

    
        select *
        from tb_brand
        where status = #{status}
        and   company_name like #{companyName}
        and   brand_name like #{brandName}
    
 
参数接受
 
1.散装参数
 
如果方法中有多个参数，需要使用@Param(“SQL参数占位符名称”)
 
List selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
 
int status = 1;
String companyName = "华为";
String brandName = "华为";
//参数处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";

List brands = brandMapper.selectByCondition(status, companyName, brandName);
 
2.对象参数
 
对象的属性名称要和参数占位符名称一致
 
List selectByCondition(Brand brand);
 
int status = 1;
String companyName = "华为";
String brandName = "华为";
//参数处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//封装对象-对象

Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);

List brands = brandMapper.selectByCondition(brand);
 
3.Map集合参数
 
List selectByCondition(Map map);
 
int status = 1;
String companyName = "华为";
String brandName = "华为";
//参数处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";

Map map = new HashMap();
map.put("status",status);
map.put("companyName",companyName);
map.put("brandName",brandName);

List brands = brandMapper.selectByCondition(map);
 
04.动态条件查询 
if：条件判断
 
​ test ：逻辑表达式
 
避免出现查询问题（第一个值为空，则会多一个and），有两种方法解决
 
1.通过恒等式解决
 
    
        select *
        from tb_brand
        where 1 = 1
        
            and status = #{status}
        
        
            and company_name like #{companyName}
        
        
            and brand_name like #{brandName}
        
    

2.通过标签替换where关键字

    
        select *
        from tb_brand
        
            
                and status = #{status}
            
            
                and company_name like #{companyName}
            
            
                and brand_name like #{brandName}
            
        
    
 
05.单条件动态查询 
用户选择按照状态、品牌名称、公司名称其中一个进行查询或者都不选择
 
标签相当于switch
 
int status = 1;
String companyName = "华为";
String brandName = "华为";
//参数处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//封装对象-对象
Brand brand = new Brand();
//brand.setStatus(status);
//brand.setCompanyName(companyName);
//brand.setBrandName(brandName);
 

    select *
    from tb_brand
    
        
            
                status = #{status}
            
            
                company_name like #{companyName}
            
            
                brand_name like #{brandName}
            
        
    

06.添加功能

※一定要在获取sqlSession对象的时候打开自动提交事务，不然添加完信息回滚，等于没加，增删改都需要

 
     insert into tb_brand(brand_name, company_name, ordered, description, status)
     VALUES (#{brandName},#{companyName},#{ordered},#{description},#{status})
 

int status = 1;
String companyName = "波导手机";
String brandName = "波导";
String description= "手机中的战斗机";
int ordered = 100;

//封装对象-对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
brand.setDescription(description);
brand.setOrdered(ordered);

//1.记载Mybatis核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//2.获取sqlSession对象，用来执行sql,
//true  自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);

//3.获取UserMapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

//4.执行
brandMapper.add(brand);

//5.释放
sqlSession.close();

添加完成后：主键返回

useGeneratedKeys=“true”

keyProperty=“id”

    insert into tb_brand(brand_name, company_name, ordered, description, status)
    VALUES (#{brandName},#{companyName},#{ordered},#{description},#{status})

brandMapper.add(brand);
Integer id = brand.getId();
System.out.println("id = " + id);

07.修改功能

修改全部字段

    update tb_brand
    set
        brand_name = #{brandName},
        company_name = #{companyName},
        ordered = #{ordered},
        description = #{description},
        status = #{status}
    where id = #{id};

int status = 1;
String companyName = "波导手机2.0";
String brandName = "波导";
String description= "手机中的战斗机,手机中的战斗机";
int ordered = 200;
int id = 6;

//封装对象-对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setId(id);


//1.记载Mybatis核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//2.获取sqlSession对象，用来执行sql,
//true  自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);

//3.获取UserMapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

//4.执行
int count = brandMapper.update(brand);
System.out.println("count = " + count);


//5.释放
sqlSession.close();

修改动态字段：修改哪个字段是不一定的，要使用动态SQL

使用 标签

    update tb_brand
    
        
            brand_name = #{brandName},
        
        
            company_name = #{companyName},
        
        
            ordered = #{ordered},
        
        
            description = #{description},
        
        
            status = #{status}
        
    
    where id = #{id};

08.删除功能

删除一个

    delete from tb_brand where id = #{id}

int id = 6;
//1.记载Mybatis核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取sqlSession对象，用来执行sql,
//true  自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.获取UserMapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行
brandMapper.deleteById(id);
//5.释放
sqlSession.close();

批量删除

int[] ids = {8,9,10};

//1.记载Mybatis核心配置文件，获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//2.获取sqlSession对象，用来执行sql,
//true  自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);

//3.获取UserMapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

//4.执行
brandMapper.deleteByIds(ids);

//5.释放
sqlSession.close();

Mybatis会将数组参数封装为一个Map集合

• 默认array = 数组

    delete from tb_brand where id
    in
    
        #{id}
    
    ;

void deleteByIds (int[] ids);

• 使用@Param注解改变map集合的默认Key的名称

void deleteByIds(@Param("ids") int[] ids);

    delete from tb_brand where id
    in
    
        #{id}
    
    ;

4.注解开发

直接把SQL语句写在Mapper里，不过只适合非常简单的SQL语句

@Select @Insert @Update @Delete

@Select("select * from tb_user where id = #{id}")
Brand selectByIdBrand(int id);