Java操作MySQL应用实例

本案例树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;@Overridepublic Connection getConn() throws Exception {// TODO Auto-generated method stubtry {Class.forName(DBDRIVER);this.conn=DriverManager.getConnection(DBURL, DBUSER, DBPASS);} catch (Exception e) {// TODO Auto-generated catch blockthrow 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 blockthrow 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 exceptione.printStackTrace();}}if(ps!=null){try {ps.close();} catch (SQLException e) {// TODO: handle exceptione.printStackTrace();}}if(rs!=null){try {rs.close();} catch (SQLException e) {// TODO: handle exceptione.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 blocke.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

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

结果2

userid username  5 张飞  6 黄忠