public class UserInfo {
protected Shell shell;
private Table table;
private Text text;
private AudioStream as;
private FileInputStream fileau=null; //背景音乐流
private Display display;
private String filePath; //文件路径
private DBHelper db=new DBHelper();
private List<UsersInfo> ufs;
private static boolean play=false;
public static void main(String[] args) {
try {
UserInfo window = new UserInfo();
window.open();
} catch (Exception e) {
e.printStackTrace();
}
}
public void open() {
display = Display.getDefault();
createContents();
shell.open();
shell.layout();
while (!shell.isDisposed()) {
if (!display.readAndDispatch()) {
display.sleep();
}
}
}
protected void createContents() {
shell = new Shell();
shell.addDisposeListener(new DisposeListener() {
public void widgetDisposed(DisposeEvent arg0) {
if(as!=null){
AudioPlayer.player.stop(as);
try {
as.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(fileau!=null){
try {
fileau.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
});
shell.setImage(SWTResourceManager.getImage(UserInfo.class, "/images/yc.ico"));
shell.setSize(274, 417);
shell.setText("用户信息管理");
shell.setLayout(new FillLayout(SWT.HORIZONTAL));
shell.setLocation(
(display.getClientArea().width - shell.getSize().x) / 2,
(display.getClientArea().height - shell.getSize().y) / 2);
Composite composite = new Composite(shell, SWT.NONE);
table = new Table(composite, SWT.BORDER | SWT.FULL_SELECTION);
table.setBounds(0, 0, 264, 143);
table.setHeaderVisible(true);
table.setLinesVisible(true);
TableColumn tableColumn = new TableColumn(table, SWT.CENTER);
tableColumn.setWidth(56);
tableColumn.setText(" 编号");
TableColumn tableColumn_1 = new TableColumn(table, SWT.CENTER);
tableColumn_1.setWidth(100);
tableColumn_1.setText("姓名");
final TableCursor tableCursor = new TableCursor(table, SWT.NONE);
TableColumn tableColumn_2 = new TableColumn(table, SWT.CENTER);
tableColumn_2.setWidth(100);
tableColumn_2.setText("图像");
final Label label = new Label(composite, SWT.BORDER);
label.setBounds(42, 149, 152, 122);
Button button = new Button(composite, SWT.NONE);
button.setBounds(127, 329, 80, 25);
button.setText("添加");
text = new Text(composite, SWT.BORDER);
text.setBounds(42, 288, 152, 21);
ToolBar toolBar = new ToolBar(composite, SWT.FLAT | SWT.RIGHT);
toolBar.setToolTipText("打开/关闭");
toolBar.setBounds(0, 352, 27, 26);
ToolItem toolItem = new ToolItem(toolBar, SWT.NONE);
toolItem.setHotImage(SWTResourceManager.getImage(UserInfo.class, "/images/open.jpg"));
toolItem.setImage(SWTResourceManager.getImage(UserInfo.class, "/images/close.png"));
table.addSelectionListener(new SelectionAdapter() {
@Override
public void widgetSelected(SelectionEvent e) {
TableItem ti=tableCursor.getRow();
if(ti!=null){
for(UsersInfo uf:ufs){
if(uf.getUsid()==Integer.parseInt(ti.getText(0).trim() )){
label.setImage( scaledImageSize(label,uf.getPhoto()) );
}
}
text.setText(ti.getText(1));
}
}
});
//添加
button.addSelectionListener(new SelectionAdapter() {
@Override
public void widgetSelected(SelectionEvent e) {
String uname=text.getText().trim();
String sql="insert into userInfo values(seq_usid.nextval,?,?)";
List<Object> params=new ArrayList<Object>();
params.add(uname);
if(filePath!=null && !"".equals(filePath) ){
params.add(getPic(filePath));
}else{
params.add("");
}
int result=db.update(sql, params);
if(result>0){
showUserInfo();
text.setText("");
}else{
MessageBox msg=new MessageBox(shell,SWT.NONE);
msg.setText("失败提示");
msg.setMessage("数据添加失败...");
msg.open();
}
}
});
Button button_1 = new Button(composite, SWT.NONE);
//打开图片选择对话框
button_1.addSelectionListener(new SelectionAdapter() {
@Override
public void widgetSelected(SelectionEvent e) {
// 打开文件选择框，并设定文件名过滤
FileDialog fd=openFileDialog();
if(fd!=null&&!"".equals(fd.getFilterPath()) ){
filePath=fd.getFilterPath() + "\\"+ fd.getFileName();
Image image = previewImage(label,filePath);
label.setImage(image);
}
}
});
button_1.setBounds(28, 329, 80, 25);
button_1.setText("选择");
//音乐开关
toolItem.addSelectionListener(new SelectionAdapter() {
@Override
public void widgetSelected(SelectionEvent e) {
if(!play){
play=true;
//音乐播放
try {
fileau=new FileInputStream(System.getProperty("user.dir")+"/src/sounds/bgmusic.mid");
as=new AudioStream(fileau);
AudioPlayer.player.start(as);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e1) {
e1.printStackTrace();
}
}else{
play=false;
AudioPlayer.player.stop(as);
}
}
});
showUserInfo();
}
private void showUserInfo(){
table.removeAll();
TableItem ti;
String sql="select * from userInfo";
ufs=db.find(sql,null,UsersInfo.class);
InputStream is;
for(UsersInfo uf:ufs){
ti=new TableItem(table,SWT.NONE);
ti.setText(new String[]{uf.getUsid()+"",uf.getUname()});
is=new ByteArrayInputStream(uf.getPhoto());
ti.setImage(2,scaledImageSize(50,50,is));
}
}
//将图片转为字节数组
private byte[] getPic(String pic){
FileInputStream fis=null;
byte[] bt = null;
try {
fis = new FileInputStream(new File(pic));
bt=new byte[fis.available()];
fis.read(bt);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return bt;
}
// 打开文件选择框，并设定文件名过滤
private FileDialog openFileDialog() {
FileDialog fd = new FileDialog(shell);
String[] fileExt = { "*.jpg", "*.gif" };
fd.setFilterExtensions(fileExt);
fd.open();
return fd;
}
// 预览图片内容
private Image previewImage(Label label,String path) {
Image image = null;
try {
File f = new File(path);
if (f.exists()) {
InputStream is = new FileInputStream(f);
if (is != null) {
image = scaledImageSize(label, is); //调整图片大小
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return image;
}
// 调整图片大小
private Image scaledImageSize(Label lbl,InputStream is) {
ImageData id = new ImageData(is);
int width, height;
if (id.width > lbl.getBounds().width) {
width = lbl.getBounds().width;
} else{
width = id.width;
}
if (id.height > lbl.getBounds().height) {
height = lbl.getBounds().height;
} else{
height = id.height;
}
id = id.scaledTo(width, height);
return new Image(null, id);
}
// 调整图片大小
private Image scaledImageSize(Label lbl,byte[] bytes) {
ImageData id = new ImageData(new ByteArrayInputStream(bytes));
id = id.scaledTo(lbl.getBounds().width, lbl.getBounds().height);
return new Image(null, id);
}
// 调整图片大小
private Image scaledImageSize(int width,int height,InputStream is) {
ImageData id = new ImageData(is);
if (id.width <= width) {
width = id.width;
}
if (id.width <= height) {
height = id.height;
}
id = id.scaledTo(width, height);
return new Image(null,id);
}
public class DBHelper {
private Connection con=null;
private PreparedStatement pstmt=null;
private ResultSet rs=null;
private CallableStatement cs=null;
//静态快，用来加载驱动
static{
try {
Class.forName(MyPro.getInstance().getProperty("drivername"));
} catch (ClassNotFoundException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
/**
* 获取连接
*/
public Connection getConnection(){
try {
con=DriverManager.getConnection(MyPro.getInstance().getProperty("url"),MyPro.getInstance());
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
return con;
}
/**
* 数据库连接池的方式获取连接
*/
//
public Connection getConnection(){
//
//javax.naming.Context提供了查找JNDI 的接口
//
try {
//
Context ctx=new InitialContext();
//
DataSource ds=(DataSource) ctx.lookup("java:comp/env/jdbc/news");
//
con=ds.getConnection(); //与JNDI获取到的数据源建立连接
//
} catch (NamingException e) {
//
e.printStackTrace();
//
LogUtil.log.error(e.toString());
//
} catch (SQLException e) {
//
LogUtil.log.error(e.toString());
//
throw new RuntimeException(e);
//
}
//
return con;
//
}
/**
* 关闭的方法
*/
public void closeAll(Connection con,PreparedStatement pstmt,ResultSet rs,CallableStatement cs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
}
/**
* 设置PreparedStatement对象的sql语句中的参数？
*/
public void setValues(PreparedStatement pstmt,List<Object> params){
if(pstmt!=null&¶ms!=null&¶ms.size()>0){
for(int i=0;i<params.size();i++){
Object obj=params.get(i);
try {
if("javax.sql.rowset.serial.SerialBlob".equals(obj.getClass().getName())){
pstmt.setBlob(i+1, (Blob)obj);
}if("java.lang.Integer".equals(obj.getClass().getName()) ){
pstmt.setInt(i+1, (Integer)obj);
}else{
try {
pstmt.setString(i+1,(String)obj);
} catch (Exception e) {
pstmt.setBytes(i+1,(byte[])obj);
}
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
}
}
/**
* 增删改
* @param sql：sql语句集合，里面可以加？
* @param params：表示?对应的参数值的集合
* @return int:返回的值。成功>0，失败<=0
*/
public synchronized int update(List<String> sql,List<List<Object>> params){
int result=0;
con=getConnection();
try {
con.setAutoCommit(false); //事务处理
for(int i=0;i<sql.size();i++){
List<Object> param=params.get(i);
pstmt=con.prepareStatement(sql.get(i)); //预编译对象
setValues(pstmt,param); //设置参数
result=pstmt.executeUpdate();
}
con.commit(); //没有错处执行
} catch (SQLException e) {
LogUtil.log.error(e.toString());
try {
con.rollback(); //出错回滚
} catch (SQLException e1) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
throw new RuntimeException(e);
}finally{
try {
con.setAutoCommit(true);
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
closeAll(con,pstmt,null,null);
}
return result;
}
/**
* 单表增删改
* @param sql：sql语句集合，里面可以加？
* @param params：表示?对应的参数值的集合
* @return int:返回的值。成功>0，失败<=0
*/
public synchronized int update(String sql,List<Object> params){
int result=0;
con=getConnection();
try {
pstmt=con.prepareStatement(sql); //预编译对象
setValues(pstmt,params); //设置参数
result=pstmt.executeUpdate();
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}finally{
closeAll(con,pstmt,null,null);
}
return result;
}
/**
* 聚合查询
* @param sql：聚合查询语句
* @param params：参数列表，用来替换sql中的?（占位符）
* @return list:结果集
*/
public List<String> uniqueResult(String sql,List<Object> params){
List<String> list=new ArrayList<String>();
con=getConnection();
try {
pstmt=con.prepareStatement(sql); //预编译对象
setValues(pstmt,params); //设置参数
rs=pstmt.executeQuery(); //执行查询
ResultSetMetaData md=rs.getMetaData(); //结果集的元数据，它反映了结果集的信息
int count=md.getColumnCount(); //取出结果集中列的数量
if(rs.next()){
for(int i=1;i<=count;i++){
list.add(rs.getString(i));
}
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}finally{
closeAll(con,pstmt,rs,null);
}
return list;
}
/**
* 查询单个表
* @param <T> 泛型：即你要得到的集合中存的对象的类型
* @param sql: 查询语句，可以含有?
* @param params: ?所对应的参数值的集合
* @param c： 泛型类型所对应的反射对象
* @return ：存储了对象的集合
* @throws SQLException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public <T> List<T> find(String sql,List<Object> params,Class<T> c) {
List<T> list=new ArrayList<T>(); //要返回的结果的集合
con=getConnection(); //获取连接
try {
pstmt=con.prepareStatement(sql); //预编译对象
setValues(pstmt, params); //设置占位符
rs=pstmt.executeQuery(); //执行查询语句，得到结果集
Method[] ms=c.getMethods(); //取出这个反射实例的所有方法
ResultSetMetaData md=rs.getMetaData(); //获取结果集的元数据，它反映了结果集的信息
String[] colnames=new String[md.getColumnCount()];//创建一个数据colnames，用来存放列的名字
for(int i=0;i<colnames.length;i++){ //将列名保存到colname数组中
colnames[i]=md.getColumnName(i+1);
}
T t;
String mname=null; //方法名
String cname=null; //列名
String ctypename=null; //类型名
while(rs.next()){
t=(T)c.newInstance(); //创建反射类的实例化对象 Product t=(Product)c.newInstance();
for(int i=0;i<colnames.length;i++){//循环方法名 ,格式为setXXXX或getXXX
cname=colnames[i]; //取出列名并在前面加上set setXXX
cname="set"+cname.substring(0,1).toUpperCase()+cname.substring(1).toLowerCase();
if(ms!=null&&ms.length>0){
for(Method m:ms){//循环列名
mname=m.getName(); //取出方法名
if(cname.equals(mname)&&rs.getObject(colnames[i])!=null){//判断方法名和列名是否一样，相同则激活方法，注入数据 //只要"set"+数据列名.equalsIgnoreCase（方法名），则激活这个方法
//setXXX(String str); setXXX(int num); 激活对应的方法还必须知道它的数据类型
ctypename=rs.getObject(colnames[i]).getClass().getName();//获取当前列的类型名
if("java.lang.Integer".equals(ctypename)){
m.invoke(t,rs.getInt(colnames[i])); //obj.setXX(xx);
}else if("java.lang.String".equals(ctypename)){
m.invoke(t, rs.getString(colnames[i]));
}else if("java.math.BigInteger".equals(ctypename)){
m.invoke(t, rs.getDouble(colnames[i]));
}else if("java.math.BigDecimal".equals(ctypename)){
try{
m.invoke(t, rs.getInt(colnames[i]));
}catch(Exception e1){
m.invoke(t, rs.getDouble(colnames[i]));
}
}else if("java.sql.Timestamp".equals(ctypename)){
m.invoke(t, rs.getString(colnames[i]));
}else if("java.sql.Date".equals(ctypename)){
m.invoke(t, rs.getString(colnames[i]));
}else if("java.sql.Time".equals(ctypename)){
m.invoke(t, rs.getString(colnames[i]));
}else if("image".equals(ctypename)){
m.invoke(t,rs.getBlob(colnames[i]));
}else if("oracle.sql.BLOB".equals(ctypename)){
BufferedInputStream is = null;
byte[] bytes = null;
Blob blob=rs.getBlob(colnames[i]);
try {
is = new BufferedInputStream(blob.getBinaryStream());
bytes = new byte[(int) blob.length()];
is.read(bytes);
} catch (Exception e) {
e.printStackTrace();
}
m.invoke(t,bytes);
}else{
m.invoke(t, rs.getString(colnames[i]));
}
break;
}
}
}
}
list.add(t);
}
} catch (SecurityException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
} catch (IllegalArgumentException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
} catch (InstantiationException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
} catch (IllegalAccessException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
} catch (InvocationTargetException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}finally{
closeAll(con, pstmt, rs,null);
//closeAll(null, pstmt, rs,null);
}
return list;
}
/**
* 多表查询
* @param sql：查询语句
* @param params： 查询语句中?所对应的值
* @return：结果集，存在一个List表中，用Map一对一的存放
* @throws SQLException
*/
public List<String> find(String sql,List<Object> params){
List<String> result=new ArrayList<String>(); //将结果一次存在list中返回
con=this.getConnection();
try {
pstmt=con.prepareStatement(sql);
this.setValues(pstmt, params);
rs=pstmt.executeQuery();
ResultSetMetaData md=rs.getMetaData(); //获取结果集的元数据
String[] colnames=new String[md.getColumnCount()]; //获取结果集中的列名
for(int i=0;i<colnames.length;i++){
colnames[i]=md.getColumnName(i+1);
}
while(rs.next()){
for(int i=0;i<colnames.length;i++){
result.add(rs.getString(i+1));
}
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}finally{
this.closeAll(con, pstmt, rs ,null);
}
return result;
}
/**
* 存过过程参数设置
* @param cst
* @param params
*/
@SuppressWarnings("unchecked")
public void setParams(CallableStatement cs,Map<Integer,Object> paramsIn,Map<Integer,String> paramsOut){
int key=0; //对应的问号的序号
Object value=null;
String typename=null;
String attrType;
Set keys; //所有的键
if(paramsIn!=null&¶msIn.size()>0){
keys=paramsIn.keySet(); //取出所有入参的键，即入参对应的问号的序号
if(keys!=null){
Iterator iterator=keys.iterator();
while(iterator.hasNext()){
key=(Integer) iterator.next();
value=paramsIn.get(key); //1,88
attrType=value.getClass().getName();
//判断值的数据类型
try {
if("java.lang.Integer".equals(attrType)){
cs.setInt(key,(Integer)value);
}else if("java.lang.String".equals(attrType)){
cs.setString(key,(String)value);
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
}
}
int typeId=0;
if(paramsOut!=null&¶msOut.size()>0){
keys=paramsOut.keySet(); //取出所有入参的键，即入参对应的问号的序号
if(keys!=null){
Iterator iterator=keys.iterator();
while(iterator.hasNext()){
key=(Integer) iterator.next();
typename=(String) paramsOut.get(key); //3,varchar 4, cursor
//判断值的数据类型
try {
if("cursor".equals(typename)){
typeId=oracle.jdbc.OracleTypes.CURSOR;
}else if("int".equals(typename)){
typeId=Types.INTEGER;
}else if("double".equals(typename)){
typeId=Types.NUMERIC;
}else{
typeId=Types.VARCHAR;
}
cs.registerOutParameter(key,typeId);
} catch (Exception e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
}
}
}
/**
* 执行oracle中的存储过程
* @param prc：要执行的存储过程名字 如：test(?);
* @param params：占位符对于的参数列表
* @return
*/
@SuppressWarnings("unchecked")
public List<String> execProcedure(String prc,Map<Integer,Object> paramsIn,Map<Integer,String> paramsOut){
List<String> list=new ArrayList<String>();
String pro="{call "+prc+"}";
con=getConnection();
int key;
String typename;
Set keys;
ResultSetMetaData md=null;
try {
cs=con.prepareCall(pro);
//设置过程的参数
setParams(cs,paramsIn,paramsOut);
cs.execute();
if(paramsOut!=null&¶msOut.size()>0){
keys=paramsOut.keySet(); //取出所有入参的键，即入参对应的问号的序号
if(keys!=null){
Iterator iterator=keys.iterator();
while(iterator.hasNext()){
key=(Integer) iterator.next();
typename=(String) paramsOut.get(key); //3,varchar 4, cursor
//判断值的数据类型
try {
if("cursor".equals(typename)){
rs=(ResultSet) cs.getObject(key); //1 a 24 2 b 23
md=rs.getMetaData();
while(rs.next()){
for(int i=0;i<md.getColumnCount();i++){
list.add(rs.getString(i+1));
}
}
}else{
list.add(cs.getString(key));
}
} catch (Exception e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
}
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}finally{
closeAll(con,null,rs,cs);
}
return list;
}
/**
* oracle数据的备份
* @param path：备份文件存放路径
* @param tablespaceName：要备份的表空间
* @return
*/
public int OracleBackUp(String path,String tablespaceName){
Runtime rt = Runtime.getRuntime();
@SuppressWarnings("unused")
Process processexp = null;
//String exp="exp system/a@orcl tablespaces=("+tablespaceName+") file="+path+".dmp log="+path+".log owner=scott";
String exp="exp system/a@orcl tablespaces=("+tablespaceName+") file="+path+".dmp";
try {
processexp = rt.exec(exp);
return 1;
} catch (IOException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
/**
* Oracle中按用户方式恢复数据库
* @param uname：用户名
* @return
*/
public int dataResume(String path,String uname){
Runtime rt = Runtime.getRuntime();
@SuppressWarnings("unused")
Process processimp = null;
//String imp="imp system/a@orcl file="+path+" log="+path.substring(0,path.lastIndexOf("."))+".log ignore=y owner=scott"; // full=y
//
List<String> list=new ArrayList<String>();
//
list.add("tri_aid");
//
list.add("tri_cid");
//
list.add("tri_eid");
//
list.add("tri_pid");
//
list.add("tri_sid");
//
list.add("tri_uid");
//
String sql;
//
//
for(String str:list){
//
sql="alter trigger "+str+" disable";
//
con=getConnection(); //获取连接
//
//
try {
//
pstmt=con.prepareStatement(sql); //预编译对象
//
pstmt.execute();
//
} catch (SQLException e) {
//
e.printStackTrace();
//
}
//
}
String imp="imp system/a@orcl file="+path+" ignore=y full=y";
try {
processimp = rt.exec(imp);
//
//
for(String str:list){
//
sql="alter trigger "+str+" enable";
//
con=getConnection(); //获取连接
//
//
try {
//
pstmt=con.prepareStatement(sql); //预编译对象
//
pstmt.execute();
//
} catch (SQLException e) {
//
e.printStackTrace();
//
}
//
}
//
return 1;
} catch (IOException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
/**
* 获取当前数据库中的所有表的表名
* @return：返回一个list,存放着当前数据库中的所有表的表名
* oracle中获取当前模式的所有表信息为select * from tab;
*/
public List<String> findtable(){
List<String> list=new ArrayList<String>();
con=this.getConnection();
try {
DatabaseMetaData dmd=con.getMetaData();
rs=dmd.getTables(null, null, null,new String[]{"TABLE"});
while(rs.next()){
list.add(rs.getString("Table_name")); //将当前数据库中是所有表存到list中
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
return list;
}
/**
* 自动创建一个由一个类中的属性作为字段的表
* @param <T>
* @param c：要生成表的类
*/
public <T> void getTable(Class<T> c){
con=this.getConnection();
Field[] fs=c.getDeclaredFields(); //获取元数据
String st=c.getName(); //得到结果集中的每个列名
st=st.substring(st.lastIndexOf(".")+1); //截取类名
st=st.substring(0,1).toLowerCase()+st.substring(1,st.length()); //将首字母变为小写
String str1=null;
StringBuffer str=new StringBuffer();
String str3=null;
List<String> list=this.findtable();
try {
for(String lt:list){
if(lt.equalsIgnoreCase(st)){
String sql="drop table "+st;
pstmt=con.prepareStatement(sql);
pstmt.executeUpdate();
break;
}
}
str.append("create table "+st+"(id int primary key identity(1,1), ");
for(int i=0;i<fs.length;i++){
str1=fs[i].getName(); //得到每个属性的名字
Class<?> str2=fs[i].getType(); //得到每个属性的数据类型
str3=str2.toString();
if("class java.lang.String".equals(str3)){//如果是String类型，则把它改为varchar(50)
str3="varchar(50)";
}
if("double".equals(str3)){//如果是double类型，则把它改为float
str3="float";
}
if(i==fs.length-1){
str.append(str1+"\t"+str3+")"); //如果是最后一个字段，不要加逗号而加括号
}else{
str.append(str1+"\t"+str3+","); //如果不是最后一个，则在语句后在括号
}
}
String sql=str.toString();
System.out.println(sql);
boolean result=true;
pstmt=con.prepareStatement(sql);
result=pstmt.execute();
if(result!=true){
LogUtil.log.info(st+"数据库创建成功!");
}else{
LogUtil.log.info(st+"数据库创建失败!");
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
/**
* 自动创建一个由一个类中的属性作为字段的表
* @param <T>
* @param c：要生成表的类
* @throws ClassNotFoundException
* @throws IllegalAccessException
* @throws InstantiationException
*/
@SuppressWarnings("null")
public <T> void getTables(File f){
Class<?> c = null;
con=this.getConnection();
File[] fls=f.listFiles(); //得到f路径下的文件
String fname=null;
String st=null;
String str1=null;
String str;
String str3=null;
Field[] fd;
if(fls!=null){ //判断是不是有文件
for(File fs:fls){ //循环读取文件
fname=fs.getName(); //得到文件名
if(fname.substring(fname.lastIndexOf(".")+1).equalsIgnoreCase("java")){//判断是不是java文件
st=fname.substring(0,fname.indexOf("."))+".class";//取到类名，并添加后缀.class
fd=c.getClass().getDeclaredFields();//得到java类中的属性
str="create table "+st+"(id int primary key identity(1,1), ";
for(int i=0;i<fd.length;i++){
str1=fd[i].getName(); //得到每个属性的名字
Class<?> str2=fd[i].getType(); //得到每个属性的数据类型
str3=str2.toString();
if("class java.lang.String".equals(str3)){//如果是String类型，则把它改为varchar(50)
str3="varchar(50)";
}
if("double".equals(str3)){//如果是double类型，则把它改为float
str3="float";
}
if(i==fd.length-1){
str=str+str1+"\t"+str3+")"; //如果是最后一个字段，不要加逗号而加括号
}else{
str=str+str1+"\t"+str3+","; //如果不是最后一个，则在语句后在括号
}
}
String sql=str;
System.out.println(sql);
boolean result=true;
try {
pstmt=con.prepareStatement(sql);
result=pstmt.execute();
if(result!=true){
LogUtil.log.info("数据库创建成功!");
}else{
LogUtil.log.info("数据库创建失败!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.closeAll(con, pstmt,null,null);
}
}
}
}else{
LogUtil.log.info("此文件下，没有文件！");
}
}
/**
* 将一个表中的字段自动生成一个存放在path路径下的java文件
* @param tablename:数据库中的表名 存放在list中
* @param path ：生成的java所存放的路径
* @throws SQLException
* @throws IOException
*/
public void getClasses(List<String> tablename,String path){
con=this.getConnection();
String sql="";
try {
List<String> list=this.findtable();
if(tablename!=null&&tablename.size()>0){
for(String tbname:tablename){
StringBuffer str=new StringBuffer();
StringBuffer getstr =new StringBuffer(); //用来记录get方法
StringBuffer setstr=new StringBuffer(); //用来记录set方法
StringBuffer name=new StringBuffer();
for(int j=0;j<list.size();j++){
if(tbname.equals(list.get(j))){//如果存在此表，则生成相应的java类
sql="select * from sysobjects where name="+tbname;
pstmt=con.prepareStatement(sql);
name.append(tbname.substring(0,1).toUpperCase()); //将首字母变为大写
name.append(tbname.substring(1,tbname.length()));
sql="select * from "+tbname;
pstmt=con.prepareStatement(sql);
rs=pstmt.executeQuery();
str.append("public class "+name+"{\n");
ResultSetMetaData md=rs.getMetaData(); //获取结果集的元数据
String[] colnames=new String[md.getColumnCount()];
String[] ctypename=new String[md.getColumnCount()];
for(int i=0;i<colnames.length;i++){ //将列名保存到colname数组中，类型保存到ctypename数组
colnames[i]=md.getColumnName(i+1);//获取结果集中的列名
ctypename[i]=md.getColumnTypeName(i+1);//获取结果集中的列每个列的类型
if(ctypename[i].indexOf("char")>0){
ctypename[i]="String";
}
if(ctypename[i].equals("money")){
ctypename[i]="double";
}
str.append("\tprivate"+"\t"+ctypename[i]+"\t"+colnames[i]+";\n");
getstr.append(Methods.getMethod(colnames[i],ctypename[i])+"\n");
setstr.append(Methods.setMethod(colnames[i],ctypename[i])+"\n");
}
str.append(getstr);
str.append(setstr+"}");
str.substring(str.indexOf("p"), str.length());
System.out.println(str);
Methods.createFile(str.toString(), path, name.toString());
break;
}else if(j==list.size()-1){
LogUtil.log.error(tbname+"表不存在");
}
}
}
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
}
/**
* sql数据库的备份
* @param path：备份的路径
* @return
*/
public boolean backupdatabase(String path){
boolean result=true;
con=this.getConnection();
//截取数据库的名字
String databasename=MyPro.getInstance().getProperty("sql.url");
databasename=databasename.substring(databasename.indexOf("=")+1);
String sql="backup database ? to disk=?";//数据库的备份第一?是要备份的数据库，第二个是备份的路径
List<Object> params=new ArrayList<Object>();
params.add(databasename);
params.add(path);
try {
pstmt=con.prepareStatement(sql);
this.setValues(pstmt, params);
result=pstmt.execute();
if(result!=false){
LogUtil.log.info("数据库"+databasename+"备份失败...");
}else{
LogUtil.log.info("数据库"+databasename+"备份成功...");
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
return result;
}
/**
* 数据库的还原
* @param path：还原的路径
* @return
*/
public boolean restoredatabase(String path){
boolean result=true;
con=this.getConnection();
String databasename=MyPro.getInstance().getProperty("sql.url");
databasename=databasename.substring(databasename.indexOf("=")+1); //截取数据库的名字
StringBuffer sql=new StringBuffer("use master;declare @dbName nvarchar(50);") ;
sql.append("set @dbName='"+databasename+"';declare @spid nvarchar(20);" +
"declare cur_lock cursor for SELECT DISTINCT request_session_id FROM master.sys.dm_tran_locks " +
"WHERE resource_type = 'DATABASE' AND resource_database_id = db_id(@dbName);");
sql.append("open cur_lock;fetch cur_lock into @spid while @@fetch_status=0 begin " +
"exec( 'kill '+@spid) fetch Next From cur_lock into @spid end;close cur_lock;deallocate cur_lock;");
sql.append("restore database ? from disk=? with replace;"); //数据库的还原
List<Object> params=new ArrayList<Object>();
params.add(databasename);
params.add(path);
try {
pstmt=con.prepareStatement(sql.toString());
this.setValues(pstmt, params);
result=pstmt.execute();
if(result!=false){
LogUtil.log.info("数据库"+databasename+"还原失败...");
}else{
LogUtil.log.info("数据库"+databasename+"还原成功...");
}
} catch (SQLException e) {
LogUtil.log.error(e.toString());
throw new RuntimeException(e);
}
return result;
}
}
扫一扫
1453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
