本案例树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 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 getContent() { return content; } public void setContent(List content) { this.content = content; } public Table(int Count){ this.coloumnCount=Count; this.content=new ArrayList (); }// 得到表中第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 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
8.结果
结果1
userid username userpasswd 1 赵超 zhaochao 2 赵云 zhaoyun 3 马超 machao 4 关羽 guanyu 5 张飞 zhangfei 6 黄忠 huangzhong 7 吕布 lvbu 8 刘备 luibei结果2
userid username 5 张飞 6 黄忠