Creating a database access program using JDBC ZENGQINGSONG@sohu.com 2003-12-23 What is a database? The database is a series of information stores stored in some file structure, which allows you to access these tables, select the columns in the table, sort the table and select line according to various standards. Database usually have multiple indexs associated with many columns in these tables, so we can access these tables as quickly as possible. Taking employee record as an example, you can imagine a table containing the employee's name, address, salary, deduction, and allowances. Let us consider these contents may be organized together. You can imagine a table that contains employee names, addresses, and phone numbers. Other information you want to save may include salary, wage, last salary time, next salary-saving time, employee performance assessment and other content. Is these contents should be saved in a table? It is almost certain that it should not be. There may be no difference in the wage range of different categories of employees; this, you can store only employee types in the employee record table, and store the wage range in another table, and associate with this table by the type number. Considering the following: Key Lastname Salarittype Salarytype Min Max1 Adams 2 1 30000 450002 JOHNSON 1 2 45000 600003 Smyth 3 3 60000 750004 TULLY 1 5 WOLFF 2 SALYTYPE column The second table is referenced. We can imagine a table such as a table for storing the tax value of living cities and each city, and the health plan is deducted. Each table has a primary key column (such as the leftmost column above the top of the top) and a number of data columns. The establishment of a table in the database is both an art and a science. The structure of these tables is pointed out by their paradigm. We usually say 1nf, 2nF or 3nf. The first paradigm: Each table in the table should have only one value (never could be an array). (1NF) Second paradigm: satisfy 1NF, and each non-main key column is completely dependent on the primary key column. This means that the main key and the remaining table elements in the row are 1 pair 1. (2NF) Third paradigm: Separate 2NF, and all non-main key columns are independent of each other. The values contained in any of the data columns cannot be calculated from other columns. (3NF) Now, almost all databases are created based on "Third Paradigm (3NF)". This means that there is usually a very much table, and the information columns in each table are relatively small. Getting data from the database assumes that we want to generate a table containing employees and their wage range, which will use this table in one exercise we designed. This form does not exist directly in the database, but can build it by sending a query to the database. We hope to get a table as shown below: Name Min MaxTully $ 30,000.00 $ 45,000.00Johnson $ 30,000.00 $ 45,000.00Wolff $ 45,000.00 $ 60,000.00Adams $ 45,000.00 $ 60,000.00Smyth $ 60,000.00 $ 75,000.00 We found that to obtain inquiry form tables below SELECT DISTINCTROW Employees.Name shown, SalaryRanges.Min, SalaryRanges .Max from Employees Inner Join SalaryRanges on Employees.SalaryKey = SalaryRanges.salaryKeyOrder by SalaryRanges.min; this language is called structured query language, SQL, and it is a language that is almost currently all databases. SQL-92 standards are considered to be a basic standard and have been updated multiple times. Databases of the database, such as DBASE, Borland Paradox, Microsoft Access, and FoxBase.
Database Server: 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 seem to be interchangeable. Each database has different performance characteristics, and each has different user interfaces and programming interfaces. ODBC If we can write code that does not depend on the database of specific vendors in some way, and can get the same result from these databases without changing your call programs, which will be a good thing. If we can write some encapsulation only to all of these databases, they have similar programming interfaces, which can be easily implemented for database programming. What is JDBC? JDBC is an object-oriented package and redesign for ODBC API. It is easy to learn and use, and it really enables you to write unrelated vendor's code for querying and manipulating databases. Although it is the same as all Java APIs, it is not a high-level object set. In addition to Microsoft, most vendors use JDBC and provide JDBC drivers for their databases; this makes you It is easy to write almost completely not dependent on the code. In addition, JavaSoft and Intersolv have developed a product called JDBC-ODBC Bridge that allows you to connect to the database of JDBC drivers. All databases that support JDBC must support the SQL-92 standard. This greatly implements transplantability across databases and platforms. The classes installed and using JDBCJDBC are returned to the Java.sql package, which is automatically installed when installing Java JDK 1.4. However, if you want to use a JDBC-ODBC bridge. The JDBC-ODBC driver can easily find and download from the Sun's Java website (http://java.sun.com/). After you expand and install this driver, you must perform the following steps: add / jdbc-odbc / classes; path Add to your PATH environment variable. Add / JDBC-ODBC / CLASSES; the path is added to your ClassPath environment variable. The method of the JDBC driver Java program connection database is actually four: 1. JDBC-ODBC Bridge and ODBC Driver - In this way, this is a local solution because the ODBC driver and bridge code must An appearance in each machine of the user. Fundamentally, this is a temporary solution. 2. Notel code and Java driver - it replaces ODBC and JDBC-ODBC Bridge with another local solution (this platform can be called native code). 3. Pure Java Drivers of JDBC Networks - JDBC translated by Java drivers form an independent protocol for transmitting to the server. The server then connects to any number of databases. This method allows you to call the server from the client applet and return the result to your applet. In this case, the middleware software provider can provide a server. 4. Native Agreement Java Driver - Java driver directly converts the protocol of the database and calls. This method can also be used through the network, and the results can be displayed in the Applet of the web browser. In this case, each database vendor will provide a driver. If you want to write code to handle the PC client database, such as DBASE, FoxBase, or Access, you may use the first method and have all the code on the user machine. A larger client-server database product (such as IBM DB2) has provided a third-level driver.
Two-layer model and three-layer model When the database and query it on the same machine, and there is no intervention of server code, we will be called two-layer model. One layer is an application, and the other layer is a database. This is usually this in the JDBC-ODBC bridge system. When an application or applet calling the server, when the server calls the database, we call it three-layer model. This is usually the case when you call a program called "server". Write a JDBC code Access database Use ODBC to register your database Connection database All objects and methods related to the database are in the Java.SQL package, so "Import Java.sql. *" Must be added in the program with JDBC. JDBC To connect an ODBC database, you must first load the JDBC-ODBC Bridge Driver Class.Forname ("Sun.jdbc.odbc.jdbCodbDriver); the statement loads the driver and creates an instance of the class. Then, to connect to a specific database, you must create an instance of the Connect class and use the URL syntax to connect the database. String URL = "JDBC: ODBC: Northwind"; Connection Con = DriverManager.getConnection (URL); Please note that the database name you use is the "Data Source" name you entered in the ODBC Settings panel. The URL syntax may vary greatly depending on the database type. JDBC: Subprotocol: Subname The first set of characters represents the connection protocol and is always JDBC. There may also be a sub-protocol where the sub protocol is designated as ODBC. It specifies the connectivity mechanism for a class of databases. If you want to connect to a database server on other machines, you may also specify the machine and a subdirectory: JDBC: Bark // Doggie / Elliott Finally, you may want to specify the username and password, as part of the connection string: JDBC: BARK // doggie / elliot; UID = GoodDog; PWD = woof access MSSQL Server method :( driver needs: msutil.jar, msbase.jar, mssqlServer.jar) dBDriver = com.microsoft.jdbc.sqlserver.SQLServerDriverURL = jdbc: microsoft : SQLSERVER: // localhost: 1433; DatabaseName = DEMOUSERNAME = sapassword = maxcon = 10mincon = 1POOLNAME = SkyDev
Using the database class we develop, the method is as follows: DBObject dbo = new dbObject (New SQLServerConnectionFactory ("localhost", 1433, "demo", "sa", ")); connection con = dbo.getConnection (); // Class code (excluding connecting factory implementation) package skydev.modules.data;
public final class SqlServerConnectionFactory extends ConnectionFactory {private final String dbDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; private String host; private int port; private String databaseName; public SqlServerConnectionFactory () {super.setDriverName (dbDriver);}
/ ** * * @Param Host Database The host name of the database: If the "localhost" * @Param port number running, if the default value is 1433, it will be introduced into a negative number * @PARAM DATABASENAME database name * @ param userName * @param password username password * / public SqlServerConnectionFactory (String host, int port, String databaseName, String userName, String password) {this.setHost (host); this.setPort (port); this.setDatabaseName (databaseName) This.SetUsername (username); this.SetPassword (Password);
INIT ();
Private void init () {super.setdrivername (dbdriver); Super.SetURL ("JDBC: Microsoft: SQLServer: //" host.trim () ":" new integer (port) .tostring () "; DatabaseName = " DatabaseName.trim ()); //super.seturl ("jdbc:MICROSOFT: 1433; DatabaseName=Demo");} ...
/ / -------------------------------------------------------------------------------------------- ------------------------------------
How to access mysql: dbdriver = com.mysql.jdbc.driverURL = jdbc: mysql: // localhost / demousername = password = maxcon = 5mincon = 1POOLNAME = zhengmao
Accessing the Database Once connected to the database, you can request a table name and a list of names and contents of the table column, and you can run the SQL statement to query the database or add or modify its content. Objects that can be used to get information from the database: DatabaseMetadata about information about the entire database: table name, table index, name and version of the database product, and the operation of database support. ResultSet About a table of information or results of a query. You must access the data line by line, but you can access columns in any order. ResultSetMetadata information about the names and types of columns in ResultSet. Although each object has a large number of ways to get the extremely detailed information of the database element, there are several major methods to get the most important information for data in each object. However, if you want to see more information than this, it is recommended that you learn a document to get the description of the rest. The ResultSetResultSet object is the most important single object in JDBC. In essence, it is an abstraction of a table for a general width and unknown length. Almost all methods and queries return the data as the Resultset. The ResultSet contains any number of naming columns, and you can access these columns by name. It also contains one or more rows, you can access it from top to top by sequence. You must query how many columns it contains before you use the ResultSet. This information is stored in the ResultSetMetadata object. // Receive the number of columns RSULTSETMETADATA RSMD in metadata; RSMD = Results.getMetadata (); Numcols = rsmd.getColumnCount (); When you get a resultset, it is pointing to the first line. You can use the next () method to get another row, when there is no more line, the method returns false. Since getting data from the database may result in errors, you must always include result set processing statements in a TRY block.
You can get data in the ResultSet in a variety of forms depending on the data type stored in each column. Also, you can get the contents of the column by column or column name. Note that the column number starts from 1, not from 0. Some of the most common methods for the ResultSet object are as follows. GetInt (int); returns the content of the column of the serial number INT as an integer. GetInt (string); returns the contents of the column name String as an integer. Getfloat (int); Returns the content of the column of the serial number INT as a FLOAT type. Getfloat (String); returns the content of the column name String as a FLOAT type. getDate (int); Returns the content of the column of the number INT as the date. Getdate (string); returns the content of the column named String. Next (); move the row pointer to the next line. Returns false if there is no residual line. Close (); Close 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 column and the name of each column. getColumnCount (); Returns the number of columns in the ResultSet. getColumnName (int); Returns the column name INT. GetColumnLabel (int); returns this column containing labels. iScurrency (int); Returns true if this column contains a number with currency units. IsReadonly (int); Returns true if this is read-only. ISAUTOINCREMENT; if this column is automatically incremented, TRUE is returned. This type of column is usually key and is always read-only. getColumnType (int); Returns this column SQL data type. These data types include
BIGINTBINARYBITCHARDATEDECIMALDOUBLEFLOATINTEGERLONGVARBINARYLONGVARCHAR NULLNUMERICOTHERREALSMALLINTTIMETIMESTAMPTINYINTVARBINARYVARCHAR DatabaseMetaDataDatabaseMetaData object provides information for your entire database. You mainly use it to get the name of the table in the database, and the names listed in the table. Since different databases support different SQL variants, there are also multiple methods to query the database to support which SQL methods. getCatalogs () Returns the information directory list in the database. With a JDBC-ODBC Bridge driver, you can get a list of databases registered with ODBC. This is rarely used in the JDBC-ODBC database. GetTables (Catalog, Schema, TableNames, ColumnNames) returns to the table names that match the tablenames and column names and ColumnNames. GetColumns (Catalog, Schema, Tablenames, ColumnNames) Returns all table columns that are grouped with Tablenames and column names with ColumnNames. getURL (); get the URL name you connect. GetDriverName (); get the name of the database driver you connect. Get information about the table You can use the DatabaseMetadata's GetTables () method to get the information on the table in the database. This method has the following four String parameters: results = Dma.gettables (Catalog, Schema, Tablemask, Types []); the meaning of the parameters is: Catalog To find the name of the table name. For a JDBC-ODBC database, many other databases, it can be set to NULL. The directory items of these databases are actually the absolute path name in the file system. Schema's database "scheme" is included. Many databases do not support the scheme, and for other databases, it represents the username of the database owner. Generally set it to NULL. TableMask is a mask to describe the name of the table you want to retrieve. If you want to retrieve all table names, set it to wildcard. Note that wildcards in SQL are% symbols instead of a general PC user * symbol. Types [] This is a String array describing the type of the table you want to retrieve. A database usually includes many tables for internal processing, and it is not worthy of you as a user. If it is a null value, you get all these tables. If you set it with a single element array containing string "Tables", you will only get a table you useful for users. A simple JDBC program we have learned all the basic functions of JDBC, now we can write a simple program that opens the database, prints its table name, and the content of a list, then execute the query on the database. This procedure is as follows: 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 JDBC-ODBC bridge driver Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection ( URL); // Connect the database DMA = con.getMetadata (); // Get the metadata of 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 An example of calling the SQL Server stored procedure: (uses the database connection class we develop) Create Procedure [dbo]. [Sp_getstudentbyname] (@ name char (10)) as select * from students where [name] = @ Namegodbobject DBO = New DBObject ("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 the parameter passes PSTMT.SetString (1," Tom ");} ... Use the output parameters: create procedure [dbo]. [sp_insertstudent] (@ Name Char (10), @ agn @ id outputs Asinsert Into Students ([Name], [AGE]) VALUES (@ name, @ agn) select @ID = @@ identityGO 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);} An example of using the return parameter: Create Procedure [DBO]. [Sp_insertstudent] (@ Name Char (10), @ Age int, @ id int output) Asinsert Into Students ([Name], [AGE]) VALUES (@ Name, @ agn) select @ id = @@ identity - Test output parameter Return 30 - Test Returns 30GO Try {PSTMT = Con.PrepareCall ("{? = CALL SP_INSERTSTUDENT (?,?,?)}"); PSTMT.SetString (2, "zengqingsong"); pstmt.setint (3, 22); PSTMT.RegisterOutparameter (4, Types.integer); PSTMT.RegisterOutParameter (1, Types.Iteger); int RET = PSTMT.ExecuteUpdate (); // Performing the number of rows of rows Int Ret2 = pstmt.Getint (1); // Return parameter (output parameters) int ID = pstmt.getint (4); // Output parameter system.out.Println (RET); system.out.println (RET2); system.out.println (ID);}