Java下MySQL易用处理工具(支持XML/JavaBean)

淩亂°似流年 2021-11-29 13:18 116阅读 0赞

本文作者：合肥工业大学 电子商务研究所 钱洋 email：1563178220@qq.com 。  
**内容可能有不到之处，欢迎交流。**

**未经本人允许禁止转载。**

### 文章目录 ###

*  项目代码地址
 *  项目简介
 *  依赖jar包下载
 *  项目包含的内容
 *  使用案例
 *   *  执行SQL语句（创建数据表）
     *  查询一列数据
     *  更新数据表
     *  查询多列
     *  向数据表插入集合数据
     *  XML配置多节点数据库和表

# 项目代码地址 #

[https://github.com/soberqian/MySQLUtils][https_github.com_soberqian_MySQLUtils]

# 项目简介 #

本项目是基于package DbUtils编写的，DbUtils的地址为：  
[https://commons.apache.org/proper/commons-dbutils/][https_commons.apache.org_proper_commons-dbutils]

**该项目提供了简单易用的数据库操作方法，其支持数据库的查询，更新和插入操作。支持XML配置多节点数据库，支持JavaBean操作Java对象。**

# 依赖jar包下载 #

工程依赖的jar有：

<dependency>
    			<groupId>commons-dbutils</groupId>
    			<artifactId>commons-dbutils</artifactId>
    			<version>1.7</version>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-dbcp2</artifactId>
    			<version>2.5.0</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>5.1.32</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>dom4j</groupId>
    			<artifactId>dom4j</artifactId>
    			<version>1.6.1</version>
    		</dependency>
    				
    		<dependency>
    			<groupId>jaxen</groupId>
    			<artifactId>jaxen</artifactId>
    			<version>1.1.6</version>
    		</dependency>

其中，dom4j和jaxen用于操作XML文件。

# 项目包含的内容 #

![在这里插入图片描述][20190722074157773.png_pic_center]  
其中，MYSQLControl类提供了数据库配置，数据库的各种方法。db.xml为配置数据库XML文件。model包下面存放的是JavaBean，test包存放的使用案例。

如下，为MYSQLControl类的内容：

package com.db;
    
    import java.io.File;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.sql.SQLException;
    import java.util.List;
    import javax.sql.DataSource;
    import org.apache.commons.dbcp2.BasicDataSource;
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.apache.commons.dbutils.handlers.ColumnListHandler;
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.dom4j.io.SAXReader;
    import org.dom4j.Document;
    import org.dom4j.DocumentException;
    import org.dom4j.Element;
    
    /** * We develop this tool based on the java package DbUtils. * https://commons.apache.org/proper/commons-dbutils/ * * * It provides an easy-to-use interface for users to operating mysql database. * This tool supports XML database configuration and Java Bean. * * Function: query, update and insert data * * @author: Yang Qian * emiail:qy20115549@126.com */
    public class MYSQLControl { 
    	static final Log logger = LogFactory.getLog(MYSQLControl.class);
    	public DataSource ds;
    	public QueryRunner qr;
    
    	/** * the constructor * * @param connectURI * @param databaseName * @param userName * @param password * */
    	public MYSQLControl(String connectURI, String databaseName, String userName, String password){ 
    		ds = getDataSource("jdbc:mysql://" + connectURI +"/" + databaseName, userName, password);
    		qr = new QueryRunner(ds);
    	}
    
    	/** * the constructor for xml * * @param connectURI * @param databaseName * @param userName * @param password * */
    	public MYSQLControl(String node){ 
    		SAXReader reader=new SAXReader();
    		Document doc = null;
    		try { 
    			doc = reader.read(new File("db.xml"));
    		} catch (DocumentException e) { 
    			e.printStackTrace();
    		}
    		Element urlEle = (Element) doc.selectObject("/config/connectionInfo/" + node + "/url");
    		Element usenameEle  = (Element)doc.selectObject("/config/connectionInfo/" + node + "/username");
    		Element passwordEle  = (Element) doc.selectObject("/config/connectionInfo/" + node + "/password");
    		String dbURL = urlEle.getStringValue(); 
    		String userName = usenameEle.getStringValue();  
    		String password = passwordEle.getStringValue();  
    		ds = getDataSource(dbURL, userName, password);
    		qr = new QueryRunner(ds);
    	}
    
    	public static DataSource getDataSource(String connectURI,String userName, String password){ 
    
    		BasicDataSource ds = new BasicDataSource(); 
    		ds.setDriverClassName("com.mysql.jdbc.Driver");
    		ds.setUsername(userName);              
    		ds.setPassword(password);             
    		ds.setUrl(connectURI);
    		return ds;
    	}
    	/** * update the table based on the sql * * @param sql * */
    	public void executeUpdate(String sql){ 
    		try { 
    			qr.update(sql);
    		} catch (SQLException e) { 
    			logger.error(e);
    		}
    	}
    	/** * get the result based on sql and JavaBean * * @param sql * @param type * */
    	public  <T> List<T> getListInfoBySQL (String sql, Class<T> type ){ 
    		List<T> list = null;
    		try { 
    			list = qr.query(sql,new BeanListHandler<T>(type));
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    		return list;
    	}
    	/** * get the one column result based on sql and id * * @param sql * @param id * */
    	@SuppressWarnings({  "unchecked", "rawtypes" })
    	public List<Object> getListOneBySQL (String sql,String id){ 
    		List<Object> list = null;
    
    		try { 
    			list = (List<Object>) qr.query(sql, new ColumnListHandler(id));
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    		return list;
    	}
    	/** * insert list data to database * * @param sql * @param id * @throws SecurityException * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalArgumentException * @throws IllegalAccessException * */
    	public  <T> void insertListData(List<T> listData, String tableName) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { 
    		Object[][] params = 
    				new Object[listData.size()][listData.get(0).getClass().getDeclaredFields().length];
    		for ( int i = 0; i < listData.size(); i++ ){ 
    			T t = listData.get(i);
    			java.lang.reflect.Field[] fields = t.getClass().getDeclaredFields();
    			for (int j = 0; j < t.getClass().getDeclaredFields().length; j++) { 
    				Method m = t.getClass().getMethod("get" + fields[j].getName().substring(0, 1).toUpperCase() + fields[j].getName().substring(1));
    				params[i][j] = (String) m.invoke(t);
    			}
    		}
    		System.out.println(params.length);
    		String value = "";
    		for (int i = 0; i < listData.get(0).getClass().getDeclaredFields().length; i++) { 
    			value += "?,";
    		}
    		try { 
    			qr.batch("INSERT INTO " + tableName + " VALUES ("  + value.substring(0, value.length() - 1) + ")", params);
    			System.out.println("insert finish!");
    		} catch (SQLException e) { 			
    		}	
    	}
    }

# 使用案例 #

本人的数据库相关配置如下：

![在这里插入图片描述][20190722074932501.png_pic_center]  
如下为数据表carsales的内容：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center]

## 执行SQL语句（创建数据表） ##

如下程序为创新数据表的使用方式：

package com.test;
    
    import com.db.MYSQLControl;
    
    public class CreateTable { 
    
    	public static void main(String[] args) { 
    		MYSQLControl control = new MYSQLControl("127.0.0.1:3306", "crawler", "root", "112233");
    		String sql = "CREATE TABLE newcarsales " +
                    "(month varchar(50), " +
                    "sales varchar(255), " + 
                    " PRIMARY KEY ( month ))"; 
    		control.executeUpdate(sql);
    	}
    }

执行该程序，可以看到成功创建了newcarsales表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 1]

## 查询一列数据 ##

package com.test;
    
    import java.util.List;
    
    import com.db.MYSQLControl;
    
    public class QueryOneColumn { 
    
    	public static void main(String[] args)  { 
    		MYSQLControl control = new MYSQLControl("127.0.0.1:3306", "crawler", "root", "112233");
    		List<Object> list = control.getListOneBySQL("select month from carsales", "month");
    		for (int i = 0; i < list.size(); i++) { 
    			System.out.println(list.get(i).toString());
    		}
    	}
    }

执行程序，可以输出如下结果：  
![在这里插入图片描述][20190722075551877.png_pic_center]

## 更新数据表 ##

执行该程序，可以看到更新的结果。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 2]

## 查询多列 ##

如果要查询多列，如month和sales，需要先创建JavaBean，如下的CarSaleModel类：

package com.model;
    
    public class CarSaleModel { 
    	private String month;
    	private String sales;
    	public String getMonth() { 
    		return month;
    	}
    	public void setMonth(String month) { 
    		this.month = month;
    	}
    	public String getSales() { 
    		return sales;
    	}
    	public void setSales(String sales) { 
    		this.sales = sales;
    	}
    }

如下为查询多列的程序：

package com.test;
    
    import java.util.List;
    
    import com.db.MYSQLControl;
    import com.model.CarSaleModel;
    
    public class QueryMultColumn { 
    
    	public static void main(String[] args) { 
    		MYSQLControl control = new MYSQLControl("127.0.0.1:3306", "crawler", "root", "112233");
    		List<CarSaleModel> listData = control.getListInfoBySQL("select month, sales from carsales", CarSaleModel.class);
    		for (CarSaleModel model : listData) { 
    			System.out.println(model.getMonth() + "\t" + model.getSales());
    		}
    	}
    }

执行程序，会在控制台输出如下结果：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 3]

## 向数据表插入集合数据 ##

package com.test;
    
    import java.lang.reflect.InvocationTargetException;
    import java.util.ArrayList;
    import java.util.List;
    import com.db.MYSQLControl;
    import com.model.CarSaleModel;
    
    public class InsertListData { 
    
    	public static void main(String[] args) { 
    		MYSQLControl control = new MYSQLControl("127.0.0.1:3306", "crawler", "root", "112233");
    		List<CarSaleModel> saleList = new ArrayList<CarSaleModel>();
    		CarSaleModel model1 = new CarSaleModel();
    		model1.setMonth("2007-01-01");
    		model1.setSales("14834");
    		CarSaleModel model2 = new CarSaleModel();
    		model2.setMonth("2007-02-01");
    		model2.setSales("9687");
    		//add data
    		saleList.add(model1);
    		saleList.add(model2);
    		try { 
    			control.insertListData(saleList, "newcarsales");
    		} catch (NoSuchMethodException e) { 
    			e.printStackTrace();
    		} catch (SecurityException e) { 
    			e.printStackTrace();
    		} catch (IllegalAccessException e) { 
    			e.printStackTrace();
    		} catch (IllegalArgumentException e) { 
    			e.printStackTrace();
    		} catch (InvocationTargetException e) { 
    			e.printStackTrace();
    		}
    	}
    
    }

执行该程序，可以看到数据表中成功插入了两条数据：  
![在这里插入图片描述][20190722081045401.png_pic_center]

## XML配置多节点数据库和表 ##

如下，通过db.xml配置了两个节点：

<?xml version="1.0" encoding="UTF-8"?>
    <config>
    	<connectionInfo>
    		<node1>
    			<nodeName>node1</nodeName>
    			<url>jdbc:mysql://127.0.0.1:3306/crawler</url>
    			<username>root</username>
    			<password>112233</password>
    		</node1>
    
    		<node2>
    			<nodeName>node2</nodeName>
    			<url>jdbc:mysql://114.213.252.26:3306/crawler</url>
    			<username>root</username>
    			<password>112233</password>
    		</node2>
    
    	</connectionInfo>
    </config>

如下，为使用方式：

package com.test;
    
    import java.util.List;
    
    import com.db.MYSQLControl;
    import com.model.CarSaleModel;
    
    public class XMLMySQLDo { 
    
    	public static void main(String[] args) { 
    		MYSQLControl control = new MYSQLControl("node1");
    		List<CarSaleModel> listData = control.getListInfoBySQL("select month, sales from carsales", CarSaleModel.class);
    		for (CarSaleModel model : listData) { 
    			System.out.println(model.getMonth() + "\t" + model.getSales());
    		}
    	}
    }

执行程序输出结果为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 4]

[https_github.com_soberqian_MySQLUtils]: https://github.com/soberqian/MySQLUtils
[https_commons.apache.org_proper_commons-dbutils]: https://commons.apache.org/proper/commons-dbutils/
[20190722074157773.png_pic_center]: /images/20211129/b947eb075697404fa421c8daffa41791.png
[20190722074932501.png_pic_center]: /images/20211129/14b517d7e04c44c099b66286dbb10a0c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20211129/1c6e8c13a0e74aa386b756a9f850aef3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20211129/e88bea7bfc36433493451c9aab47905c.png
[20190722075551877.png_pic_center]: /images/20211129/459ee372549644c4a8e323ad55676e57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20211129/e1930fa01f69441daa8fd1ce1bf6fc6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20211129/73e3f59cc57d4cc4aff64f89af50ec7e.png
[20190722081045401.png_pic_center]: /images/20211129/6584ecfbe09545debdc9c47577a0d3bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9xaWFueWFuZy1oZnV0LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20211129/d4fefeef07444318ab64e94997c88ef5.png