package com.db;
import java.sql.*;
import java.util.*;
/**
*
* @author zhaozhi3758
* @desc 底层数据库操作封装
*/
public class JdbcExcute {
/**
* 获取链接
* @return
*/
public Connection getCon(){
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");// 加载驱动
String url = "jdbc:mysql://localhost:3306/libraryv1?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true";
conn = DriverManager.getConnection(url, "root", "123");// 建立连接
conn.setAutoCommit(false);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 得到查询结果集的字段结构
* @param rs
* @return 字段结果集
*/
/* public HashMap getDataColl(ResultSet rs){
HashMap mapcoll = new HashMap();
try {
ResultSetMetaData rsmd = rs.getMetaData();
for(int i=1;i<=rsmd.getColumnCount();i++){
String filedName=rsmd.getColumnName(i);
System.out.println("*******"+filedName);
//String fileType = rsmd.getColumnTypeName(i);
mapcoll.put(filedName, "");
}
} catch (Exception e) {
e.printStackTrace();
}
return mapcoll;
}*/
/**
* 获得查询数据,使用HashMap进行封装
* @param sqlstr 查询的SQL语句
* @return
*/
public ArrayList selQuery(String sqlstr){
Connection connetcion=null;
Statement stm=null;
try {
connetcion = getCon();
connetcion.setAutoCommit(true);
} catch (Exception e1) {
e1.printStackTrace();
}
ArrayList datalist = new ArrayList();
ResultSet rs = null;
if(sqlstr.equals(""))
return null;
try {
stm=connetcion.createStatement();
rs = stm.executeQuery(sqlstr);
ResultSetMetaData rsmd = rs.getMetaData();
int colum=rsmd.getColumnCount();
//HashMap map = getDataColl(rs);
//Object[] keys = map.keySet().toArray();
while(rs.next()){
HashMap dataSet = new HashMap();
for(int i=1;i<=colum;i++){
String strFieldName = rsmd.getColumnName(i).toLowerCase();
// if(keys.toString().equals("COLUMN_NAME"))keys="Field";
// if(keys.toString().equals("COLUMN_TYPE"))keys="Type";
// if(keys.toString().equals("IS_NULLABLE"))keys="Null";
// if(keys.toString().equals("COLUMN_KEY"))keys="Key";
// if(keys.toString().equals("COLUMN_DEFAULT"))keys="Default";
// if(keys.toString().equals("EXTRA"))keys="Extra";
dataSet.put(strFieldName, rs.getObject(i));
}
datalist.add(dataSet);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(stm!=null)stm.close();
if(connetcion!=null)connetcion.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return datalist;
}
/**
* 执行数据库增删改操作
* @param sqlstr 增删改的SQL语句
* @return 成功:0 失败:-1
*/
public int saveOrUpdate(String sqlstr){
Connection connetcion=null;
try {
connetcion = getCon();
connetcion.setAutoCommit(true);
} catch (Exception e1) {
e1.printStackTrace();
}
try {
connetcion.createStatement().execute(sqlstr);
return 0;
} catch (Exception e) {
e.printStackTrace();
return -1;
}finally{
try {
if(!connetcion.isClosed()){
connetcion.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
JdbcExcute dbExcute=new JdbcExcute();
System.out.println(dbExcute.selQuery("SELECT * FROM reader"));
}
}
操作业务类
package com.db;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
public class DbOper {
private String tableName;
private String dbName="libraryv1";
private String _username = "root";
private String _password = "123";
private String _host = "localhost";
private String _port="3306";
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public String get_username() {
return _username;
}
public void set_username(String _username) {
this._username = _username;
}
public String get_password() {
return _password;
}
public void set_password(String _password) {
this._password = _password;
}
public String get_host() {
return _host;
}
public void set_host(String _host) {
this._host = _host;
}
public String get_port() {
return _port;
}
public void set_port(String _port) {
this._port = _port;
}
/**
* 查询指定数据库的所有表名
* @param dbName 数据库名称
* @return ArrayList
*/
public ArrayList selAllTableInfo(String dbName){
//返回的结果集
ArrayList<HashMap> resultList = new ArrayList<HashMap>();
JdbcExcute exec = new JdbcExcute();
String sql = "select table_name, table_type,engine from information_schema.tables where table_schema='"+this.dbName+"' order by table_name asc";
resultList = exec.selQuery(sql);
return resultList;
}
/**
* 查询指定表的表结构
* @param tableName 表名
* @return ArrayList
*/
public ArrayList selTableStructure(String tableName){
ArrayList<HashMap> resultList = new ArrayList<HashMap>();
JdbcExcute exec = new JdbcExcute();
String sql = "desc "+tableName;
resultList = exec.selQuery(sql);
return resultList;
}
/**
* 根据路径生成备份数据库的Shell字符串
* @param targetName 要备份的对象名：只能为表名和数据库名称
* @return 实际执行的shell命令
*/
public String getBackupShellString(String targetName){
String basepath=Thread.currentThread().getContextClassLoader().getResource("").toString();
String backFile = "";
String database_tools_path=basepath.substring(6, basepath.length()-4)+"dbtools/";//备份工具路径
if(targetName.equals(this.dbName)){//若要备份整个数据库
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
backFile = "F:/database/"+targetName+"_"+sdf.format(new Date())+".sql";//要备份的文件
targetName = "";
}else{
backFile = "F:/tables/"+targetName+".sql";
}
String OSType = System.getProperty("os.name");
String shellStr = "";
if(OSType.indexOf("Windows")!=-1){
shellStr = database_tools_path+"mysqldumpwin.exe -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+
this._password+" --result-file="+backFile+" --default-character-set=gbk "+this.dbName+" "+targetName;
}else{
shellStr = database_tools_path+"mysqldump -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+
this._password+" --result-file="+backFile+" --default-character-set=gbk "+this.dbName+" "+targetName;
}
System.out.print("##############"+shellStr);
return shellStr;
}
/**
* 备份数据库
* @param targetName 要备份的对象名：只能为表名和数据库名称
* @return 成功:TRUE 失败:FALSE
* 备份表直接备份在指定文件夹，备份库则按日期备份到指定的文件夹
*
*/
public boolean backup(String targetName){
String backFilePath = "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String backDirString = "F:/database/";//默认备份库
try {
if(!targetName.equals(this.dbName)){//备份表
File tableDir = new File("F:/tables/");
if(!tableDir.exists()){//存放表的文件夹不存在
tableDir.mkdir();
System.out.println("--------->"+tableDir);
}
backFilePath ="F:/tables/"+targetName+".sql";//要备份的文件
}else {//备份库
backFilePath ="F:/database/"+targetName+"_"+sdf.format(new Date())+".sql";//要备份的文件
File backDir = new File(backDirString);
if(!backDir.exists()){//存放库的文件夹不存在
backDir.mkdir();
}
}
//判断要备份的文件是否已存在
File backFile = new File(backFilePath);
if(backFile.exists()){
backFile.delete();
}
Runtime runt = Runtime.getRuntime();
//Process proc = runt.exec("D:/myec6_tomcat/webapps/cms/dbtools/mysqldumpwin.exe -h 127.0.0.1 -P3306 -uroot -p123 --result-file=F:/tables/menuinfo.sql --default-character-set=gbk bizoss_cms menuinfo");
Process proc = runt.exec(getBackupShellString(targetName));
int tag = proc.waitFor();//等待进程终止
if(tag==0){
return true;
}else{
return false;
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
/**
* 恢复数据库
* @param targetName 要备份的对象名：只能为表名和数据库名称
* @return 成功:TRUE 失败:FALSE
*/
public boolean restore(String targetName){
try {
Runtime runt = Runtime.getRuntime();
Process proc;
String cmdtext = this.getRestoreShellString(targetName);
if(System.getProperty("os.name").indexOf("Windows")!=-1){
String[] cmd= { "cmd", "/c", cmdtext};
proc= runt.exec(cmd);
}else{
String[] cmd= { "sh","-c",cmdtext};
proc = runt.exec(cmd);
}
System.out.println(cmdtext);
int tag = proc.waitFor();//等待进程终止
System.out.println("进程返回值为tag:"+tag);
if(tag==0){
return true;
}else{
return false;
}
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
/**
* 根据路径生成恢复数据库的Shell字符串
* @param targetName targetName 要还原的对象名：只能为表名和数据库名称
* @return 恢复数据时实际执行的shell
*/
public String getRestoreShellString(String targetName){
String basepath=Thread.currentThread().getContextClassLoader().getResource("").toString();
String database_tools_path=basepath.substring(6, basepath.length()-4)+"dbtools/";//备份工具路径
String backFile = "";//已备份的文件
if(targetName.indexOf(this.dbName) == -1){//还原表
backFile = "f:/tables/"+targetName+".sql";
}else{//还原库
backFile ="f:/database/"+targetName;
}
String OSType = System.getProperty("os.name");
String shellStr = "";
if(OSType.indexOf("Windows")!=-1){
shellStr = database_tools_path+"mysqlwin.exe -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+
this._password+" --default-character-set=gbk "+this.dbName +" < "+backFile;
}else{
shellStr = database_tools_path+"mysql -h "+this._host+" -P"+this._port+" -u"+this._username+" -p"+
this._password+" --default-character-set=gbk "+this.dbName+" < "+backFile;
}
return shellStr;
}
public static void main(String[] args) {
DbOper db=new DbOper();
// System.out.println(db.selAllTableInfo("libraryv1"));
System.out.println(db.selTableStructure("bookinfo"));
//System.out.println(db.backup("t"));
// System.out.println(System.getProperty("os.name"));
//System.out.println(db.restore("t"));
}
}
最新评论