java 操作mysql实例_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;

@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 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

System.out.print(table.getTablehead().getTableColumnName(i)+" ");

}

//输出表中内容

for(int k=0;k

System.out.println(" ");

for(int j=0;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 黄忠