Java操作MySQL应用实例

素颜马尾好姑娘i 2022-09-17 12:22 167阅读 0赞

本案例树JDBC做了轻型封装，主要目的是方便用户查询数据库后得到的就是一张表对象 ，此表与数据库中的表有对应关系

# 1.定义接口类，为了方便以后扩展开发 #

package com.zhaochao.dba;
    
    import java.sql.Connection;
    public interface iConn {
    	Connection getConn() throws Exception;
    }

# 2.定义连接MySQL类，实现iConn接口 #

此类主要是为了获得MySQL连接

package com.zhaochao.dba;
    import java.sql.Connection;
    import java.sql.DriverManager;
    
    public class MySQLConn implements iConn {
    	private static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
    	private static final String DBURL = "jdbc:mysql://localhost:3306/zhaochao" ;
    	private static final String DBUSER = "root" ;
    	private static final String DBPASS = "admin";
    	private Connection conn=null;
    	@Override
    	public Connection getConn() throws Exception {
    		// TODO Auto-generated method stub
    		try {
    			Class.forName(DBDRIVER);
    			this.conn=DriverManager.getConnection(DBURL, DBUSER, DBPASS);
    			
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			throw e;
    		}
    		
    		return this.conn;
    	}
    
    }

# 3.定义表头类 #

此类与数据库的表头相对应，主要是获得数据库的表的信息

package com.zhaochao.dba;
    
    public class TableHead {
    //	数据库中表名
    	private String    tableName;
    //	表中列数量
    	private int       tableColumn;
    //	表中列名
    	private String [] tableColumnName;
    	
    	public String getTableName() {
    		return tableName;
    	}
    
    	public void setTableName(String tableName) {
    		this.tableName = tableName;
    	}
    
    	public int getTableColumn() {
    		return tableColumn;
    	}
    
    	public void setTableColumn(int tableColumn) {
    		this.tableColumn = tableColumn;
    	}
    
    	public String getTableColumnName(int i) {
    		return tableColumnName[i];
    	}
    
    	public void setTableColumnName(String tableColumnName,int i) {
    		this.tableColumnName[i-1] = tableColumnName;
    	}
    
    	public TableHead(int count){
    		this.tableColumn=count;
    		this.tableColumnName=new String [count];
    	}
    }

# 4.定义表中字段类 #

此类与数据库中的一个字体相对应

package com.zhaochao.dba;
    
    public class TableContent {
    //	表字段内容
    	private String [] tableContent;
    	
    	public TableContent(int Count){
    		this.tableContent=new String[Count];
    	}
    
    	public String getTableContent(int i) {
    		return tableContent[i];
    	}
    
    	public void setTableContent(String tableContent ,int i) {
    		this.tableContent[i-1] = tableContent;
    	}
    	
    }

# 5.定义表类 #

此类与数据库中的一张表相对应，主要有表头和字段组成

package com.zhaochao.dba;
    
    import java.util.ArrayList;
    import java.util.List;
    
    public class Table {
    //	表中列数量
    	private int coloumnCount;
    //	表头信息
    	private TableHead tablehead;
    //	表个字段
    	private List<TableContent> content;
    //	向表中增加字段
    	public void addContent(TableContent content){
    		this.content.add(content);
    	}
    //	表中字段大小
    	public int  contentSize(){
    		return this.content.size();
    	}
    	
    //	表列数
    	public int getColoumnCount() {
    		return coloumnCount;
    	}
    
    	public void setColoumnCount(int coloumnCount) {
    		this.coloumnCount = coloumnCount;
    	}
    
    	public TableHead getTablehead() {
    		return tablehead;
    	}
    
    	public void setTablehead(TableHead tablehead) {
    		this.tablehead = tablehead;
    	}
    
    	public List<TableContent> getContent() {
    		return content;
    	}
    
    	public void setContent(List<TableContent> content) {
    		this.content = content;
    	}
    
    	public Table(int Count){
    		this.coloumnCount=Count;
    		this.content=new ArrayList<TableContent>();
    	}
    //	得到表中第i个字段
    	public TableContent getTableContent(int i){
    		return this.content.get(i);
    	}
    }

# 6.定义MySQL类 #

此类完成对数据库的具体操作

package com.zhaochao.dba;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ParameterMetaData;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;;
    
    public class MySQL {
    	private Connection conn;
    	private PreparedStatement ps;
    	private ResultSet         rs;
    	private ResultSetMetaData rsData;
    	
    	public MySQL(iConn icon) throws Exception{
    		try {
    			this.conn=icon.getConn();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			throw e;
    		}
    	}
    	
    //	获取表
    	public Table ExecuteSQL(String sql,String [] paramters) throws SQLException{
    		
    		this.ps=this.conn.prepareStatement(sql);
    		if(paramters!=null){
    			for(int i=1;i<=paramters.length;i++){
    				this.ps.setString(i, paramters[i-1]);
    			}
    		}
    		this.rs=this.ps.executeQuery();
    		TableHead tableHead=null;
    		Table     table=null;
    		
    		this.rsData=this.rs.getMetaData();
    		int columnCount=this.rsData.getColumnCount();
    		table=new Table(columnCount);	
    		tableHead=new TableHead(columnCount);
    		tableHead.setTableName(this.rsData.getTableName(1));
    		
    		for(int i=1;i<=this.rsData.getColumnCount();i++){
    			tableHead.setTableColumnName(this.rsData.getColumnName(i), i);
    		}
    //		设置表头
    		table.setTablehead(tableHead);
    			
    		while(this.rs.next()){
    			TableContent content=new TableContent(columnCount);
    				for(int i=1;i<=columnCount;i++){
    					content.setTableContent(this.rs.getString(i), i);
    				}
    //				加入字段
    				table.addContent(content);
    		}	
    		return table;
    	}
    	
    	
    
    	
    	private void close(){
    		if(conn!=null){
    			try {
    				conn.close();
    			} catch (SQLException e) {
    				// TODO: handle exception
    				e.printStackTrace();
    			}
    		}
    		if(ps!=null){
    			try {
    				ps.close();
    			} catch (SQLException e) {
    				// TODO: handle exception
    				e.printStackTrace();
    			}
    		}
    		if(rs!=null){
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				// TODO: handle exception
    				e.printStackTrace();
    			}
    		}
    	}
    	
    	
    
    }

# 7.测试 #

package com.zhaochao.dba;
    
    public class Main {
    	public static void main(String [] rags){
    		
    //		String sql="select id,username from user where id>? and id<? limit 2,3 ";
    		String sql="select * from user";
    		String [] paramters=null;
    //		String [] paramters={"2","7"};
    		
    //		mysql> select * from user;
    //		+------+----------+------------+
    //		| id   | username | userpasswd |
    //		+------+----------+------------+
    //		|    1 | 赵超        | zhaochao   |
    //		|    2 | 赵云         | zhaoyun    |
    //		|    3 | 马超        | machao     |
    //		|    4 | 关羽         | guanyu     |
    //		|    5 | 张飞        | zhangfei   |
    //		|    6 | 黄忠         | huangzhong |
    //		|    7 | 吕布         | lvbu       |
    //		|    8 | 刘备         | luibei     |
    //		+------+----------+------------+
    //		8 rows in set (0.00 sec)
    //	   从id为 3 4 5 6 中第2个开始取3个 所以为 5 6   limit编号从0开始 
    		
    		iConn iconn=new MySQLConn();
    		MySQL mysql=null;
    		Table table=null;
    		try {
    			 mysql=new MySQL(iconn);
    			 table=mysql.ExecuteSQL(sql, paramters);
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    //		输出表名
    		System.out.println(table.getTablehead().getTableName());
    //		输出表头
    		for(int i=0;i<table.getColoumnCount();i++){
    			System.out.print(table.getTablehead().getTableColumnName(i)+" ");
    		}
    //		输出表中内容
    		for(int k=0;k<table.contentSize();k++){
    			System.out.println(" ");
    			for(int j=0;j<table.getColoumnCount();j++){
    				System.out.print(table.getContent().get(k).getTableContent(j)+" ");
    			}
    		}
    		
    	}
    
    }

# 8.结果 #

结果1

user
    id username userpasswd  
    1 赵超 zhaochao  
    2 赵云 zhaoyun  
    3 马超 machao  
    4 关羽 guanyu  
    5 张飞 zhangfei  
    6 黄忠 huangzhong  
    7 吕布 lvbu  
    8 刘备 luibei

结果2

user
    id username  
    5 张飞  
    6 黄忠