本案例树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 黄忠