Program | create | access | data | database
What is a database?
A database is a series of information tables stored in a file structure that enables you to access these tables, select columns in a table, sort tables, and select rows based on various criteria. A database usually has multiple indexes associated with many of these tables, so we can access them as quickly as possible.
Take an employee record as an example, you can imagine a table that contains the employee's name, address, payroll, tax deduction, and allowances. Let's consider how the content might be organized together. You can imagine a table that contains employee names, addresses, and phone numbers. Other information you want to keep may include salary, salary range, last raise, next raise, employee performance evaluation, etc.
Should the content be saved in a table? This is almost certainly not the way it should be. There may be no difference in the salary range for different categories of employees, so you can store the employee type only in the Employee Records table, and the salary range in another table, which is associated with the table by type number. Consider the following:
The data in the Salarytype column refers to the second table. We can imagine a number of such tables, such as the tax value for the living city and each city, the amount of health plan deduction, and so on. Each table has a primary key column, such as the leftmost column in the two tables above, and several data columns. Building tables in a database is both an art and a science. The structure of these tables is indicated by their paradigms. We usually say that the table belongs to 1NF, 2NF or 3NF.
First paradigm: Each table element in a table should have only one value (never an array). (1NF)
Second normal form: satisfies 1NF, and each non-key key column depends entirely on the primary key column. This means that the primary key and the remaining table elements in the row are 1 to 1 relationships. (2NF)
Third normal form: satisfies 2NF, and all Non-key key columns are independent of each other. The values contained in any one data column cannot be computed from the data in the other columns. (3NF) Almost all databases are now created based on the third normal form (3NF). This means that there are usually quite a few tables, with relatively few columns of information in each table.
Getting data from the database
Let's say we want to generate a table that contains employees and their salary ranges, and we'll use this table in an exercise we design. This table does not exist directly in the database, but it can be built by issuing a query to the database. We would like to get a table as shown below:
Name Min Max Tully $30,000.00 $45,000.00 Johnson $30,000.00 $45,000.00 Wolff $45,000.00 $60,000.00 Adams $45,000.00 $ 0,000.00 Smyth $60,000.00 $75,000.00
We found that the query form for these tables is shown below
SELECT Distinctrow employees.name, Salaryranges.min, Salaryranges.max from Employees INNER JOIN salaryranges on Employees . Salarykey = Salaryranges.salarykey ORDER by salaryranges.min;
This language is called a structured Query language, SQL, and it is a language that almost all databases are currently available in. The SQL-92 standard is considered to be a basic standard and has been updated several times.
Types of databases
Databases on your PC, such as DBase, Borland Paradox, Microsoft Access, and FoxBase.
Database servers: IBM DB/2, Microsoft SQL Server, Oracle, Sybase, SQLBase, and XDB.
All of these database products support a variety of relatively similar SQL dialects, so all databases initially look like interchangeable. Each database has different performance characteristics, and each has a different user interface and programming interface.
Odbc
It would be nice if we could somehow write code that doesn't depend on a particular vendor's database and can get the same results from those databases without changing its calling program. If we can write some encapsulation for all of these databases and have a similar programming interface, this feature of database programming that is independent of the vendor will be easy to implement.
What is JDBC?
JDBC is an object-oriented encapsulation and redesign of the ODBC API that is easy to learn and use, and it really enables you to write code that does not rely on vendors to query and manipulate the database. Although it is object-oriented, like all Java APIs, it is not a very high level set of objects. Most vendors, with the exception of Microsoft, have adopted JDBC and provided JDBC drivers for their databases, which makes it easy to really write code that is almost completely independent of the database. In addition, JavaSoft and INTERSOLV have developed a product called JDBC-ODBC Bridge, which enables you to connect to a database that does not have a direct JDBC driver. All databases that support JDBC must at least support the SQL-92 standard. This has largely achieved portability across databases and platforms.
Installing and using JDBC
The JDBC classes are grouped into the java.sql package and are installed automatically when Java JDK 1.4 is installed. However, if you want to use the Jdbc-odbc bridge. The JDBC-ODBC driver can be easily found and downloaded from the Sun's Java Web site (http://java.sun.com/). After you have expanded and installed the driver, you must perform the following steps:
Will \jdbc-odbc\classes; The path is added to your PATH environment variable.
Will \jdbc-odbc\classes; The path is added to your CLASSPATH environment variable.
Types of JDBC drivers
There are actually four ways Java programs can connect to a database:
1. Jdbc-odbc Bridge and ODBC driver--In this way, this is a local solution because the ODBC driver and bridge code must appear on each user's machine. Basically, this is a temporary solution.
2. Native code and Java driver-it replaces ODBC and Jdbc-odbc Bridge with another local solution (Java callable native code on the platform).
3. The pure Java driver for the JDBC network-the JDBC translated by the Java driver forms the standalone protocol that is delivered to the server. The server can then connect to any number of databases. This approach allows you to invoke the server from the client applet and return the results to your applet. In this case, the middleware software provider can provide the server.
4. Native Protocol Java driver-the Java driver is directly converted to the protocol of the database and invoked. This method can also be used over a network and can display results in a Web browser Applet. In this case, each database vendor will provide the driver.
If you want to write code to handle a PC client database, such as DBase, Foxbase, or Access, you might use the first method and have all the code on the user's machine. Larger client-server database products, such as IBM's DB2, have provided a level 3rd driver.
Two-layer model and three-layer model
When the database and query its application on the same machine, and there is no server code intervention, we will generate the program called a two-tier model. One layer is an application, and the other is a database. This is usually the case in the Jdbc-odbc Bridge system.
When an application or applet calls the server and the server calls the database, we call it a three-tier model. This is usually the case when you call a program called a server.
Write JDBC code Access database
Registering your database with ODBC
Connecting to a database
All database-related objects and methods are in the java.sql package, so you must add "import java.sql.*" in programs that use JDBC. JDBC to connect to an ODBC database, you must first load the JDBC-ODBC bridge driver
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
The statement loads the driver and creates an instance of the class. Then, to connect to a particular database, you must create an instance of the Connect class and connect to the database using URL syntax.
String url = "Jdbc:odbc:Northwind";
Connection con = drivermanager.getconnection (URL);
Note that the database name you are using is the data source name that you entered in the ODBC Setup Panel.
URL syntax can vary greatly depending on the type of database.
Jdbc:subprotocol:subname
The first set of characters represents the connection agreement and is always JDBC. There may also be a child protocol, where the child protocol is specified as ODBC. It provides a kind of database connectivity mechanism. If you are connecting to a database server on another machine, you may also want to specify the machine and a subdirectory: Jdbc:bark//doggie/elliott
Finally, you may want to specify a user name and password as part of the connection string:
Jdbc:bark//doggie/elliot; Uid=gooddog; Pwd=woof
Accessing the MSSQL Server method: (Driver required: Msutil.jar,msbase.jar,mssqlserver.jar)
Using the database classes we developed, we use the following methods:
DBObject DbO = new DBObject (new Sqlserverconnectionfactory ("localhost", 1433, "demo", "sa", "")); Connection con = dbo.getconnection (); Class code (not including connection factory implementation) package skydev.modules.data;
Public final class Sqlserverconnectionfactory extends ConnectionFactory {private final String dbdriver = "COM.MI Crosoft.jdbc.sqlserver.SQLServerDriver "; Private String host; private int port; Private String DatabaseName;
Public Sqlserverconnectionfactory () {super.setdrivername (dbdriver); }
/** * * @param hostname of host database: such as "localhost" * @param port number that the port SQL Server is running, if you use the default value of 1433, pass in a negative numbers * @param databaseName Database name * @param userName username * @param password Password * *
Dbdriver=com.mysql.jdbc.driver url=jdbc:mysql://localhost/demo username= password= maxcon=5 mincon=1 Ao
Accessing the database
Once connected to a database, you can request information such as table names and table column names and content, and you can run SQL statements to query the database or add or modify its contents. The objects that can be used to get information from the database are:
DatabaseMetaData information about the entire database: Table name, index of the table, name and version of the database product, operations supported by the database.
ResultSet information about a table or the results of a query. You must access the rows of data line by line, but you can access the columns in any order.
ResultSetMetaData information about the names and types of columns in ResultSet.
Although each object has a large number of methods that allow you to obtain extremely detailed information about the database elements, there are several main methods in each object that allow you to obtain the most important information about the data. However, if you want to see more information than this, it is recommended that you learn the documentation to get a description of the remaining methods.
ResultSet
The ResultSet object is the most important single object in JDBC. Essentially, it is an abstraction of a table with a general width and an unknown length. Almost all methods and queries return the data as ResultSet. ResultSet contains any number of named columns, you can access these columns by name. It also contains one or more rows, which 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.
Obtaining the number of columns ResultSetMetaData rsmd from metadata; RSMD = Results.getmetadata (); Numcols = Rsmd.getcolumncount ();
When you get a ResultSet, it points exactly where it was before the first row. You can use the next () method to get each of the other rows, and the method returns False when there are no more lines. Because fetching data from a database can result in an error, you must always include the result set processing statement in a try block.
You can get data from ResultSet in many forms, depending on the type of data stored in each column. Alternatively, you can get the contents of the column by column ordinal or column name. Note that the column ordinal starts at 1 instead of 0. Some of the most common methods for ResultSet objects are shown below.
getInt (int); Returns the contents of a column ordinal int as an integer.
GetInt (String); Returns the contents of a column with the name String as an integer.
getfloat (int); Returns the contents of a column with the ordinal int as a float.
GetFloat (String); Returns the contents of a column with the name String as the float number.
getDate (int); Returns the contents of a column ordinal int as a date.
GetDate (String); Returns the contents of a column with the name String as a date.
Next (); Moves the row pointer to the next line. Returns False if there are no remaining rows.
Close (); Closes 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 name of the column with an int in the column ordinal.
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 can provide 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 which SQL methods the database supports. GetCatalogs () Returns a list of information directories in the database. Using the Jdbc-odbc bridge driver, you can obtain 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 that match the table name to Tablenames and that the column name matches the ColumnNames. GetColumns (catalog, schema, Tablenames, columnnames) returns all table column descriptions that match the table name to Tablenames and that the column name matches the ColumnNames. GetURL (); Gets the name of the URL that you are connecting to.
Getdrivername (); Gets the name of the database driver that you are connecting to.
Get information about a table
You can use the DatabaseMetaData gettables () method to get information about the 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 the name of the directory in which to look for the table name. For JDBC-ODBC databases and many other databases, you can set them to null. The directory entries for these databases are actually the absolute path names that it has in the file system.
The database "scenario" to include in the Schema. Many databases do not support scenarios, but for other databases it represents the user name of the database owner. It is generally set to null.
Tablemask a mask to describe the name of the table you want to retrieve. If you want to retrieve all the table names, set them as wildcard characters. 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. A database typically includes many tables for internal processing, and is of little value to you as a user. If it is a null value, you will get all of these tables. If you set it to a set of cell numbers that contain the string "TABLES," you will only get a table that is useful to the user.
A simple JDBC program
We've learned all the basics of JDBC, and now we can write a simple program that opens a database, prints its table name and the contents of a table column, and then executes a 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;
For an example of invoking a SQL Server stored procedure: (Using the Database connection class we developed)
CREATE PROCEDURE [dbo]. [Sp_getstudentbyname] (@name char (10)) As Select * from Students where [name]= @name 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 (), @age int, @id int OUTPUT) As insert into Students ([name],[age]) VALUES (@name, @age) Select @id =@ @IDENTITY Go
int id = pstmt.getint (3); SYSTEM.OUT.PRINTLN (ID); Use an example of a return parameter:
CREATE PROCEDURE [dbo]. [Sp_insertstudent] (@name char (), @age int, @id int OUTPUT) As insert into Students ([name],[age]) VALUES (@name, @age) Select @id =@ @IDENTITY – Test output parameters return 30– Test back to go
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 parameter (output parameter) int id = pstmt.getint (4); Output parameter System.out.println (ret); System.out.println (Ret2); SYSTEM.OUT.PRINTLN (ID); }
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
