最近一直在做java數(shù)據(jù)庫操作的applicaitons,今天抽時間整理了一下，有些比較粗糙，實在是太忙了，所以整理的有點倉促,一些注釋也沒有來的及加上，有問題請和我聯(lián)系，我會不斷改進(jìn)。愿與各位交流，共同提高。轉(zhuǎn)載請注明作者信息以及出處。謝謝。mail:suntao2000st@ustc.edu
//DbOperation Source Code:
/**
* Title:Database basic Operation
* Description:This program intends to deal with basic database operation includes
* querying,updating,inserting and creating table,also user can get table
* column names and existed tables in specific database.
* Copyright:Copyright (c) 2005
* @author:Tao Sun (<a href="mailto:suntao2000st@ustc.edu">Tao Sun</a>)
* @version:1.0
*/
import java.sql.*;
public class DbOperation {
private String databaseName;
private String operationException;
private String password;
private String userName;
private int columnCount;
/**
@roseuid 429FF7C10280
*/
public DbOperation() {
databaseName = new String();
operationException = new String();
password = new String();
userName = new String();
}
/**
@param password
@roseuid 429FAC3002CE
*/
public void setPassword(String password) {
this.password = password;
}
/**
@param userName
@roseuid 429FAC3C00EA
*/
public void setUserName(String userName) {
this.userName = userName;
}
/**
@param databaseName
@roseuid 429FF3BC0138
*/
public void setDatabaseName(String databaseName) {
this.databaseName = databaseName;
}
/**
@return java.lang.String
@roseuid 429FF3D901F4
*/
public String getOperationException() {
return operationException;
}
/**
@return int
*/
public int getTableColumnCount() {
return columnCount;
}
/**
@return java.lang.String[]
@roseuid 429FAB1503B9
*/
public String[] queryDbExistedTableNames() {
String[] dbExistedTableNames = null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=" +
databaseName;
Connection con = DriverManager.getConnection(url, userName, password);
Statement passt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
DatabaseMetaData databaseMetaData = con.getMetaData();
String[] types = new String[1];
types[0] = "TABLE";
//return tables that are available for users
ResultSet tableResult = databaseMetaData.getTables(null, null, "%",
types);
int numCols = 0;
while (tableResult.next()) {
++numCols;
}
dbExistedTableNames = new String[numCols];
tableResult.first();
int ii = 0;
do {
dbExistedTableNames[ii] = tableResult.getString("TABLE_NAME");
++ii;
}
while (tableResult.next());
tableResult.close();
con.close();
operationException = new String("Get db existed tablename successfully!");
}
catch (SQLException ex) {
operationException = new String(ex.toString());
}
catch (Exception ex) {
operationException = new String(ex.toString());
}
return dbExistedTableNames;
}
/**
@param tableName
@return java.lang.String[][]
@roseuid 429FAB460167
*/
public String[][] queryTableData(String tableName) {
String[][] tableData = null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=" +
databaseName;
String pas = "select * from " + tableName;
Connection conn = DriverManager.getConnection(url, userName, password);
Statement passt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet resultset = passt.executeQuery(pas); //return result set
ResultSetMetaData rsmd = resultset.getMetaData();
columnCount = rsmd.getColumnCount();
int row = 0;
while (resultset.next()) {
row++;
}
tableData = new String[row][columnCount];
resultset.first();
int j = 0;
do {
for (int i = 1; i <= columnCount; i++) {
tableData[j][i - 1] = resultset.getString(i);
}
j++;
}
while (resultset.next());
passt.close();
conn.close();
operationException = new String("get data success!");
}
catch (SQLException ex) {
operationException = new String(ex.toString());
}
catch (Exception e) {
operationException = new String(e.toString());
}
return tableData;
}
/**
@param tableName
@return java.lang.String[]
@roseuid 429FAB5301E4
*/
public String[] queryTableColumnNames(String tableName) {
String[] tableColumnNames = null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=" +
databaseName;
String pas = "select * from " + tableName;
Connection conn = DriverManager.getConnection(url, userName, password);
//execute the sql statement
Statement passt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet resultset = passt.executeQuery(pas); //return result set
ResultSetMetaData rsmd = resultset.getMetaData();
int columnCount = rsmd.getColumnCount();
tableColumnNames = new String[columnCount];
for (int i = 1; i <= columnCount; i++) {
tableColumnNames[i - 1] = rsmd.getColumnName(i);
}
passt.close();
conn.close();
operationException = new String(
"Get " + tableName + "column name successfully!");
}
catch (SQLException ex) {
operationException = new String(ex.toString());
}
catch (Exception e) {
operationException = new String(e.toString());
}
return tableColumnNames;
}
/**
@param tableName
@param columnNames using default types char(100)
@throws java.sql.SQLException
@roseuid 429FAB930186
*/
public void createTable(String tableName, String[] columnNames) {
String columnNameString = new String();
for (int i = 0; i < columnNames.length; i++) {
if (i == columnNames.length - 1) {
columnNameString += columnNames[i] + " char(100)";
}
else {
columnNameString += columnNames[i] + " char(100),";
}
}
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=" +
databaseName;
String pas = "CREATE TABLE " + tableName + "(" + columnNameString + ");";
Connection conn = DriverManager.getConnection(url, userName, password);
//execute the sql statement
Statement passt = conn.createStatement();
passt.execute(pas); //
//close the connection and data workspace
passt.close();
conn.close();
operationException = new String("Create table:" + tableName +
"successfully!");
}
catch (SQLException ex) {
operationException = new String(ex.toString());
}
catch (Exception e) {
operationException = new String(e.toString());
}
}
//delete data from specific table
public void delData(String tableName)
{
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName="+databaseName;
String pas = "delete from "+tableName+"where x=45 and z=45";
Connection conn = DriverManager.getConnection(url, user, password);
//execute the sql statement
Statement passt = conn.createStatement();
passt.executeUpdate(pas); //return result set
passt.close();
conn.close();
System.out.println("delete data success!") ;
}
catch (SQLException ex) {
System.out.println(ex.toString());
}
catch (Exception e) {
System.out.println(e.toString());
}
}
/**
@param tableName
@param insertColumnNames
@param insertValues
@throws java.sql.SQLException
@roseuid 429FF45402EE
*/
public void insertTableValues(String tableName, String[] insertColumnNames,
String[] insertValues) {
if (insertColumnNames.length > insertValues.length) {
System.out.println("Not enough values for columns!");
}
else if (insertColumnNames.length < insertValues.length) {
System.out.println("Not enough columns to receive values!");
}
else {
String columnString = new String();
String valueString = new String();
for (int i = 0; i < insertColumnNames.length; i++) {
if (i == insertColumnNames.length - 1) {
columnString += insertColumnNames[i];
valueString += "'" + insertValues[i] + "'";
}
columnString += insertColumnNames[i] + ",";
valueString += "'" + insertValues[i] + "',";
}
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").
newInstance();
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=" +
databaseName;
String pas = "insert into userselect(" + columnString + ")values(" +
valueString + ");";
Connection conn = DriverManager.getConnection(url, userName, password);
//execute the sql statement
Statement passt = conn.createStatement();
passt.executeUpdate(pas); //
//close the connection and data workspace
passt.close();
conn.close();
operationException = new String("Insert table values successfully!");
}
catch (SQLException ex) {
operationException = new String(ex.toString());
}
catch (Exception e) {
operationException = new String(e.toString());
}
}
}
}
//The Test Code:
public class Application {
DbOperation dbo = new DbOperation();
String[] bdExistedTabelNames = null;
String[] createColumnNames;
String[] tableColumnNames = null;
String[][] tableData = null;
public Application() {
createColumnNames = new String[] {
"a", "b", "c", "d"};
test();
}
public void test() {
dbo.setDatabaseName("pestforecast");
dbo.setUserName("crop");
dbo.setPassword("crop");
/**
* Test to create table
*/
// dbo.createTable("test",createColumnNames);
// System.out.println(dbo.getOperationException());
/**
* Test to get database existed table names
*/
bdExistedTabelNames = dbo.queryDbExistedTableNames();
System.out.println(dbo.getOperationException());
for (int i = 0; i < bdExistedTabelNames.length; i++) {
System.out.println(bdExistedTabelNames[i]);
}
/**
* Test to get specific table column names
*/
tableColumnNames = dbo.queryTableColumnNames("historydata");
System.out.println(dbo.getOperationException());
for (int i = 0; i < tableColumnNames.length; i++) {
System.out.println(tableColumnNames[i]);
}
/**
* Test to get specific table data
*/
tableData = dbo.queryTableData("historydata");
System.out.println(dbo.getOperationException());
for (int i = 0; i < tableData.length; i++) {
for (int j = 0; j < dbo.getTableColumnCount(); j++) {
System.out.print(tableData[i][j] + " ");
}
System.out.println();
}
/**
* Test to insert table values
*/
int n = tableColumnNames.length;
String[] inputValue = new String[n];
for (int i = 0; i < n; i++) {
inputValue[i] = "test" + i;
}
dbo.insertTableValues("historydata", tableColumnNames, inputValue);
System.out.println(dbo.getOperationException());
}
public static void main(String[] args) {
new Application();
}
}