springboot - 集成mysql实现方式之一，DruidDataSource 完整代码实现

小灰灰 2022-06-08 02:15 149阅读 0赞

首先springboot 集成mysql的方式很多，jdbcTemplate ,mybatis 都可以；

这里选用的是阿里开源数据持久层处理框架：druid;    【强大到爆炸的工具，但也只是工具】

### 1.配置数据源 ###

.properties 文件配置数据源，加载驱动

#集成mysql
    #spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://ip:port/数据库名?useUnicode=true&characterEncoding=utf8&autoReconnect=true
    spring.datasource.username=用户名
    spring.datasource.password=密码
    spring.datasource.driverClassName=com.mysql.jdbc.Driver

### 2.添加jar依赖 ###

Druid 是阿里分布式，大数据，高性能，数据持久层解决方案

<dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
                <version>3.6</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>1.2.35</version>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.2</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>4.3.9.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.42</version>
            </dependency>
            <dependency>
                <groupId>commons-codec</groupId>
                <artifactId>commons-codec</artifactId>
                <version>1.10</version>
            </dependency>
            <dependency>
                <groupId>commons-collections</groupId>
                <artifactId>commons-collections</artifactId>
                <version>3.2.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.httpcomponents</groupId>
                <artifactId>httpclient</artifactId>
                <version>4.5.3</version>
            </dependency>

### 3、配置加载 ###

import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    
    import javax.sql.DataSource;
    
    /**
     *
     */
    //@Component
    @Configuration
    public class JDBCConfig {
        @Autowired
        private Environment env;
    
        //JDBC------------------------------------------------
        @Bean
        public DataSource dataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setUrl(env.getProperty("spring.datasource.url"));
            dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
            dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
            dataSource.setInitialSize(2);
            dataSource.setMaxActive(20);
            dataSource.setMinIdle(0);
            dataSource.setMaxWait(60000);
            dataSource.setValidationQuery("SELECT 1");
            dataSource.setTestOnBorrow(false);
            dataSource.setTestWhileIdle(true);
            dataSource.setPoolPreparedStatements(false);
            return dataSource;
        }
    
    }

## 4.编写持久层代码： ##

import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.util.List;
    
    
    /**
     * Created by steadyjack on 2017/3/22.
     * 充当dao层UserRepository
     */
    @Repository("userRepository")
    public class UserRepository {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        /**
         * 获取用户列表
         *
         * @return
         * @throws Exception
         */
        @Transactional(readOnly = true)
        public List<UserDTO> getUserList() throws Exception {
            List<UserDTO> userList = jdbcTemplate.query("select id,name,email from users", new UserRowMapper());
            return userList;
        }
    
    //    /**
    //     * 根据用户id获取用户
    //     *
    //     * @param id
    //     * @return
    //     * @throws Exception
    //     */
    //    @Transactional(readOnly = true)
    //    public UserDTO getUserById(Integer id) throws Exception {
    //        //queryForObject:找不到会报异常  query:找不到则Null
    //        //UserDTO user=jdbcTemplate.queryForObject("select id,name,email from users where id=?",new Object[]{id},new UserRowMapper());
    //        List<UserDTO> userList = jdbcTemplate.query("select id,name,email from users where id=?", new Object[]{id}, new UserRowMapper());
    //        UserDTO user = null;
    //        if (!userList.isEmpty()) {
    //            user = userList.get(0);
    //        }
    //        System.out.println(user);
    //        return user;
    //    }
    //
    //    /**
    //     * 插入用户数据
    //     *
    //     * @param user
    //     * @return
    //     * @throws Exception
    //     */
    //    public int saveUser(final UserDTO user) throws Exception {
    //        int resRow = jdbcTemplate.update("INSERT INTO users(id,name,email) VALUES(NULL,?,?)", new Object[]{
    //                user.getName(), user.getEmail()
    //        });
    //        System.out.println("操作结果记录数：  " + resRow);
    //        return resRow;
    //    }
    //
    //    /**
    //     * 插入用户数据-防止sql注入
    //     *
    //     * @param user
    //     * @return
    //     * @throws Exception
    //     */
    //    public int saveUserWithSafe(final UserDTO user) throws Exception {
    //        int resRow = jdbcTemplate.update("INSERT INTO users(id,name,email) VALUES(NULL,?,?)", new PreparedStatementSetter() {
    //            @Override
    //            public void setValues(PreparedStatement ps) throws SQLException {
    //                ps.setString(1, user.getName());
    //                ps.setString(2, user.getEmail());
    //            }
    //        });
    //        System.out.println("操作结果记录数：  " + resRow);
    //        return resRow;
    //    }
    //
    //    /**
    //     * 插入用户数据-防止sql注入-可以返回该条记录的主键（注意需要指定主键）
    //     *
    //     * @param user
    //     * @return
    //     * @throws Exception
    //     */
    //    @Transactional(rollbackFor = UserException.class)
    //    public int saveUserWithKey(final UserDTO user) throws Exception {
    //        String sql = "INSERT INTO users(id,name,email) VALUES(NULL,?,?)";
    //        KeyHolder keyHolder = new GeneratedKeyHolder();
    //        int resRow = jdbcTemplate.update(new PreparedStatementCreator() {
    //            @Override
    //            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
    //                PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id"}); //指定 id 为主键
    //                ps.setString(1, user.getName());
    //                ps.setString(2, user.getEmail());
    //                return ps;
    //            }
    //        }, keyHolder);
    //        System.out.println("操作结果记录数：  " + resRow + " 主键: " + keyHolder.getKey());
    //        return Integer.parseInt(keyHolder.getKey().toString());
    //    }
    //
    //    /**
    //     * 更新用户信息
    //     *
    //     * @param user
    //     * @return
    //     */
    //    public int updateUser(final UserDTO user) throws Exception {
    //        String sql = "update users set name=?,email=? where id=?";
    //        int resRow = jdbcTemplate.update(sql, new PreparedStatementSetter() {
    //            @Override
    //            public void setValues(PreparedStatement preparedStatement) throws SQLException {
    //                preparedStatement.setString(1, user.getName());
    //                preparedStatement.setString(2, user.getEmail());
    //                preparedStatement.setInt(3, user.getId());
    //            }
    //        });
    //        System.out.println("操作结果记录数：  " + resRow);
    //        return resRow;
    //    }
    //
    //    /**
    //     * 删除用户
    //     *
    //     * @param user
    //     * @return
    //     * @throws Exception
    //     */
    //    public int deleteUser(final UserDTO user) throws Exception {
    //        int resRow = jdbcTemplate.update("DELETE FROM users WHERE id=?", new PreparedStatementSetter() {
    //            @Override
    //            public void setValues(PreparedStatement ps) throws SQLException {
    //                ps.setInt(1, user.getId());
    //            }
    //        });
    //        System.out.println("操作结果记录数：  " + resRow);
    //        return resRow;
    //    }
    //
    //    /**
    //     * 根据用户名查找用户-用于判断用户是否存在
    //     *
    //     * @param user
    //     * @return
    //     * @throws Exception
    //     */
    //    public UserDTO getUserByUserName(final UserDTO user) throws Exception {
    //        String sql = "select id,name,email from users where name=?";
    //        List<UserDTO> queryList = jdbcTemplate.query(sql, new UserRowMapper(), new Object[]{user.getName()});
    //        if (queryList != null && queryList.size() > 0) {
    //            return queryList.get(0);
    //        } else {
    //            return null;
    //        }
    //    }
    //
    //    /**
    //     * 获取记录数
    //     *
    //     * @return
    //     * @throws Exception
    //     */
    //    public Integer getCount() throws Exception {
    //        String sql = "select count(id) from users";
    //        //jdbcTemplate.getMaxRows();
    //        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
    //        System.out.println("操作结果记录数：  " + total);
    //        return total;
    //    }
    
        //其他的像模糊查询之类的可以自己尝试查查 jdbcTemplate 的使用文档
    
    
    }

## 5.编写controller ##

import org.springframework.beans.factory.annotation.Autowired;
    //import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    /**
     * mysql 链接
     */
    @RestController
    @RequestMapping("/user")
    public class MysqlController {
    
        @Autowired
        private UserRepository userRepository;
    
        /**
         * 用户列表
         */
        @RequestMapping("/list")
        public List<UserDTO> listUser() {
            List<UserDTO> userList=null;
            try {
                userList=userRepository.getUserList();
            }catch (Exception e){
                System.out.println("异常信息:  "+e.getMessage());
            }
            return userList;
        }
    
    }

## 6.UserDTO 编写 ##

import java.io.Serializable;
    
    /**
     * Created by YUANYIRUI839 on 2017-09-07.
     */
    public class UserDTO implements Serializable {
        Integer id;
        String name;
        String email;
    
        public UserDTO() {
    
        }
    
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public UserDTO(Integer id, String name, String email) {
            this.id = id;
            this.name = name;
            this.email = email;
        }
    }

7.数据库脚本（改天补上）

没有特殊说明，请按本地springboot的最原始的启动方式启动即可