11月24, 2020

commons-dbutils数据库操作工具包

通过读取properties获取链接数据库信息,利用apache开源数据库操作工具包DBUtils进行数据库CRUD操作。

properties文件:

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=11111

通过dbutils自定义获取数据库连接对象:

package learn.javase.jdbc;

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

public class JdbcUtils{
    private static Connection conn;
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    static {
        try {
            getReadConfig();
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, user, password);
        }catch(Exception e) {
            new RuntimeException("链接数据库失败!");
        }
    }

    private static void getReadConfig() throws Exception{
        InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        pro.load(in);
        driverClass =pro.getProperty("driverClass");
        url = pro.getProperty("url");
        user = pro.getProperty("user");
        password = pro.getProperty("password");
    }

    public static Connection getConnection() {
        return conn;
    }

}

使用工具包中的QueryRunner进行数据库的CRUD操作:

package learn.javase.jdbc;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
/**
 * 利用apatche的DBUtils工具包,添加\修改\删除数据
 * @author Jole
 *
 */
public class DbUtilsDemo {

    private static Connection conn = JdbcUtils.getConnection();
    private static QueryRunner qr = new QueryRunner();

    public static void main(String[] args) throws Exception{
//        insert();
//        update();
        delte();
    }

    public static void insert() throws SQLException{
        String sql = "insert into my_user(u_id,u_name,u_age) values(11111,'inster',10)";
        Object[] param = {};
        int n = qr.update(conn, sql, param);
        System.out.println(n);
        DbUtils.close(conn);
    }

    public static void update() throws SQLException{
        String sql ="update my_user set u_name=? ,u_age=? where u_id=?";
        Object[] param = {"ceshi",1000,11111};
        int n = qr.update(conn, sql, param);
        DbUtils.close(conn);
        System.out.println(n);
    }

    public static void delte() throws SQLException{
        String sql = "delete from my_user where u_id=?";
        int n = qr.update(conn, sql, 11111);
        DbUtils.close(conn);
        System.out.println(n);
    }

}

封装QueryRunner返回结果成对象: 要封装成的对象User

package learn.javase.jdbc;

public class User {

    private String u_id;
    private String u_name;
    private String u_age;
    public String getU_id() {
        return u_id;
    }
    public void setU_id(String u_id) {
        this.u_id = u_id;
    }
    public String getU_name() {
        return u_name;
    }
    public void setU_name(String u_name) {
        this.u_name = u_name;
    }
    public String getU_age() {
        return u_age;
    }
    public void setU_age(String u_age) {
        this.u_age = u_age;
    }
    public User() {
        super();
        // TODO Auto-generated constructor stub
    }
    public User(String u_id, String u_name, String u_age) {
        super();
        this.u_id = u_id;
        this.u_name = u_name;
        this.u_age = u_age;
    }
    @Override
    public String toString() {
        return "User [u_id=" + u_id + ", u_name=" + u_name + ", u_age=" + u_age + "]";
    }


}

根据返回结果,通过实现ResultHandler接口的实现类,封装对象:

package learn.javase.jdbc;

import java.sql.Connection;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
/**
 * 利用开源apache的commons-dbutils-1.7.jar的QueryRunner封装数据
 * @author Jole
 *
 */
public class QueryRunnerDemo01 {

    public static final Connection conn = JdbcUtils.getConnection();
    public static final QueryRunner qr = new QueryRunner();

    public static void main(String[] args) throws Exception {
        // TODO Auto-generated method stub
//        getInfo();
//        getInfo2();
//        getInfo3();
//        getInfo4();
//        getInfo5();
//        getInfo6();
//        getInfo7();
        getInfo8();
    }

    //ArrayHandler 第一行
    public static void getInfo() throws Exception{
        String sql ="select * from my_user";
        Object[] rs = qr.query(conn, sql, new ArrayHandler());
        for(Object obj : rs) {
            System.out.print(obj + "\t");    
        }

    }

    //ArrayListHandler 所有记录
    public static void getInfo2() throws Exception{
        String sql ="select * from my_user";
        List<Object[]> rs = qr.query(conn, sql, new ArrayListHandler());
        for(Object[] objs : rs) {
            for(Object obj : objs) {
                System.out.print(obj + "\t");    
            }    
            System.out.println();
        }
    }

    //javaBean 第一行封装成对象
    public static void getInfo3() throws Exception{
        String sql ="select * from my_user";
        User user = qr.query(conn, sql, new BeanHandler<User>(User.class));
        System.out.println(user);
    }

    //javaBean 所有记录封装成对象
    public static void getInfo4() throws Exception{
        String sql ="select * from my_user";
        List<User> userList = qr.query(conn, sql, new BeanListHandler<User>(User.class));
        for(User user : userList) {
            System.out.println(user);    
        }
    }

    //ColumnListHandler所有记录的某列值
    public static void getInfo5() throws Exception{
        String sql ="select * from my_user";
        List<Object> list = (List<Object>) qr.query(conn, sql, new ColumnListHandler("u_name"));
        for(Object obj : list) {
            System.out.println(obj);    
        }
    }

    //ScalarHandler返回单个值
    public static void getInfo6() throws Exception{
        String sql ="select count(*) from my_user";
        Long count =  qr.query(conn, sql, new ScalarHandler<Long>());
        System.out.println(count);
    }

    //MapHandler将第一行数据封到map中
    public static void getInfo7() throws Exception{
        String sql ="select * from my_user";
        Map<String, Object> map =  qr.query(conn, sql, new MapHandler());

        Set<String> set =map.keySet();
        for(String key : set) {
            System.out.println(key+" "+map.get(key));
        }
    }

    //MapListHandler将所有数据封到list中的map中
    public static void getInfo8() throws Exception{
        String sql ="select * from my_user";
        List<Map<String, Object>> list=  qr.query(conn, sql, new MapListHandler());
        for(Map<String, Object> map : list) {
            for(String key : map.keySet()) {
                System.out.print(key+" "+ map.get(key));
            }
            System.out.println();
        }
    }

}

主要涉及的jar包:commons-dbutils-1.7.jar(基本的CRUD)、mysql-connector-java-5.1.37-bin.jar(数据库连接驱动)

本文链接:https://www.yanxizhu.com/post/commons-dbutils.html

-- EOF --

Comments