java操作MySQL封装工具类JDBCUtils

最新推荐文章于 2024-05-07 17:09:42 发布

longHARDEN

最新推荐文章于 2024-05-07 17:09:42 发布

阅读量981

点赞数

分类专栏： Java基础 SQL 文章标签： mysql java

本文链接：https://blog.csdn.net/qq_42002500/article/details/115312417

版权

Java基础同时被 2 个专栏收录

30 篇文章 4 订阅

订阅专栏

SQL

6 篇文章 0 订阅

订阅专栏

工具类的封装：以数据库的操作为例

需导入jar包：mysql-connection-mysql，druid，JDBCTemplate，redis（可选）

如果是maven项目，导入jar包如下：

pom.xml

<!--连接MySQL数据库-->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>

    <!--C3P0数据库连接池-->
    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.2</version>
    </dependency>

    <!--c3p0连接Java包-->
    <!-- https://mvnrepository.com/artifact/com.mchange/mchange-commons-java -->
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>mchange-commons-java</artifactId>
        <version>0.2.15</version>
    </dependency>

    <!--阿里Druid数据库连接池工具包-->
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.3</version>
    </dependency>

    <!--Spring JDBC工具包-->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.2.11.RELEASE</version>
    </dependency>

    <!--缺乏这个包，Spring JDBC报错-->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-nop</artifactId>
        <version>1.7.2</version>
    </dependency>

    <!--jedis-->
    <!-- https://mvnrepository.com/artifact/redis.clients/jedis -->
    <dependency>
        <groupId>redis.clients</groupId>
        <artifactId>jedis</artifactId>
        <version>3.3.0</version>
    </dependency>

druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day23?useSSL=false&serverTimezone=GMT%2B8
username=root
password=root
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=20
# 最大等待时间
maxWait=3000

JDBCUtils.java

/**
 * JDBC工具类 使用Druid连接池
 */
public class JDBCUtils {

    private static DataSource ds;

    static {
        try {
            //1、加载配置文件
            Properties pro = new Properties();
            //使用ClassLoader加载配置文件，获取字节输入流
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);

            //2、初始化连接池对象
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException exception) {
            exception.printStackTrace();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    /**
     * 获取连接池对象
     *
     * @return
     */
    public static DataSource getDataSource() {
        return ds;
    }

    /**
     * 获取连接池connection对象
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

jedis.properties

host=127.0.0.1
port=6379
maxTotal=50
maxIdle=10

JedisPoolUtils.java

package com.example.util;

/**
 * JedisPool工具类
 * 加载配置文件，配置连接池参数
 * 提供获取连接的方法
 */
public class JedisPoolUtils {

    private static JedisPool jedisPool;

    static {
        //读取配置文件
        InputStream is = JedisPoolUtils.class.getClassLoader().getResourceAsStream("jedis.properties");
        System.out.println("is = " + is);
        //创建Properties对象
        Properties pro = new Properties();
        //关联文件
        try {
            pro.load(is);
        } catch (IOException exception) {
            exception.printStackTrace();
        }
        //获取数据，设置到JedisPoolConfig中
        JedisPoolConfig config = new JedisPoolConfig();
        config.setMaxTotal(Integer.parseInt(pro.getProperty("maxTotal")));
        config.setMaxIdle(Integer.parseInt(pro.getProperty("maxIdle")));
        //初始化JedisPool
        jedisPool = new JedisPool(config, pro.getProperty("host"), Integer.parseInt(pro.getProperty("port")));
    }

    /**
     * 获取连接方法
     */
    public static Jedis getJedis() {
        return jedisPool.getResource();
    }
}

dao层

UserDao.java

public interface UserDao {

    public List<User> findAll();

    public User findUserByUsernameAndPassword(String username, String password);

    public User findUserById(int id);

    /**
     * 查询总记录数
     *
     * @param condition
     * @return
     */
    public int findTotalCount(Map<String, String[]> condition);

    /**
     * 分页查询每页记录
     *
     * @param start
     * @param rows
     * @param condition
     * @return
     */
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition);

    public void add(User user);
    
    public void update(User user);

    public void delete(int id);

}

UserDaoImpl.java

使用JdbcTemplate实现操作数据库，JdbcTemplate使得程序员更加关注sql语句的书写，而不必考虑复杂的数据库连接

package com.example.dao.impl;

import com.example.dao.UserDao;
import com.example.domain.User;
import com.example.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;

public class UserDaoImpl implements UserDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<User> findAll() {
        String sql = "select * from userinfo";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<>(User.class));
        return users;
    }

    @Override
    public User findUserByUsernameAndPassword(String username, String password) {
        String sql = "select * from userinfo where username=? and password=?";
        User user = template.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), username, password);
        return user;
    }

    @Override
    public User findUserById(int id) {
        String sql = "select * from userinfo where uid=?";
        User user = template.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
        return user;
    }

    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        //1、定义模板初始化sql
        String sql = "select count(uid) from userinfo where 1=1";
        //定义参数集合
        List<Object> params = new ArrayList<>();
        //调用retSql方法，添加参数，修改sql语句
        sql = retSql(sql, params, condition);
        return template.queryForObject(sql, Integer.class, params.toArray());
    }

    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        //1、定义模板初始化sql
        String sql = "select * from userinfo where 1=1";
        //定义参数集合
        List<Object> params = new ArrayList<>();
        //调用retSql方法，添加参数，修改sql语句
        sql = retSql(sql, params, condition);
        sql += "limit ?,? ";
        params.add(start);
        params.add(rows);

        return template.query(sql, new BeanPropertyRowMapper<User>(User.class), params.toArray());
    }

    private String retSql(String sql, List<Object> params, Map<String, String[]> condition) {
        //创建builder缓冲区，保存频繁修改的sql语句
        StringBuilder builder = new StringBuilder(sql);
        //遍历condition，修改sql语句,添加params
        Set<String> keySet = condition.keySet();
        for (String key : keySet) {
            //排除分页条件参数
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if (value != null && !"".equals(value)) {
                //有值
                builder.append(" and " + key + " like ? ");//模糊查询
                params.add("%" + value + "%");  //条件值
            }
        }

        return builder.toString();//sql赋值并返回
    }

    @Override
    public void add(User user) {
        String sql = "INSERT INTO userinfo (username,password,name,birthday,sex,telephone,email,status,code) VALUES (?,?,?,?,?,?,?,?,?)";
        try {
            template.update(sql, user.getUsername(), user.getPassword(), user.getName(), user.getBirthday(),
                    user.getSex(), user.getTelephone(), user.getEmail(), user.getStatus(), user.getCode());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(User user) {
        String sql = "update userinfo set username=?,password=?,name=?,birthday=?,sex=?,telephone=?,email=?,status=?,code=?";
        try {
            template.update(sql, user.getUsername(), user.getPassword(), user.getName(), user.getBirthday(),
                    user.getSex(), user.getTelephone(), user.getEmail(), user.getStatus(), user.getCode());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {
        String sql = "delete from userinfo where uid = ?";
        try {
            template.update(sql, id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }
}

service层

UserService接口

package com.example.service;

/**
 * 用户管理的service层接口
 */
public interface UserService {

    /**
     * 查询所有用户信息
     * @return
     */
    public List<User> findAllUser();

    public User findUserById(String id);

    public User login(User user);

    public void addUser(User user);

    public void updateUser(User user);

    public void deleteUser(String id);

    /**
     * 批量删除用户
     * @param ids
     */
    void delSelectedUser(String[] ids);

    /**
     * 分页条件查询
     * @param currentPage
     * @param rows
     * @param condition
     * @return
     */
    PageBean<User> findUserByPage(String currentPage, String rows, Map<String, String[]> condition);
    
}

UserServiceImpl.java

package com.example.service.impl;

public class UserServiceImpl implements UserService {
    private UserDao dao=new UserDaoImpl();

    @Override
    public List<User> findAllUser() {
        //调用dao
        return dao.findAll();
    }

    @Override
    public User findUserById(String id) {
        return dao.findUserById(Integer.parseInt(id));
    }

    @Override
    public User login(User user) {
        return dao.findUserByUsernameAndPassword(user.getUsername(), user.getPassword());
    }

    @Override
    public void addUser(User user) {
        dao.add(user);
    }

    @Override
    public void updateUser(User user) {
        dao.update(user);
    }

    @Override
    public void deleteUser(String id) {
        dao.delete(Integer.parseInt(id));
    }

    @Override
    public void delSelectedUser(String[] ids) {
        if (ids != null && ids.length > 0) {
            //遍历数组
            for (String id : ids) {
                dao.delete(Integer.parseInt(id));
            }
        }
    }

    @Override
    public PageBean<User> findUserByPage(String _currentPage, String _rows, Map<String, String[]> condition) {
        int currentPage = Integer.parseInt(_currentPage);
        int rows = Integer.parseInt(_rows);
        //1、创建空的PageBean对象
        PageBean<User> pageBean = new PageBean<>();
        //2、设置参数
        pageBean.setCurrentPage(currentPage);
        pageBean.setRows(rows);
        //3、调用dao查询总记录数
        int totalCount = dao.findTotalCount(condition);
        pageBean.setTotalCount(totalCount);
        //4、调用dao查询list集合
        //计算开始的记录索引
        int start = (currentPage - 1) * rows;
        List<User> list = dao.findByPage(start, rows,condition);
        pageBean.setList(list);
        //5、计算总页码
        int totalPage = totalCount % rows == 0 ? totalCount / rows : totalCount / rows + 1;
        pageBean.setTotalPage(totalPage);
        return pageBean;
    }
}

web层，主要servlet层。作为controller控制器，根据具体业务逻辑分析

获取请求参数
调用service层方法，获取数据
response返回相应数据

longHARDEN

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
java操作MySQL封装工具类JDBCUtils

工具类的封装：以数据库的操作为例需导入jar包：mysql-connection-mysql，druid，JDBCTemplate，redis（可选）如果是maven项目，导入jar包如下：pom.xml  <dependency> <groupId&gt
复制链接

扫一扫

专栏目录