java 数据库操作

系统管理员 2022-08-27 09:57 215阅读 0赞

数据库操作

1.使用Class.ForName提示找不到该类  
解决方法：首先安装驱动，然后将包msbase.jar,mssqlserver.jar,msutil.jar添加到库(lib)中。

2.简单应用  
private void getResultSet() throws SQLException \{  
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";  
Connection conn;  
Statement stmt;  
ResultSet rs;  
try \{  
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");  
\} catch (ClassNotFoundException ex) \{  
Logger.getLogger(DataBaseFrame.class.getName()).log(Level.SEVERE, null, ex);  
\}  
conn = DriverManager.getConnection(url, "sa", "2468");  
stmt = conn.createStatement();  
rs = stmt.executeQuery("select d1,d2,d3 from head");  
  
if(rs.next()) \{  
JOptionPane.showMessageDialog(this, rs.getString(1), "提示", JOptionPane.ERROR\_MESSAGE);  
\}  
conn.close();  
stmt.close();  
\}

3.公用数据库操作类  
import java.sql.\*;  
import javax.swing.\*;  
import java.util.\*;

public class DBPubClass extends JFrame \{

private String\[\]\[\] UserName = null;  
String url = "jdbc:microsoft:sqlserver://CZY-WIN:1433;DatabaseName=testdb";  
private Connection conn;  
private Statement stmt;  
private ResultSet rs;  
/\*-------方法描述--------------\*/  
/\*初始化得到数据库连接Connection\*/  
/\*----------------------------\*/

public DBPubClass() \{  
try \{  
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");  
conn = DriverManager.getConnection(url, "sa", "2468");  
//stmt = conn.createStatement(ResultSet.TYPE\_SCROLL\_INSENSITIVE, ResultSet.CONCUR\_READ\_ONLY);

\} catch (ClassNotFoundException e) \{  
System.err.println("fail to load JDBC/ODBC driver");  
e.printStackTrace();  
System.exit(1);  
\} catch (SQLException ee) \{  
System.err.println("fail to connect");  
ee.printStackTrace();  
\}

/\*-------方法描述--------------\*/  
/\*得到表中的数据,返回ResultSet\*/  
/\*----------------------------\*/  
public ResultSet getDataSet(String SelectSql) throws SQLException \{  
stmt = conn.createStatement();  
rs = stmt.executeQuery(SelectSql);  
/\*这里的conn和stmt不能关闭,否则ResultSet将提示“关闭状态”\*/  
/\*conn.close();  
stmt.close();\*/  
return rs;  
\}

/\*-------方法描述--------------\*/  
/\*得到表中的列名（字段名）,返回Vector类型\*/  
/\*----------------------------\*/  
Vector getColumn(ResultSet rs) throws SQLException \{  
Vector columns = new Vector();  
ResultSetMetaData rsmd = rs.getMetaData();  
for (int i = 1; i <= rsmd.getColumnCount(); ++i) \{  
columns.addElement(rsmd.getColumnName(i));  
\}  
return columns;  
\}

/\*-------方法描述--------------\*/  
/\*得到表中的数据,返回Vector类型\*/  
/\*----------------------------\*/  
Vector getRows(ResultSet rs) throws SQLException \{  
Vector rows = new Vector();  
boolean moreRecords = rs.next();  
if (!moreRecords) \{  
JOptionPane.showMessageDialog(this, "ResultSet has no records");  
\}  
ResultSetMetaData rsmd = rs.getMetaData();

do \{  
rows.addElement(getNextRow(rs, rsmd));  
\} while (rs.next());  
return rows;  
\}

private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException \{  
Vector currentRow = new Vector();  
for (int i = 1; i <= rsmd.getColumnCount(); ++i) \{  
switch (rsmd.getColumnType(i)) \{  
case Types.VARCHAR:  
currentRow.addElement(rs.getString(i));  
break;  
case Types.INTEGER:  
currentRow.addElement(new Long(rs.getLong(i)));  
break;  
default:  
System.out.println("Type was: " + rsmd.getColumnTypeName(i));  
\}  
\}  
return currentRow;  
\}  
/\*-------方法描述--------------\*/  
/\*关闭数据库连接和statement\*/  
/\*----------------------------\*/

void closed() throws SQLException \{  
conn.close();  
stmt.close();  
\}  
/\*-------方法描述--------------\*/  
/\*以二维数组的方式得到表中的数据,error!!!\*/  
/\*----------------------------\*/

String\[\]\[\] GetUserName(ResultSet rs) \{  
int i = 0;  
try \{  
ResultSetMetaData rsmd = rs.getMetaData();  
while (rs.next()) \{  
for (int j = 0; j < rsmd.getColumnCount() - 1; j++) \{  
UserName\[i\]\[j\] = rs.getString(j + 1);  
\}  
i++;  
\}  
\} catch (SQLException ee) \{  
ee.printStackTrace();  
\}  
return UserName;  
\}

/\*-------方法描述--------------\*/  
/\*更新数据表中的数据\*/  
/\*插入数据表中的数据\*/  
/\*删除数据表中的数据\*/  
/\*----------------------------\*/  
public void Excute(String UpdateSql) \{  
try \{  
stmt = conn.createStatement();  
stmt.executeUpdate(UpdateSql);  
stmt.close();  
conn.close();  
\} catch (SQLException ee) \{  
ee.printStackTrace();  
\}  
\}  
\}

使用：  
DBPubClass d=new DBPubClass();  
d.url="jdbc:microsoft:sqlserver://CZY-WIN:1433;DatabaseName=testdb";  
ResultSet rs=d.getDataSet("select d1,d2,d3 from head");  
  
if (rs.next())\{  
JOptionPane.showMessageDialog(this, rs.getString(1), "提示", JOptionPane.ERROR\_MESSAGE);  
\}  
d.closed();