一、项目结构图：
二、项目源代码：
1、Person实体类
=》Person.java
package instance;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Vector;
import db.DBConnManager;
/**
* 数据库导入导出程序_personTable实体类
* @author 23_11
*/
public class Person {
/**
* 属性
*/
// 通讯ID
private int id;
// 姓名
private String personName;
// 联系方式
private String contact;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPersonName() {
return personName;
}
public void setPersonName(String personName) {
this.personName = personName;
}
public String getContact() {
return contact;
}
public void setContact(String contact) {
this.contact = contact;
}
// db类
private DBConnManager dbcm;
// 构造函数
public Person() {
dbcm = new DBConnManager();
}
/**
* 添加<单>
* @param p_包含信息的Person对象；
* @return 受影响的行数
*/
public int add(Person p) {
int num = -1; // 受影响的行数;
String insertsql = null;
if(p.getId() == 0) {//id没有设置值，则取数据库自动增长；
insertsql = "insert into personTable(personName, contact) values('" + p.getPersonName() + "', '" + p.getContact() + "')";
}else {
insertsql = "insert into personTable(id, personName, contact) values(" + p.getId()+ ", '" + p.getPersonName() + "', '" + p.getContact() + "')";
}
System.out.println("Person.add()_insertsql_"+insertsql);
num = dbcm.aduPerform(insertsql);
return num;
}
/**
* 删除<单>
* @param id_要删除记录id；
* @return_影响行数；
*/
public int delete(int id) {
int num = -1;
String delsql = "delete from personTable where id = " + id;
num = dbcm.aduPerform(delsql);
return num;
}
/**
* 更新<单>
* @param id_要更新的记录id；
* @param updatep_更新的对象，含有更新数据；
*/
public void update(int id, Person updatep) {
try {
String updatesql = "select * from personTable where id = " + id;
ResultSet rs = dbcm.query(updatesql, false);
while(rs.next()) {
rs.updateString("personName", updatep.getPersonName());
rs.updateString("contact", updatep.getContact());
rs.updateRow();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 搜索<单>_通过id获取person对象
* @param id
* @return 搜索结果集
*/
public ResultSet getPersonById(int id) {
ResultSet rs = null;
String qsql = "select * from personTable where id =" + id;
rs = dbcm.query(qsql, true);
return rs;
}
/**
* 搜索全部
* @param id
* @return
*/
public ResultSet getPersons() {
ResultSet rs = null;
String qsql = "select * from personTable";
rs = dbcm.query(qsql, true);
return rs;
}
/**
* 测试
* @param args
*/
public static void main(String[] args) {
System.out.println("=>Person_Class; ");
/**
* Person添加
*/
// Person addp = new Person();
// addp.setPersonName("方少云");
// addp.setContact("湖南-长沙");
// addp.add(addp);
// Person addpid = new Person();
// addpid.setId(12315);
// addpid.setPersonName("苟小云");
// addpid.setContact("上海-RVA");
// addpid.add(addpid);
/**
* Person删除
*/
// Person delp = new Person();
// delp.delete(27); //27 吴痴 失踪
/**
* Person更新
*/
// Person updatep = new Person();
// updatep.setPersonName("吴邪");
// updatep.setContact("杭州古董街");
// updatep.update(25, updatep);
/**
* 搜索
*/
Person qp = new Person();
Vector<ResultSet> rsv = new Vector<ResultSet>();
rsv.add(qp.getPersonById(28));
rsv.add(qp.getPersons());
Enumeration<ResultSet> rse = rsv.elements();
ResultSet rs;
while(rse.hasMoreElements()) {
rs = rse.nextElement();
System.out.println("--- --- ---");
try {
while(rs.next()) {
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、配置文件操作类
=》propertiesOperate.java
package utility;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Properties;
/**
* 数据库导入导出程序_配置文件操作类
* @author 23_11
*/
public class propertiesOperate {
/**
* 配置文件所在目录>native2ascii -encoding UTF-8(源文件编码格式) info.properties info_encoding.properties(编译后文件)
*/
//配置文件路径
private static final String PROPERTY_FILE=System.getProperty("user.dir") +"\\properties\\info_encoding.properties";
//配置文件类
Properties props;
/**
* 构造函数
* @param propsurl_*.properties文件路径
*/
public propertiesOperate(String propsurl){
if(propsurl == null || propsurl.equals("")) {
propsurl = PROPERTY_FILE;
}
props = new Properties();
try {
InputStream ip = new BufferedInputStream(new FileInputStream(propsurl));
props.load(ip);
ip.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取key对应的value值
* @param key
* @return 配置值
*/
public String getPropertyValue(String key) {
String val = props.getProperty(key);
return val;
}
/**
*
* 获取value值对应的key值
* @param value_字段值
* @param dbname_数据库名
* @param tablename_表名
* @return 返回key值；
*/
public String getKey(String value, String dbname, String tablename){
String key = null;
String pre = dbname+"."+tablename;
Enumeration<Object> enu = props.keys();
while(enu.hasMoreElements()) {
key = enu.nextElement().toString();
if(getPropertyValue(key).equals(value) && key.indexOf(pre)>-1) {
return key;
}
}
return null;
}
/**
* 获取表字段数组
* @param dbname
* @param tablename
* @return 返回指定表格字段；
*/
public String[] getKeys(String dbname, String tablename) {
String pre = dbname + "." + tablename + "."; // key前缀；
String[] keys = null; // key数组；
String key; //key字符串；
ArrayList<String> arraystmp = new ArrayList<String>();
Enumeration<Object> enu = props.keys();
while(enu.hasMoreElements()) {
key = enu.nextElement().toString();
if(key.indexOf(pre)>-1) {
arraystmp.add(key);
}
}
// 组装数组
int len = arraystmp.toArray().length;
keys = new String[len];
for(int i=0; i<len; i++) {
keys[i] = (String)arraystmp.toArray()[i];
}
return keys;
}
/**
* 获取表中文字段数组
* @param dbname
* @param tablename
* @return 表中文字段数组
*/
public String[] getValues(String dbname, String tablename) {
String keypre = dbname + "." + tablename + "."; //key前缀；
String[] values = null; //返回指定的值数组；
String key;
String value;
ArrayList<String> valuesstmp = new ArrayList<String>();
Enumeration<Object> enu = props.keys();
while(enu.hasMoreElements()) {
key = enu.nextElement().toString();
if(key.indexOf(keypre) > -1) {
value = props.getProperty(key);
valuesstmp.add(value);
}
}
int len = valuesstmp.toArray().length;
values = new String[len];
for(int i=0; i<len; i++) {
values[i] = valuesstmp.toArray()[i].toString();
}
return values;
}
/**
* 测试
* @param args
*/
public static void main(String[] args) {
propertiesOperate po = new propertiesOperate(PROPERTY_FILE);
// 获取值：
String tablename = po.getPropertyValue("addressBook.personTable");
System.out.println("=>addressBook.personTable_"+tablename);
//获取key：
String key = po.getKey("通讯ID", "addressBook", "personTable");
System.out.println("=>通讯ID_" + key);
//获取表字段：
String[] keys = po.getKeys("addressBook", "personTable");
for(int i=0; i<keys.length; i++) {
System.out.print(keys[i] + " ");
}
//获取表格中文字段：
String[] values = po.getValues("addressBook", "personTable");
for(int i=0; i<values.length; i++) {
System.out.print(values[i] + " ");
}
}
}
3、连接池
=>DBConnPool.java
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;
/**
* 数据库导入导出程序_连接池类
* @author 23_11
*/
public class DBConnPool {
private int inUse = 0; //实际使用中的链接数；
private Vector<Connection> connections = new Vector<Connection>(); //空闲链接数组；
private String poolname; //连接池名称；
private String dburl; //数据库路径；
private String drivername; //驱动程序名称，如:com.hxtt.sql.access.AccessDriver(jdbc);
private String username; //数据库账号；
private String psw; //数据库密码；
private int maxconn; //最大链接数；
/**
* 构造函数
* @param poolname
* @param dburl
* @param drivername
* @param username
* @param psw
* @param maxconn
*/
public DBConnPool(
String poolname,
String dburl,
String drivername,
String username,
String psw,
int maxconn){
this.poolname = poolname;
this.dburl = dburl;
this.drivername = drivername;
this.username = username;
this.psw = psw;
this.maxconn = maxconn;
}
/**
* 创建新链接
* @return 链接对象；
*/
private Connection newConnection() {
Connection con = null;
try{
Class.forName(drivername).newInstance();
con = DriverManager.getConnection(dburl, username, psw);
}catch(Exception e) {
e.printStackTrace();
}
return con;
}
/**
* 获取连接对象<同步>
* @return 连接对象
*/
public synchronized Connection getConnection() {
Connection con = null;
// 如果连接池有空闲链接对象，就从中取：
if(connections.size() > 0) {
con = (Connection)connections.elementAt(0); //获取连接列表中第一个链接；
connections.removeElementAt(0);
// 如果此链接已经关闭，则继续获取：
try {
if(con.isClosed()) {
con = getConnection();
}
} catch (SQLException e) {
e.printStackTrace();
}
}else if(inUse <= maxconn) {//实际使用连接数不大于最大值；
con = newConnection();
}
if(con != null) {
inUse++; // 连接数增一；
}
return con;
}
/**
* 释放链接对象<同步>
* @param con_要释放的链接对象；
*/
public synchronized void releaseConnection(Connection con) {
connections.addElement(con); //将指定链接加入到向量末尾；
inUse--;
}
/**
* 关闭所有链接对象
*/
public synchronized void closeConns() {
Enumeration<Connection> allConnections = connections.elements();
Connection con;
while(allConnections.hasMoreElements()) {
con = (Connection)allConnections.nextElement();
try{
con.close();
}catch(Exception e) {
e.printStackTrace();
}
}
connections.removeAllElements();
}
/**
* 测试
* @param args
*/
public static void main(String[] args) {
System.out.println("=>数据库导入导出程序_连接池类;");
//采用jdbc连接access数据库
String dburl = "jdbc:Access:///" + System.getProperty("user.dir")+"\\db\\addressBook.mdb";
String qsql = "select * from personTable";
System.out.println("=>查询语句："+qsql);
DBConnPool dbcp = new DBConnPool("access", dburl, "com.hxtt.sql.access.AccessDriver", "", "", 300);;
Connection con = dbcp.getConnection();
try{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(qsql);
while(rs.next()) {
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println(rs.getString(3));
}
rs.close();
stmt.close();
con.close();
}catch(Exception e) {
e.printStackTrace();
}finally{
dbcp.releaseConnection(con); //释放；
dbcp.closeConns(); //销毁；
}
}
}
4、连接池管理类
=> DBConnManager.java
package db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;
/**
* 数据库导入导出程序_连接池管理类
* @author 23_11
*/
public class DBConnManager {
private Vector<String> poolnames = new Vector<String>(); //连接池名称列表；
private Vector<String> dburls = new Vector<String>(); //数据库连接路径列表；
private Vector<String> drivernames = new Vector<String>(); //驱动程序列表；
private Vector<String> usernames = new Vector<String>(); //用户名列表；
private Vector<String> psws = new Vector<String>(); //密码列表；
private Vector<Integer> maxconns = new Vector<Integer>(); //最大连接数列表；
// 连接池队列
private Hashtable<String, DBConnPool> connpools = new Hashtable<String, DBConnPool>();
/**
* 设置连接池信息
* @param poolname
* @param dburl
* @param drivername
* @param username
* @param psw
* @param maxconn
*/
private void setPoolInfo(
String poolname,
String dburl,
String drivername,
String username,
String psw,
int maxconn) {
//添加数据库的连接信息
poolnames.addElement(poolname);
dburls.addElement(dburl);
drivernames.addElement(drivername);
usernames.addElement(username);
psws.addElement(psw);
maxconns.addElement(maxconn);
}
/**
* 创建所有链接池
*/
private void createPools() {
DBConnPool dbcp = null;
for(int i=0; i<poolnames.toArray().length; i++) {
dbcp = new DBConnPool(
poolnames.elementAt(i),
dburls.elementAt(i),
drivernames.elementAt(i),
usernames.elementAt(i),
psws.elementAt(i),
maxconns.elementAt(i)
);
connpools.put(poolnames.elementAt(i), dbcp);
}
}
/**
* 构造函数
*/
public DBConnManager() {
/**
* 添加Access数据库
*/
String accessdburl = "jdbc:Access:///" + System.getProperty("user.dir")+"\\db\\addressBook.mdb";
setPoolInfo("Access", accessdburl, "com.hxtt.sql.access.AccessDriver", "", "", 300);
//创建所有连接池
createPools();
}
/**
* 获取连接池对应链接对象
* @param poolname
* @return 链接对象
*/
public Connection getConnection(String poolname) {
DBConnPool dbcp = connpools.get(poolname);
if(dbcp != null) {
return dbcp.getConnection();
}else {
return null;
}
}
/**
* 释放对应连接池的链接对象
* @param poolname
* @param con
*/
public void releaseConnection(String poolname, Connection con) {
DBConnPool pool = connpools.get(poolname);
if(pool != null) {
pool.releaseConnection(con);
}
}
/**
* 关闭所有连接池的链接
*/
public void closeConns() {
Enumeration<DBConnPool> pools = connpools.elements();
DBConnPool pool = null;
while(pools.hasMoreElements()) {
pool = pools.nextElement();
if(pool != null) {
pool.closeConns();
}
}
}
增、删、改以及查询操作//
/**
* 增(add)删(delete)改(update)执行方法
* @param sql
* @return 受影响的行数
*/
public int aduPerform(String sql) {
int num = -1;
if (sql != null || !sql.equals("")) {
Connection con = null;
Statement stmt = null;
try{
con = getConnection("Access");
//createStatement(resultSetType, resultSetConcurrency_并发性);
//(1)resultSetType_ResultSet.TYPE_FORWARD_ONLY、ResultSet.TYPE_SCROLL_INSENSITIVE 或 ResultSet.TYPE_SCROLL_SENSITIVE 之一;
//(2)resultSetConcurrency_ResultSet.CONCUR_READ_ONLY 或 ResultSet.CONCUR_UPDATABLE 之一;
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
num = stmt.executeUpdate(sql);
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
}catch(Exception e) {
e.printStackTrace();
}finally{
releaseConnection("Access", con);
}
}else {
System.out.println("=>DBConnManager_aduPerform_sql为空；");
}
return num;
}
/**
* 查询
* @param sql
* @param isSimple_是否是单纯查询(有些用于更新)；
* @return 搜索结果集
*/
public ResultSet query(String sql, boolean isSimple) {
ResultSet rs = null;
if (sql != null || !sql.equals("")) {
Connection con = null;
Statement stmt = null;
try{
con = getConnection("Access");
if(isSimple){
stmt = con.createStatement();
}else {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
}
rs = stmt.executeQuery(sql);
}catch(Exception e) {
e.printStackTrace();
}finally{
releaseConnection("Access", con);
}
}else {
System.out.println("=>DBConnManager_query_sql为空；");
}
return rs;
}
/**
* 测试
* @param args
*/
public static void main(String[] args) {
System.out.println("=>数据库导入导出程序_连接池管理类;");
String qsql = "select * from personTable";
System.out.println("=>查询语句："+qsql);
//连接池管理类
DBConnManager dbcm = new DBConnManager();
Connection con = dbcm.getConnection("Access");
try{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(qsql);
while(rs.next()) {
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println(rs.getString(3));
}
rs.close();
stmt.close();
con.close();
}catch(Exception e) {
e.printStackTrace();
}finally{
dbcm.releaseConnection("Access", con);
dbcm.closeConns();
}
}
}
