databasemanager.java

简单java成绩管理 提供添加 统计 修改功能

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;


public class databasemanager {
	
	DefaultTableModel tmodel;
	String dbdriverpath="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	String dbURL="jdbc:sqlserver://127.0.0.1 :1433";
	String dbname="学生管理";
	String dbuser="sa";
	String dbpassword="123";
	JTable table;
	Connection con;
	String tablename="学生信息表";//用于在更新时使用   指定更新的表  
	//boolean ifcanupdate=false;
	public databasemanager() {
		initconnection(dbdriverpath,dbURL,dbname,dbuser,dbpassword);
	}
	public void initconnection(String dbdriverpath,
			String dbURL,String dbname,String dbuser,String dbpassword){
		
		if(dbdriverpath==null)dbdriverpath=this.dbdriverpath;
		if(dbURL==null)dbURL=this.dbURL;
		if(dbname==null)dbname=this.dbname;
		if(dbuser==null)dbuser=this.dbuser;
		if(dbpassword==null)dbpassword=this.dbpassword;
		
		try {
			Class.forName(dbdriverpath);
		} catch (ClassNotFoundException e) {
			System.out.println("数据库驱动出错!");
		}
		try {
			con=DriverManager.getConnection(dbURL+";databasename="+dbname,
					dbuser,dbpassword);
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("数据库连接出错!");
		}
		
	}
	
	public ResultSet select(String sql){
		ResultSet rset=null;
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			rset=ps.executeQuery();
			
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("数据库出错在查询!");
		}
		return rset;
	}
	public ResultSet select(String sql,Object obj){
		ResultSet rset=null;
		Object[]objs=new Object[1];
		objs[0]=obj;
		rset=select(sql,objs);
		return rset;
	}
	public ResultSet select(String sql,Object[]obj){
		ResultSet rset=null;
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			
			for(int i=1;i<=obj.length;i++){
				ps.setObject(i, obj[i-1]);
			}
			rset=ps.executeQuery();
			
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("数据库出错在查询!");
		}
		return rset;
	}
	public ResultSet select(String sql,Object[]objname,Object[]obj){
		ResultSet rset=null;
		if(objname!=null&&objname.length>0){
			sql=createsql(sql,objname);
		}
		rset=select(sql,obj);
		return rset;
	}
	
	public int update(String sql){
		int rn=0;
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			rn=ps.executeUpdate();
			System.out.println(rn);
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("数据库出错在更新!");
		}
		return rn;
	}
	public int update(String sql,Object obj){
		Object[]objs=new Object[1];
		objs[0]=obj;
		int rn=0;
		rn=update(sql,objs);
		return rn;
	}
	public int update(String sql,Object[]obj){
		int rn=0;
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			for(int i=1;i<=obj.length;i++){
				ps.setObject(i,obj[i-1]);
			}
			rn=ps.executeUpdate();
			System.out.println(rn);
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("数据库出错在更新!");
		}
		return rn;
	}
	public int update(String sql,Object[]objname,Object[]obj){
		int rn=0;
		
		if(objname!=null&&objname.length>0){
			sql=createsql(sql,objname);
		}
		
		rn=update(sql,obj);
		return rn;
	}
	
	public int delete(String sql){
		int rn=0;
		rn=update(sql);
		return rn;
	}
	public int delete(String sql,Object obj){
		int rn=0;
		rn=update(sql,obj);
		return rn;
	}
	public int delete(String sql,Object[]obj){
		int rn=0;
		rn=update(sql,obj);
		return rn;
	}
	public int delete(String sql,Object[]objname,Object[]obj){
		int rn=0;
		
		if(objname!=null&&objname.length>0){
			sql=createsql(sql,objname);
		}
		
		rn=delete(sql,obj);
		return rn;
	}
	
	public int insert(String sql){
		int rn=0;
		rn=update(sql);
		return rn;
	}
	public int insert(String sql,Object obj){
		int rn=0;
		rn=update(sql,obj);
		return rn;
	}
	public int insert(String sql,Object[]obj){
		int rn=0;
		rn=update(sql,obj);
		return rn;
	}
	public int insert(String sql,Object[]objname,Object[]obj){
		int rn=0;
		
		if(objname!=null&&objname.length>0){
			sql=createsql(sql,objname);
		}
		
		rn=insert(sql,obj);
		return rn;
	}
	
	public boolean login(Object[]objn,Object[]objc){
		boolean ifsucc=false;
		
		
		
		return ifsucc;
	}
	
	public void showdata(ResultSet rset){
		
		JFrame  frame=new JFrame("");
		
		frame.setSize(400, 300);
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setVisible(true);
		JTable table=maketable(rset);
		JScrollPane sp=new JScrollPane();
		
		sp.setViewportView(table);
		frame.getContentPane().add(sp);
		frame.setVisible(true);
		
	}

	public String[] getcolumnsname(ResultSet rset){
		ResultSetMetaData rm;
		String[] columnsname=null;
		try {
			rm=rset.getMetaData();
			int columnsn=rm.getColumnCount();
			columnsname=new String[columnsn];
			for(int i=1;i<=columnsn;i++){
				columnsname[i-1]=rm.getColumnName(i);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return columnsname;
	}
	public JTable maketable(ResultSet rset){
		if(rset==null)return null;
		
		
		
		table=new JTable();
		tmodel=new DefaultTableModel();
		table.setEnabled(false);
		
		ResultSetMetaData rm;
		int columnsn=0;
		try{
			rm=rset.getMetaData();
			columnsn=rm.getColumnCount();
			for(int i=1;i<=columnsn;i++){
				tmodel.addColumn(rm.getColumnName(i));
			}
		}catch(SQLException e1) {
			e1.printStackTrace();
		}
			
		
		try {
			while(rset.next()){
				Vector<Object> v=new Vector<Object>();
				for(int i=1;i<=columnsn;i++){
					v.add(rset.getObject(i));
				}
				tmodel.addRow(v);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		table.setModel(tmodel);
		table.setAutoResizeMode(JTable.AUTO_RESIZE_LAST_COLUMN);
		table.setAutoscrolls(true);
		/*
		tmodel.addTableModelListener(new TableModelListener(){
			public void tableChanged(TableModelEvent tme) {
				System.out.println(tmodel.getColumnName(tme.getColumn()));
				System.out.println(table.getValueAt(tme.getLastRow(),tme.getColumn()));
				System.out.println(table.getValueAt(tme.getLastRow(),0));
				System.out.println(tmodel.getColumnName(0));
				String sql="update ??  set ??=? where ??=?";
				String[] sname=new String[3];
				sname[0]=gettablename();
				sname[1]=tmodel.getColumnName(tme.getColumn());
				sname[2]=tmodel.getColumnName(0);
				//sname[1]=
				sql=createsql(sql,sname);
				
				String[] svar=new String[2];
				svar[0]=(String) table.getValueAt(tme.getLastRow(),tme.getColumn());
				svar[1]=(String) table.getValueAt(tme.getLastRow(),0);
				System.out.println(sql);
				
				int n=update(sql,svar);
				System.out.println(n);
				//updata("update ")
				if(n==0){
					error("更新出错!");
					table.setValueAt("",tme.getLastRow(),tme.getColumn());
					
				}
			}
		});
		*/
		return table;
		
	}
	public JScrollPane makescrollpane(ResultSet rset){
		JScrollPane sc=null;
		sc=new JScrollPane();
		sc.setViewportView(maketable(rset));
		
		return sc;
	}
	public String createsql(String sql,Object[]name){
		StringBuffer bufsql=new StringBuffer(sql);
		int n=0;
		int len=bufsql.length();
		for(int i=0;i<bufsql.length();i++){
			if(bufsql.charAt(i)=='?'){
				i++;
				if(i<bufsql.length()&&bufsql.charAt(i)=='?'){
					bufsql.replace(i-1,i+1,String.valueOf(name[n]));
					i--;
					n++;
					if(n==name.length){
						break;
					}
				}
			}
			if(i>=bufsql.length()-1){
				System.out.println("缺少??");
				
			}
		}
		return new String(bufsql);
	}
	public String createsql(String sql,Object name){
		String[] names=new String[1];
		names[0]=(String)name;
		return createsql(sql,names);
	}
	public static void main(String[] args) {
		databasemanager dm=new databasemanager();
		dm.showdata(dm.select("select * from 学生信息表 where 姓名=? ","云天河"));
		
	}
	public String gettablename(){
		return tablename;
	}
	public void settablename(String tablename){
		this.tablename=tablename;
	}
	public void error(String ss){
		JOptionPane.showMessageDialog(null,ss,"错误",JOptionPane.ERROR_MESSAGE);
	}
}