spring基础之JDBC配置

在spring配置文件配置数据库连接池

  <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mysql2209?userUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
<!--        配置jdbcTemplate对象，属性DataSource，将连接池组件注入-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

dao层使用jdbcTemplate实现数据库操作

注入jdbcTemplate
    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

1. upadte（String sql，Object args）方法实现单条增删改操作
    * 参数1.sql语句可传入预处理语句
    参数2：可变参数，替换sql语句值

    public int add(User user) {
        String sql="insert into user1 values(?,?,?)";
        Object[] args={user.getUserId(),user.getUserName(),user.getUserAge()};
   int update= jdbcTemplate.update(sql,args);
        return update;
    }
 @Override
    public int delete(int id) {
        String sql="delete from user1 where user_id=?";

         jdbcTemplate.update(sql,id);
        return  jdbcTemplate.update(sql,id);
    }

    @Override
    public int updateUserById(User user) {
       String sql="update user1 set user_name=?,user_age=? where user_id=?";
        Object[] args={user.getUserId(),user.getUserName(),user.getUserAge()};

        return jdbcTemplate.update(sql,args);
    }

.查询表中的某个数据，返回的是某个值

2.queryForObject（String sql，Class<T>requiredType）
    * 参数1：预处理的sql语句
    参数2：返回类型的class

  public Integer selectCount(){
        String sql="select count(*) from user1";
        Integer count=jdbcTemplate.queryForObject(sql,Integer.class);
   return count;
    }

 查询返回对象

 3. queryForObject（String sql，RowMapper<T> rowMapper，Object。。。args）
    参数1：预处理语句
    参数2：RowMapper接口，针对返回不同类型数据，使用该接口里边的实现类完成数据的封装
    参数3：替换sql语句值

  @Override
    public User selectUserById(int userId) {
       String sql="select*from user1 where user_id=?";
        RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
        User user=jdbcTemplate.queryForObject(sql,rowMapper,userId);
        return user;
    }

批量查询

4. query(String sql.RowMapper<T> rowMapper,Object....args)

参数1sql语句

参数2.实现数据的封装

参数3.sql的值

 

   public List<User> findAllUser() {
    
        String sql="select *from user1";
        List<User> userList=jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

 批量增删改

5.batchUpdate(String sql,List<Object[]> batchArgs)
* 参数1sql语句
* 参数2：List集合，添加多条记录数据

1.pom.xml配置文件导入

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>Spring_06_JDBC</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.2.22.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.21.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.2.22.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.2.22.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>5.2.22.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.2.22.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.16</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
    </dependencies>

</project>

2.xml文件实现数据库衔接

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="com.dltt"/>
<!--    jdbcTemplate
spring框架对jdbc进行封装，使用jdbcTemplate方便的对数据库操作
-->
<!--在spring配置文件配置数据库连接池-->
<!--    数据库连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mysql2209?userUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
<!--        配置jdbcTemplate对象，属性DataSource，将连接池组件注入-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

3.dao层数据持久层进行数据操作

package com.dltt.user.dao;

import com.dltt.user.vo.User;

import javax.jws.soap.SOAPBinding;

public interface UserDao {
    public int add(User user);
    public int delete(int id);
    public  int updateUserById(User user);
    public Integer selectCount();
    public  User selectUserById(int userId);
}







package com.dltt.user.dao.imp;

import com.dltt.user.dao.UserDao;
import com.dltt.user.vo.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

@Repository
public class ImplUserDao implements UserDao {
    /*在dao层使用jdbcTemplate实现数据库操作
    upadte（String sql，Object args）方法实现单条增删改操作
    * 参数1.sql语句可传入预处理语句
    参数2：可变参数，替换sql语句值
    * */
//注入jdbcTemplate
    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Override
    public int add(User user) {
        String sql="insert into user1 values(?,?,?)";
        Object[] args={user.getUserId(),user.getUserName(),user.getUserAge()};
   int update= jdbcTemplate.update(sql,args);
        return update;
    }

    @Override
    public int delete(int id) {
        String sql="delete from user1 where user_id=?";

         jdbcTemplate.update(sql,id);
        return  jdbcTemplate.update(sql,id);
    }

    @Override
    public int updateUserById(User user) {
       String sql="update user1 set user_name=?,user_age=? where user_id=?";
        Object[] args={user.getUserId(),user.getUserName(),user.getUserAge()};

        return jdbcTemplate.update(sql,args);
    }
    /*1.查询表中的某个数据，返回的是某个值
    2.使用queryForObject（String sql，Class<T>requiredType）
    * 参数1：预处理的sql语句
    参数2：返回类型的class
    * */
    public Integer selectCount(){
        String sql="select count(*) from user1";
        Integer count=jdbcTemplate.queryForObject(sql,Integer.class);
   return count;
    }


    /*查询返回对象
    queryForObject（String sql，RowMapper<T> rowMapper，Object。。。args）
    1：预处理语句
    参数2：RowMapper接口，针对返回不同类型数据，使用该接口里边的实现类完成数据的封装
    参数3：替换sql语句值
    *
    * */
    @Override
    public User selectUserById(int userId) {
       String sql="select*from user1 where user_id=?";
        RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
        User user=jdbcTemplate.queryForObject(sql,rowMapper,userId);
        return user;
    }
}

4.Test进行测试

package com.dltt.test;

import com.dltt.user.dao.UserDao;
import com.dltt.user.vo.User;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Test01 {
    @Test
    public void test01(){
        ApplicationContext app=new ClassPathXmlApplicationContext("app.xml");
        UserDao userDao=(UserDao) app.getBean("implUserDao");
        int result=userDao.add(new User(1002,"李四",19));
        System.out.println(result);
    }
    public void test02(){
        ApplicationContext app=new ClassPathXmlApplicationContext("app.xml");
        UserDao userDao=(UserDao) app.getBean("implUserDao");
        int result=userDao.updateUserById(new User(1002,"王五",22));
        System.out.println(result);
    }
    public void test03(){
        ApplicationContext app=new ClassPathXmlApplicationContext("app.xml");
        UserDao userDao=(UserDao) app.getBean("implUserDao");
        int result=userDao.delete(1002);
        System.out.println(result);
    }
    @Test
    public void test04(){
        ApplicationContext app=new ClassPathXmlApplicationContext("app.xml");
        UserDao userDao=(UserDao) app.getBean("implUserDao");
        System.out.println(userDao.selectCount());
    }
    @Test
    public void test05(){
        ApplicationContext app=new ClassPathXmlApplicationContext("app.xml");
        UserDao userDao=(UserDao) app.getBean("implUserDao");
        System.out.println(userDao.selectUserById(1001));
    }
}