使用原生的JDBC, commons-dbutils-1.3.jar封装一个通用的DAO, 可以对数据库进行基本的CRUD

为啥要写这样一个工具呢???, 吃饱了没事干,现在Mybatis,SpringData,JPA不香吗?,(⊙o⊙)…,的确很香,但是每一做项目就使用这些ORM框架难免过于臃肿了一些,所以自己封装了一个通用的DAO,短小精悍~~哈哈哈哈哈...

DAO接口

该接口主要定义基本的CRUD对数据库的操作

package com.coderman.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 访问数据库的DAO接口
 * @Author zhangyukang
 * @Date 2020/4/24 16:37
 * @Version 1.0
 * T :处理的实体类的类型
 **/
public interface DAO<T> {

    /**
     * UPDATE DELETE INSERT
     * @param connection: 数据库连接
     * @param sql: sql语句
     * @param args: 可变参数
     */
    void update(Connection connection,String sql,Object ...args) throws SQLException;

    /**
     * 返回一个T的集合
     * @param connection
     * @param sql
     * @param args
     * @return
     */
    List<T> getForList(Connection connection,String sql,Object ...args) throws SQLException;

    /**
     * 返回一个T的对象
     * @param connection
     * @param sql
     * @param args
     * @return
     */
    T get(Connection connection,String sql,Object ...args) throws SQLException;


    /**
     * 返回一个值: 例如用户的姓名,年龄,总人数...
     * @param <E>
     * @return
     */
    <E> E getForValue(Connection connection,String sql,Object... args) throws SQLException;


    /**
     * 批量处理数据: 例如批量删除,插入..
     * @param connection
     * @param sql
     * @param args
     */
    void batch(Connection connection,String sql,Object[]... args) throws SQLException;

}

JDBCImpl实现类

该类利用反射获取父类的泛型,使用commons-dbutils-1.3.jar的QueryRunner实现基本的操作

package com.coderman.dao;

import com.coderman.utils.ReflectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Author zhangyukang
 * @Date 2020/4/24 16:52
 * @Version 1.0
 **/
public class JDBCImpl<T> implements DAO<T> {

    private QueryRunner queryRunner;

    private Class<T> type;

    public JDBCImpl(){
        queryRunner=new QueryRunner();
        this.type=ReflectionUtils.getSuperGenericType(this.getClass());
    }

    /**
     * 更新,删除,插入
     * @param connection: 数据库连接
     * @param sql: sql语句
     * @param args: 可变参数
     */
    @Override
    public void update(Connection connection, String sql, Object... args) throws SQLException {
         queryRunner.update(connection,sql,args);
    }

    /**
     *  获取对象集合
     * @param connection
     * @param sql
     * @param args
     * @return
     */
    @Override
    public List<T> getForList(Connection connection, String sql, Object... args) throws SQLException {
        return queryRunner.query(connection,sql,new BeanListHandler<>(type),args);
    }

    /**
     * 获取一个对象
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    @Override
    public T get(Connection connection, String sql, Object... args) throws SQLException {
        return queryRunner.query(connection,sql,new BeanHandler<>(type),args);
    }

    /**
     * 获取一个值
     * @param connection
     * @param sql
     * @param args
     * @param <E>
     * @return
     */
    @Override
    public <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException {
        return (E) queryRunner.query(connection,sql,new ScalarHandler(),args);
    }

    /**
     * 批量处理SQL
     * @param connection
     * @param sql
     * @param args
     */
    @Override
    public void batch(Connection connection, String sql, Object[]... args) throws SQLException {
        queryRunner.batch(connection,sql,args);
    }
}

User类

封装了User的基本信息,查询数据库之后,会利用反射,将查询后的数据写入Bean中,必须提供get,set方法

package com.coderman.dao;

/**
 * @Author zhangyukang
 * @Date 2020/4/24 17:07
 * @Version 1.0
 **/

public class User {
    private String username;
    private String password;


    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}'+"\n";
    }
}

UserDAO

这是用来对用户表进行数据的操作的DAO,我们只需继承JDBCImpl即可~

package com.coderman.dao;

/**
 * @Author zhangyukang
 * @Date 2020/4/24 17:07
 * @Version 1.0
 **/
public class UserDAO extends JDBCImpl<User> {

}

JDBCUtils

该类封装了基本的连接的获取,释放...

package com.coderman.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @Author zhangyukang
 * @Date 2020/4/10 16:46
 * @Version 1.0
 **/
public class JDBCUtils {


    private static Properties info;

    private static String url="jdbc:mysql://localhost:3306/jdbc";
    static {
        try {
            InputStream ins= JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            if(ins!=null){
                info=new Properties();
                info.load(ins);
            }
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("读取配置文件错误");
        }
    }

    /**
     * 获取一个数据库连接
     * @return
     */
    public static Connection getConnection(){
        try {
            Connection connection = DriverManager.getConnection(url, info);
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}


测试DAO

OK,写到这里基本就封装完了,接下来测试一下效果

建表

在名称为jdbc的库下建一张user表

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

配置文件

将配置文件放在类路径下即可

url=jdbc:mysql://localhost:3306/jdbc
user=root
password=zhangyukang
className=com.mysql.jdbc.Driver

编写测试类

1. 插入

    @Test
    public void testInsert(){
        String sql="insert into tb_user(username,password) values(?,?)";
        Connection connection = JDBCUtils.getConnection();
        try {
            userDAO.update(connection, sql, "coderman","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

这里为啥是id=5呢??,因为我之前插过几条数据

image.png

2. 查询 (一条记录)

@Test
    public void testGetOne(){
        Connection connection = JDBCUtils.getConnection();
        try {
            User user = userDAO.get(connection, "select * from tb_user where id=?", 5);
            System.out.println(user);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

User{username='coderman', password='123456'}

3. 查询(多条记录)

 @Test
    public void testGetList(){
        Connection connection = JDBCUtils.getConnection();
        try {
            List<User> userList = userDAO.getForList(connection, "select * from tb_user ");
            System.out.println(userList);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
[User{username='coderman', password='123456'}
, User{username='coderman2', password='123456'}
]

4. 更新

 @Test
    public void testUpdate(){
        Connection connection = JDBCUtils.getConnection();
        try {
            userDAO.update(connection, "update  tb_user set username=? where id=? "
                    ,"zhangyukang",5);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

image.png

# jdbc  

评论

公众号:mumuser

企鹅群:932154986

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×