【DBUtils】Springmvc 结合 dbUtils

1. 引入对应的依赖文件：

pom.xml

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.0.2.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.8.7</version>
    </dependency>

    <dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
      <version>1.4</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.11</version>
    </dependency>

    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>

 

2. 创建 Config 文件夹，并创建配置文件：

SpringMVC.java

@Configuration
// 配置只扫描Controller
//@ComponentScan(value = "xyz.tom.www", includeFilters = {
//    @ComponentScan.Filter(type = FilterType.ANNOTATION, classes = {Controller.class})
//})
@ComponentScan("xyz.tom.www")
// 引入子配置文件
@Import(JdbcConfig.class)
//载入配置文件
@PropertySource("classpath:jdbcConfig.properties")
@EnableWebMvc // 启动SpringMVC配置
public class SpringMVCConfig {

}

jdbcConfig.java

public class JdbcConfig {

    @Value("${jdbc.driver}")
    private String driver;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;

    @Bean("runner")
    public QueryRunner createQueryRunner(DataSource dataSource) {
        return new QueryRunner(dataSource);
    }

    @Bean(name="dataSource")
    public DataSource createDataSource() {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        try {
            comboPooledDataSource.setDriverClass(driver);
            comboPooledDataSource.setJdbcUrl(url);
            comboPooledDataSource.setUser(username);
            comboPooledDataSource.setPassword(password);
            return comboPooledDataSource;
        } catch (PropertyVetoException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
}

 

3. 创建配置文件

resources 下创建 jdbcConfig.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://www.xxxx.xyz:3306/testdb?useUnicode=true&characterEncoding=utf8&useSSL=false
jdbc.username=root
jdbc.password=root

4. 使用

@Repository("userDao")
public class UserDaoImpl implements UserDao {

    @Autowired
    private QueryRunner runner;

//    @Autowired
//    private ConnectionUtils connectionUtils;

    @Override
    public int save(User user) {
        try {
            System.out.println("持久层 注册账户");
            return runner.update("insert into user(account,password) values(?,?)",user.getAccount(), user.getPassword());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public User getObjectById(int id) {
        try {
            List<User> lists = runner.query("select * from user where id = ?", new BeanListHandler<User>(User.class), id);
            if (lists == null || lists.size() == 0) {
                return null;
            }
            if(lists.size() > 0) {
                throw new RuntimeException("结果集不唯一！");
            }
            return lists.get(0);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public User getObjectByAcc(String acc) {
        System.out.println("持久层 查找用户");
        try {
            List<User> lists = runner.query("select * from user where account = ?", new BeanListHandler<User>(User.class), acc);
            if (lists == null || lists.size() == 0) {
                return null;
            }
            if(lists.size() > 1) {
                throw new RuntimeException("结果集不唯一！");
            }
            return lists.get(0);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public int update(User user) {
        System.out.println("持久层 更新用户");
        try {
            return runner.update("update user set password=? where account = ?", user.getPassword(), user.getAccount());
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public List<User> getAll() {
        try {
            return runner.query("select * from user", new BeanListHandler<User>(User.class));
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public int remove(User user) {
        try {
            return runner.update("delete from user where account = ?", user.getAccount());
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
}