Java-总结Spring中实现数据库连接几种方式

比眉伴天荒 2023-06-19 02:53 15阅读 0赞

## 一.QueryRunner ##

**1.Package**

import org.apache.commons.dbutils.QueryRunner;

**2.Maven-pom.xml**

<dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.0.10.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.18</version>
            </dependency>
            <dependency>
                <groupId>commons-dbutils</groupId>
                <artifactId>commons-dbutils</artifactId>
                <version>1.7</version>
            </dependency>
            <dependency>
                <groupId>c3p0</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.1</version>
            </dependency>
            <dependency>

**3.Code**

package config;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.ComponentScan;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.PropertySource;
    import javax.sql.DataSource;
    import java.beans.PropertyVetoException;
    import java.io.Serializable;
    
    @Configuration("jdbc_config")
    @ComponentScan("com.study")
    //标记：注册类路径下的properties文件
    @PropertySource("classpath:db.properties")
    public class JdbcConfig implements Serializable {
    
        @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("ds")
        public DataSource createDataSource() {
            try {
                ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
                comboPooledDataSource.setDriverClass(driver);
                comboPooledDataSource.setJdbcUrl(url);
                comboPooledDataSource.setUser(username);
                comboPooledDataSource.setPassword(password);
                return comboPooledDataSource;
            } catch (PropertyVetoException e) {
                e.printStackTrace();
            }
            return null;
        }
    }

/**
     * 数据持久层
     */
    @Repository("tuser_imp")
    public class TUserImp implements ITUser {
    
        //注入
        @Resource(name = "query_runner")
        private QueryRunner runner;
    
        public List<TUser> getAll() {
            try {
                System.out.println("TUserImp-getAll方法执行了。。。");
                String sql = "select * from t_user";
                return runner.query(sql, new BeanListHandler<TUser>(TUser.class));
                //return (List<TUser>) DbUtil.executeQuery(sql, new ListResultHandler(TUser.class), null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    
        public Integer update(TUser user) {
            try {
                String sql = "update t_user set t_name= '" + user.getT_name() + "'  where t_id=" + user.getT_id() + "";
                return runner.update(sql, null);
                //return DbUtil.executeUpdate(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    }

## 二.JdbcTemplate ##

**1.Package**

import org.springframework.jdbc.core.JdbcTemplate;

**2.Maven-pom.xml**

<dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.0.10.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.2.2.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>5.2.2.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.18</version>
            </dependency>

**3.Code**

package com.config;
    
    import com.mysql.cj.jdbc.MysqlDataSource;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.*;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    
    
    @Configuration("jdbctemplate_config")
    @ComponentScan("com")
    @PropertySource("classpath:db.properties")
    public class JdbcTemplateConfig {
    
        @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("jdbc_template")
        @Scope("prototype")
        public JdbcTemplate createJdbcTemplate() {
            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName(driver);
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            JdbcTemplate jdbcTemplate = new JdbcTemplate();
            jdbcTemplate.setDataSource(dataSource);
            return  jdbcTemplate;
        }
    
    }

/**
     * 数据持久层
     */
    @Repository("tuser_imp")
    public class TUserImp implements ITUser {
    
        @Resource(name = "jdbc_template")
        private JdbcTemplate jdbcTemplate;
    
        public List<TUser> getAll() {
            String sql = "select * from t_user";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper(TUser.class), null);
        }
    
        public TUser getRow(Integer id) {
            String sql = "select * from t_user where t_id=?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper(TUser.class), id).isEmpty() ? null : (TUser) jdbcTemplate.query(sql, new BeanPropertyRowMapper(TUser.class), id).get(0);
        }
    
        public Integer update(TUser user) {
            String sql = "update t_user set t_name=? where t_id=? ";
            return jdbcTemplate.update(sql, user.getT_name(), user.getT_id());
        }
    }