ResultSet related ResultSetMetaData Details

ResultSet related ResultSetMetaData Details

DatabaseMetaData information about the entire database: The table name, the index of the table, the name and version of the database product, and the operations supported by the database.

ResultSet information about a table or the results of a query. You must access rows of data row by row, but you can access the columns in any order.

ResultSetMetaData information about the names and types of columns in the ResultSet.

Although each object has a number of methods that let you get very detailed information about the database elements, there are several primary methods in each object that allow you to get the most important information about your data. However, if you want to see more information than here, it is recommended that you study the documentation for instructions on the remaining methods.

ResultSet

The ResultSet object is the most important single object in JDBC. Essentially, it is an abstraction of a table of a general width and an unknown length. Almost all methods and queries return data as ResultSet. The ResultSet contains any number of named columns that you can access by name. It also contains one or more rows that you can access sequentially from top to bottom. Before you use ResultSet, you must query how many columns it contains. This information is stored in the ResultSetMetaData object.

Get the number of columns from the metadata
ResultSetMetaData RSMD;
RSMD = Results.getmetadata ();
Numcols = Rsmd.getcolumncount ();

When you get a ResultSet, it exactly points to the position before the first line. You can use the next () method to get every other row, and the method returns False when there are no more rows. Because fetching data from the database can cause errors, you must always include the result set processing statement in a try block.

You can get the data in ResultSet in many forms, depending on the type of data stored in each column. In addition, you can get the contents of a column by column ordinal or column name. Note that the column ordinal starts at 1 instead of starting at 0. Some of the most common methods of ResultSet objects are shown below.

getInt (int); Returns the contents of a column that is ordinal int as an integer.

GetInt (String); Returns the contents of a column named String as an integer.

getfloat (int); Returns the contents of a column with the ordinal number int as a float type.

GetFloat (String); Returns the contents of a column named String as a float type number.

getDate (int); Returns the contents of a column that is ordinal int as a date.

GetDate (String); Returns the contents of a column named String as a date.

Next (); Moves the row pointer to the next row. Returns False if there are no remaining rows.

Close (); Close the result set.

GetMetaData (); Returns the ResultSetMetaData object.

ResultSetMetaData

You use the GetMetaData () method to get the ResultSetMetaData object from the ResultSet. You can use this object to get the number and type of columns and the name of each column.

getColumnCount (); Returns the number of columns in the ResultSet.
getColumnName (int); Returns the column name of the column ordinal of Int.
Getcolumnlabel (int); Returns the label implied by this column.
iscurrency (int); Returns TRUE if this column contains a number with a currency unit.
isreadonly (int); Returns TRUE if this column is read-only.
isautoincrement (int); Returns TRUE if this column is automatically incremented. This type of column is usually a key and is always read-only.
Getcolumntype (int); Returns the SQL data type for this column. These data types include

BIGINT
BINARY
BIT
CHAR
DATE
DECIMAL
DOUBLE
FLOAT
INTEGER
LongVarBinary
LongVarChar
Null
NUMERIC
Other
REAL
SMALLINT
Time
TIMESTAMP
TINYINT
VARBINARY
VARCHAR

DatabaseMetaData

The DatabaseMetaData object provides you with information about the entire database. You use it primarily to get the names of the tables in the database and the names of the columns in the table. Because different databases support different SQL variants, there are several ways to query the database for which SQL methods are supported.

GetCatalogs () Returns a list of information directories in the database. Using the JDBC-ODBC Bridge driver, you can get a list of databases registered with ODBC. This is rarely used for JDBC-ODBC databases.

Gettables (catalog, schema,tablenames, ColumnNames) returns a description of all tables where the table name matches tablenames and the column name corresponds to ColumnNames.

GetColumns (catalog, schema,tablenames, ColumnNames) returns all table column descriptions that match the table name to Tablenames and that the column name corresponds to ColumnNames.

GetURL (); Get the name of the URL you are connecting to.

Getdrivername (); Get the name of the database driver that you are connected to.

Get information about a table

You can use the DatabaseMetaData gettables () method to get information about tables in the database. This method has the following 4 String parameters:

Results =dma.gettables (catalog, schema, Tablemask, types[]);

The meaning of the parameters is:

Catalog name in which to look for table names. For JDBC-ODBC databases and many other databases, you can set them to null. The directory entries for these databases are actually their absolute path names in the file system.

Schema to include in the database "schema". Many databases do not support scenarios, and for other databases, it represents the user name of the database owner. It is generally set to null.

Tablemask a mask that describes the name of the table you want to retrieve. If you want to retrieve all the table names, set it as a wildcard%. Note that the wildcard character in SQL is the% symbol, not the general PC user's * symbol.

Types[] This is a String array that describes the type of table you want to retrieve. The database typically includes many tables for internal processing, and it is of little value to you as a user. If it is a null value, you will get all these tables. If you set it to a cell that contains the string "TABLES", you will only get tables that are useful to the user.

A simple JDBC program

We have learned all the basic functions of JDBC, and now we can write a simple program that opens the database, prints its table name and the contents of a table column, and then executes the query against that database. The program looks like this:

Package Skydevkit;
Import java.sql.*;
public class Jdbcodbc_test {
ResultSet results;
ResultSetMetaData RSMD;
DatabaseMetaData DMA;
Connection con;

Public Jdbcodbc_test () throws SQLException {
String url = "Jdbc:odbc:Northwind";
try {
Load the Jdbc-odbc bridge driver
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
con = drivermanager.getconnection (URL);//Connect database
DMA = Con.getmetadata ();//Get the metadata for the database
System.out.println ("Connected to:" + dma.geturl ());
System.out.println ("Driver" + dma.getdrivername ());
} catch (Exception e) {
System.out.println (e);
}
try {
Statement stmt = Con.createstatement ();
Results = Stmt.executequery ("SELECT * from Customer;");
ResultSetMetaData resultmetadata = Results.getmetadata ();
int cols = Resultmetadata.getcolumncount ();
String resultrow = "";
for (int i = 1; i < cols; i++) {
Resultrow + = Resultmetadata.getcolumnname (i) + ";";
}
System.out.println (Resultrow);
while (Results.next ()) {
Resultrow = "";
for (int i = 1; i < cols; i++) {
try {
Resultrow + = results.getstring (i) + ";";
} catch (NullPointerException e) {
System.out.println (E.getmessage ());
}
}
System.out.println (Resultrow);
}
} catch (Exception e) {
SYSTEM.OUT.PRINTLN ("Query exception");
} finally {
Results.close ();
}
}
}

Supplemental Premium Content

An example of invoking SQL Server stored procedures: (Using the Database connection class we developed)

CREATE PROCEDURE [dbo]. [Sp_getstudentbyname] (@name char (10))
As
Select * from Students where [name][email protected]
GO

DBObject DbO = new DBObject (new Sqlserverconnectionfactory ("localhost",
1433, "demo", "sa", ""));
Connection con = dbo.getconnection ();
CallableStatement pstmt = null;
System.out.println ("TestDB1 () ..."););
/* Try {
pstmt = Con.preparecall ("{Call Sp_getstudentbyid (?)}");
Pstmt.setint (1, 1);
}*/
try {
pstmt = Con.preparecall ("{Call Sp_getstudentbyname (?)}"); Note how parameters are passed
Pstmt.setstring (1, "Tom");
}
......

Use output parameters:

CREATE PROCEDURE [dbo]. [Sp_insertstudent] (@name char (TEN), @age int, @id int OUTPUT) As
INSERT into Students ([name],[age]) VALUES (@name, @age)
Select @[email Protected] @IDENTITY
GO

try {
pstmt = Con.preparecall ("{Call Sp_insertstudent (?,?,?)}");
Pstmt.setstring (1, "Zengqingsong");
Pstmt.setint (2, 22);

Pstmt.registeroutparameter (3, Types.integer);
Pstmt.executeupdate ();

int id = pstmt.getint (3);
SYSTEM.OUT.PRINTLN (ID);
}

Examples of using return parameters:

CREATE PROCEDURE [dbo]. [Sp_insertstudent] (@name char (TEN), @age int, @id int OUTPUT) As
INSERT into Students ([name],[age]) VALUES (@name, @age)
Select @[email Protected] @IDENTITY – test output parameters
Return 30– Test returns 30
GO

try {
pstmt = Con.preparecall ("{=call sp_insertstudent (?,?,?)}");
Pstmt.setstring (2, "Zengqingsong");
Pstmt.setint (3, 22);

Pstmt.registeroutparameter (4, Types.integer);
Pstmt.registeroutparameter (1, Types.integer);
int ret = Pstmt.executeupdate (); Number of rows affected by execution

int Ret2 = Pstmt.getint (1); return parameters (output parameters)
int id = pstmt.getint (4); Output parameters
SYSTEM.OUT.PRINTLN (ret);
System.out.println (Ret2);
SYSTEM.OUT.PRINTLN (ID);

ResultSet related ResultSetMetaData Details